다른 글에서 여러 조건을 만족하는 데이터를 출력하기 위해 조건에 해당하는 열들을 '&'로 묶어서 VLOOKUP 함수를 활용하는 방법을 소개한 적 있습니다. 하지만 이 방법은 조건이 많아지거나 조회하고 싶은 데이터가 많아질수록 활용하기 번거러워지는데요. 하지만 파워쿼리를 활용하면 간편하게 여러 조건에 맞는 데이터를 출력할 수 있습니다.
[엑셀 함수] VLOOKUP 사용법 #2 MATCH와 조합, INDEX-MATCH 함수, VLOOKUP으로 다중조건 검색하기
VLOOKUP 함수의 여러 사용법과 용도 #1에 이어 VLOOKUP 함수와 연관된 사용법들을 소개해 보겠습니다. ④ VLOOKUP과 MATCH 함수 조합해서 활용하기 ⑤ INDEX 함수와 MATCH 함수 조합해서 활용하기 ⑥ VLOOKUP
writingpjt.tistory.com
이번 글에서는 한국거래소 정보데이터시스템(http://data.krx.co.kr/)에서 다운로드 받은 후 가공한 2022/7/1~2022/8/2 기간 삼성SDI 및 현대로템 주식 시세 정보를 활용해 보겠습니다.
검색조건을 표(테이블)로 만든 후 쿼리 생성하기
1) 검색조건을 표로 만들기
▲첨부된 엑셀 파일을 보시면, 첫 번째 시트에 삼성SDI와 현대로템 주식 시세추이 로데이터와 검색조건을 기입한 셀들이 있습니다. 어떤 종목의 특정 일자 종가를 알아보고 싶은 상황을 가정해 봅시다.
여기서 특이한 점은 검색조건 열들의 이름은 '날짜'와 '종목'으로, 원본 데이터의 '종목'과 '일자'와 순서와 열 이름('일자' ≠날짜)이 다릅니다. 하지만 상관 없습니다. 파워쿼리로 다중조건 검색을 할 때에는 비교적 열 순서와 이름에서 자유롭습니다.
파워쿼리를 활용하기 위해 먼저 로데이터와 검색조건을 단축키 Ctrl+T를 활용하여 표(테이블)로 변환하겠습니다. 그리고 각 표의 이름은 상단의 '테이블 디자인 탭'에서 '로데이터'와 '검색조건'으로 만들겠습니다.
2) 쿼리 생성하기
▲'로데이터' 표의 아무 셀을 선택한 상태에서 '데이터 탭' → '테이블/범위에서' 버튼을 클릭하면 '로데이터'라는 이름의 쿼리가 생성됩니다.
저희가 필요한 건 '검색조건'에 해당되는 데이터이므로, '로데이터' 쿼리는 좌측상단의 '닫기 및 로드'에서 '연결만 생성하기'를 선택해봅시다. 파워쿼리에 대한 이전 글에서 설명 드렸듯이, 연결 전용 쿼리는 시트에 출력되지 않고 다른 쿼리를 가공하는데 사용됩니다.
[파워쿼리] 한 개의 파일 내 여러 데이터 합치기 (VLOOKUP 상위호환)
VLOOKUP 함수는 데이터를 합칠 때 활용되기도 합니다. VLOOKUP과 IF함수 및 MATCH 함수 조합 혹은 INDEX-MATCH 함수 조합을 활용하면 VLOOKUP만 썼을 때의 한계를 어느 정도 극복할 수 있습니다. (VLOOKUP 사용
writingpjt.tistory.com
쿼리 병합으로 조건에 맞는 데이터 끌고오기
▲이제 검색조건 표로 쿼리를 생성한 후, 원하는 형태의 데이터가 출력되도록 쿼리를 편집할 차례입니다.
먼저 쿼리가 생성되면 상단의 '쿼리 병합' 버튼을 클릭합니다. 그 후 병합 팝업창의 아래의 콤보박스에서 '로데이터'를 클릭합니다.
▲이후 '검색조건'에서 'Ctrl' 키를 누른 채 '날짜'와 '종목'을 순서대로 클릭한 후, '로데이터'에서 'Ctrl' 키를 누른 채 '일자'와 '종목'을 순서대로 클릭해줍니다. 위의 사진을 보면 날짜와 일자 옆에 '1'이, 종목 옆에 '2'가 있는 것을 볼 수 있습니다. 숫자가 같은 열의 데이터끼리 일치 여부를 확인하는 것입니다.
즉, 팝업창 상단에 있는 검색조건 기준으로, 검색조건의 날짜 열과 로데이터의 일자 열이 같은 데이터 중에, 종목까지 같은 데이터를 찾는 것입니다.
조건에 해당되는 열들을 순서대로 클릭 후 '확인'을 누르면 '로데이터'라는 열이 생성됩니다.
▲위의 좌측 사진처럼 '로데이터'열의 오른쪽에 있는 버튼을 클릭하면, '로데이터' 쿼리의 열 목록이 나옵니다. 여기서 필요한 데이터 열을 클릭하고 확인을 누르면 다중조건에 맞는 데이터들이 끌려옵니다(VLOOKUP과 유사). 모든 열을 선택할 수도 있지만, '종가'열만 끌고오도록 하겠습니다.
위의 우측 사진을 보면 종가에 'null'값이 있는 행이 2개 있습니다. 하나는 일자가 '20220920'이며, 다른 하나는 종목명이 '삼성전자'입니다. 즉, 원본데이터에서 조회할 수 없는 조건은 'null'(엑셀 시트에서는 빈 칸)으로 표현됩니다.
검색조건 결과 출력하기
▲'검색조건' 쿼리를 선택한 상태에서 '닫기 및 로드'를 클릭하면 자동으로 새로운 시트에 쿼리가 출력됩니다.
▲검색조건 내 데이터를 바꾸고 '데이터 탭' → '모두 새로고침'을 누르면, 출력표가 자동적으로 업데이트 됩니다.
관련 글
[파워쿼리- 쿼리 생성하는 5가지 방법] #1 테이블/범위에서 가져오기
[엑셀 함수] VLOOKUP 사용법 #1 함수 개념, 일치옵션, IF와 조합
[엑셀 함수] VLOOKUP 사용법 #2 MATCH와 조합, INDEX-MATCH 함수, VLOOKUP으로 다중조건 검색하기
[파워쿼리] 한 개의 파일 내 여러 데이터 합치기 (VLOOKUP 상위호환)
'엑셀, VBA, 파워쿼리 > 파워쿼리' 카테고리의 다른 글
| [파워쿼리] 쿼리추가로 여러 데이터를 하나의 표로 합치기 (2) | 2023.10.12 |
|---|---|
| [파워쿼리] 한 개의 파일 내 여러 데이터 합치기 (VLOOKUP 상위호환) (1) | 2023.10.12 |
| [파워쿼리- 쿼리 생성하는 5가지 방법] #5 빈 쿼리로 쿼리 생성하기 (3) | 2023.10.07 |
| [파워쿼리- 쿼리 생성하는 5가지 방법] #4 폴더에서 가져오기 (여러 파일 취합하기) (1) | 2023.10.07 |
| [파워쿼리- 쿼리 생성하는 5가지 방법] #3 텍스트/CSV에서 가져오기 (1) | 2023.10.07 |
댓글