구글시트 - row 자료 요약을 표로 만들기
#구글시트팁 #행열전환 #query #transpose #unique
수십~수천 row에 있는 자료의 요약 내용을 표에 넣고 싶다면?
이번에는 row 단위의 많은 자료를 받았을 때 이것을 표로 잘 정돈해서 보여주려고 합니다.
(못 보신 분은 https://brunch.co.kr/@muyeolhong/7 참고)
지난번에 봤던 그림 다시 나갑니다.
색깔 문제는 지난번에 해결했지만 여전히 표에 매주 새로운 텍스트 수십 개를 입력하는 작업은 반복적으로 하고 있었는데, 알고 봤더니 최근 한 달치 자료가 엑셀에 row 단위로 정리된다고 합니다. 매번 A> B> C (이러쿵저러쿵 손이 많이 간다는 얘기)를 거쳐서 입력하는 과정을 줄일 수 있을 것 같은 생각이 듭니다.
아래와 같은 자료를 메일로 받았습니다. 해당 자료를 받으면 시트에 붙여 넣기를 하고, 참조 수식으로 표에 값이 자동으로 채워지는 상상을 해봅니다. (ImportRange는 다음에 다루는 것으로)
자료를 받고 보니 좀 이상합니다. 자료가 너무 많네요. 필요한 것은 일별 Top 10인데 일별 Top 100을 주셨네요. 물어보니 지금은 안되고 2주 후부터는 Top 10으로 줄 수 있을는지도 모르겠다고 합니다.
음... 공부를 하는 김에 Top 100 자료를 가지고도 잘 보이게 해 보죠.
붙여 넣기를 할 시트를 추가하고 이름을 '일별 Top 100'이라고 한 후 데이터를 붙여 넣습니다.
<작업할 문서에서 참조할 시트를 추가>이제 작업할 시트도 만들어야겠죠? '최근Top10목록' 이라고 만들어 봅니다.
1. 기본 모양 만들기
첫 줄의 레이블은 수동으로 입력합니다. '날짜', 'Top1', 'Top2'....
이미 색깔 넣기를 하셨던 분들은 첫 줄만 남기고 아래 부부는 지우는 것이 되겠죠.
2. 날짜 부분 채워 넣기
직접 입력해도 되겠지만 '일별Top100'에 입력되어 있는 자료에서 가져오는 방법을 사용해 봅니다.
해당 수식은 B5에 작성하지만 적용은 빨간색 방향으로 원하는 만큼 채우는 것이 목표입니다.
<함수 입력 위치 및 적용 방향>결과를 먼저 보고 하나씩 생각해보죠.
최종 입력값
=query( UNIQUE (query('일별Top100'! A:A, "select A order by A desc") ), "select Col1 limit 5")
왜 이렇게 하는지 살펴보기 위해 하나씩 연습을 해봅시다.
1) query('일별Top100'! A:A, "select A order by A desc") 를 확인해 봅시다. 적용할 때는 = 을 앞에 붙여줍시다.
select A : A (날짜가 있는 행)를 골라
order by A desc : A (날짜) desc (역순) 으로
2) UNIQUE 적용은 간단합니다.
중복은 없애고 1개씩만
3) 개수를 조절하기 위해 다시 query를 사용합니다.
"select Col1 limit 5"
A,B 와 같은 컬럼이 아니라 이미 다른 함수를 사용하면서 배열로 변경이 되기 때문에 고정된 키워드 Col에 숫자 1,2,3 을 붙여서 순서가 부여됩니다. 이때 대소문자를 꼭 구분하세요.
select Col1 : 첫 번째 컬럼을 골라
limit 5 : 다섯 개만
3. 단어 부분 채워 넣기
이번에도 결과를 먼저 살펴보죠.
최종 입력값
=transpose( query('일별Top100'!A:C, "select B where A='"&B5&"' order by C desc limit 10"))
마찬가지로 나눠서 생각해보죠.
1) query('일별Top100'!A:C, "select B where A='"&B5&"' order by C desc limit 10")
select B : B (단어)를 골라
where A = B5 : A (날짜)가 B5와 같은 것만
order by C desc : C (수치) desc (역순) 으로
limit 10 : 열 개만
2) transpose : 옆으로 돌리기
적용하면 바로 결과 화면과 같게 됩니다.
셀을 선택하고 굵은 파란 점 위에 마우스를 올리면 십자 표시가 나타납니다. 그다음에는 클릭하고 쭉 드래그를 하면 복사가 되겠죠?
이미 앞의 글에서 색깔 적용을 연습하셨다면 아래처럼 색깔까지 자동으로 칠해진 것을 볼 수 있습니다.
성공을 축하합니다~
p.s) 후속 작업에는 더 재미(?)있는 얘기가 있지만 장편 소설이 될 예정이라 잠시 마음을 가다듬고 오겠습니다.
