Como converter uma tabela no estilo matriz para três colunas no Excel?
Ao trabalhar com dados no Excel, você pode frequentemente encontrar tabelas no estilo matriz que apresentam informações em uma grade, onde tanto as linhas quanto as colunas servem como cabeçalhos. Embora esse formato seja visualmente útil para algumas análises, pode ser necessário transformar essa matriz em uma "lista" ou tabela de três colunas para tarefas como importação de banco de dados, normalização de dados, criação de gráficos ou análise avançada. Converter a matriz em uma lista de três colunas (às vezes referida como dados "despivotados") permite filtragem, agregação e integração mais fáceis com outras ferramentas de dados. Veja o exemplo abaixo ilustrando a transformação:
➤ Converta uma tabela no estilo matriz para lista com Tabela Dinâmica
➤ Converta uma tabela no estilo matriz para lista com código VBA
➤ Converta uma tabela no estilo matriz para lista com Kutools para Excel
➤ Converta uma tabela no estilo matriz para lista com Fórmula do Excel
Converta uma tabela no estilo matriz para lista com Tabela Dinâmica
No Excel, não há um comando embutido direto para converter uma tabela no estilo matriz em uma lista de três colunas. No entanto, usando o Assistente de Tabela Dinâmica, você pode transformar eficientemente uma matriz tabulada cruzada em dados planos e tabulares adequados para análises adicionais. Essa abordagem é ideal para conjuntos de dados pequenos a médios e especialmente útil quando se deseja simplificar uma estrutura de relatório complexa. É menos adequada para grandes conjuntos de dados ou usuários não familiarizados com operações de Tabela Dinâmica.
1. Abra a planilha contendo sua matriz. Pressione Alt + D, depois P para abrir o Assistente de Tabela e Gráfico Dinâmico. No assistente:
- Em Onde estão os dados que você deseja analisar, escolha Intervalos de consolidação múltipla.
- Em Que tipo de relatório você deseja criar, selecione Tabela Dinâmica.
2. Clique em Próximo. Na Etapa 2a de 3 do diálogo, selecione Criarei os campos de página:
3. Clique Próximo. Em Etapa 2b de 3, clique o 
botão e selecione o intervalo completo de dados da matriz, incluindo cabeçalhos de linha e coluna. Clique Adicionar para inserir o intervalo na Lista de todos os intervalos . Confirme que sua seleção de intervalo cobre toda a matriz.
4. Clique em Próximo. Na Etapa 3 de 3, escolha se deseja colocar a Tabela Dinâmica em uma nova planilha ou em uma célula específica:
5. Clique em Concluir. O Excel gerará uma Tabela Dinâmica resumindo a matriz. Por padrão, ela exibirá totais agregados nas interseções. Você não precisa ajustar a estrutura da Tabela Dinâmica para esta tarefa — apenas prossiga para a próxima etapa:
6. Dê um duplo clique na célula onde os Grandes Totais de linha e coluna se cruzam (por exemplo, célula F22). O Excel criará uma nova planilha contendo uma lista de três colunas, onde cada linha mostra uma combinação única de cabeçalho de linha e coluna com o valor correspondente.
7. Para finalizar, selecione a nova tabela, clique com o botão direito do mouse, depois escolha Tabela > Converter em Intervalo. Isso remove a formatação da tabela, deixando uma lista simples e editável:
Dica: Se sua matriz mudar frequentemente, será necessário repetir este processo para atualizar a lista de três colunas. Este método é melhor para dados estáticos. Além disso, se sua matriz contiver células em branco ou mescladas, pode ser necessário fazer uma limpeza antes de usar esta técnica.
Converta uma tabela no estilo matriz para lista com código VBA
Se você prefere automação ou deseja aplicar essa transformação repetidamente, usar uma macro VBA pode rapidamente converter qualquer tabela no estilo matriz em uma lista estruturada de três colunas. Esse método é especialmente eficaz para grandes conjuntos de dados ou layouts diversos e elimina a necessidade de formatação manual. É ideal para usuários familiarizados com a execução de scripts VBA.
1. Pressione Alt + F11 para abrir o editor Microsoft Visual Basic for Applications.
2. No editor, clique em Inserir > Módulo para criar um novo módulo. Depois, cole o seguinte código na janela do módulo:
📜 Código VBA: Converter matriz para lista
Sub ConvertTable()
' Updated by Extendoffice
Dim Rng As Range
Dim cRng As Range
Dim rRng As Range
Dim xOutRng As Range
xTitleId = "KutoolsforExcel"
Set cRng = Application.InputBox("Select your Column labels", xTitleId, Type:=8)
Set rRng = Application.InputBox("Select Your Row Labels", xTitleId, Type:=8)
Set Rng = Application.InputBox("Select your data", xTitleId, Type:=8)
Set outRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set xWs = Rng.Worksheet
k = 1
xColumns = rRng.Column
xRow = cRng.Row
For i = Rng.Rows(1).Row To Rng.Rows(1).Row + Rng.Rows.Count - 1
For j = Rng.Columns(1).Column To Rng.Columns(1).Column + Rng.Columns.Count - 1
outRng.Cells(k, 1) = xWs.Cells(i, xColumns)
outRng.Cells(k, 2) = xWs.Cells(xRow, j)
outRng.Cells(k, 3) = xWs.Cells(i, j)
k = k + 1
Next j
Next i
End Sub3. Pressione F5 ou clique em Executar para executar a macro. Uma série de prompts irá guiá-lo pelas seleções necessárias:
Passo 1: Selecione os rótulos de coluna (geralmente a primeira linha da sua matriz):
Passo 2: Selecione os rótulos de linha (geralmente a primeira coluna da sua matriz):
Passo 3: Selecione o intervalo real de dados da matriz (excluindo cabeçalhos de linha e coluna):
Passo 4: Escolha a célula de saída onde a lista de três colunas convertida deve começar. Recomenda-se usar uma célula em branco ou uma nova planilha:
Passo 5: Clique em OK. Sua matriz agora será convertida em uma lista plana de três colunas.
⚠️ Notas & Dicas:
• Certifique-se de não incluir cabeçalhos de coluna ou linha no intervalo de dados da matriz.
• Se sua matriz tiver células mescladas, desmesclá-las antes de executar a macro para evitar erros.
• Se você encontrar erros, verifique novamente os intervalos selecionados e certifique-se de que eles estejam alinhados corretamente.
Converta uma tabela no estilo matriz para lista com Kutools para Excel
Embora os métodos acima sejam eficazes, podem parecer tediosos ou intimidadores para usuários menos experientes. Se você está procurando por uma solução rápida e amigável, o Kutools para Excel oferece uma utilidade dedicada chamada Converter Dimensão da Tabela, especificamente projetada para esse propósito.
Essa ferramenta é ideal para usuários que frequentemente convertem tabelas no estilo matriz ou precisam realizar processamento em lote. Ela pode preservar a formatação original — como fonte, cor de preenchimento e fórmulas — se necessário. Um possível inconveniente é que o Kutools é um complemento de terceiros que precisa ser instalado, mas é uma opção poderosa para quem regularmente remodela dados no Excel.
Passos:
1. Após instalar o Kutools, vá para a aba Kutools, clique em Intervalo e depois selecione Converter Dimensão da Tabela:
2. Na caixa de diálogo Converter Dimensão da Tabela:
- (1) Escolha Converter Tabela Bidimensional em Lista sob a seção Tipo de Conversão.
- (2) Clique o
botão ao lado de Intervalo de origem para selecionar sua tabela matriz. - (3) Clique o botão ao lado de Intervalo de resultados para especificar onde colocar a saída.
Certifique-se de selecionar toda a matriz (incluindo cabeçalhos e dados) para evitar conversão parcial ou resultados incorretos.
3. Clique em OK. A matriz será instantaneamente convertida em uma lista de três colunas, preservando a formatação de célula original sempre que possível:
Dica: Esta função também suporta a operação inversa — convertendo uma lista plana em uma matriz bidimensional. É útil para reconstruir relatórios ou preparar dados para análise tabular cruzada. Veja: como converter uma lista em uma tabela cruzada 2D.
➤ Saiba mais sobre o recurso Converter Dimensão da Tabela
⏬ Baixe e experimente o Kutools para Excel gratuitamente agora!
Converta uma tabela no estilo matriz para lista com Fórmula do Excel
Se você prefere uma abordagem baseada em fórmulas — especialmente útil se quiser que sua lista de três colunas seja atualizada dinamicamente conforme a matriz muda — pode usar uma combinação das funções ÍNDICE, LINHA, COLUNA e CONT.VALORES para desagrupar manualmente os dados. Esta solução não requer VBA ou complementos e é ideal para aqueles que desejam evitar o uso de macros ou ferramentas externas. No entanto, requer atenção cuidadosa às referências de fórmulas, pois elas geralmente devem ser preenchidas como matrizes ou arrastadas sistematicamente para baixo/através. É mais prático para matrizes de tamanho moderado e situações onde a lista precisa permanecer ativa e responsiva a alterações nos dados de origem.
Suponha que seu intervalo de dados seja o seguinte:
- Os rótulos de linha estão nas células A2:A10.
- Os rótulos de coluna estão nas células B1:J1.
- Os valores da matriz estão nas células B2:J10.
1. Crie uma nova planilha ou comece em uma área vazia da sua planilha existente. Na célula L2, insira a seguinte fórmula para extrair o rótulo de linha:
=INDEX($A$2:$A$10,INT((ROW(A1)-1)/COUNTA($B$1:$J$1))+1)2. Na célula M2, insira esta fórmula para extrair o rótulo de coluna correspondente:
=INDEX($B$1:$J$1,MOD(ROW(A1)-1,COUNTA($B$1:$J$1))+1)3. Na célula N2, extraia o valor da matriz com:
=INDEX($B$2:$J$10,INT((ROW(A1)-1)/COUNTA($B$1:$J$1))+1,MOD(ROW(A1)-1,COUNTA($B$1:$J$1))+1)4. Selecione as células L2:N2 e arraste a alça de preenchimento para baixo. O ponto de parada deve ser número de linhas × número de colunas (neste exemplo, são 9 linhas × 9 colunas = 81 linhas no total).
✅ Dicas:
- Ajuste todos os intervalos para se ajustarem à sua estrutura de dados real.
- Use
SEeÉ.VAZIOpara filtrar linhas vazias, se necessário. - Para comportamento autoexpansivo, considere usar
DESLOCAMENTOou intervalos nomeados dinâmicos. - Este método é mais prático para tamanho de matriz estática e volume moderado de dados.
ℹ️ Notas Adicionais:
- Vantagens: As fórmulas permanecem ativas e refletem automaticamente as mudanças na matriz. Não é necessário VBA ou complementos.
- Desvantagens: Pode reduzir o desempenho da pasta de trabalho para matrizes grandes. Requer configuração cuidadosa.
Demonstração: Converta uma tabela no estilo matriz para lista com Kutools para Excel
Melhores Ferramentas de Produtividade para Office
Impulsione suas habilidades no Excel com Kutools para Excel e experimente uma eficiência incomparável. Kutools para Excel oferece mais de300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para acessar o recurso que você mais precisa...
Office Tab traz interface com abas para o Office e facilita muito seu trabalho
- Habilite edição e leitura por abas no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie múltiplos documentos em novas abas de uma mesma janela, em vez de em novas janelas.
- Aumente sua produtividade em50% e economize centenas de cliques todos os dias!
Todos os complementos Kutools. Um instalador
O pacote Kutools for Office reúne complementos para Excel, Word, Outlook & PowerPoint, além do Office Tab Pro, sendo ideal para equipes que trabalham em vários aplicativos do Office.
- Pacote tudo-em-um — complementos para Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Um instalador, uma licença — configuração em minutos (pronto para MSI)
- Trabalhe melhor em conjunto — produtividade otimizada entre os aplicativos do Office
- Avaliação completa por30 dias — sem registro e sem cartão de crédito
- Melhor custo-benefício — economize comparado à compra individual de add-ins