엑셀 꿀팁: 아래로 드래그해도 옆으로(K열→L열) 자동 참조하는 마법의 수식! (#REF! 오류 해결)

안녕하세요! 엑셀 작업을 하다 보면 이런 난감한 상황이 생기곤 하죠.

"수식은 아래 행으로 복사(드래그)하고 싶은데, 참조하는 데이터는 옆으로(K열, L열, M열...) 한 칸씩 옮겨졌으면 좋겠다!"

하지만 엑셀의 기본 채우기 핸들은 아래로 내리면 숫자(행)만 커질 뿐, 알파벳(열)은 변하지 않습니다. 일일이 수정하자니 양이 너무 많고(K열부터 BE열까지라니!), 그냥 끌어다 놓으면 오류가 나기도 하죠.

오늘은 이 고민을 한 방에 해결해 줄 OFFSET 함수 사용법을 아주 쉽게 알려드릴게요!

1. 왜 그냥 드래그하면 안 될까요?

엑셀은 아래로 당기면 "아래 칸으로 가라는 뜻이구나!"라고 생각해서 숫자만 키웁니다.

알파벳을 바꾸고 싶다면 엑셀에게 "내가 아래로 한 칸 갈 때마다, 너는 옆으로 한 칸 이동해서 데이터를 가져와!"라고 명령을 내려야 해요.

2. 해결사: OFFSET 함수 등장!

이럴 때 사용하는 마법의 수식이 바로 이것입니다. F3 셀에 아래 수식을 복사해서 넣어보세요.

=COUNTIF(OFFSET('01. 데이터'!K3:K4048, 0, ROW(A1)-1), "o")

3. 수식, 아주 쉽게 이해하기 (딱 3가지만 보세요)

'01. 데이터'!K3:K4048
: 우리의 기준점입니다. 제일 처음 시작하는 K열 범위를 딱 고정해둔 거예요.

ROW(A1)-1: 이 수식의 핵심 엔진입니다!

첫 번째 칸(F3)에서는 0이 되어 K열을 그대로 봅니다.

아래로 한 칸 내리면 자동으로 1이 되어, 기준점에서 오른쪽으로 1칸 옆인 L열을 봅니다.

또 내리면 자동으로 2가 되어 M열을 보게 되죠.

"o": 우리가 찾고 싶은 데이터(알파벳 o)의 개수를 세라는 뜻입니다.

4. 자주 발생하는 #REF! 오류, 왜 그럴까요?

혹시 수식을 넣었는데 #REF!라는 글자가 뜨나요?

이건 보통 "참조할 범위를 벗어났다"는 뜻이에요.

INDEX 함수를 쓸 때 범위를 K열 하나만 잡아놓고 "옆으로 가!"라고 하면 엑셀이 당황해서 오류를 냅니다.

위에서 알려드린 OFFSET 수식은 기준점만 있으면 알아서 옆으로 점프하기 때문에 오류 걱정 없이 안전하게 사용할 수 있습니다.

5. 마무리 팁

이제 F3 셀에 수식을 넣었다면, 오른쪽 하단 모서리를 잡고 F49 셀까지 쭉~ 내리세요. K열부터 BE열까지의 데이터가 순식간에 착착 계산될 거예요!

일일이 수정하던 노가다 작업은 이제 끝! 엑셀 함수 하나로 칼퇴하세요!


엑셀, 채우기핸들, 가로참조세로복사, OFFSET함수, ROW함수, 엑셀꿀팁, 직장인엑셀, 엑셀함수정리, REF오류해결, 열이동복사, COUNTIF응용, 엑셀독학, 업무자동화, 데이터집계, 엑셀수식오류