이번에는 각반이 각각의 시트에 같은 형식으로 기록되어 있을 때 각반의 자료를 모아 전교생을 대상으로 원하는 데이터를 가져오는 방법이다. 이 때 각반은 수시로 전입생이나 전출생에 의해 각반 자료값이 추가되거나 삭제될 수 있고, 상황에 따라 입력값이 수정될 수 있다. 그래도 원하는 데이터를 뽑아 오는데 영향을 미치지 않아야 한다.
이 방법이 제대로 적용되면, 공동 작업할 수 있는 구글 스프레드시트를 만들고, 각반 담임 선생님이 들어와서 자신의 반에 대한 자료만 입력해 주면 된다. 수정할 일이 생기면 자신의 반에 가서 수정하거나 추가로 입력해 주면 된다.
그래도 담당자는 별다른 작업 없이 원하는 데이터를 뽑아 올 수 있다.
예를 들면 전교1등부터 10등까지 학생을 알아낸다거나, 1차 평가 점수가 90점 이상이면서 2차 평가도 90점 이상인 학생들만 뽑아 올 수 있다. 물론 중간에 담임생이 입력한 점수가 바뀌면 바뀐 점수가 그대로 반영되어 자동으로 명단이 바뀌게 된다.
아래 링크에 있는 구글스프레드 시트 문서를 사본복사 하면 지금부터 설명할 내용이 그대로 들어 있다. 이번에는 에러가 날 때 해결하는 방법과 원하는 값을 좀 더 고급적으로 가져오는 방법에 대해 설명하겠다.
아래 링크를 사본 복사하기 해서 확인해 보면서 따라 하면 된다.
각반 시트를 합치는 방법부터 알아보자.
1반시트 (A1:H4)에 있는 값과 2반시트(A1:H5) 에 있는 값을 이용해서 필요한 값 꺼내오기.
아래 1반, 2반 시트에 가보면 데이터가 들어 있다. A열 반, B열 번호, C열 이름, D열 1차평가, E열 2차평가 F열 수행평가, G열 총점, H열은 구분 이다. 각반은 동일한 형태로 되어 있다.
1. 각 시트 합쳐서 하나의 데이터로 만들기
1-1. 1반과, 2반 시트 내용 합치기
합쳐진 데이터를 사용하기 위해서는 반드시 중괄호{ }로 전체를 묶어 주어야 한다. 각 반 자료는 세미콜론(;)을 찍어가며 다른 반들도 계속 추가하면 된다.
여기에서는 그대로 모두 가져올 것이기 때문에 조건(WHERE) 이 없다.
머리행이 둘다 나오게 합치면 검색내용이 없어서 에러나는 것 방지할 수 있기 때문에 머리행을 0이 아니라 1로 두었다..
={QUERY('1반'!A1:H4,"SELECT *",1);QUERY('2반'!A1:H5,"SELECT *",1)}
QUERY 문을 계속 반복해서 사용했다. 그대로 읽어 보면 1반 시트 A1:H4 범위에 있는 값을 모두 가져오고, 그리고 2반 시트 A1:H5 범위에 있는 값을 모두 가져오라는 뜻이다.
QUERY문을 계속 연결해서 불러오기 때문에 { } 로 묶어 주었고, 2개의 문장은 세미콜론(;) 으로 구분해 주었다. 만약에 다른 시트도 불러오게 되면 같은 방법으로 세미콜론을 추가해 가며 계속 불러 오면 된다. 이렇게 불러온 값은 다시 QUERY 문을 사용할 때 합쳐진 데이터 범위가 된다.
1-2. 1반과 2반 시트 이름이 있는 사람만 합치기
그런데 1반에 전입생이 오면 데이터 범위가 달라진다. 그래서 열 뒤에 숫자(H4) 를 적지 않고, 컬럼(H)만 적어서 전체 컬럼내용을 가져오게 한다. 그런데 이렇게 하면 H열에 아래 빈칸까지 다 가져다가 합치게 되는 문제가 있다. 그래서 가져올때 이름(C)이 있는 값만 가져 오게 조건문을 넣었다.
={QUERY('1반'!A:H,"SELECT * WHERE C IS NOT NULL",1);QUERY('2반'!A:H,"SELECT * WHERE C IS NOT NULL",1)}
이제는 전입생이 추가되거나 전출생이 삭제되어도 문제가 없다. A열부터 H열에 있는 모든 값을 가져오기 때문이다. 모든값을 가져오면 빈칸 까지 다 가져오기 때문에 이름이 있는 값만 가져오도록 했다. 하지만 이것도 직접 표시할 게 아니면 큰 상관이 없다.
2. 합쳐진 데이터에서 원하는 값만 찾아서 나타내기
위 1번 설명에서 각반 시트를 합치는 방법을 알아 보았다. 이제 합쳐진 데이터에서 원하는 결과를 가져오면 된다. 위 합쳐진 데이터는 화면에 표시되지 않아도 가상의 공간에 나열되어 있다고 보면 된다. 그럼 그 가상의 공간에 나열되어 있는 데이터에서 원하는 조건 (WHERE) 을 가져오면 된다.
QUERY 함수를 다시 정리하면
QUERY(데이터, 검색어, [헤더]) 로 구분된다.
즉 데이터범위에서, SELECT WHERE 문으로 원하는 조건을 검색해서, 머리말을 포함해서 출력하라는 의미다. 위 1번 설명에서 한 것은 여기에서 데이터 범위를 다시 정의한 것이다. 따라서 복잡해 보이지만, 위 1번 활동으로 데이터 범위를 중괄호로 묶어 주었다고 보면 된다. 그럼 나머지는 기존과 똑같다.
이때 주의할점은 가상의 공간에 합쳐진 데이터의 열은 A,B,C 와 같은 알파벳으로 정의할 수 없다. 가상의 공간에는 A,B,C와 같은 열이 없기 때문이다.
따라서 이때 부터는 A,B,C .... 대신 Col1, Col2,Col3 로 열 이름을 정의해 줘야 한다. 또 주의할 점 col1 이라고 쓰면 에러 난다. Col1 이라고 써야 한다. 첫 글자 c 를 반드시 대문자 C 로 써야 한다. 필자도 이것때문에 한참 고생했다.
2-1 합쳐진 데이터에서 이름이 김민서인 학생의 데이터만 가져오기
=QUERY({QUERY('1반'!A:H,"SELECT * WHERE C IS NOT NULL",1);QUERY('2반'!A:H,"SELECT * WHERE C IS NOT NULL",1)},"SELECT * WHERE Col3='김민서'",1)
복잡해 보이지만 앞에 데이터 범위가 중괄호 { }를 이용해서 여러 시트값이 합쳐진 것만 이해하면 나머지는 어렵지 않다. 그리고 합쳐진 데이터에서 WHERE 로 검색할 때 알파벳이 아니라 Col1, Col2 등으로 검색해야 하는 것만 기억하면 된다. 앞에 복잡해 보이는 중괄호 { } 안에 있는 값은 결국 각 시트에 있는 데이터를 합쳐준 것 뿐이다.
2-2 합쳐진 각반 시트는 가상의 공간에 존재하니까, 빈칸 상관없이 일단 합쳐 놓고, 거기에서 원하는 값을 가져와도 된다. 그럼 더 간단하게 표현할 수 있다.
=QUERY({QUERY('1반'!A1:H4,"SELECT *",1);QUERY('2반'!A1:H5,"SELECT *",1)},"SELECT * WHERE Col3='김민서'" ,1)
위 2-1 보다 훨씬 간단해 졌다. 가상의 공간에 각반 시트가 합쳐지니까, 빈칸이 있는 값을 가상의 공간에 가지고 와도 상관없다. 어차피 보여줄 때 는 김민서 학생만 찾아서 보여주면 되기 때문이다. 그래서 2-2와 2-1의 다른 점은 각반 시트에 있는 데이터를 합칠 때 IS NOT NULL 조건을 빼 버렸다는 점이다.
3. 응용해 보자.
여러반 시트를 합쳐서 새로운 데이터(전교생 자료)를 만들고 그 데이터에서 원하는 값만 뽑아 와 보자. 각반 시트에 있는 데이터를 합치고 나면 다시 QUERY 문을 이용해서 앞에서 공부한 것처럼 원하는 자료를 뽑아 오면 된다.
3-1. 1반, 2반 학생들을 총점 점수 순으로 배열하기
1,2반 시트 데이터 합치고, 합쳐진 데이터에서 이름이 있는 학생들만 가져다가 총점이 높은 순으로 배열하기
=QUERY({QUERY('1반'!A:H,"SELECT * ",1);QUERY('2반'!A:H,"SELECT *",1)},"SELECT * WHERE Col2 IS NOT NULL ORDER BY Col7 DESC" ,1)
설명해 보면 1반2반 합친 데이터에서 이름이 있는 사람들만 골라서, 총점이 높은 순으로 출력하라는 의미다.
3-2. 내용중 총점 점수순으로 배열하고 전교 3등까지만 표시하기
=QUERY({QUERY('1반'!A:H,"SELECT * ",1);QUERY('2반'!A:H,"SELECT *",1)},"SELECT * WHERE Col2 IS NOT NULL ORDER BY Col7 DESC limit 3" ,1)
1,2반 시트 데이터 합치고, 합쳐진 데이터에서 이름이 있는 학생들만 가져다가 총점이 높은 순으로 3명만 배열하기. 앞 명령어에서 limit 3 만 추가 되었다. 모두 가져오는게 아니라 필요한 인원만큼만 가져올 수 있다.
3-3.1,2,3반 학생들(전교생) 중에 김씨성을 가진 학생들만 가져오기
=QUERY({QUERY('1반'!A:H,"SELECT * ",1);QUERY('2반'!A:H,"SELECT *",1);QUERY('3반'!A:H,"SELECT *",1)},"SELECT * WHERE Col3 starts with '김'" ,1)
각반 시트를 합쳐서 전교생 데이터를 만들고, 그 데이터에서 3번째 열 이름이 ‘김’ 으로 시작되는 사람들만 출력하라는 의미다.
3-4. 1,2,3반 학생들(전교생) 중에 김씨성을 가진 이과 학생들만 가져오기
=QUERY({QUERY('1반'!A:H,"SELECT * ",1);QUERY('2반'!A:H,"SELECT *",1);QUERY('3반'!A:H,"SELECT *",1)},"SELECT * WHERE Col3 starts with '김' AND Col8='이과'" ,1)
전교생 중에 김씨성을 가지면서 이과인 학생을 출력하라는 의미다.
만약 에러가 난다면 Col 을 적을 때 맨 앞글자 C를 소문자로 적었을 가능성이 크다.
3-5. 전교생 중에 1차평가 점수가 30점 초과이면서, 2차 평가 점수도 30점 초과인 학생들만 가져오기
=QUERY({QUERY('1반'!A:H,"SELECT * ",1);QUERY('2반'!A:H,"SELECT *",1);QUERY('3반'!A:H,"SELECT *",1)},"SELECT * WHERE Col4>30 AND Col5>30" ,1)
보는 것처럼 각반 시트를 합쳐서 하나의 전교생 데이터로 만들고, 그 데이터에서 원하는 값들을 뽑아 오면 된다.
4. 응용
전교생 중에 노란색 칸에 직접 입력한 총점 보다 더 높은 점수를 가지면서 구분에서 제시한 학생을 표시해 보자.
=QUERY({QUERY('1반'!A:H,"SELECT * ",1);QUERY('2반'!A:H,"SELECT *",1);QUERY('3반'!A:H,"SELECT *",1)},"SELECT * WHERE Col7>"&A108&" AND Col8='"&B108&"'" ,1)
매번 수식을 입력하는게 아니라, 기본 수식을 만들어 놓고, 노란색 셀에 원하는 값을 입력하면 그 값에 해당하는 사람을 보여주게 하면, 응용 가치가 높아진다.
그럼 학생이름만 입력하면 그 학생에 대한 자료만 검색할 수도 있기 때문에, 개인별 출력물을 만들거나 할때 도움이 된다.
5. 응용2
셀에 입력된 시트값에 해당하는 내용만 가져오는 경우. 셀에 2반이라고 입력하면, 2반 시트에서 해당되는 내용만 가져와아 햔다. 이럴 때는 indirect 문을 사용하면 셀에 입력한 시트명을 사용할 수 있다.
6. 추가질문
1학기때 전교 5등안에 들면서 2학기때도 전교 5등안에 드는 학생을 보고 싶다면?
수식으로 복잡하게 만드는 것보다 각반 시트 원본데이터에 1차평가, 2차평가 등수란을 만들어서 각반에서 등수를 입력하게 하고 1차평가와 2차평가 등수가 5보다 작은 사람들을 불러오게 하는 것이 더 편하다.
그래서 기본 데이터를 어떻게 설계하느냐가 문제를 해결하는데 큰 도움이 된다는 것을 명심하자. 필자라면 이런 데이터가 꼭 필요하다면 각반 시트에 rank 함수를 이용해서 등수를 표시하는 열을 만들었을 것이다.
7. 마치며
이상 3회에 걸쳐 구글스프레드시트 중 QUERY 문을 사용하는 방법을 알아 보았다. 잘 응용하면 학교 업무를 간소화 할 수 있다. 익숙해지면, 담당자에게 집중되던 업무를 여러 선생님에게 분산시켜 구글스프레드 시트에 원본데이터를 입력하게 하고, 담당자는 QUERY 문을 이용해서 원하는 데이터를 뽑아 내면 된다.
8. QUERY 문 관련 자료
QUERY 함수 1번째 설명은 아래 링크 참고
QUERY 함수 2번째 사용법은 아래 링크 참고
