2D Excel 테이블을 1D로 "축소"하거나 "축소"하는 방법은 무엇입니까?

2D Excel 테이블을 1D로 "축소"하거나 "축소"하는 방법은 무엇입니까?

엑셀에 국가 및 연도가 표시된 2차원 표가 있습니다.예:

        1961        1962        1963        1964
USA      a           x            g           y
France   u           e            h           a
Germany  o           x            n           p

첫 번째 col에 Country, 두 번째 col에 Year, 세 번째 col에 값을 지정하도록 "평활화"하고 싶습니다.예:

Country      Year       Value
USA          1961       a
USA          1962       x
USA          1963       g
USA          1964       y
France       1961       u
              ...

이 예는 3x4 매트릭스일 뿐이지만 실제 데이터 세트는 훨씬 더 큽니다(대략 50x40 정도).

Excel을 사용하여 어떻게 하면 좋을까요?

Excel 피벗 테이블 기능을 사용하여 피벗 테이블을 되돌릴 수 있습니다(기본적으로 여기에 있습니다).

좋은 지시사항은 다음과 같습니다.

http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/

수동으로 지시사항을 따르지 않으려면 다음 VBA 코드에 링크하십시오(모듈에 삽입).

Sub ReversePivotTable()
'   Before running this, make sure you have a summary table with column headers.
'   The output table will have three columns.
    Dim SummaryTable As Range, OutputRange As Range
    Dim OutRow As Long
    Dim r As Long, c As Long

    On Error Resume Next
    Set SummaryTable = ActiveCell.CurrentRegion
    If SummaryTable.Count = 1 Or SummaryTable.Rows.Count < 3 Then
        MsgBox "Select a cell within the summary table.", vbCritical
        Exit Sub
    End If
    SummaryTable.Select
    Set OutputRange = Application.InputBox(prompt:="Select a cell for the 3-column output", Type:=8)
'   Convert the range
    OutRow = 2
    Application.ScreenUpdating = False
    OutputRange.Range("A1:C3") = Array("Column1", "Column2", "Column3")
    For r = 2 To SummaryTable.Rows.Count
        For c = 2 To SummaryTable.Columns.Count
            OutputRange.Cells(OutRow, 1) = SummaryTable.Cells(r, 1)
            OutputRange.Cells(OutRow, 2) = SummaryTable.Cells(1, c)
            OutputRange.Cells(OutRow, 3) = SummaryTable.Cells(r, c)
            OutputRange.Cells(OutRow, 3).NumberFormat = SummaryTable.Cells(r, c).NumberFormat
            OutRow = OutRow + 1
        Next c
    Next r
End Sub

-아담

Excel 2013에서는 다음 단계를 따라야 합니다.

• 데이터를 선택하고 테이블로 변환(삽입 -> 테이블)
• 테이블의 Call Query Editor(Power Query -> 테이블에서)
• 연도가 포함된 열 선택
• 컨텍스트 메뉴에서 'Unpivot Columns'-command를 선택합니다.

서포트 오피스:분할 해제 열(Power Query)

Excel 2016에서 Power Query는 Get & Transform이라고 불리며 Data 탭에 있습니다.

@Adam Davis의 답변은 완벽합니다만, Excel VBA에 대해 저처럼 잘 모르실 경우를 대비해서 Excel 2007에서 코드를 작동시키기 위해 제가 한 일은 다음과 같습니다.

1. 테이블로 평탄화해야 하는 매트릭스가 있는 워크북을 열고 해당 워크시트로 이동합니다.
2. Alt-F11을 눌러 VBA 코드 편집기를 엽니다.
3. 왼쪽 창의 프로젝트 상자에 Excel 개체와 이미 존재하는 코드(모듈이라고 함)를 나타내는 트리 구조가 표시됩니다.박스내의 임의의 장소를 오른쪽 클릭하고, 「삽입」-> 「모듈」을 선택해, 빈 모듈 파일을 작성합니다.
4. @Adman Davis의 코드를 위에서 그대로 복사하여 빈 페이지에 붙여넣습니다.이 코드를 열고 저장합니다.
5. VBA 에디터 창을 닫고 스프레드시트로 돌아갑니다.
6. 매트릭스 내의 임의의 셀을 클릭하여 사용할 매트릭스를 지정합니다.
7. 이제 매크로를 실행해야 합니다.이 옵션은 Excel 버전에 따라 달라집니다.2007을 사용하고 있기 때문에, 매크로를 「View(보기)」리본에 보존하고 있는 것을 알 수 있습니다.클릭하면 매크로 세탁 목록이 나타납니다. "Reverse Pivot Table"이라는 매크로를 더블 클릭하여 실행합니다.
8. 그런 다음 평평한 테이블을 작성할 위치를 알려달라는 팝업이 표시됩니다.스프레드시트의 빈 공간을 가리키고 "확인"을 클릭합니다.

