Como transpor células em uma coluna com base em valores únicos em outra coluna?

Suponha que você tenha um intervalo de dados que contém duas colunas, e agora deseja transpor as células em uma coluna para linhas horizontais com base em valores únicos em outra coluna para obter o seguinte resultado. Você tem alguma boa ideia para resolver esse problema no Excel?

Transpor células em uma coluna com base em valores únicos com fórmulas

Transpor células em uma coluna com base em valores únicos com código VBA

Transpor células em uma coluna com base em valores únicos com Kutools para Excel

Transpor células em uma coluna com base em valores únicos com fórmulas

Com as seguintes fórmulas matriciais, você pode extrair os valores únicos e transpor seus dados correspondentes em linhas horizontais; por favor, siga os passos abaixo:

1. Insira esta fórmula matricial: =ÍNDICE($A$2:$A$16; CORRESP(0; CONT.SE($D$1:$D1; $A$2:$A$16); 0)) em uma célula em branco, D2, por exemplo, e pressione Shift + Ctrl + Enter juntos para obter o resultado correto, veja a captura de tela:

Observação: Na fórmula acima, A2:A16 é a coluna da qual você deseja listar os valores únicos, e D1 é a célula acima desta célula de fórmula.

2. Em seguida, arraste a alça de preenchimento para baixo nas células para extrair todos os valores únicos, veja a captura de tela:

3. E então continue inserindo esta fórmula na célula E2: =SEERRO(ÍNDICE($B$2:$B$16; CORRESP(0; CONT.SE($D2:D2;$B$2:$B$16)+SE($A$2:$A$16<>$D2; 1; 0); 0)); 0), e lembre-se de pressionar Shift + Ctrl + Enter para obter o resultado, veja a captura de tela:

Observação: Na fórmula acima: B2:B16 é a coluna de dados que você deseja transpor, A2:A16 é a coluna com base na qual você deseja transpor os valores, e D2 contém o valor único que você extraiu na Etapa 1.

4. Em seguida, arraste a alça de preenchimento para a direita das células onde você deseja listar os dados transpostos até que exiba 0, veja a captura de tela:

5. E então continue arrastando a alça de preenchimento para baixo no intervalo de células para obter os dados transpostos conforme mostrado na captura de tela a seguir:

Transpor células em uma coluna com base em valores únicos com código VBA

Talvez as fórmulas sejam complexas para você entender, aqui você pode executar o seguinte código VBA para obter o resultado desejado.

1. Mantenha pressionadas as teclas ALT + F11 para abrir a janela do Microsoft Visual Basic for Applications.

2. Clique em Inserir > Módulo e cole o seguinte código na Janela do Módulo.

Código VBA: Transpor células em uma coluna com base em valores únicos em outra coluna:

Sub transposeunique()
'updateby Extendoffice
    Dim xLRow As Long
    Dim i As Long
    Dim xCrit As String
    Dim xCol  As New Collection
    Dim xRg As Range
    Dim xOutRg As Range
    Dim xTxt As String
    Dim xCount As Long
    Dim xVRg As Range
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("please select data range(only two columns):", "Kutools for Excel", xTxt, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
    If (xRg.Columns.Count <> 2) Or _
       (xRg.Areas.Count > 1) Then
        MsgBox "the used range is only one area with two columns ", , "Kutools for Excel"
        Exit Sub
    End If
    Set xOutRg = Application.InputBox("please select output range(specify one cell):", "Kutools for Excel", xTxt, , , , , 8)
    If xOutRg Is Nothing Then Exit Sub
    Set xOutRg = xOutRg.Range(1)
    xLRow = xRg.Rows.Count
    For i = 2 To xLRow
        xCol.Add xRg.Cells(i, 1).Value, xRg.Cells(i, 1).Value
    Next
    Application.ScreenUpdating = False
    For i = 1 To xCol.Count
        xCrit = xCol.Item(i)
        xOutRg.Offset(i, 0) = xCrit
        xRg.AutoFilter Field:=1, Criteria1:=xCrit
        Set xVRg = xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible)
        If xVRg.Count > xCount Then xCount = xVRg.Count
        xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible).Copy
        xOutRg.Offset(i, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        Application.CutCopyMode = False
    Next
    xOutRg = xRg.Cells(1, 1)
    xOutRg.Offset(0, 1).Resize(1, xCount) = xRg.Cells(1, 2)
    xRg.Rows(1).Copy
    xOutRg.Resize(1, xCount + 1).PasteSpecial Paste:=xlPasteFormats
    xRg.AutoFilter
    Application.ScreenUpdating = True
End Sub

3. Em seguida, pressione a tecla F5 para executar este código, e uma caixa de prompt aparecerá para lembrá-lo de selecionar o intervalo de dados que deseja usar, veja a captura de tela:

4. E então clique no botão OK, outra caixa de prompt aparecerá para lembrá-lo de selecionar uma célula para colocar o resultado, veja a captura de tela:

6. Clique no botão OK, e os dados na coluna B terão sido transpostos com base nos valores únicos na coluna A, veja a captura de tela:

Transpor células em uma coluna com base em valores únicos com Kutools para Excel

Se você tiver o Kutools para Excel, combinando as utilidades Avançado Combinar Linhas e Dividir Células, poderá concluir rapidamente essa tarefa sem qualquer fórmula ou código.

Após instalar o Kutools para Excel, por favor, faça o seguinte:

1. Selecione o intervalo de dados que deseja usar. (Se quiser manter os dados originais, copie e cole os dados em outro local primeiro.)

2. Em seguida, clique em Kutools > Mesclar e Dividir > Avançado Combinar Linhas, veja a captura de tela:

3. Na caixa de diálogo Combinar Linhas Baseadas na Coluna, por favor, faça as seguintes operações:

(1.) Clique no nome da coluna com base na qual deseja transpor os dados e selecione Chave Primária;

(2.) Clique em outra coluna que deseja transpor, e clique em Combinar e escolha um separador para separar os dados combinados, como espaço, vírgula, ponto e vírgula.

4. Em seguida, clique no botão Ok, os dados na coluna B terão sido combinados em uma célula com base na coluna A, veja a captura de tela:

5. E então selecione as células combinadas, e clique em Kutools > Mesclar e Dividir > Dividir Células, veja a captura de tela:

6. Na caixa de diálogo Dividir Células, selecione Dividir em Colunas sob a opção Tipo, e então escolha o separador que separa seus dados combinados, veja a captura de tela:

7. Em seguida, clique no botão Ok, e selecione uma célula para colocar o resultado dividido na caixa de diálogo que apareceu, veja a captura de tela:

8. Clique em OK, e você obterá o resultado de que precisa. Veja a captura de tela:

Baixe e experimente gratuitamente o Kutools para Excel agora!

Demonstração: Transpor células em uma coluna com base em valores únicos com Kutools para Excel