☰

Conte a quantidade de combinações únicas de múltiplas colunas no Excel

tag-icon • tag-icon microsoft-excel
Conte a quantidade de combinações únicas de múltiplas colunas no Excel

Tenho duas colunas em uma planilha e preciso escrever uma fórmula para determinar quantas vezes cada combinação aparece. Exemplo:

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

Deve resultar em:

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

Ontem fiz isso com muita facilidade importando para uma tabela SQL, mas preciso fazer para que qualquer pessoa possa fazer, simplesmente colocando essas duas colunas em uma planilha. Deverá então preencher uma segunda planilha com a tabela de quantidades.

Responder1

Editar: Solução simples (crédito para SaintWacko)

Concatene as duas colunas:

=$A2&$B2

Crie uma tabela dinâmica, usando a coluna concatenada como um 'Rótulo de linha' e um 'Valor'. Em seguida, certifique-se de que o 'Valor' esteja calculando contagens (deve ser o padrão).

Solução complicada

Você pode concatenar as duas colunas na coluna C:

=$A2&$B2

Na coluna D, você precisará de uma função que determine se o item é uma duplicata de outro item, para que o valor seja verdadeiro para apenas um de cada item (isso só funciona se os itens estiverem classificados pelas colunas de dados, mas a macro mais tarde faz isso).

=$C1<>$C2

Em seguida, calcule a contagem se correspondida na coluna E:

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

Uma macro encontrará todas as combinações para você, coloque-as na folha 2 e copie as contagens. O código abaixo faz algumas suposições, mas provavelmente você pode modificá-lo para fazer o que precisa.

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

Isso deve resolver.

informação relacionada