VBA 빈셀 채우기!(피벗테이블 오류 최소화 방법)

 

피벗테이블을 사용할 때 가장 주의하여야 할 점은 셀병합으로 인해 숫자가 누락되는 것입니다. 또한 합계가 되는 기준셀의 명칭이 없게 되면 우리가 원하는 값에 도달할 수 없다는 것이죠. 이러한 실수를 최소화하기 위해 빈셀의 값을 채우는 방법을 VBA로 짜보는 시간을 갖도록 하겠습니다.

 

 

 

 

엑셀의 빈칸을 VBA로 채워보기

오늘은 VBA로 빈셀을 채우는 방법에 대해 알아보는 시간을 갖도록 하겠습니다.

보통 아래와 같은 예시가 주어질 경우 공란을 그대로 두거나, "A열"의 학교명대로 "병합하고 가운데 맞춤"을 사용하는 경우가 많으실 텐데요. 그럴 경우 필연적으로 발생하는 문제가 있습니다.

 

바로 피벗테이블을 사용할 경우 학교명이 비어있거나, 병합이 되어 있으면 피벗에서 "비어있음"으로 표기가 되어 합계값이 달라지는 오류를 범할 수 있는 단점이 있습니다.

 

빈셀 채우기 예시

 

예제를 가지고 피벗테이블을 돌려보면 "비어있음"의 평균값이 97.9%가 반영된것을 볼 수 있습니다. 

그럼 확인이 편하도록 가장 위에 있는 서울대를 기준으로 살펴볼까요? 서울대 99.2, 98.1점의 평균은 98.7점입니다.

피벗의 나타난 서울대의 점수는 학교명이 기입된 99.2점으로 나타나고 있는데요. 이 문제를 해결하기 위해서는 점수 앞 빈칸이 없도록 학교명을 다 입력해주어야 합니다.

 

 

 

그렇다면 빈칸을 채우려면 어떻게 해야 할까요? 일일히 복사해서 붙여넣는 수고를 해야할까요?

엑셀 중급자 이상의 능력을 갖추신 분들은 이동옵션을 사용하여 빈셀을 선택하고 "=A2" + Ctrl + 엔터키를 통해 한번에 빈칸을 바로 위에 있는 학교명으로 채우는 방법을 사용하실 것 같아요. (단, 병합된 셀은 병합해제 후 진행!)

 

이동옵션을 통해 빈셀만 선택할 수 있습니다.

 

빈칸을 바로 윗셀의 데이터를 반영합니다.

 

물론 간단한 엑셀의 경우에는 이동옵션을 사용하는 것이 가장 빠릅니다. 하지만 Raw가 매번 바뀌는 루틴업무를 한다면 어떨까요? 매일같이 이동옵션을 사용하여 빈셀을 선택하고 "=A2" + Ctrl + 엔터키를 통해 한번에 빈칸을 바로 위에 있는 것으로 채우는 방법을 쓰실 건가요?

 

 

 

 

우리는 엑셀 노가다가 아닌 업무자동화를 구축하기 위함이니 VBA를 사용 함하여 간단하게 해결해야 합니다.

그럼 VBA를 사용하여 위와 같은 작업을 진행해 볼까요?

 

코드 먼저 확인해 보시죠!

Sub 빈셀채우기() Dim rng As Range ' rng라는 범위를 Range로 선언 Set rng = Range("a2", Cells(Rows.Count, "a").End(xlUp).Offset(3)) ' a2셀부터 데이터가 있는 마지막 셀이 있는 위치에서 Offset으로 아래로 3칸 이동한 범위까지 rng.SpecialCells(xlCellTypeBlanks).Formula = "=A2" ' SpecialCells 메서드를 이용하여 빈칸을 선택합니다. ' 빈칸에는 바로 위의 수식을 반영합니다. End Sub

 

해당코드는 A2셀을 시작으로 데이터가 있는 마지막셀까지 범위를 잡아 이동옵션을 통해 빈칸의 값들을 채우는 VBA입니다. 세부적으로 한번 살펴보시죠!

 

rng범위를 설정하기 위해 Dim rng As Range로 선언해 주었습니다.

Set rng = Range("a2", Cells(Rows.Count, "a"). End(xlUp). Offset(3))는 데이터가 있는 마지막 셀까지 범위를 잡겠다는 코드입니다. 앞 포스팅에서도 중요성을 강조드렸던 코드이니만큼 꼭 외워서 사용하시길 권장드립니다.

다만 이번엔 offset을 추가해 주었는데요. 엑셀의 offset함수와 동일한 역할을 하게 됩니다.

결국 Range("a2", Cells(Rows.Count, "a"). End(xlUp)를 통해 셀의 마지막 위치를 선택한 상태에서 offset(3)을 사용하여 아래로 3셀을 이동하겠다는 코드입니다.

 

rng.SpecialCells(xlCellTypeBlanks). Formula = "=A2"는 이동옵션을 사용하여 빈셀을 선택하고 윗셀에 있는 데이터를 빈셀에 채워넣겠다는 코드입니다. SpecialCells가 이동옵션 창을 여는 역할을 하고, XlcellTypeBlanks를 통해 빈셀을 선택하는 역할을 한다고 이해하시면 좋습니다. 또한 Formula는 수식을 의미하기 때문에 선택된 빈센들에 "=A2"의 수식을 각각 입력한다고 생각하시면 이해가 쉽게 되실 거예요.그럼 코드에 따른 결과를 한번 살펴보시죠!

 

빈셀 데이터 채우기 결과

 

---

마치며

이렇게 VBA를 사용하여 빈센에 데이터를 채워 넣는 코드를 짜보았습니다. 간단한 자료의 경우에는 엑셀 함수와 기능을 이용하는 것이 훨씬 빠를 수 있습니다. 다만 우리의 목적이 업무자동화이니 만큼 대량의 데이터를 활용한 루틴업무에 대응하기 위해 연습하고 있다는 것을 잊어서는 안 됩니다!

 

다음 포스팅에서는  오늘 학습했던 빈칸 채우기를 더욱더 효율적으로 사용하기 위해 병합된 셀들을 병합해제하는 VBA를 짜보도록 하겠습니다. 두 가지 코드가 합쳐진다면 병합된 셀을 해제하고 위에 있는 셀의 값을 채워 넣는 방법으로 효율을 높일 수 있기 때문입니다.

 

▼▼▼함께 보면 좋은 정보▼▼▼

VBA 반복문 1탄(for~next 사용하기)

 

VBA 반복문 2탄(For each ~ Next 사용하기)

 

Do ~ Loop문(While, Until)

 

VBA로 셀병합 해제 후 빈셀 채우기(빈셀 채우기와 찰떡궁합!)

 

 

스마트하게 일하는 법!