끝났어!첫 번째 열은 행, 두 번째 열은 열, 세 번째 열은 데이터가 됩니다.

데이터 매트릭스(일명 표)를 평탄화하려면 1개의 어레이 공식 and과 2개의 표준 공식을 사용합니다.

      

G3의 배열 공식과 2개의 표준 공식은 다음과 같습니다.I3는,

=IFERROR(INDEX(A$2:A$4, MATCH(0, IF(COUNTIF(G$2:G2, A$2:A$4&"")<COUNT($1:$1), 0, 1), 0)), "")
=IF(LEN(G3), INDEX($B$1:INDEX($1:$1, MATCH(1E+99,$1:$1 )), , COUNTIF(G$3:G3, G3)), "")
=INDEX(A:J,MATCH(G3,A:A,0),MATCH(H3,$1:$1,0))

필요에 따라서 기입해 주세요.

어레이 공식은 주기적인 계산으로 인해 성능에 부정적인 영향을 미칠 수 있지만 40행 x 50열의 작업 환경이 계산 지연과 함께 성능에 지나치게 영향을 미치지 않아야 합니다.

---

array 어레이 공식은 Ctrl+Shift+ Enterprise를 입력해야 합니다.첫 번째 셀에 입력하거나 복사하거나 복사하거나 복사할 수 있습니다.전체 열 참조는 실제 데이터의 범위를 나타내는 전체 열 참조하고 감소시킵니다.어레이 공식 씹는 계산 사이클을 씹기 때문에 참조된 범위를 좁혀야 합니다.자세한 내용은 어레이 공식을 참조하십시오.

PivotTable을 사용하여 이 작업을 수행하고자 하는 모든 사용자는 http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/에서 이 작업을 수행할 수 있습니다.

Excel 2007 또는 2010에서 이 기능을 사용하려면 먼저 PivotTable 마법사를 활성화해야 합니다.

메인 Excel 창 아이콘을 사용하여 "Excel Options"로 이동하여 "Customize" 섹션에서 선택한 옵션을 보려면 "Choose Commands from:" 드롭다운에서 "Commands Not in the Ribbon"을 선택하고 "PivotTable and PivotChart Wizard"를 오른쪽에 추가해야 합니다.아래 이미지를 참조하십시오.

이 작업이 완료되면 Excel 창 상단의 퀵바 메뉴에 피벗 가능한 작은 마법사 아이콘이 나타납니다.그러면 위의 링크와 같은 절차를 수행할 수 있습니다.

동적 어레이를 사용하면 공식만 사용하여 이 일반적인 작업을 해결하는 것이 이전 버전의 Excel보다 조금 더 쉬워집니다(또는 적어도 명확해짐).

TLDR은 다음과 같은 결과를 얻습니다.

필요한 공식은 다음과 같습니다.

행 7의 텍스트 형식의 공식은 다음과 같습니다.

