Como transpor células de uma coluna com base em valores únicos presentes noutra coluna?
Suponha que tem um intervalo de dados com duas colunas e pretende transpor os valores de uma coluna para linhas horizontais, agrupando-os pelos valores únicos da outra coluna, de modo a obter o resultado seguinte. Tem alguma boa ideia para resolver este problema no Excel?
Transpor células numa coluna com base em valores únicos com fórmulas
Transpor células numa coluna com base em valores únicos com código VBA
Transpor células numa coluna com base em valores únicos com Kutools para Excel
Transpor células numa coluna com base em valores únicos com fórmulas
Com as seguintes fórmulas matriciais, pode extrair os valores únicos e transpor os respetivos dados para linhas horizontais. Siga estes passos:
1. Introduza esta fórmula matricial: =ÍNDICE($A$2:$A$16; CORRESP(0; CONTAR.SE($D$1:$D1; $A$2:$A$16); 0)) numa célula vazia, por exemplo D2, e prima simultaneamente as teclas Shift + Ctrl + Enter para obter o resultado correto. Veja a imagem:
Nota: Na fórmula acima, A2:A16 é a coluna da qual pretende listar os valores únicos e D1 é a célula imediatamente acima da célula com a fórmula.
2. Em seguida, arraste a alça de preenchimento para baixo até às células para extrair todos os valores únicos. Veja a imagem:
3. Depois, introduza 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 premir simultaneamente as teclas Shift + Ctrl + Enter para obter o resultado. Veja a imagem:
Nota: Na fórmula acima: B2:B16 é a coluna de dados que pretende transpor, A2:A16 é a coluna com base na qual pretende transpor os valores e D2 contém o valor único que extraiu no Passo 1.
4. Em seguida, arraste a alça de preenchimento para a direita até às células onde pretende listar os dados transpostos, parando quando aparecer 0. Veja a imagem:
5. Em seguida, continue a arrastar a alça de preenchimento para baixo ao longo do intervalo de células para obter os dados transpostos, tal como mostrado na imagem seguinte:
Transpor células numa coluna com base em valores únicos com código VBA
As fórmulas podem ser difíceis de entender. Nesse caso, basta executar o seguinte código VBA para obter o resultado desejado.
1. Prima e mantenha premidas as teclas ALT + F11 para abrir a janela do Microsoft Visual Basic para Aplicações.
2. Clique em Inserir > Módulo e cole o código seguinte na janela do Módulo.
Código VBA: Transpor células numa coluna com base em valores únicos noutra 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 Sub3. Em seguida, prima a tecla F5 para executar este código. Irá aparecer uma caixa de diálogo a solicitar que selecione o intervalo de dados que pretende utilizar. Veja a imagem:
4. Depois, clique no botão OK. Aparecerá outra caixa de diálogo a solicitar que selecione uma célula para inserir o resultado. Veja a imagem:
6. Clique no botão OK e os dados da coluna B serão transpostos com base nos valores únicos da coluna A. Veja a imagem:
Transpor células numa coluna com base em valores únicos com Kutools para Excel
Se tiver o Kutools para Excel, basta combinar as funcionalidades Mesclar Linhas Avançado e Dividir Células para concluir esta tarefa rapidamente — sem precisar de fórmulas nem de código.
Após instalar o Kutools para Excel, proceda da seguinte forma:
1. Selecione o intervalo de dados que pretende utilizar. (Se quiser preservar os dados originais, copie-os e cole-os primeiro noutra localização.)
2. Em seguida, clique em Kutools > Mesclar e Dividir > Mesclar Linhas Avançado. Veja a imagem:
3. Na caixa de diálogo Combinar Linhas Baseado na Coluna, realize as seguintes operações:
(1.) Clique no nome da coluna com base na qual pretende transpor os dados e selecione Chave Primária;
(2.) Clique noutra coluna que pretenda transpor, clique em Combinar e escolha um separador para os dados combinados, como espaço, vírgula ou ponto e vírgula.
4. Em seguida, clique no botão Ok e os dados da coluna B serão combinados numa única célula com base na coluna A. Veja a imagem:
5. Em seguida, selecione as células combinadas e clique em Kutools > Mesclar e Dividir > Dividir Células. Veja a imagem:
6. Na caixa de diálogo Dividir Células, selecione Dividir em Colunas na opção Tipo e escolha o separador que utilizou nos seus dados combinados. Veja a imagem:
7. Em seguida, clique no botão Ok e selecione uma célula para colocar o resultado da divisão na caixa de diálogo que aparece. Veja a imagem:
8. Clique em OK e obterá o resultado pretendido. Veja a imagem:
Descarregue e experimente gratuitamente o Kutools para Excel agora!
Demonstração: Transpor células numa coluna com base em valores únicos com Kutools para Excel
As Melhores Ferramentas de Produtividade para o Office
Potencie as suas competências no Excel com Kutools para Excel e experimente uma eficiência como nunca antes.Kutools para Excel Oferece mais de 300 funcionalidades avançadas para impulsionar a produtividade e Economizar Tempo.Clique aqui para obter a funcionalidade de que mais precisa...
Office Tab Traz uma interface com separadores para o Office e torna o seu trabalho muito mais fácil
- Ative a edição e leitura com separadores no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novos separadores da mesma janela, em vez de em janelas separadas.
- Aumente a sua produtividade em 50 % e elimine centenas de cliques do rato todos os dias!
Todos os suplementos Kutools — num único instalador.
Kutools for Office reúne suplementos para Excel, Word, Outlook e PowerPoint, além do Office Tab Pro, sendo a solução ideal para equipas que trabalham em várias aplicações do Office.
- Pacote tudo-em-um— suplementos para Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Um instalador, uma licença— configuração em minutos (pronto para MSI)
- Funciona melhor em conjunto— produtividade simplificada em todas as aplicações do Office
- Teste gratuito de 30 dias com todas as funcionalidades— sem registo, sem cartão de crédito
- Melhor relação qualidade-preço— poupe face à compra individual dos suplementos