엑셀 | 표에서 (x, y) 좌표로 값 찾기 offset 활용 및 응용

1. 개요

2. OFFSET 사용법

3. OFFSET 응용 MATCH 함수 등

 

 

1. 개요

선택한 셀의 값을 불러오고 싶으면 어떻게 해야할까요? VLOOKUP? HLOOKUP? 모두 좋은 방법입니다.

HLOOKUP 보다는 VLOOKUP 이 쉽겠죠.

HLOOKUP을 한다면, 아래와 같이 합니다.

VLOOKUP(B15,Table3[#All],C15+1,0)

하지만 이마저도 "항목", "월"을 계속 변경해야합니다.

만약, 표에서 매출이 제일 많은 항목은 어떻게 추출할까요? 편법으로 VLOOKUP을 사용해도 됩니다만, 오늘은 OFFSET 을 알아봅시다.

 

 

2. OFFSET 사용법

OFFSET(Table3[[#Headers],[매출]],5,4,1,1)

OFFSET( "기준점", "세로[행, rows] 칸 수", "가로[열, columns] 칸 수", 1, 1)

 

(마지막 1,1 은 기본값이기에 생략해도 무방합니다.)

 

컴퓨터는 0,1,2,3,4... 같이 숫자를 세기 때문에, 기준점의 좌표는 (0, 0)입니다.

따라서, 세로로 5칸, 가로로 4칸을 가면 해당 값이 나옵니다.

 

마지막 2개의 파라미터 값의 사용법은 아래와 같습니다.

 

OFFSET(Table3[[#Headers],[매출]],5,4,2,1)

OFFSET( "기준점", "세로[행, rows] 칸 수", "가로[열, columns] 칸 수",  "찾아올 값부터 세로 칸 수", "찾아올 값부터 가로 칸 수")

세로로 2칸, 가로로 1칸만큼 값을 통째로 가져옵니다. (아래칸은 [OFFSET( "기준점", "세로[행, rows] 칸 수" + 1, "가로[열, columns] 칸 수", 1, 1)]로 봐도 무방할 듯지도 모르겠네요.)

 

 

 

 

3. OFFSET 응용 MATCH 함수 등

처음으로 돌아와서 매출이 제일 많은 항목은 무엇일까요?

매출이 제일 많은 월에서, 연간 매출이 제일 많은 항목은 얼마를 팔았을까요? (ex, 8월에 매출이 제일 많다면, 8월에 1년 동안 제일 많은 금액을 판 딸기는 얼마나 팔았을까?)

MATCH 함수와 함께 사용하면 엄청 좋아집니다.

MATCH(MAX(Table3[[#Totals],[1]:[12]]),Table3[[#Totals],[1]:[12]],0)
MATCH(MAX(Table3[Total]),Table3[Total],0)

OFFSET(Table3[[#Headers],[매출]],E14,E13)

# 따라서

OFFSET(Table3[[#Headers],[매출]],
       MATCH(MAX(Table3[Total]),Table3[Total],0),
       MATCH(MAX(Table3[[#Totals],[1]:[12]]),Table3[[#Totals],[1]:[12]],0)
       )

조금 길 긴하지만, 차근차근하면 이해할 수 있습니다. MAX 함수 대신 LARGE를 사용해도 됩니다.

 

회색셀의 값(8월의 딸기 매출)을 정확하게 불러온 것을 확인할 수 있습니다.

 

 

(참고자료)

엑셀 | 목록 중에서 해당 값은 몇 번째에 있을까? MATCH🔗