피벗 테이블은 내가 필요한 데이터만 나타내기 힘들어 함수를 이용하여 데이터를 추출해서 가공한다.
1. 함수 인수
1) IFERROR > 상세내용 클릭
| =IFERROR(INDEX($A$1:$E$13,2,3),0) |
> A1:E13 의 값이 오류이면 0 으로 표시 (숫자일때) 참고 : $ -> 좌표가 고정됨 |
| =IFERROR(INDEX($A$1:$E$13,2,4),"") | > A1:E13 의 값이 오류이면 빈칸 으로 표시 (문자일때) |
2) INDEX > 상세내용 클릭
| =INDEX($A$1:$E$13,2,3) | > A1:E13 의 범위에서 2행(가로), 3열(세로) 값(1번수량)을 반환함 |
3) GETPIVOTDATA > 상세내용 클릭
| =GETPIVOTDATA("test",$A$3,"분류",P3) |
> 필드 이름 넣을때는 따옴표가 있어야함 피벗 테이블 필드 이름, 고정좌표("test",$A$3) / 표출할 필드 이름, 항목 이름 넣거나 항목 이름 있는 좌표("분류",P3) |
| =GETPIVOTDATA("합계 : 수량",$A$3,"분류",P3) |
> 피벗 테이블 필드가 2개만 있을때 피벗 테이블 필드 이름을 표출할 값으로 설정, 고정좌표("합계 : 수량",$A$3) / 표출할 필드 이름, 참조좌표("분류",P3) |
| =GETPIVOTDATA("test",$A$3,"분류1",P3,"분류2",Q3,"분류3",R3) |
> 분류가 여러개 일때 피벗 테이블 필드 이름, 고정좌표("합계 : 수량",$A$3) / 표출할 필드 이름, 참조좌표("분류1",P3) / 표출할 필드 이름, 참조좌표("분류2",Q3) / 표출할 필드 이름, 참조좌표("분류3",R3) |
4) ROW > 상세내용 클릭
| =ROW(U9) | > 참조 영역의 행 번호 반환함 |
| =ROW()-2 | > 현위치 행 번호에서 -2행 반환함 |
| =ROW(Sheet1!$A:$A) | > Sheet1 의 A 열에서 조건에 맞는 행 번호 반환함 |
5) COLUMN > 상세내용 클릭
| =COLUMN(U1) | > 참조 영역의 열 번호 반환함 |
| =COLUMN(Sheet1!$A$1:$U$1) | > Sheet1 의 1행에서 조건에 맞는 열 번호 반환함 |
6) IF > 상세내용 클릭
| =IF(Sheet1!$D:$D="내용D", ROW()) | > Sheet1의 D열 내용이 "내용D" 와 일치하면 Sheet1의 행 번호 반환함 |
7) SMALL > 상세내용 클릭
| {=SMALL(IF(Sheet1!$D:$D="내용D",ROW(Sheet1!$D:$D)),ROW()-2)} |
> Sheet1의 D열 내용이 "내용D" 와 일치하면 Sheet1의 D행 번호 반환하는데 번호를 나타내는 행위치가 T3이면 ROW()-2 로해서 행번호를 1행이 되게 설정함 참고 : 배열수식으로 {} 나타내야함, 배열수식 : Ctrl+Shift+Enter |
8) SUMPRODUCT > 상세내용 클릭
| =SUMPRODUCT((Sheet1!$A$1:$E$1=S4)*COLUMN(Sheet1!$A$1:$E$1)) | > Sheet1의 A1:E1 까지 참조좌표 S4 내용과 일치하면 Sheet1의 일치하는 열 번호 반환함 |
9) COUNTIF > 상세내용 클릭
| =COUNTIF(Sheet1!$D:$D,P3) | > Sheet1의 D열 내용이 참조좌표 P3의 내용과 일치하는 개수를 반환함 |
사용예제
| =IFERROR(INDEX($T$3:$U$14,1,2),0) |
| =IFERROR(GETPIVOTDATA("개수 : 증상",$AE$2,"월",AP3),0) |
| =IFERROR(GETPIVOTDATA("AS상세",$A$2,"증상",AU3),0) |
| =IFERROR(GETPIVOTDATA("구분별",$AX$2,"품목",BV3,"구분",BW3,"월",$BX$2),0) |
| =IFERROR(INDEX(국내판매!$B:$B,SMALL(IF(국내판매!$A:$A=$B$3,ROW(국내판매!$A:$A)),ROW()-2)),"") |
| =IFERROR(IF("국내"=$B$2, INDEX(국내판매!$A$1:$U$92,SMALL(IF(국내판매!$A:$A=$B$3,ROW(국내판매!$A:$A)),ROW()-2), SUMPRODUCT((국내판매!$C$2:$U$2=$B$5)*COLUMN(국내판매!$C$2:$U$2))), INDEX(해외판매!$A$1:$U$92,SMALL(IF(해외판매!$A:$A=$B$3,ROW(해외판매!$A:$A)),ROW()-2), SUMPRODUCT((해외판매!$C$2:$U$2=$B$5)*COLUMN(해외판매!$C$2:$U$2))) ),0) |
| =COUNTIF(국내판매!$A:$A,$B$3) |
2. VBA 예제
| Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Variant i = Range("A23").Value + 2 ActiveSheet.ChartObjects("차트 1").Activate ActiveChart.SetSourceData Source:=Range("A1:AM" & i) ActiveChart.Refresh ActiveSheet.ChartObjects("차트 2").Activate ActiveChart.Refresh ActiveSheet.ChartObjects("차트 3").Activate ActiveChart.Refresh End Sub |
차트가 있는 시트에 VBA 삽입 선택항목에 따라서 한차트를 변경하기 위해서 VBA 사용하고, 나머지 차트는 새로고침 함 {i 값에 +2 한것은 데이터의 위치가 A2에서 시작 Source는 시작좌표는 고정하고 끝좌표는 변경이 되어 i 값으로 조정함} |