엑셀 빠른채우기, 함수보다 더 중요한 엑셀 필수 기능!

간단하면서도 매우 유용한, 엑셀 함수보다도 더 중요한 실무자 필수 기능! 빠른 채우기 실전예제 총정리

홈페이지 » 엑셀 빠른채우기, 함수보다 더 중요한 엑셀 필수 기능!

엑셀 빠른채우기 목차 바로가기
영상 강의
큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 모든 강의 예제파일은 여러분께 무료로 제공됩니다.

  • [기초입문] 엑셀 빠른채우기의 모든 것
    예제파일
  • [기초입문] 엑셀 빠른채우기의 모든 것
    완성파일

호환성
운영체제 호환성
Windows 버전 엑셀 2013 이후 버전에서 사용 가능합니다.
Mac 버전 Mac용 엑셀 2013 이후 버전에서 사용 가능합니다.

엑셀 빠른채우기란 무엇인가요?

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

빠른채우기를 잘 활용한다면 엑셀에서 제공하는 대부분의 텍스트 관련 함수(예: TEXT 함수, MID 함수, CONCAT 함수 등..)을 모두 대체할 수 있을 정도로 간단하면서 아주 유용한 기능인데요. 사용방법은 아주 간단합니다.

  1. 예제파일의 [1] 시트로 이동합니다. T6:U12 범위에 직원명과 영문이름이 적힌 표가 있습니다. 직원명에서 '영문이름'을 빠른채우기로 추출해보겠습니다.

    엑셀 빠른채우기 예제1
    직원명에서 영문이름을 추출합니다.

  2. U7셀을 선택한 뒤, 첫번째 직원의 영문이름인 Stephen을 입력합니다.

    빠른채우기 값 입력
    첫번째 직원의 영문이름을 입력합니다.

  3. U8셀을 선택한 뒤, [홈] - [채우기] - [빠른채우기]를 클릭하거나 단축키 Ctrl + E 를 누르면 빠른채우기가 실행되며 나머지 직원의 영문이름이 자동으로 채워집니다.

    빠른채우기 영문이름 추출
    아래셀로 이동한 뒤, Ctrl + E 를 누르면 나머지 직원의 영문 이름이 자동으로 입력됩니다.

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

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

ㄱ. 빠른채우기는 세로방향으로만 사용할 수 있습니다.

빠른채우기는 세로방향으로만 동작합니다. 따라서 가로방향으로 패턴을 인식하여 빠른채우기 해야 할 경우에는 행/열 전환 기능을 사용하여 데이터를 세로방향으로 전환한 뒤, 빠른채우기를 사용합니다.

  1. 예제파일의 [1] 시트로 이동합니다. W6:AC7 범위에 입력된 직원명(나이)에서 나이만 추출하려고 합니다. 하지만 직원명이 가로로 입력되어 빠른채우기를 사용할 수 없습니다. 따라서 행/열 전환으로 데이터를 세로방향으로 변경하겠습니다. W6:AC7 범위를 복사합니다.

    엑셀 빠른채우기 가로 범위
    빠른채우기를 사용하려면 가로방향에서 세로방향으로 데이터를 변경해야 합니다.

  2. W9셀을 선택한 뒤, 우클릭 - 선택하여 붙여넣기로 이동한 후, [행/열 전환]을 선택합니다. 이후 확인을 누르면 데이터가 세로방향으로 전환됩니다.

    범위 행열전환
    선택하여 붙여넣기 - 행열전환으로 붙여넣기합니다.

  3. 첫번째 직원의 나이인 39를 입력한 뒤, 밑에 셀로 이동한 후 Ctrl + E 를 눌러 빠른채우기 하면 나머지 직원의 나이가 자동으로 입력됩니다.

    범위 행열전환 빠른채우기
    직원의 나이를 빠른채우기 합니다.

  4. 다시 범위를 복사한 뒤, 붙여넣기 하고자 하는 범위를 우클릭 - 선택하여 붙여넣기로 이동합니다. 이후 '행/열전환'으로 붙여넣기 하면 가로방향으로 입력된 데이터 가공이 완료되었습니다.

    엑셀 가로방향 빠른채우기 완료
    다시 행열전환으로 붙연허기 하면 가로방향 빠른채우기가 완료됩니다.

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

