엑셀 수식에서 $기호의 힘을 과소평가하지 마세요

마이크로소프트 엑셀은 보통 '왼쪽 셀 하나를 보라'는 식으로 방향을 기준으로 생각합니다. 수식이 이동하고 참조가 사라질 때까지는 괜찮습니다. 달러($) 기호를 사용하는 방법을 알면 엑셀에 언제 고정된 주소를 보고 언제 이동하는 대상을 보아야 하는지 알릴 수 있어, 더 견고한 수식을 만들 수 있습니다.

저는 가격과 수량이 주요 데이터셋에 있고, 세금 및 수수료율은 별도의 '상수' 셀에 있는 판매 추적기를 사용하고 있습니다. 목표는 셀을 그리드 전체로 끌어도 정확하게 유지되는 수식을 만드는 것입니다.

상대 참조: 수식이 함께 이동하도록 하세요

같은 상대적 논리를 열 아래로 적용하세요

달러 기호($)의 마법을 이해하기 전에, 우리는 Excel이 그것 없이 어떻게 작동하는지 이해할 필요가 있습니다. B6와 같은 일반 셀 참조는 실제로 단지 지침의 집합일 뿐입니다. 예를 들어, 셀 D6에 =B6*C6를 입력하면, Excel은 '내 왼쪽으로 두 칸 떨어진 셀을 가져와, 내 왼쪽으로 한 칸 떨어진 셀과 곱하라'라고 생각합니다.

이 상대 논리가 바로 Excel을 강력하게 만드는 이유입니다. 수식이 고정된 주소가 아니라 위치를 기준으로 하기 때문에, 각 행마다 수학 계산을 다시 작성할 필요가 없습니다:

1. 셀 E6에 수식 =C6*D6을 입력하고 Ctrl+Enter를 눌러 현재 셀을 선택한 상태로 수식을 확정합니다. 이렇게 하면 Widget A의 소계가 계산됩니다.
2. 셀 E6의 오른쪽 아래에 있는 채우기 핸들을 클릭하고 드래그하여 셀 E8까지 내립니다.
3. 셀 E7을 선택하고, 수식이 =C7*D7로 조정된 것을 확인하세요.

공식을 한 행 아래로 이동했기 때문에 Excel은 지시도 한 행 아래로 이동하여 맞추었습니다. 만약 E6 셀을 복사하여 K15 셀에 붙여넣으면 수식은 =I15*J15로 변경됩니다.

절대 참조: 참조를 위한 앵커를 만드세요

항상 같은 셀을 참조하세요

상대 참조는 단일 고정 셀을 참조해야 할 때까지는 훌륭합니다—예를 들어 B3 셀에 있는 우리의 "글로벌 세금" 승수처럼요. 여기에 상대 수식을 사용하려고 하면 상황이 빠르게 엉망이 됩니다.

만약 셀 F6에 =E6*B3을 입력하면 첫 번째 행에서는 작동합니다. 그러나 아래로 드래그하면 F7의 수식은 =E7*B4가 됩니다. B4 셀은 비어 있으므로 계산 결과는 0이 됩니다.

이를 수정하려면 $ 기호를 사용하여 수식을 셀 B3에 고정해야 합니다:

1. 셀 F6에 =E6*$B$3을 입력하고 Ctrl+Enter를 누릅니다. B 앞의 $ 기호는 열을 고정하고, 3 앞의 $ 기호는 행을 고정합니다.
2. 채우기 핸들을 클릭하고 F8까지 끌어 내립니다.
3. 셀 F7을 선택하세요. 이제 이 셀에 =E7*$B$3이 표시되는 것을 볼 수 있습니다. 상대적인 총 좌표는 이동했지만 세금 좌표는 정확히 원래 위치에 남아 있습니다.

전문가 팁: 상수를 참조하는 더 깔끔한 방법

