엑셀 OFFSET 함수 완벽 정리 – MATCH 조합 조회부터 동적 범위까지

오늘은 엑셀의 OFFSET(오프셋) 함수를 실무 예제 3가지와 함께 소개하려고 합니다.

먼저 수식의 구문을 살펴본 뒤, OFFSET 함수를 활용해 실제 업무 문제를 해결하는 방법을 차근차근 알아보겠습니다.

OFFSET 함수란?

OFFSET 함수는 특정 셀(기준 셀) 또는 범위(기준 범위)로부터 지정한 행·열만큼 떨어진 위치에 있는 셀(대상 셀)이나 범위(대상 범위)의 참조 값을 반환하는 함수입니다.

아래 그림은 OFFSET 함수를 사용해 단일 셀(왼쪽) 또는 범위(오른쪽)를 참조하는 방법을 보여줍니다.

그림을 통해 기준 셀과 대상 셀이 무엇인지 직관적으로 이해할 수 있습니다.

초록색으로 표시된 셀이 대상 셀이며, 노란색으로 표시된 셀들이 대상 범위를 구성합니다. 파란색으로 표시된 셀은 기준 셀입니다.

엑셀 OFFSET 함수 완벽 정리 – MATCH 조합 조회부터 동적 범위까지

그림 1

OFFSET 함수 구문

OFFSET 함수의 구문은 다음과 같습니다. OFFSET(reference, rows, cols, [height], [width])

reference	필수 인수입니다. 오프셋 계산의 시작점이 되는 셀 또는 셀 범위입니다. 범위를 지정할 경우 셀들이 서로 인접해 있어야 한다는 점에 유의하세요.
rows	필수 인수입니다. 기준 셀(또는 기준 범위의 왼쪽 위 셀)에서 위나 아래로 몇 행 떨어져 있는지 나타냅니다. rows는 양수와 음수 모두 사용할 수 있습니다. 그림 1의 왼쪽 부분을 보면, 수식을 OFFSET(C3, -1, -1)로 바꾸면 대상 셀은 B2가 됩니다. B2는 C3보다 한 행 위에 있기 때문입니다.
cols	필수 인수입니다. 기준 셀(또는 기준 범위의 왼쪽 위 셀)에서 왼쪽이나 오른쪽으로 몇 열 떨어져 있는지 나타냅니다. rows 인수와 마찬가지로 cols 값도 양수와 음수 모두 가능합니다. B4를 기준 셀로, C3를 대상 셀로 지정한다면 OFFSET 함수는 어떻게 작성해야 할까요? 정답은 OFFSET(B4, -1, 1)입니다. cols가 양수이고, C3는 B4보다 한 열 오른쪽에 있음을 알 수 있습니다.
height	선택 인수입니다. 대상이 범위일 때만 사용합니다. 대상 범위에 포함되는 행 수를 의미하며 반드시 양수여야 합니다. 그림 1의 오른쪽 부분에서 대상 범위가 두 개의 행으로 이루어져 있으므로, 이 경우 height는 2로 설정합니다.
width	선택 인수입니다. 역시 대상이 범위일 때만 사용합니다(그림 1의 오른쪽 부분 참조). 대상 범위에 포함되는 열 수를 나타내며, 반드시 양수여야 합니다.

그럼 이제 OFFSET 함수를 활용해 실제 문제를 해결하는 방법을 살펴보겠습니다.

예제 1: OFFSET + MATCH 함수 조합으로 오른쪽→왼쪽 조회하기

VLOOKUP 함수는 왼쪽→오른쪽 방향의 조회만 가능하다는 점은 잘 알려진 사실입니다.

찾으려는 값이 반드시 테이블 배열의 첫 번째 열에 위치해야 하기 때문입니다.

새로운 조회 값을 추가하려면 테이블 전체 범위를 한 열 오른쪽으로 밀어야 하고, 다른 열을 조회 기준으로 사용하고 싶다면 데이터 구조 자체를 변경해야 하는 불편함도 있습니다.

하지만 OFFSET 함수와 MATCH 함수를 결합하면 VLOOKUP 함수의 이러한 한계를 극복할 수 있습니다.

MATCH 함수란 무엇이고, 어떻게 OFFSET 함수와 결합해 조회를 수행할 수 있을까요?

MATCH 함수는 지정한 셀 범위에서 특정 항목을 검색한 후, 해당 항목이 범위 내에서 차지하는 상대적인 위치를 반환합니다.

