M365Con 2027 – 무료 온라인 컨퍼런스 · 11–22 Jan 2027 · 120 sessions→ m365con.net

블로그 · Mirko Peters

Excel에서 피벗 테이블을 만드는 방법: 단계, 필드, 새로 고침

By · · Updated

Excel에서 피벗 테이블을 만드는 방법: 데이터 준비, 삽입 > 피벗 테이블 사용, 필드 끌어 놓기, 슬라이서 추가, 새로 고침 및 문제 해결. 초보자를 위한 단계별 안내입니다.

Automatically translated with AI from the original post. Read the English original

Excel에서 피벗 테이블을 만들려면 데이터 안의 셀을 클릭한 다음 삽입 > 피벗 테이블로 이동하고, 표를 배치할 위치(새 워크시트 또는 기존 워크시트)를 선택한 후 확인을 선택합니다. 그다음 피벗 테이블 필드 창에서 필드를 선택하거나 끌어 놓아 요약을 만듭니다. 이 글은 판매, 티켓, 근무 시간처럼 행이 많은 목록을 가지고 있으며, 수식을 한 줄도 작성하지 않고 고객, 지역 또는 월별 합계를 구하려는 Excel 사용자를 위한 글입니다. 데이터를 준비하고, 표를 만들고, 계산 방식을 바꾸고, 슬라이서로 필터링하고, 표를 최신 상태로 유지하며, 자주 발생하는 오류를 해결하는 방법을 다룹니다.

핵심 포인트

  • 피벗 테이블을 만들려면 데이터를 선택한 다음 삽입, 피벗 테이블 순으로 이동하고, 새 워크시트 또는 기존 워크시트를 선택한 후 확인을 선택합니다.
  • 깨끗한 데이터가 가장 먼저 필요합니다. 머리글 행은 하나만 두고, 모든 열에 고유하고 비어 있지 않은 머리글을 붙이며, 병합된 셀을 사용하지 않고, 원본에 중간 합계를 넣지 않습니다.
  • 필드를 행, 열, 값 영역으로 끌어 놓습니다. 숫자 필드는 기본적으로 합계로 계산되지만, Excel이 텍스트로 읽는 열은 기본적으로 개수로 계산됩니다.
  • 피벗 테이블이 모든 경우에 자동으로 업데이트되지는 않습니다. 원본 데이터가 바뀌면 피벗 테이블에서 마우스 오른쪽 버튼을 클릭해 새로 고침을 선택하거나 Alt+F5를 누르세요.
  • 원본을 Excel 표로 서식 지정하면 피벗 테이블을 새로 고칠 때 새 행이 포함됩니다.

피벗 테이블은 데이터를 변경하지 않습니다. 원본을 읽어 요약을 보여줄 뿐입니다. 그래서 원본 행을 위험에 빠뜨리지 않고 필드를 원하는 만큼 자유롭게 끌어 놓을 수 있습니다.

원본 데이터 준비하기

피벗 테이블 문제의 대부분은 첫 클릭 전, 바로 이 단계에서 시작됩니다. Microsoft의 워크시트 데이터를 분석하기 위해 피벗 테이블 만들기 문서는 다음 속성을 갖춘 깨끗한 표 형식 데이터를 요구합니다.

  • 머리글 행은 하나이고, 모든 열의 머리글은 고유하며 비어 있지 않아야 합니다.
  • 이중 머리글 행이 없고 병합된 셀도 없어야 합니다.
  • 원본에 중간 합계가 없어야 합니다. Microsoft의 피벗 테이블 개요에서는 피벗 테이블을 만들기 전에 기존 중간 합계를 제거하라고 안내합니다.
  • 목록 안에 빈 행이나 빈 열이 없어야 합니다.
  • 열마다 한 가지 종류의 데이터만 있어야 합니다. 숫자와 텍스트를 섞지 마세요.

마지막 항목은 결과에 실제로 영향을 줍니다. Microsoft는 숫자 값 필드가 기본적으로 합계로 계산되지만, Excel이 필드를 텍스트로 읽으면 기본값이 개수가 된다고 설명합니다. 금액 열에 "N/A" 또는 "n/a"가 적힌 셀이 하나만 있어도 이런 현상이 생길 수 있습니다. 합계를 기대했는데 피벗 테이블에 개수가 표시된다면 먼저 원본 열을 확인하세요.

