O Excel VLOOKUP pode retornar todos os resultados e juntá-los com uma vírgula no meio?
Assistir vídeo
- O objetivo é concatenar todas as respostas de texto de um VLOOKUP
- Método de Bill: use uma função VBA chamada GetAll
- Lista única usando Remover Duplicados
- Método de Mike:
- Lista única usando filtro avançado
- Função TEXTJOIN adicionada no Office 365
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- Por causa da função IF, a fórmula requer Ctrl + Shift + Enter sempre que você editar a fórmula
- Alt AQOR Enter executará novamente o Filtro avançado!
Transcrição de vídeo
Episódio 183: Junte-se a todas as partidas VLOOKUP
Bill Jelen: Ei, bem-vindo de volta. É hora de outro Podcast de duelo do Excel. Chamo-me Bill Jelen, sou acompanhado por Mike Girvin do Excel Is Fun. Este é o nosso Episódio 183: Junte-se a todas as partidas de VLOOKUP.
(Música)
Tudo bem, pergunta de hoje do Matt. A VLOOKUP pode retornar todos os resultados e juntá-los com um espaço de vírgula entre cada um. Por exemplo, 109876 que são esses dois aqui, pode retornar Low Oil vírgula space Verificado em 12/12. E, claro, se houvesse mais, ele retornaria mais. Tudo bem, então minha solução aqui será usar algum VBA. Tudo bem, então certifique-se de que está salvo como xlsm ou você não pode executar VBA ou xlsb, mas não xlsx - xlsx é o único arquivo que não pode executar VBA. Vamos pressionar Alt + F11, verifique se você está no Dual183 ou qualquer que seja o nome da sua pasta de trabalho. Insira o módulo no módulo em branco e vamos colar esse código, certo.
Vamos dar uma olhada nesta função GetAll, e aqui está o número de ID que estamos procurando e, em seguida, o intervalo que queremos examinar. E começamos, vamos retornar uma variável chamada GetAll, então começamos com isso sendo igual a branco em branco. Para cada célula no meu intervalo, se o valor da célula é o que estamos procurando, vamos pegar GetAll = GetAll & “” e então Cell.Offset (0 linhas, 1 coluna), em outras palavras, o valor que fica próximo ao número de identificação porque, no VBA, aqui está o número de identificação. Se encontrarmos o número de ID correspondente, queremos avançar 1 coluna. Agora, e se você quiser passar 2 colunas ou 3 colunas, bem, então você altera 0 linhas e 1 coluna para 2. Tudo bem, também verifique se - não colocamos um espaço de vírgula se for o primeiro.Portanto, se a variável GetAll for atualmente “”, não colocaremos o espaço de vírgula, certo?
Agora que temos essa função aqui, observe como é fácil resolver o problema de Matt. Nós vamos vir aqui e pegar seus IDs, Ctrl + C e colar Ctrl + V assim. Dados, remover duplicatas, clique em OK. Portanto, há uma lista exclusiva de IDs e queremos dizer = getall e estamos procurando esse valor na vírgula E2. Olhando por este intervalo aqui, pressionarei F4. F4 funciona como uma função normal. E novamente movendo a pergunta de Matt para fora do caminho, clique duas vezes para derrubá-la. Vai funcionar.
E vamos tentar, vamos tentar algo maluco aqui. Vamos fazer uma Frase 1 e apenas colocar um monte delas, como Frase 1 a 10. Iremos assinar todas elas em 109999. Cole e cole aqui. Copie essa fórmula para baixo, edite a fórmula para que vá até o final, é claro. Sim. E ele retornará todas essas frases. Tudo bem, essa é minha solução, VBA, uma pequena função aqui. Mike, vamos ver o que você tem.
Mike Girvin: Obrigado. GetAll, essa é uma função VBA incrível. Tudo bem, vou passar para a folha bem aqui. Já o converti em uma tabela do Excel para que, quando adicionarmos os registros abaixo, esperemos que as coisas sejam atualizadas.
Agora, a primeira coisa que farei em duas partes. Eu poderia fazer uma fórmula aqui para extrair uma lista exclusiva, mas quero ver outra opção: o filtro avançado tem uma opção de extrair lista exclusiva e pode ser atualizada. Vou destacar apenas os dados da coluna ID, em Filtro avançado ou vou usar o teclado Alt, A, Q. Agora, lista de filtros no local, de jeito nenhum. Quero copiá-lo para outro local. Ele tem apenas a coluna A e porque é uma tabela do Excel que se expandirá posteriormente. Não tenho nenhum critério, desejo copiá-lo para D1 e verificar apenas os registros exclusivos. Clique OK.
Agora, vou vir aqui, entrar Todos os comentários e vou usar uma função que só funciona no Excel 2016 Office 365: = função TEXTJOIN. Esta função por si só vale a pena obter a versão mais recente do Excel. Esta é uma tarefa tão comum que as pessoas querem fazer, juntar muitas coisas. Agora nosso delimitador em “,”, e a melhor coisa sobre essa função é que podemos dizer a ela para Ignorar células vazias. Agora, posso colocar TRUE, 1 ou Deixar, Omitir. Então, eu vou deixar isso, omitir. E é aqui que precisamos do nosso texto. Vamos usar a função IF para filtrar e obter apenas os itens que queremos. Vou dizer, olhe por toda esta coluna aqui: Nome da tabela e depois em () o nome do campo, qualquer um de vocês = para esta referência de célula relativa, esse é o teste lógico. Se eu clicar aqui e pressionar a tecla F9 para avaliar,você pode ver que agora temos apenas 2 VERDADEIRAS, Ctrl + Z agora eu digito uma vírgula e com a matriz de Verdades e Falsas, agora posso fornecer os itens para selecionar. Portanto, agora, escolheremos apenas os itens que possuem um VERDADEIRO neste intervalo. Vírgula e eu queremos ter certeza de colocar “” - que aparecerá como uma célula vazia em relação ao segundo argumento em TEXTJOIN.
Agora, fecharei os parênteses e agora a função IF criará aquela string de verdadeiros e falsos, os itens reais deste intervalo serão selecionados se virem True e todos os outros itens terão aquela célula vazia. E adivinha? TEXTJOIN irá ignorar totalmente todas as células vazias e retornar apenas os itens que correspondem a este ID e, em seguida, juntá-lo a esse delimitador. Agora, esta é definitivamente uma fórmula de matriz que requer o pressionamento de tecla especial Ctrol + Shift + Enter. O argumento de teste lógico contém nossa operação Array e esse argumento não pode calcular essa operação Array corretamente, a menos que usemos o teclado Ctrl + Shift + Enter. Agora vou fechar os parênteses. Na verdade, poderíamos provar 1 bem aqui no Texto 1 se eu F9 tudo isso, poderíamos ver que obtemos os 2 itens, o resto dessas células vazias serão ignoradas. Ctrl + Z. Agora deixe's insira-o na célula com Ctrl + Shift + Enter. Olhe imediatamente para a Barra de Fórmulas. Essas chaves são do Excel dizendo que entendeu e calculou isso como uma fórmula de matriz. Agora posso clicar duas vezes e enviar. Isso parece bom.
Vou para a última célula e pressiono F2 para verificar se todos os intervalos estão parecendo corretamente. Agora, o que eu não quero fazer é não quero pressionar Enter porque aquela fórmula depois de colocá-la no Modo de Edição só calculará corretamente se usarmos Ctrl + Shift + Enter; ou, porque já inserimos a fórmula, podemos simplesmente usar a tecla Esc para reverter para o que quer que esteja na célula antes de colocá-la no Modo de Edição.
Agora, vamos testar isso. Vou clicar na última célula aqui embaixo, clicar em Tab e digitar um novo ID, Tab, Tab. Outro novo recorde, Tab, e já posso ver que não tive trabalho suficiente aqui. Eu estou, vamos colocar - Perfeito e depois Enter. Agora, isso não vai ser atualizado automaticamente como se tivéssemos um monte de fórmulas que contamos itens únicos e depois extraímos itens únicos, mas sem problemas. Vê isto. Podemos atualizar esta lista de registros exclusivos porque usamos o Filtro Avançado e não importa de qual célula você inicia, porque quando o Filtro Avançado é invocado, ele memoriza o intervalo de extração e os intervalos que estava originalmente procurando. Você pode clicar em Filtro avançado ou usar o teclado Alt + A + Q. Precisamos selecionar Copiar para outro local, mas observe isso.É totalmente lembrado e expandido para A13 por causa do recurso Tabela do Excel. Ele se lembrou do intervalo de extração. Preciso verificar apenas os registros exclusivos, mas clique em OK.
Agora, eu tenho que vir e copiar esta fórmula. E aí está, usando Advanced Filter e a incrível função TEXTJOIN com, na operação Array para obter apenas os itens correspondentes. Tudo bem, jogue de volta para.
Bill Jelen: Hey, Mike, that is awesome. Alright, wrapping up this episode. I used the VBA function called GetAll, and my unique list was created by Remove Duplicates which is far easier than Advanced Filter but the problem is it’s a one-time thing. It doesn't remember the previous settings. Mike created his unique list using Advanced Filter which means that he could later redo that Advanced Filter without re-specifying the input range and the extract range. And then TEXTJOIN, a beautiful new function, added an Office 365. Mike says that alone is a reason to get the latest Office. I said the TEXTJOIN would be life-changing. TEXTJOIN is awesome because it can handle Arrays.
Alright, so here's the formula that Mike wrote: putting an IF in there and the “ ” returning the equivalent of an empty cell; and here we're saying Ignore empty cells. Ahh, that's beautiful but because of the IF function, the formula requires Ctrl+Shift+Enter to create the formula, or any time you edit the formula, all that Mike used to Esc to get out. And this section about IF forces you into Ctrl+Shift+Enter is a topic in Mike's awesome, awesome book, An Array Formulas Ctrl+Shift+Enter. Check that out at Amazon or elsewhere, your favorite bookseller. And then, the beautiful thing is that because Advanced Filter remembers the old settings, Mike used Alt+A+Q and then could have used O+R Enter, will rerun the Advanced Filter, copy the formula down for the new cells and it works. That is beautiful, alright.
Oh, ei, quero agradecer a todos pela visita. Nos vemos na próxima vez para outro podcast Dueling Excel de e Excel Is Fun.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Duel183.xlsm
