구글시트 열 피벗 해제하기
구글시트의 강점은 엑셀과 다르게(최근 많은 기능이 생겼지만) 배열과 테이블에 대해서 쿼리가 가능하다는 것이다.
- 함수 안에서 sql문을 사용할 수 있으니 말 다한 것이다.
그래도 그런 복잡한건 부담스러우니, 최소한의 액션으로 처리할 수 있는 방법을 찾아 기재한다.
열 피벗 해제는 뭔가 하니, 이름처럼, 피벗테이블처럼 구성된 크로스탭 쿼리 결과에 대하여, 다시 원본 DB형태로 전처리 하는 작업이다.(뭔...소린가..)
아래와 같이 이름과 소속이 있고, C, D컬럼에 날짜, 근무여부가 있다면, 예를들어 차트 X축에 날짜를 배치하기가 어렵게 된다. C, D열을 Unpivot 해서, A열에 날짜를, D열에 근무여부 1,0을 배치하려고 한다.
사용할 함수는 3가지이며, flatten으로 각 셀의 값을 연결하고, split으로 다시 나눠줄 것이다. split에서 구분자로 사용할 구분자를 나의 경우는 " ________ "로 넣었다. 해당 함수를 배열 함수로 처리하면 끝이다.
=ARRAYFORMULA(
split(
flatten(
'시트이름'!C1:D1 // 날짜 헤더 영역
&"________"&
'시트이름'!A2:A33 // 소속 열
&"________"&
'시트이름'!B2:B33 // 이름 열
&"________"&
'시트이름'!C2:D33 // 날짜에 대한 근무여부 데이터영역
)
,"________" )
)
결과는 대략 아래 그램과 같다.
Unpivot이 실행되어 생성되는 첫번째와 마지막 컬럼사이는 원본 데이터 열이 추가되 었을 경우 추가해도 되고 삭제해도 된다.
단순히 값들을 붙였다가 구분자를 기준으로 각 행/열에 다시 뿌려주는 방식이라서, 공백(null) 값이 있는 경우, 결과값이 꼬일 수 있으므로 공백(null)은 0이나 "-" 값으로 변경해준 다음 작업을 해주는 것이 좋다.
구분자를 셀값에 있을 법한 단순한 것으로 처리하면, 이 또한 결과값에 오류가 생길 수있다. 이유는 구지 설명할 필요가 있나 싶다.
이상 끝.