다음으로 목록을 Excel 표로 바꿉니다. 데이터 안의 셀을 클릭하고 Ctrl+T를 누르세요. Microsoft는 원본으로 표를 사용할 것을 권장하며, 개요 페이지에서는 피벗 테이블을 새로 고칠 때 표가 새 데이터를 자동으로 포함한다고 설명합니다. 일반 범위는 처음 만들어진 마지막 행까지만 참조합니다. 같은 페이지에 따르면 동적 명명된 범위도 확장될 수 있지만, 표가 설정하기 더 간단하고 다음 사용자가 이해하기도 더 쉽습니다.

데이터를 먼저 재구성해야 하는 경우, 예를 들어 월별로 나뉜 열을 행으로 바꿔야 한다면 Microsoft는 표를 만들기 전에 Power Query로 피벗 해제(unpivot)를 하라고 안내합니다. 이 도구는 우리 Power Query 페이지에서 다룹니다.

Excel에서 피벗 테이블을 만드는 단계별 방법

아래 단계는 Microsoft 페이지에 설명된 대로 Windows용 Excel 기준입니다.

  1. 사용할 셀을 선택하거나, 표나 목록 안의 셀 하나를 클릭합니다. Excel이 주변의 데이터 블록을 자동으로 잡아 줍니다.
  2. 삽입으로 이동한 다음 피벗 테이블을 선택합니다.
  3. PivotTable 만들기 대화 상자에서 표/범위 상자에 표시된 표 또는 범위를 확인합니다. 잘못되어 있으면 수정합니다.
  4. 보고서를 놓을 위치로 새 워크시트 또는 기존 워크시트 중 하나를 선택합니다. 새 시트를 쓰면 원본 데이터와 요약 결과가 분리되어 읽기 쉽고 실수로 원본을 망가뜨릴 가능성도 줄어듭니다.
  5. 확인을 선택합니다. Excel이 오른쪽에 피벗 테이블 필드 창을 엽니다. 필드를 추가하기 전까지는 시트의 피벗 테이블 영역이 비어 있습니다. 이는 정상입니다.

대화 상자에는 선택 사항인 이 데이터를 데이터 모델에 추가 확인란이 있습니다. 첫 번째 피벗 테이블이라면 선택하지 않은 상태로 두십시오. 이 옵션은 여러 표를 결합하거나 매우 큰 데이터를 다룰 때를 위한 것입니다. 피벗 테이블 단추의 화살표에는 외부 데이터 원본에서 가져오기, 데이터 모델에서 가져오기, Power BI에서 가져오기 옵션도 있습니다. 표 사이의 관계가 필요한 시점이 오면 Power Pivot을 살펴보십시오.

Excel이 레이아웃을 제안하도록 하기

무엇을 만들어야 할지 모르겠다면 삽입 > 추천 피벗 테이블에서 데이터에 맞는 몇 가지 기본 레이아웃을 보여 줍니다. 제가 확인한 Microsoft 피벗 테이블 페이지에서는 이 기능을 단계별로 설명하지 않지만, 피벗 테이블 추천이 Microsoft 365의 연결된 환경(connected experiences)의 일부이며 데이터를 분석하기 위해 AI 서비스를 사용한다고 명시되어 있습니다. 조직에서 연결된 환경을 끈 경우에는 이 기능을 사용할 수 없습니다. 앞서 설명한 수동 방법은 언제나 사용할 수 있습니다.

피벗 테이블 필드 창으로 레이아웃 구성하기

이 창에는 원본의 모든 열 머리글이 필드로 나열됩니다. 필드의 확인란을 선택하거나 영역 중 하나로 끌어다 놓을 수 있습니다. 확인란을 선택했을 때의 Microsoft 기본 동작은 다음과 같습니다.

  • 숫자가 아닌 필드는 행으로 이동합니다.
  • 날짜 및 시간 필드는 계층 구조로 열에 들어갑니다.
  • 숫자 필드는 값으로 이동합니다.

이후에 어떤 필드든 다른 영역으로 끌어다 놓을 수 있습니다. 간단한 예로, 판매 목록에서 지역(행으로 이동)과 금액(값으로 이동)을 선택하면 지역별 합계가 나옵니다. 여기서 제품을 열로 끌어다 놓으면 같은 합계가 제품별로 나뉩니다.

