Подсчитайте количество уникальных комбинаций нескольких столбцов в Excel

Question

У меня есть два столбца в электронной таблице, и мне нужно написать формулу, чтобы определить, сколько раз каждая комбинация появляется. Пример:

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, но мне нужно сделать так, чтобы каждый мог это сделать, просто поместив эти два столбца в электронную таблицу. Затем следует заполнить второй лист таблицей количеств.

Answer 1

Изменить: простое решение (кредит переходит к SaintWacko)

Объединить две колонки:

=$A2&$B2

Создайте сводную таблицу, используя объединенный столбец как «Метка строки» и «Значение». Затем убедитесь, что «Значение» рассчитывает количество (должно быть по умолчанию).

Сложное решение

Вы можете объединить два столбца в столбце C:

=$A2&$B2

В столбце D вам понадобится функция, которая определяет, является ли элемент дубликатом другого элемента, так что значение истинно только для одного из каждого элемента (это работает, только если элементы отсортированы по столбцам данных, но макрос позже это сделаю).

=$C1<>$C2

Затем вычислите количество, если оно соответствует столбцу E:

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

Макрос найдет все комбинации для вас, разместит их на листе 2 и скопирует их. Код ниже делает некоторые предположения, но вы, вероятно, можете изменить его, чтобы сделать то, что вам нужно.

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

Это должно сделать это.