Computer >> 컴퓨터 >  >> 소프트웨어 >> Office

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

엑셀로 데이터를 관리하다 보면 의도적으로 비워 둔 빈 셀이 있기 마련입니다. 하지만 이런 빈 셀은 데이터를 필터링하거나 정렬할 때 심각한 문제를 일으킬 수 있습니다. 예를 들어 매출액 기준으로 정렬하면 어떤 영업 담당자의 실적인지 알 수 없게 되죠. 따라서 작업 전에 빈 셀을 미리 채워 두는 것이 좋습니다. 이 글에서는 엑셀에서 빈 셀을 쉽고 빠르게 채우는 4가지 방법을 소개합니다.

엑셀에서 빈 셀을 채우는 4가지 방법

예제로 사용할 데이터셋은 영업 담당자들의 월별 판매 수량과 금액을 나타내고 있습니다. 그런데 영업 담당자(Sales Representative) 열에 빈 칸이 여러 곳 있는 것을 볼 수 있습니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

이 예제에서 B열의 값은 여러 행에 걸쳐 적용되는 구조입니다. 이런 방식은 눈으로 보기에는 편리합니다. 예를 들어 Jane의 1월, 2월, 3월 판매 실적을 한눈에 찾을 수 있죠. 하지만 이런 목록을 정렬하면 문제가 발생합니다. 빈 셀 때문에 데이터가 뒤섞여 누가 무엇을 판매했는지 구분할 수 없게 됩니다. 아래는 금액(Amount) 기준으로 정렬한 결과입니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

정렬 후에는 해당 열에 빈 칸이 있었기 때문에 영업 담당자를 확인할 수 없습니다. 지금부터 이 문제를 해결하는 4가지 방법을 하나씩 살펴보겠습니다.

방법 1: 채우기(Fill) 명령으로 빈 셀 채우기

데이터 양이 적다면 위쪽 셀의 값을 직접 입력하거나 채우기 명령을 활용하는 것이 가장 간단합니다.

실행 단계:

  • B6 셀을 선택한 후 홈 ➪ 편집 ➪ 채우기 ➪ 아래로 채우기를 클릭하거나 단축키 Ctrl + D를 누릅니다. 그러면 B6 셀에 "Jane"이 자동 입력됩니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

셀이 위쪽 값으로 채워진 것을 확인할 수 있습니다.

  • 나머지 빈 셀도 같은 방식으로 반복해서 채웁니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

  • 한 셀씩 선택하기 번거롭다면 값을 가진 셀을 선택한 뒤 셀 오른쪽 아래의 플러스(+) 아이콘(채우기 핸들)을 더블클릭하세요.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

그러면 순식간에 모든 빈 셀이 같은 값으로 채워집니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

완성된 최종 결과물은 다음과 같습니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

방법 2: 이동 옵션(Go To Special) 도구로 빈 셀 채우기

데이터가 많다면 일일이 손으로 채울 수 없습니다. 이럴 때는 이동 옵션(Go To Special) 도구와 간단한 수식을 조합하면 효율적입니다.

실행 단계:

  • 빈 셀이 포함된 범위를 선택합니다(예제에서는 B5:B16).
  • 홈 ➪ 편집 ➪ 찾기 및 선택 ➪ 이동 옵션을 클릭하면 대화상자가 나타납니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

  • "빈 셀(Blanks)" 옵션을 선택하고 확인을 누르면 범위 내 모든 빈 셀이 한꺼번에 선택됩니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

  • 이어서 등호(=)를 입력하고 같은 열에서 첫 번째 데이터 셀의 주소를 입력합니다.
=B5
  • 마지막으로 Ctrl + Enter를 누릅니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

그러면 다음과 같이 빈 셀이 모두 원하는 값으로 채워집니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

단, 방금 채운 셀들을 선택해 보면 수식 표시줄에 값이 아닌 "=B5" 형태의 수식이 남아 있습니다. 값 자체를 저장하려면 추가 작업이 필요합니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

  • 처음 선택했던 원래 범위를 다시 선택하고 Ctrl + C로 복사합니다.
  • 홈 ➪ 클립보드 ➪ 붙여넣기 ➪ 값 붙여넣기(Paste Values)를 실행하여 수식을 값으로 변환합니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

