엑셀 빠른채우기의 모든 것

엑셀 빠른채우기 가이드

엑셀 빠른채우기는 '채우기 범위의 왼쪽에 입력된 기존 데이터의 패턴을 분석해 나머지 값을 자동으로 채워주는 기능'으로, 엑셀 2013 이후 버전을 사용하는 실무자라면 반드시 익혀야 할 필수 기능 중 하나입니다.

빠른채우기를 잘 활용하면 엑셀에서 제공하는 대부분의 텍스트 관련 함수(예: TEXT 함수, MID 함수, CONCAT 함수 등)를 대체할 수 있을 만큼 간단하고 강력합니다. 사용 방법도 다음과 같이 매우 간단합니다.

1. 예제파일의 [1] 시트로 이동합니다. T6:U12 범위에 직원명과 영문이름이 입력된 표가 있는데, 직원명에서 '영문이름'만 빠른채우기로 추출해 보겠습니다.
2. U7셀을 선택한 뒤 첫 번째 직원의 영문이름인 Stephen을 입력합니다.
3. U8셀을 선택한 뒤 [홈] - [채우기] - [빠른채우기]를 클릭하거나 단축키 Ctrl + E 를 누르면 빠른채우기가 실행되어 나머지 직원의 영문이름이 자동으로 채워집니다.

빠른채우기 사용 시 참고사항 4가지

빠른채우기는 매우 간단한 기능이지만, 사용하기 전에 반드시 알아두어야 할 4가지 참고사항이 있습니다.

1. 빠른채우기는 세로 방향으로만 동작합니다.

빠른채우기는 세로 방향으로만 동작합니다. 따라서 가로 방향으로 입력된 데이터에 빠른채우기를 적용하려면, 행/열 전환 기능으로 데이터를 세로 방향으로 변환한 뒤 사용해야 합니다.

1. 예제파일의 [1] 시트로 이동합니다. W6:AC7 범위에 입력된 직원명(나이)에서 나이만 추출해 보겠습니다. 다만 직원명이 가로로 입력되어 있어 빠른채우기를 곧바로 사용할 수 없으므로, 행/열 전환으로 데이터를 세로 방향으로 변경하겠습니다. 먼저 W6:AC7 범위를 복사합니다.
2. W9셀을 선택한 뒤 우클릭 - [선택하여 붙여넣기]로 이동한 후 [행/열 전환]을 선택합니다. 확인을 누르면 데이터가 세로 방향으로 전환됩니다.
3. 첫 번째 직원의 나이인 39를 입력한 뒤, 아래 셀로 이동해 Ctrl + E 를 누르면 나머지 직원의 나이가 자동으로 채워집니다.
4. 다시 결과 범위를 복사한 뒤, 붙여넣을 위치를 우클릭하여 [선택하여 붙여넣기]로 이동합니다. '행/열 전환'으로 붙여넣으면 가로 방향 데이터 가공이 완료됩니다.

2. 빠른채우기는 연속된 데이터의 패턴만 분석할 수 있습니다.

빠른채우기는 연속된 데이터의 패턴만 분석합니다. 따라서 빠른채우기를 적용할 범위와 패턴이 입력된 범위가 떨어져 있으면, 패턴을 분석할 수 없다는 오류 메시지가 나타납니다.

1. 예제파일의 [1] 시트로 이동한 뒤, T열과 U열 사이에 새로운 열을 추가하여 기존의 직원명/영문이름 표를 분리합니다.
2. 첫 번째 직원의 영문이름인 Stephen을 입력하고 빠른채우기를 실행하면, 패턴을 인식할 수 없다는 오류 메시지가 나타나며 빠른채우기가 동작하지 않습니다.
3. 이런 경우에는 두 데이터 사이의 열을 삭제해 다시 연결하거나, 두 데이터 사이에 임의의 값을 입력해 연속된 데이터로 만든 뒤 빠른채우기를 실행합니다.

3. 패턴이 충분하지 않으면 잘못된 결과로 채워질 수 있습니다.

빠른채우기는 왼쪽 방향에 입력된 데이터 패턴과 현재 채울 필드의 데이터 패턴을 함께 분석해 값을 자동으로 채웁니다. 따라서 기존 데이터의 패턴이 중복되거나 부족할 경우, 또는 패턴을 인식하기 어려울 경우 의도와 다른 결과가 반환될 수 있습니다.

