[365 함수리뷰] PIVOT 구현하기

서랍님의 코드 리뷰이다.

LAMBDA(range,
    TRANSPOSE(
        IFERROR(HSTACK(UNIQUE(CHOOSECOLS(range, 1)), 
        DROP(REDUCE("", UNIQUE(CHOOSECOLS(range, 1)), 
        LAMBDA(acc, val, VSTACK(acc, 
        TRANSPOSE(FILTER(CHOOSECOLS(range, 2), 
        val = CHOOSECOLS(range, 1)))))), 1)), "")
    )
)(A4:B49)


* 서랍님 수식 리뷰

1. LAMBDA                                                // 함수선언 범위는 9행의 (A4:B49)
2. TRANSPOSE                                             // 행렬 변환
3. IFERROR(...)                                          // 에러처리
   HSTACK(이름,담당구역 )                                 // 이름과 담당구역을 수평으로 나열  
   UNIQUE(CHOOSECOLS(range, 1))                          // range 범위의 첫번째 열에서 고유값을 가져옴 [성명]
4. DROP(REDUCE("", UNIQUE(CHOOSECOLS(range, 1)),         // REDUCE 함수는 초기값이 빈값을 가지기 때문에 에러발생 - 후에 초기값을 없애기 위해 DROP선언
5. LAMBDA(acc, val, VSTACK(acc,                          // LAMBDA 초기값으로 REDUCE함수의 빈값과 이름 영역을 가져와서 acc, val 각각 삽입
   VSTACK(acc,담당구역)                                   // acc는 각 행들마다 만들어진 것을 최종값 + 차례대로 이름과 같은 담당구역을 쌓게 된다.
6. TRANSPOSE(FILTER(CHOOSECOLS(range, 2),                // 위의 코드들과 이어지는 것으로 고유한 이름값으로 필터링하여 결과값을 도출한다. 
7. val = CHOOSECOLS(range, 1)))))), 1)), "")             // REDUCE의 영향으로 없는 값은 에러를 발생하는데 이를 IFERROR로 처리하면서 마지막으로
                                                         // TRANSPOSE를 통해 결과값처럼 해결하는 코드이다.

 

이번 코드는 LET을 통한 변수선언없이 쭈욱 나열해서 써서 판독을 하기 어려울 수 있다. 크게 크게 놓치지말고 따라와주길 바란다.

 

전체적인 코드 구성은 이렇다.

1. LAMBDA로 해당 영역을 설정하고

2. 처음부터 결과처럼 PIVOT 모양이 아닌 이름과 담당구역의 DICTIONATY 형태로 구성하다가 마지막에 TRANSPOSE를 통해서 결과를 도출한다.

3.  REDUCE값의 초기값이 빈값이기 때문에

이런 형태로 하나씩 밀려내려가면서 FILTER로 못찾은 값들이 에러를 발생하게 된다.

4. 이를 바로잡기 위해서 DROP(배열,1) 이렇게 하여 빈값들을 제거하면서 IFERROR을 통해 마지막으로는

 

이런 형태의 테이블을 완성하게 된다.

5. 마지막으로는 TRANSPOSE로 이러한 형태를 만들어 주게 된다.

 

이번 코드를 진행하면서 처음에는 MAP 함수를 사용하였는데, MAP과 REDUCE의 차이를 몰라서 발생한 헤프닝이었다.

 

간단하게 설명하자면 ,

MAP 은 원 데이터를 가지고 새로운 배열을 만들어낸다. 하지만 크기 자체는 불변이기에 크기가 변경이 되면 에러를 발생한다. 또한 원 데이터를 변화가 없다.

 

REDUCE는 초기값을 계속 누적시키면서 마지막에 값을 도출하기 때문에 결국 이 초기값이 계속 누적되는 값이기에 이 값을 드러낼수 있는 후속조치가 필요하다. REDUCE는 너무나 당연하게도 초기값의 변화가 있으며, 크기 역시 변화가 있다.

 

 

오늘도 화이팅이다.

 

기초방77.xlsx

0.01MB