빠른채우기 기능은 연속된 데이터의 패턴만 분석합니다. 따라서 빠른채우기 된 범위와 패턴이 입력된 범위가 떨어져 있을 경우에는 빠른채우기 패턴을 분석할 수 없다는 오류메시지가 출력됩니다.

  1. 예제파일의 [1] 시트로 이동한 뒤, T열과 U열 사이에 새로운 열을 추가하여 기존의 직원명/영문이름 표를 분리합니다.

    엑셀 새로운 열 추가
    두 데이터 사이에 새로운 열을 추가합니다.

  2. 첫번째 직원의 영문이름인 Stephen을 입력한 뒤, 빠른채우기 하면 빠른채우기 패턴을 인식할 수 없다는 오류메시지가 나오며 빠른채우기를 할 수 없습니다.

    패턴을 찾을 수 없습니다 오류
    채우기 패턴을 인식할 수 없다는 오류메시지가 나옵니다.

  3. 그럴 경우 두 데이터 사이에 열을 삭제하여 다시 연결하거나, 또는 두 데이터 사이에 아무런 값이나 입력하여 연결된 데이터로 만든 뒤 빠른채우기를 실행합니다.

    두 데이터 연결
    두 데이터 사이에 아무런 값을 입력하여 연결한 뒤에 빠른채우기 합니다.

ㄷ. 빠른채우기 할 대상의 패턴이 충분하지 않을 경우, 잘못된 형태로 빠른채우기 될 수 있습니다.

빠른채우기는 왼쪽방향으로 입력된 데이터의 패턴과 현재 빠른채우기 될 필드의 데이터 패턴을 분석하여 나머지 값을 자동채우기 합니다. 따라서 기존 데이터의 패턴이 중복되거나 부족할 경우, 또는 패턴을 인식할 수 없을 경우 빠른채우기로 옳지 않은 결과가 반환될 수 있습니다.

엑셀 빠른채우기 패턴 부족
첫번째 데이터의 패턴이 부족하여 빠른채우기 된 값의 세번째 방문지가 누락되었습니다.
ㄹ. 빠른채우기 미리보기가 나오지 않을 경우, 설정에서 활성화 할 수 있습니다. 

빠른채우기 기능을 사용하면 빠른채우기로 출력될 결과를 미리 확인할 수 있습니다. 만약 빠른채우기 미리보기가 보이지 않을 경우, 설정에서 '빠른 자동채우기' 기능을 활성화하면 빠른채우기 미리보기를 볼 수 있습니다.

엑셀 빠른 채우기 미리보기
파일 - 옵션 - 고급 에서 '빠른 자동채우기' 옵션을 활성화합니다.

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

엑셀 빠른채우기 기능을 사용하면 다양한 상황에서 데이터를 아주 손쉽고 빠르게 가공하고 편집할 수 있는데요. 이번 강의에서는 실무에서 자주 발생하는 5개의 실전 예제를 바탕으로 빠른채우기 사용법을 하나씩 살펴보겠습니다. 빠른채우기 기능을 실제 적용하는 방법은 영상강의를 참고해주세요.

ㄱ. 개인정보/민감한 정보 ***로 숨기기

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

엑셀 전화번호 숨기기
전화번호의 가운데 4자리를 별표로 숨깁니다.

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

엑셀 이메일 주소 숨기기
메일주소의 도메인과 앞 3글자만 제외하고 나머지는 별표로 숨깁니다.

ㄴ. 연락처 형식을 빠르게 일괄 변경하기

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

