엑셀 중복값 갯수 상황별로 정확히 세는 방법

얼마 전 차량 정비 이력을 엑셀로 옮기다가 같은 차량번호가 여러 번 나오는 걸 봤습니다. 처음에는 중복 입력인 줄 알았는데, 날짜를 확인하니 실제로 여러 차례 방문한 차량도 있더군요. 엑셀 중복값 갯수를 셀 때는 무엇을 중복으로 볼지 먼저 정해야 숫자가 맞습니다.

중복으로 셀 기준부터 정하기

차량번호가 적힌 A열을 예로 들어볼게요. 12가3456이 세 번, 34나5678이 두 번, 56다7890이 한 번 있다면 전체 기록은 6건입니다. 이때 중복된 차량번호의 종류는 2개, 중복된 번호가 적힌 행은 5개, 첫 기록을 제외하고 추가로 나온 행은 3개입니다. 모두 ‘중복값 갯수’라고 부를 수 있지만 결과는 서로 다르죠.

정비 이력에서는 같은 차량번호가 반복되는 일이 정상일 수 있습니다. 반면 차량번호와 방문일, 정비 항목까지 같다면 이중 입력을 의심할 만합니다. 작업 전에 기준 열과 원하는 숫자가 무엇인지 한 줄로 적어두면 수식을 고르기 쉬워집니다.

COUNTIF로 값마다 나온 횟수 세기

A2부터 A101까지 차량번호가 있다면 B2에 =IF(A2="","",COUNTIF($A$2:$A$101,A2))를 입력하고 아래로 채우면 됩니다. COUNTIF는 지정한 범위에서 A2와 같은 값이 몇 번 나오는지 셉니다. 범위에 붙은 달러 기호는 수식을 아래로 복사해도 검색 범위가 움직이지 않게 고정합니다. IF는 차량번호가 없는 행에 숫자를 표시하지 않기 위해 넣었습니다.

앞의 예시라면 12가3456이 적힌 세 행의 B열에는 모두 3이 표시됩니다. 34나5678이 적힌 두 행에는 각각 2가 표시되고요. 특정 차량의 방문 기록 횟수를 확인할 때 가장 간단한 방식입니다. 다만 이 숫자만 보고 세 건이 잘못 입력됐다고 판단하면 안 됩니다. 차량번호가 같아도 방문일이나 작업 내용이 다를 수 있으니까요.

한 차량번호만 따로 확인하고 싶다면 빈 셀에 =COUNTIF(A2:A101,"12가3456")처럼 입력할 수도 있습니다. 번호를 매번 수식에 쓰기 번거롭다면 E2에 찾을 번호를 적고 =COUNTIF(A2:A101,E2)를 사용하면 됩니다.

중복된 행과 초과 입력 건수 구분하기

B열에 각 번호의 출현 횟수를 구했다면 =COUNTIF(B2:B101,">1")은 두 번 이상 나온 번호가 적힌 행을 모두 셉니다. 앞의 6건 사례에서는 결과가 5입니다. 중복 묶음에 속한 행의 수이지, 지워야 할 행의 수는 아닙니다.

첫 번째 기록은 남기고 그다음부터 나온 행만 세려면 C2에 =IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,1,0))를 입력한 뒤 아래로 채우세요. 검색 범위의 시작점 A2는 고정하고 끝점만 현재 행까지 늘립니다. 같은 번호가 처음 등장한 행은 0, 이후 행은 1이 됩니다. 마지막에 =SUM(C2:C101)을 입력하면 앞의 사례에서는 3이 나옵니다.

중복된 번호가 몇 종류인지 필요할 때는 D2에 =IF(A2="","",IF(AND(B2>1,COUNTIF($A$2:A2,A2)=1),1,0))를 넣고 아래로 복사하세요. 두 번 이상 나온 번호의 첫 등장 행에만 1을 표시합니다. =SUM(D2:D101)의 결과는 사례 기준 2입니다. 행 5개, 초과 입력 3건, 번호 2종류의 차이가 여기서 분명해집니다.

차량번호와 방문일을 함께 비교할 때

실제 정비 자료에서는 차량번호 하나만으로 중복을 판단하기 어렵습니다. A열이 차량번호, B열이 방문일, C열이 정비 항목이라면 D2에 =IF(OR(A2="",B2="",C2=""),"",COUNTIFS($A$2:$A$101,A2,$B$2:$B$101,B2,$C$2:$C$101,C2))를 입력해 보세요. 세 값이 모두 같은 행의 개수를 알려줍니다. 같은 차량이 같은 날 엔진오일과 타이어 작업을 각각 받았다면 서로 다른 기록으로 남습니다.

숫자가 예상과 다르면 원본 값도 확인해야 합니다. 차량번호 앞뒤에 공백이 있으면 눈에는 같아 보여도 다른 텍스트로 취급될 수 있습니다. 방문일도 화면에는 같은 날짜로 보이지만 셀에 시간이 포함돼 있으면 COUNTIFS 결과가 달라집니다. 또 필터로 행을 숨겨도 위 수식은 지정한 범위 전체를 계산합니다.

저라면 정비 이력에서 중복 행을 바로 삭제하기 전에 차량번호, 방문일, 작업 항목을 나란히 확인하겠습니다. 반복된 방문은 고객 이력이고, 같은 작업이 두 번 입력된 기록은 수정 대상일 수 있습니다. 숫자를 세는 수식보다 그 숫자에 붙일 기준을 먼저 정하는 편이 실수를 줄여줍니다.