데이터 검증법, 엑셀을 통한 중복데이터 및 데이터 차이 찾기

안녕하세요 데이터사이언티스트 먼지입니다.

 

지난 데이터 검증으로 고생했던 포스팅에 이어 이번엔 실제로 데이터 검증하는 법에 대해 포스팅 해보려 합니다.

혹시나 데이터 검증으로 제가 했던 고생이 궁금하신 분들은 이전 포스팅을 참고해 주세요^^

 

https://sequence-data.tistory.com/45

 

데이터 검증은 너무나도 중요합니다.

다만, 파이썬을 띄워 검증을 하기엔 너무 번거롭기 때문에 간편한 엑셀로 진행하게 되는데요.

 

하지만 막상 엑셀을 켜도 직접 해보지 않고, 익숙하지 않으면 더욱 해매게 됩니다.

저도 공부할겸, 저와 비슷한 어려움을 겪었던 분들을 위해

 

1. 데이터 중복 검증법

2. 데이터 차이 검증법

 

2가지를 포스팅으로 정리해보려 합니다.

 

Case1. 하나의 데이터셋에서 중복데이터 검증

데이터셋에서 중복데이터를 철저하게 확인해야 하는 경우들이 있습니다.

예를 들어, 거래내역 데이터에서 같은 데이터가 2번 들어가게 되는 경우들이 있는데 이 경우 부정확한 매출액이 산정되게 됩니다.

따라서 정확하게 중복되는 데이터 파악하고 제거해줘야 합니다.

중복데이터 예시

 

Soultion. Countif 함수

중복데이터를 확인하는 여러 검증법이 있겠지만 저의 경우 Countif 함수가 가장 편했습니다.

새로운 칼럼에 다음과 같은 함수식을 만들어줍니다.

전체 A열 중에 A2 데이터, 전체 B열 중에 B2 데이터... 인 데이터를 카운트하는 함수입니다.

=COUNTIFS(A:A, A2, B:B, B2, C:C, C2, D:D, D2, E:E,E2)

 

 

따라서 아래 함수식은

날짜가 "2025-06-01", 거래처명이 "주식회사 하나", 제품분류가 "스마트폰 A100", 매출액이 "1200000"인

데이터의 개수를 찾는 식입니다. 

 

위 함수를 적용해보면 아래와 같이 중복된 데이터를 빠르게 찾을 수 있습니다.

 

Case2. 2개의 데이터셋에서 다른 수치 찾기

이번엔 2개의 데이터셋에 다른 데이터를 찾는 case에 대해 적어보겠습니다.

 

분명 같은 데이터셋인데, 코드 프로세스를 돌렸을 때 결과값이 다르게 나오는 경우가 종종 생깁니다.

하나의 데이터만 수치가 바뀌어도 결과값이 다르게 산출되기 때문에 이상이 생겼다면 input데이터를 꼭 체크해야 하죠.

 

먼저 리스트 형식의 1열 데이터로 진행해보겠습니다.

 

A열 데이터가 나와야 하는데, 2개의 데이터가 생략된 B열 데이터가 나왔다고 가정해봅시다.

지금이야 20개 정도의 데이터지만 100개 이상의 데이터라면 달라진 데이터를 찾기 어렵겠죠?

 

Solution1. Countif 함수

마찬가지로 해결책은 countif 함수입니다.

다만 If 함수를 조금 곁들여 주면 됩니다. 

=IF(COUNTIF(A:A,B2)>0, "o", "x")

 

A열에 B2의 데이터 개수가 1개 이상일 경우 "o", 0개일 경우 "x"라고 표현하는 간단한 함수식입니다. 

 

위 함수를 쓰면? 아래와 같이 o, x가 표시가 됩니다.

 

이후 x라고 표시된 데이터를 클릭해서 확인해주면 됩니다.

B열의 "전기포트 K5"는 A열에 존재하지 않는 것으로 나타나는 걸 확인할 수 있습니다.

 

그렇다면 여러행인 데이터는 어떻게 검증하나요?

라고 궁금하실 수 있는데 간단합니다. 하나의 데이터로 만들어주면 끝입니다.

하나의 문자열로 만들어주고 위와 동일한 방법으로 검증하면 됩니다.

# 검증용 데이터
=A2 & "|" & B2 & "|" & C2 & "|" & D2 & "|" & E2

 

 

Solution2. 조건부 서식을 사용한 시각화

countif로 확인되는 건 알겠는데, 어디가 차이나는지 눈으로 쉽게 판단하고 싶을 때가 있습니다.

위의 경우도 그렇고 데이터가 많다보면 일일이 확인하기 어려운 경우가 있으니까요.

 

 

이럴 경우는 데이터 시트를 2개로 나눠 편하게 확인하는 방법이 있습니다.

바로 "조건부 서식"의 같음을 이용하는 방법인데요.

 

단, 이 기능의 경우 정말 1대1 매치가 되는 경우에만 표시를 해주기 때문에, "정렬"이 필수입니다.

열마다 하나씩 정렬을 걸어주고 난 뒤에 사용이 가능합니다.

 

A,B,C,D,E열에 대해 모두 같은 조건으로 정렬되었다고 가정하고  조건부 서식을 이용해보겠습니다.

 

(1) 먼저 검증하려고 하는 데이터를 드래그 합니다. 

 

(2) 홈 -> 조건부 서식 같음 클릭

 

(3) 다른 시트에 있는 데이터셋 가장 첫번째 데이터를 클릭

 

다른 데이터가 있는 시트에 가서 가장 첫번째 데이터를 클릭해줍니다.

 

 

 

(4) F4 3번 클릭

$A$2 에서 A2가 되도록 바꿔줍니다.

 

 

(5) 결과

 한눈에 보기 쉽게 다른 데이터만 볼 수 있게 됩니다.

데이터셋1의 거래처명이 "에이치유 무역"인 데이터가 , 데이터셋2에선 "hu무역",

데이터셋1의 매출액이 "12000" 인 데이터가, 데이터셋1에선 "14000"인 것을 확인할 수 있습니다.

 

 

마무리하며..

지금까지 엑셀을 통한 데이터검증법에 대해 포스팅 해봤습니다.

경력 높으신 분들께서 "엑셀이 가장 먼저고, 그 다음이 파이썬이다" 라는 말을 하셨을 때 사실 잘 와닿지 않았는데 이번 프로젝트에서 고생하며 깨닫게 된 것 같습니다.

 

이외에도 데이터검증에 더 많은 케이스들이 있겠지만, 그런 방법들도 천천히 익히고 정리해 나가보겠습니다.

읽어주셔서 감사합니다.