엑셀 전화번호 변경
전화번호 형식을 일괄 변경합니다.

영문이름 참조하여 이메일 주소 일괄 생성

엑셀 빠른채우기 메일주소 일괄 생성
직원 영문이름을 참조하여 이메일을 일괄 생성합니다.

ㄷ. 텍스트가 포함 된 숫자에서 숫자만 빠르게 추출하기

엑셀 숫자 추출 빠른채우기
빠른채우기로 숫자만 추출하여 바로 계산에 사용할 수 있습니다.

ㄹ. 괄호 안과 밖의 데이터를 치환하기

엑셀 괄호 데이터 추출 빠른채우기
엑셀 빠른채우기로 괄호 안과 밖의 데이터를 추출하거나 치환합니다.

ㅁ. 텍스트 나누기/합치기

빠른채우기 기능으로 텍스트 나누기

엑셀 주소지 추출
입력된 문자열의 특정 부분만 빠르게 추출할 수 있습니다.

빠른채우기 기능으로 텍스트 합치기

엑셀 빠른채우기 텍스트 합치기
여러개로 나누어진 값을 하나의 문자열로 합칠 수 있습니다.

엑셀 함수를 사용하여 텍스트 합치기 자동화하기

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

엑셀 빠른채우기 응용예제 (데이터모델 피벗테이블 응용)

엑셀 빠른채우기를 데이터모델 피벗테이블과 같이 응용하면 복잡한 형태의 데이터 가공 작업도 아주 손쉽게 할 수 있습니다. 이번에는 실전 예제로 각 동네/세대주별 세대 정보를 하나의 레이블로 취합해야 하는 경우, 피벗테이블과 빠른채우기 기능을 사용하여 손쉽게 해결하는 방법을 알아보겠습니다.

동네 세대주 별 레이블 출력
빠른채우기와 피벗테이블로 동네/세대주별 레이블을 제작합니다.

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

  1. 예제파일의 [7] 시트로 이동한 뒤, C4:G22 범위를 선택합니다. [삽입] - [피벗테이블]을 선택하거나 단축키 Alt - N - V 로 피벗테이블 만들기 대화상자를 실행한 뒤, '데이터 모델에 이 데이터 추가'를 활성화 한 후 [확인] 버튼을 눌러 새로운 피벗테이블을 생성합니다.
    *"데이터 모델에 이 데이터 추가" 를 반드시 체크해주셔야 합니다. (파워쿼리가 지원되는 엑셀 2013 이후 버전에서만 사용 가능합니다)

    피벗테이블 데이터 모델
    데이터모델 피벗테이블을 생성합니다.

  2. 피벗테이블 필드 목록 - 범위 테이블을 우클릭한 뒤 '측정값 추가'를 클릭합니다.

    피벗테이블 측정값 추가
    피벗테이블에 측정값을 추가합니다.

  3. 아래와 그림과 같이 CONCATENATEX 함수를 이용하여 '범위' 테이블의 '이름', '전화번호'를 출력하는 측정값 2개를 생성합니다.
    *테이블 이름(=예제에서는 '범위')는 생성된 테이블 이름에 따라 적절히 수정합니다.
    측정값 이름 : 이름텍스트
    수식 : =CONCATENATEX('범위','범위'[이름])
    측정값 이름 : 전화번호텍스트
    수식 : =CONCATENATEX('범위','범위'[전화번호])

    빠른채우기 측정값 추가
    이름텍스트, 전화번호텍스트 측정값을 추가합니다.

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

    피벗테이블 필드 설정
    피벗테이블 필드를 이동합니다.

  5. 피벗테이블을 선택한 뒤, [디자인]으로 이동하여 '보고서 레이아웃'을 테이블형식으로, 총합계를 모두 해제하면 피벗테이블이 보다 깔끔하게 변경됩니다.

    데이터모델 피벗테이블 생성 완료
    동네/세대주별 데이터를 나타내는 피벗테이블 생성이 완료되었습니다.

  6. 이후 피벗테이블 우측으로 출력하고자 하는 형태의 첫번째 값을 입력합니다.

    빠른채우기 첫 데이터 입력
    피벗테이블 우측으로 첫번째 레이블을 입력합니다.

  7. 아래 셀로 이동한 뒤, 단축키 Ctrl + E 를 눌러 빠른채우기 하면 동네/세대주별 정보가 한 번에 정리되었습니다.

    엑셀 빠른채우기 완료
    Ctrl + E 로 빠른채우기 하면 동네/세대주별 레이블 생성이 완료됩니다.

