엑셀 파워 쿼리를 이용하여 빈 셀 채우기

이전에는 이동옵션을 이용하여 빈 셀을 채우는 방법에 대해 알아봤습니다. 이번에는 파워 쿼리를 이용하여 빈 셀을 채우는 방법을 알아보겠습니다. 아울러 이를 피벗 테이블을 이용하여 데이터를 가공하는 방법을 알아보겠습니다.

 

 

 

 

[그림1] 영업 실적 시트

 [그림1]과 같이 영업 실적 시트가 있습니다. A열과 B열을 보시면 2행이 병합되어 있습니다. 병합되어 있는 셀은 엑셀에서 데이터로 사용하기가 어렵습니다. 

A3:G17 셀 중에서 임의의 셀에 마우스 클릭하고 Ctrl + T를 클릭하여 데이터를 표로 인식시키겠습니다. 이때 병합되어 있는 셀을 클릭하고 표 만들기를 실행하면 정확한 데이터 영역이 설정되지 않을 수 있으므로 병합되지 않은 셀을 선택하거나 표 만들기 창에서 데이터 지정을 다시 합니다.

[그림2] 표 만들기 메세지 창

[그림2]와 같은 창이 뜹니다. 확인 버튼을 클릭합니다. 그러면 표 만들기를 하면서 병합되어 있던 모든 셀의 병합이 해제됩니다. 머리글 또한 3행과 4행이 병합되어 있어 표를 만들자 4행은 모두 빈 셀이 되므로 4행을 삭제하였습니다.

 

파워 쿼리를 사용하기 위해 데이터 메뉴에서 가져오기 및 변환 그룹의 테이블에서를 선택합니다.

그러면 파워 쿼리 편집기 창이 뜹니다. ([그림3] 참조)

[그림3] 파워 쿼리 편집기 창

빈 셀에는 'null'이라는 데이터가 입력되어 있는 모습을 보실 수 있습니다. 이제 이 빈 셀들을 위의 값으로 채워줍니다. 먼저 성명과 직위 열을 선택합니다. 변환 메뉴의 열 그룹에서 채우기를 클릭하고 출력되는 메뉴에서 아래로를 선택합니다. ([그림4] 참조)

 

[그림4] 파워 쿼리 편집기의 변환 메뉴

[그림5]와 같이 모든 빈 셀에는 위쪽 데이터로 채워진 것을 보실 수 있습니다.

[그림5] 빈 셀이 채워진 모습

이제 쿼리 설정 제목란에 영업실적이라 입력하고 홈 메뉴의 닫기 그룹에서 닫기 및 로드의 역삼각형을 클릭합니다. 출력된 메뉴에서 닫기 및 다음으로 로드를 선택합니다.

 출력되는 로드 메세지 창에서 같은 시트에 원하는 셀을 선택한 후 로드 버튼을 클릭합니다. 

[그림6] 파워 쿼리로 빈 셀 채우기 완성 모습

[그림6]과 같이 빈 셀이 모두 채워진 상태로 표가 작성됩니다. 그런데 값들이 이전에 데이터와 다르게 나타납니다. 이제 값들의 표시형식을 변경하여 보기 좋게 꾸미겠습니다. 목표와 실적 값의 모든 셀을 선택하고 홈 메뉴의 표시 형식 그룹에서 콤마(,) 아이콘을 클릭하면 값이 세자리마다 콤마가 표시되는 형식으로 나타납니다. 또한 달성률의 모든 셀을 선택하고 홈 메뉴의 표시 형식 그룹에서 % 아이콘을 클릭하면 값이 백분율 형식으로 나타납니다. 그리고 모든 셀의 표시를 가운데로 설정합니다. 그 결과는 [그림7]과 같습니다.

 

[그림7] 데이터 표시 형식 변경 완료 모습

 

 

 

 

이번에는 완성된 표를 피벗테이블을 이용하여 좀 더 정리된 표를 작성해 보겠습니다.

[그림8] 삽입 메뉴

표의 임의의 셀을 클릭한 후 삽입 메뉴의 표 그룹에서 피벗 테이블을 클릭합니다. ([그림8] 참조)

[그림9] 피벗 테이블 만들기 메세지 창

[그림9]와 같은 메세지 창이 출력되며 확인 버튼을 클릭합니다.

[그림10] 피벗 테이블 필드 창

[그림10]과 같이 새 워크시트에 피벗 테이블 필드 창이 오른쪽에 뜨게 됩니다. 그러면 위쪽의 사각형안에 있는 메뉴들을 마우스로 클릭하여 드래그하여 아래쪽 해당 칸에 이동시킵니다.

먼저 분기는 열로, 성명은 행으로, 값에는 실적과 달성률을 이동시킵니다.

그러면 새 워크시트에 [그림11]과 같이 피벗테이블이 출력됩니다.

[그림11] 피벗 테이블 완성 모습

피벗 테이블의 데이터와 머리글들을 정리하면 [그림12]와 같이 완성됩니다.

 

[그림12] 피벗 테이블 데이터 정리한 모습

 

 

 

이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.