B3 셀도 작업을 수행할 수는 있지만, 전문가들은 종종 셀에 이름을 지정하는 것을 선호합니다. 셀 B3를 선택하고 이름 상자(수식 입력줄 왼쪽에 있는 작은 필드)에 Global_tax를 입력하면, 수식을 =E6*Global_tax로 작성할 수 있습니다.

명명된 범위는 기본적으로 절대값이므로, $기호의 시각적 혼란 없이 제자리에 고정됩니다.

혼합 참조: 반잠금 그리드 마스터하기

행 또는 열 중 어느 것을 고정할지 선택하세요

혼합 참조는 Excel에서 고효율 스프레드시트를 만드는 비결입니다. 우리 트래커에서는 열 F의 합계를 기준으로 두 가지 다른 수수료 계층(Tier 1은 C3, Tier 2는 D3)을 계산하고자 합니다. 모든 열에 대해 다른 수식을 작성하는 대신, 하나의 수식을 작성하고 전체 그리드에 걸쳐 끌어다 사용할 수 있습니다:

1. 셀 G6에 =$F6*C$3을 입력하고 Ctrl+Enter를 누릅니다. $F는 열을 고정하므로, 공식을 H열로 끌어도 여전히 F열의 합계를 참조합니다. $3은 행을 고정하므로 아래로 끌어도 여전히 3행의 수수료율을 참조합니다.
2. G6에서 H6까지 채우기 핸들을 클릭하고 끌어오세요.
3. G6와 H6가 모두 선택된 상태에서 채우기 핸들을 H8까지 끌어내립니다.
4. 셀 H8에서 결과를 확인하세요. 수식은 =$F8*D$3입니다.

전략적으로 배치된 $ 기호 때문에, 엑셀은 수식의 어느 부분을 이동하고 어느 부분을 고정할지 정확히 알 수 있었다.

셀을 넘어서: $ 기호를 다른 도구로 가져가기

조건부 서식에서 다른 참조 유형 사용

$의 힘은 표준 셀 수식에만 국한되지 않으며, 고급 조건부 서식의 엔진이 되기도 합니다. 여기서 많은 사람들이 문제에 봉착하는데, 그 이유는 Excel의 기본 '드리프트' 논리가 그리드에서처럼 대화 상자의 수식 입력 필드에도 적용된다는 것을 깨닫지 못하기 때문입니다.

만약 F열의 세금 포함 합계가 100달러를 초과하면 해당 행의 모든 셀을 녹색으로 만들고 싶다고 가정해 보십시오. 이 경우 혼합 참조를 사용해야 합니다. 달러($) 기호가 없으면 각 셀은 자신의 상대 참조를 평가하여 서식이 일관되지 않게 됩니다.

다음과 같이 하세요:

1. 머리글 행을 제외하고 B6에서 H8까지 데이터를 선택하세요.
2. 홈 탭에서 조건부 서식을 클릭하고, 새 규칙을 선택합니다.
3. 클릭 수식을 사용하여 서식을 지정할 셀 결정.
4. 이 수식이 true인 값 서식 지정 필드에 =$F6>100을 입력합니다.
5. 서식을 클릭하고, 채우기 색(연두색 등)을 선택한 다음 확인을 클릭하세요.

당신이 열(₣)을 잠갔기 때문에, 행의 모든 셀은 녹색으로 바뀔지 여부를 결정하기 위해 열 F의 값을 참조하도록 강제됩니다.

이 동일한 논리는 데이터 유효성 검사 규칙을 설정할 때와 같이 Excel 대화 상자에서 수식 필드를 볼 때마다 적용됩니다.

$기호는 수식의 운전대 역할을 하여 어떤 참조가 고정되고 어떤 참조가 이동할지 제어할 수 있게 합니다. 하지만 이것은 작업이 복잡해질수록 마주치게 될 많은 Microsoft Excel 기호 중 하나일 뿐입니다. 이러한 문자를 사용하는 방법을 배우면 스프레드시트가 더욱 견고해지고, 수학 계산이 정확하며, 작업 흐름이 훨씬 더 효율적으로 됩니다.