그림 2.1(국가별·연도별 매출 데이터)의 범위 B3:B8을 예로 들어 보겠습니다.

수식 "=MATCH("USA", B3:B8, 0)"는 USA가 범위 내 첫 번째 항목이므로 1을 반환합니다(B10, C10 셀 참조).

또 다른 범위 C2:F2에서 수식 "=MATCH(2015, C2:F2, 0)"는 2015가 세 번째 항목이므로 3을 반환합니다(B11, C11 셀 참조).

이제 다시 OFFSET 함수로 돌아가 보겠습니다.

B2 셀을 기준 셀로, E3 셀을 대상 셀로 지정한다면 OFFSET 수식은 어떻게 작성해야 할까요?

E3는 B2보다 1행 아래, 3열 오른쪽에 있습니다.

따라서 수식은 "=OFFSET(B2, 1, 3)"이 됩니다. 빨간색 숫자들을 잘 살펴보세요. 서로 일치한다는 것을 발견하셨나요?

바로 이것이 OFFSET 함수와 MATCH 함수를 결합하는 방법의 핵심입니다. MATCH 함수를 OFFSET 함수의 두 번째 또는 세 번째 인수로 활용하는 것입니다(C13 셀 참조).

C14 셀은 VLOOKUP 함수로 동일한 데이터를 가져오는 방법을 보여줍니다.

VLOOKUP 함수를 작성하기 전에 2015년 매출이 테이블 배열 B2:F8의 네 번째 열에 기록되어 있다는 사실을 미리 알고 있어야 합니다.

즉, VLOOKUP 함수를 사용하려면 데이터 구조를 매우 잘 파악하고 있어야 합니다. 이것이 VLOOKUP의 또 다른 한계입니다. 반면 MATCH 함수를 OFFSET 함수의 인수로 사용하면 열 번호를 몰라도 됩니다.

열이 많은 경우에는 특히 유용한 방법입니다.

그림 2.1

이제 좀 더 복잡한 예제를 살펴보겠습니다.

회사명, 담당자 이름, 이메일 주소가 담긴 테이블이 있다고 가정해 보겠습니다.

담당자 이름으로 회사명을 찾거나, 이메일 주소로 담당자 이름을 찾고 싶다면 어떻게 해야 할까요?

그림 2.2를 보면, 범위 B5:E8에 회사 정보가 들어 있습니다. C2 셀과 B3 셀에 입력값을 넣고 빨간색 테두리 안의 수식을 활용하면, 담당자 이름만으로 회사명을 찾아낼 수 있습니다.

범위 D2:E4는 이메일 주소로 담당자 이름을 찾는 방법을 보여줍니다.

요약하면, 이 두 예제는 오른쪽→왼쪽 방향의 조회가 가능하며, 찾는 값이 반드시 맨 왼쪽 열에 있을 필요가 없다는 것을 보여줍니다. 테이블 배열의 어느 열에든 찾는 값을 둘 수 있습니다.

그림 2.2

예제 2: OFFSET + COUNT 함수 조합으로 계산 자동화하기

새로운 숫자가 추가될 때마다 계산을 자동화하는 방법을 소개하기 전에, 먼저 열의 마지막 숫자를 자동으로 반환하는 방법부터 알아보겠습니다.

아래 그림은 인사팀의 급여 데이터입니다. B열의 마지막 숫자를 구하고 싶다면 OFFSET 함수를 적용해 수식 "=OFFSET(C2, 9, 0)"을 작성할 수 있습니다.

이 수식에서 핵심은 바로 9라는 숫자입니다.

이 숫자를 자동으로 구할 수만 있다면, 열의 마지막 숫자도 자동으로 찾을 수 있습니다.

9는 C열에서 숫자가 들어 있는 셀의 개수입니다.

COUNT 함수에 익숙하다면, COUNT 함수가 범위 내에서 숫자가 포함된 셀의 개수를 세어 준다는 것을 알고 있을 겁니다.

예를 들어 수식 "=COUNT(C3:C11)"은 C3부터 C11까지의 셀 중 숫자가 포함된 셀의 개수를 셉니다.

우리의 경우 열 전체에 숫자가 몇 개 있는지 알고 싶으므로, C열의 모든 행을 포함하는 C:C 같은 참조를 사용해야 합니다.

G4와 H4 셀을 보면, "=COUNT(C:C)"가 반환한 값이 정확히 9와 일치합니다.