각 영역의 역할은 다음과 같습니다.

  • 행은 왼쪽에 항목을 세로로 나열하며, 고유한 값마다 한 행이 만들어집니다.
  • 열은 항목을 위쪽에 가로로 나열합니다.
  • 값에는 합계, 개수 또는 평균을 구할 숫자가 들어갑니다.
  • 필터는 표 위에 필드를 배치하여 한 번에 하나의 값만 표시할 수 있게 합니다. Microsoft의 피벗 테이블 필터링 페이지에서는 이를 보고서 필터라고 설명합니다.

개요 페이지에는 보고서 형식을 압축 형식, 개요 형식, 표 형식 사이에서 전환할 수 있다는 내용도 나옵니다. 기본 레이아웃에서 여러 필드가 한 열에 몰려 있다면 표 형식을 시도해 보십시오. 결과를 다른 곳에 복사할 때 더 읽기 쉽습니다. 각 형식이 실제 데이터에서 어떻게 보이는지 직접 확인해 보시기 바랍니다.

계산 방식 변경하기

값의 계산 방식을 바꾸려면 값 영역에 있는 필드 옆의 화살표를 클릭하고 값 필드 설정을 선택합니다. 값 요약 기준 탭에서 합계, 개수, 평균, 최대, 최소 및 그 밖에 나열된 옵션 사이를 전환할 수 있습니다. Excel은 사용자 지정 이름을 자동으로 갱신하며, 이 이름은 직접 수정할 수도 있습니다. “Amount 합계”와 같은 이름은 본인이 보기에는 충분하지만, 보고서를 공유할 때는 읽기 쉬운 이름으로 바꾸십시오. Microsoft는 계산 방식을 바꾸면 이름도 다시 바뀌므로, 설정을 모두 마친 뒤에 이름을 바꾸라고 권장합니다.

같은 대화 상자의 숫자 서식 버튼은 통화나 고정 소수 자릿수 같은 서식을 필드 전체에 적용합니다. 셀 서식을 일일이 지정하지 말고 여기서 한 번만 설정하십시오. 레이아웃이 바뀌면 수동으로 지정한 셀 서식이 사라질 수 있기 때문입니다. 서식을 유지하고 싶다면 PivotTable 분석 탭에서 옵션을 열고 레이아웃 및 서식 아래의 업데이트 시 셀 서식 유지를 선택합니다. 같은 탭에는 업데이트 시 열 너비 자동 맞춤 옵션도 있습니다. 업데이트할 때마다 열 너비가 자꾸 바뀐다면 이 옵션을 끄는 것이 좋습니다.

값을 백분율로 표시

'값 필드 설정'의 다른 방법으로 값 표시 탭은 숫자의 의미를 바꿉니다. 예를 들어 총합계의 %처럼 각 값을 전체에 대한 비율로 표시할 수 있습니다. 금액과 비율을 나란히 보여주고 싶다면 같은 필드를 값 영역에 두 번 끌어다 놓고, 각 복사본에 서로 다른 설정을 지정합니다. "매출"과 "매출 비중" 조합을 만들 때 유용한 방법입니다.

슬라이서와 보고서 필터로 필터링하기

테이블을 만들고 나면 보통 그중 일부만 보고 싶어집니다. 이를 위한 도구는 세 가지이며, 모두 Microsoft 문서에 자세히 나와 있습니다.

슬라이서는 테이블 옆에 계속 표시되는 단추입니다. PivotTable 아무 곳이나 클릭한 다음 삽입 > 슬라이서로 이동하고, '슬라이서 삽입' 대화 상자에서 원하는 필드를 선택한 뒤 확인을 누릅니다. 필드마다 슬라이서가 하나씩 만들어집니다. 단추를 클릭하면 필터가 적용되고, Ctrl을 누른 채 클릭하면 여러 항목을 선택할 수 있으며, 필터 지우기로 초기화합니다. Microsoft의 슬라이서를 사용하여 데이터 필터링 문서에는 유용한 내용이 하나 더 있습니다. 슬라이서는 원본 데이터가 같은 경우에 한해 둘 이상의 PivotTable을 제어할 수 있습니다. 슬라이서를 선택하고 슬라이서 탭으로 이동해 보고서 연결을 선택한 다음 PivotTable을 선택하면 됩니다.

필터 드롭다운. 행 또는 열 레이블의 화살표를 누르면 레이블 필터와 값 필터가 있는 메뉴가 열립니다. 값 필터 아래에서 상위 10개를 찾을 수 있으며, 이 기능으로 개수, 백분율 또는 합계 기준의 상위 또는 하위 항목을 표시할 수 있습니다. 항목 목록에서는 모두 선택의 선택을 해제하고 필요한 항목만 선택하거나 검색 상자에 입력합니다.

