학교에서 업무를 하다 보면 수많은 원본 데이터를 가공해야 하는 경우가 생긴다. 기본 함수만 가지고 웬만한 건 다 할 수 있지만, 조금 더 다양하게 데이터를 가공해야 할 때가 있다.
그럴 때 구글 스프레드시트의 QUERY 함수를 사용하면 된다.
(vlookup 함수나 index, match 함수의 한계를 해결할 수 있다.)
QUERY 함수는 그냥 우리가 말로 설명할 수 있는 건 다 거의 다 표현할 수 있는 함수라고 보면 된다. 예를 들어 성적 데이터를 보고, 원하는 데이터를 말로 표현할 수 있으면 된다. 이때는 구체적으로 잘 표현할 수 있어야 한다.
‘1학년 남학생 중에, 전교 100등 안에 드는 학생 중에, 국어점수가 90점 이상인 학생 중에, 영어점수가 제일 높은 학생’ 이런 식으로 말로 진술할 수 있으면 그걸 함수로 만들 수 있다.
추가로 데이터만 충분하다면 위 조건에 더해서 수학 점수는 50점 이하인 학생 중에, 어느 지역에 사는 학생 중에, 김씨성을 가진 학생 등등 원하는 내용을 추가하면 된다.
기본적으로 말로 표현할 수 있다면 모두 QUERY문을 통해서 원하는 데이터를 뽑아낼 수 있다.
이방법은 1명의 학생이 아니라 이 조건에 해당하는 학생들을 전부 뽑아서 표시할 수 있다.
지금부터는 간단하게 예를 들어가며 설명하겠다. 일단 쉬운 것부터... ^^
아래 링크에 있는 구글스프레드 시트 문서를 사본복사 하면 지금부터 설명할 내용이 그대로 들어 있다. 사본복사해서 테스트 해보기 바란다.
https://docs.google.com/spreadsheets/d/14lvzmqtmsF2U_utX2qsqkHF7CXUANEVDAEewknk-wWM/edit#gid=1463887070
아래와 같은 원본데이터를 (I2:O6) 에 넣어 놓고 QUERY 문을 사용해 보겠다.
(숙달이 되면 원본데이터를 다른 시트에 넣어 놓고 활용하면 좋다.)
1. 이름이 김민서인 학생 점수 가져오기
=QUERY(I2:O16, "SELECT * WHERE J = '김민서'" , 0)
이 함수를 분석해 보자.
QUERY 함수를 통해 원하는 데이터를 뽑아오기 위해서 함수 안에 SELECT 문이 필요하다.
=QUERY( I2:O16 , "SELECT * WHERE J = '김민서'" , 0)
[데이터범위] [모든걸 선택해라] [조건은 J번째 줄에 김민서라는 이름이 들어간]
마지막에는 0이나 1을 쓸 수 있는데, 1을 쓰면 제목줄도 함께 표시하라는 뜻이다.
어려워 보이지만, 몇 번 보다 보면 어렵지 않다.
그러니까 맨 앞에 정의된 데이터 범위에서 원하는 조건(WHERE)에 해당하는 것들을 가져다가 필요로 하는 내용들(SELECT)을 출력하라는 명령이다.
만약 에러가 난다면 ‘김민서’ 뒤에 쌍따옴표가 빠졌을 수 있다. SELECT 문은 반드시 쌍따옴표로 묶어 줘야 한다. 아래 함수를 다시 한번 살펴보자.
1-1 . 이런 함수는 뭘 요구하는 걸까?
=QUERY(I2:O16, "SELECT I , J WHERE N>90" , 0)
결과
6 김가빈
그래도 읽어보면
데이터 범위(I2:O6)에서 원하는 조건(WHERE) N열에 점수가 90점 보다 큰 학생들을 찾아서 원하는 내용(SELECT) I, J열만 가져다가 출력하라는 명령이다.
그래서 총점이 90점 보다 큰 학생들을 가져다가 번호와 이름만 출력하게 되는 것이다.
2. 만약 이름을 계속 바꿔가며 확인하고 싶다면
A8셀에 이름을 계속 바꿔가며 입력하게 하고
A8셀 에 입력된 학생 점수 가져오게 하면 된다. 이때 QUERY함수에 이름 대신 따옴표로 감싼 셀 주소 넣어 주면 된다. 아래 예시를 보면서 A8셀을 어떻게 입력하는지 확인하기 바란다.
대부분 따옴표 개수를 잘 못 넣어 에러가 발생한다. 따옴표 수와 위치 등을 잘 입력해야 한다. 어렵다고 생각되면 아래 함수를 복사해서 붙여넣기 하고 A8셀 주소만 바꿔 주면 된다.
A8셀에 입력된 값(문자) : 김민서
=QUERY(I2:O16,"SELECT * WHERE J = '"&A8&"'",0)
위 1번과 같지만 이젠 함수를 하나 만들어 놓고, A8셀에 원하는 학생의 이름을 바꿔가며 입력해서 학생의 데이터를 가져올 수 있다.
2-1. A13셀 에 입력된 점수가 1차평가 점수와 같은 사람을 찾아서 가져오기
숫자가 든 셀을 가져오는 경우 작은 따옴표 하지 않음. 필자도 처음 공부할 때 이 부분에서 가장 힘들었던 기억이 난다. 셀에 입력된 값을 가져다 참조할 때, 셀에 입력된 값이 숫자인지, 문자인지에 따라 입력하는 방법이 다르다. 위 2번에서 설명한 것과 다른 차이점이 보이는가?
A13을 묶을 때 작은따옴표가 없다. 시간을 내서 이름 입력할 때와 어떤 점이 다른지 잘 비교해 보기 바란다.
A13셀에 입력된 값(숫자) : 21
=QUERY(I2:O16,"SELECT * WHERE K = "&A13&"",0)
3. 문과(O열)인 학생들 데이터 모두 가져오기 : 맨끝 헤더를 1로 쓰면 제목표시줄도 가져옴. QUERY문은 1명만 가져오는 것이 아니다. 조건에 해당하는 모든 학생들을 가져 올 수 있다. 아래처럼 쓰면, 문과에 해당하는 학생들을 모두 가져온다. 맨끝이 1로 되어 있으니까 제목값도 가져 온다.
=QUERY(I2:O16,"SELECT * WHERE O = '문과'",1)
3-1 O열에서 문과 학생만 찾아서 그 학생들 중 N열에 최고점수만 가져오기
해석해 보면 문과인 학생들의 N(총점)만 표시하는 건데, MAX함수를 써서 그 중에 제일 큰 점수만 표시하라는 뜻이다.
=QUERY(I2:O16,"SELECT MAX(N) WHERE O='문과'",0)
4. J열에서 홍길동 이름을 가진 학생의 번호(I), 이름(J), 총점(N)만 가져오기
이제 대충 함수가 이해될 것이다. 결국 SELECT와 WHERE을 이용해서 내가 원하는 문구를 만들면 된다.
=QUERY(I2:O16,"SELECT I,J,N WHERE J = '홍길동'",1)
WHERE 조건문에 여러 가지 조건을 줄 수 있다. 이때 각 조건들을 모두 포함해야 한다면 AND 로 계속 연결하면 된다. 또는 조건을 주려면 OR로 연결하면 된다.
5. 이름은 홍길동이면서 문과에 다니는 학생의 데이터 모두 가져오기
=QUERY(I2:O16,"SELECT * WHERE J = '홍길동' AND O = '문과'",1)
6. 수행평가 점수가 15점 이상이면서 문과에 다니는 학생의 데이터 모두 가져오기
=QUERY(I2:O16,"SELECT * WHERE M > 15 AND O = '문과'",1)
6-1. 1차평가 점수(K)가 35점 이상이거나 또는 2차 평가 점수(L)가 30점 이상인 학생만 가져오기
=QUERY(I2:O16,"SELECT * WHERE K > 35 OR L > 30 ",1)
6-2. 문과 학생중에 수행평가 점수가 15점 이상이거나, 이과 학생중에 수행평가가 10점 이상인 학생 가져오기
=QUERY(I2:O16,"SELECT * WHERE (M > 15 AND O = '문과') OR (M > 10 AND O = '이과')",1)
뽑아오고 싶은 조건들을 말로 표현할 수 있다면 WHERE 조건에서 AND 와 OR 로 표현하면 된다.
위 링크 사본만들기 해서 이것 저것 테스트 해보기 바란다.
추후에 QUERY 사용법 2탄은 조금 더 어려운 고급적인 내용을 추가로 올릴 예정이다.
에러가 나는 경우 해결법, 여러 시트 데이터에서 원하는 항목 뽑아오기 등등.
7. QUERY 문 관련 자료
QUERY 함수 2번째 사용법은 아래 링크 참고
QUERY 함수 3번째 사용법은 아래 링크 참고
