엑셀 피벗테이블 단축 작업을 익히는 방법

얼마 전 월별 매출표를 고치다가 새로 들어온 거래 30건이 피벗테이블에 빠진 것을 발견했습니다. 새로고침만 누르면 될 줄 알았는데, 원본 범위가 고정돼 있어 추가 행 자체를 읽지 못한 상태였죠. 그때 다시 느꼈습니다. 엑셀 피벗테이블 단축 작업은 키 하나를 외우는 것보다 원본을 준비하고, 표를 만들고, 갱신하는 흐름을 익힐 때 훨씬 빨라집니다.

아래 설명은 윈도우용 엑셀을 기준으로 합니다. 맥이나 웹 버전은 키 조합과 메뉴 위치가 다를 수 있습니다.

원본 데이터를 표로 만들면 나중 일이 줄어듭니다

피벗테이블을 만들기 전에는 원본의 제목 행부터 확인합니다. 예를 들어 ‘거래일’, ‘담당자’, ‘상품’, ‘매출액’처럼 각 열에 이름이 하나씩 있어야 합니다. 중간에 완전히 빈 행이나 열이 끼어 있으면 엑셀이 데이터 범위를 잘못 잡을 수 있습니다. ‘매출액’ 열에 숫자와 ‘미정’ 같은 문자가 섞여 있는지도 봐야 합니다. 이런 값이 있으면 합계 대신 개수로 집계되는 일이 생깁니다.

원본 안의 셀 하나를 선택하고 Ctrl+T를 누르면 표 만들기 창이 열립니다. 선택된 범위와 ‘머리글 포함’ 여부를 확인한 뒤 Enter를 누릅니다. 마이크로소프트 지원 안내에서도 엑셀 표를 피벗테이블 원본으로 쓰는 방식을 권합니다. 표 아래에 행을 추가하면 원본 범위가 함께 늘어나므로, 갱신할 때 새 거래가 포함되기 때문입니다.

다만 이미 일반 셀 범위로 만든 피벗테이블이 표로 저절로 연결되지는 않습니다. 기존 피벗테이블을 계속 쓸 때는 피벗테이블 분석 탭의 ‘데이터 원본 변경’에서 새 표를 지정해야 합니다. 이 차이를 놓치면 Ctrl+T로 표를 만든 뒤에도 보고서 숫자는 그대로일 수 있습니다.

삽입은 키 설명을 따라가면 편합니다

원본 표 안에 커서를 두고 Alt를 누르면 리본 메뉴에 작은 문자 표시가 나타납니다. 윈도우용 엑셀에서 삽입 탭은 Alt, N 순서로 열 수 있습니다. 이어서 화면에 표시되는 피벗테이블 명령의 문자를 누르면 만들기 창으로 이동합니다. 이 문자 표시는 엑셀 버전과 표시 언어에 따라 달라질 수 있으니, 화면에 뜬 표시를 확인하는 편이 정확합니다. 여기서 쉼표는 키를 동시에 누르라는 뜻이 아니라 차례대로 누르라는 뜻입니다.

만들기 창에서는 원본이 방금 만든 표인지 확인하고 배치 위치를 고릅니다. 처음에는 ‘새 워크시트’가 다루기 쉽습니다. 원본 데이터 옆에 피벗테이블을 붙여 두면 표가 커졌을 때 서로 겹치거나, 원본과 결과를 헷갈리기 쉽거든요.

예를 들어 거래일, 지역, 매출액이 있는 판매 자료라면 지역을 ‘행’, 거래일을 ‘열’, 매출액을 ‘값’ 영역에 놓을 수 있습니다. 처음부터 필드를 여러 개 넣기보다 질문 하나를 먼저 정하는 게 좋습니다. ‘지역별 매출은 얼마인가’라면 지역과 매출액만 넣어 결과를 확인하고, 그다음 월별 구분을 더하는 식입니다. 필드 목록이 보이지 않으면 피벗테이블 안의 셀을 선택한 뒤 ‘피벗테이블 분석’ 탭에서 ‘필드 목록’을 켜면 됩니다.

자주 쓰는 단축키는 새로고침과 메뉴 열기입니다

실제 업무에서는 피벗테이블을 새로 만드는 횟수보다 원본을 수정하고 다시 확인하는 횟수가 많습니다. 이때 아래 키 몇 개가 체감 시간을 줄여줍니다.

• Alt+F5: 선택한 피벗테이블의 데이터를 새로 고칩니다. 먼저 피벗테이블 안의 셀을 선택합니다.
• Ctrl+Alt+F5: 통합 문서의 데이터 연결과 피벗테이블 등을 한꺼번에 새로 고칩니다. 여러 보고서를 함께 갱신할 때 유용합니다.
• Shift+F10: 선택한 셀의 바로 가기 메뉴를 엽니다. 피벗테이블 항목을 선택한 상태라면 마우스 오른쪽 버튼을 누른 것과 비슷하게 관련 명령을 찾을 수 있습니다.
• Alt+아래쪽 화살표: 필터가 있는 셀에서 목록을 여는 데 사용합니다. 피벗테이블의 필터 항목을 고른 뒤 메뉴가 열리는지 확인해 두면 좋습니다.

Alt+F5와 Ctrl+Alt+F5는 용도가 다릅니다. 보고서 한 개의 숫자만 확인하려면 전자가 간단합니다. 여러 쿼리와 피벗테이블이 연결된 파일에서 후자를 누르면 갱신 범위가 넓어 시간이 더 걸릴 수 있습니다. 또 단순히 F5만 누르면 엑셀의 ‘이동’ 창이 열립니다. 새로고침 키와 헷갈리기 쉬운 부분입니다.

숫자가 그대로라면 키보다 원본 범위를 확인합니다

가령 월요일 매출 120건으로 만든 보고서에 화요일 거래 20건을 추가했다고 해보죠. Alt+F5를 눌렀는데도 건수가 120건이라면, 우선 새 행이 원본 표 안에 들어왔는지 확인합니다. 표 바깥의 빈 행을 사이에 두고 데이터를 붙여 넣으면 표가 확장되지 않을 수 있습니다. 일반 셀 범위를 원본으로 사용했다면 ‘데이터 원본 변경’에서 범위를 넓혀야 합니다.

새 행은 포함됐는데 매출 합계가 이상하다면 값 영역의 계산 방식도 봅니다. 매출액이 ‘합계’가 아니라 ‘개수’로 표시되면 숫자가 문자로 저장됐거나 빈 값이 섞였을 가능성이 있습니다. 원본을 고친 뒤 다시 갱신하고, 거래 몇 건을 직접 더한 금액과 피벗테이블 결과를 대조하면 오류를 빨리 찾을 수 있습니다.

제가 자주 쓰는 순서는 원본 셀 선택, Ctrl+T로 표 만들기, 피벗테이블 삽입, 필드 두 개로 첫 결과 확인, 원본 수정 후 Alt+F5입니다. 처음에는 키를 누르는 속도보다 원본과 결과의 관계를 확인하는 습관이 더 중요합니다. 그 흐름이 익숙해지면 반복 보고서에서 마우스를 찾는 시간도 자연스럽게 줄어듭니다.