[ 엑셀 Tip ] 엑셀 빈칸 채우기 ( 상위 값을 아래로 채우기 )

엑셀 상위 값과 같은 값으로 아래 빈칸을 채우는 방법을 알아보도록 하겠습니다. 

피벗테이블을 만들거나 sumif 함수를 사용하기 위해서는 빈칸이 있으면 제한이 되는데 이럴 때 사용하는 방법입니다.

그리고 피벗테이블에서 다시 함수를 사용해서 값을 구할 때도 사용할 수 있습니다.

다음과 같이 누군가가 보기 좋게 만들어 놓은 가공의 데이터를 만들어 보았습니다.

그렇게 보기 좋지도 않지만 빈칸은 데이터를 분석하는 과정에서 곤란하게 만드는 가장 큰 요인입니다.

엑셀에는 빈칸을 "0"으로 인식하지만 다른 분석 Tool에서는 Null 값으로 이건 데이터가 있는 것도 없는 것도 아닌 상황이 발생하게 됩니다.

엑셀에서 빈칸 채우는 방법

 

엑셀에서 빈칸을 채우는 방법입니다. 비어있는 칸은 아마 데이터를 만든 사람이 위의 값과 동일하기 때문에 배려하기 위해서 친절하게 비워 둔 것으로 보입니다.

• 채우기 기능 활용

구분을 선택 해 준 후 단축키 Ctrl + G를 눌러 줍니다. 이동의 탭으로 이동하게 되고 옵션을 눌러 줍니다.

다양한 이동 옵션이 있지만 빈셀만 선택을 하고 싶기 때문에 빈셀을 클릭해 주도록 하겠습니다. 그럼 오른쪽과 같이 빈센에 음영이 표기되는 것을 볼 수 있습니다. 

빈셀만 자동으로 선택을 해주었습니다.

이때 "+"를 눌러 줍니다. 그리고 화살표 "▲"을 누른 후 Ctrl을 누른 상태로 Enter를 눌러 줍니다.

오른쪽과 같은 결과가 나오게 되었습니다. sumif 함수를 한번 해 보도록 하겠습니다.

구분은 a, b, c, d로 이루어져 있는데 해당 값은 합은 총계와 같이 되는 것을 볼 수 있습니다.

※ 빈칸을 두는 것보다 조금 더 좋은 방법 ( 추천은 하지 않음 )

데이터는 있는 상태지만 아까와 같아 보이게 만들어 보았습니다. 무엇을 변경하였을까요?

글씨를 흰색으로 변경했습니다. 보는 사람이 이러한 양식을 좋아한다면 빈칸을 주는 것보다 나중에 데이터 관리하기 용이합니다.
하지만 결국 이것도 시간이 지나면 내가 색을 변경했는지 알 수가 없어지기 때문에 하지 않으셨으면 좋겠습니다.

---

피벗테이블에서 빈칸 채우기

피벗테이블을 만들어서 아까와 같은 테이블로 만들어 보도록 하겠습니다.

피벗테이블 레이아웃을 다음과 같이 변경했더니 아까와 같이 구분에 a, b, c, d 아래가 비어 있는 것을 볼 수 있습니다.

피벗테이블은 집계를 하기 때문에 해당 데이터를 두고 중구분의 값은 빈 값처럼 표시하게 됩니다. 

피벗테이블에서 Dax를 사용하지 않는다면 피벗테이블에서 연산을 하고 싶으면 sumif 함수가 유용하기 때문에 구분의 아래 값을 채워 놓는 것이 좋을 것 같습니다.

일단 지금 상태에서 sumif 함수를 사용해 보도록 하겠습니다.

다음과 같이 a의 모든 값이 아닌 첫 번째 값만 가져오는 것을 볼 수 있습니다. a의 아래가 비었기 때문에 나, 다, 라의 값은 연산을 하지 못하게 됩니다. 

이때 피벗테이블 디자인을 변경하면 간단하게 해결할 수 있습니다.

피벗테이블 디자인 ▶ 보고서 레이아웃 ▶ 모든 항목 레이블 반복을 누르면 다음과 같이 값이 채워지면서 올바른 결과가 나오는 것이 가능합니다.

반응형

a의 값이 아래로 채워지면서 올바른 결과가 나오는 것을 볼 수 있습니다.

파워 쿼리를 이용한 빈칸 채우기

 

이번에는 파워 쿼리를 이용한 빈칸 채우는 방법을 알아보도록 하겠습니다.

다시 최초의 데이터를 사용해 보도록 하겠습니다. 데이터를 파워쿼리 편집기로 올려서 작업해 보도록 하겠습니다.

[ 파워 BI ] 파워 쿼리를 이용한 데이터 합치기 ( 파일 합치기 )

파워쿼리에 대한 관심이 있는 분들은 편집기 사용은 위에 글을 참조해 주십시오.

파워 쿼리 편집기로 데이터를 업로드하였더니 엑셀에서는 빈칸이었는데 null 값으로 나오고 있습니다.

null 값은 값이 없는 상태이며 "0"이 아닌 그냥 데이터가 없는 상태를 의미합니다.

그럼 빈칸 null값을 채워 보도록 하겠습니다.

구분에서 우클릭을 하면 선택하는 항목이 나오는데 여기서 채우기를 누르면 2가지 옵션이 나옵니다.

그냥 글자 그대로 인식하시면 됩니다. 

아래로는 위의 데이터를 기준으로 그 값을 아래로 채우는 것이고 위로는 아래 데이터를 기준으로 아래 데이터를 위로 채우는 방법입니다.

• 아래로

아까와 동일한 결과로 올바른 결과를 얻기 위한 방법입니다.

• 위로

참고로 봐주시면 됩니다. a를 기준으로 하는 것이 아닌 b의 값이 a 방향으로 채워지는 것을 볼 수 있습니다.

알고 계시면 언젠가는 사용할 수 있지 않을까 생각합니다.

( 아직 저는 이용한 적은 없는 것 같습니다 )

닫기 및 다음으로 로드 후 표를 선택 후 기존 워크시트에 넣어서 기존 결과와 비교해 보겠습니다.

표 모양으로 결과가 나오지만 동일한 결과가 나오는 것을 알 수 있습니다.

피벗테이블을 선택하면 바로 피벗테이블을 사용하는 것이 가능하니 필요에 따라 사용하시면 될 것 같습니다.