Recentemente, um amigo meu me procurou com um pedido para ajudar na geração de todas as frases possíveis que consistem em um conjunto de palavras dadas. Problemas desse tipo podem surgir ao compilar listas de palavras-chave e frases para publicidade online e promoção de SEO, quando você precisa passar por todas as permutações possíveis de palavras em uma consulta de pesquisa:
Em matemática, essa operação é chamada de produto cartesiano. A definição oficial é a seguinte: o produto cartesiano dos conjuntos A e B é o conjunto de todos os pares, cujo primeiro componente pertence ao conjunto A e o segundo componente pertence ao conjunto B. Além disso, os elementos dos conjuntos podem ser ambos números e texto.
Traduzido para a linguagem humana, isso significa que se no conjunto A temos, por exemplo, as palavras “branco” e “vermelho”, e no conjunto B “BMW” e “Mercedes”, então após o produto cartesiano desses dois conjuntos temos get on the output é o conjunto de todas as variantes possíveis de frases, compostas pelas palavras de ambas as listas:
- bmw branco
- bmw vermelho
- Mercedes branca
- mercedes vermelho
… ou seja, exatamente o que precisamos. Vejamos algumas maneiras de resolver essa tarefa no Excel.
Método 1. Fórmulas
Vamos começar com fórmulas. Vamos supor que como dados iniciais temos três listas de palavras originais nas colunas A, B e C, respectivamente, e o número de elementos em cada lista pode variar:
Primeiro, vamos fazer três colunas com índices, ou seja, números ordinais de palavras de cada lista em todas as combinações possíveis. A primeira linha de unidades (E2:G2) será inserida manualmente e, para o restante, usaremos a seguinte fórmula:
A lógica aqui é simples: se o índice na célula anterior superior já atingiu o final da lista, ou seja, é igual ao número de elementos da lista calculado pela função CONTAGEM (CONTAGEM), então reiniciamos a numeração. Caso contrário, aumentamos o índice em 1. Preste atenção especial à fixação inteligente dos intervalos com cifrões ($) para que você possa copiar a fórmula para baixo e para a direita.
Agora que temos os números ordinais das palavras que precisamos de cada lista, podemos extrair as próprias palavras usando a função ÍNDICE (ÍNDICE) em três colunas separadas:
Se você ainda não se deparou com essa função em seu trabalho antes, recomendo fortemente que você a estude pelo menos na diagonal – ajuda em muitas situações e é útil não menos (e ainda mais!) VPR (PROCV).
Bem, depois disso, resta apenas colar os fragmentos resultantes linha por linha usando o símbolo de concatenação (&):
… ou (se você tiver a versão mais recente do Excel) com a função útil COMBINAR (TEXTOIN), que pode colar todo o conteúdo das células especificadas por meio de um determinado caractere separador (espaço):
Método 2. Através do Power Query
O Power Query é um suplemento poderoso para o Microsoft Excel que executa duas tarefas principais: 1. carregar dados no Excel de praticamente qualquer fonte externa e 2. todos os tipos de transformações de tabelas carregadas. O Power Query já está integrado ao Excel 2016-2019 e, para o Excel 2010-2013, é instalado como um suplemento separado (você pode baixá-lo gratuitamente no site oficial da Microsoft). Se você ainda não começou a usar o Power Query em seu trabalho, é hora de pensar nisso, pois transformações como as descritas acima são feitas lá de maneira fácil e natural, em apenas alguns movimentos.
Primeiro, vamos carregar as listas de origem como consultas separadas no Power Query. Para fazer isso, para cada tabela, execute as seguintes etapas:
- Vamos transformar as mesas em mesas “inteligentes” com um botão Formatar como uma tabela aba INÍCIO
(Início — Formatar como Tabela) ou atalho de teclado Ctrl+T. Cada tabela receberá automaticamente um nome Tabela 1,2,3…, que, no entanto, pode ser alterado se desejar na guia Construtor (Desenhar).
- Tendo definido a célula ativa na tabela, pressione o botão Da mesa (Da tabela) aba Data (Encontro: Data) ou na aba Consulta de energia (se você o tiver instalado como um suplemento separado para o Excel 2010-2013).
- Na janela do editor de consultas que se abre, selecione o comando Home — Fechar e carregar — Fechar e carregar em… (Início — Fechar e carregar — Fechar e carregar para..) e depois a opção Basta criar uma conexão (Criar apenas conexão). Isso deixará a tabela carregada na memória e permitirá que ela seja acessada no futuro.
Se você fizer tudo corretamente, a saída no painel direito deve ser três solicitações no modo Somente conexão com os nomes das nossas tabelas:
Agora clique com o botão direito do mouse na primeira consulta e selecione o comando Ligação (Referência)para fazer uma cópia atualizável dele e, em seguida, adicionar uma coluna adicional aos dados por meio do comando Adicionando uma coluna ¾ – Coluna personalizada (Adicionar Coluna - Coluna Personalizada). Na janela de entrada de fórmula, insira o nome da nova coluna (por exemplo, Fragment2) e uma expressão extremamente simples como uma fórmula:
=Tabela2
… ou seja, em outras palavras, o nome da segunda consulta:
Depois de clicar em OK veremos uma nova coluna, em cada célula da qual haverá uma tabela aninhada com frases da segunda tabela (você poderá ver o conteúdo dessas tabelas se clicar no fundo da célula ao lado da palavra mesa):
Resta expandir todo o conteúdo dessas tabelas aninhadas usando o botão com setas duplas no cabeçalho da coluna resultante e desmarcando Use o nome da coluna original como prefixo (Use o nome da coluna original como prefixo):
… e obtemos todas as combinações possíveis de elementos dos dois primeiros conjuntos:
Além disso, tudo é semelhante. Adicione outra coluna calculada com a fórmula:
=Tabela3
…, e depois expanda as tabelas aninhadas novamente – e agora já temos todas as opções possíveis para permutar palavras dos três conjuntos, respectivamente:
Resta selecionar todas as três colunas da esquerda para a direita, segurando Ctrl, e concatenar seu conteúdo separado por espaços usando o comando Mesclar colunas (Mesclar colunas) da guia Transformação (Transformar):
Os resultados resultantes podem ser descarregados de volta na folha com o comando já familiar Home — Fechar e carregar — Fechar e carregar em… (Início — Fechar e carregar — Fechar e carregar para..):
Se no futuro algo mudar em nossas tabelas de origem com fragmentos, será suficiente apenas atualizar a consulta gerada clicando com o botão direito do mouse na tabela resultante e escolhendo o comando Atualizar e salvar (Atualizar) ou pressionando o atalho de teclado Ctrl+outro+F5.
- O que é Power Query, Power Pivot, Power Map e Power BI e por que eles precisam de um usuário do Excel
- Criando um gráfico de Gantt no Power Query
- 5 maneiras de usar a função INDEX