엑셀 빠른채우기 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 모든 강의 예제파일은 여러분께 무료로 제공됩니다.
- [기초입문] 엑셀 빠른채우기의 모든 것예제파일[기초입문] 엑셀 빠른채우기의 모든 것완성파일
호환성
운영체제 호환성 Windows 버전 엑셀 2013 이후 버전에서 사용 가능합니다. Mac 버전 Mac용 엑셀 2013 이후 버전에서 사용 가능합니다. 엑셀 빠른채우기란 '채우기 된 범위의 왼쪽으로 입력된 기존 데이터의 패턴을 분석하여 나머지 값을 자동으로 채워주는 기능'으로 엑셀 2013 이후 버전을 사용중인 실무자라면 반드시 알아야 할 엑셀 필수 기능 중 하나입니다.
빠른채우기를 잘 활용한다면 엑셀에서 제공하는 대부분의 텍스트 관련 함수(예: TEXT 함수, MID 함수, CONCAT 함수 등..)을 모두 대체할 수 있을 정도로 간단하면서 아주 유용한 기능인데요. 사용방법은 아주 간단합니다.
- 예제파일의 [1] 시트로 이동합니다. T6:U12 범위에 직원명과 영문이름이 적힌 표가 있습니다. 직원명에서 '영문이름'을 빠른채우기로 추출해보겠습니다.
직원명에서 영문이름을 추출합니다. - U7셀을 선택한 뒤, 첫번째 직원의 영문이름인 Stephen을 입력합니다.
첫번째 직원의 영문이름을 입력합니다. - U8셀을 선택한 뒤, [홈] - [채우기] - [빠른채우기]를 클릭하거나 단축키 Ctrl + E 를 누르면 빠른채우기가 실행되며 나머지 직원의 영문이름이 자동으로 채워집니다.
아래셀로 이동한 뒤, Ctrl + E 를 누르면 나머지 직원의 영문 이름이 자동으로 입력됩니다.
빠른채우기 사용 시 참고사항 4가지
빠른채우기는 매우 간단한 기능이지만 사용 전 알아두어야 할 4가지 참고사항이 있습니다.
ㄱ. 빠른채우기는 세로방향으로만 사용할 수 있습니다.
빠른채우기는 세로방향으로만 동작합니다. 따라서 가로방향으로 패턴을 인식하여 빠른채우기 해야 할 경우에는 행/열 전환 기능을 사용하여 데이터를 세로방향으로 전환한 뒤, 빠른채우기를 사용합니다.
- 예제파일의 [1] 시트로 이동합니다. W6:AC7 범위에 입력된 직원명(나이)에서 나이만 추출하려고 합니다. 하지만 직원명이 가로로 입력되어 빠른채우기를 사용할 수 없습니다. 따라서 행/열 전환으로 데이터를 세로방향으로 변경하겠습니다. W6:AC7 범위를 복사합니다.
빠른채우기를 사용하려면 가로방향에서 세로방향으로 데이터를 변경해야 합니다. - W9셀을 선택한 뒤, 우클릭 - 선택하여 붙여넣기로 이동한 후, [행/열 전환]을 선택합니다. 이후 확인을 누르면 데이터가 세로방향으로 전환됩니다.
선택하여 붙여넣기 - 행열전환으로 붙여넣기합니다. - 첫번째 직원의 나이인 39를 입력한 뒤, 밑에 셀로 이동한 후 Ctrl + E 를 눌러 빠른채우기 하면 나머지 직원의 나이가 자동으로 입력됩니다.
직원의 나이를 빠른채우기 합니다. - 다시 범위를 복사한 뒤, 붙여넣기 하고자 하는 범위를 우클릭 - 선택하여 붙여넣기로 이동합니다. 이후 '행/열전환'으로 붙여넣기 하면 가로방향으로 입력된 데이터 가공이 완료되었습니다.
다시 행열전환으로 붙연허기 하면 가로방향 빠른채우기가 완료됩니다.
ㄴ. 빠른채우기는 연속 된 데이터의 패턴만 분석할 수 있습니다.
빠른채우기 기능은 연속된 데이터의 패턴만 분석합니다. 따라서 빠른채우기 된 범위와 패턴이 입력된 범위가 떨어져 있을 경우에는 빠른채우기 패턴을 분석할 수 없다는 오류메시지가 출력됩니다.
- 예제파일의 [1] 시트로 이동한 뒤, T열과 U열 사이에 새로운 열을 추가하여 기존의 직원명/영문이름 표를 분리합니다.
두 데이터 사이에 새로운 열을 추가합니다. - 첫번째 직원의 영문이름인 Stephen을 입력한 뒤, 빠른채우기 하면 빠른채우기 패턴을 인식할 수 없다는 오류메시지가 나오며 빠른채우기를 할 수 없습니다.
채우기 패턴을 인식할 수 없다는 오류메시지가 나옵니다. - 그럴 경우 두 데이터 사이에 열을 삭제하여 다시 연결하거나, 또는 두 데이터 사이에 아무런 값이나 입력하여 연결된 데이터로 만든 뒤 빠른채우기를 실행합니다.
두 데이터 사이에 아무런 값을 입력하여 연결한 뒤에 빠른채우기 합니다.
ㄷ. 빠른채우기 할 대상의 패턴이 충분하지 않을 경우, 잘못된 형태로 빠른채우기 될 수 있습니다.
빠른채우기는 왼쪽방향으로 입력된 데이터의 패턴과 현재 빠른채우기 될 필드의 데이터 패턴을 분석하여 나머지 값을 자동채우기 합니다. 따라서 기존 데이터의 패턴이 중복되거나 부족할 경우, 또는 패턴을 인식할 수 없을 경우 빠른채우기로 옳지 않은 결과가 반환될 수 있습니다.
첫번째 데이터의 패턴이 부족하여 빠른채우기 된 값의 세번째 방문지가 누락되었습니다. ㄹ. 빠른채우기 미리보기가 나오지 않을 경우, 설정에서 활성화 할 수 있습니다.
빠른채우기 기능을 사용하면 빠른채우기로 출력될 결과를 미리 확인할 수 있습니다. 만약 빠른채우기 미리보기가 보이지 않을 경우, 설정에서 '빠른 자동채우기' 기능을 활성화하면 빠른채우기 미리보기를 볼 수 있습니다.
파일 - 옵션 - 고급 에서 '빠른 자동채우기' 옵션을 활성화합니다. 엑셀 빠른채우기 실전 사용예제
엑셀 빠른채우기 기능을 사용하면 다양한 상황에서 데이터를 아주 손쉽고 빠르게 가공하고 편집할 수 있는데요. 이번 강의에서는 실무에서 자주 발생하는 5개의 실전 예제를 바탕으로 빠른채우기 사용법을 하나씩 살펴보겠습니다. 빠른채우기 기능을 실제 적용하는 방법은 영상강의를 참고해주세요.
전화번호 가운데 4자리를 별표로 숨기기
전화번호의 가운데 4자리를 별표로 숨깁니다. 메일 주소의 도메인과 앞 3자리만 남기고 나머지는 별표로 숨기기
메일주소의 도메인과 앞 3글자만 제외하고 나머지는 별표로 숨깁니다. 국가번호를 포함한 형태로 전화번호 일괄 변경
전화번호 형식을 일괄 변경합니다. 영문이름 참조하여 이메일 주소 일괄 생성
직원 영문이름을 참조하여 이메일을 일괄 생성합니다. 
빠른채우기로 숫자만 추출하여 바로 계산에 사용할 수 있습니다. 
엑셀 빠른채우기로 괄호 안과 밖의 데이터를 추출하거나 치환합니다. 빠른채우기 기능으로 텍스트 나누기
입력된 문자열의 특정 부분만 빠르게 추출할 수 있습니다. 빠른채우기 기능으로 텍스트 합치기
여러개로 나누어진 값을 하나의 문자열로 합칠 수 있습니다. 엑셀 함수를 사용하여 텍스트 합치기 자동화하기
사용중인 엑셀 버전에따라 PHONETIC 함수, TEXTJOIN 함수 등을 사용하면 텍스트 합치기를 실시간으로 자동화할 수 있습니다. 엑셀 버전별로 사용가능한 텍스트합치기 자동화 방법은 아래 영상강의를 참고하세요.
엑셀 빠른채우기를 데이터모델 피벗테이블과 같이 응용하면 복잡한 형태의 데이터 가공 작업도 아주 손쉽게 할 수 있습니다. 이번에는 실전 예제로 각 동네/세대주별 세대 정보를 하나의 레이블로 취합해야 하는 경우, 피벗테이블과 빠른채우기 기능을 사용하여 손쉽게 해결하는 방법을 알아보겠습니다.
빠른채우기와 피벗테이블로 동네/세대주별 레이블을 제작합니다. 데이터모델 피벗테이블을 사용하여 피벗테이블 안에 텍스트를 출력하는 방법은 아래 영상강의를 참고해주세요.
- 예제파일의 [7] 시트로 이동한 뒤, C4:G22 범위를 선택합니다. [삽입] - [피벗테이블]을 선택하거나 단축키 Alt - N - V 로 피벗테이블 만들기 대화상자를 실행한 뒤, '데이터 모델에 이 데이터 추가'를 활성화 한 후 [확인] 버튼을 눌러 새로운 피벗테이블을 생성합니다.
*"데이터 모델에 이 데이터 추가" 를 반드시 체크해주셔야 합니다. (파워쿼리가 지원되는 엑셀 2013 이후 버전에서만 사용 가능합니다)
데이터모델 피벗테이블을 생성합니다. - 피벗테이블 필드 목록 - 범위 테이블을 우클릭한 뒤 '측정값 추가'를 클릭합니다.
피벗테이블에 측정값을 추가합니다. - 아래와 그림과 같이 CONCATENATEX 함수를 이용하여 '범위' 테이블의 '이름', '전화번호'를 출력하는 측정값 2개를 생성합니다.
*테이블 이름(=예제에서는 '범위')는 생성된 테이블 이름에 따라 적절히 수정합니다.측정값 이름 : 이름텍스트
수식 : =CONCATENATEX('범위','범위'[이름])측정값 이름 : 전화번호텍스트
수식 : =CONCATENATEX('범위','범위'[전화번호])
이름텍스트, 전화번호텍스트 측정값을 추가합니다. - 측정값 추가가 완료되었으면, 아래와 같이 피벗테이블 필드를 설정합니다.
(*순서도 동일하게 추가합니다)열 : 세대주와의관계, 값
행 : 동네, 세대주
값 : 이름텍스트, 전화번호텍스트
피벗테이블 필드를 이동합니다. - 피벗테이블을 선택한 뒤, [디자인]으로 이동하여 '보고서 레이아웃'을 테이블형식으로, 총합계를 모두 해제하면 피벗테이블이 보다 깔끔하게 변경됩니다.
동네/세대주별 데이터를 나타내는 피벗테이블 생성이 완료되었습니다. - 이후 피벗테이블 우측으로 출력하고자 하는 형태의 첫번째 값을 입력합니다.
피벗테이블 우측으로 첫번째 레이블을 입력합니다. - 아래 셀로 이동한 뒤, 단축키 Ctrl + E 를 눌러 빠른채우기 하면 동네/세대주별 정보가 한 번에 정리되었습니다.
Ctrl + E 로 빠른채우기 하면 동네/세대주별 레이블 생성이 완료됩니다.
