피벗테이블 없이 동적인 리스트로 요약테이블 항목 만들기(match, index, countif)
배열함수로 VBA처럼 조건문을 활용해야는 경우가 많은데, 아무래도 생각을 좀 해야하는 관계로;; 사용에 소극적이 될수 밖에 없다. 하지만, 한번 생각하고 서식에 두고두고 써먹을 수 있다면 의미가 있다고 생각한다.
지금 적는 글도 그와 다르지 않으니, 테이블에서 중복값을 없애고 새로운 요약테이블 항목을 만드는 작업이다.
내 경우에는 이해는 해도 막상 자유자재로 응용을 할수 있을까란 생각이 들었다. 하지만, 아무렴어떤가.. 지금의 문제를 해결한게 중하지.. ㅋ
아래 그림의 A열과 같이, 중복값을 처리할 target area가 있다고 하자. 영역을 tarr2로 이름정의를 해두었다.
step #1 match
match 함수는 4개의 인자를 가진다.
lookup_value : 찾으려는 값
lookup_array : 찾으려는 값이 들어있을 것 같은 배열
match_type : 정확하게 일치하는 값을 찾을지에 대한 파라미터(0을 넣으면 정확하게 일치하는 값을 찾음)
중요한 점은 찾으려는 값이 첫번째로 등장하는 배열(lookup_array)상의 순서(index)를 반환한다는 점이다. 값이 아니라 순서를 반환한다는 것을 잊지 말아야 한다.
아래 우측 테이블처럼, countif를 c열의 값을 위에서부터 차례대로 한행씩 확장되게 걸어두고, 같은 값이 존재하는지 여부를 확인하면, 현재는 아래 그림처럼 같은 값이 없으니, 전부 0을 반환할 것이다.
match 함수에서는 해당 값이 0인 첫번째 순서가 true가 되므로, 결과가 1이 된 것이다.
* 배열 수식 처리를 해줘야하는 것을 잊으면 안된다.
step #2 index
index 함수로 반환된 순서 값을 값으로 변경하는 작업을 진행한다. tarr2 배열에서, match 함수로 불러들인 결과를 넣어주면 된다. F열 1번행에서는 결과가 1이었으므로, tarr2 배열에서의 첫번째 값인 5가 반환된다. F열 2번행부터는 위에 5가 생겼기 때문에, match함수의 결과가 H열 match2처럼 달라졌을음 알 수 있다.
step #3 iferror
iferror 문으로 더 이상 추가할 값이 없을 때, 예외를 지정해주면 길고 긴 싸움이 끝난다.
예외가 나오기 전까지 자동 채우기를 해주면 된다. 애초에 넉넉하게 행을 확보해두고 작업하는 것이 좋다.
끝.
