엑셀(Excel) 표 데이터에서 중복값 찾아 나열하고 개수 카운트 수식 작성을 해보자.

안녕하세요. 일상에서 얻는 정보를 모두 기억할 수 없기 때문에 일상에서 일어나는 에피소드들의 정보들을 기록하여 기억하고자 하는 시점에 기록된 정보를 활용할 수 있도록 글을 쓰고자 하는 이작가입니다.

저 역시 지나간 모든 과거의 경험들을 전부 기억할 수 없기 때문에 경험을 통해 얻은 지식과 정보를 기록함으로써 필요한 시점에 과거의 경험의 정보를 활용해 보기 위해 이 글을 작성합니다.

기록된 정보들이 많은 분들에게 도움이 될 수 있도록 전달될 수 있길 바라겠습니다.

---

목차

 

 

표 데이터에서 중복값 1개만 나열하고 중복값 데이터 몇번 입력 되었는지 개수 카운트 하는 수식 작성 방법 썸네일 이미지

앞서 작성한 글에서 중복값을 찾아 중복값의 데이터를 1개의 데이터만 남기고 나머지 중복값은 삭제하는 수식을 작성 하는 는 방법에 대해서 기록한바 있습니다.

엑셀(Excel) 중복값 제거 1개만 나열 하는 수식을 작성해 보자.

 

이번 엑셀(Excel) 내용은 표 형태로 작성된 데이터의 범위내에서 중복값을 찾아 나열하고 중복값이 각각 몇번씩 입력되었는지 개수를 세우는 수식을 함께 확인해 보기로 하겠습니다.

 

앞서 작성한 중복값 제거 수식은 간결하게 작성한 수식이라면 이번 내용에서 작성할 수식은 표 데이터 범위내에서 중복된 모든 데이터를 찾아 나열하는것으로 수식의 길이가 길게 작성될것이고 사용되는 함수도 다르기 때문에 앞전에 기록한 "엑셀(Excel) 중복값 제거 1개만 나열 하는 수식을 작성해 보자." 글에서의 수식과는 차이가 있을 수 있습니다.

따라서 중복값을 제거 하고 나열하는 수식 이 잘 이해가 되지 않는다면 어떤 형태로 데이터가 입력되었는지에 따라서 적용할 수 있는 수식을 Case by Case로 기억해 두었다가 사용자의 서식 환경에 맞게 조금씩 수정해서 사용하는것도 방법이 될 수 있겠습니다.

---

1. 표 데이터에서 중복값 데이터를 찾아 1개 데이터만 입력하고 중복된 데이터값 몇번 입력되었는지 개수 카운트 하는 수식 작성.

표 데이터에서 중복값 1개만 나열하고 중복값 데이터 몇번 입력 되었는지 개수 카운트 하는 수식 작성 방법 썸네일 이미지

샘플 예제 이미지에서 볼 수 있듯이 표 데이터안에 1부터 10까지의 데이터를 무작위로 A1셀부터 F11셀까지 데이터값을 입력한 데이터에서 중복된 값을 1개 데이터값을 H2셀부터 이하 셀까지 수식에 의한 계산으로 결과값을 입력하고 I2셀부터 이하 셀까지는 H열에 입력된 데이터값을 표 데이터의 범위 A1셀부터 F11셀 안에서 각각 몇번씩 입력되었는지 개수를 카운트 하는 수식을 작성하여 결과값을 반환 시키고자 합니다.

 

표 데이터를 참조 범위 영역으로 해서 중복값 데이터를 1개씩만 계산하여 나열하는 수식을 작성해 보면 H2셀에 다음과 같이 작성 할 수 있고, 필요한 행까지 수식 복사하여 사용할 수 있습니다.

=IFERROR(INDIRECT(ADDRESS(INT(((1/MAX(INDEX(((COUNTIF(H1:H$1,$A$1:$F$11)=0)* (COUNTIF($A$1:$F$11,$A$1:$F$11)>1)) /(ROW($A$1:$F$11)*1000+COLUMN($A$1:$F$11)),))))/1000), MOD((1/MAX(INDEX(((COUNTIF(H1:H$1,$A$1:$F$11)=0)* (COUNTIF($A$1:$F$11,$A$1:$F$11)>1)) /(ROW($A$1:$F$11)*1000+COLUMN($A$1:$F$11)),))),1000))),"")

 

다음으로 표 데이터 범위를 참조 영역으로 중복된 값중 1개 데이터값만 결과값으로 H열에 수식으로 계산되어 자동으로 입력된 각각의 값마다 표 데이터 범위에서 몇번씩 작성되었는지 개수를 카운트 하는 수식은 다음과 같이 I열에 입력해 주시고 필요한 행까지 수식 복사하여 주시면 됩니다.

=COUNTIFS($A$1:$F$11,$H2)

표 데이터 안에서 H열의 값이 몇번이나 작성되었는지 개수를 카운트 할 수 있는 함수 countifs함수를 작성하여 표 데이터 참조 영역 A1:F11에서 H2,3,4...의 각각의 값이 몇번이나 작성 되었는지 간단하게 카운트 할 수 있는 수식을 작성 해 주시면 됩니다.

 

만약 표 데이터 안의 범위가 조건에 따라 변동되는 범위를 작성 할 경우에는 표 데이터 범위 A1:F11의 참조 범위 영역을 offset함수로 동적 참조 범위 영역으로 수식을 작성하여 countifs함수의 참조 영역 인수로 활용 할 수 있습니다.

 

offset함수의 사용, 활용 방법과 동적 참조 영역 범위로 활용하기 위한 수식은 앞서 기록된 "엑셀(Excel) offset 함수로 참조 범위 영역을 동적 범위로 수식 작성을 해보자" 글을 함께 참고 해 주시면 좋을것 같습니다.

엑셀(Excel) offset 함수로 참조 범위 영역을 동적 범위로 수식 작성을 해보자.

 

---

이상으로 "글쓰는이작가의 엑셀(Excel) 시간"은 마무리 하고,
"표 데이터에서 중복값 1개만 나열하고 중복값 데이터 몇번 입력 되었는지 개수 카운트 하는 수식 작성 방법." 예제 샘플을 하단에 첨부하였으니 필요한곳에서 활용할 수 있는 수식으로 적용해 보시길 바라겠습니다.

긴 글 읽어주신 여러분들께 감사 말씀 드리며 많은 도움이 될 수 있기를 바라겠습니다.

 

※ "표 데이터에서 중복값 1개만 나열하고 중복값 데이터 몇번 입력 되었는지 개수 카운트 하는 수식 작성 방법" 샘플 예제 다운로드.

글쓰는이작가 샘플 예제 - 표 데이터에서 중복값 1개만 나열하고 중복값 데이터 몇번 입력 되었는지 개수 카운트 하는 수식 작성 방법..xlsx

0.01MB