Google 스프레드 시트에서 데이터를 조작해야하는 경우 QUERY 기능이 도움이 될 수 있습니다. 강력한 데이터베이스 스타일 검색 기능을 스프레드 시트에 제공하므로 원하는 형식으로 데이터를 조회하고 필터링 할 수 있습니다. 사용법을 알려 드리겠습니다.
QUERY 함수 사용
QUERY 함수는 SQL을 사용하여 데이터베이스와 상호 작용 한 경우에도 마스터하기가 어렵지 않습니다. 일반적인 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('Staff List'!A2:E12, "SELECT A, B, C, E WHERE E = 'No'"). “직원 목록”시트에서 A2에서 E12 범위의 데이터를 쿼리합니다.
일반적인 SQL 쿼리와 같이 QUERY 함수는 표시 할 열을 선택 (SELECT)하고 검색 매개 변수 (WHERE)를 식별합니다. 열 A, B, C 및 E를 반환하고 열 E의 값 ( “Attended Training”)이 “No”를 포함하는 텍스트 문자열 인 일치하는 모든 행의 목록을 제공합니다.
위에서 볼 수 있듯이 초기 목록의 직원 4 명은 교육 세션에 참석하지 않았습니다. QUERY 함수는 이름과 직원 ID 번호를 별도의 목록으로 표시하기 위해 일치하는 열뿐만 아니라이 정보를 제공했습니다.
이 예는 매우 구체적인 데이터 범위를 사용합니다. A 열에서 E 열의 모든 데이터를 쿼리하도록이를 변경할 수 있습니다. 그러면 새 직원을 목록에 계속 추가 할 수 있습니다. 사용한 QUERY 수식은 새 직원을 추가하거나 누군가가 교육 세션에 참석할 때마다 자동으로 업데이트됩니다.
이것에 대한 올바른 공식은 =QUERY('Staff List'!A2:E, "Select A, B, C, E WHERE E = 'No'"). 이 수식은 A1 셀의 초기 “직원”제목을 무시합니다.
아래에 표시된 것처럼 (Christine Smith) 교육에 참석하지 않은 11 번째 직원을 초기 목록에 추가하면 QUERY 수식도 업데이트되고 새 직원이 표시됩니다.
고급 QUERY 수식
QUERY 기능은 다목적입니다. 검색의 일부로 다른 논리 연산 (예 : AND 및 OR) 또는 Google 함수 (예 : COUNT)를 사용할 수 있습니다. 비교 연산자 (보다 큼, 더 작음 등)를 사용하여 두 그림 사이의 값을 찾을 수도 있습니다.
QUERY와 비교 연산자 사용
비교 연산자 (예 :보다 작거나 크거나 같음)와 함께 QUERY를 사용하여 데이터를 좁히고 필터링 할 수 있습니다. 이를 위해 각 직원이 수상한 상과 함께 “직원 목록”시트에 추가 열 (F)을 추가합니다.
QUERY를 사용하여 하나 이상의 상을 수상한 모든 직원을 검색 할 수 있습니다. 이 공식의 형식은 =QUERY('Staff List'!A2:F12, "SELECT A, B, C, D, E, F WHERE F > 0").
이는 F보다 큰 비교 연산자 (>)를 사용하여 F 열에서 0보다 큰 값을 검색합니다.
위의 예는 QUERY 함수가 하나 이상의 상을 수상한 직원 8 명을 반환 한 것을 보여줍니다. 총 11 명의 직원 중 3 명이 상을받지 못했습니다.
QUERY와 함께 AND 및 OR 사용
AND 및 OR와 같은 중첩 된 논리 연산자 함수는 더 큰 QUERY 수식 내에서 잘 작동하여 수식에 여러 검색 기준을 추가합니다.
관련 : Google 스프레드 시트에서 AND 및 OR 함수를 사용하는 방법
AND를 테스트하는 좋은 방법은 두 날짜 사이의 데이터를 검색하는 것입니다. 직원 목록 예를 사용하면 1980 년에서 1989 년 사이에 태어난 모든 직원을 나열 할 수 있습니다.
또한 크거나 같거나 (> =) 같거나 (<=) 같은 비교 연산자를 활용합니다.
이 공식의 형식은 =QUERY('Staff List'!A2:E12, "SELECT A, B, C, D, E WHERE D >= DATE '1980-1-1' and D <= DATE '1989-12-31'"). 또한 중첩 된 DATE 함수를 사용하여 날짜 타임 스탬프를 올바르게 구문 분석하고 1980 년 1 월 1 일부터 1989 년 12 월 31 일 사이의 모든 생일을 찾습니다.
위에서 볼 수 있듯이 1980 년, 1986 년 및 1983 년에 태어난 세 명의 직원이 이러한 요구 사항을 충족합니다.
OR을 사용하여 유사한 결과를 생성 할 수도 있습니다. 동일한 데이터를 사용하지만 날짜를 변경하고 OR을 사용하는 경우 1980 년대에 태어난 모든 직원을 제외 할 수 있습니다.
이 공식의 형식은 =QUERY('Staff List'!A2:E12, "SELECT A, B, C, D, E WHERE D >= DATE '1989-12-31' or D <= DATE '1980-1-1'").
원래 10 명의 직원 중 3 명이 1980 년대에 태어났습니다. 위의 예는 우리가 배제한 날짜 이전 또는 이후에 태어난 나머지 7 명을 보여줍니다.
QUERY에 COUNT 사용
단순히 데이터를 검색하고 반환하는 대신 QUERY를 COUNT와 같은 다른 함수와 혼합하여 데이터를 조작 할 수도 있습니다. 의무 교육 세션에 참석했거나 참석하지 않은 목록에있는 모든 직원을 지우고 싶다고 가정 해 봅시다.
이렇게하려면 QUERY를 COUNT와 같이 결합하면됩니다. =QUERY('Staff List'!A2:E12, "SELECT E, COUNT(E) group by E").
E 열 ( "Attended Training")에 중점을 둔 QUERY 함수는 COUNT를 사용하여 각 유형의 값 ( "예"또는 "아니오"텍스트 문자열)이 발견 된 횟수를 계산했습니다. 이 목록에서 6 명의 직원이 교육을 수료했으며 4 명은 교육을 수료하지 않았습니다.
이 수식을 쉽게 변경하고 SUM과 같은 다른 유형의 Google 함수와 함께 사용할 수 있습니다.