관련링크 : MS 홈페이지 빠른채우기 사용법 설명

4.8 24 투표
게시글평점
guest
44 댓글
Inline Feedbacks
모든 댓글 보기
san****
san****
2020년 11월 18일 11:03 오후
게시글평점 :
     

진짜 말도 안돼.......................감사합니다.

제이
제이
2020년 11월 19일 9:36 오전
게시글평점 :
     

30년 엑셀 써왔는데.. 이런 엄청난 기능이 있었군요. 한수 배워갑니다. 충성!

글래스
글래스
2020년 11월 19일 11:27 오전
게시글평점 :
     

우와 와전 대박!!! ㅠㅠㅠ

방구통통
방구통통
2020년 11월 20일 1:54 오전
게시글평점 :
     

유투브 영상 먼저 보고 왔습니다. 또 배웁니다! 고맙습니다!!

생맥한잔
생맥한잔
2020년 11월 20일 12:21 오후
게시글평점 :
     

열심히 배우겠습니다...감사합니다

미성
미성
2020년 11월 23일 4:01 오후
게시글평점 :
     

힘든방법으로 작업하던 것을 아주 간단히 작업하는 방법 하나 배우고 갑니다

너른내
너른내
2020년 11월 23일 4:33 오후
게시글평점 :
     

엑셀이 많이 발전했네요.......

무언
무언
2020년 11월 24일 10:31 오전
게시글평점 :
     

좋은 강의 감사합니다. 🙂
혹시 "빠른채우기 기능으로 텍스트 합치기" 예시가 맞는 것인지요.
방문지3이 있는 데이터도 있고 없는 데이터도 있어서 빠른 채우기가 예상대로 나오지 않는 것 같아서요.

무언
무언
2020년 12월 3일 4:14 오후
답글 남기기  오빠두엑셀

말씀하신대로 했더니 제대로 채워지네요. 감사합니다.
강의 스크린 캡쳐 화면에 Ctrl+E 하는 셀 시작 시점이 나와있었네요.
더 꼼꼼하게 읽어봐야겠습니다. 🙂

0ugi
0ugi
2020년 11월 25일 9:28 오전
게시글평점 :
     

ctrl + E 자동채우기라고 알고는 있었는데 사용법은 이제야 배웠습니다 ㅋㅋ 감사합니다!!

라미만
라미만
2020년 11월 25일 4:26 오후
게시글평점 :
     

완젼..잘배우고 갑니다 ~~ㅎㅎ

Stranger
Stranger
2020년 11월 25일 7:34 오후
게시글평점 :
     

와.. 잘배웠습니다. 마지막 응용까지 좋네요 ㅎㅎ

qkdwns****
qkdwns****
2020년 11월 28일 10:14 오후
게시글평점 :
     

좋은 강의 감사합니다

찐이야
찐이야
2020년 12월 22일 6:23 오후
게시글평점 :
     

최고입니다^^

나실인
나실인
2021년 1월 6일 5:26 오후
게시글평점 :
     

빠른 채우기의 이런 활용팁이 있었다니,, 감탄했습니다. 실습하는 도중에 몇가지 설명하신 내용과 다르게 값이 나오는 경우가 있네요. 오피스 버전 차이려나요? (사용버전 office2016 pro plus) 1) 전화번호가 02인데 2 ,1 , 8,… 더보기 »

