구글 스프레드시트에서 자료를 다뤄야 할 때, QUERY 기능은 매우 유용합니다! 이 기능은 스프레드시트 내에서 데이터베이스와 유사한 강력한 검색 기능을 제공하여, 사용자가 원하는 방식으로 데이터를 추출하고 걸러낼 수 있도록 도와줍니다. 이제 이 기능을 어떻게 활용하는지 자세히 알아보겠습니다.
QUERY 함수 활용법
만약 SQL을 사용하여 데이터베이스를 조작해 본 경험이 있다면, QUERY 함수를 익히는 것은 그리 어렵지 않을 것입니다. QUERY 함수의 기본 구조는 SQL과 유사하며, 구글 스프레드시트 내에서 데이터베이스 검색을 수행할 수 있도록 합니다.
QUERY 함수를 사용하는 수식의 형식은 다음과 같습니다:
=QUERY(data, query, headers)
. 여기서 “data”는 자료 범위(예: “A2:D12” 또는 “A:D”)로 대체되고, “query”는 검색어 입력란에 해당합니다.
선택 사항인 “headers” 인수는 데이터 범위 상단에 포함할 헤더 행의 수를 지정합니다. 만약 A1 셀에 “First”, A2 셀에 “Name”과 같이 두 셀에 걸쳐 있는 헤더가 있을 경우, QUERY 함수는 처음 두 행의 내용을 통합된 헤더로 간주하도록 설정할 수 있습니다.
다음 예시에서는 ‘직원 목록’이라는 이름의 구글 스프레드시트 시트에 직원 목록이 있다고 가정합니다. 이 목록에는 직원의 이름, 사원 ID, 생년월일, 그리고 필수 교육 참석 여부 등이 포함되어 있습니다.
이제 두 번째 시트에서 QUERY 수식을 사용하여 필수 교육 세션에 참여하지 않은 모든 직원의 목록을 추출해 보겠습니다. 이 목록에는 사원 ID, 이름, 성, 그리고 교육 참석 여부가 표시됩니다.
위의 데이터에서 이를 수행하기 위해
=QUERY('직원 목록'!A2:E12, "SELECT A, B, C, E WHERE E = 'No'")
를 입력할 수 있습니다. 이 수식은 ‘직원 목록’ 시트의 A2부터 E12 범위의 데이터를 검색합니다.
일반적인 SQL 쿼리와 마찬가지로, QUERY 함수는 표시할 열을 선택하고(SELECT) 검색 기준을 설정합니다(WHERE). 이 수식은 A, B, C, E 열을 결과로 반환하며, E 열의 값(“수강 교육”)이 ‘아니요’라는 텍스트와 일치하는 모든 행을 보여줍니다.
위에서 볼 수 있듯이, 원래 목록에 있는 직원 중 4명이 교육에 참여하지 않았습니다. QUERY 함수는 해당 정보를 기반으로 직원 이름과 사원 ID를 별도의 목록에 표시합니다.
이 예시에서는 특정 데이터 범위를 사용했습니다. 이를 수정하여 A열부터 E열까지 전체 데이터를 검색할 수 있습니다. 이렇게 하면 새로운 직원이 추가되어도 목록이 계속 업데이트됩니다. 사용된 QUERY 수식은 새로운 직원이 추가되거나 누군가가 교육에 참여할 때마다 자동으로 업데이트됩니다.
이러한 용도로 적합한 수식은
=QUERY('직원 목록'!A2:E, "SELECT A, B, C, E WHERE E = '아니요'")
입니다. 이 수식은 A1 셀에 있는 초기 “Employees” 제목을 무시합니다.
만약 초기 목록에 교육을 받지 않은 11번째 직원(Christine Smith)을 추가하면, QUERY 수식도 업데이트되어 새로운 직원을 반영합니다.
QUERY 고급 활용법
QUERY 기능은 매우 다재다능합니다. 검색 조건으로 다양한 논리 연산(AND, OR 등)이나 구글 시트 함수(COUNT 등)를 사용할 수 있습니다. 또한, 비교 연산자(크다, 작다 등)를 활용하여 특정 범위의 값을 찾을 수도 있습니다.
비교 연산자를 활용한 QUERY
QUERY 함수와 비교 연산자(작다, 크다, 같다)를 함께 사용하면 데이터 범위를 세밀하게 조정하고 걸러낼 수 있습니다. 예를 들어, ‘직원 목록’ 시트에 각 직원이 받은 상의 횟수를 나타내는 열(F)을 추가했다고 가정합니다.
QUERY 함수를 사용하여 하나 이상의 상을 받은 모든 직원을 검색할 수 있습니다. 이를 위한 수식은
=QUERY('직원 목록'!A2:F12, "SELECT A, B, C, D, E, F WHERE F > 0")
입니다.
이 수식은 F 열에서 0보다 큰 값을 검색하기 위해 ‘보다 큼’ 비교 연산자(>)를 사용합니다.
위 예시는 QUERY 함수가 하나 이상의 상을 받은 8명의 직원 목록을 출력했음을 보여줍니다. 전체 직원 11명 중 3명은 상을 받은 적이 없습니다.
AND 및 OR 연산자를 활용한 QUERY
AND 및 OR과 같은 중첩된 논리 연산 함수는 더 복잡한 QUERY 수식 내에서 유용하게 사용되어, 수식에 여러 검색 조건을 추가할 수 있게 해줍니다.
AND 연산자를 테스트하기 좋은 예시는 특정 기간 내의 데이터를 검색하는 것입니다. 직원 목록 예시를 사용하여, 1980년에서 1989년 사이에 태어난 모든 직원을 나열해 보겠습니다.
이를 위해 크거나 같음(>=) 및 작거나 같음(<=) 연산자도 사용해야 합니다.
수식은 다음과 같습니다:
=QUERY('직원 목록'!A2:E12, "SELECT A, B, C, D, E WHERE D >= DATE '1980-1-1' and D
위의 결과에서 1980년, 1986년, 1983년에 태어난 세 명의 직원이 이 조건을 충족하는 것을 확인할 수 있습니다.
OR 연산자를 사용해도 비슷한 결과를 얻을 수 있습니다. 동일한 데이터를 사용하되, 날짜를 변경하고 OR 연산자를 사용하면 1980년대에 태어난 직원을 제외한 모든 직원을 찾을 수 있습니다.
이를 위한 수식은 다음과 같습니다:
=QUERY('직원 목록'!A2:E12, "SELECT A, B, C, D, E WHERE D >= DATE '1989-12-31' or D
원래 직원 10명 중 3명이 1980년대에 태어났습니다. 위 결과는 해당 기간 이전이나 이후에 태어난 나머지 7명을 보여줍니다.
COUNT 함수를 활용한 QUERY
단순히 데이터를 검색하고 반환하는 것 외에도, QUERY 함수를 COUNT와 같은 다른 함수와 결합하여 데이터를 조작할 수 있습니다. 예를 들어, 필수 교육 세션에 참여했거나 참여하지 않은 직원의 수를 파악하고 싶다고 가정해 봅시다.
이를 위해 QUERY와 COUNT 함수를 다음과 같이 결합할 수 있습니다:
=QUERY('Staff List'!A2:E12, "SELECT E, COUNT(E) group by E")
.
E 열(“교육 참석 여부”)에 초점을 맞춘 QUERY 함수는 COUNT 함수를 사용하여 각 값 유형(“예” 또는 “아니오” 문자열)이 발견된 횟수를 계산합니다. 결과적으로, 우리 목록에서 6명의 직원이 교육을 이수했고 4명은 아직 이수하지 않았다는 것을 알 수 있습니다.
이 수식을 쉽게 수정하여 SUM과 같은 다른 구글 시트 함수와 함께 사용할 수도 있습니다.