- 구글 스프레드시트에서 다중 조건으로 값을 찾는 방법에 관한 글입니다.
- 여러 방법이 있겠지만, 저는 arrayformula와 vlookup을 이용하여 이 문제를 해결했습니다.
문제
- vlookup을 쓰다보면, 조건을 하나가 아닌 여러 컬럼으로 조건을 걸어야 할 때가 있다.
- 하지만, vlookup 하나만으로는 이 문제를 해결하기 쉽지 않다.
해결 방법
- 아래와 같은 방법으로 vlookup에 다중 조건을 걸 수 있다. 복잡해 보이지만, 막상 하면 어렵지 않다.
- ARRAYFORMULA(VLOOKUP(찾는 기준이 되는 키1&찾는 기준이 되는 키2, {검색 대상 테이블의 키1 전체&검색 대상 테이블의 키2 전체, 검색 대상 테이블에서 갖고 오고 싶은 열}, 2, 0))
- 아래의 스크린샷은 연도와 월을 기준으로 성수기 여부 열을 가져오는 예제다.
- 왼쪽 테이블엔 연도, 월, 사용자수가 있었고, 오른쪽 테이블엔 연도, 월, 성수기 여부가 있었다.
- 양쪽 테이블에 연도와 월 열이 있으므로 이를 조합해 키로 삼고, 성수기 여부 열을 가져와서 왼쪽 테이블에 병합했다.
- 여기서 주의할 점은 아래와 같다.
- arrayformula가 vlookup을 감싸고 있는 형태여야 한다.
- vlookup의 두번째 파라미터인 '범위'는 중괄호 형태여야 한다.
- 중괄호로 묶은 범위는 쉼표를 기준으로 컬럼을 구분한다.
참고 문서
- [공식 문서] ARRAYFORMULA
- [공식 문서] VLOOKUP
공유하기
게시글 관리
구독하기삽질의 기록이전 포스팅에서 검색함수에 대해 계속해서 설명을 해 왔었는데, 이번 포스팅에는 그동안 나왔었던 검색함수에 대해 간단한 사용 예제를 가지고 설명을 하고자 한다. 설명은 구글 스프레드시트를 기준으로 진행한다. 엑셀도 버전에 따라 사용할 수 있는 함수가 있는 반면에 아직까지 구글 스프레드시트에서만 지원하는 함수가 있으므로 참고하기 바란다.
설명은 앞선 포스팅에서 사용했던 구매제품 리스트 예제와 함께, 같이 설명할 검색함수는 다음과 같다.
1. VLOOKUP
2. INDEX(MATCH)
3. DGET
4. FILTER
5. QUERY
엑셀도 버전이 바뀔수록 구글시트에서 지원하는 함수를 적용하는듯…
이 포스트 작성시점 기준으로, EXCEL에서 DGET과 FILTER함수를 지원하는 버전을 확인해 보니 아래와 같다.
DGET함수 사용가능 EXCEL 버전 = OFFICE 365, EXCEL 2016,2019 (구글시트의 DGET과 조금 차이있음)
FILTER함수 사용가능 EXCEL 버전 = OFFICE 365
QUERY는 EXCEL 2007부터 있긴 했지만 고급 사용자용으로 구글시트QUERY와 차이가 있음.
참고로 최근에 엑셀에서 VLOOKUP에서 확장된 XLOOKUP함수가 릴리즈 되었으나, 사용측면에 있어서는 FILTER와 QUERY함수의 활용범위가 넓어 XLOOKUP함수는 설명에서 제외하였다.
반응형
구글 스프레드 시트에서 QUERY 함수를 사용하여 목록을 가져오고 싶은 경우가 있습니다.
항목을 가져올 때 지정한 범위의 항목 전체를 가져오는 경우와 원하는 항목만 가져와야 하는 경우가 있습니다.
SELECT를 사용하여 전체 가져오는 방법과 원하는 일부 항목만 가져오는 방법을 보도록 하겠습니다.
항목 전체 가져오기
=QUERY(범위, 쿼리, 헤더)
범위 - 쿼리를 검색할 범위
쿼리 - 검색 조건
헤더 - 첫 번째 행 헤더를 표시하고 싶은 경우 TRUE. 표시하고 싶지 않은 경우 FALSE
QUERY 함수를 사용하여 전체 항목을 가져오는 방법을 보도록 하겠습니다.
쿼리에는 간단한 조건을 하나 작성하도록 하겠습니다.
사용 수식
=QUERY(A1:D10, "SELECT * WHERE B='신발'", TRUE)
B열인 판매 목록에서 데이터가 신발인 데이터를 취득해오고 있습니다.
항목은 지정 범위인 A열부터 D열까지 모두 표시하고 있습니다.
지정한 범위를 모두 표시하는 이유는 SELECT * 를 설정하였기 때문입니다.
SELECT * 를 생략해서 쿼리를 작성해보겠습니다.
사용 수식
=QUERY(A1:D10, "WHERE B='신발'", TRUE)
SELECT * 를 생략해도 지정한 범위의 항목을 모두 표시합니다.
생략한 경우와 SELECT * 를 붙인 경우 모두 전체 항목을 표시합니다.
일부 항목만 표시
지정한 범위에서 원하는 항목만 표시하는 방법을 보도록 하겠습니다.
사용 수식
=QUERY(A1:D10, "SELECT A WHERE B='신발'", TRUE)
SELECT 뒤에 A를 지정하였습니다.
QUERY 결과에는 A열만 표시되고 있습니다.
이번에는 여러개 지정해서 결과를 보도록 하겠습니다.
사용 수식
=QUERY(A1:D10, "SELECT A,D WHERE B='신발'", TRUE)
SELECT에 A열과 D열을 지정했습니다.
결과도 A열과 D열 항목만 표시되고 있습니다.
표시하고 싶은 항목이 여러 개일 경우에는 ,(콤마)를 사용하여 지정하면 됩니다.
정리
구글 스프레드 시트에서 SELECT를 사용하여 지정 범위의 전체 항목을 표시하는 방법과 원하는 일부 항목만 표시하는 방법을 봤습니다.
전체 표시하고 싶은 경우에는 SELECT * 또는 생략을 하면 됩니다.
원하는 항목만 표시하고 싶은 경우에는 SELECT 뒤에 표시하고 싶은 열을 설정합니다.
그리고 여러개 표시하고 싶은 경우에는 ,(콤마)를 사용하여 지정합니다.
반응형
공유하기
게시글 관리
구독하기구글오피스 정복하기'Google 스프레드시트' 카테고리의 다른 글
구글 스프레드시트 GROUP BY 사용 방법 예제 (0)2020.06.02구글 스프레드시트 부분 일치 검색 LIKE 사용 방법 예제 (2)2020.06.01구글 스프레드시트 중복 제거 UNIQUE 사용 방법 (0)2020.06.01구글 스프레드시트 쿼리 정렬 방법 예제 (0)2020.06.01구글 스프레드시트 검색 쿼리 QUERY() 함수 사용 방법 (0)2020.06.01원본 정보가 아래와 같이 참가자, 테이블번호, 음식타입으로 저장된 상태입니다. 이 상태에서 테이블 번호가 1보다 큰 테이블 중에서 음식타입이 Vegetarian인 정보만 필터링하여 새로운 데이터를 만들어 보겠습니다.
filter 함수를 이용하기 위해 원본 데이터의 전체 범위를 [범위]에 전달합니다. 원본 데이터의 범위는 A2:C6 입니다.
범위조건1조건2A2:C6[테이블번호]가 2번인 데이터와 [음식타입]이 "Vegetarian"인 데이터만 필터링하여 사용이 필요합니다. 조건1과 조건2를 추가합니다.
범위조건1조건2A2:C6B2:B6 > 1조건1과 조건2는 column 또는 row로 서로 동일하여야 동작이 가능합니다. 조건1에 의해서 아래와 같이 colume B를 조건1을 만족하는 row 데이터를 필터링 합니다.
조건1만을 사용하여 원복데이터를 필터링 하는 경우 경우 1보다 큰 값을 가진 Jack, Nancy, Rob의 데이터가 아래와 같이 조건1에 의해서 필터링 됩니다.
여기에 다시 음식타입이 "Vegetarian" 경우를 설정하는 조건2를 적용합니다. 이제 2개의 조건이 모두 true인 데이터만 필터링 하게 됩니다.
최종 필터링된 결과를 출력하기 위한 위치인 E2위치에 조건2를 추가하여 함수를 적용합니다. E2에서부터 G2까지 조건1과 조건2를 만족하는 데이터가 필터링되어 출력됩니다. 모든 조건을 만족하는 "Rob"의 데이터만 출력되었습니다.