☰

計算Excel中多列的唯一組合的數量

tag-icon • tag-icon microsoft-excel
計算Excel中多列的唯一組合的數量

我的電子表格中有兩列，我需要寫一個公式來決定每個組合出現的次數。例子：

A  1
A  1
A  2
A  2
A  2
B  1
B  1
B  2

應該導致：

A  1  2
A  2  3
B  1  2
B  2  1

昨天我透過匯入到 SQL 表中很容易地做到了這一點，但我需要讓任何人都可以做到這一點，只需將這兩列放在電子表格中即可。然後，它應該使用數量表填充第二個工作表。

答案1

編輯： 簡單的解決方案（歸功於 SaintWacko）

連接兩列：

=$A2&$B2

建立一個資料透視表，使用連接的列作為「行標籤」和「值」。然後確保“值”正在計算計數（應該是預設值）。

複雜的解決方案

您可以連接 C 列中的兩列：

=$A2&$B2

在 D 列中，您需要一個函數來確定該項目是否為另一個項目的重複項，以便每個項目中只有一個值為 true（這僅在項目按資料列排序時有效，但巨集稍後會這樣做）。

=$C1<>$C2

然後計算 E 列中是否符合的計數：

=COUNTIF($C:$C,$C2)

巨集將為您找到所有組合，將它們放在第二張紙上並複製計數。下面的程式碼做出了一些假設，但您可以修改它來執行您需要的操作。

Option Explicit

Sub GetCombinations()

    Dim sheet1, sheet2 As Worksheet
    Set sheet1 = Worksheets(1)
    Set sheet2 = Worksheets(2)

    Dim sStartColumn As String
    Dim iTopRow As Long
    Dim sEndColumn As String
    Dim iBottomRow As Long

    sStartColumn = "A"
    iTopRow = 1
    sEndColumn = "E"
    iBottomRow = sheet1.UsedRange.Rows.Count

    Dim Rng As Range
    Dim sRange1 As String
    sRange1 = sStartColumn & CStr(iTopRow) & ":" & sEndColumn & CStr(iBottomRow)

    Set Rng = sheet1.Range(sRange1)

    Rng.Sort Key1:=Range("A2"), Order1:=xlAscending, _
             Key2:=Range("B2"), Order2:=xlAscending, _
             Orientation:=xlSortColumns, Header:=xlYes

    Dim i, j As Integer

    j = 2

    For i = 2 To iBottomRow

        If sheet1.Cells(i, 4) Then

            sheet2.Cells(j, 1) = sheet1.Cells(i, 1)
            sheet2.Cells(j, 2) = sheet1.Cells(i, 2)
            sheet2.Cells(j, 3) = sheet1.Cells(i, 5)
            j = j + 1

        End If

    Next i

End Sub

應該可以做到這一點。

相關內容