행렬을 3열 표로 변환합니다('역방향 피벗', '비회전', '평탄', '정규화').

행렬을 3열 표로 변환합니다('역방향 피벗', '비회전', '평탄', '정규화').

Excel 매트릭스를 변환해야 합니다.FIRST표에서LATER:

첫 번째:

    P1  P2  P3  P4
F1  X
F2  X   X
F3      X       X
F4      X   X

후일:

F   P   VALUE
F1  P1  X
F1  P2
F1  P3
F1  P4
F2  P1  X
F2  P2  X
F2  P3
F2  P4
F3  P1
F3  P2  X
F3  P3
F3  P4  X
F4  P1
F4  P2  X
F4  P3  X
F4  P4

"역방향 피벗", "비회전" 또는 "평탄화"하려면:

1. Excel 2003의 경우: 요약 테이블 내의 임의의 셀을 활성화하고 [Data](데이터) - [PivotTable](피벗테이블) 및 [PivotChart Report](피벗차트 리포트)를 선택합니다.

   

이후 버전의 경우 + ,D 를 사용하여 마법사에 액세스합니다.

Mac용 Excel 2011의 경우 ++AltP입니다(여기를 참조하십시오).

2. Multiple consolidation ranges ]를 선택하고 을 클릭합니다.

   

3. "Step 2a of 3"에서 "I will create the page fields" (페이지 필드를 만듭니다)를 선택하고 을 클릭합니다.

   

4. '스텝 2b/3'의 [범위(Range)]필드에 요약 테이블 범위를 지정하고(샘플 데이터의 경우 A1:E5)를 클릭한 후 을 클릭합니다.

   

5. "3단계"에서 피벗 테이블의 위치를 선택합니다(PT는 일시적으로만 필요하므로 기존 시트가 작동해야 합니다).

   

6. 클릭하여 피벗 테이블을 만듭니다.

   

7. 총합계 교차점(여기서는 셀 V7)을 드릴다운(두 번 클릭)합니다.7):

   

8. 이제 PT를 삭제할 수 있습니다.

9. 퀵 메뉴에서 표(표에서 마우스 오른쪽 버튼 클릭) 및 범위로 변환을 선택하여 결과 표를 일반적인 셀 배열로 변환할 수 있습니다.

---

Launch Excel에 같은 주제에 대한 동영상이 있는데 화질이 매우 좋다고 생각합니다.

VBA를 사용하지 않고 데이터를 분리할 수 있는 또 다른 방법은 Excel 2010 이상용 무료 애드인 PowerQuery를 사용하는 것입니다.이 기능은 다음과 같습니다.

Power Query 추가 기능을 설치하고 활성화합니다.다음으로 다음 단계를 수행합니다.

데이터 소스에 컬럼라벨을 추가하고 [Insert]> [ Table ]또는 [-]를 사용하여 Excel 테이블로 변환합니다.

테이블에서 셀을 선택하고 검정력 조회 리본에서 "From Table"을 클릭합니다.

그러면 Power Query Editor 창에 테이블이 열립니다.

첫 번째 열의 열 머리글을 클릭하여 선택합니다.그런 다음 변환 리본에서 열 분할 해제 드롭다운을 클릭하고 다른 열 분할 해제를 선택합니다.

Power Query 버전에 Unpivot other columns 명령이 없는 경우 첫 번째 열을 제외한 모든 열을 선택하고(Shift 키를 누른 상태에서 열 머리글을 클릭) Unpivot 명령을 사용합니다.

그 결과 평평한 테이블이 됩니다.Home 리본에서 Close and Load를 클릭하면 데이터가 새 Excel 시트에 로드됩니다.

이제 본론으로 들어갑시다.예를 들어 원본 테이블에 일부 데이터를 추가합니다.

Power Query 결과 테이블이 있는 시트를 클릭하고 Data 리본에서 Refresh all을 클릭합니다.다음과 같은 것이 표시됩니다.

Power Query는 일회성 변환이 아닙니다.반복 가능하며 동적으로 변화하는 데이터에 연결할 수 있습니다.

지금까지의 솔루션에는 모두 VBA, PowerQuery 등이 포함되어 있습니다.이는 훌륭하지만 일회성 이벤트입니다.보다 동적인 설정을 실시하려면 , INDEX(MATCH(...))의 사용을 검토해 주세요.그러면 테이블에 대한 동적 업데이트가 허용됩니다.

LET 기능과 다이내믹 어레이가 추가되어 이 비 VBA 솔루션이 가능합니다.

=LET(data,B2:E5,
     dataRows,ROWS(data),
     dataCols,COLUMNS(data),
     rowHeaders,OFFSET(data,0,-1,dataRows,1),
     colHeaders,OFFSET(data,-1,0,1,dataCols),
     dataIndex,SEQUENCE(dataRows*dataCols),
     rowIndex,MOD(dataIndex-1,dataRows)+1,
     colIndex,INT((dataIndex-1)/dataRows)+1,
     dataColumn, IF(INDEX(data,rowIndex,colIndex)="","",INDEX(data,rowIndex,colIndex)),
     unfiltered, CHOOSE({1,2,3},INDEX(rowHeaders,rowIndex),INDEX(colHeaders,colIndex), dataColumn),
     filtered, FILTER(unfiltered, dataColumn<>""),
     unfiltered)

공백 데이터를 포함한 모든 항목이 표시됩니다.공백을 제거하려면 마지막 매개 변수를 필터링됨으로 변경합니다.

BoK에 하나 더 추가 할 수 있습니다.여기에는 Excel 365가 필요합니다.B1:을 전개합니다.E5 by A1:답 5.

=LET( unPivMatrix, B1:E5,
      byMatrix, A1:A5,
        upC, COLUMNS( unPivMatrix ),
        byC, COLUMNS( byMatrix ),
        dmxR, MIN( ROWS( unPivMatrix ), ROWS( byMatrix ) ) - 1,
        dmxSeq, SEQUENCE( dmxR ) + 1,
        upCells, dmxR * upC,
        upSeq, SEQUENCE( upCells,, 0 ),
        upHdr, INDEX( INDEX( unPivMatrix, 1, ),  1,  SEQUENCE( upC ) ),
        upBody, INDEX( unPivMatrix,  dmxSeq,  SEQUENCE( 1, upC ) ),
        byBody, INDEX( byMatrix,  dmxSeq,  SEQUENCE( 1, byC ) ),
        attr, INDEX( upHdr, MOD( upSeq, upC ) + 1 ),
        mux, INDEX( upBody, upSeq/upC + 1, MOD( upSeq, upC ) + 1 ),
        demux, IFERROR( INDEX(
                              IFERROR( INDEX( byBody,
                                              IFERROR( INT( SEQUENCE( upCells, byC,0 )/byC/upC ) + 1, MOD( upSeq, upC ) + 1 ),
                                                       SEQUENCE( 1, byC + 1 ) ),
                                        attr ),
                              upSeq + 1, SEQUENCE( 1, byC + 2 ) ),
                         mux ),
        FILTER(demux, mux<>"")
 )

NB: byMatrix는 여러 열이 있는 범위일 수 있으며 열의 행 값을 복제합니다. 예를 들어 byMatrix는 A1:C5이고 unPivMatrix는 D1:H5이며 A2:C5 열 값을 복제합니다(A1 무시).

언급URL : https://stackoverflow.com/questions/20541905/convert-matrix-to-3-column-table-reverse-pivot-unpivot-flatten-normal