Como usar TOCOL e TOROW no Excel para transformar matrizes em colunas ou linhas

Introdução

Reformatar uma matriz bidimensional em uma única coluna ou linha é uma operação comum em preparação de dados, consolidação e criação de listas mestras. As funções TOCOL e TOROW foram adicionadas para resolver essa tarefa de forma nativa e eficiente. Elas funcionam com arrays dinâmicos, o que simplifica processos repetitivos e mantém a planilha atualizada quando os dados origem mudam.

Definição rápida:

• TOCOL: converte uma matriz 2D em uma única coluna vertical.
• TOROW: converte uma matriz 2D em uma única linha horizontal.

Compatibilidade: Excel 2024 ou posterior, Excel para Microsoft 365, Excel para a web, apps móveis.

Sumário do artigo

• Sintaxe e argumentos de TOCOL e TOROW
• Exemplos práticos com instruções passo a passo
• Mesclar múltiplas matrizes com VSTACK e HSTACK
• Remover vazios e erros e criar listas únicas ordenadas (UNIQUE + SORT)
• Alternativas (TRANSPOSE, Power Query, VBA) e quando usá-las
• Contraexemplos e limitações
• Playbook para copiar resultados como valores, testes e critérios de aceitação
• Checklists por função e por papel
• Mini-glossário

Sintaxe e explicação de argumentos

TOCOL e TOROW partilham a mesma assinatura básica:

=TOCOL(a,[b],[c])
=TOROW(a,[b],[c])

Parâmetros:

• a (obrigatório): matriz a ser transformada.
• b (opcional): tipo de valores a ignorar: 1 = ignorar vazios, 2 = ignorar erros, 3 = ignorar vazios e erros. Se omitido, nenhum valor será ignorado.
• c (opcional): Booleano que define a direção de leitura: omitido ou FALSE = varre por linhas (linha 1, depois linha 2); TRUE = varre por colunas (coluna 1, depois coluna 2).

Comportamentos importantes:

• Resultado dinâmico: o array “espalha” (spill) automaticamente para as células vizinhas. Alterações na matriz origem atualizam o resultado.
• Incompatibilidade com Tabelas do Excel: arrays dinâmicos não podem ser colocados diretamente dentro de um objeto Tabela (ListObject). Para inserir o resultado em uma Tabela, copie e cole os valores.

Exemplos práticos: TOCOL passo a passo

Cenário: você é gestor e tem uma matriz em B2:F17 com nomes de gerentes por tipo de loja e país. Objetivo: transformar esses nomes em uma lista única em coluna.

Exemplo básico — sem filtros:

=TOCOL(B2:F17)

Passos:

1. Em uma célula vazia, digite =TOCOL(.
2. Selecione o intervalo B2:F17.
3. Feche ) e pressione Enter.

Resultado: uma coluna dinâmica que contém todos os valores do intervalo, na ordem de leitura por linhas.

Para omitir vazios e erros:

=TOCOL(B2:F17,3)

Para varrer por colunas em vez de por linhas:

=TOCOL(B2:F17,3,TRUE)

Observação: quando você omite o argumento b, vazios são exibidos (às vezes como células em branco ou zeros, dependendo do conteúdo) e erros são preservados no resultado.

Imagem: Planilha com matriz de nomes de gerentes (colunas representam países, linhas representam tipos de loja).

Exemplos práticos: TOROW passo a passo

Use TOROW quando você precisa de uma linha única:

=TOROW(B2:F17)

Para ignorar vazios e erros e varrer por colunas:

=TOROW(B2:F17,3,TRUE)

Cenário típico: transformar nomes em cabeçalhos de coluna ou preparar uma lista horizontal para validação de dados.

Imagem: Inserção da fórmula TOROW com uma matriz selecionada.

Combinar várias matrizes: VSTACK e HSTACK

Quando você precisa consolidar mais de um intervalo, empilhe-os antes de transformar com TOCOL ou TOROW.

• VSTACK empilha matrizes verticalmente (uma em cima da outra).
• HSTACK junta matrizes horizontalmente (lado a lado).

Exemplos:

Empilhar dois intervalos e transformar em coluna:

=TOCOL(VSTACK(B2:F17,B20:F35),3)

Juntar dois intervalos lado a lado e transformar em coluna:

=TOCOL(HSTACK(B2:F17,B20:F35),3)

Resultado: você obtém uma lista contínua combinando os dois conjuntos de dados. Ajuste o argumento c para controlar a ordem (por linha ou por coluna).

