피벗테이블을 만들거나 sumif 함수를 사용하기 위해서는 빈칸이 있으면 제한이 되는데 이럴 때 사용하는 방법입니다.
그리고 피벗테이블에서 다시 함수를 사용해서 값을 구할 때도 사용할 수 있습니다.
다음과 같이 누군가가 보기 좋게 만들어 놓은 가공의 데이터를 만들어 보았습니다.
그렇게 보기 좋지도 않지만 빈칸은 데이터를 분석하는 과정에서 곤란하게 만드는 가장 큰 요인입니다.
엑셀에는 빈칸을 "0"으로 인식하지만 다른 분석 Tool에서는 Null 값으로 이건 데이터가 있는 것도 없는 것도 아닌 상황이 발생하게 됩니다.
엑셀에서 빈칸 채우는 방법
엑셀에서 빈칸을 채우는 방법입니다. 비어있는 칸은 아마 데이터를 만든 사람이 위의 값과 동일하기 때문에 배려하기 위해서 친절하게 비워 둔 것으로 보입니다.
채우기 기능 활용
구분을 선택 해 준 후 단축키 Ctrl + G를 눌러 줍니다. 이동의 탭으로 이동하게 되고 옵션을 눌러 줍니다.
다양한 이동 옵션이 있지만 빈셀만 선택을 하고 싶기 때문에 빈셀을 클릭해 주도록 하겠습니다. 그럼 오른쪽과 같이 빈센에 음영이 표기되는 것을 볼 수 있습니다.
빈셀만 자동으로 선택을 해주었습니다.
이때 "+"를 눌러 줍니다. 그리고 화살표 "▲"을 누른 후 Ctrl을 누른 상태로 Enter를 눌러 줍니다.
오른쪽과 같은 결과가 나오게 되었습니다. sumif 함수를 한번 해 보도록 하겠습니다.
구분은 a, b, c, d로 이루어져 있는데 해당 값은 합은 총계와 같이 되는 것을 볼 수 있습니다.
※ 빈칸을 두는 것보다 조금 더 좋은 방법 ( 추천은 하지 않음 )
데이터는 있는 상태지만 아까와 같아 보이게 만들어 보았습니다. 무엇을 변경하였을까요?
글씨를 흰색으로 변경했습니다. 보는 사람이 이러한 양식을 좋아한다면 빈칸을 주는 것보다 나중에 데이터 관리하기 용이합니다. 하지만 결국 이것도 시간이 지나면 내가 색을 변경했는지 알 수가 없어지기 때문에 하지 않으셨으면 좋겠습니다.
피벗테이블에서 빈칸 채우기
피벗테이블을 만들어서 아까와 같은 테이블로 만들어 보도록 하겠습니다.
피벗테이블 레이아웃을 다음과 같이 변경했더니 아까와 같이 구분에 a, b, c, d 아래가 비어 있는 것을 볼 수 있습니다.
피벗테이블은 집계를 하기 때문에 해당 데이터를 두고 중구분의 값은 빈 값처럼 표시하게 됩니다.
피벗테이블에서 Dax를 사용하지 않는다면 피벗테이블에서 연산을 하고 싶으면 sumif 함수가 유용하기 때문에 구분의 아래 값을 채워 놓는 것이 좋을 것 같습니다.
일단 지금 상태에서 sumif 함수를 사용해 보도록 하겠습니다.
다음과 같이 a의 모든 값이 아닌 첫 번째 값만 가져오는 것을 볼 수 있습니다. a의 아래가 비었기 때문에 나, 다, 라의 값은 연산을 하지 못하게 됩니다.
이때 피벗테이블 디자인을 변경하면 간단하게 해결할 수 있습니다.
피벗테이블 디자인 ▶ 보고서 레이아웃 ▶ 모든 항목 레이블 반복을 누르면 다음과 같이 값이 채워지면서 올바른 결과가 나오는 것이 가능합니다.
반응형
a의 값이 아래로 채워지면서 올바른 결과가 나오는 것을 볼 수 있습니다.
파워 쿼리를 이용한 빈칸 채우기
이번에는 파워 쿼리를 이용한 빈칸 채우는 방법을 알아보도록 하겠습니다.
다시 최초의 데이터를 사용해 보도록 하겠습니다. 데이터를 파워쿼리 편집기로 올려서 작업해 보도록 하겠습니다.
