[엑셀가이드 - 초보] 엑셀에서 여러 조건을 만족하는 값 찾기

VLookup을 사용하다 보면 항상 아쉬울 순간이 발생한다.

내가 원하는건 "21년 6월 5일"의 "제주"의 신규 확진자 수인데, Vlookup을 사용해서 할 수 없을까?

바로 이렇게 다중조건을 적용하고 싶은 순간이다.

우선 결론만 말하자면 할 수는 있다.

오늘은 3가지 방법들을 알아보고 이 중에 "쉬운" 방법만 사용해 보자.

---

요약

1. index-match 함수 활용하기 (여기서 설명하지 않음)

2. 데이터 열 추가해서 VLOOKUP사용하기 (초보 추천)

3. 피벗테이블 형태로 가공해서 GETPIVOTDATA 함수 사용하기 (데이터가 피벗테이블 형태로 되어 있다면 추천)

---

오늘의 예제문서

예제에서 활용한 6월 5일 ~ 6월 8일의 누적 확진자 수 데이터다.

오늘의 목표는  조건1, 조건2 를 만족하는 "신규확진자" 수 알아내기다.
조건1 : 6월 5일
조건2 : 제주

다중조건_예제 .xlsx

0.03MB

---

1. index-match 함수를 이용하는 방법

검색해보면 많이들 사용하라고 알려주는 함수다.

Index라는 함수를 이용해서 가상의 배열을 만들고 해당 배열에서 match를 통해 몇번째 열인지 찾아낸 후 다시 index함수를 사용해서 값을 찾아내는 함수다.
예시는 작성하지만, 이 블로그에서는 자세한 설명하지 않을 예정이다.

index match index 3번에 걸쳐 로직을 활용하기 때문에 함수가 복잡하고 어렵다.

우리 초보들은 좀더 쉬운 방법을 사용하자.

=INDEX(D:D,MATCH(H2&H3,INDEX(A:A&B:B,),0))

=> INDEX(A:A&B:B,) A&B 형태의 1차원 배열을 구성한다.

=> MATCH(H2&H3,INDEX(A:A&B:B,),0) 위에서 만든 1차원 배열에서 우리가 찾는 조건의 데이터가 몇번 째 행에 있는지 찾아준다. => 값 18

=> INDEX(D:D,MATCH(H2&H3,INDEX(A:A&B:B,),0)) D 열의 18번째 행의 값을 찾아준다.

++장점과 단점

장점 : 원본 데이터 가공없이 값을 찾아낼 수 있다.

단점 : 어렵다.

---

2. 열을 추가해서 Vlookup으로 검색하기

개인적으로 가장 많이 사용하는 방법이다.

데이터를 한줄 추가 해야 하지만, 직관적이어서 자주 사용하는 방법이다.

Vlookup의 작동원리는 심플하다, 테이블의 첫 열에서 원하는 데이터가 있는 행을 찾고, 해당 행의 지정된 열의 값을 반환한다.

그럼 조건1조건2를 검색할수 있는 형태로 만든 후 검색을 하면 되지 않을까?

이런 사고의 흐름에서 활용하게 되는 방법이다.

1. 데이터 열 추가 하기

검색을 하기위한 검색용 데이터열을 추가하고 조건1조건2 형태의 데이터로 채워준다.
"&" 는 하나의 셀에 여러 값을 보여주기 위해 사용하는 기능이다.

=B2&C2

이때, 셀 값이 "2021-06-05서울" 이 아닌 "44352서울"과 같이 나와도 당황하지 말자.

2021-06-05 라는 표기는 44352의 날짜 타입의 표기 방식이다.
(참고링크 : https://teamcanaria.tistory.com/43?category=944313 )

[엑셀가이드 - 왕초보] ep6. 엑셀 숫자가 이상하게 보여요 ㅜㅜ

이렇게 함으로써 우리는 이제 "날짜지역" 형태의 데이터 열을 확보했다.

2. vlookup 사용하기

테이블의 첫 열을 "날짜지역" 형태로 만들었기 때문에, 이것을 활용하여 vlookup을 사용해 준다.

=VLOOKUP(I2&I3,$A:$E,5,FALSE)

=> A:E 테이블의 첫번째 열 에서 I2&I3 와 정확히 일치하는 값을 찾아서 5번째 열의 값을 출력하라. 

=> A열에서 "44352제주"와 정확히 일치하는 값이 있다면 5번째 열(E열)의 값을 출력해라

이 글을 보는 사람은 vlookup에 대해서는 알고 있다고 생각하기 때문에 VLOOKUP함수의 자세한 설명은 별도로 하지 않겠다.

혹시라도 필요하면 아래 링크에서 확인하면 된다.
(참고링크 : https://teamcanaria.tistory.com/46?category=944313 )

[엑셀가이드 - 초보] 표에서 특정 조건을 만족하는 값 찾기 VLOOKUP

 

++ 2번 방법을 사용할 때의 주의 사항

위에 주어진 예제의 경우 숫자&문자 형태이기 때문에 데이터가 오류가 나지 않지만 아래와 같이 숫자로만 이뤄진 데이터는 합칠 때 의도치 않게 동일한 숫자로 변경될 여지가 있다.

이런 문제가 발생할 수 있음을 인지하고, 가급적 데이터를 합칠 때, 구분값을 넣어서 합치면 이와같은 문제를 방지할 수 있다. (=A1&"\"&B1)

이때 당연히 vlookup으로 검색할때는 찾는값도 조건1과 조건2 사이에 동일한 문자 "\"를 넣어야 정확히 찾을수 있다.

 

++ 2번 방법의 장점과 단점

장점 : 조건을 2개 3개 4개와 같이 늘리더라도 함수를 짜는 것이 크게 복잡하지 않다

단점 : 검색을 위해 가장 앞열에 데이터열이 1줄이 추가되어야 한다.

---

3. 피벗테이블 활용하기

이 블로그에서 피벗테이블에 대해 아직 설명해 주진 않았지만, 피벗 테이블을 사용해서 데이터가 작성된 경우, 아래와 같이 비교적 직관적인 방법으로 데이터를 찾아낼 수 있다.

피벗테이블에서 값을 찾는것은 어려운 일은 아니지만,
피벗테이블에 대한 이해가 선행되어야 사용할수 있다는 문제가 있다.

여기서는 어떻게 하는지와 예제 함수만 작성해 두었다.

해당 예제에서 피벗테이블에는 "날짜","지역","누적확진자","신규확진자"가 데이터 필드로 추가되어 있는 상태다.

=GETPIVOTDATA(값필드명칭,피벗테이블위치,조건1필드,조건1,조건2필드,조건2)
=GETPIVOTDATA("신규확진자",$I$1,"날짜",44352,"지역","제주")
=> $N$1에 있는 피벗 테이블에서 날짜가 44352(=21년6월5일) 이고, 지역이 제주인 "신규확진자"의 수 값을 찾기

 

++3번 방법의 장점과 단점

장점 : 조건이 여러개일 때, 데이터의 추가 가공 없이 직관적인 방식으로 값을 찾아낼 수 있다.

단점 : 데이터가 피벗테이블 형태로 있지 않으면 피벗 테이블 형태로 가공하는 작업이 선행되어야 한다.