Google 스프레드시트에서 데이터를 조작해야 하는 경우 QUERY 기능이 도움이 될 수 있습니다! 강력한 데이터베이스 스타일 검색을 스프레드시트에 제공하므로 원하는 형식으로 데이터를 조회하고 필터링할 수 있습니다. 사용 방법을 안내해 드리겠습니다.
목차
QUERY 함수 사용
SQL을 사용하여 데이터베이스와 상호 작용한 적이 있다면 QUERY 함수를 마스터하는 것이 그리 어렵지 않습니다. 일반적인 QUERY 함수의 형식은 SQL과 유사하며 Google 스프레드시트에 데이터베이스 검색 기능을 제공합니다.
QUERY 함수를 사용하는 수식의 형식은 =QUERY(data, query, headers)입니다. “data”를 셀 범위(예: “A2:D12” 또는 “A:D”)로 바꾸고 “query”를 검색어로 바꿉니다.
선택적 “headers” 인수는 데이터 범위의 맨 위에 포함할 헤더 행 수를 설정합니다. A1의 “First”와 A2의 “Name”과 같이 두 개의 셀에 걸쳐 있는 헤더가 있는 경우 QUERY가 처음 두 행의 내용을 결합된 헤더로 사용하도록 지정합니다.
아래 예에서 Google 스프레드시트의 시트(‘직원 목록’이라고 함)에는 직원 목록이 포함되어 있습니다. 여기에는 이름, 직원 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, “A, B, C, E 선택 WHERE E = ‘아니요'”)입니다. 이 수식은 A1 셀의 초기 “Employees” 제목을 무시합니다.
교육에 참석하지 않은 11번째 직원을 초기 목록에 추가하면(Christine Smith) 아래와 같이 QUERY 공식도 업데이트되어 새 직원을 표시합니다.
고급 QUERY 수식
QUERY 기능은 다양합니다. 검색의 일부로 다른 논리 연산(AND 및 OR 등) 또는 Google 기능(COUNT 등)을 사용할 수 있습니다. 비교 연산자(보다 큼, 보다 작음 등)를 사용하여 두 숫자 사이의 값을 찾을 수도 있습니다.
QUERY와 함께 비교 연산자 사용
QUERY를 비교 연산자(보다 작음, 보다 큼 또는 같음)와 함께 사용하여 데이터 범위를 좁히고 필터링할 수 있습니다. 이를 위해 각 직원이 수상한 상 수와 함께 “직원 목록” 시트에 열(F)을 추가합니다.
QUERY를 사용하여 하나 이상의 상을 수상한 모든 직원을 검색할 수 있습니다. 이 수식의 형식은 =QUERY(‘직원 목록’!A2:F12, “SELECT A, B, C, D, E, F WHERE F > 0”)입니다.
이것은 열 F에서 0보다 큰 값을 검색하기 위해 보다 큼 비교 연산자(>)를 사용합니다.
위의 예는 QUERY 함수가 하나 이상의 상을 수상한 8명의 직원 목록을 반환했음을 보여줍니다. 전체 직원 11명 중 3명이 수상한 적이 없습니다.
QUERY와 함께 AND 및 OR 사용
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년생 3명의 직원이 이 요건을 충족합니다.
OR을 사용하여 유사한 결과를 생성할 수도 있습니다. 동일한 데이터를 사용하지만 날짜를 변경하고 OR을 사용하면 1980년대에 태어난 모든 직원을 제외할 수 있습니다.
이 수식의 형식은 =QUERY(‘직원 목록’!A2:E12, “SELECT A, B, C, D, E WHERE D >= DATE ‘1989-12-31’ 또는 D
원래 10명의 직원 중 3명은 1980년대에 태어났습니다. 위의 예는 제외된 날짜 이전 또는 이후에 태어난 나머지 7명을 보여줍니다.
QUERY와 함께 COUNT 사용
단순히 데이터를 검색하고 반환하는 대신 QUERY를 COUNT와 같은 다른 함수와 혼합하여 데이터를 조작할 수도 있습니다. 의무 교육 세션에 참석했거나 참석하지 않은 목록의 모든 직원을 지우고 싶다고 가정해 보겠습니다.
이렇게 하려면 다음과 같이 QUERY를 COUNT와 결합할 수 있습니다. =QUERY(‘Staff List’!A2:E12, “SELECT E, COUNT(E) group by E”).
E 열(“교육 참석”)에 초점을 맞춘 QUERY 함수는 COUNT를 사용하여 각 값 유형(“예” 또는 “아니오” 텍스트 문자열)이 발견된 횟수를 계산했습니다. 우리 목록에서 6명의 직원이 교육을 이수했고 4명은 아직 교육을 이수하지 않았습니다.
이 수식을 쉽게 변경하고 SUM과 같은 다른 유형의 Google 함수와 함께 사용할 수 있습니다.