엑셀 필수 기초 (자동채우기, 데이터구조, 피벗테이블)

 

회사에서 받은 엑셀 파일을 열었는데, 수백 줄짜리 데이터에서 특정 항목만 골라내야 할 때 막막했던 경험이 있습니다. 저도 처음엔 함수를 몰라서 안 된다고 생각했는데, 실제로 파고들어 보니 문제는 함수가 아니라 데이터를 다루는 방식에 있었습니다. 기초 기능 몇 가지만 제대로 이해해도 반복 작업을 크게 줄일 수 있었고, 그 과정을 정리해 봤습니다.

자동 채우기와 빠른 채우기로 반복 작업 줄이기

엑셀에서 가장 먼저 익혀야 할 기능을 하나만 꼽으라면, 저는 자동 채우기(AutoFill)라고 말하겠습니다. 자동 채우기란 셀 오른쪽 하단의 채우기 핸들을 드래그하거나 더블클릭해 수식이나 데이터를 아래쪽으로 한 번에 복사하는 기능입니다. 수량과 단가를 곱하는 수식을 첫 번째 행에 입력한 뒤, 채우기 핸들을 더블클릭하면 인접한 데이터가 있는 마지막 행까지 수식이 자동으로 채워집니다.

제가 직접 써봤는데, 100줄짜리 자료를 한 줄씩 입력하던 시간이 더블클릭 한 번으로 사라졌습니다. 처음엔 이게 되는 건지 반신반의했는데 실제로 확인해 보고 나서야 그동안 왜 이걸 몰랐나 싶었습니다.

날짜 데이터도 마찬가지입니다. 1월 1일을 입력하고 자동채우기를 실행하면 1월 2일, 1월 3일 순으로 채워집니다. 격일 간격이 필요하면 1월 1일과 1월 3일 두 셀을 먼저 입력한 뒤 범위를 선택해 채우기 핸들을 아래로 끌면 엑셀이 패턴을 인식해 이틀 간격으로 채워줍니다. 평일만 입력하고 싶을 때는 자동 채우기 실행 후 오른쪽 하단에 나타나는 옵션 버튼에서 '평일 단위 채우기'를 선택하면 됩니다.

빠른 채우기(Flash Fill)는 여기서 한 단계 더 나아갑니다. 여기서 빠른 채우기란 왼쪽 데이터에서 특정 패턴을 인식해 나머지 행을 자동으로 채워주는 기능으로, 단축키는 Ctrl+E입니다. 예를 들어 '무스 피자 / 라지 / 고구마'처럼 슬래시로 구분된 제품명에서 사이즈 정보만 추출하고 싶을 때, 첫 번째 행에 'Large'라고 한 번만 입력한 뒤 Ctrl+E를 누르면 나머지 행의 사이즈가 한 번에 채워집니다. 단, 데이터의 구분 패턴이 일정해야 올바르게 동작하기 때문에 결과를 반드시 눈으로 확인하는 습관이 필요합니다.

솔직히 이건 예상 밖이었습니다. 함수 없이 텍스트 가공이 이렇게 간단하게 된다는 게 처음엔 믿기지 않았습니다.

단축키도 조금만 익혀두면 작업이 훨씬 편해집니다. 셀 내용을 바로 편집할 때는 F2 키를 사용하고, 천 단위 구분 기호(쉼표)를 추가할 때는 Ctrl+Shift+1, 백분율 서식을 적용할 때는 Ctrl+Shift+5를 사용합니다. 이 두 서식 단축키는 보고서를 만들 때마다 반복적으로 쓰게 되는 기능입니다(출처: Microsoft 지원).

• 자동채우기: 채우기 핸들 더블클릭으로 수식을 마지막 행까지 한 번에 복사
• 빠른채우기(Ctrl+E): 패턴을 인식해 텍스트 추출·조합 작업을 자동화
• 서식 단축키: Ctrl+Shift+1(천 단위 구분), Ctrl+Shift+5(백분율) 필수 암기
• 채우기 옵션 버튼: 날짜 자동채우기 시 평일 단위, 월 단위 등 세부 설정 가능