4. 미리보기가 보이지 않으면 설정에서 활성화할 수 있습니다.

빠른채우기 기능을 사용하면 채워질 결과를 미리 확인할 수 있습니다. 만약 미리보기가 보이지 않는다면, 엑셀 옵션의 '빠른 자동 채우기' 기능을 활성화하면 미리보기를 정상적으로 확인할 수 있습니다.

엑셀 빠른채우기 실전 사용 예제

빠른채우기 기능을 활용하면 다양한 상황에서 데이터를 손쉽고 빠르게 가공·편집할 수 있습니다. 이번 강의에서는 실무에서 자주 마주치는 5개의 실전 예제를 통해 빠른채우기 사용법을 하나씩 살펴봅니다. 실제 적용 과정은 영상 강의를 함께 참고해 주세요.

전화번호 가운데 4자리를 별표로 숨기기

메일 주소의 도메인과 앞 3자리만 남기고 나머지를 별표로 숨기기

국가번호를 포함한 형태로 전화번호 일괄 변경

영문이름을 참조해 이메일 주소 일괄 생성

숫자만 빠르게 추출하기

괄호 안 텍스트 추출/치환하기

텍스트 나누기

텍스트 합치기

엑셀 함수로 텍스트 합치기 자동화하기

사용 중인 엑셀 버전에 따라 PHONETIC 함수, TEXTJOIN 함수 등을 활용하면 텍스트 합치기를 실시간으로 자동화할 수 있습니다. 엑셀 버전별로 사용 가능한 텍스트 합치기 자동화 방법은 아래 영상 강의를 참고해 주세요.

데이터모델 피벗테이블 활용하기

빠른채우기를 데이터 모델 피벗테이블과 함께 응용하면 복잡한 데이터 가공 작업도 손쉽게 처리할 수 있습니다. 다음 예제에서는 동네/세대주별 세대 정보를 하나의 레이블로 취합해야 하는 상황에서, 피벗테이블과 빠른채우기를 결합해 손쉽게 해결하는 방법을 알아봅니다.

데이터 모델 피벗테이블을 사용해 피벗테이블 안에 텍스트를 출력하는 방법은 아래 영상 강의를 참고해 주세요.

1. 예제파일의 [7] 시트로 이동한 뒤 C4:G22 범위를 선택합니다. [삽입] - [피벗테이블]을 선택하거나 단축키 Alt - N - V 로 피벗테이블 만들기 대화상자를 실행한 뒤, '데이터 모델에 이 데이터 추가'를 활성화한 후 [확인] 버튼을 눌러 새 피벗테이블을 생성합니다.
   *'데이터 모델에 이 데이터 추가'는 반드시 체크해야 합니다. (파워쿼리가 지원되는 엑셀 2013 이후 버전에서만 사용 가능합니다.)
2. 피벗테이블 필드 목록의 범위 테이블을 우클릭한 뒤 '측정값 추가'를 클릭합니다.
3. 아래 그림과 같이 CONCATENATEX 함수를 활용해 '범위' 테이블의 '이름'과 '전화번호'를 출력하는 측정값 2개를 생성합니다.
   *테이블 이름(예제에서는 '범위')은 실제 생성된 테이블 이름에 맞게 수정합니다.
   측정값 이름 : 이름텍스트
   수식 : =CONCATENATEX('범위','범위'[이름])
   측정값 이름 : 전화번호텍스트
   수식 : =CONCATENATEX('범위','범위'[전화번호])

   

4. 측정값 추가가 끝났으면, 아래와 같이 피벗테이블 필드를 설정합니다.
   (*순서도 동일하게 추가합니다.)
   열 : 세대주와의관계, 값
   행 : 동네, 세대주
   값 : 이름텍스트, 전화번호텍스트

   

5. 피벗테이블을 선택한 뒤 [디자인]으로 이동하여 '보고서 레이아웃'을 테이블 형식으로 바꾸고 총합계를 모두 해제하면 피벗테이블이 한층 깔끔하게 정리됩니다.
6. 이제 피벗테이블 오른쪽에 출력하고자 하는 형태의 첫 번째 값을 입력합니다.
7. 아래 셀로 이동해 단축키 Ctrl + E 를 누르면 동네/세대주별 정보가 한 번에 정리됩니다.