Excel에서 대량 데이터를 비교하는 VBA 자동화

태그

생성 일시

2025/02/03 02:23

일 하다 보면 엑셀로 자료 취합할 때가 정말 많죠.

그리고 때로는 지난 번 대비 이번에는 어디가 달라졌는지 “변경점”을 찾아야 할 때도 있고요.

그런데 만약 엑셀에 데이터가 수 만 개가 되버리면 이걸 언제 다 비교하죠?

견적서 다시 받았을 때,

지난 달 대비 이번 달 실적 비교할 때,

개인별 영업 목표 다시 취합했을 때,

설비 작동 로그 받았는데 변경점 찾아야 할 때,

수만개의 데이터 비교를 엑셀 VBA로 one click 자동화 합시다 !

데이터 준비

우선 데이터를 준비합니다.

각자 본인 데이터를 가져오세요.

저는 이렇게 생긴 데이터를 준비했어요.

바나나를 생산하고 품질을 측정한 데이터이고요,

7개의 수치형 칼럼과, 1개의 범주형 칼럼이 있습니다.

칼럼 8개고 데이터 행이 1~8,000 이라서 엑셀의 셀은 총 64,000개네요.

이런 거 2개 주면서 서로 다른 거 찾으라 하면…

틀린 그림 찾기는 재밌지만… 이건 좀…

이 시트와 비교할 다른 시트도 준비하겠습니다.

간편하게 GPT에게 시켜서 만들어 볼게요.

비교 데이터 만들기

우선 GPT에 엑셀 파일을 업로드 해서 데이터를 인식시킵니다.

언제나 그랬듯 판다스로 읽었네요.

그리고 제가 랜덤하게 몇천개를 변경해 달라고 했어요.

몇천개나 변경해야 하니까 GPT가 파이썬 코드를 좀 길게 작성할거라 생각했어요.

저의 오산이었고요. GPT는 역시 저보다 뛰어나네요.

이 열 줄 정도의 코드로 5천개의 랜덤 변경은 끝났어요.

랜덤을 처리하기 위해 numpy 라이브러리를 불러왔고,

그 안에 있는 np.random 명령으로 랜덤 행 지정, 랜덤 열 지정을 해버리네요.

그리고 이걸 5천 바퀴 돌리고 있네요.

간편하게 엑셀 파일로 받았습니다.

이걸 원래의 엑셀로 합쳐서 이렇게 준비했어요.

VBA 코드는 사실 다른 폴더에 있는 엑셀 파일도 열 수 있으니까

굳이 하나의 파일로 통합하지 않아도 됩니다.

VBA 코드만 살짝 더 복잡하게 짜면 돼요.

이번에는 간단하게 해보려고 하나의 파일로 통합했어요.

VBA 코드 작성

VBA 코드 작성 시에 제일 중요한 건

작동해야 하는 아주 명확한 논리를 구체적으로 설명하고,

작동해야 하는 순서도 아주 명확하게 설명해야 한다는 점입니다.

그래야 오류 없이, 시행착오를 줄이며, 잘 작동하는 코드를 빨리 받아낼 수 있어요.

VBA 실행

엑셀에서 Alt + F11 눌러주고 VBA 코드 삽입합니다.

그리고 다시 엑셀에서 Alt + F8 눌러서 매크로 실행합니다.

그랬더니…

Sheet1과 Sheet2를 나란히 보여드릴게요.

같은 위치의 셀을 비교해서 서로 다르면 양쪽 모두 음영을 적용했어요.

그러니 Sheet1과 Sheet2는 정확히 같은 셀 위치에 음영이 적용되어 있어요.

깔끔합니다.

응용

이것이 가장 간단한 VBA 자동화였어요.

그런데 만약 5월에 있던 사람이 6월에는 없거나,

5월에 없던 사람이 6월에 추가되거나 그랬다면 어쩌죠?

그럼 같은 행과 열을 비교하면 안되겠죠?

그럴 땐 고유값을 하나 만들면 됩니다.

직원들 이름으로 하면 혹시 중복이 있을 수도 있고, 그럼 비교 결과가 꼬일 수도 있죠.

그러니 중복이 없는 고유값으로 사번이 더 좋겠어요.

GPT에게 VBA 코드를 작성시킬 때,

Sheet1의 행 데이터에서 A열의 사번을 확인하고,

그 사번을 Sheet2의 A열에서 찾아서,

그 두 데이터의 B열~I열 데이터를 각각 열별로 비교하라고 지시하면 되겠죠.

오늘도 자동화에 한 걸음 더~