Excel에서 큰 데이터 세트로 작업하는 경우 피벗 테이블은 많은 레코드에서 대화 형 요약을 만드는 빠른 방법으로 매우 유용합니다. 무엇보다도 데이터의 다양한 하위 집합을 자동으로 정렬 및 필터링하고, 합계를 계산하고, 평균을 계산하고, 교차표를 생성할 수 있습니다.
피벗 테이블 사용의 또 다른 이점은 원본 테이블의 열을 끌어다 놓기만 하면 요약 테이블의 구조를 설정하고 변경할 수 있다는 것입니다.
Excel 피벗 테이블?
Excel피벗 테이블은 대량의 데이터를 탐색 및 요약하고, 관련 합계를 분석하고, 다음을 위해 설계된 요약 보고서를 제공하는 도구입니다.
사용자 친화적인 방식으로 많은 양의 데이터를 제공합니다.
범주 및 하위 범주별로 데이터를 요약합니다.
가장 관련성이 높은 정보에 집중할 수 있도록 다양한 데이터 하위 집합을 필터링, 그룹화, 정렬 및 조건부로 서식을 지정합니다.
행을 열로 또는 열을 행으로 회전("피벗"이라고 함)하여 원본 데이터의 다양한 요약을 볼 수 있습니다.
스프레드시트의 숫자 데이터를 소계하고 집계합니다.
데이터 수준을 확장하거나 축소하고 드릴다운하여 합계 뒤에 있는 세부 정보를 확인합니다.
간결하고 매력적인 온라인 데이터 또는 인쇄된 보고서를 제공합니다.
예를 들어, 워크시트에 지역 대리점의 판매 수치가 포함된 수백 개의 항목이 있을 수 있습니다.
이 긴 숫자 목록을 하나 또는 여러 조건으로 합산하는 한 가지 방법은SUMIF및SUMIFS자습서에 설명된 대로 수식을 사용하는 것입니다. 그러나 각 그림에 대한 몇 가지 사실을 비교하려면 피벗 테이블을 사용하는 것이 훨씬 더 효율적인 방법입니다. 몇 번의 마우스 클릭만으로 원하는 필드별로 숫자를 합산하는 탄력 있고 쉽게 사용자 지정할 수 있는 요약 테이블을 얻을 수 있습니다.
위의 스크린샷은 가능한 많은 레이아웃 중 일부만 보여줍니다. 아래 단계는 모든 버전의 Excel에서 자신 만의 피벗 테이블을 빠르게 만드는 방법을 보여줍니다.
Excel에서 피벗 테이블을 만드는 방법
많은 사람들은 피벗 테이블을 만드는 것이 번거롭고 시간이 많이 걸린다고 생각합니다. 그러나 이것은 사실이 아닙니다! Microsoft는 수년 동안 이 기술을 개선해 왔으며 최신 버전의 Excel에서는 요약 보고서가 사용자 친화적이며 매우 빠릅니다. 실제로 몇 분 만에 자신 만의 요약 테이블을 만들 수 있습니다.
방법은 다음과 같습니다.
1. 원본 데이터 구성
요약 보고서를 만들기 전에 데이터를 행과 열로 구성한 다음 데이터 범위를 Excel 표로 변환합니다. 이렇게 하려면 모든 데이터를 선택하고삽입탭으로 이동한 다음테이블을클릭합니다.
원본 데이터에 Excel 테이블을 사용하면 데이터 범위가 "동적"이 된다는 매우 좋은 이점이 있습니다. 이 컨텍스트에서 동적 범위는 항목을 추가하거나 제거할 때 테이블이 자동으로 확장 및 축소된다는 것을 의미하므로 피벗 테이블에 최신 데이터가 누락되지 않을까 걱정할 필요가 없습니다.
열에 고유하고 의미 있는 제목을 추가하면 나중에 필드 이름으로 바뀝니다.
원본 테이블에 빈 행이나 열이 없고 부분합이 없는지 확인합니다.
테이블을 보다 쉽게 유지 관리하려면디자인탭으로 전환하고 워크시트의 오른쪽 위 모서리에 있는테이블 이름상자에 이름을 입력하여 원본 테이블의 이름을 지정할 수 있습니다.
2. 피벗 테이블 만들기
원본 데이터 테이블에서 셀을 선택한 다음삽입탭 > 테이블 그룹 >피벗 테이블로 이동합니다.
그러면피벗 테이블 만들기창이 열립니다.테이블/범위필드에서 올바른 테이블 또는 셀 범위가 강조 표시되어 있는지 확인합니다. 그런 다음 Excel 피벗 테이블의 대상 위치를 선택합니다.
새 워크 시트를 선택하면 A1 셀에서 시작하는새 워크시트에 테이블이 배치됩니다.
Existing Worksheet(기존워크시트)를 선택하면 기존 워크시트의 지정된 위치에 테이블이 배치됩니다.위치상자에서 대화 상자 축소 단추를클릭하여 테이블을 배치할 첫 번째 셀을 선택합니다.
확인을 클릭하면 대상 위치에 다음과 같은 빈 피벗 테이블이 만들어집니다.
대부분의 경우 피벗 테이블을별도의 워크시트에 배치하는 것이 좋으며 특히 초보자에게 권장됩니다.
다른 워크시트 또는 통합 문서의데이터에서 피벗 테이블을 만드는 경우 [Book1.xlsx]Sheet1!$A$1:$E$20과 같이 [workbook_name]sheet_name!range 구문을 사용하여 통합 문서 및 워크시트 이름을 포함합니다. 또는 대화 상자 축소 단추를클릭하고 마우스를 사용하여 다른 통합 문서의 테이블 또는 셀 범위를 선택할 수 있습니다.
피벗 테이블과피벗 차트를동시에 만드는 것이 유용할 수 있습니다. 이렇게하려면 Excel 2013 이상에서삽입탭 > 차트 그룹에서 피벗 차트 단추 아래의 화살표를 클릭 한 다음 피벗차트및 피벗 테이블을클릭합니다.Excel 2010 및 2007에서 피벗테이블아래의 화살표를 클릭 한 다음피벗 차트를클릭 합니다.
3. 피벗 테이블 보고서의 레이아웃 정렬
요약 보고서의 필드로 작업하는 영역을피벗 테이블 필드 목록이라고 합니다. 워크시트의 오른쪽 부분에 있으며 머리글과 본문 섹션으로 나뉩니다.
필드 섹션에는 테이블에 추가할 수 있는 필드의 이름이 포함되어 있습니다. 필드 이름은 원본 테이블의 열 이름과 일치합니다.
레이아웃 섹션에는보고서 필터영역,열레이블,행 레이블영역 및값영역이 포함되어 있습니다. 여기에서 테이블의 필드를 정렬하고 다시 정렬할 수 있습니다.
피벗 테이블 필드 목록에서변경한 내용은 테이블에 즉시 반영됩니다.
피벗 테이블에 필드를 추가하는 방법
레이아웃섹션에필드를 추가하려면필드 섹션에서필드이름 옆에 있는 확인란을 선택합니다.
기본적으로 Microsoft Excel에서는 다음과 같은 방법으로레이아웃섹션에 필드를 추가합니다.
숫자가 아닌 필드는Row Labels영역에 추가됩니다.
숫자 필드가값영역에 추가됩니다.
OLAP(온라인 분석 처리) 날짜 및 시간 계층이열 레이블영역에 추가됩니다.
피벗 테이블에서 필드를 제거하는 방법
특정 필드를 삭제하려면 다음 중 하나를 수행할 수 있습니다.
피벗 테이블 창의 필드 섹션에 있는필드이름에 대한 상자 중첩을 선택 취소합니다.
피벗 테이블의 필드를 마우스 오른쪽 버튼으로 클릭한 다음 "Field_Name 제거"를 클릭합니다.
피벗 테이블 필드를 정렬하는 방법
레이아웃섹션의 필드를 다음 세 가지 방법으로 정렬할 수 있습니다.
마우스를 사용하여레이아웃섹션의 4개 영역 사이에필드를 끌어다 놓습니다. 또는 필드 섹션에서필드이름을 클릭한 상태에서 레이아웃 섹션의 영역으로 드래그하면레이아웃섹션의 현재 영역에서 필드가 제거되고 새 영역에 배치됩니다.
필드 섹션에서필드이름을 마우스 오른쪽 단추로 클릭하고 추가할 영역을 선택합니다.
레이아웃섹션에서 필드를 클릭하여 선택합니다. 그러면 해당 특정 필드에 사용할 수 있는 옵션도 표시됩니다.
4. [값(Values)] 필드에 대한 함수를 선택합니다(선택 사항).
기본적으로 Microsoft Excel에서는 필드 목록의 값 영역에 배치하는 숫자값필드에 대해Sum함수를 사용합니다. 숫자가 아닌 데이터(텍스트, 날짜 또는 부울) 또는 빈 값을값영역에 배치하면Count함수가 적용됩니다.
그러나 물론 원하는 경우 다른 요약 기능을 선택할 수 있습니다. Excel 2013 이상에서는 변경할 값 필드를 마우스 오른쪽 단추로 클릭하고값 요약 기준을클릭한 다음 원하는 요약 함수를 선택합니다.
Excel 2010 이하에서는값 요약 기준옵션을 리본 메뉴의옵션탭에 있는 계산 그룹에서도 사용할 수있습니다.
아래에서Average함수가 있는 피벗 테이블의 예를 볼 수 있습니다.
함수의 이름은 대부분 자명합니다.
Sum - 값의 합계를 계산합니다.
Count - 비어 있지 않은 값의 수를 계산합니다(COUNTA 함수로 작동).
Average - 값의 평균을 계산합니다.
Max - 가장 큰 값을 찾습니다.
최소 - 가장 작은 값을 찾습니다.
Product - 값의 곱을 계산합니다.
5. 값 필드에 다른 계산 표시(선택 사항)
Excel 피벗 테이블은합계를 백분율로 표시하거나 가장 작은 값에서 가장 큰값으로 또는 그 반대로 순위를 표시하는등 다양한 방식으로 값을 표시할 수 있는 유용한 기능을 하나 더 제공합니다. 계산 옵션의 전체 목록은 여기에서 확인할 수있습니다.
이 기능을다른 이름으로 값 표시라고 하며 Excel 2013 이상에서 표의 필드를 마우스 오른쪽 단추로 클릭하여 액세스할 수 있습니다. Excel 2010 이하에서는 계산 그룹의옵션탭에서도 이 옵션을 찾을 수있습니다.
팁.값 표시 기능은동일한 필드를 두 번 이상 추가하고 예를 들어 총 판매액과 판매액을 동시에 백분율로 표시하는 경우에 특히 유용할 수 있습니다.
이것이 Excel에서 피벗 테이블을 만드는 방법입니다. 이제 필드를 약간 실험하여 데이터 세트에 가장 적합한 레이아웃을 선택할 차례입니다.
피벗 테이블 필드 목록 작업
피벗 테이블 창(예:피벗 테이블 필드 목록)은 요약 테이블을 원하는 대로 정확하게 정렬하는 데 사용하는 기본 도구입니다. 필드 작업을 보다 편안하게 하려면 원하는 대로 창을 사용자 지정할 수 있습니다.
필드 목록 보기 변경
필드 목록에섹션이 표시되는 방식을 변경하려면도구단추를 클릭하고 원하는 레이아웃을 선택합니다.
워크시트에서 창을 구분하는 막대(분할자)를 끌어 창 크기를 가로로조정할수도 있습니다.
피벗 테이블 창 닫기 및 열기
PivotTableField 목록을닫는 것은 창의 오른쪽 위 모퉁이에 있는닫기단추(X)를 클릭하는 것만큼 쉽습니다. 다시 나타나게 하는 것은 그리 분명하지 않습니다:)
필드 목록을 다시 표시하려면 테이블의 아무 곳이나 마우스 오른쪽 단추로 클릭한 다음 상황에 맞는 메뉴에서필드 목록 표시를선택합니다.
표시그룹의분석 / 옵션탭에 있는 리본의필드 목록단추를 클릭할 수도 있습니다.
권장 피벗 테이블 사용
방금 보았듯이 Excel에서 피벗 테이블을 만드는 것은 쉽습니다. 그러나 최신 버전의 Excel에서는 한 단계 더 나아가 원본 데이터에 가장 적합한 보고서를 자동으로 만들 수 있습니다. 4번의 마우스 클릭만 하면 됩니다.
원본 셀 또는 테이블 범위에서 셀을 클릭합니다.
삽입탭에서권장 피벗 테이블을클릭합니다. Microsoft Excel은 데이터를 기반으로 몇 가지 레이아웃을 즉시 표시합니다.
권장 피벗 테이블대화 상자에서 레이아웃을 클릭하여 미리 보기를 표시합니다.
미리보기가 만족 스러우면 OK 버튼을 클릭하고 새 워크 시트에 피벗 테이블을 추가합니다.
위의 스크린샷에서 볼 수 있듯이 Excel은 원본 데이터에 대한 몇 가지 기본 레이아웃만 제안할 수 있었는데, 이는 이전에 수동으로 만든 피벗 테이블보다 훨씬 열등합니다. 물론 이것은 내 의견 일 뿐이며 편견이 있습니다.
전반적으로 권장 피벗 테이블을 사용하면 빠르게 시작할 수 있으며, 특히 데이터가 많고 어디서부터 시작해야 할지 잘 모르는 경우에는 더욱 그렇습니다.
Excel에서 피벗 테이블을 사용하는 방법
이제 기본 사항을 알았으므로 Excel 2013 이상에서피벗 테이블 도구의분석및 디자인 탭(Excel 2010 및 2007의옵션및디자인탭)으로이동하여 여기에 제공된 그룹 및 옵션을 탐색할 수 있습니다. 이 탭은 테이블 내의 아무 곳이나 클릭하자마자 사용할 수 있습니다.
또한 특정 요소에 대해 사용할 수 있는 옵션과 기능을 마우스 오른쪽 버튼으로 클릭하여 액세스할 수 있습니다.
피벗 테이블을 디자인하고 개선하는 방법
원본 데이터를 기반으로 피벗 테이블을 만든 후에는 피벗 테이블을 더 구체화하여 강력한 데이터 분석을 수행할 수 있습니다.
테이블의 디자인을 개선하려면 미리 정의된 스타일을 많이 찾을 수 있는디자인탭으로 이동하세요. 스타일을 직접 만들려면 피벗 테이블 스타일 갤러리에서자세히단추를 클릭한 다음 "새 피벗 테이블 스타일..."을 클릭합니다.
특정 필드의 레이아웃을 사용자 지정하려면 해당 필드를 클릭 한 다음 Excel 2013 이상의분석탭 (Excel 2010 및 2007의옵션탭)에서필드 설정버튼을 클릭합니다. 또는 필드를 마우스 오른쪽 버튼으로 클릭하고 상황에 맞는 메뉴에서Field Settings(필드 설정)를 선택할 수 있습니다.
아래 스크린샷은 Excel 2013의 피벗 테이블에 대한 새로운 디자인과 레이아웃을 보여줍니다.
"Row Labels" 및 "Column Labels" 제목을 제거하는 방법
피벗 테이블을 만들 때 Excel은 기본적으로축소레이아웃을 적용합니다. 이 레이아웃은 "RowLabels" 및 "Column Labels"를 테이블 제목으로 표시합니다. 이것은 특히 초보자에게 그다지 의미 있는 제목이 아니라는 데 동의합니다.
이러한 우스꽝스러운 제목을 제거하는 쉬운 방법은 컴팩트 레이아웃에서 개요 또는 테이블 형식으로 전환하는 것입니다.
이렇게 하려면디자인리본 탭으로 이동하여보고서 레이아웃드롭다운을 클릭하고개요 폼에 표시또는테이블 형식으로 표시를선택합니다.
이렇게 하면 오른쪽 표에서 볼 수 있듯이 실제 필드 이름이 표시되므로 훨씬 더 의미가 있습니다.
또 다른 해결책은분석(옵션) 탭으로 이동하여옵션버튼을 클릭하고디스플레이탭으로 전환한 다음 "필드 캡션 및 필터 드롭다운 표시" 상자의 선택을 취소하는 것입니다. 그러나 이렇게 하면 테이블의 모든 필드 캡션과 필터 드롭다운이 제거됩니다.
Excel에서 피벗 테이블을 새로 고치는 방법
피벗 테이블 보고서가 원본 데이터에 연결되어 있더라도 Excel에서 자동으로 새로 고쳐지지 않는다는 사실에 놀랄 수 있습니다. 새로 고침 작업을 수동으로 수행하여 데이터 업데이트를 가져오거나 통합 문서를 열 때 자동으로 새로 고쳐지도록 할 수 있습니다.
수동으로 피벗 테이블 데이터 새로 고침
표의 아무 곳이나 클릭합니다.
Analyze(분석) 탭(이전 버전의Options(옵션) 탭)의Data(데이터) 그룹에서 Refresh(새로 고침) 버튼을 클릭하거나Alt+F5.또는 테이블을 마우스 오른쪽 버튼으로 클릭하고 상황에 맞는 메뉴에서[Refresh]를 선택할 수 있습니다.
통합 문서의 모든 피벗 테이블을 새로 고치려면 새로 고침 단추 화살표를 클릭한 다음 모두새로 고침을 클릭합니다.
메모.새로고침 후 피벗 테이블의 형식이 변경되면 "업데이트 시열 너비 자동 맞춤" 및 "업데이트 시 셀 서식 유지"옵션이 선택되어 있는지 확인합니다. 이를 확인하려면분석(옵션) 탭 >피벗 테이블그룹 >옵션단추를 클릭합니다.피벗 테이블 옵션대화 상자에서레이아웃 및 서식탭으로 전환하면 이러한 확인란이 표시됩니다.
새로 고침을 시작한 후상태를 검토하거나 마음이 바뀐 경우취소할수 있습니다. Refresh(새로 고침) 단추 화살표를 클릭한 다음 Refresh Status(새로 고침 상태) 또는Cancel Refresh(새로 고침 취소)를 클릭하기만 하면 됩니다.
통합 문서를 열 때 자동으로 피벗 테이블 새로 고침
분석/옵션 탭의피벗 테이블그룹에서옵션 > 옵션을클릭합니다.
피벗 테이블 옵션대화 상자에서 데이터 탭으로 이동하고파일을 열 때 데이터 새로 고침확인란을 선택합니다.
피벗 테이블을 새 위치로 이동하는 방법
테이블을 새 통합 문서로 이동하려면 워크 시트가 현재 시트의 다른 영역이며분석탭 (Excel 2010 및 이전 버전의옵션탭)으로 이동하여피벗 테이블 이동단추 동작그룹. 새 수신인을 선택하고확인을클릭합니다.
Excel 피벗 테이블을 삭제하는 방법
특정 요약 보고서가 더 이상 필요하지 않은 경우 여러 가지 방법으로 삭제할 수 있습니다.
테이블이별도의 워크시트에 있는 경우 해당 시트를 삭제하기만 하면 됩니다.
테이블이 시트의 다른 데이터와 함께 있는 경우 마우스를 사용하여 전체 피벗 테이블을 선택하고Delete키를 누릅니다.
삭제할 피벗 테이블의 아무 곳이나 클릭하고분석탭(Excel 2010 및 이전 버전의옵션탭) >작업그룹으로 이동한 다음선택단추 아래의 작은 화살표를 클릭하고전체 피벗 테이블을선택한 다음 Delete 키를 누릅니다.
피벗 테이블 차트가 표와 연결되어 있는 경우 피벗 테이블을 삭제하면 피벗 테이블이 표준 차트로 바뀌어 더 이상 피벗하거나 업데이트할 수 없습니다.
피벗 테이블 예제
아래 스크린샷은 올바른 경로에서 시작하는 데 도움이 될 수 있는 동일한 원본 데이터에 대해 가능한 몇 가지 피벗 테이블 레이아웃을 보여 줍니다.
클릭하고 마우스를 사용하여 다른 통합 문서의 테이블 또는 셀 범위를 선택할 수 있습니다.
