Conte a quantidade de combinações exclusivas de várias colunas no Excel

Question

Eu tenho duas colunas em uma planilha e preciso escrever uma fórmula para determinar quantas vezes cada combinação é exibida. 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

Eu fiz isso muito facilmente ontem, importando para uma tabela SQL, mas preciso fazer com que qualquer pessoa possa fazer isso, simplesmente colocando essas duas colunas em uma planilha. Em seguida, deve preencher uma segunda planilha com a tabela de quantidades.

    

Answer 1

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

Concatene as duas colunas:

=$A2&$B2

Crie uma tabela dinâmica usando a coluna concatenada como 'Rótulo da Linha' e 'Valor'. Em seguida, certifique-se de que o "Valor" esteja calculando as contagens (deve ser 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 forem classificados pelas colunas de dados, mas a macro mais tarde faz isso).

=$C1<>$C2

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

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

Uma macro encontrará todas as combinações para você, coloque-as na planilha 2 e copie as contagens. O código abaixo faz algumas suposições, mas você provavelmente pode modificá-lo para fazer o que você 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 ser feito.