대부분의 사람들은 Excel에서(in Excel) 상대 참조를 사용하는 데 익숙 합니다. 이는 Excel 스프레드시트의 셀 참조가 기본적으로 상대 참조 방법을 사용하기 때문입니다.
그러나 상대 참조가 짜증나는 경우가 있습니다. 셀을 복사하거나 열과 행을 채울 때마다 셀 참조가 변경됩니다. 참조를 변경하지 않으려면 절대 참조 또는 혼합 참조(상대 및 절대 참조 포함)를 사용해야 합니다.
절대 참조에서는 열 참조와 행 참조가 모두 "잠겨" 있으므로 해당 셀에서 복사하거나 채울 때 둘 다 변경되지 않습니다.
이 기사에서는 절대 참조를 사용하여 Excel 이 데이터에 대해 원하는 방식으로 작동하도록 하는 방법을 살펴봅니다.
Excel 에서 상대 참조(References) 가 작동 하는 방식
Excel 스프레드시트 에 값을 입력하면 각 셀에 특정 문자와 숫자가 할당됩니다. 이것은 해당 셀의 열과 행을 나타냅니다.
예를 들어, 아래 스프레드시트에서 "1" 값은 A열과 2행에 있습니다. 따라서 이 셀에 대한 "참조"는 A2 입니다.
이 셀을 기반으로 다음 셀에서 계산을 수행하려면 1을 추가하여 다음 공식을 작성합니다.
=A2+1
이 수식은 A2의 값을 수식에 삽입하고 계산한 다음 이 수식이 있는 셀에 결과를 출력합니다.
Enter 키(Enter) 를 누르면 결과가 표시됩니다.
상대 참조를 사용하면 이 수식을 다른 모든 셀에 입력할 필요가 없습니다. 원래 수식이 있는 셀의 모서리를 원하는 만큼 아래로 드래그하기만 하면 됩니다.
다음 셀에서 A2에 대한 참조는 A3이 됩니다. 그 아래 셀에서 A3는 A4가 됩니다. 즉, Excel 은 이전 셀에 1을 추가하려는 것을 알고 있으므로 아래로 끌면 Excel 에서 그에 따라 숫자(행 참조)를 업데이트합니다.(Excel)
열을 가로질러 수식을 드래그하는 경우에도 동일한 방식으로 작동합니다. 숫자를 업데이트하는 대신 Excel 은 참조의 뒷부분(열)을 업데이트하여 항상 위에 있는 셀을 참조합니다.
오른쪽 열에는 B2가 포함되고 오른쪽 열에는 C2가 포함되는 식입니다.
이것은 열 참조와 셀 참조 모두에 대해 상대 주소 지정이 작동하는 방식에 대한 간단한 예입니다.
Excel 에서 절대 참조(References) 가 작동 하는 방식
(Absolute)Excel 의 (Excel)절대 참조를 사용하면 Excel에서 자동으로 행 또는 열 참조를 업데이트(Excel automatically update) 하지 않고 동일한 셀을 참조할 수 있습니다. "혼합" 참조는 행 또는 열만 잠그는 경우이고 "절대 참조"는 둘 다 잠그는 경우입니다.
몇 가지 예를 살펴보겠습니다.
스프레드시트의 맨 위 행에 "10"이 있고 그 아래의 모든 행에 이 숫자를 왼쪽 셀의 숫자로 곱하려고 한다고 가정해 보겠습니다.
이렇게 하려면 다음과 같은 수식을 입력합니다.
=B$2*A3
이렇게 하면 "2" 참조가 잠기므로 이 수식이 있는 셀을 그 아래의 셀로 드래그해도 행 참조가 변경되지 않습니다. A3는 "잠금 해제"된 상태로 유지되기 때문에 행과 열 참조는 여전히 자동으로 변경되고 항상 왼쪽에 있는 셀을 참조합니다.
같은 열의 셀로 드래그하기 때문에 이것이 작동한다는 것을 알 수 있습니다. 따라서 앞에 달러 기호($)를 배치하여 열(B)을 잠글 필요가 없습니다.
이것의 문제는 원래 수식의 오른쪽에 동일한 수식을 사용하려는 경우 "B" 참조가 변경되고 수식이 의도한 대로 더 이상 B2를 참조하지 않는다는 것입니다.
양방향 채우기가 제대로 작동하도록 하기 위해 혼합 참조 대신 절대 참조를 사용하는 방법을 살펴보겠습니다.
Excel 에서 절대 (Absolute) 참조(References) 를 올바르게 사용 하기
이 공식에서 올바른 참조를 사용하려면 수행하려는 작업을 정확하게 고려해야 합니다.
이 경우 오른쪽으로 채울 때 다음 동작을 원합니다.
- 항상 B2 셀의 값을 참조하십시오.
- 항상 A열의 값을 참조하십시오.
- 열 A의 행 참조를 공식의 현재 행으로 이동
이러한 동작을 살펴보면 이제 "잠금"해야 하는 것과 그렇지 않은 것을 알 수 있습니다. "B"와 "2"는 모두 잠겨 있어야 합니다(변경되지 않음). 또한 A열을 잠가야 합니다.
따라서 B3의 수식은 다음과 같아야 합니다. =$B$2*$A3
이제 이 동일한 셀을 위나 아래로 끌면 수식이 의도한 대로 작동합니다.
절대 참조를 올바르게 사용하는 것은 까다로울 수 있으므로 어느 방향으로든 열이나 행(columns or rows) 을 채울 때 Excel에서 수식을 업데이트하는 방법을 신중하게 고려해야 합니다 .
Excel 에서 참조 유형(Reference Types) 순환
절대 참조가 있는 수식을 입력 할 때 (typing formulas)F4 키를 눌러 프로세스 속도를 높일 수 있습니다 . 그러면 셀 참조가 절대 참조가 됩니다.
커서는 F4 키를 누를 때 셀 참조의 양쪽(또는 셀 참조의 중간)에 있을 수 있으며 여전히 단일 참조를 절대 참조로 변환합니다.
절대값을 원하지 않는 경우(예: 대신 혼합형) 참조가 원하는 대로 표시될 때까지 F4 키를 계속 누릅니다.
수식의 다른 셀에 대한 참조를 추가하려면 거기에 커서를 놓고 F4 키를 다시 순환하기 시작하면 됩니다.
수식을 구성했으면 Enter 키(Enter) 를 누르고 원하는 방향으로 스프레드시트를 채우기 시작하세요. 참조를 올바르게 설정하면 모든 것이 예상대로 작동합니다.
How to Use Absolute References in Excel
Most people are familiar with using relative referenceѕ in Excel. This is because cell references in Excel spreadsheets default to the relative reference method.
However, there are times when relative references get annoying. It will change cell references any time you copy cells or fill columns and rows. If you don’t want references to change, you’ll need to go with absolute references or mixed references (including relative and absolute references).
In an absolute reference, both the column and row references are “locked,” so neither of those change when you copy or fill from that cell.
This article will look at how you can use absolute references to make Excel behave the way you want with data.
How Relative References Work in Excel
When you enter values into an Excel spreadsheet, each cell has a specific letter and number assigned to it. This represents the column and row of that cell.
For example, the value of “1” in the spreadsheet below is in column A and row 2. So the “reference” to this cell is A2.
If you want to perform a calculation in the next cell based on this cell, by adding 1 to it, you will write the following formula:
=A2+1
This formula will insert the value from A2 into the formula, calculate it, and then output the result into the cell where this formula is.
When you press Enter, you’ll see the result.
With relative referencing, you don’t have to type this formula into every other cell. All you have to do is drag the corner of the cell with the original formula down as far as you’d like.
In the next cell, the reference to A2 will become A3. In the cell below that, A3 will become A4. In other words, Excel knows that you want to add 1 to the previous cell, so Excel updates the number (the row reference) accordingly as you drag down.
This works in the same way if you drag the formula across columns. Instead of updating the number, Excel will update the latter part of the reference (the column) to always reference the cell above it.
The column to the right contains B2, to the right of that contains C2, and so on.
This is a simple example of how relative addressing works for both column and cell references.
How Absolute References Work in Excel
Absolute references in Excel let you reference the same cell, rather than letting Excel automatically update the row or column references for you. “Mixed” referencing is if you lock only the row or the column, and “absolute referencing” is when you lock both.
Let’s look at some examples.
Let’s say your spreadsheet has a “10” in a top row, and you want every row beneath it to multiply that number by the number in the cell to the left.
To do this, you’d enter a formula that looks like this:
=B$2*A3
This locks the “2” reference so it won’t change the row reference if you drag the cell with this formula down to the cells below it. Since A3 remains “unlocked” both the row and column reference will still change automatically and always reference the cell to the left.
You will notice that this only works because you’re dragging down into cells in the same column. So you don’t have to lock column (B) by placing a dollar sign ($) in front of it.
The problem with this is that if you want to use the same formula to the right of the original formula, the “B” reference will change, and the formula will no longer reference B2 as intended.
Let’s take a look at how to use absolute references instead of mixed references to make filling in both directions work properly.
Correctly Using Absolute References in Excel
To use the correct referencing in this formula, you must consider precisely what you’re trying to do.
In this case, we want the following behaviors when filling to the right.
- Always reference the value in cell B2
- Always reference the value in column A
- Shift the reference of the row for column A to the current row of the formula
Looking at these behaviors, you now know what you need to “lock” and what you don’t. Both “B” and “2” need to be locked (unchanged). Also, column A needs to be locked.
So your formula in B3 needs to look like this: =$B$2*$A3
Now when you drag this same cell either down or up, the formula works as intended.
Correctly using absolute referencing can get tricky, so it’s essential to take the time to carefully consider how you want Excel to update the formula as you fill columns or rows in either direction.
Cycling Through Reference Types in Excel
You can speed along the process when you’re typing formulas with absolute references by pressing the F4 key, which will make the cell reference absolute.
The cursor can be on either side of the cell reference (or even in the middle of it) when you press F4, and it’ll still convert that single reference to absolute.
If you don’t want absolute (for example, mixed instead), keep tapping F4 until the reference looks the way you want.
If you want to add any kind of referencing to other cells in the formula, just place your cursor there and start cycling through F4 again.
Once you have configured your formula, just press Enter and begin filling your spreadsheet in any direction you like. If you set up your references right, everything should work just as expected.
