OFFSET 함수의 기본적인 개념에 대해서는 이전 글을 참고해 주시길 바랍니다.
[엑셀 함수] OFFSET 함수의 개념 이해하기
OFFSET 함수는 기준 위치(셀/범위)에서 지정한 행과 열만큼 이동한 후, 설정한 너비와 높이만큼의 범위를 반환하는 함수입니다. 범위를 반환하기 때문에 SUM, AVERAGE 등 계산 함수와 함께 사용되는
writingpjt.tistory.com
OFFSET 함수는 셀뿐만 아니라 범위도 반환할 수 있습니다. 단, 오피스365 이전 버전에서는 범위를 시트에 직접 반환하기 위해서는 OFFSET 함수를 배열 수식으로 작성해야 합니다. 반면 SUM 함수나 AVERAGE 함수 등으로 범위 안의 값들을 계산할 때는 배열수식으로 작성할 필요가 없습니다.
이전 글과 마찬가지로, OFFSET 함수로 범위를 반환하는 법을 위의 사진처럼 1~9까지 채워진 3x3 표로 설명드리겠습니다.
1. OFFSET 함수 배열수식으로 시트에 범위 출력하기
1(B3셀)에서 시작해서, 1~9(B3:D5)까지 범위를 출력하기 위해서는 행/열 이동은 하지 않고, 3개의 열과 행을 출력해야 합니다. 따라서 OFFSET 함수 수식을 OFFSET(B3, 0, 0, 3, 3)로 작성해야 합니다.
1-1) 엑셀 오피스365 버전
엑셀 오피스365 버전에서는 OFFSET(B3, 0, 0, 3, 3) 입력 시 위의 사진처럼 자동적으로 해당 범위가 시트에 바로 표시됩니다. 함수를 K3셀에 입력했지만, 파란색 선 범위 안의 셀들에도 동일한 함수가 입력되기 때문입니다.
1-2) 오피스365 이전 버전
오피스365 이전 버전에서는 위의 사진처럼 출력받고 싶은 범위를 먼저 드래그(3행 3열)한 후, OFFSET(B3, 0, 0, 3, 3) 수식을 입력한 뒤 <CTRL + SHIFT + ENTER>를 눌러줘 배열 수식으로 만들어야 합니다. <CTRL + SHIFT + ENTER>를 누르면 수식창 양 끝에 '{', '}'가 입력되면서 배열 수식이 됩니다.
만약에 범위를 3행 3열로 드래그하지 않고, 3행 3열보다 더 작은 범위를 드래그하면 수식의 일부만 출력됩니다.
한편, 만약에 3행 3열보다 더 큰 범위를 드래그한 후 배여루식을 입력하면 범위 이상의 셀들에 #N/A가 출력됩니다.
2. SUM 함수와 OFFSET 함수 조합해보기
OFFSET 함수로 범위를 반환할 때는 홀로 쓰일 때보단 오히려 SUM 함수 등 다른 함수와 많이 조합하여 사용합니다.
위의 사진처럼 SUM(OFFSET(B3, 0, 0, 3, 3))는 SUM(B3:D5)와 동일한 결과를 출력하는데요.
왜냐하면 OFFSET(B3, 0, 0, 3, 3)이 범위 B3:D5 내 값들을 반환하기 때문입니다.
그렇다면, 왜 굳이 SUM 함수만 사용하지 않고 OFFSET 함수와 SUM 함수를 조합하여 수식을 짜는 경우가 생길까요? 왜냐하면 OFFSET 함수로 계산할 범위를 조건에 따라 자동적으로 설정해줄 수 있기 때문입니다. 이를 '동적범위'라고도 표현합니다.
다음 글에서는 동적 범위의 예시로 OFFSET 함수를 활용해서 월 별로 연 누적 매출을 구하는 방법을 소개해 드리겠습니다.
관련 글
'엑셀, VBA, 파워쿼리 > 엑셀 스프레드시트' 카테고리의 다른 글
| [엑셀 함수] OFFSET으로 연 누적 실적 구하기 (OFFSET 동적범위) (2) | 2022.10.24 |
|---|---|
| [엑셀 팁] '선택하여 붙여넣기'로 여러셀 숫자 단위 한번에 바꾸기 (1) | 2022.10.22 |
| [엑셀 함수] OFFSET 함수의 개념 이해하기 (0) | 2022.10.05 |
| [회사에서 많이 쓰이는 엑셀 함수]#3 IF함수 중첩 (vs VLOOKUP) (0) | 2022.09.24 |
| [엑셀 함수] HLOOKUP 함수 사용 방법 (0) | 2022.09.17 |
댓글