Como transpor células em uma coluna com base em valores únicos em outra coluna?
Supondo que você tenha um intervalo de dados que contém duas colunas, agora você deseja transpor células de uma coluna para linhas horizontais com base em valores exclusivos em outra coluna para obter o seguinte resultado. Você tem alguma boa ideia para resolver este problema no Excel?
Transponha 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
Transponha células em uma coluna com base em valores únicos com o Kutools para Excel
Transponha células em uma coluna com base em valores únicos com fórmulas
Com as seguintes fórmulas de matriz, você pode extrair os valores exclusivos e transpor seus dados correspondentes em linhas horizontais. Faça o seguinte:
1. Insira esta fórmula de matriz: = ÍNDICE ($ A $ 2: $ A $ 16, CORRESPONDÊNCIA (0, CONTADOR ($ D $ 1: $ D1, $ A $ 2: $ A $ 16), 0)) em uma célula em branco, D2, por exemplo, e pressione Shift + Ctrl + Enter juntas para obter o resultado correto, veja a captura de tela:
Note: Na fórmula acima, A2: A16 é a coluna da qual você deseja listar os valores exclusivos, e D1 é a célula acima desta célula de fórmula.
2. Em seguida, arraste a alça de preenchimento até as células para extrair todos os valores exclusivos, consulte a captura de tela:
3. E então continue inserindo esta fórmula na célula E2: =IFERROR(INDEX($B$2:$B$16, MATCH(0, COUNTIF($D2:D2,$B$2:$B$16)+IF($A$2:$A$16<>$D2, 1, 0), 0)), 0), e lembre-se de pressionar Shift + Ctrl + Enter chaves para obter o resultado, consulte a imagem:
Note: Na fórmula acima: B2: B16 são os dados da coluna que você deseja transpor, A2: A16 é a coluna na qual você deseja transpor os valores, e D2 contém o valor exclusivo que você extraiu na Etapa 1.
4. Em seguida, arraste a alça de preenchimento para a direita das células que deseja listar os dados transpostos até exibir 0, consulte a captura de tela:
5. Em seguida, continue arrastando a alça de preenchimento até o intervalo de células para obter os dados transpostos, conforme mostrado a seguir:
Transpor células em uma coluna com base em valores únicos com código VBA
Pode ser que as fórmulas sejam complexas para você entender, aqui, você pode executar o seguinte código VBA para obter o resultado desejado de que precisa.
1. Segure o ALT + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.
2. Clique inserção > Móduloe cole o seguinte código no Módulo Janela.
Código VBA: transponha células em uma coluna com base em valores exclusivos 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. Então aperte F5 para executar este código e uma caixa de prompt aparecerá para lembrá-lo de selecionar o intervalo de dados que deseja usar, consulte a captura de tela:
4. E, em seguida, clique em OK botão, outra caixa de prompt aparecerá para lembrá-lo de selecionar uma célula para colocar o resultado, veja a captura de tela:
6. Clique OK botão, e os dados na coluna B foram transpostos com base em valores únicos na coluna A, consulte a captura de tela:
Transponha células em uma coluna com base em valores únicos com o Kutools para Excel
Se você tem Kutools for Excel, combinando o Combinar linhas avançadas e Dividir células utilitários, você pode concluir essa tarefa rapidamente sem fórmulas ou códigos.
| Kutools for Excel : com mais de 300 suplementos úteis do Excel, grátis para testar sem limitação em 30 dias. |
Depois de instalar Kutools for Excel, faça o seguinte:
1. Selecione o intervalo de dados que você deseja usar. (Se você deseja manter os dados originais, copie e cole os dados em outro local primeiro.)
2. Então clique Kutools > Unir e dividir > Combinar linhas avançadas, veja a captura de tela:
3. No Combinar linhas com base na coluna caixa de diálogo, execute as seguintes operações:
(1.) Clique no nome da coluna em que deseja transpor os dados e selecione Chave primária;
(2.) Clique em outra coluna que deseja transpor e clique em Combinar em seguida, escolha um separador para separar os dados combinados, como espaço, vírgula, ponto e vírgula.
4. Então clique Ok botão, os dados na coluna B foram combinados em uma célula com base na coluna A, veja a captura de tela:
5. Em seguida, selecione as células combinadas e clique em Kutools > Unir e dividir > Dividir células, veja a captura de tela:
6. No Dividir células caixa de diálogo, selecione Dividir em colunas sob a Formato opção e, em seguida, escolha o separador que separa seus dados combinados, consulte a captura de tela:
7. Então clique Ok botão e selecione uma célula para colocar o resultado da divisão na caixa de diálogo exibida, consulte a captura de tela:
8. Clique OK, e você obterá o resultado que precisa. Veja a imagem:
Baixe e teste grátis Kutools para Excel agora!
Demonstração: Transponha células em uma coluna com base em valores únicos com Kutools para Excel
Melhores ferramentas de produtividade de escritório
Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para obter o recurso que você mais precisa...
Office Tab traz interface com guias para o Office e torna seu trabalho muito mais fácil
- Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
- Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
