들어가기 전에
Excel의 OFFSET 함수를 사용하면 시작 지점에서 문자 그대로 오프셋(Offset) 된 참조를 만들 수 있습니다. 스프레드시트의 데이터와 구조의 변경에 따라 참조 범위가 동적으로 조정되도록 하는 방법을 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: How to Use the OFFSET Function in Excel
- URL: https://www.howtogeek.com/how-to-use-the-offset-function-in-excel/
※ 본문 내용과 함께 다음 글도 참고하세요.
📌 Offset 함수 사용법 명쾌하게 정리하기
OFFSET 함수 구문
실제로 어떻게 작동하는지 살펴보기 전에 구문을 살펴봅니다. OFFSET 함수에는 5개의 인수가 있습니다. 함수 중에 복잡한 편에 속합니다.
=OFFSET(A,B,C,D,E)
- A: 시작 지점의 셀 참조
- B: A 지점에서 아래로 이동하는 행의 수
- C: A 지점에서 오른쪽으로 이동할 열의 수
- D(선택 사항): 결과에 표시되는 행 수(높이)
- E(선택 사항): 결과에 표시되는 열 수(너비)
값 B, C, D, E는 숫자 또는 셀 참조일 수 있습니다. B에 음수 값을 사용하면 상향 오프셋이 생성되고, C에 음수 값을 사용하면 왼쪽 오프셋이 생성됩니다.
OFFSET 기본 예제
OFFSET의 작동 방식을 설명하기 위해 실용적이지 않은 아주 기본적인 예제를 사용하겠습니다.
=OFFSET(A1,2,3,2,3)
위와 같이 입력하면 A1 셀을 시작점으로 사용하고(A), 2행 아래로 이동하고(B), 3열을 가로질러 이동하며(C), 2셀 높이(D)와 3셀 너비(E)의 일련의 값을 반환합니다.
수식에 값 D와 E를 입력하지 않았다면 결과는 기본적으로 D3(Deer) 셀만 참조하는 것으로 설정되었을 것입니다. 높이와 너비를 지정하지 않으면 Excel은 결과가 참조와 같은 크기라고 가정하기 때문입니다.
이 원칙에 따라 값 A 내에서 결과의 크기를 정의할 수도 있습니다. 예를 들어 다음과 같이 입력합니다.
=OFFSET(A1:A3,2,3)
시작점 A의 크기인 3셀 높이의 결과가 반환됩니다.
실무에서 OFFSET 함수 사용하기
이제 실제 예제에서 OFFSET을 어떻게 사용하는지 살펴봅니다.
실무 예제 1
10명의 직원이 있고 이들이 3주 동안 판매한 유닛 수가 있습니다. OFFSET을 사용하여 세 개의 데이터 보고서를 만들겠습니다.
첫 번째 데이터 보고서(보라색)에서 직원의 ID와 주 번호를 입력하면 Excel에서 직원이 판매한 개수를 알려주기를 원합니다. 이를 위해 H4 셀에 다음과 같이 입력합니다.
=OFFSET(B1,H2,H3)
여기서 B1은 시작점이고, H2는 시작점(즉, 직원 ID)에서 몇 행을 오프셋해야 하는지, H3은 시작점(즉, 주 번호)에서 몇 열을 오프셋해야 하는지 Excel에 알려줍니다. 여기서 셀 참조를 사용한 것을 주목하세요. 이는 OFFSET 함수가 테이블에서 가져오는 데이터가 동적일 수 있음을 의미하기 때문입니다.
B1 셀을 첫 번째 입력으로 선택한 이유는 첫 번째 주가 시작점의 오른쪽에 있는 첫 번째 셀이 되기를 원하기 때문입니다. 따라서 OFFSET 함수를 사용할 때마다 시작점이 어디가 되어야 하는지 신중하게 생각하세요.
위의 수식을 H4 셀에 입력한 후 ID 상자(H2 셀)에 6을 입력하고 주 상자(H3 셀)에 2를 입력합니다. 이렇게 하면 OFFSET 함수가 시작점인 B1 셀에서 6행 아래, 2열 건너의 셀에서 데이터를 가져오므로 2주차에 Ollie가 판매한 제품 수를 알 수 있습니다.
이제 언제든지 H2 및 H3 셀의 값을 변경하여 테이블에서 개별 수치를 가져올 수 있습니다.
다음으로 파란색 표에 직원 보고서를 만들겠습니다. 직원 ID를 입력하면 Excel에서 자동으로 직원의 이름, 보유한 총 데이터 주 수, 해당 직원이 판매한 총 수량 및 평균 수량을 알려줍니다.
먼저, 셀 H7에 직원 ID를 입력합니다. 직원 ID 3, 즉 Jason으로 가보겠습니다. 그런 다음 VLOOKUP 함수를 사용하여 H8 셀에 Jason의 이름을 반환합니다.
=VLOOKUP(H7,A2:B11,2)
이제 와일드카드와 함께 COUNTIF 함수를 사용하여 1행에 “Week”라는 단어 뒤에 숫자가 포함된 셀의 수를 계산하기 위한 쉭을 작성합니다.
=COUNTIF(1:1,“Week*”)
이제 이 정보를 사용하여 Jason의 총 판매량과 평균 판매량을 계산할 수 있습니다. H10 셀에 다음과 같이 입력합니다.
=SUM(OFFSET(B1,H7,1,1,H9))
여기서 B1은 시작점, H7은 아래쪽으로 이동할 행 수(직원 ID), 첫 번째 1은 오른쪽으로 이동할 열 수, 두 번째 1은 결과의 높이, H9은 결과의 너비(총 주 수)입니다. 그러나 OFFSET은 SUM 함수 내에 포함되어 있으므로 결과의 크기는 단일 셀만 됩니다.
수식을 복사/붙여넣기 한 다음, SUM을 AVERAGE로 변경합니다.
=AVERAGE(OFFSET(B1,H7,1,1,H9))
이제 H7 셀의 값을 변경하여 데이터 테이블의 다른 직원에 대한 보고서를 생성할 수 있습니다. 또한 데이터에 주(week)를 하나 더 추가하면 셀 H9가 자동으로 4로 변경되고 셀 H10 및 H11의 후속 계산도 업데이트됩니다.
마지막으로, 주황색 표에 주별 보고서를 만듭니다. 이를 위해서는 주 번호를 입력해야 하며, 그러면 Excel에서 해당 주의 총 판매량과 평균 판매량을 생성합니다. 먼저 H14 셀에 1을 입력하면 Excel에서 1주차 데이터를 생성합니다. 그런 다음 H15 셀에 다음을 입력합니다.
=SUM(OFFSET(B1,1,H14,10,1))
여기서 B1은 시작점이고, 첫 번째 1은 아래쪽으로 오프셋할 행 수, H14는 오른쪽으로 오프셋할 열 수(주 번호), 10은 결과의 높이, 두 번째 1은 결과의 너비입니다. 그러나 OFFSET은 SUM 함수 내에 포함되어 있으므로 결과는 단일 셀 크기만 됩니다.
마지막으로, 해당 수식을 복사하여 H15 셀에 붙여넣고 SUM을 AVERAGE로 변경할 수 있습니다.
=AVERAGE(OFFSET(B1,1,H14,10,1))
실무 예제 2
이 예에서는 서식 있는 표에서 OFFSET을 사용하는 방법과 두 개 이상의 셀에 결과를 반환하는 데 사용할 때 어떤 모습인지 알아봅니다.
여기에서는 올해까지의 현재 매출과 수익이 서식 있는 테이블에 있고, 오른쪽의 보고서 테이블에 지난 3개월의 수익 합계와 같은 기간의 평균 및 총 수익이 개별적으로 표시되도록 하고자 합니다.
먼저, 지금까지 몇 개월이 경과했는지 계산하여 OFFSET을 사용할 때 이 수치를 사용하여 시작점에서 얼마나 멀리 오프셋해야 하는지를 Excel에 알려주기 위한 수식을 F1 셀에 입력합니다.
=MONTH(TODAY())
이제 이 월 번호를 사용하여 지난 3개월 동안의 수익을 표시할 수 있습니다. F3 셀에 '=OFFSET('을 입력하고 시작점인 A1 셀을 클릭합니다. 그러면 서식 지정된 테이블의 월 열 머리글이 수식에 자동으로 추가됩니다.
=OFFSET(Table1[[#Headers],[Month]]
쉼표를 추가하고 OFFSET 수식을 계속합니다. 다음 단계는 아래쪽으로 오프셋할 행 수를 Excel에 알려주는 것입니다. 현재 월(F1 셀)에서 3을 뺀 값이 됩니다.
=OFFSET(Table1[[#Headers],[Month]],SUM(F1-3),
마지막으로, Excel에 오른쪽으로 두 열을 오프셋해야 한다고 알려주면 결과는 세 행 높이와 한 열 너비가 됩니다.
=OFFSET(Table1[[#Headers],[Month]],SUM(F1-3),2,3,1)
수식이 F3에만 있는데도 셀 F4와 F5에 데이터가 들어 있는 것을 볼 수 있습니다. 이를 유출된 배열(Spilled Array)이라고 하는데, 데이터가 활성 셀 위로 유출되었기 때문입니다.
이제 월이 12(12월)로 변경되면 F1 셀의 값이 1만큼 증가하므로 Excel은 이 결과를 자동으로 한 셀 아래로 이동합니다. 그런 다음 다음과 같이 입력하여 표를 완성할 수 있습니다.
F7: =AVERAGE(F3#)
F8: =SUM(F3#)
# 기호는 유출된 배열을 계산하는 Excel의 방식입니다.
기억해야 할 사항
오프셋을 사용할 때 기억해야 할 두 가지 중요한 사항이 있습니다.
- OFFSET은 휘발성 함수(volatile function)이므로 항상 업데이트가 필요합니다. 따라서 큰 스프레드시트나 메모리가 부족한 컴퓨터에서 사용할 경우 성능 문제가 발생할 수 있습니다.
- 위의 모든 예는 OFFSET 함수를 사용할 수 있는 다양한 방법을 설명하기 위해 사용되었습니다. INDIRECT 함수를 사용하는 등 동일한 결과를 생성하는 더 효율적인 다른 방법도 있을 수 있습니다. 함수의 작동 원리를 이해했다면 상황에 따라 어떤 기능을 사용하는 것이 가장 적합한지 결정할 수 있습니다.
마치며
만물은 정해진 용도가 따로 있지 않습니다. Excel 또한 마찬가지입니다. Excel은 숫자를 다루는 사람들만 사용하는 것이 아니라 취미 생활에도 얼마든지 적용할 수 있습니다.
'Excel' 카테고리의 다른 글
| Excel의 유출(Spill)에 대해 알아야 할 모든 것 (18) | 2025.01.02 |
|---|---|
| 상황별 적절한 Excel 글꼴 비교 및 추천 (8) | 2024.12.30 |
| 스프레드시트 마법사가 될 수 있는 엑셀 단축키 (25) | 2024.12.27 |
| 동적 시각화로 엑셀 데이터를 생생하게 표현하는 법 (25) | 2024.12.26 |
| 모든 Excel 전문가가 이해해야 하는 IF와 SWITCH 함수 비교 (35) | 2024.12.25 |