마우스 오른쪽 버튼 필터링. 테이블에서 항목을 선택한 뒤 마우스 오른쪽 버튼을 누르고 필터를 고른 다음 선택 항목만 유지 또는 선택 항목 숨기기를 선택하세요. 필터 지우기를 누르면 필터가 제거됩니다.

보고서 필터(필터 영역)에는 유용한 기능이 하나 더 있습니다. 보고서 필터 페이지 표시를 사용하면 필터 필드의 값마다 워크시트가 하나씩 만들어집니다. 지역별로 별도의 시트를 만들 때 빠른 방법입니다.

데이터가 바뀔 때 피벗 테이블 새로 고침

피벗 테이블은 만들어졌거나 마지막으로 새로 고쳐진 시점의 원본 데이터를 보여 주는 스냅샷입니다. 원본에 새 행이 추가되어도 테이블에는 바로 표시되지 않을 수 있습니다. 피벗 테이블 데이터 새로 고침 문서에는 다음 옵션이 정리되어 있습니다.

  1. 테이블 하나 새로 고침. 피벗 테이블을 선택하고 피벗 테이블 분석 탭의 데이터 그룹에서 새로 고침을 선택하거나 Alt+F5를 누르세요. 테이블을 마우스 오른쪽 버튼으로 클릭하고 새로 고침을 선택해도 됩니다.
  2. 모두 새로 고침. 새로 고침 아래의 화살표를 열고 모두 새로 고침을 선택하세요. 통합 문서에 있는 모든 피벗 테이블이 업데이트됩니다.
  3. 열 때 새로 고침. 테이블을 선택하고 PivotTable 분석 탭에서 옵션을 연 다음 데이터 탭으로 이동하여 파일을 열 때 데이터 새로 고침을 선택합니다.

Microsoft 문서에는 자동 새로 고침 설정도 설명되어 있습니다. 통합 문서 데이터를 기반으로 만든 피벗 테이블이 원본이 바뀌면 자동으로 업데이트되는 기능입니다. 문서에 따르면 현재는 Microsoft 365 Insider 프로그램 참가자에게만 제공되며, 이전 버전의 Excel은 자동으로 새로 고쳐지지 않고 열 때만 새로 고쳐질 수 있다고 합니다. 따라서 사용 중인 빌드에서 이 기능을 지원하는지 확인하기 전에는 기대하지 않는 편이 좋습니다. 그때까지는 수동 새로 고침이나 열 때 새로 고침이 안전한 방법입니다.

새 행을 추가해도 여전히 표시되지 않으면 어떻게 하나요?

원본 범위가 너무 작을 가능성이 큽니다. 데이터가 Excel 표라면 새로 고칠 때 새 행이 자동으로 포함됩니다. 일반 범위라면 피벗 테이블이 여전히 이전 셀을 가리키고 있는 것입니다. Microsoft의 피벗 테이블의 원본 데이터 변경 문서에 따라 해결하세요. 피벗 테이블을 선택하고 분석 탭으로 이동한 다음 데이터 원본 변경을 두 번 선택합니다(먼저 단추, 그다음 메뉴 항목). 테이블 또는 범위 선택을 선택하고 표/범위 상자에 범위를 입력한 후 확인을 선택합니다. Microsoft는 열이 추가되거나 삭제되는 등 데이터가 많이 바뀌는 경우에는 새 피벗 테이블을 만드는 편이 대개 더 깔끔하다고 덧붙입니다.

피벗 테이블이 통합 문서 데이터 모델을 사용하는 경우에는 이 방법으로 데이터 원본을 변경할 수 없습니다.

피벗 테이블 삭제 또는 정리

Microsoft의 피벗 테이블 삭제 문서에는 두 가지 방법이 나와 있습니다. 첫 번째는 피벗 테이블 전체를 선택하고 Delete를 누르는 것입니다. "피벗 테이블 보고서의 이 부분은 변경할 수 없습니다"라는 메시지가 표시되면 Ctrl+A를 눌러 모든 항목을 선택한 후 Delete를 다시 누르세요. 두 번째는 피벗 테이블 안의 셀을 클릭하고 피벗 테이블 분석 > 선택 > 전체 피벗 테이블으로 이동하여 거기서 삭제하는 것입니다. 피벗 테이블이 시트에 단독으로 있다면 시트 전체를 삭제할 수도 있습니다.

