티스토리 뷰
■ INDEX, MATCH, MAX 함수
INDEX 함수는 지정된 범위 내에서, 지정된 행, 열 값이 만나는 셀 값을 표시한다.
MATCH 함수는 지정된 범위 내에서, 찾고자 하는 값과 동일하거나, 작거나, 큰 값을 찾은 후 해당 셀의 행 값만 숫자로 가져옵니다.
MAX 함수는 말 그대로 가장 큰 수를 찾아 표시해준다.
■ INDEX 함수
형식 : INDEX(표시할 값이 있는 범위, 행 값, 열 값)
예시 : =INDEX($D$2:$F$11,10, 2)
$D$2:$F$11는 가져올 값이 존재하는 영역을 지정해줍니다.
10은 선택된 영역 중 10번째 줄이란 의미로, 여기선 D2 줄부터 시작하니 11번째 줄이 됩니다.
2 는 선택된 영역 중 2번째 열에서 찾는다는 의미로, 여기선 1번째 E열인 날짜가 해당됩니다.
■ MATCH 함수
형식 : MATCH(찾을 값, 찾을 값 범위, 옵션)
예시 : =MATCH(6,$F$2:$F$11,0)
6은 찾고자 하는 값이 6이란 의미로, 문자를 기입해도 상관없습니다. 그리고 문자를 기입할 경우 옵션을 일치함으로 찾아주셔야 정확하게 됩니다.
$F$2:$F$11 은 찾을 값을 찾을 범위이며, 이 범위에서 6은 F10에 위치해있기 때문에, 결과를 표시할 때, 범위가 아닌 실제 행 값인 10을 가져오게 됩니다.
0 은 옵션 값으로, 1 은 보다 작은 값을, 0은 일치하는 값을, -1은 보다 큰 값의 행 번호를 가져오는데, 가급적 일치하는 값으로 하는 게 좋다.
■ MAX 함수
형식 : MAX(범위)
예시 : =MAX(D2:D11)
D2:D11 사이에 있는 숫자 중 가장 큰 수를 표시합니다.
■ INDEX, MATCH, MAX 함수
예제를 설명드리기 전에, 저는 재고 관리하는 업무와는 관계가 없습니다. 그래서 실무의 내용과 조금 다를 수도 있지만, 가급적 실무에서 이런 느낌으로 사용할 수 있겠구나란 기준으로 작성했으니, 이 점을 참고해서 봐주시면 좋겠습니다.
예제 설명 : 아래 예제는 마지막으로 입고된 날짜 기준으로 입고 수량, 현재 재고 수량을 불러올 수 있는 수식입니다. 많은 자재를 개별로, 입고 수량, 날짜, 재고 수량을 관리하고 있을 경우, SHEET 하나에 모든 자재의 요약 정보를 기록 하고 싶을때 사용할 수 있을 것 같습니다.
아래 예시를 실행하기 위해 현 재고 수량엔 =SUM($G$2:G11) - SUM($H$2:H11) 수식이 대입되어 있고, 이 수식은 입고 수량과 출고 수량에 대한 누적값을 표시하기 위한 수식입니다.
그리고 마지막 확인을 위한 카운트 셀엔 =IF(NOT(ISBLANK(G11)), COUNT($G$2:G11), "미입고") 수식이 대입되어 있는데, 이 수식은 1 달 치 날짜가 미리 기입되어 있다고 가정할 시, 마지막 입고 수량을 날짜가 아닌 입고 횟수로 카운트하기 위한 수식입니다. 예로 첫 번째 입고된 경우 1이 표시될 것이고, 30일 동안 10번의 입고가 있었다면, 10으로 표시됩니다. 그리고 입고되지 않은 경우 오류가 나지 않도록, 미입고가 표시되도록 했습니다.
예시 : =INDEX($E$2:$G$11,MATCH(MAX($E$2:$E$11),$E$2:$E$11,0),2)
결과 : 입고 일자는 2018-10-09, 입고 수량은 6, 마지막 현 재고 수량은 6을 표시합니다.
MAX($E$2:$E$11)는 E열의 가장 큰 값을 가져오며, 아래 예제에선 4를 가져옵니다
MATCH(MAX(4),$E$2:$E$11,0)는 E2:E11 범위에서 4와 동일한 값(옵션 0)인 E10번 열을 찾게 되며, 실제 주소의 행 값을 가져오기 때문에, 10을 가져옵니다.
INDEX($E$2:$G$11,MATCH(10),2)는 E2:G11 영역에서 아래로 10번째 행, 우측으로 2번째 열의 값을 가져오는데, E2:G11 범위 내에서 가져오기 때문에 F11 값을 가져옵니다.
■ INDEX와 MATCH를 포함한 많은 함수를 포함하고 있는 관련포스팅
아래 자재관리 포스팅은 #1 ~ #4 까지로 구성된 포스팅이며, 처음 접하시는 분들은 난이도가 있을 수도 있습니다. 하지만 INDEX와 MATCH를 위와 비슷하게 활용한 예제가 존재함으로, 한번 쯤 읽어보시는 것도 좋으실 것 같습니다.
'엑셀 응용' 카테고리의 다른 글
| 엑셀 교육, 납품 수량 관리 시트 만들기#1 (시트 기능 설명) (0) | 2019.03.18 |
|---|---|
| 자재 관리 요약 표지 만들기#4 (INDIRECT, ADDRESS, IFERROR 엑셀 함수 활용) (0) | 2019.01.09 |
| 자재 관리 요약 표지 만들기#3 (IFERROR, INDEX, MATCH 엑셀 함수 활용) (0) | 2019.01.08 |
| 엑셀로 자재 실재고 및 예상 재고 수량 계산하기#1 (ADDRESS, MATCH, TODAY) (0) | 2019.01.07 |
| 엑셀로 자재 실재고 및 예상 재고 수량 계산하기#2 (IF, SUM, INDIRECT 함수 활용) (0) | 2019.01.06 |
| 엑셀에서 조건에 맞는 셀 또는 줄 전체에 서식 지정하기 (0) | 2019.01.03 |
| 엑셀에서 중복 값 입력 안되도록 하기(데이터 유효성 검사) (0) | 2019.01.01 |
| 엑셀에서 대,중,소분류 목록 만들기 (이름 정의, 데이터 유효성 검사 활용) (0) | 2018.12.31 |