전근수
전근수
2021년 1월 12일 3:09 오후
게시글평점 :
     

저는 왜 피벗 테이블에서 이름텍스트랑 전화번호 텍스트를 추가하면 ***요약이라는 행이 하나더 생기는 걸까요??

star
star
2021년 1월 23일 1:14 오전
게시글평점 :
     

엄청난 꿀팁이네요. 잘 배우고 갑니다. 감사합니다^^

맥심커피
맥심커피
2021년 1월 25일 9:43 오전
게시글평점 :
     

감사합니다

아해들
아해들
2021년 1월 28일 1:52 오후
게시글평점 :
     

아.... 엑셀버전이 낮아서 ......
다음 기회에 꼭 활용하겠습니다. 감사합니다.

매니동
매니동
2021년 2월 12일 6:24 오후
게시글평점 :
     

와 ... 진짜 미뗬네요 .. 최고에요 !!

바보야
바보야
2021년 2월 19일 10:16 오전
게시글평점 :
     

너무 재미있습니다.

bj00902
bj00902
2021년 3월 10일 6:16 오전
게시글평점 :
     

감사합니다.

얼큰이
얼큰이
2021년 3월 24일 9:37 오후
게시글평점 :
     

너무 좋은 내용 감사해요.

Indi
Indi
2021년 4월 6일 6:42 오전
게시글평점 :
     

이제서야 이 강의를 보다니 ㅠㅠ. 정말 감사드립니다.

과유불급
과유불급
2021년 4월 9일 5:49 오후
게시글평점 :
     

이렇게 쉽게 할 수 있는 것이군요 ^^

난다고
난다고
2021년 7월 5일 12:46 오후
게시글평점 :
     

강의 정말 감사드립니다!
근데 예제파일이 받아지질 않아요. ㅠㅠ

난다고
난다고
2021년 7월 5일 12:50 오후
게시글평점 :
     

아 업무용 PC라 구글드라이브에 접속이 안되는 문제때문에 그러네요.
집에서 따로 받아서 공부하겠습니다~~

민지아빠
민지아빠
2021년 8월 9일 7:11 오후
게시글평점 :
     

피봇으로 이런 것도 하네요 또 배웁니다.

우슉
우슉
2021년 9월 14일 10:17 오후
게시글평점 :
     

최고에요!!!

pyou****
pyou****
2021년 9월 25일 12:44 오전
게시글평점 :
     

좋은정보 감사해요

미아리짱
미아리짱
2021년 10월 1일 11:58 오전
게시글평점 :
     

우아 진짜 어마어마합니다.
엑셀의 신세계를 너무 많이 배우고 있습니다
오빠두 정말 완짱~~~~

나도_엑셀
나도_엑셀
2021년 10월 6일 3:21 오후
게시글평점 :
     

굿!

tmddm****
tmddm****
2021년 10월 10일 4:58 오전
게시글평점 :
     

대한민국 엑셀의 선한신~^^이십니다~!!!

나무잎새
나무잎새
2021년 10월 13일 4:10 오후
게시글평점 :
     

감사합니다.

jony
jony
2021년 10월 17일 6:58 오후
게시글평점 :
     

아주 좋은 기능이네요^^

태백
태백
2021년 11월 6일 10:51 오후
게시글평점 :
     

잘 배우겠습니다.

떠연
떠연
2021년 11월 19일 6:18 오전
게시글평점 :
     

감사합니다!

하유네비
하유네비
2021년 11월 20일 5:31 오후
게시글평점 :
     

감사합니다

만수무강짱
만수무강짱
2021년 11월 22일 11:17 오전
게시글평점 :
     

감사합니다

토백이
토백이
2021년 11월 28일 1:56 오후
게시글평점 :
     

빠른 채우기 정말 좋은 기능이네요~
잘 배우고 갑니다^^

44
0
여러분의 생각을 댓글로 남겨주세요.x