Imagem: Uso de VSTACK para unir intervalos de regiões diferentes.

Criar uma lista única e ordenada: aninhando UNIQUE e SORT

Para extrair valores únicos e ordená-los, aninhe TOCOL/TOROW dentro de UNIQUE e, então, SORT:

=SORT(UNIQUE(TOCOL(B2:F17)))

Para remover vazios e erros primeiro:

=SORT(UNIQUE(TOCOL(B2:F17,3)))

Se usar TOROW, ajuste UNIQUE e SORT para tratar a entrada como linha:

=SORT(UNIQUE(TOROW(B2:F17),TRUE),,,TRUE)

Explicação: UNIQUE por padrão deduplica por coluna; quando a entrada está em linha (TOROW), passe TRUE como segundo argumento. SORT precisa do quarto argumento TRUE para ordenar por linha.

Imagem: Exemplo real com nomes de vencedores e resultado ordenado e único.

Alternativas e quando escolhê-las

• TRANSPOSE: inverte linhas por colunas em um único passo. Não “achata” uma matriz 2D em 1D, mas é útil quando você precisa girar os dados.
• Power Query: ideal para ETL em grandes volumes, transformação repetível e para carregar resultados como Tabelas sem depender de arrays dinâmicos. Use quando a performance e a rastreabilidade forem prioritárias.
• VBA / Macro: quando precisa de automação complexa ou compatibilidade com versões antigas do Excel.
• Fórmulas clássicas (ÍNDICE/SEQUÊNCIA/AGGREGATE): podem replicar comportamento de achatar matrizes em versões antigas, mas costumam ser mais complexas e menos legíveis.

Quando escolher:

• Pequenos a médios conjuntos e necessidade de atualização dinâmica: TOCOL/TOROW.
• Conjuntos muito grandes, transformações repetitivas ou necessidade de carregar em Tabela: Power Query.
• Compatibilidade com Excel antigo: VBA ou fórmulas matriciais complexas.

Contraexemplos e limitações práticas

• Ausência das funções em versões antigas do Excel.
• Dados com células mescladas podem quebrar a leitura.
• Arrays grandes podem impactar a performance.
• Incapacidade de “spill” dentro de uma Tabela do Excel; exige colagem como valores.
• Referências estruturadas em Tabelas precisam de tratamento adicional.

Mitigações:

• Para performance, pré-filtre ou use Power Query.
• Remova mesclas antes de aplicar as funções.
• Se precisar de Tabela, cole como valores e atualize periodicamente via script ou fluxo Power Automate.

Playbook rápido: transformar resultados dinâmicos em valores estáticos

Situação: você quer que a lista resultante faça parte de uma Tabela ou permanecer fixa.

1. Selecione o intervalo resultante (o bloco que “spill”).
2. Pressione Ctrl+C (Copiar).
3. Selecione a célula de destino (ou a primeira célula da Tabela).
4. Pressione Ctrl+Shift+V (Colar valores).
5. Se necessário, formate como Tabela (Inserir > Tabela).

Nota: Depois desse procedimento, o resultado não muda quando a matriz origem for alterada.

Checklist por papel

Analista de Dados:

• Verificar versão do Excel antes de usar TOCOL/TOROW.
• Confirmar ausência de células mescladas.
• Remover cabeçalhos ou rodapés antes de converter.
• Testar saída com argumentos b=3 para limpar vazios/erros.

Gestor / Usuário Final:

• Revisar a lista final para duplicados indesejados.
• Decidir se o resultado precisa ser estático (colar valores) ou dinâmico.

Administrador de TI / BI:

• Se houver planos de padronização, documentar macros ou fluxos Power Query para versões sem suporte.
• Garantir políticas de backups antes de correr macros que alterem dados.

Testes e critérios de aceitação

Cenário de teste: converter matriz B2:F17 em coluna única limpa e ordenada.

Critérios de aceitação mínimos:

1. A fórmula =SORT(UNIQUE(TOCOL(B2:F17,3))) retorna todos os nomes não vazios sem erros.
2. A ordem está em ordem alfabética crescente.
3. Adições na matriz origem são refletidas automaticamente no resultado (testar atualização dinâmica).
4. Depois de colar como valores, o resultado permanece igual mesmo se a origem mudar.

Mini-glossário (1 linha cada)

• Array dinâmico: resultado que “spill” automaticamente em múltiplas células.
• VSTACK/HSTACK: funções que empilham matrizes vertical/horizontalmente.
• UNIQUE: remove duplicatas de uma lista.
• SORT: ordena uma lista por ordem definida.