원본 데이터도 같은 시트에 있다면 마지막 방법은 주의해서 사용하세요. Microsoft 문서에는 원본 데이터가 어떻게 되는지 나와 있지 않으므로, 무엇인가를 삭제하기 전에 데이터가 다른 시트에 있는지 확인하세요. 슬라이서는 별도의 개체입니다. 슬라이서를 선택하고 Delete를 누르면 제거됩니다.

피벗 테이블 결과가 잘못될 때

  • 합계 대신 개수가 표시됩니다. 값 열에 텍스트 셀이 있는 경우입니다. 데이터를 정리하거나 값 요약 기준을 합계로 설정한 다음, 숫자가 텍스트로 표시되는 셀이 남아 있지 않은지 확인하세요.
  • 목록에 필드가 없습니다. 열에 머리글이 없거나 범위에 해당 열이 포함되지 않았을 수 있습니다. 원본의 첫 번째 행과 데이터 원본 변경 대화 상자의 표/범위 상자를 확인하세요.
  • 이전 숫자가 표시됩니다. 새로 고치지 않은 상태입니다. Alt+F5를 사용하세요.
  • 날짜가 텍스트처럼 동작합니다. 날짜 열이 텍스트로 저장되어 있으면 Excel은 이를 날짜로 인식하지 못합니다. 원본에서 실제 날짜로 변환한 후 새로 고치세요.
  • 레이아웃이 뒤죽박죽으로 보입니다. 원본에 병합된 셀이나 두 번째 머리글 행이 있는지 확인하세요. Microsoft의 데이터 요구 사항에서는 이 두 가지를 모두 허용하지 않습니다.

다른 사람에게 전달할 작업이라면 피벗 테이블에 "지역별 판매"처럼 명확한 시트 이름을 붙이고, 그 위에 어떤 데이터를 읽는지 짧은 메모를 남기세요. 1분이면 충분하며, 다음 사용자가 추측하는 수고를 덜어 줍니다.

흔한 실수

  • 빈 행이 있는 범위로 표를 만들어 간격 위쪽 부분만 포함되는 경우입니다.
  • 원본에 소계 행을 그대로 두어 요약에서 이중으로 계산되는 경우입니다.
  • 피벗 테이블 위에 숫자를 직접 입력하는 경우입니다. 피벗 테이블은 보고서이므로 원본을 변경한 후 새로 고치세요.
  • 계산이 확정되기 전에 필드 이름을 바꾸면 Excel이 이름을 재설정하는 경우입니다.
  • 파일을 보내기 전에 새로 고치는 것을 잊는 경우입니다.
  • 서로 다른 데이터 원본을 사용하는 표에 슬라이서를 연결한 뒤 연결이 안 되는 이유를 궁금해하는 경우입니다.

함께 살펴볼 관련 주제

  • 월 또는 분기별로 날짜 그룹화. 판매 및 시간 데이터에서 흔히 다음 단계로 진행하는 작업입니다.
  • 여러 표용 슬라이서. 하나의 슬라이서를 여러 피벗 테이블에 연결하면 간단한 대시보드를 만들 수 있습니다.
  • PivotChart. 피벗 테이블과 연결된 차트로, 테이블이 바뀌면 함께 바뀝니다.
  • 피벗 전에 Power Query 사용. 내보내기 파일에 월별 열이 하나씩 있다면 Power Query로 먼저 데이터를 열에서 행으로 변환하거나 병합하세요.
  • 데이터 모델과 측정값. 단일 목록으로는 부족할 때 Power Pivot으로 여러 표를 결합하세요.

다음 단계

직접 만든 목록 중 하나를 선택하고 Ctrl+T를 눌러 표로 만든 다음, 행에 필드 하나, 값에 필드 하나만 넣어 피벗 테이블을 만들어 보세요. 이것이 잘 되면 슬라이서를 추가하고, 원본의 숫자를 바꾼 뒤 한 번 새로 고치세요. 이것이 Excel에서 피벗 테이블을 만드는 전체 흐름입니다. 준비하고, 만들고, 필터링하고, 새로 고치는 과정입니다.

데이터 원본

쇼 팔로우하기Spotify

Entities in this article

Microsoft Excel Power Query Power Pivot

Related articles

RSS