들어가기 전에
Excel 수식에서 하나 이상의 셀, 표와 열 머리글 또는 범위를 참조할 수 있다는 것은 많은 사람들이 알고 있습니다. 하지만 GETPIVOTDATA 함수로 피벗 테이블에서 특정 데이터 요소를 참조하는 방법을 제대로 사용하는 사람은 많지 않습니다. GETPIVOTDATA 함수를 사용하여 피벗 테이블에서 원하는 데이터를 검색하는 방법을 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: GETPIVOTDATA: The Easy Way to Retrieve PivotTable Data in Microsoft Excel
- URL: https://www.howtogeek.com/microsoft-excel-getpivotdata-function-retrieve-pivot-table-data/
GETPIVOTDATA 함수 구문
GETPIVOTDATA 함수를 사용할 때마다 최소 두 개의 인수를 입력해야 합니다. 두 개 이상의 인수를 입력할지 여부는 검색하려는 항목에 따라 달라집니다.
=GETPIVOTDATA(a, b, c¹, c²...)
- a(필수): 검색하려는 데이터가 포함된 피벗 테이블 필드 이름
- b(필수): 피벗 테이블의 단일 셀, 여러 셀 또는 명명된 범위에 대한 참조
- c¹, c²(선택 사항): 검색하려는 데이터를 정의하는 최대 126쌍의 필드 이름( x¹ )과 항목 이름( x² ) 중 첫 번째
인수 a는 항상 큰따옴표로 묶어야 합니다. 필드 이름(x¹)과 항목 이름(x²)도 날짜, 숫자 또는 셀 참조가 아닌 경우 동일하게 적용됩니다.
GETPIVOTDATA를 사용하여 총계 검색
GETPIVOTDATA 함수는 필드 이름과 항목 이름을 기반으로 데이터를 추출하므로, 직접 셀을 참조하는 것보다 피벗 테이블 의 구조적 변경 사항을 처리하는 데 더 효과적입니다 . 따라서 보고서 등의 총합계를 가져오는 데 매우 유용합니다.
이 예제에서 피벗 테이블의 행은 국가이고, 열은 제품이며, 값은 매출입니다.
피벗 테이블에서 총합계를 가져오려면 먼저 총합계가 표시되어 있는지 확인해야 합니다. 이렇게 하려면 피벗 테이블에서 아무 셀이나 선택하고 리본의 디자인 탭에서 "총합계" 드롭다운 메뉴를 확장합니다. 그런 다음 "행 및 열에 대해 설정"을 클릭합니다.
이제 피벗 테이블에는 각 행과 열의 총합계가 표시되고, 오른쪽 하단 셀에는 모든 항목을 합친 총합계가 표시됩니다.
피벗 테이블의 모든 데이터의 총계 검색
Microsoft Excel에서 GETPIVOTDATA 수식의 가장 기본적인 예는 피벗 테이블의 모든 데이터의 총합을 구하는 것입니다. 실제로는 등호(=)만 입력하면 됩니다.
피벗 테이블에서 총합계를 추출하여 통합 문서의 다른 셀에 저장하려면 검색된 총합계를 표시할 셀을 선택하고 등호를 입력합니다. 그런 다음 피벗 테이블에서 총계를 표시하는 셀을 클릭하면 수식이 자동으로 삽입되는 것을 확인할 수 있습니다.
=GETPIVOTDATA("Sales", Totals!$A$3)
이 예에서 "Sales" 는 검색할 데이터가 들어 있는 필드이고, Totals!$A$3은 Totals 워크시트의 피벗 테이블에서 왼쪽 위 셀에 대한 절대 참조 입니다. Enter를 누르면 총액이 표시됩니다.
GETPIVOTDATA 함수는 서식이 아닌 값만 검색하므로 셀, 텍스트 및 숫자 서식을 수동으로 적용해야 합니다.
피벗 테이블 열 또는 행의 총계 검색
Excel의 피벗 테이블에서 열이나 행의 총합계를 검색하려면 동일한 원칙을 따를 수 있습니다. 즉, 등호를 입력하고, 주어진 열의 맨 아래나 주어진 행의 끝에 있는 총합계가 들어 있는 셀을 클릭한 다음 Enter 키를 누르면 됩니다.
여기서는 Sales 시트의 피벗 테이블 B열에 있는 amarilla라는 제품에 대한 총 판매액을 검색합니다.
=GETPIVOTDATA("Sales", Totals!$A$3, "Product", "Amarilla")
여기서 "Sales"는 검색하려는 값이고, Totals!$A$3은 관련 피벗 테이블의 셀을 참조하며, "Product", "Amarilla" 쌍은 Excel에서 Product라는 이름의 필드를 찾은 다음, 해당 필드 내에서 amarilla라는 이름의 항목을 찾도록 지시합니다.
하지만 현재 이 수식은 동적이 아닙니다. 즉, 다른 제품의 총합계를 가져오려면 기존 GETPIVOTDATA 수식을 편집하거나 새 수식을 생성해야 합니다.
대신, 최종 인수에 제품 이름을 하드코딩하는 대신 제품 이름이 포함된 셀을 참조할 수 있습니다.
=GETPIVOTDATA("Sales", Totals!$A$3, "Product", A1)
이제 A1 셀에 다른 제품 이름을 입력하면 총액도 그에 따라 업데이트됩니다.
피벗 테이블에서 행 끝에 있는 총합계를 반환하려면 동일한 과정을 따릅니다. 다음 수식은 캐나다의 총 판매액을 반환합니다.
=GETPIVOTDATA("Sales", Totals!$A$3, "Country", "Canada")
여기서 "Sales"는 검색할 값, Totals!$A$3은 Excel에 피벗 테이블의 위치, "Country", "Canada" 쌍은 Excel에 country 라는 필드를 찾은 다음, 해당 필드 내에서 Canada 라는 항목을 찾도록 알려줍니다.
수식을 동적으로 만들려면 "캐나다"를 국가 이름이 포함된 셀에 대한 참조로 수정합니다.
=GETPIVOTDATA("Sales", Totals!$A$3, "Country", A1)
GETPIVOTDATA를 사용하여 특정 데이터 포인트 검색
Excel의 GETPIVOTDATA 함수는 총계를 검색할 뿐만 아니라, 지정된 열과 행의 교차점에서 특정 데이터 포인트를 검색할 수도 있습니다. 멕시코에서 'Montana'라는 제품에 대한 총 판매량을 보고 싶다고 가정해 보겠습니다.
GETPIVOTDATA 함수의 경우 항상 그렇듯이, Excel에서 수식을 자동으로 생성해 주고 필요에 따라 직접 수정하는 것이 좋습니다. 결과 값을 저장할 셀에 등호(=)를 입력한 후 피벗 테이블에서 해당 셀을 클릭합니다. 이 경우에는 Montana 열과 Mexico 행이 교차하는 D8 셀입니다.
Excel은 다음 수식을 생성합니다.
=GETPIVOTDATA("Sales", Totals!$A$3, "Country", "Mexico", "Product", "Montana")
이제 수식에 하드코딩된 값을 셀 참조로 대체할 수 있습니다. 구체적으로, 국가를 "멕시코"로 참조하는 대신 B2 셀을 참조하고, 제품을 "Montana"로 참조하는 대신 A2 셀을 참조할 수 있습니다.
=GETPIVOTDATA("Sales", Totals!$A$3, "Country", B2, "Product",A2)
A2와 B2 셀에서 각각 제품과 국가를 변경하면 GETPIVOTDATA는 해당 값을 반환합니다.
GETPIVOTDATA를 사용하여 날짜를 기준으로 검색
피벗 테이블에서 날짜별 정보를 확인하는 한 가지 방법은 타임라인 슬라이서를 삽입하는 것입니다. 타임라인 슬라이서를 사용하면 특정 기간을 기준으로 피벗 테이블을 필터링 할 수 있는 반면 , GETPIVOTDATA 함수를 사용하면 피벗 테이블에서 정보를 추출 할 수 있으며 공간도 덜 차지합니다.
GETPIVOTDATA 함수를 날짜와 함께 사용하는 핵심은 피벗 테이블을 올바르게 구성하는 것입니다. 이 섹션에서 사용된 예제에서 피벗 테이블의 행은 날짜, 열은 제품, 값은 매출입니다. 또한 행과 열의 총합계가 표시됩니다.
데이터를 더 명확하게 하기 위해 A열의 날짜 중 하나를 마우스 오른쪽 버튼으로 클릭하고 "그룹"을 클릭한 다음 "월"과 "년"을 선택하고 "확인"을 클릭했습니다. 이에 따라 날짜는 2013년과 2014년으로 나뉘고, 각 달마다 별도의 행이 지정되었습니다.
이러한 변경 사항은 피벗 테이블 필드 창에서 확인할 수 있습니다. 여기서는 연도(날짜)와 월(날짜)이 별도의 행 필드로 표시됩니다.
지정된 연도의 총계 검색
이제 피벗 테이블이 연도와 월별로 제대로 구성되었으므로 GETPIVOTDATA 함수를 사용하여 특정 연도의 총계를 추출할 수 있습니다.
2013년 총액을 구하고 싶다고 가정해 보겠습니다. 이렇게 하려면 등호(=)를 입력하고 피벗 테이블에서 해당 값이 포함된 셀을 클릭합니다. Excel에서 바로 수식을 생성합니다.
=GETPIVOTDATA("Sales", Sales!$A$3, "Years (Date)", 2013)
여기서 "Sales"는 검색할 데이터가 포함된 필드이고, Sales!$A$3은 Sales 워크시트의 피벗 테이블에서 왼쪽 위 셀에 대한 절대 참조이고, "Years (Date)"는 필드 이름이고, 2013은 해당 필드 내의 품목 이름입니다.
이제 수식을 동적으로 만듭니다. 즉, 하드코딩된 연도를 셀 참조로 바꿉니다. 이 경우 연도는 A2 셀에 있습니다.
=GETPIVOTDATA("Sales", Sales!$A$3, "Years (Date)", A2)
수식이 동적이므로 연도를 2014년으로 변경하면 수치도 그에 따라 업데이트됩니다.
지정된 연도의 지정된 월에서 총계를 검색
Excel에서 피벗 테이블에서 연간 총계를 검색하는 것 외에도, 특정 연도의 특정 월에 대한 총계를 추출할 수 있습니다. 등호(=)를 입력하고 피벗 테이블에서 해당 셀을 선택하세요. 예를 들어 2014년 1월의 총합계를 구하려면 H11 셀을 선택합니다.
Excel에서 자동으로 생성한 수식은 다음과 같습니다.
=GETPIVOTDATA("Sales", Sales!$A$3, "Months (Date)", 1, "Years (Date)", 2014)
월이 숫자로 표현되어 있는 것에 주목하세요(1월은 1). 따라서 이 수식을 동적으로 만들 때는 입력하는 모든 값이 1(1월)에서 12(12월) 사이의 숫자가 되도록 해야 합니다. 바로 이 부분에서 MONTH 함수가 활용됩니다. 동적 수식은 다음과 같습니다.
=GETPIVOTDATA("Sales", Sales!$A$3, "Months (Date)", MONTH(A2&1), "Years (Date)", B2)
- Month (Date): 첫 번째 행 필드 이름
- 첫 번째 필드의 항목 이름은 MONTH(A2&1)로 표현되며, 이는 셀 A2의 3글자 월을 1~12 사이의 숫자 값으로 변환
- Years (Date): 두 번째 행 필드 이름
- B2: 두 번째 필드의 항목 이름이 B2 셀의 값(이 경우 2014)에 따라 결정된다는 것을 Excel에 알려줌
날짜를 기준으로 특정 데이터 포인트 검색
지금까지 이 가이드에서 설명한 모든 팁을 결합하여 특정 연도의 특정 월에 특정 제품의 특정 판매 총액을 반환해 보겠습니다.
등호를 입력하고 피벗 테이블 내에서 개별 데이터 포인트를 클릭하여 작업할 수식을 생성합니다.
Excel이 생성한 수식은 다음과 같습니다.
=GETPIVOTDATA("Sales", Sales!$A$3, "Product", "Velo", "Months (Date)", 3, "Years (Date)", 2014)
이 수식을 동적으로 수정합니다. "Velo"를 A2(제품 이름이 있는 셀), "2014"를 C2(연도가 있는 셀), 그리고 3을 MONTH(B2&1)로 변경합니다. 이 함수는 B2 셀의 월을 숫자로 변환합니다.
=GETPIVOTDATA("Sales", Sales!$A$3, "Product", A2, "Months (Date)", MONTH(B2&1), "Years (Date)", C2)
마지막으로 A2~C2 셀의 세부 정보를 변경하고 피벗 테이블과 결과를 교차 참조하여 수식이 예상대로 작동하는지 테스트합니다.
GETPIVOTDATA 사용 시 주의 사항
Excel 통합 문서에서 이 편리한 기능을 사용하기 전에 다음 마지막 사항을 참고하세요.
- 피벗 테이블에서 =를 입력 하고 셀을 클릭하면 해당 도구가 활성화된 경우에만 GETPIVOTDATA 수식이 실행됩니다. 이를 확인하려면 피벗 테이블을 선택하고 "피벗 테이블 분석" 탭을 연 다음, 피벗 테이블 이름 아래의 옵션 드롭다운 메뉴에서 "GetPivotData 생성" 옵션 옆에 확인 표시가 있는지 확인하세요. 확인 표시가 없으면 한 번 클릭하세요.
- GETPIVOTDATA 수식의 두 번째 인수가 피벗 테이블이 아닌 셀을 참조하는 경우 #REF! 오류가 발생합니다. 피벗 테이블의 필드 이름과 일치하지 않는 인수도 마찬가지입니다.
- GETPIVOTDATA 인수에 날짜, 숫자 또는 셀 참조가 포함되지 않는 한 큰따옴표를 사용해야 합니다.
- GETPIVOTDATA 인수로 월이나 연도가 아닌 전체 날짜를 사용하는 경우 DATE 함수를 사용하여 텍스트에서 인식 가능한 날짜 형식으로 변환해야 합니다.
마치며
GETPIVOTDATA는 Excel의 여러 조회 함수 중 하나일 뿐이지 만, 피벗 테이블에서 작동하는 방식이 독특하여 다른 함수들과 차별화됩니다. 일반 범위의 데이터나 Excel 표 형식으로 된 데이터를 사용하는 경우, XLOOKUP, INDEX와 XMATCH, 또는 FILTER를 사용하는 것이 좋습니다.
'Excel' 카테고리의 다른 글
| 복잡한 계산을 해결하기 위해 사용하는 5가지 Excel 배열 수식 (0) | 2025.12.23 |
|---|---|
| 더 스마트하게 일하는 10가지 간단한 엑셀 활용법 (0) | 2025.12.22 |
| 내가 가장 많이 사용하는 Excel 함수(그리고 그 이유) (0) | 2025.12.16 |
| 이 6가지 함수를 쓰지 않는다면 Excel을 잘못 사용하고 있는 것 (1) | 2025.12.15 |
| 야근 없는 삶? 엑셀 x AI 자동화로 업무 속도 확 끌어올리는 비법! (0) | 2025.12.14 |
