☰

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

=$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

Этого должно хватить.

Связанный контент