Como escrever uma fórmula do Excel que pesquisará um valor em uma planilha diferente com base no produto selecionado. Como extrair dados de uma planilha diferente para cada produto.
Assistir vídeo
- Rhonda de Cincinnati: Como procurar linha e planilha?
- Use a coluna Data para descobrir qual folha usar
- Etapa 1: crie um VLOOKUP regular e use FORMULATEXT para ver como a referência deve ser
- Etapa 2: use a concatenação e a função TEXT para construir uma referência que se pareça com a referência de matriz da tabela na fórmula
- Etapa 3: crie sua VLOOKUP, mas para a matriz da tabela, use INDIRETO (resultados da etapa 2)
- Etapa 4: copie a fórmula da Etapa 2 (sem o sinal de igual) e cole na fórmula da etapa 3
Transcrição de vídeo
Aprenda Excel com o Podcast, Episódio 2173: Procure a planilha e a linha.
Ei, bem-vindo de volta ao netcast, sou Bill Jelen. Eu estive em Cincinnati na semana passada, e Rhonda em Cincinnati tinha essa grande pergunta. Rhonda precisa pesquisar este produto, mas a tabela Look Up é diferente, dependendo do mês em que se encontra. Veja, temos diferentes tabelas Look Up aqui para janeiro a abril e, presumivelmente, para os outros meses também. Tudo bem.
Vou usar INDIRETO para resolver isso, mas antes de fazer INDIRETO, sempre acho mais fácil apenas fazer um PROCV direto. Portanto, podemos ver como será a aparência do formulário. Portanto, estamos procurando A1 nesta tabela em janeiro e queremos a sétima coluna, vírgula FALSO, todos os VLOOKUPs terminam em FALSO. (= PROCV (A2, 'janeiro de 2018'! A1: G13,7, FALSO)). Tudo bem.
E, bem, essa é a resposta certa. O que realmente me interessa é obter o texto da fórmula disso. Isso me mostra como a fórmula vai se parecer. E todo o truque aqui é, estou tentando construir uma coluna Helper que será exatamente como esta Referência, certo? Portanto, esta parte - apenas aquela parte aqui. Tudo bem. Portanto, esta coluna do Helper deve ser semelhante a essa coisa. E a primeira coisa que quero fazer, é usar a função TEXT da data - a função TEXT da data - para obter mmm, espaço, aaaa - então, "mmm aaaa" assim- - que deve retornar, para cada uma das células, o mês que estamos procurando. Agora, eu preciso embrulhar isso em apóstrofos. Se não houvesse um nome de espaço ali, eu não precisaria dos apóstrofos, mas preciso. Então, vamos concatenar antecipadamente,o apóstrofo, então essa é a citação-- apóstrofo, aspas-- e comercial, e aqui, outra citação, apóstrofo e ponto de exclamação, A1: G13, aspas de fechamento, e comercial ali.
Tudo bem. Portanto, agora, o que fizemos com sucesso aqui na coluna Helper, é que construímos algo que se parece exatamente com a matriz da tabela em VLOOKUP. Tudo bem. Então nossa resposta, então, será = VLOOKUP desta célula, A2, vírgula, e então quando chegarmos ao array da tabela, vamos usar o INDIRETO. INDIRETO é uma função legal que diz: "Ei, aqui está uma célula que se parece com uma referência de célula e quero que você vá para F2, pegue o que parece uma referência de célula e use o que quer que esteja nessa referência de célula como a resposta, "vírgula, 7, vírgula, FALSO", assim. (= VLOOKUP (A2, 'janeiro de 2018'! A1: G13,7, FALSO)) Tudo bem, agora, em tempo real, estamos escolhendo um tabela Look Up diferente e retornando os valores dependendo se é abril ou o quê.
Tudo bem. Então, vamos pegar esse 24/04, vou mudar para 17/02/2018, assim, e devemos ver a mudança de 403 para 203 - perfeito. Está funcionando. Tudo bem. Agora, não precisamos dessas duas colunas aqui, é claro, e realmente, se você pensar bem, não precisamos dessa coluna inteira. Poderíamos pegar tudo isso, exceto pelo sinal de igual, Ctrl + C para copiá-lo, e então onde temos D2, basta colar, assim. Perfeito. Clique duas vezes para derrubar e se livrar disso. Esta é a nossa resposta. Tudo bem, usaremos nosso texto de fórmula aqui, apenas para dar uma olhada na resposta final.
Eu tenho que dizer a você, se eu tivesse que construir essa fórmula do zero, eu não o faria. Eu não seria capaz de fazer isso. Eu iria estragar tudo, com certeza. É por isso que eu sempre construo em etapas - eu descubro como a fórmula vai se parecer e então concateno a coluna Helper que será usada dentro do INDIRETO e então, finalmente, talvez aqui no final, coloque tudo de volta junto.
Ei, muitas dicas como esta dica no livro, Power Excel com. Esta é a edição de 2017 com 617 mistério do Excel resolvido. Clique no "I" no canto superior direito para obter mais informações.
Tudo bem, encerrando este episódio: Rhonda de Cincinnati - como procurar a linha e a planilha. Eu uso a coluna Data para descobrir qual folha usar; então eu construo um PROCV normal e uso o texto da fórmula para ver como a referência deve se parecer; e então construir algo que se pareça com essa referência usando a função de texto para converter a Data em Mês e Ano; use a Concactenação para construir algo que se pareça com a referência; e então quando você constrói sua PROCV para o segundo argumento, a matriz da tabela, use INDIRETO; e, em seguida, aponte para os resultados da etapa 2; e, em seguida, a quarta etapa opcional, copie a fórmula da etapa 2, sem o sinal de igual, e cole-a na fórmula da etapa 3, de modo que você termine com uma única fórmula.
Bem, quero agradecer a Rhonda por ter comparecido ao meu seminário em Cincinnati e quero agradecer a você por vir. Vejo você na próxima vez para outro netcast de.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2173.xlsm
