엑셀 중복값 개수 세기 헷갈리지 않고 하는 방법

얼마 전 차량 정비 접수 목록을 엑셀로 확인하는데, 같은 차종이 여러 줄에 적혀 있었습니다. ‘쏘나타가 몇 번 나왔나’와 ‘중복으로 입력된 줄이 몇 개인가’는 비슷해 보여도 답이 다르더군요. 엑셀 중복값 개수 세기는 먼저 어떤 숫자가 필요한지 정하면 수식이 훨씬 간단해집니다.

같은 값의 등장 횟수부터 확인하기

A2부터 A8까지 차종이 쏘나타, 아반떼, 쏘나타, K5, 아반떼, 쏘나타, 투싼 순서로 입력됐다고 가정해 볼게요. 쏘나타의 등장 횟수는 3회, 아반떼는 2회입니다. 특정 차종이 몇 번 등장했는지 알고 싶다면 COUNTIF 함수가 가장 직접적입니다.

빈 셀에 =COUNTIF(A2:A8,"쏘나타")를 입력하면 3이 나옵니다. 찾을 차종을 D2에 적어 두었다면 =COUNTIF(A2:A8,D2)로 바꿀 수 있습니다. 이후 D2의 차종만 바꾸면 결과도 함께 달라지니, 접수 현황을 반복해서 확인할 때 편합니다. 마이크로소프트의 엑셀 안내에서도 COUNTIF를 조건에 맞는 셀의 개수를 세는 함수로 설명합니다.

각 행 옆에 중복 횟수 표시하기

목록 전체에서 어떤 값이 반복되는지 보려면 B2에 =IF(A2="","",COUNTIF($A$2:$A$8,A2))를 입력하고 B8까지 채우면 됩니다. 수식의 $A$2:$A$8은 비교할 전체 범위를 고정하고, 마지막 A2는 현재 행의 차종을 가리킵니다. 수식을 아래로 복사할 때 현재 행만 A3, A4처럼 바뀌는 구조죠.

위 예시에서 쏘나타가 적힌 세 행의 B열에는 모두 3이 표시되고, 투싼 행에는 1이 표시됩니다. B열의 숫자가 2 이상인 행을 필터로 걸면 반복된 차종이 있는 행만 볼 수 있습니다. 다만 이 행들을 단순히 더하면 같은 차종을 여러 번 세게 됩니다. B열의 3은 ‘이 차종이 목록에 총 세 번 나온다’는 뜻이지, ‘이 행 이후에 세 번 더 중복됐다’는 뜻이 아닙니다.

두 번째 입력부터 중복으로 세기

접수 목록에서 중복 입력 때문에 늘어난 행 수가 궁금할 때는 기준이 달라집니다. 쏘나타가 3번 있으면 첫 번째를 제외한 2행, 아반떼가 2번 있으면 첫 번째를 제외한 1행이 중복입니다. 예시의 중복 입력 행은 모두 3행입니다.

C2에 =IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,1,0))를 넣고 아래로 채워 보세요. $A$2:A2는 맨 위부터 현재 행까지만 확인합니다. 처음 등장한 값에는 0, 두 번째부터 등장한 값에는 1이 붙습니다. =SUM(C2:C8)을 계산하면 중복으로 늘어난 행 수인 3을 얻습니다. A열이 비어 있는 행은 C열도 빈칸으로 남겨 두도록 했습니다.

이 방법은 명단이나 주문번호처럼 ‘처음 입력된 한 건은 유지하고 나머지를 점검’할 때 유용합니다. 차량 정비 이력이라면 같은 차종이 여러 번 나오는 것이 당연할 수 있으니, 실제 중복 접수를 찾을 때는 차종만 보지 말고 접수번호처럼 한 건을 구별하는 열을 기준으로 삼아야 합니다.

차종별 개수를 표로 보고 싶다면

차종마다 몇 건인지 한눈에 보려면 피벗 테이블도 좋습니다. 목록에서 빈칸 없이 데이터 범위를 선택하고 피벗 테이블을 만든 뒤, 차종 필드를 ‘행’과 ‘값’ 영역에 각각 넣습니다. 값 영역이 ‘개수’로 표시되는지 확인하면 쏘나타 3, 아반떼 2, K5 1, 투싼 1처럼 차종별 건수가 나옵니다. 값이 합계로 잡혔다면 값 필드 설정에서 개수로 바꾸면 됩니다.

반면 차종이 총 몇 종류인지 궁금하다면 중복 횟수와는 다른 계산이 필요합니다. Microsoft 365나 Excel 2021·2024에서는 =ROWS(UNIQUE(FILTER(A2:A8,A2:A8<>"")))로 빈칸을 제외한 서로 다른 차종의 수를 셀 수 있습니다. 위 목록에서는 4가 나옵니다. UNIQUE 함수를 지원하지 않는 버전이라면 피벗 테이블의 행 항목을 확인하는 편이 간단합니다.

숫자가 예상과 다를 때 확인할 것

겉보기에는 같은 차종인데 개수가 갈리면 입력값부터 확인해야 합니다. 예를 들어 ‘쏘나타’와 ‘쏘나타 ’처럼 뒤에 공백이 붙은 값은 결과를 혼란스럽게 만듭니다. 원본을 수정하기 어렵다면 보조 열에 =TRIM(A2)를 넣어 불필요한 앞뒤 공백을 제거한 뒤 그 열을 기준으로 세는 방법이 있습니다. 차종명 표기가 ‘쏘나타’와 ‘소나타’처럼 다르면 공백을 지워도 합쳐지지 않으므로 표기 기준을 먼저 맞춰야 합니다.

또 숫자 1이 나온다고 해서 오류는 아닙니다. 그 값이 한 번만 등장했다는 뜻일 수 있으니까요. 먼저 몇 건이 등장했는지, 처음 이후 몇 행이 반복됐는지, 서로 다른 값이 몇 종류인지를 구분해 두면 필요한 결과를 정확히 읽을 수 있습니다. 저는 접수 자료를 볼 때도 그 기준을 먼저 적어 둡니다. 같은 ‘중복’이라는 말 아래 서로 다른 숫자가 숨어 있는 경우가 꽤 많거든요.