현장에서 유용한 엑셀 2부는
Index 함수와 match함수를 이용해
같은 Sheet 또는 다른 Sheet 혹은 다른 Excel 파일에서 내가 필요한 자료를 가져오는 방법을 알아보자.
Ⅰ. INDEX & Match 함수
Case. 매일매일 변동되는 Raw 데이터를 즉각 반영할 수 있는 보고서를 만들어 보기
상기 이미지와 같이 상품수량 및 가격 현황 Sheet에
매일 변동되는 자료가 담긴 Raw Data를 반영하고 싶다면 Index와 match함수를 통해 해결할 수 있다.
raw 데이터는 상기 이미지와 같이 매일 변동되는 형식으로 전산에서 다운이 가능하다고 가정!
보고서의 밥솥 수량에 Raw데이터의 수량을 불러오기 위해서는 어떻게 함수를 입력해야 할까?
바로 Index와 match함수를 사용하면 된다.
1. Index 함수
index함수는 특정 범위에서 행과 열에 해당하는 셀 값을 구하는 함수이다.
=Index(Array,Row_num,Column_num)형태로 사용되는데,
여기서 Array는 지정할 범위
Row_num은 Array에서 지정한 범위에서의 행 번호
Column_num은 Array에서 지정한 범위에서의 열 번호를 의미한다.
빈 셀에서 =index(A2:D6,1,2)를 입력할 경우 A2:D6 범위의 첫 행 두 번째 열인 B2값이 불러와지게 된다.
2. match 함수
match함수는 특정 범위에서 항목의 상대적 위치를 구하는 함수로
=match(Lookup_value,Lookup_array,Match_type)형태로 사용되는데
여기서 Lookup_value는 표에서 찾고자 하는 값
Lookup_array는 찾고자 하는 값이 포함된 특정 범위
Match_type은 정확한 값을 찾고자 할 경우 0을 입력하면 된다.
따라서 Index와 Match함수를 이용하면
표에서 내가 원하는 자료의 값을 추출할 수 있는 것!
아까 말했듯 보고서의 밥솥 수량에 Raw 데이터의 수량을 불러오기 위해서는 어떻게 함수를 입력해야 하는지 살펴보자
상품 수량 및 가격 현황의 C6셀에
=INDEX(Raw_상품상세!B:B,MATCH('상품수량 및 가격 현황'!B:B,Raw_상품상세!A:A,0))
즉 =index(찾고자 하는 값이 있는 열, match(무엇이 일치해야 하는가?))
다시 살펴보자면 =index(찾고자하는 값이 있는 열인 Raw데이터의 수량 열,match(상품 수량 및 가격현황의 상품명,Raw데이터의 상품명,정확히 일치할 경우))로 해석하면 된다.
해당 함수를 입력하면 상기 이미지처럼
Raw데이터의 수량이 본 보고서인 상품수량 및 가격 현황 Sheet로 불러져 오는 것을 확인할 수 있다!
+) Office 365 사용자의 경우 #spill오류가 발생할 수 있는데 이때는
=INDEX(Raw_상품상세!B:B,MATCH(@'상품수량 및 가격 현황'!B:B,Raw_상품상세!A:A,0))
match함수 이후에 @를 달아주면 오류가 해결된다.
수량을 불러온 것과 같이 가격, 원가, MOQ 역시 Raw 데이터에서
Index와 match함수를 이용하면 쉽게 불러올 수 있다.
index와 match함수는 실무에서 굉장히 유용하게 쓸 수 있는 함수이다.
엑셀에 어려움을 겪고 있다면!
참고해서 잘 활용해보자 :)
현장에서 유용한 엑셀 시리즈
'정보 > OA' 카테고리의 다른 글
| [현장에서 유용한 엑셀] 3부 공통 데이터 추출 위해 Left, Right,Mid 함수 사용하기 (0) | 2021.12.23 |
|---|---|
| [현장에서 유용한 Excel] 1화 엑셀 단축키 설정하기 (0) | 2021.11.25 |
댓글