이전 시간에는 index match 함수의 기본으로,
하나의 조건으로 index match 함수를 사용하는 법을 배웠어요.
그럼 이번 시간에는 좀 더 응용해서 여러가지 조건이 있는 경우
index-match 함수의 사용법을 알아보도록 하겠습니다.
혹시 이전에 배운 내용이 필요한 경우 아래 링크를 읽어 보시고 진행해주세요~!
내배내적 엑셀 - index-match 함수 기본 응용하기
내배내적 엑셀 - index-match 함수 기본 응용하기
index와 match 함수에 대해 이해했으면 본격적으로 활용해보겠습니다. 혹시 index, match 함수에 대한 이해가 필요하신 분은 아래 링크를 클릭해서 각각의 함수를 이해하고 이 장으로 넘어와주세요! ��
snow-hiems.tistory.com
여러가지 조건이 있는 index-match 함수 응용하기
우리는 아래와 같이 작성된 표에서
목표 : 2015년에 판매한 복숭아의 판매량과 단가를 구하려고 합니다.
그래서 이전에 배웠던 index-match 함수를 활용해서 구해보도록 하겠습니다.
이전에 배운 단일 조건의 index-match 함수는
예를 들면, 변수가 "복숭아" 하나였습니다.
한개의 열에서 복숭아를 찾고 나면 나머지는 옆으로 몇칸을 갈까?의 문제였죠.
그런데 이번에는 "복숭아"이면서 "2015"년이라는 조건이 하나가 더 붙었어요.
이를 어떻게 해결할 수 있을까요?
바로 연산자 "&"와 "*"를 활용해서 구할 수 있습니다. :)
1. 연산자 "&" 이용
"&"을 연결해서 쓰는 index-match 함수는 정말 쉬워요!
Match의 각 함수인수에 각각의 조건값과 해당하는 열/행을 &로 묶어준다고 생각하면 되요.
아래와 같이 예를 들어볼게요.
복숭아와 2015년이라는 2개의 조건에 대한 index-match 함수를 구하려고 해요.
'복숭아'와 '2015'의 위치 둘 다 행의 위치가 바뀌는 조건이에요.
따라서 Match 함수는 index의 row_num에 들어가야해요.
=index(A1:E22, Match (), )
이제 Match 안에 들어갈 인수들을 고려해볼게요.
어려우면 쉽게 따로따로 생각해요!
먼저, 각각의 조건을 따로따로 분리해보면,
복숭아의 위치에 대한 Match 함수와 연도의 위치에 대한 Match 함수는 다음과 같을 거에요.
■ 복숭아(G10)의 위치 =MATCH(G10,A1:A22,0)
■ 2015년(I10)의 위치 =MATCH(I10,C1:C22,0)
이제 여기서 '복숭아'이고 '2015'년인 Match 함수는 각각의 인수들을 &로 묶기만 하면 돼요!
■ 복숭아(G10)이고 2015(I10)의 위치 =MATCH(G10 & I10,A1:A22 & C1:C22,0)
그리고 나서 판매량의 열 위치는 왼쪽에서부터 4번째니까
우리가 구하고자 하는 2015년 복숭아의 판매량 함수는 다음과 같아요.
■ 2015년(I10) 복숭아(G10)의 판매량 =INDEX($A$1:$E$22,MATCH(G10&I10,A1:A22&C1:C22,0),4)
만약에 I4셀 앞에 있는 조건값을 참고하면 값은 다음과 같습니다.
■ 2015년(H4) 복숭아(G4)의 판매량 =INDEX($A$1:$E$22,MATCH($G4&$H4,$A$1:$A$22&$C$1:$C$22,0),4)
근데 종종 함수를 위와 같이 입력하고 나서 그냥 enter를 누르면
반영이 안되고 #VALUE! 값이 뜨는 경우가 있더라구요.
그럴 때는 Ctrl+Shift+Enter 를 눌러주면 됩니다.
제가 찾은 또하나의 다른 경우는 식도 정상이고 CSE(Ctrl+Shift+Enter )도 눌렀는데 VALUE!가 뜨는 경우가 있더라구요.
그럴 때는 그냥 맨 끝에 4)부분을 지우고 다시 4)적은 다음에 CSE를 누르면 정상 반영이 되었어요.🤔🤔
왜 이런 차이가 발생하는진 모르겠지만
대부분의 아래 를 진행하면 거의 해결되니까 한번 진행해보고 안되면 이야기 해주세요!
1. CSE를 누른다.
2. 끝의 수식을 지우고 다시 작성한 뒤 CSE를 누른다.
2. 연산자 "*"
두번째 방법은 연산자 *를 이용하는 방법입니다.
Match 함수의 모양이 어떻게 되는지 살펴보고, 이렇게 되는 원리를 살펴보겠습니다.
=MATCH(1, (범위1=조건1) * (범위2=조건2) ,0)
맨 앞자리는 항상 1로 고정입니다.
이는 (범위1=조건1) * (범위2=조건2) 과 관련이 있습니다.
엑셀에서는 False=0, True=1로 치환되기 때문이에요.
그래서 만약에 조건이 둘 다 맞는다면 True * True = 1*1이 되서 1이 되겠죠.
따라서 맨 앞자리는 항상 1이 된답니다.
이를 응용하면 조건이 3개이상인 경우에서도 *(범위=조건)을 추가하여
원하는대로 해당 다중 조건에 맞는 값의 열/행을 알 수 있습니다.
따라서 I17셀의 Match 함수는 다음과 같아요.
■2015(H17)년 복숭아(G17)의 위치=MATCH(1,(A1:A22=G17)*(C1:C22=H17),0)
계산해보니 19가 나왔네요! 그래서 그에 따라 19행을 찾아보니 "2015년"이고 "복숭아"인 조건을 만족합니다.
Match 함수를 넣고 나서 Ctrl+Shift+Enter 눌러주는 것 잊지 마세요!
조건을 이제 그대로 index에 넣어주고 Ctrl+Shift+Enter 눌러주면 다음과 같이 됩니다.
■2015(H17)년 복숭아(G17)의 판매량 =INDEX(A1:E22,MATCH(1,(A1:A22=G17)*(C1:C22=H17),0),4)
쨔쟌~! 우와 같이 우리가 원하는 2015년 복숭아의 판매량이 나왔습니다.
이정도면 index-match 함수를 활용한 다중조건 문제는 잘 해결하실거에요!
그럼 다음 포스팅으로 뵐게요 ~!:)
2020/10/12 - [사용법/엑셀(EXCEL)] - 내배내적 엑셀 - index-match 함수 기본 응용하기
2020/10/12 - [사용법/엑셀(EXCEL)] - 내배내적 엑셀 - index 와 match 함수 이해하기
2020/10/09 - [사용법/엑셀(EXCEL)] - 내배내적 엑셀 - Vlookup 다중조건
2020/10/07 - [사용법/엑셀(EXCEL)] - 내배내적 엑셀 - Vlookup(feat. 내가 원하는 값을 쉽게 찾자)
2020/09/24 - [사용법/엑셀(EXCEL)] - 엑셀 빠른 실행도구 어디까지 써봤니?(feat. 빠르게 필터링하자)
2020/09/24 - [사용법/엑셀(EXCEL)] - 내배내적 엑셀 - Subtotal (feat.필터링된 숫자의 합만 구하는 법)
2020/09/09 - [사용법/엑셀(EXCEL)] - 내배내적 엑셀 - Countifs 함수
'사용법 > 엑셀(EXCEL)' 카테고리의 다른 글
| 내배내적 엑셀 - Countif 함수(feat. 조건에 맞는 갯수를 세는 함수) (0) | 2020.10.20 |
|---|---|
| 처음보면 헷갈리는 절대참조와 상대참조 이해하기 (0) | 2020.10.15 |
| 내배내적 엑셀 - index-match 함수 기본 응용하기 (1) | 2020.10.12 |
| 내배내적 엑셀 - index 와 match 함수 이해하기 (0) | 2020.10.12 |
| 내배내적 엑셀 - Vlookup 다중조건 (0) | 2020.10.09 |