엑셀 Distinct Count 구하기

이리저리 조립과 해체를 반복하면서 데이터랑 친해지는 데 엑셀만한 게 없지. 그래서 웬만한 분석은 여전히 엑셀로 한다. 다음은 피벗 테이블을 이용한 시간 단위 사용자 접속 발생 추이. 시간당 최대 접속자는 9,000명을 넘는다. 이 수치로 알 수 있는 건 무엇일까?

한 명이 9,000번 접속? 아니면 9,000명이 한 번씩 접속? 해당 그래프는 접속자 ip 개수를 그저 시간 단위로 더한 거라 알 수 없다. 중복되지 않는 접속자 ip의 정확한 개수를 세려면 clientip 필드의 계산 유형을 바꿔야 함. 그런데 중복을 제거해주는 계산 유형이 없다(..)

포기할 수 없다. 구글 뒤져보니 피벗 테이블 만들 때 '데이터 모델'이란 옵션이 있다고. (엑셀2013 이상)

해당 옵션으로 피벗 테이블을 만드니 계산 유형에 '고유 개수' 항목이 추가된다. 역시 엑셀 짱. 참고로 엑셀 포맷으로 저장된 상태에서만 해당 옵션을 사용할 수 있다.

다 됐다고 좋아했는데 초 단위 시간 필드를 시간 단위로 묶을 때 문제 발생.

시간 순서로 정렬이 안 된다. 기존에 인식하던 시간 포맷을 '데이터 모델' 상태에서는 그냥 텍스트로 인식.

방법은 나만의 정렬 목록 만들기

'파일 > 옵션 > 고급' 메뉴에서 '사용자 지정 목록 편집' 클릭.

정렬을 원하는 목록 범위 지정.

원하는 정렬 순서로 편집 후 저장.

피벗 테이블에서 앞서 만든 정렬 목록 선택.

다음은 고유한(중복을 제거한) 접속자 ip의 발생 추이. 동접 최대치는 대략 천 명 정도이다. 고유 개수 측정의 중요함을 알 수 있는 대목.  

그런데 너무 번잡하다. 차라리 처음 필드 분류할 때 분 단위나 시간 단위 필드 따로 만드는 게 나을 듯. 더 간단한 방법 아시는 분 제보 좀(..)

관련 글

• 엑셀 Distinct Count 구하기 - 2nd