요약: 자동채우기와 빠른채우기만 익혀도 반복 입력 작업의 상당 부분을 줄일 수 있으며, 서식 단축키를 함께 사용하면 보고서 작성 속도가 눈에 띄게 달라집니다.

 

데이터 구조가 맞아야 피벗테이블이 작동한다

엑셀을 오래 써온 분들도 피벗테이블(PivotTable)을 잘 활용하지 못하는 경우가 많습니다. 여기서 피벗테이블이란 대량의 데이터를 행·열·값으로 재배치해 원하는 집계 결과를 즉시 얻을 수 있는 분석 도구입니다. 함수를 쓰지 않고도 월별 매출, 지역별 판매량, 담당자별 실적 같은 집계 보고서를 몇 번의 클릭만으로 만들어낼 수 있습니다.

그런데 제 경험상 이건 좀 다릅니다. 피벗테이블이 작동하지 않는다고 느끼는 이유의 대부분은 기능 자체의 문제가 아니라, 원본 데이터 구조가 잘못돼 있기 때문입니다. 가장 흔한 오류는 데이터를 날짜별로 열을 나눠 가로 방향으로 쌓는 것입니다. 예를 들어 1월, 2월, 3월을 별도 열로 두고 각 셀에 수량을 입력하는 방식은 보기에는 깔끔해 보이지만, 데이터가 월마다 열로 늘어나기 때문에 피벗테이블로 집계하거나 정렬·필터를 적용하기 어렵습니다.

올바른 구조는 세로 방향 블록 쌓기입니다. 즉, 한 행에 날짜 하나, 지점 하나, 수량 하나처럼 각 데이터를 개별 행으로 기록하고, 각 열에는 '날짜', '지점명', '품목', '수량', '금액'처럼 고유한 항목명(필드)을 배치하는 방식입니다. 이렇게 되면 2019년, 2020년, 2021년 자료를 아래로 계속 쌓아도 피벗테이블 하나로 연도별·지점별·품목별 집계를 자유롭게 뽑아낼 수 있습니다.

구조적 참조(Structured Reference)라는 개념도 여기서 중요해집니다. 구조적 참조란 표(Table) 기능으로 범위를 지정한 뒤, 열 이름으로 데이터를 참조하는 방식입니다. 예를 들어 단순히 G6:G20처럼 셀 주소로 범위를 지정하면 데이터가 늘어날 때 범위를 수동으로 수정해야 하지만, 표로 만든 뒤 '원가표[제품명]' 형태로 참조하면 데이터가 추가될 때 자동으로 범위가 확장됩니다. 제가 직접 써봤을 때 가장 체감이 큰 부분이었습니다. 데이터를 추가하면 피벗테이블 새로 고침만으로 보고서가 업데이트되니, 그전까지 매번 범위를 재설정하던 번거로움이 완전히 사라졌습니다.

셀 병합(Merge Cells)은 되도록 피하는 것이 좋습니다. 화면에서는 깔끔하게 보이지만 셀 병합이 된 범위는 실제로 첫 번째 셀에만 값이 저장되고 나머지는 빈 셀로 처리됩니다. 이 때문에 SUMIF 같은 집계 함수를 적용하거나 열 단위로 선택해야 할 때 예상치 못한 오류가 발생할 수 있습니다. 국내 오피스 실무 교육 현장에서도 셀 병합을 남용하지 말라는 지침이 공통적으로 강조되고 있습니다(출처: 한국생산성본부).