• " " " : "=SEQUENCE(15,1)
• 번호: " " " 입니다.=FLOOR.MATH((A7#-1)/5)+1
• 번호: 열호 column:=MOD(A7# - 1,5) + 1
• 번호: " " " " "=INDEX(A2:A4,B7#)
• 번호: " " " " "=INDEX(B1:F1,1,C7#)
• : " " " " " " " " "=INDEX(B2:F4,B7#,C7#)

여기서 중요한 것은 다음과 같습니다.

• sequence데이터 항목당 하나의 인덱스로 시퀀스를 만드는 함수입니다.
• 의 #(다이나믹 어레이)를 사용하면 드래그 앤 페이스트 없이 필요에 따라 수식을 확장할 수 있습니다.
• 수열의 각 항목에 대한 행 및 열 인덱스를 계산하는 B7 및 C7의 공식.

D은 그냥 D:F의 입니다.index.

단순함을 위해 여기에 포함시킨 것은 아니지만let ★★★★★★★★★★★★★★★★★」lambda공식을 더 명확하고 완전히 재사용할 수 있습니다.

출력 테이블을 자주 새로 고쳐야 하고(입력 테이블이 다른 테이블에 의해 채워짐), 출력 테이블에 더 많은 정보를 담고 싶었기 때문에 다른 매크로를 개발했습니다(복사된 열과 일부 공식).

Sub TableConvert()

Dim tbl As ListObject 
Dim t
Rows As Long
Dim tCols As Long
Dim userCalculateSetting As XlCalculation
Dim wrksht_in As Worksheet
Dim wrksht_out As Worksheet

'##block calculate and screen refresh
Application.ScreenUpdating = False
userCalculateSetting = Application.Calculation
Application.Calculation = xlCalculationManual

'## get the input and output worksheet
Set wrksht_in = ActiveWorkbook.Worksheets("ressource_entry")'## input
Set wrksht_out = ActiveWorkbook.Worksheets("data")'## output.


'## get the table object from the worksheet
Set tbl = wrksht_in.ListObjects("Table14")  '## input
Set tb2 = wrksht_out.ListObjects("Table2") '## output.

'## delete output table data
If Not tb2.DataBodyRange Is Nothing Then
    tb2.DataBodyRange.Delete
End If

'## count the row and col of input table

With tbl.DataBodyRange
     tRows = .Rows.Count
     tCols = .Columns.Count
End With

'## check every case of the input table (only the data part)
For j = 2 To tRows '## parse all row from row 2 (header are not checked)
    For i = 5 To tCols '## parse all column from col 5 (first col will be copied in each record)
        If IsEmpty(tbl.Range.Cells(j, i).Value) = False Then
            '## if there is time enetered create a new row in table2 by using the first colmn of the selected cell row and cell header plus some formula
            Set oNewRow = tb2.ListRows.Add(AlwaysInsert:=True)
            oNewRow.Range.Cells(1, 1).Value = tbl.Range.Cells(j, 1).Value
            oNewRow.Range.Cells(1, 2).Value = tbl.Range.Cells(j, 2).Value
            oNewRow.Range.Cells(1, 3).Value = tbl.Range.Cells(j, 3).Value
            oNewRow.Range.Cells(1, 4).Value = tbl.Range.Cells(1, i).Value
            oNewRow.Range.Cells(1, 5).Value = tbl.Range.Cells(j, i).Value
            oNewRow.Range.Cells(1, 6).Formula = "=WEEKNUM([@Date])"
            oNewRow.Range.Cells(1, 7).Formula = "=YEAR([@Date])"
            oNewRow.Range.Cells(1, 8).Formula = "=MONTH([@Date])"
        End If
   Next i
Next j
ThisWorkbook.RefreshAll

'##unblock calculate and screen refresh
Application.ScreenUpdating = True 
Application.Calculate
Application.Calculation = userCalculateSetting

End Sub

상황에 따라서는, VBA 솔루션이 받아들여지지 않는 경우가 있습니다(예를 들면, 시큐러티상의 이유로 매크로를 짜넣을 수 없습니다).이러한 상황이나 그 외의 일반적인 상황에서도, 저는 매크로보다 공식을 사용하는 것을 선호합니다.

아래에 제 솔루션을 설명하려고 합니다.

• 질문에 표시된 대로 데이터를 입력한다(B2:F5)
• column_header (C2:F2)
• row_header (B3:B5)
• data_matrix (C3:F5)
• no_of_data_rows(I2) = COUNTA(row_header) + COUNTBLANK(row_header)
• no_of_data_hander (I3) = COUNTA (column_header) + COUNTBLANK (column_header)
• no_output_rows (I4) = no_of_data_rows*no_of_data_rows
• 시드 영역은 K2:M2로 공백이지만 참조되므로 삭제되지 않습니다.
• K3(예를 들어 K100에서 코멘트 설명 참조) = ROW()-ROW($K$2) <= no_output_rows
• L3(L100까지 포함, 설명 참조) = IF(K3,IF(COUNTIF)($L$2:L2,L2)
• M3(예: M100을 통해 설명, 주석 설명 참조) = IF(K3, IF(M2 < no_of_data_columns, M2+1,1) "-"
• N3(예: N100까지 표시, 주석 설명 참조) = INDEX(row_header, L3)
• O3(예: O100, 코멘트 설명 참조) = INDEX(column_header, M3)
• P3(예: P100까지 포함, 코멘트 설명 참조) = INDEX(data_matrix, L3, M3)
• K3 코멘트: 옵션:출력 행의 예상 개수에 도달했는지 확인합니다.이 테이블을 출력 행의 수로 제한하여 작성하는 경우에는 필수가 아닙니다.
• L3 코멘트: 목표:각 RowIndex(1..no_of_data_rows)는 no_of_data_rows를 반복해야 합니다.그러면 row_header 값에 대한 인덱스 조회가 제공됩니다.이 예에서는 각 RowIndex(1 .. 3)를 4회 반복해야 합니다.알고리즘:RowIndex가 발생한 횟수를 확인합니다.no_of_data_syslog 횟수보다 작으면 해당 RowIndex를 계속 사용하고 그렇지 않으면 RowIndex를 늘립니다.옵션:출력 행의 예상 개수에 도달했는지 확인합니다.
• M3에서의 코멘트: 목표:각 ColumnIndex(1..no_of_data_disples)는 사이클 내에서 반복되어야 합니다.그러면 column_header 값에 대한 인덱스 조회가 제공됩니다.이 예에서는 각 ColumnIndex(1 .. 4)를 사이클로 반복해야 합니다.알고리즘:ColumnIndex가 no_of_data_cycle을 초과하면 1에서 사이클을 재시작하고 그렇지 않으면 ColumnIndex를 증가시킵니다.옵션:출력 행의 예상 개수에 도달했는지 확인합니다.
• R4 코멘트: 옵션:L열과 M열에 표시된 것처럼 오류 처리에 K열을 사용합니다.data_matrix의 공백 입력으로 인해 출력에서 잘못된 "0"이 발생하지 않도록 IsBlank 값을 조회했는지 확인합니다.

헤더 열과 행 수를 지정할 수 있도록 ReversePivotTable 함수를 업데이트했습니다.

Sub ReversePivotTable()
'   Before running this, make sure you have a summary table with column headers.
'   The output table will have three columns.
    Dim SummaryTable As Range, OutputRange As Range
    Dim OutRow As Long
    Dim r As Long, c As Long

    Dim lngHeaderColumns As Long, lngHeaderRows As Long, lngHeaderLoop As Long

    On Error Resume Next
    Set SummaryTable = ActiveCell.CurrentRegion
    If SummaryTable.Count = 1 Or SummaryTable.Rows.Count < 3 Then
        MsgBox "Select a cell within the summary table.", vbCritical
        Exit Sub
    End If
    SummaryTable.Select

    Set OutputRange = Application.InputBox(prompt:="Select a cell for the 3-column output", Type:=8)
    lngHeaderColumns = Application.InputBox(prompt:="Header Columns")
    lngHeaderRows = Application.InputBox(prompt:="Header Rows")
'   Convert the range
    OutRow = 2
    Application.ScreenUpdating = False
    'OutputRange.Range("A1:D3") = Array("Column1", "Column2", "Column3", "Column4")
    For r = lngHeaderRows + 1 To SummaryTable.Rows.Count
        For c = lngHeaderColumns + 1 To SummaryTable.Columns.Count
            ' loop through all header columns and add to output
            For lngHeaderLoop = 1 To lngHeaderColumns
                OutputRange.Cells(OutRow, lngHeaderLoop) = SummaryTable.Cells(r, lngHeaderLoop)
            Next lngHeaderLoop
            ' loop through all header rows and add to output
            For lngHeaderLoop = 1 To lngHeaderRows
                OutputRange.Cells(OutRow, lngHeaderColumns + lngHeaderLoop) = SummaryTable.Cells(lngHeaderLoop, c)
            Next lngHeaderLoop

            OutputRange.Cells(OutRow, lngHeaderColumns + lngHeaderRows + 1) = SummaryTable.Cells(r, c)
            OutputRange.Cells(OutRow, lngHeaderColumns + lngHeaderRows + 1).NumberFormat = SummaryTable.Cells(r, c).NumberFormat
            OutRow = OutRow + 1
        Next c
    Next r
End Sub

일부 범용성에 대한 클레임을 수반하는 코드 이 책에는 두 개의 시트가 있어야 합니다. Sour = Source data Dest = "disclosed" 테이블이 여기에 표시됩니다.

    Option Explicit
    Private ws_Sour As Worksheet, ws_Dest As Worksheet
    Private arr_2d_Sour() As Variant, arr_2d_Dest() As Variant
    ' https://stackoverflow.com/questions/52594461/find-next-available-value-in-excel-cell-based-on-criteria
    Public Sub PullOut(Optional ByVal msg As Variant)
        ws_Dest_Acr _
                arr_2d_ws( _
                arr_2d_Dest_Fill( _
                arr_2d_Sour_Load( _
                arr_2d_Dest_Create( _
                CountA_rng( _
                rng_2d_For_CountA( _
                Init))))))
    End Sub

    Private Function ws_Dest_Acr(Optional ByVal msg As Variant) As Variant
        ws_Dest.Activate
    End Function

    Public Function arr_2d_ws(Optional ByVal msg As Variant) As Variant
        If IsArray(arr_2d_Dest) Then _
           ws_Dest.Cells(1, 1).Resize(UBound(arr_2d_Dest), UBound(arr_2d_Dest, 2)) = arr_2d_Dest
    End Function

    Private Function arr_2d_Dest_Fill(Optional ByVal msg As Variant) As Variant
        Dim y_Sour As Long, y_Dest As Long, x As Long
        y_Dest = 1
        For y_Sour = LBound(arr_2d_Sour) To UBound(arr_2d_Sour)
            ' without the first column
            For x = LBound(arr_2d_Sour, 2) + 1 To UBound(arr_2d_Sour, 2)
                If arr_2d_Sour(y_Sour, x) <> Empty Then
                    arr_2d_Dest(y_Dest, 1) = arr_2d_Sour(y_Sour, 1)    'iD
                    arr_2d_Dest(y_Dest, 2) = arr_2d_Sour(y_Sour, x)    'DTLx
                    y_Dest = y_Dest + 1
                End If
            Next
        Next
    End Function

    Private Function arr_2d_Sour_Load(Optional ByVal msg As Variant) As Variant
        arr_2d_Sour = ReDuce_rng(ws_Sour.UsedRange, 1, 0).Offset(1, 0).Value
    End Function

    Private Function arr_2d_Dest_Create(ByVal iRows As Long)
        Dim arr_2d() As Variant
        ReDim arr_2d(1 To iRows, 1 To 2)
        arr_2d_Dest = arr_2d
        arr_2d_Dest_Create = arr_2d
    End Function

    Public Function CountA_rng(ByVal rng As Range) As Double
        CountA_rng = Application.WorksheetFunction.CountA(rng)
    End Function

    Private Function rng_2d_For_CountA(Optional ByVal msg As Variant) As Range
        ' without the first line and without the left column
        Set rng_2d_For_CountA = _
        ReDuce_rng(ws_Sour.UsedRange, 1, 1).Offset(1, 1)
    End Function

    Public Function ReDuce_rng(rng As Range, ByVal iRow As Long, ByVal iCol As Long) _
           As Range
        With rng
            Set ReDuce_rng = .Resize(.Rows.Count - iRow, .Columns.Count - iCol)
        End With
    End Function

    Private Function Init()
        With ThisWorkbook
            Set ws_Sour = .Worksheets("Sour")
            Set ws_Dest = .Worksheets("Dest")
        End With
    End Function

'https://youtu.be/oTp4aSWPKO0

언급URL : https://stackoverflow.com/questions/687470/how-to-flatten-or-collapse-a-2d-excel-table-into-1d