구글 스프레드시트 QUERY 함수 이용해서 학생 데이터 가공하기 2
아래 링크에 있는 구글스프레드 시트 문서를 사본복사 하면 지금부터 설명할 내용이 그대로 들어 있다. 이번에는 에러가 날 때 해결하는 방법과 원하는 값을 좀 더 고급적으로 가져오는 방법에 대해 설명하겠다.
아래 링크를 사본 복사하기 해서 확인해 보면서 따라 하면 된다.
https://docs.google.com/spreadsheets/d/1Pn9zweyWrPTmhNHbfQzOtGD_i1mjoI-XVBntZgyo8do/edit?usp=sharing
데이터를 다른 시트에 넣어 놓고 가져오기.
실제 학교에서 작업을 하다 보면, 정리된 학생 자료가 다른 시트에 있는 경우가 있다. 원본 시트에서 원하는 내용을 가져다 확인해 보자.
참고로 1반 시트에 가보면 데이터가 다음과 같이 들어 있다.
이전 설명에서 데이터 범위를 시트가 포함된 데이터 범위로 바꿔 주면 된다.
1. 이번에는 1반시트에서 이름이 김민서인 학생을 찾아서 제목줄 포함해서 검색해 보자.
=QUERY('1반'!A1:G15,"SELECT * WHERE B = '김민서'",1)
1-1. 전입생 추가 : 학교에서 학생을 가르치다 보면 전입생이나 전출생이 생겨서 1반 시트에 있는 원본데이터 범위가 달라지는 경우가 생긴다. 이럴때를 대비해서 데이터 범위를 좀 더 여유있게 잡아 놔도 된다.
어차피 원하는 내용을 가져오는 것이기 때문에, 아래쪽에 빈 줄이 있어도 상관 없다. 만약 계속해서 데이터가 추가될 것 같으면 아예 컬럼 전체를 데이터 범위로 잡아 줘도 된다. 지금 부터는 데이터 범위를 컬럼전체를 잡아 주도록 하겠다.
아래 데이터 범위에 숫자가 빠지고, A열에서 G열까지 있는 모든 내용에서 검색하게 된다.
그럼 전입생이 왔을 때 계속해서 1반 시트에 내용을 추가해 주면 된다.
=QUERY('1반'!A:G,"SELECT * WHERE B = '김민서'",1)
2. 가져고오고 싶은 열 주소를 A23셀에 입력해서 입력된 열의 값만 가져오기
점수중에 1차평가점수만 가지고 올 건지, 2차평가 점수만 가지고 올건지 노란색 칸(A23)에 열주소를 입력해 보자.
1차평가 열주소는 C, 2차평가 열주소는 D, 수행평가는 E열에 들어 있다.
A23 열에 입력된 값 : D
입력된 값은 C,D,E 중에 원하는 열 주소 값으로 바꾸면 된다.
=QUERY('1반'!A:G,"SELECT A,B,"&A23&" WHERE B= '김민서'",1)
A23에 입력된 값에 따라 1차평가를 가져오기도 하고, 2차평가를 가져 오기도 하면서 결과 값이 달라진다.
3. 해당 조건이 없어서 에러 날때 해결 방법(제목이 나오게 하면 에러는 나지 않는다)
찾는 결과가 없으면 에러가 발생한다. 1반 시트에 금길동이라는 학생이 없기 때문에 검색결과가 나오지 않는다. 그럼 아래처럼 #N/A 에러가 나와서 보기가 좋지 않다.
=QUERY('1반'!A:G,"SELECT * WHERE B = '금길동'",0)
이런경우에는 ISERROR 문을 사용해서 에러가 났는지 확인하고 찾는 값이 없어 에러가 날때 다른 작업을 하게 하면 된다. 먼저 에러가 나는지 ISERROR문을 이용해서 확인해 보자.
=ISERROR(QUERY('1반'!A:G,"SELECT * WHERE B = '금길동'",0))
ISERROR문은 수식의 결과 에러가 있는지를 확인하는 함수다, 금길동 이 검색되지 않으므로 에러가 난다. 따라서 TRUE를 반환한다.
3-1. 명령어가 길어져서 복잡해 보인다면 QUERY 문으로 결과를 다른셀에 도출하고 그 값을 이용해서 결과가 있는지 확인해도 좋다.
A31셀에서 검색한 결과가 없어서 에러가 났는지 확인
=ISERROR(A31)
3-2 if 문을 사용해서 에러가 발생한 경우 찾는 값이 없다는 메시지를 보내고, 그렇지 않을 경우 결과를 출력하게 해 보자.
조금 어려워 보이지만 잘 살펴보면 크게 어렵지 않다.
=IF(ISERROR(QUERY('1반'!A1:G15,"SELECT * WHERE B = '금길동'",0)),"데이타 없음",QUERY('1반'!A1:G15,"SELECT * WHERE B = '금길동'",0))
문장을 그대로 읽어 보면, 1반 시트에서 금길동 학생이 있는지 검색해서 찾아와라, 만약 검색결과가 없어서 에러가 나면, 데이터 없음 이라는 문구를 출력하고, 그렇지 않으면 결과 값을 출력해라 라는 명령어 이다.
위 수식이 복잡하면 아래 수식 처럼 A31셀에서 결과를 확인하고 그 결과를 이용해서 표시해 주면 된다.
=IF(ISERROR(A31),"데이타 없음",A31)
A31 셀에 에러가 나면 데이타 없음이라는 글자를 출력하고, 그렇지 않으면 A31셀 결과를 그대로 출력하라는 의미다
3-3 결과 값이 있으면 결과 값이 표시된다.
IF(ISERROR(QUERY('1반'!A1:G15,"SELECT * WHERE B = '홍길동'",1)),"데이타 없음",QUERY('1반'!A1:G15,"SELECT * WHERE B = '홍길동'",1))
4. WHERE 조건문에서 알아두면 좋은 내용 정리
WHERE 조거문 안에서 사용하는 함께 사용하는 명령어 들이다.
말로 하는 걸 잘 표현하기 위해 함수나 명령어들을 잘 알아 두어야 한다.
4-1 문자열 다양한 방법으로 검색하기
일부 글자가 포함된 경우를 검색하고 싶다면 WHERE 문에 아래 명령어를 함께 써야 한다. 이름에 포함된 글자가 있는지 검색해 보자
아래 검색창에 WHERE 문을 아래와 같이 바꿔보자.
=QUERY('1반'!A:G,"SELECT * WHERE B = '김민서'",1)
='김민서' 이름이 김민서인 사람 WHERE B = '김민서'
starts with ‘김’ 김씨성을 가진 사람 WHERE B starts with '김'
ends with ‘서’ 서짜로 끝나는 사람 WHERE B ends with '서'
contains ‘민’ 이름에 민자가 포함된 사람 WHERE B contains '민'
김씨성을 가진 학생만 나오게 하고 싶은 경우
=QUERY('1반'!A:G,"SELECT * WHERE B starts with '김'",1)
1반 시트에서 이름이 ‘김’ 자로 시작하는 데이터들을 보여 준다.
4-2 생년월일이 2002-03-01 일 이후인 학생만 필요한 경우. H열에 생년월일이 있다면 아래와 같은 방법으로 검색하면 된다.
=QUERY('1반'!A:G,"SELECT * WHERE H>date '2002-03-01'",1)
단 1반 시트에 H열 서식은 반드시 날짜로 되어 있어야 정상 작동한다. 날짜나 시간은 숫자가 아니기 때문에 날짜 서식으로 검색해야 한다.
날짜 조건: date 'yyyy-MM-dd'
시간 조건: timeofday 'HH:mm:ss'
날짜와 시간 조건: datetime 'yyyy-MM-dd HH:mm:ss'
4-3 데이터가 있는지 여부를 검색해서 나타내기
가끔 데이터가 많아 지다 보면, 빈칸이 없는 학생만 보고 싶거나, 빈칸인 학생들만 모아서 보고 싶을 때가 있다. 이때 null 명령어를 사용한다.
예를 들어 백신 접종을 신청한 학생만 보고 싶거나, 신청하지 않은 학생(신청란이 빈칸으로 된 학생)만 모아 볼 수 있다.
빈칸인 데이터만 검색하는 조건: is null
빈칸이 아닌 데이터만 검색하는 조건: is not null
구분에서 문과,이과, 예체능을 신청하지 않은 학생만 검색해서 표시하기
=QUERY('1반'!A1:G15,"SELECT * WHERE G is null",1)
주의할점 : 이때 데이터범위를 ('1반'!A:G) 로 주고, is not null 로 검색하면 G 열에 비어 있는 데이터가 엄청나게 많기 때문에, 문제가 생길 수 있다.
4-4 검색한 결과를 크기순으로 정렬하고 싶은 경우
검색한 결과를 성적순으로 정렬하고 싶은 경우에 사용하면 좋다.
WHERE 조건문에 정렬 순서 포함
order by F desc
order by F ASC
예를 들어 김씨성을 가진 학생 중 총점(F)이 높은 순서대로 나오게 하고 싶은 경우
=QUERY('1반'!A:G,"SELECT * WHERE B starts with '김' order by F desc",1)
검색어 창에 콤마 없이 order by 문을 추가로 적어 넣으면 된다.
4-5 검색결과 나오는 인원수를 2명으로 제한 하고 싶은 경우
총점이 높은 순으로 정렬시키면서 그 중에 몇 명만 보고 싶은 경우 검색결과 나오는 수를 제한하면 된다.
WHERE 조건문에 limit 포함
limit 2 검색결과 중 2명만 보이게
limit 3 검색결과 중 3명만 보이게
김씨성을 가진 학생중 총점순으로 정렬한 다음 제일 높은 1명만 나오게 하고 싶은 경우 limit 를 추가하면 된다.
=QUERY('1반'!A:G,"SELECT * WHERE B starts with '김' order by F desc limit 1",1)
5. select 문에 추가하는 함수 모음:
검색해온 데이터에서 바로 원하는 값을 얻어낼 수 있다.
성적이 90점 보다 큰 학생을 출력해라 라는 명령에서 아래 함수를 함께 사용하면 조건에 맞게 가져온 학생들을 출력하지 않고, 원하는 값만 알아낼 수 있다.
갯수: count( ) 갯수를 알아낼 때 사용
최대치: max( )
최소치: min( )
합계: sum( )
평균: avg( )
문과 학생이 몇명인지 알고 싶은 경우 :
문과학생의 이름을 나오게 하면서 그 이름 앞에 count 함수를 사용해서 갯수를 세면 됨
아래처럼 하면 문과 학생들 이름이 다 나온다.
=QUERY('1반'!A:G,"SELECT B WHERE G='문과'",1)
이때 count로 묶어 주면 검색된 이름이 몇 명인지 알 수 있다.
=QUERY('1반'!A:G,"SELECT count(B) WHERE G='문과'",1)
즉 문과 학생은 총 4명 이라는 걸 알 수 있다.
6. 기본적인 것은 다 설명했다. 아래 예시를 보면서 조금 더 익혀 보자.
말로 잘 표현할 수 있으면 내가 하는 말을 함수로 바꿔서 원하는 값을 찾아서 표시할 수 있다.
예) 1반에서 김씨성을 가진 학생중에 총점이 60점 미만이면서, 수행평가 점수는 20점 초과인 학생 중 문과인 학생을 총점이 큰 순으로 표시하기
=QUERY('1반'!A:G,"SELECT * WHERE B starts with '김' AND F<60 AND E>20 AND G='문과' order by F DESC",1)
예) 1반 학생 중에 이씨성을 가진 학생을 수행평가가 높은 순으로 정렬하고 그 중에 수행평가가 높은 제일 높은 학생 2명만 보여주기
=QUERY('1반'!A:G,"SELECT * WHERE B starts with '이' AND E<60 order by E DESC limit 2",1)
예) 검색하고 싶은 값을 미리 넣어 놓지 않고, 특정셀에 입력되는 값을 참고해서 결과 보여 주기.
=QUERY('1반'!A:G,"SELECT * WHERE B starts with '"&A141&"' AND F>="&B141&" AND G='"&C141&"' order by E DESC",1)
위 설명은 1반 시트에서 값을 가져다가 이름(B)에 A141셀에 있는 성씨를 가지면서, B141셀에 있는 점수보다 높은 총점을 가진 사람들 중에 C141에 해당하는 사람들을 골라서 수행평가가 높은 순으로 출력하라는 의미이다.
간단하게 QUERY 함수문을 사용해서 조금 더 고급적으로 검색하는 방법을 알아 보았다.
다음에는 1반, 2반 시트에 반마다 통일된 형태로 성적이 입력되어 있을 때
각 반 자료를 합해서 내가 원하는 값을 알아내는 방법에 대해 알아 보겠다.
예를 들면 각반 성적을 비교해서 전교 1-10등 안에 드는 학생만 가져오는 방법?
7. QUERY 문 관련 자료
QUERY 함수 1번째 설명은 아래 링크 참고
QUERY 함수 3번째 사용법은 아래 링크 참고
