Como fazer um cálculo (como VLOOKUP) para cada item que foi inserido com Alt + em uma célula.
Assistir vídeo
- Um visualizador baixa dados de um sistema onde cada item é separado por Alt + Enter
- Bill: Por que você está fazendo isso? Visualizador: É assim que herdo os dados. Eu quero manter dessa forma.
- Bill: O que você quer fazer com os 40% dos valores que não estão na tabela? Visualizador: sem resposta
- Bill: Existe uma maneira complicada de resolver isso se você tiver as ferramentas Power Query mais recentes.
- Em vez disso, uma macro VBA para resolvê-lo - a macro deve funcionar desde o Excel 2007
- Em vez de fazer VLOOKUP, faça uma série de Find & Replace with VBA
Transcrição de vídeo
Aprenda Excel com, Podcast Episódio 2150: VLOOKUP Cada Alt + Valor Inserido Em Cada Célula.
Ei. Bem-vindo de volta ao netcast. Sou Bill Jelen. Hoje, uma das perguntas mais bizarras. Alguém disse: ei, quero fazer um PROCV para cada valor da célula e, quando abri o arquivo do Excel, os dados foram inseridos com ALT +. Portanto, há 4 itens nesta ordem e estão todos separados por ALT + ENTER, e apenas 2 aqui e 6 aqui e assim por diante.
Voltei para a pessoa que enviou isso. Eu pensei, bem, essa é uma maneira muito ruim de armazenar esses dados. Por que você está fazendo isso? E ele disse, eu não estou fazendo isso. É assim que os dados são baixados. Eu disse, tudo bem se eu dividi-lo em linhas diferentes? Não, você precisa mantê-lo assim.
Tudo bem. Portanto, não há uma boa maneira de fazer uma VLOOKUP para cada item individual e amanhã, no episódio de amanhã, 2151, vou mostrar como podemos usar um novo recurso do Power Query para fazer isso, mas você teria ter o Office 365 para isso.
Então, hoje, eu quero usar um método que vai de volta ao passado, e o que fiz aqui foi criar uma nova planilha com o LOOKUPTABLE, então esses são os itens. Eu também notei que há um monte de coisas, cerca de 40% das coisas aqui, não estão no LOOKUPTABLE. Eu disse, o que você quer fazer lá, e nenhuma resposta a essa pergunta, então vou deixá-los como estão se não encontrar uma correspondência.
Certo, então, o que eu tenho aqui é uma planilha chamada LOOKUPTABLE e você verá que meu arquivo agora está armazenado como xlsx e vou usar uma macro VBA. Para usar uma macro VBA, você não pode tê-la como xlsx. É contra as regras. Então, você tem que SALVAR COMO e salvar este é xlsm. ARQUIVAR, SALVAR COMO e alterá-lo de LIVRO DE TRABALHO para LIVRO DE TRABALHO XLSM ATIVADO PARA MACRO ou LIVRO DE TRABALHO BINÁRIO - qualquer um deles funcionará - certo, e clique em SALVAR.
Tudo bem, agora temos permissão para executar macros. ALT + F11 para acessar o gravador de macro. Você começa com essa grande tela cinza. INSERT, MODULE e aí está nosso módulo, e aqui está o código. Então, chamei de ReplaceInPlace e defino uma planilha. Essa é a LookupTable. Você colocaria o nome da planilha da tabela de pesquisa real lá e, em seguida, minha tabela de pesquisa começaria na coluna A, que é a coluna 1. Então, vou para a última linha da coluna 1, pressiono a tecla END e a seta PARA CIMA, ou , é claro, CONTROL + seta PARA CIMA faria a mesma coisa, descobrir qual linha é, e então iremos de cada linha de 2 para FinalRow. Por que 2? Bem, porque os cabeçalhos estão na linha 1. Então, eu quero substituir, começando na linha 2 até a última linha, e assim, para cada linha de 2 a FinalRow, FromValue é o que 's na coluna A e o ToValue é o que está na coluna B.
Agora, se, por algum motivo, seus dados estavam em J e K, então este J seria a 10ª coluna, então você coloca um 10 lá, e K seria a 11ª coluna, e então, na Seleção, vamos substituir o FromValue para o ToValue. Isso é muito importante aqui. xlPart, xlPart - e isso é um L, não um número 1 - xlPart que diz que nos permitirá substituir parte da célula porque esses números de peça são todos separados por um caractere de alimentação de linha. Mesmo que você não possa ver, ele está lá. Portanto, isso deve nos permitir não atualizar acidentalmente a coisa errada e, em seguida, xlByRows, MatchCase, False, SearchFormat, False, ReplaceFormat, False, Next i.
Tudo bem. Então, essa é nossa pequena macro aqui. Vamos tentar. Pegaremos esses dados e não quero destruir nada, então vou apenas pegar os dados originais e copiá-los à direita. Tudo bem. Então, nós temos nossa seleção lá. Na verdade, vou começar deste ponto. CONTROL + BACKSPACE e ALT + F8 para obter uma lista de todas as macros. Aqui está o nosso REPLACEINPLACE. Clicarei em EXECUTAR e, em todos os lugares em que encontrou um item em LOOKUPTABLE, ele substituiu esse número de item pelo item, aparentemente fazendo um PROCV, embora não estejamos resolvendo com um PROCV.
Tudo bem. Então, ei, o novo livro que saiu - Power Excel With, a edição de 2017, 617 Excel Mysteries Solved - todos os tipos de ótimas dicas.
Conclusão de hoje: o visualizador baixa dados de um sistema em que cada item é separado por ALT + ENTER e, em seguida, precisa fazer uma VLOOKUP em cada item e, você sabe, por que estou fazendo isso; então, a pessoa falou, não tô fazendo mas preciso continuar assim; e então 40% dos valores não estão na tabela, bom, nenhuma resposta; então acho que eles vão adicionar esses itens à mesa; agora, amanhã, vamos falar sobre como resolver isso com o Power Query, mas, hoje, essa macro vai funcionar em todas as versões do Excel do Windows, voltando ao Excel 2007 pelo menos; então, em vez de VLOOKUP, apenas uma série de localizar e substituir com VBA.
Bem, ei. Eu quero te agradecer por passar por aqui. Nos vemos na próxima vez para outro netcast de.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2150.xlsm