모든 단계를 완료하면 빈 칸이 올바른 정보로 채워진 결과를 얻을 수 있습니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

방법 3: 파워 쿼리(Power Query) 편집기 활용하기

대용량 데이터셋이라면 엑셀의 파워 쿼리 편집기를 사용하는 것도 훌륭한 선택입니다.

실행 단계:

  • 데이터셋 안의 아무 셀이나 클릭합니다.
  • 데이터 ➪ 테이블/범위에서(From Table/Range)를 클릭합니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

  • 데이터 범위가 자동으로 선택되므로 확인을 누르면 됩니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

잠시 후 아래 이미지와 같은 파워 쿼리 편집기 화면이 열립니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

  • 담당자 이름 열에서 마우스 오른쪽 버튼을 클릭하고 바로 가기 메뉴에서 채우기(Fill) > 아래로(Down)를 선택합니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

  • 곧바로 셀들이 채워집니다. 이제 닫기 및 로드(Close & Load)를 클릭하세요.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

몇 초 후 새 시트에 채워진 데이터가 생성됩니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

방법 4: VBA 매크로로 빈 셀 채우기

간단한 매크로 하나면 이 작업을 매우 빠르게 처리할 수 있습니다. VBA에 익숙하다면 이 방법이 가장 좋습니다.

실행 단계:

  • Alt + F11을 눌러 VBA 편집기를 엽니다.
  • 삽입 ➪ 모듈(Module)을 클릭해 새 모듈을 만듭니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

  • 모듈에 아래 코드를 입력합니다.
Sub Fill_Blank_Cells()
Dim BCells As Range
For Each BCells In Selection.Cells
If BCells.Value = "" Then
BCells.FillDown
End If
Next BCells
End Sub
  • 코드 입력 후 워크시트로 돌아갑니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

코드 설명:

  • 먼저 Sub 프로시저인 Fill_Blank_Cells()를 생성합니다.
  • 변수 BCells를 Range 유형으로 선언합니다.
  • For 루프와 If 문을 사용해 빈 셀을 찾아 위쪽 값으로 채웁니다.
  • 이후 셀 범위를 선택하고 개발 도구 ➪ 매크로(Macros)를 클릭합니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

  • 목록에서 매크로 이름을 선택하고 실행(Run)을 누르면 끝입니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

원하던 결과가 완성되었습니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

빈 셀에 0 또는 대시(-) 입력하기

빈 셀에 0 또는 대시(-)를 넣고 싶다면 찾기 및 바꾸기 도구가 가장 효과적입니다. 찾을 내용 상자는 비워 두고, 바꿀 내용 상자에 0 또는 대시를 입력하면 됩니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

여기서는 0을 사용했지만, 같은 방법으로 대시(-)를 입력할 수도 있습니다.

왼쪽 셀의 값으로 빈 셀 채우기

왼쪽 셀의 값을 가져와 빈 셀을 채워야 한다면 앞서 소개한 4가지 방법을 가로 방향으로 응용하면 됩니다. 절차는 동일하며, 셀을 세로가 아닌 가로로 선택하기만 하면 됩니다. 아래는 첫 번째 방법의 채우기(Fill) 명령을 가로 방향으로 적용한 예시입니다.

엑셀 데이터 정리의 핵심: 빈 셀을 한 번에 채우는 4가지 방법

마무리

지금까지 엑셀에서 빈 셀을 채우는 다양한 방법을 살펴보았습니다. 데이터 크기와 상황에 따라 채우기 명령, 이동 옵션, 파워 쿼리, VBA 중 가장 적합한 방법을 골라 활용해 보세요. 궁금한 점이 있다면 댓글로 남겨 주세요. 즐거운 엑셀 되세요 🙂

함께 읽으면 좋은 글

  • 엑셀에서 인접하지 않은 셀 선택하기 (5가지 방법)
  • 엑셀에서 여러 셀 선택하는 7가지 방법
  • 이동 옵션(Go To Special)으로 빈 셀 채우기 (예제 3가지)
  • 엑셀에서 빈 셀에 색상 채우기 (5가지 방법)
  • 위쪽 셀 값으로 빈 셀 자동 채우기 (5가지 방법)
  • 왼쪽 셀 값으로 빈 셀 채우기 (4가지 방법)
  • 엑셀에서 빈 칸 아래로 채우기 (4가지 빠른 방법)