따라서 앞선 OFFSET 수식에서 9를 COUNT(C:C)로 바꾸면 새로운 수식 "=OFFSET(C2, COUNT(C:C), 0)"(H5 셀)을 얻을 수 있습니다.

이 수식이 반환하는 값은 87000으로, 정확히 C열의 마지막 숫자입니다.

이제 자동 계산으로 넘어가 보겠습니다. C열의 모든 숫자의 합계를 구하고 싶다고 가정해 보죠.

SUM 함수와 OFFSET 함수를 함께 사용한다면 수식은 "=SUM(OFFSET(C2, 1, 0, 9, 1))"이 됩니다.

9는 범위 C3:C11의 총 행 수이자, C열에서 숫자가 포함된 셀의 총 개수입니다.

따라서 수식을 "=SUM(OFFSET(C2, 1, 0, COUNT(C:C), 1))"처럼 새롭게 작성할 수 있습니다.

G10과 H10 셀을 보면, 9명 직원의 급여 총액은 $521,700입니다.

이제 C12 셀에 $34,000 같은 숫자를 입력하면 G5와 G10 셀의 값이 각각 $34,000과 $555,700으로 자동 변경됩니다.

G5나 G10 셀의 수식을 수정할 필요가 없으니, 이것이 바로 제가 말하는 '자동화'입니다.

단, COUNT 함수를 사용할 때는 주의가 필요합니다. COUNT 함수는 숫자가 포함된 셀의 개수만 반환하기 때문입니다.

예를 들어 "=COUNT(B:B)"는 B열에 숫자가 포함된 셀이 없으므로 9가 아닌 0을 반환합니다(G3, H3 셀 참조).

D열에는 숫자가 포함된 셀이 10개 있고, "COUNT(D:D)"의 반환값도 10입니다.

하지만 C열에서 했던 것처럼 D열의 마지막 숫자를 찾으려고 하면 0이 반환됩니다(G8, H8 셀 참조).

분명히 0은 우리가 원하는 값이 아닙니다. 무엇이 문제였을까요? D13 셀은 D2 셀에서 10행이 아니라 11행 떨어져 있습니다.

이는 G7 셀의 수식 "=OFFSET(D2, COUNT(D:D)+1, 0)"으로도 확인할 수 있습니다.

요약하면, COUNT 함수와 OFFSET 함수를 결합해 계산을 자동화하려면 숫자들이 서로 인접해 있어야 합니다.

그림 3

예제 3: OFFSET 함수로 동적 범위 만들기

회사의 월별 판매 수량을 차트로 만들고 싶다고 가정해 보겠습니다. 그림 4.1은 현재 데이터와 이를 기반으로 만든 차트를 보여줍니다.

매달 최신 월의 판매 수량이 C열의 마지막 숫자 아래에 추가됩니다.

차트를 자동으로 업데이트하는 쉬운 방법이 있을까요?

차트 업데이트의 핵심은 OFFSET 함수를 사용해 '판매 수량' 열에 대한 동적 범위 이름을 만드는 것입니다.

판매 수량의 동적 범위는 새 데이터가 입력될 때마다 모든 판매 데이터를 자동으로 포함하게 됩니다.

그림 4.1

동적 범위를 만들려면 수식 탭을 클릭한 후 이름 관리자 또는 이름 정의를 선택합니다.

이름 정의를 클릭하면 아래의 새 이름 대화 상자가 나타납니다.

이름 관리자를 선택한 경우에도 새로 만들기를 클릭해야 새 이름 대화 상자가 표시됩니다.

그림 4.2

이름: 입력 상자에는 동적 범위의 이름을 입력합니다. 그리고 참조 대상: 입력 상자에는 C열에 입력되는 판매 수량 값을 기반으로 동적 범위를 생성하는 OFFSET 수식 "=OFFSET(Figure4!$C$2, 1, 0, COUNT(!$C:$C), 1)"을 입력합니다.

기본적으로 이름은 통합 문서 전체에 적용되며 통합 문서 내에서 고유해야 합니다.

하지만 여기서는 범위를 특정 시트로 제한하고 싶습니다.

따라서 범위: 입력 상자에서 Figure4를 선택합니다. 확인을 클릭하면 동적 범위가 생성되고, 새 데이터가 입력될 때마다 모든 판매 데이터가 자동으로 포함됩니다.

이제 차트의 아무 곳이나 마우스 오른쪽 버튼으로 클릭한 후 "데이터 선택"을 선택합니다.