Compatibilidade e migração

• Excel compatível: Excel 2024+, Microsoft 365 (desktop), Excel para web, apps móveis.
• Usuários de Excel 2019/2016: migre planilhas críticas para Microsoft 365 ou substitua com Power Query/VBA.
• Power Query é recomendado para processos ETL recorrentes e grandes volumes de dados.

Segurança e privacidade

Se a matriz contém dados pessoais identificáveis (PII), verifique as políticas de privacidade da sua organização antes de consolidar ou exportar listas. Evite colagens em arquivos locais não criptografados e audite quem tem acesso às planilhas mestras.

Fluxograma de decisão (Mermaid)

flowchart TD
  A[Preciso transformar uma matriz 2D em 1D?] --> B{Minha versão do Excel
  suporta TOCOL/TOROW?}
  B -- Sim --> C{Tamanho dos dados}
  C -- Pequeno/Médio --> D[Usar TOCOL/TOROW]
  C -- Grande --> E[Considerar Power Query]
  B -- Não --> F[Usar Power Query ou VBA]
  D --> G{Precisa de lista única e ordenada?}
  G -- Sim --> H[UNIQUE + SORT]
  G -- Não --> I[Manter TOCOL/TOROW simples]
  E --> J[Power Query: ETL e carregar como Tabela]
  F --> J

Modelos de fórmulas rápidos (cheat sheet)

• Achatar matriz em coluna (manter tudo): =TOCOL(B2:F17)
• Achatar e ignorar vazios/erros: =TOCOL(B2:F17,3)
• Achatar por coluna: =TOCOL(B2:F17,3,TRUE)
• Achatar em linha: =TOROW(B2:F17)
• Mesclar 2 intervalos e achatar: =TOCOL(VSTACK(B2:F17,B20:F35),3)
• Lista única e ordenada: =SORT(UNIQUE(TOCOL(B2:F17,3)))

Boas práticas e heurísticas

• Regra prática (heurística): se o conjunto tem menos de 5.000 células relevantes, TOCOL/TOROW serão rápidos e fáceis; acima disso, avalie Power Query.
• Sempre use b=3 para limpar vazios/erros antes de deduplicar com UNIQUE, a menos que você precise contabilizar erros ou vazios.
• Nomeie intervalos de origem para facilitar manutenção (por exemplo: Gerentes_Europa).

Exemplo final completo

Suponha que você quer uma lista única, ordenada alfabeticamente, combinando dois intervalos separados (Europa e Ásia), ignorando vazios e erros:

=SORT(UNIQUE(TOCOL(VSTACK(B2:F17,B20:F35),3)))

Resultado: uma única coluna com todos os nomes não vazios e sem duplicatas, em ordem alfabética.

Imagem: Resultado consolidado depois de combinar intervalos e aplicar TOCOL.

Resumo final

• TOCOL e TOROW tornam trivial transformar matrizes 2D em listas 1D dinâmicas.
• Use os argumentos b e c para limpar resultados e controlar direção de leitura.
• Combine com VSTACK/HSTACK para consolidar múltiplas matrizes.
• Aninhe com UNIQUE e SORT para obter listas únicas e ordenadas.
• Se a sua versão do Excel não suportar essas funções ou se os dados forem muito grandes, escolha Power Query ou VBA.

Notas importantes:

• Para inserir o resultado em uma Tabela do Excel, copie e cole como valores.
• Remova células mescladas e verifique se não há referências estruturadas que possam causar erros.

Referências rápidas e recursos adicionais

• Alternativas: TRANSPOSE, Power Query, PivotTables, VBA

Imagem: Divulgação do Microsoft 365.

Microsoft 365 Personal

SO

Windows, MacOS, iPhone, iPad, Android

Teste gratuito

1 mês

Informação: O Microsoft 365 inclui apps como Word, Excel e PowerPoint em até cinco dispositivos e 1 TB de OneDrive. Verifique o preço atual no site Microsoft.

Critérios de aceitação

• A fórmula proposta deve retornar exatamente os valores esperados sem incluir células vazias ou erros quando b=3.
• A ordenação deve corresponder à ordem especificada pelo SORT.
• A solução deve ser reproduzível por outro usuário com a mesma versão do Excel.

1 linha de encerramento

TOCOL e TOROW são ferramentas simples e poderosas para remodelar dados no Excel moderno; quando bem combinadas com VSTACK/HSTACK, UNIQUE e SORT, elas substituem trabalhos manuais e pipelines temporários.