피벗테이블을 만들 때는 '십자가 구조'만 기억하면 됩니다. 행(Row)에 기준 항목을, 열(Column)에 비교 항목을, 값(Value)에 집계할 수치를 배치하면 됩니다. 이후 슬라이서(Slicer)를 추가하면 버튼 클릭만으로 연도, 지역, 품목을 실시간으로 필터링하는 대시보드를 완성할 수 있습니다. 슬라이서란 피벗테이블의 필터를 버튼 형태로 시각화한 도구로, 여러 피벗테이블에 동시에 연결해 화면 전체를 한 번에 바꿀 수 있습니다. 여러 피벗테이블에 슬라이서를 연결할 때는 슬라이서 우클릭 → '보고서 연결'에서 연결할 피벗테이블을 모두 체크하면 됩니다.

요약: 데이터를 세로 방향으로 쌓고, 표 기능으로 범위를 동적으로 관리하면 피벗테이블이 제대로 작동하며 반복 집계 작업 대부분을 자동화할 수 있습니다.

 

자주 묻는 질문

Q. 자동채우기와 빠른채우기 차이가 뭔가요?

A. 자동채우기는 수식이나 날짜처럼 규칙적인 값을 아래로 복사하는 기능이고, 빠른채우기(Ctrl+E)는 텍스트에서 특정 패턴을 인식해 자동으로 추출하거나 조합하는 기능입니다. 예를 들어 '피자명 / 사이즈 / 옵션' 형태에서 사이즈만 뽑고 싶을 때는 빠른채우기가 훨씬 편리합니다. 다만 패턴이 불규칙하면 오류가 날 수 있어 결과 확인이 필요합니다.

 

Q. 피벗테이블이 자꾸 원하는 대로 안 만들어지는 이유가 뭔가요?

A. 대부분의 경우 원본 데이터 구조 문제입니다. 머릿글이 두 줄이거나, 날짜가 열 방향으로 펼쳐져 있거나, 셀 병합이 돼 있으면 피벗테이블이 제대로 집계되지 않습니다. 데이터를 한 행에 한 건씩 세로 방향으로 정리하고, 셀 병합을 모두 해제한 뒤 다시 시도해 보시길 권합니다.

 

Q. 표(Table) 기능을 쓰면 어떤 점이 좋은가요?

A. 데이터를 추가할 때 범위가 자동으로 확장돼 VLOOKUP, SUMIF, 피벗테이블 등 다른 기능과 연결이 훨씬 쉬워집니다. 특히 목록 상자(데이터 유효성 검사)의 원본 범위를 표로 설정해두면, 새 항목을 추가하는 즉시 목록에 반영됩니다. 표 이름을 지정해두면 수식에서 셀 주소 대신 이름으로 참조할 수 있어 수식 가독성도 높아집니다.

 

Q. 함수를 거의 쓰지 않아도 보고서를 만들 수 있나요?

A. 합계·평균·건수처럼 기본적인 집계라면 피벗테이블만으로 충분합니다. 정렬과 자동채우기를 활용하면 순위 계산도 함수 없이 할 수 있습니다. 다만 두 개 이상의 조건을 동시에 적용하거나, 다른 시트의 특정 값을 참조해야 하는 경우에는 VLOOKUP이나 SUMIF 같은 함수가 필요할 수 있습니다. 데이터 구조가 올바르게 잡혀 있을수록 함수 의존도가 줄어드는 것이 사실입니다.

 

결론

엑셀을 처음 배울 때 함수부터 외우려고 하면 쉽게 지칩니다. 제 경우에도 그랬습니다. 정작 실무에서 시간을 많이 잡아먹는 건 복잡한 수식이 아니라, 같은 입력 작업을 수백 번 반복하거나 잘못된 구조의 데이터를 매번 뜯어고치는 일이었습니다.

자동채우기로 반복 입력을 줄이고, 데이터를 세로 방향으로 정리하는 습관부터 잡으면 피벗테이블이 자연스럽게 작동합니다. 이 순서대로 하나씩 익혀가는 것이 가장 빠른 방법이라고 생각합니다. 오늘 당장 기존 파일 하나를 열고, 셀 병합이 있는지, 머리글이 한 줄인지부터 확인해 보시길 권합니다.

참고: https://www.youtube.com/watch?v=yR8_v58kN2c