[엑셀활용]COUNTIF 함수를 사용한 중복값 순서대로 카운트하기 Partition by

안녕하세요.

오늘은 오랜만에 엑셀 활용 방법에 대해서 포스팅하려고 합니다.

내용은 중복된 값을 카운트 하는건데요. 

 

※ partition by 는 SQL 문에서 특정 항목별 순서를 매기거나 합계를 할 때 쓰이곤 하는 함수인데

해당 내용을 모르시는 분은 크게 신경 안 쓰셔도 됩니다. SQL를 사용하시는 개발자가 쉽게 검색해서 참고해보시도록 하는 취지에서 적었습니다.

 

COUNTIF 함수 활용

여하튼 엑셀에서의 데이터를 항목별로 

아래 예시를 너무 간단하게 하긴 했는데 도시별로 순서대로 순번을 매기는 겁니다.   

단, 동을 정렬에서 순서대로 매기는 거죠. 

 

서울 1,2,3,4,5,6

대전 1,2,3,4

대구 1,2,3,4

부산 1,2,3,4

 

이렇게 순서를 매길 겁니다.

예시

엑셀 사용법은 할 때마다 느끼는데 

사용하는 것보다 설명하기가 더 어렵군요. ^^

 

1. 원래 문서에 Index를 달아 놓는다.   

정렬을 여러 번 하는 문서는 변경을 하기 전에 순번을 달아 놓으면 나중에 다시 원래대로 정렬하기 쉽습니다.

순번을 달아 놓는다.

 

2. 도시, 동별 정렬을 한다. COUNTIF문을 사용한다. 

도시, 동별 정렬

여기서 COUNTIF 문을 사용하는데 중요한 점은 

range를 전체 컬럼을 선택하는 것이 아니라 B2:B19 처럼 특정 범위를 선택한 후

더블클릭을 하거나 쭉 내리면 된다.

그러면 range 가 B2:B19=> B3:B20=> B4:B21 이런 식으로 변경되면서 

D2 = COUNTIF(B2:B19,B2)

D3 = COUNTIF(B3:B20,B3)

...

D19 = COUNTIF(B19:B36,B19)

이런 식으로 숫자를 찾는 범위가 줄어들게 된다.

COUNTIF( 범위, 비교대상 ) => 범위 안에 비교대상이 얼마나 있는지 카운트한다.

도시, 동별 정렬

3. 원 순번대로 정렬한다.

그런데 위에서 조금 잘못된 것이 있는데... (2번)

도시면 순서가 동의 정렬과 관계없는 순서라면 상관없지만 동의 순서 (order by)에 따라 순번을 매겨야 한다면 

사전에 정렬할 때 정렬 순서를 동의 내림차순으로 해주어야 한다.

정렬 주의

기존에 Index를 해놓은 A열의 숫자로 원래대로 정렬을 하는데

이때 주의점은 반드시 그전에 

전체 선택 후 값으로 붙여 놓기를 해놓고 해야 한다. 

왜냐하면 바로 정렬을 하면 함수값이 변경되기 때문이다.

원래대로 정렬

마무리

결과는 다음과 같습니다.

예시가 조금 부실한 듯한데... 예를 만드는 것이 어렵군요. ^^

여하튼 결과 중 서울을 예로 보면 보면 1동까지는 중복되기 때문에 1,2,3,4,5 순번 2동은 6번이 D열에 표기되었습니다. 

결과

엑셀의 기능은 언제 생각해도 막강한 것 같습니다.

오늘 제가 사용한 방법은 어떤 도서를 참고한 방법이 아니라 실제 사용을 하면서 터득한 방법인데 실제 더 쉬운 방법이 있을 수도 있습니다. 아무튼 본인이 익숙하게 사용할 수 있는 방법을 사용하시면 되겠습니다.

엑셀 기본 기능에 있지만 좀 더 복잡하게 중복 값을 제거한다든지 하는 데 위 방법을 응용하실 수 있습니다.

또, 개발자 분들은 테스트나 insert 할 값을 만들 때 seq를 중복되지 않게 만들 때도 응용하실 수 있습니다.

 

감사합니다.

 

관련글 

2020/05/11 - [유용한정보/IT&블로그] - [엑셀활용]Find함수로 IndexOf 기능을 써보자.

[엑셀활용]Find함수로 IndexOf 기능을 써보자.

2020/05/15 - [유용한정보/IT&블로그] - [엑셀활용]셀 수식 사용시 셀 고정하여 사용하기. 절대경로, 상수처럼

[엑셀활용]셀 수식 사용시 셀 고정하여 사용하기. 절대경로, 상수처럼

2020/06/26 - [유용한정보/IT&블로그] - [엑셀활용]엑셀에서 조건문, 논리연산자 IF, OR, AND, ELSE

[엑셀활용]엑셀에서 조건문, 논리연산자 IF, OR, AND, ELSE