Masterclass básico de Excel XLOOKUP para treinamento avançado

1. Introdução ao masterclass de XLOOKUP: Você ainda está usando o Lou apenas para buscar os valores simples? Ou você conhece a função, mas se sente limitado quando o problema se torna complexo? E se eu pudesse usar a pesquisa para cenários avançados do mundo real, como pesquisa condicional, extração dinâmica de dados, correspondência inteligente e solução de problemas que normalmente exigem várias fórmulas Oi. Meu nome é Mave Bardach e bem-vindo a este curso de domínio do Advanced Excel Loup Este curso não trata de pesquisas básicas. Ele foi projetado para profissionais que já conhecem a pesquisa, mas desejam desbloquear seu verdadeiro poder. Portanto, neste curso, você aprenderá a usar o X Lou em cenários avançados em que as fórmulas devem lidar com a complexidade do mundo real Então, você verá como usar a pesquisa com várias condições para retornar o resultado com base em mais de uma linha. Você pode criar um sistema de pesquisa dinâmico e flexível que se ajusta automaticamente quando os dados crescem ou mudam. Aplique uma correspondência aproximada ou inteligente para placas, faixas e categorize dados Combine a pesquisa X com funções como I let filter ou expressões lógicas para resolver problemas comerciais e financeiros complexos. Crie fórmulas claras, legíveis e com desempenho otimizado que funcionem em painéis, relatórios e modelos reais Cada conceito deste curso é explicado com exemplos práticos usando conjuntos de dados realistas, não desenhos de livros didáticos Você não aprenderá apenas fórmulas. Você aprenderá a pensar logicamente com o X ocup. Portanto, ao final deste curso, você será capaz de projetar soluções de Excel robustas, escaláveis e profissionais usando o Lookup como uma função principal Portanto, se você quiser ir além dos usos básicos e dominar a lógica avançada do Excel Loup com confiança, este curso é para você Te vejo dentro do curso. Obrigada 2. Xlookup x Vlookup, por que todos os usuários do Excel aprendem a usar o Xlookup: Olá, pessoal. Bem-vindo a este curso avançado de Excel no X Lookup. Antes de entrarmos na fórmula avançada e na automação do mundo real, é muito importante entender por que o X Lookup foi criado e por que a função Lou mais antiga falha em projetos reais Portanto, nesta lição, entenderemos claramente por que Lookup e HLookup são uísque no trabalho profissional Quais são as maiores limitações do VLookup, enquanto a Microsoft introduziu o X Lookup e nas quais versões do Excel X Loup são aplicáveis Primeiro, discutimos por que as funções de Lou são importantes. No Excel, as funções de pesquisa são usadas quando queremos corrigir dados de outra tabela. Por exemplo, encontre o preço do produto usando o nome do produto, corrija o salário do funcionário usando o ID do funcionário, obtenha as taxas de horário de verão usando os códigos HSN coloque os detalhes do cliente em uma fatura automaticamente Por muitos anos, as funções mais populares do Lu foram o VLA. Mas popularidade nem sempre significa melhor. Vamos entender isso com um exemplo da vida real. Imagine que você tenha dados de vendas, código do produto, nome do produto e preço. Agora sua tarefa é simples. Você quer o preço usando o código do produto nesta tabela, VLookup pode fazer isso, mas os problemas começam quando os dados se tornam dinâmicos Vamos começar e aprender como aplicamos o VLookup. Então, começa do seno igual. Lou, forneça o valor da pesquisa, coma forneça a tabela completa da qual queremos extrair o preço, coma forneça o número do índice da coluna Nesse caso, são três. Pesquise sempre encontre ou procure os valores no lado direito. Então, aqui eu tenho o código do produto na coluna A e o preço na coluna C. Então, isso é um, dois e três. Vou digitar três. Em seguida, a última opção se quisermos uma correspondência aproximada ou exata Então, eu preciso de uma combinação exata. Vou fechar o suporte, vou pressionar Control Enter Por engano, coloquei dois, vou mudar para três, depois Control mais Inter Agora vou clicar duas vezes aqui. Ele será arrastado automaticamente para a última linha de dados. Então eu recebi meu preço, mas os problemas começam aqui sempre que alguém faz alterações. Agora imagine que alguém insira uma nova coluna entre o nome do produto e o preço. Então, vou inserir uma coluna, nome do produto e o preço, e você verá que temos um problema aqui porque escrevemos as três e nosso resultado completo foi retirado daqui. Agora precisamos corrigir esse erro manualmente. Precisamos digitar quatro para obter o resultado exato porque esta é minha primeira coluna, essa é a segunda, essa é a terceira e agora o preço passa para a quarta coluna. O resultado está errado nos arquivos profissionais do Excel, as colunas são inseridas, as colunas são excluídas, estrutura de dados continua mudando. A pesquisa não é flexível. Sempre que a estrutura mudar, você deve corrigir a fórmula manualmente. Isso é perda de tempo e aumenta o risco. Além disso, ele tem baixa legibilidade. Quando outra pessoa abre seu arquivo e vê sua fórmula com números de coluna, ela não sabe qual coluna é duas e qual coluna é três. Isso dificulta a auditoria e a manutenção dos arquivos. Também tem muitos problemas, mas sempre que movo essa coluna para aqui, recebo um erro. Se eu mover essa coluna para aqui, também obtenho um erro, mas o Excel está mostrando um erro, não estamos obtendo os resultados adequados. Então, vou desfazer esse. E agora, vou deixar você saber por que Microsoft introduziu o Excel Loup. Então, a Microsoft observou como as pessoas realmente usam o Excel em empresas reais. Eles notam que o usuário precisa de uma pesquisa à esquerda, usuário precisa de fórmulas Cifer, o usuário quer fórmulas legíveis, o usuário quer menos usuário quer Então, a Microsoft criou o X lookup. O X Loup foi projetado para substituir o VLookup. H Loup também substitui muitas fórmulas de correspondência de índices em uma única fórmula com vários recursos avançados Então, vamos entender conceitualmente com o X Loup, você pode olhar para a esquerda, para a direita, para cima Você não usa números de coluna. Você seleciona colunas de pesquisa e retorna colunas diretamente. Agora vamos usar o X Lookup para descobrir o preço. Então, começará com o sinal de igual. Vamos digitar X Loup. E agora você pode ver que tem muitos argumentos. Então, são obrigatórios e alguns são opcionais. Portanto, os três primeiros são obrigatórios e os três últimos são opcionais, mas são muito importantes nas técnicas avançadas que exploraremos os próximos vídeos Portanto, neste caso, estou apenas dando um exemplo simples de como usar a pesquisa X. Então, primeiro, temos que atribuir o valor de pesquisa. Nesse caso, é o código do produto e em seguida, temos a matriz de pesquisa. Portanto, esta é minha matriz de pesquisa. Portanto, no VLCal, primeiro temos que atribuir a tabela de pesquisa. Mas aqui estou apenas atribuindo onde minha matriz de pesquisa usando esse código, quero extrair os dados dessa tabela Então, neste caso, é o código do produto, e então vou digitar coma. Então, a matriz escrita significa que temos que fornecer a coluna para a qual queremos extrair ou buscar Nesse caso, é preço. Está bem? Então coma, se a função não conseguir encontrar nenhuma fórmula, que ação devemos fazer. Portanto, não vamos usar isso agora, o modo de partida, a correspondência exata e, em seguida, o menor, o maior ou o curinga Portanto, neste caso, vou usar o match case e não quero usar o search case. Vou fechar este, mas antes disso, quero consertar algo que quero que seja constante usando o F quatro. Além disso, tenho que corrigir isso, tornando-o constante, e então pressionarei Enter e arrastarei até a última linha. Eu recebi a mesma resposta, mas desta vez, vamos ver a mágica. Vou inserir algo aqui. Ainda assim, estou recebendo minha resposta corretamente. Ok, não tem problema. Vou deletar esse. Está bem? Vou cortar e inserir aqui. Ainda assim, estou recebendo minha resposta, mas não aqui. Mas não aqui nesta tabela em que usamos o Vlookup. Vou recortar e colar esses dados aqui. Então, agora minha matriz de pesquisa está no lado direito e os valores de pesquisa estão no lado esquerdo, e ainda estou recebendo tudo. Portanto, é completamente dinâmico. Portanto, é muito poderoso para usuários avançados. Então, aprenderemos exemplos práticos detalhados dos vídeos recebidos de X Lou, então fique ligado no próximo vídeo. Obrigada. 3. Sintaxe do XLOOKUP: Nesta lição, entenderemos a sintaxe completa da função de pesquisa Analisaremos cada argumento passo a passo para que até mesmo os maiores possam entender de forma fácil ou clara como o X loop funciona em projetos reais A função Lou tem seis argumentos. Desses três primeiros argumentos são obrigatórios , escritos em luva preta e os três restantes são opcionais, mas muito poderosos O primeiro argumento são os valores u. O valor de pesquisa significa quais dados você deseja que o Excel pesquise. Por exemplo, se você encontrar o salário da ID do funcionário , a ID do funcionário se tornará o valor da pesquisa. O valor da pesquisa pode ser um número, texto ou referência de célula. Em arquivos do Word reais ou em exemplos práticos, geralmente usamos referências de células em vez de digitar o valor diretamente A próxima é a matriz de pesquisa. A matriz de pesquisa é o segundo argumento. Matriz de aparência significa a coluna ou intervalos em que o Excel pesquisará o valor da pesquisa. Por exemplo, valor. Se a ID do funcionário for armazenada na coluna A , a coluna A se tornará a matriz de pesquisa. Ao contrário de V X, Lou não exige que a coluna Lou esteja no lado esquerdo A pesquisa X pode pesquisar em qualquer direção. Essa é a maior vantagem da pesquisa X. O terceiro argumento é a matriz de retorno. A matriz de retorno significa qual coluna do Excel deve retornar o resultado, por exemplo, se você quiser retornar o salário , a coluna do salário se tornará a matriz de retorno. O X Lookup não usa números de coluna. Mesmo se você inserir ou excluir, a fórmula nunca será quebrada. O quarto argumento é, se não for encontrado. Esse argumento decide o que o Excel deve exibir se o valor de pesquisa não existir. Em vez de mostrar o hash e o erro, você pode exibir um texto significativo como ID não encontrado ou inválido Usando esse argumento, torne seu relatório organizado, limpo e profissional O quinto argumento é o modo match. O modo de correspondência controla como o Excel combina os valores ascendentes. Por padrão, o uso da pesquisa corresponde exatamente. Você também pode usar o uso aproximado para lajes, faixas e sistemas de nivelamento O modo de correspondência também permite a correspondência de caracteres curinga, o que é útil ao pesquisar nomes parciais ou códigos Modo de pesquisa. O sexto argumento é o modo de pesquisa. O humor da pesquisa controla a direção da pesquisa. Por padrão, o Excel pesquisa de cima para baixo. Você também pode pesquisar de baixo para cima para encontrar o registro mais recente. Isso é muito útil ao trabalhar com histórico de transações, revisões salariais ou faturas mais recentes Então, agora você entendeu todos os seis argumentos da função de pesquisa X. Na próxima lição, aplicaremos X Lou no exemplo de salário de funcionários da vida real Então, continue praticando. Você dominará o X Lookup muito rapidamente. 4. Exemplo do XLOOKUP: Nesta palestra, daremos um exemplo prático que você pode aplicar em sua vida real. Vamos dar o exemplo de uma identificação de funcionário. Portanto, temos uma tabela com a identificação do funcionário, seu nome, seu departamento e seu salário. Temos outro lençol. Ele pode estar disponível nesta planilha ou em outra planilha. Então, eu estou dando o exemplo dentro desse conjunto. Posteriormente, também abordaremos muitos exemplos em que pesquisaremos em outra planilha ou em outra pasta de trabalho Então, vamos começar. Como você sabe, sempre começamos com sinal de igual quando você usa qualquer função ou fórmula. Então, digitamos X Lou Press tab. O primeiro argumento é o valor de pesquisa, para que possamos fornecer rapidamente o valor de pesquisa ID do funcionário como referência de célula. Agora coma. O segundo argumento é a matriz de pesquisa. Portanto, a matriz de pesquisa é a coluna ID do funcionário. Então eu posso dar assim e pressionar F quatro no teclado. Então coma e eu quero recuperar o salário. Essa é a coluna salarial. Vou selecionar a mesma faixa de coluna salgada e pressionar F quatro e depois coma Eu tenho que definir se não for encontrado o valor. Às vezes, quando pesquisamos um valor de célula específico, é possível que ele não esteja disponível na tabela de pesquisa. Portanto, é uma boa prática inserir esse valor sempre. Deixe-me dar um exemplo aqui. Tenho identificações de funcionários escritas na coluna H e vou descobrir o salário. Eu tenho um total de cinco identificações de funcionários 101-105. Mas, intencionalmente, eu escrevi um agora. ID do funcionário, que não está disponível aqui. Então, vamos ver o impacto que estamos obtendo. Não vou inserir os três argumentos opcionais restantes . Primeiro, veremos o que obteremos. Então, fecharei o suporte, pressionarei o controle. Entre, vou clicar duas vezes aqui, e aqui eu posso ver NA. Digamos que eu esteja calculando seus salários e, se eu receber o NA, minha planilha salarial completa se tornará inútil porque eu receberei o erro, e agora não posso usar esse erro para calcular o salário e não receberei o Vou receber dados inúteis. Então, para nos livrarmos desse tipo de problema, temos que atribuir valores, se não forem encontrados. Sempre, seja opcional, mas você precisa atribuir. Então, neste caso, quando estou recuperando o número, ele pode ser usado para um cálculo Portanto, é recomendável usar zero ou qualquer outro valor, o que não afetará seus cálculos. Se for um texto, você poderá aplicar alguns argumentos de texto entre aspas duplas, como não aplicável A ID do funcionário não está disponível desta forma. Então, estou inserindo zero agora. O próximo valor opcional é a correspondência exata. Então, agora eu estou usando a combinação exata. Explicarei todos os quatro exemplos detalhadamente usando os exemplos práticos do vídeo recebido Estou considerando que a correspondência exata significa que se um, zero, um corresponder exatamente ao valor de pesquisa na tabela de pesquisa, somente ele precisará recuperar o valor Caso contrário, ele mostrará um erro. Em seguida, pressione Coma. O próximo é para pesquisar. Então, agora ele está pesquisando de cima para baixo, mas também daremos exemplos práticos para descobrir qual é o uso desses argumentos opcionais. Então, estou fechando agora sem inserir esses dados e, desta vez, obtive zero Também posso arrastar para o lado superior para copiar e colar a mesma função ou os valores de baixo para cima. Portanto, este é um exemplo simples que podemos usar. Então, nos próximos vídeos, exploraremos mais alguns exemplos em detalhes com muitos projetos práticos da vida real. Obrigado por assistir. Fique ligado no próximo vídeo 5. Automatize a reconciliação de contas bancárias usando o XLOOKUP: Olá, pessoal. Bem-vindo ao curso avançado de Excel. Neste vídeo, daremos o exemplo como podemos usar o X Lookup para reconciliação bancária Aqui eu tenho o extrato bancário no Excel, onde tenho o valor da data da transação e o número de referência, depois a retirada e o valor do depósito que está positivo. Agora tenho títulos bancários que exportamos de qualquer ERP ou software de Agora, nossa tarefa é combinar o valor com base no número de referência. Para isso, em primeiro lugar, temos que transformar os números em positivos e negativos usando o crédito debitante. Para isso, precisamos de uma coluna auxiliar. Para que você possa dar a maior parte do tempo na prática, eu dou o nome da coluna como auxiliar Então, vou usar a função I para converter o valor do crédito em negativos Isso ajudará a comparar o valor com as contas bancárias. Então, minha fórmula começa com igual se e minha lógica seria se essa referência de célula fosse igual a crédito, então o valor seria multiplicado por Caso contrário, seria positivo, o mesmo. Fechando o Bagget Control plus Enter, clicarei duas vezes e obtive minha resposta Além disso, como você pode ver, se eu descer pressionando Control para pressionar um novato, ele está se movendo para a última linha, mas não consigo ver o Para isso, vou congelar a primeira fila, vou subir, vou descer uma linha. Vou ver as canetas livres e a opção canetas livres. Agora, se você ver sempre que eu desço, eu posso ver meu cabeçalho, então eu sei qual coluna é essa. Da mesma forma, entrarei no extrato bancário e selecionarei a segunda linha. Depois do cabeçalho, vou ver canetas livres e selecionarei canetas livres mais uma vez, e tenho meu cabeçalho na Aqui, também, criarei uma coluna auxiliar. O valor da retirada está em números positivos, então eu também quero converter em negativos Então, em vez de fazer manualmente, aqui também usarei se sim, minha lógica seria se a célula de depósito estivesse em branco. Vou usar outra função embutida chamada is blank. Vou dar referência a essa célula em particular. Então, se essa célula estiver em branco, ela será movida para o valor visual multiplicado por menos um, e se o valor for falso, eu quero esse valor, fechando o colchete Control plus enter, e clicarei duas e se o valor for falso, eu quero esse valor, eu quero esse valor, fechando o colchete Control plus enter, e clicarei Eu tenho meus valores auxiliares em todas as colunas, agora vou usar o laser do banco Primeiro, verificarei os dados do laser do banco até o extrato bancário. Preciso recuperar o valor na coluna auxiliar. Então vou comparar com esse valor. Aqui vou digitar X look up. Também temos que fazer a mesma tarefa no extrato bancário. Preciso buscar os dados da coluna F no extrato bancário e depois comparar Estou aqui e sua tarefa seria buscar os dados do laser do banco X olhe para cima e eu darei referência a essa célula Coma. Matriz baixa. Procure a matriz, que está no extrato bancário, vou até aqui. Em vez de fornecer um intervalo específico porque esses são os dados contínuos. Se eu quiser fazer a reconciliação bancária de valores futuros , posso copiar a página Nesse caso, posso usar a coluna completa como uma matriz de pesquisa. Então coma e eu gostaria de buscar a coluna G e, desta vez, vou usar se não for encontrada Quero dizer ao sistema que, se o valor não for encontrado aqui. Eu gostaria de receber esse valor fora do extrato bancário. Eu quero esse texto ou pode ser qualquer coisa e o texto estaria sempre entre aspas duplas e eu preciso da correspondência exata Eu não quero um modelo de pesquisa agora. Vou fechar o colchete, Control plus Enter e vou clicar duas vezes aqui Vou ajustar a largura da coluna clicando duas vezes aqui. Em primeiro lugar, percebi que os dados não coincidem. Para isso, precisamos identificar, então vou até a guia Dados e depois filtrarei, clicarei aqui, depois desmarcarei essa opção e poderei clicar aqui Esses são os valores e os dados que não coincidem. Isso significa que o número de referência foi escrito incorretamente, temos que verificar e verificar e, em seguida, temos que corrigir no extrato bancário, então ele corresponderá. A segunda coisa que eu quero fazer é comparar o valor. Aqui, vou comparar ou qualquer coisa. Vou comparar, basta selecionar o valor auxiliar e é igual ao valor pesquisa X do extrato bancário e Control plus Enter e Control plus Enter e Mais uma vez, vou ver os dados. Filtrar. Novamente, preciso aplicar os filtros para acessar a guia de dados e clicar em filtro partir daqui, preciso desmarcar os dois valores e selecionar os primeiros valores Então, esses são os dados incompatíveis, e você pode retificar, pesquisar qual é a razão por trás das diferenças e, em seguida, corrigir em seus livros Então, novamente, você pode corrigir aqui. Dessa forma, você pode usar a função de pesquisa para bancária ou a reconciliação a laser, ou as duas declarações usando um valor de pesquisa Então, nos próximos vídeos, também explorarei mais alguns exemplos práticos da vida real Portanto, sua tarefa é fazer a mesma coisa com o extrato bancário. Você pode baixar esta pasta de trabalho a partir dessa descrição e, em seguida, aplicar a pesquisa do Excel aqui, comparar e identificar e filtrar os dados, que são incompatíveis Então, obrigado por assistir. Fique ligado no próximo vídeo 6. Argumento do modo de correspondência avançado, parte 1: Olá e bem-vindo mais uma vez. Agora vamos entender um dos argumentos mais poderosos da função de pesquisa X, que é chamada de modo de correspondência. O modo de correspondência controla como o Excel encontra os valores correspondentes dentro da matriz de pesquisa. Esta é a principal razão pela qual Loup é muito mais avançado e confiável do que V Lou função Roco tem seis argumentos e o modo match é o quinto Portanto, se você não mencionar o modo de correspondência durante a pesquisa no X, Excel usará automaticamente a correspondência exata. Mas nos modelos de negócios da vida real, devemos sempre entender como controlar esse argumento. Vamos começar com o modo de partida zero. Essa opção significa apenas a correspondência exata. O Excel procurará um valor que corresponda exatamente ao valor da pesquisa. Vamos começar a primeira partida, monte zero. Essa opção significa apenas a correspondência exata. O Excel procurará um valor que corresponda exatamente ao valor da pesquisa. Se o Excel encontrar o valor, ele retornará o resultado correspondente. Se o Excel não encontrar o valor, ele retornará um erro ou o texto mencionado no argumento if not. Use essa opção ao trabalhar com ID do funcionário, número da fatura, código do cliente, número da conta e ID do produto Significa que sempre que precisamos descobrir o número exato, como a codificação ou um número específico , precisamos usar apenas a correspondência exata Essa é a opção melhor, mais segura e mais usada Não é necessário usar curto-circuito para esse modo. Já discutimos, mas também estou dando um exemplo aqui. Eu tenho o código do produto e seus preços escritos aqui. Preciso extrair o preço usando o código do produto. Vou usar o lookup. Em seguida, vamos para o valor de pesquisa, aquele coma. Vou mencionar a matriz de pesquisa, corrija-a pressionando Aor Coma Preciso mencionar a matriz de retorno, corrigi-la com o controle For. Preciso mencionar aspas duplas se o valor não for encontrado A partir daqui, nossa opção de modo é desativada e precisamos inserir o modo exato. Eu já expliquei qual é a utilidade disso, então estou selecionando zero e, se você não mencionar, Excel considerará automaticamente apenas a correspondência exata, e o encurtamento não é necessário Portanto, não mencionaremos Control plus Enter e clique duas vezes para arrastar essa função para o lado negativo É assim que podemos usar a correspondência exata. Já explicamos, mas também aprendemos aqui. Agora vamos passar para a partida mais menos um. Essa opção primeiro tenta descobrir uma correspondência exata se o valor exato não for encontrado. Excel retorna o maior valor que é menor do que o valor de pesquisa. A matriz de pesquisa deve ser classificada em ordem crescente. Essa opção é usada para laboratórios fiscais, placas salariais, placas de comissões, placas taxas de juros ou estrutura de descontos Esse modo é extremamente importante na automação financeira e da folha de pagamento e na distribuição de comissões de vendas, etc Vamos dar o exemplo. Eu tenho salários e o texto é aplicável com base nessa estrutura Portanto, se o salário for maior ou igual a dois AC 50.000, 2% serão aplicáveis Se for superior a 5% e AC 50.000, 7% é aplicável. Preciso descobrir o texto aqui, então usaremos nosso cálculo. Então, o salário que eu escrevi aqui. Então, se mudarmos adequadamente, alíquota do imposto seria obtida desse slam Então, começarei com local e darei o valor de pesquisa à referência da célula salarial. A matriz de pesquisa seria o salário. Podemos consertá-lo se quisermos. Não é obrigatório neste caso. Agora, a matriz escrita seria a coluna da alíquota do imposto. Eu vou consertar isso também. Se não for encontrado, gostaria de mencionar zero. Ou não aplicável. Mas se eu colocar algum texto aqui , obterei o resultado errado no cálculo. Portanto, é sempre melhor inserir zero ou qualquer outro número. Nesse caso, vou selecionar menos um, a correspondência exata ou o próximo item menor E então o modo de pesquisa seria o último e garantiria que seus dados, valores de pesquisa ou matrizes de pesquisa estivessem em ordem crescente Agora vou fechar este, pressionarei Control Enter, e você verá que está seis atrasos e 5% que estou recebendo Então, em vez disso, está considerando o número menor. Ok. Então, se eu mudar para, digamos, um atraso, 0% virá porque não é aplicável Agora vou passar para três defasagens , então ele deve considerar apenas 2% Então, ele está procurando um número menor. Então, neste caso, é 2% porque está entre duas rúpias lac 55 lac É por isso que 2% estão chegando. Então, ele está procurando o número menor. 7. Argumento do modo de correspondência avançado, parte 2: Agora vamos entender o modo de partida um. Esta opção também procura uma correspondência exata primeiro. Se a correspondência exata não for encontrada. Excel retorna o menor valor maior que o valor da pesquisa. Novamente, a matriz de pesquisa deve ser classificada em ordem crescente Essa opção é útil quando as cobranças são baseadas em limites mínimos, aplicam-se correios, placas de peso, existem limites de penalidade e os preços sobem para o próximo Isso significa que onde quer que tenhamos alguma placa. Essa opção pode ser aplicada. Agora vamos entender com isso claramente. Suponha que uma empresa de courier tenha definido placas de peso para preços Primeira placa, temos cobranças. Na laje dois, que é de até três kg, temos cargas diferentes e, para 5 kg, temos cargas diferentes Então, precisamos calcular as cargas de acordo com o peso que alguém insere aqui. Mas, desta vez, a função procurará o valor maior. Então, vamos começar. Então, digitaremos nossa função X lookup, pressionaremos type, valor de Lou viria aqui, então a matriz seria essa, e a matriz de retorno seriam as cargas O valor, se não encontrado, seria zero. Nesse caso, podemos digitar qualquer texto. Mas aqui, como estou procurando um valor numérico para fazer qualquer cálculo , zero seria uma opção melhor Desta vez, vou selecionar a combinação exata ou o próximo item maior. E então a próxima opção ascendente ou de pesquisa seria uma, feche o colchete e pressione Enter Agora vamos ver como isso está sendo calculado. Eu escrevi 1.5, então aqui está uma porque neste laboratório 1.5 está entre esses dois valores e está procurando um valor maior. Está chegando ao máximo: até um kg, as cobranças seriam de $10 De um, dois, três, as cobranças seriam 15. Se for mais de três a cinco kg, as cargas seriam 19. Ele procurará o valor maior. Se eu mudar isso para 4.5, você pode ver que 19 está chegando. Então esse valor está chegando. E, consequentemente, podemos fazer a multiplicação. O total de cobranças virá aqui. Então, no próximo vídeo, procuraremos as combinações curinga Então, direto para o próximo vídeo. 8. Argumento do modo de correspondência avançado, parte 3: Então, neste vídeo, discutiremos a quarta opção do modo de partida, que é a partida Wildcard Portanto, essa opção é usada quando o texto exato não está disponível, mas a palavra-chave necessária está presente em um texto mais longo. Em dados comerciais reais, essa situação acontece com muita frequência. Portanto, a correspondência curinga permite que o Lookup encontre uma mensagem de texto parcial Excel verifica se o valor de pesquisa existe em qualquer lugar dentro do texto, não apenas como um valor exato completo. Isso é feito usando os curingas. Assim, podemos usar todos os caracteres curinga aplicáveis no Excel, então estamos tomando o exemplo da Astec Portanto, o Astik representa qualquer número de caracteres antes ou depois de um curinga Também podemos usar o ponto de interrogação. Isso representa apenas um caractere. Na maioria dos casos contábeis e financeiros, o ASTic Wiltcard Então, deixe-me dar um exemplo. Eu tenho um nome, o nome completo dos nossos funcionários está escrito aqui. E o salário deles está escrito aqui. Agora preciso extrair o salário deles usando apenas o primeiro nome. Este é apenas um exemplo, mas se tivermos dois nomes em que o primeiro nome corresponda, por exemplo, eu tenho Rahul Sharma e também estou tendo Portanto, o que vier primeiro será pesquisado por nossa função Porque estou pesquisando o valor apenas do primeiro nome Rahul, mas está se Portanto, se for de cima para baixo, qualquer que seja o valor que vier primeiro, será pesquisado pela pesquisa X. Mas como vamos usar esse, vou explicar agora. Usaremos a pesquisa X com a opção de pesquisa curinga. Então, iguale, pressione stp e aqui, temos que dar algumas entradas extras Então, desta vez, vou usar Etic antes ou depois do nosso valor de pesquisa. Mas como você sabe que não podemos inserir diretamente nenhum texto com as referências próprias, temos que usar o sinal de Posent e, se for um texto, temos que usar aspas duplas Então, digitaremos aspas duplas e, em seguida, Asterk, teremos que fechar essas aspas duplas para concat net ou unir com outro valor de texto, precisamos usar porcentagem Em seguida, darei a referência dessa célula onde nosso primeiro nome foi escrito. Agora eu tenho que juntar o segundo sinal de asterisco depois do curinga Após a referência do texto, temos que digitar a porcentagem Asterc entre aspas duplas e, em seguida, fechar esta Agora coma. Então, agora vou dar a matriz Luka Então, vou dar a referência dessa célula. Mas como eu mesclei três células, o intervalo vai de B 38 a D 45, que precisamos mudar apenas para B, então eu tenho que remover D e B, você precisa ter certeza de que seu argumento deve estar correto E então temos que corrigir isso, torná-lo constante, pressionar F quatro. Então, finalmente, temos que colocar coma e depois a matriz de retorno, matriz de retorno é perfeita. Pressione F quatro para torná-lo constante também. C e insira, se o valor não for encontrado, seria NA. aspas duplas, depois coma, temos que selecionar a correspondência do Wildcard Corrector, pressionar step, fechar a quebra, Control plus Enter e clicar duas vezes aqui, e obtemos nossa resposta com base no Então, agora, vou explicar mais uma vez como essa função está funcionando. Eu tenho nome completo, incluindo nome e sobrenome escritos aqui, e isso é salário por hora. Preciso buscar usando apenas o primeiro nome. Então, o que estou dizendo ao sistema é que eu estou usando o Wildcard significa que eles precisam pesquisar um texto parcial no texto completo escrito aqui usando o Wildcard Então, eu estou usando Rahul, que está disponível aqui Portanto, basta pesquisar Rahul nesse texto completo e, se estiver disponível, buscará o valor na coluna de valor Mas, como já expliquei, se eu tiver vários Rahul, deixe-me mudar Rahul Sharma mais uma vez, para que você possa ver que 55 está chegando porque está pesquisando Nesses casos, sempre usamos o código do funcionário em vez do nome, mas isso é apenas um exemplo, então você deve cuidar disso. Se tiver valores repetidos na matriz de pesquisa, você deve tomar cuidado e tomar as medidas adequadas e alterar sua fórmula de acordo com o requisito. Obrigado por assistir. Passaremos para as funções mais avançadas vídeos de entrada e exemplos do mundo real Fique ligado no próximo vídeo. Obrigada. 9. Modo de correspondência Curinga — exemplo da vida real: Neste vídeo, abordaremos mais um exemplo relacionado ao curinga Então, basicamente, eu já expliquei qual é o uso disso. Então, aqui está um exemplo prático. Digamos que temos dados de representação de vendas e precisamos atribuir a meta para eles de acordo com suas cidades. Tenho o nome dos vendedores e suas cidades, seus valores de vendas alcançados, mas aqui preciso definir suas metas, que estão disponíveis em outra tabela Pode estar em outra planilha ou na pasta de trabalho, aqui temos cidades, mas também está incluído o nome do país Podemos extrair facilmente a cidade, mas precisamos usar a opção curinga usando a função de pesquisa X. Portanto, sem extração, também podemos obter os valores-alvo aqui. Para isso, usaremos a opção curinga. Então, vamos começar nosso exemplo X Lookup. Então, aqui vamos selecionar nossa cidade no Xu, mas adicionarei o curinga com o signo de asterisco Deve estar entre aspas duplas. Vou fechar as aspas duplas, então a matriz Coma Lou seria o C. Precisamos torná-la constante pressionando F, Coma, a matriz de retorno seria o Aqui, também, precisamos pressionar For para torná-lo constante. Então, valor não encontrado, também, quero inserir esse valor sem alvo. Também deve estar entre aspas duplas, pois são as entradas de texto Agora, Coma, desta vez, vou selecionar Wildcard Collector Match, e isso é tudo o que precisamos selecionar e, em seguida, fechar o colchete, pressionar Control plus Inter e clicar duas vezes Então, agora podemos ver aqui que Londres foi escrita e, para Londres, temos 710 como alvo Então veio automaticamente, e eu posso verificar o Berlin. Então é em Berlim, Alemanha, e a meta é 890 e 890 está chegando Esse é o exemplo prático e real do curinga na função de pesquisa Portanto, no próximo vídeo, abordaremos mais um exemplo relacionado à pesquisa. Obrigado por assistir. 10. Pesquise do último ao primeiro: Oi, pessoal. Neste vídeo, aprenderemos mais um exemplo e argumento opcional no Lou, chamado últimos 21 Também estamos abordando um exemplo prático aqui com o uso avançado da função de tabela. Então, agora eu tenho uma tabela na qual tenho algumas datas, cursos e o número de participantes Minha meta é selecionar o curso a partir daqui, e a data mais recente do treinamento e o número de participantes devem vir automaticamente Então, aqui, as datas estão em ordem crescente, significa que as datas mais antigas estão no topo e as datas mais recentes estão no lado negativo Esses são os cursos, e o primeiro treinamento para Excel B six foi realizado em 12, 25 de janeiro, o total de participantes foi 38, o próximo em 5 abril e o participante Preciso extrair a data e o número de participantes do último ao topo Então esse é o requisito. Além disso, tenho que converter essa tabela no tipo oficial de tabela. Para isso, selecione NSA, venha inserir e clique na tabela e verifique se minha tabela tem cabeçalho que deve estar habilitada. Clique nele. Ela será convertida no tipo oficial de tabela, e podemos dar ou atribuir o nome a essa tabela; por padrão, a tabela um, dois, três e assim por diante, será atribuída pelo eixo Então, digamos que os dados do curso, algo assim, sejam significativos para seu usuário. E depois de converter para a tabela oficial, podemos ver a guia de design da tabela, na qual temos funções e fórmulas relacionadas e mais opções Então, eu já expliquei isso em meu curso avançado. Então, se você quiser experimentar, pode verificar e agora eu tenho que alterar a formatação também, então vou clicar aqui e selecionar esta OK. Então, agora vamos ver qual é a diferença entre usar qualquer tabela com formatação de tabela ou sem formatação de tabela Então, desta vez, vou selecionar o curso aqui e agora vou começar meu exemplo de pesquisa X. Então, vou tentar escrever Lookup após o eqosge e depois Lou, pressionando tab, minha pesquisa estaria aqui E agora, em vez de selecionar essa linha específica, selecionarei e colocarei meu cursor na parte superior do cabeçalho. Portanto, você pode ver aqui que o nome da tabela e o nome da coluna aparecem em um formato diferente. Agora, nosso intervalo foi convertido no nome da tabela e no nome da coluna. Então, em vez de fornecer esse intervalo específico, podemos fornecer o nome da tabela e o nome da coluna, e é dinâmico. Não há necessidade de alterar o intervalo a cada vez. Portanto, sempre que adicionarmos mais dados aqui, Excel os atualizará automaticamente na própria fórmula. Agora eu tenho que selecionar a matriz de retorno. Então, a matriz de retorno seria, desculpe, eu tenho que mudar isso para curso em vez de data, porque a data eu tenho que extrair desta tabela. Então, desta vez, vou selecionar a data e depois Coma. E se não for encontrado, sim, quero atribuir o valor em formato de texto a que não há cursos. E desta vez, vou selecionar a correspondência exata porque o nome do curso deve corresponder exatamente aos valores atribuídos nesta tabela na coluna do curso E depois coma e, desta vez, já selecionamos muitas vezes o penúltimo e, desta vez, vou selecionar pesquisar por último para Rápido. Isso é tudo. Temos que fechar o suporte e pressionar Control inter, obtivemos nossa resposta. Noções básicas sobre o Excel O último curso frequentado ou realizado pelo treinador é no dia 5 de abril Deixe-me mudar isso para outro valor. É 21 de julho de 2025. Mais um valor do curso está chegando aqui, mas o sistema está buscando os dados de baixo para cima Da mesma forma, podemos obter o número de participantes usando o Seria o mesmo, então seria o mesmo neste caso, e a matriz escrita seria essa, depois Coma e nocurs e, em seguida, correspondência exata, Coma, e as duas últimas primeiro, fechando o colchete Center Portanto, as fórmulas do Excel, 21 de julho e 36 são o número de participantes É assim que você pode usar diferentes opções de pesquisa na pesquisa X. Portanto, no próximo vídeo, aprenderemos opções mais avançadas relacionadas à pesquisa. Obrigado por assistir. Fique ligado nos vídeos do Moe 11. Pesquisa de várias colunas: Olá, e bem-vindo mais uma vez na master class de Pesquisa Avançada. Neste vídeo, vou explicar mais um truque relacionado à pesquisa. Então, aqui eu tenho alguns dados em duas tabelas. Um está relacionado à associação, localização e preços. E aqui tenho, desculpe, tenho o nome da pessoa e o tipo de associação que ele adquiriu da agência e do local. Preciso extrair um preço aqui, mas desta vez, o preço é diferente. Temos que verificar a associação e a localização. Só então obteremos o preço. Então, basicamente, aqui, duas colunas estão envolvidas para obter o preço dessa tabela. Então, primeiro de tudo, temos que converter essas duas tabelas no formato de tabela oficial, que já discutimos no último vídeo. Então, da mesma forma, selecionaremos uma célula, clicaremos em Inserir, selecionaremos a tabela e, em seguida, clicaremos em Ok, não vou alterar o formato, economizaremos o tempo, selecionaremos a inserção após selecionar a célula da segunda tabela, clicaremos na tabela e depois em OK. E desta vez, vou mudar o nome da tabela para, digamos, os dados de preço ou algo significativo de acordo com o requisito. E aqui, estrutura de preços ou algo assim. OK. Então, agora eu tenho que selecionar desta vez duas ou a matriz de pesquisa múltipla e o valor de pesquisa para encontrar o valor com base em duas colunas. Então, vamos começar. Então, sinal de igualdade. Olhe para cima. Portanto, valor da pesquisa, se estivermos procurando por uma coluna , seria a associação. Mas desta vez, vamos unir a associação e o local para extrair o preço com base na associação e na localização, porque aqui as assinaturas são quase iguais, o que significa que temos quatro tipos de associação, mas a localização é diferente e o preço depende da localização e da Então, vou juntar a associação com localização no valor de pesquisa usando o am posent. Então, selecionando o local também, e desta vez, você pode ver, eu tenho uma associação e a localização em vez do nome da célula. Você verá a mágica pela qual eu me converti para a tabela oficial mais tarde , quando concluirmos esta fórmula. Agora, avalie o Coma e, desta vez, tenho que selecionar a matriz Lou matriz Lou também deve ser unida, vou selecionar assim ou posso clicar na parte superior e o cursor será alterado para a seta para baixo E vou digitar posent. Vou selecionar da mesma forma esta área também. E agora Coma, vou selecionar o preço. Ele virá automaticamente. E agora você pode ver que não obtivemos nenhum alcance. Em vez disso, obtivemos o nome da tabela e uma coluna relacionada. E então Kuma não foi encontrado, então eu preciso de zero e exata, e não queremos pesquisar mais desta vez Vou fechar isso e, assim que pressionar Enter, obteremos a resposta em todas as minhas células. Então, aqui eu não copiei e colei a fórmula na última célula, ela veio automaticamente. Se eu adicionar mais dados aqui, não será necessário atualizar minha função ou a fórmula. Ele será atualizado automaticamente. Então, da próxima vez, você terá que usar todas as suas tabelas no formato de tabela para esse uso avançado de qualquer função. Então essa é a mágica. Então, agora vamos verificar nosso resultado. Então, aqui temos uma pessoa, um membro, com a associação Gold e a localização é Londres, e o resultado é 10y. Então, estou chegando à associação Gold do lado de Londres, então são 10, e está tudo bem. Então é assim que você pode usar as várias colunas no Excel. Obrigado por assistir Fique ligado no próximo vídeo. 12. Olhada de duas vias: Olá e bem-vindo. Mais uma vez, no vídeo de hoje, aprenderemos mais um truque avançado de pesquisa. Tenho duas mesas nas quais tenho o tipo de acomodação e a área e os preços específicos. Preciso extrair o preço com base na acomodação e na localização. No meu exemplo anterior, eu tenho uma combinação de colunas. Mas aqui, o problema é que eu tenho um dado na coluna e o segundo, os dados que precisamos verificar na linha. Então, isso é chamado de Xocup bidirecional. Então, como podemos conseguir isso, deixe-me começar. Usando a pesquisa. Então, primeiro de tudo, eu tenho que converter essa tabela na tabela oficial. Então, vou selecionar um, inserir o Guru, clicar em Tabela e, desta vez, não vou mudar o nome que você pode fazer para sua prática Além disso, quero converter isso a tabela oficial rapidamente para economizar nosso tempo. Agora vou usar a pesquisa para fazer a pesquisa X bidirecional. Então, desta vez, vou selecionar a pesquisa X, reiniciar e tenho que selecionar a pesquisa X. Quero dizer, o valor da pesquisa seria acomodação. Em vez da faixa Salar, valor da coluna de acomodação está chegando na matriz de pesquisa Coma Então, eu tenho que selecionar essa matriz de pesquisa e, em seguida, coma. Agora eu tenho que começar novamente a próxima pesquisa. Portanto, ele é chamado de X dentro da pesquisa X. Então, desta vez, vou selecionar X look up mais uma vez e, desta vez, o valor da pesquisa seria localização. E onde está minha localização? Está na fila. Vou selecionar a cabeça separada e, em seguida, pressionar Coma e, em seguida, a matriz de retorno seria essa área completa. Ok, então coma, não há necessidade de definir nenhum outro valor opcional, então você pode removê-lo e colocar dois colchetes de fechamento e pressionar Inter para obter seus E para sua conveniência, estou usando fórmula, texto para obter o. Qual é a função que eu escrevi? Então você pode ver que vou passar para o lado negativo por aqui. Desculpe, isso mudou. Então, eu vou mudar isso novamente também. Dê a referência aqui. Então, agora vou explicar mais uma vez essa estrutura completa. Então, eu tenho uma tabela na qual preciso buscar o preço com base na acomodação que está na coluna e a localização que está no cabeçalho dessa tabela significa na linha E o preço seria a interseção da área e o tempo de acomodação Digamos que dê uma olhada nesta casa na árvore em Yorkshire. Então eu tenho três casas, Yorkshire e a interseção custa 160 dólares, e ela está vindo aqui usando essa pesquisa X completa Então, o que eu fiz foi atribuir minha pesquisa X, primeiro valor de pesquisa X é acomodação e forneci uma matriz de pesquisa na minha primeira tabela e selecionei a coluna de acomodação. Então, novamente, eu uso a função Xu para extrair os dados com base na interseção, então decidi fornecer a localização como um valor de pesquisa dessa tabela, e minha matriz de pesquisa estava à frente, e eu dei a faixa completa dos preços começando de C quatro a F sete O benefício de usar essa estrutura de tabela já explicou a você que sempre que eu adicionar mais dados aqui, eles serão atualizados nesta função. Portanto, essa é a vantagem de usar as tabelas. Espero que seja muito útil para você dar exemplos práticos do mundo real Então, obrigado por assistir. Sintonize o próximo vídeo. 13. Vista de três vias: Olá, pessoal. Na seção anterior, discutimos sobre uma pesquisa bidirecional. Neste vídeo, vou explicar como podemos fazer a pesquisa de três vias no Excel. Então, vamos dar um exemplo. Digamos que eu tenha um pouco de Dera aqui e preciso extrair a taxa com base nas três entradas O primeiro é o tipo de quarto. O segundo é o tabuleiro. Quero dizer, o tipo de embarque que o cliente fez durante a semana Portanto, há três argumentos que precisamos usar para obter o preço que está escrito aqui Portanto, a interseção desses dados é o preço, que precisamos buscar Para isso, usaremos a função X Lu. Mas aqui também explicarei algumas funções avançadas de matriz fornecidas pelo Excel em 2026 para você Então, o que precisamos é criar uma lista suspensa para tipo de quarto, alimentação e dias úteis aqui Então, vou mudar para o dia, na verdade, e esse será o preço que precisamos calcular. Então, essas são as coisas que precisamos fazer. Então, primeiro, criaremos a lista suspensa. Para isso, precisamos de algumas colunas auxiliares que possamos ocultar. Então, aqui vamos criar três colunas auxiliares. Então, primeiro, preciso de valores exclusivos do tipo de quarto. Para isso, usaremos uma função exclusiva. É muito simples, igual a seno, único, mais uma aba e fornece o alcance Se for convertido no tipo de tabela oficial, seria fácil porque se torna dinâmico. Significa que sempre que você adicionar novos dados aqui, talvez o novo tipo de quarto, eles serão atualizados automaticamente nessa lista. Mas, no momento, não estou convertendo esse grampo no tipo oficial Estou fechando este e pressiono Enter. Portanto, temos dados exclusivos aqui. E agora eu vou vir aqui para criar a lista suspensa, chegar ao tipo de dados, chegar à validação de dados, selecionar a validação de dados aqui. E a partir daqui, você pode selecionar um tipo de lista. E na fonte, temos que dar a referência dessa célula. É K quatro, K quatro, e depois disso, temos que colocar Hash Caso contrário, não considerará essa lista completa. Ele considerará apenas o valor da célula K quatro. Agora clique em OK, e podemos ver que tenho uma lista suspensa, para que possamos selecionar ouro e prata executivos , o que quisermos. De forma semelhante, faremos isso para o tipo de embarque. Então vou digitar Unique e vir aqui, fechando o colchete. E a partir daqui, vou até a fita de Dera. Chegue à validação de dados, validação de dados e, em seguida, liste, e aqui ficariam quatro hash iguais Agora, tudo bem. Então, aqui eu vou pegá-lo. E agora é a parte complicada. Desta vez, temos dados em sequência. Então, primeiro, precisamos transpor isso e, em seguida, precisamos usar uma função única Primeiro, vou converter a transposição. Para isso, usaremos a primeira transposição. E na matriz, preciso usar Unique e, em seguida, fornecerei o intervalo de todos esses dados, fechando o colchete para exclusivo e seguida, fechando o colchete para transposição, apresentador, e obteremos os dados, todos dados exclusivos nesta coluna A partir daqui, irei para os dados. E depois validação de dados. Novamente, selecione a validação de dados, depois a lista e, a partir daqui, quatro cliques de hash Ok. Agora podemos selecionar o dF nesta lista suspensa Agora é hora de usar o X lookup de três maneiras, eqsine X lookup e seguida, temos que combinar o tipo de quarto e a Já fizemos isso, então vamos combinar usando a porcentagem e o tabuleiro, porque ambos estão nas colunas somente depois em coma e a matriz Lou está em colunas que também precisamos combinar Vou selecionar esse intervalo e, em seguida, porcentagem. Selecionarei novamente esse intervalo e depois entrarei em coma. E agora, na matriz de retorno, preciso usar novamente a pesquisa X. X olha para dentro do X para cima. Vou pressionar tab e, desta vez, vou selecionar C dois, depois coma e a matriz Lou estaria aqui todos esses dias, depois Coma E no tipo de retorno, preciso selecionar esses dados completos. Em seguida, fecharei o colchete para primeira pesquisa e a segunda pesquisa e pressionarei Enter Então, desta vez, temos o executivo completo na quarta-feira. Executivo cheio para quarta-feira, o preço é 899 Agora vou mudar qualquer dia em particular, então é 806 e é 806 Vamos mudar o tipo de quarto para prata. Portanto, o preço é 496. E isso é prata cheia no domingo. Então é assim que você pode usar. Você também pode ocultar essas colunas, para que ninguém saiba de onde vêm esses valores. Portanto, é assim que você pode usar pesquisa de três vias no Excel usando a função de pesquisa X. 14. Vista de três vias com alcance: Olá e bem-vindo à master class do X Loup. No exemplo anterior, discutimos a pesquisa de três vias. Continuaremos com o mesmo exemplo. Mas aqui, temos um intervalo de datas em vez de uma data fixa. No último exemplo, temos um dia fixo da semana como segunda, terça e sábado. Mas desta vez temos que buscar o valor entre uma data específica Usaremos o argumento para buscar as informações. Então, vamos começar. Em primeiro lugar, eu inseriria uma data que esteja entre essas duas datas. Então, digamos que eu considere o 15º dia de Fab 2025. 15º, cinco, 2025. Vou inserir isso e agora preciso buscar o preço nesta tabela Está bem? Então, para isso, usaremos a função Lou Portanto, o valor da pesquisa dependeria do tipo de quarto e da placa. Então, vamos unir esses dois valores. Portanto, esse procedimento é bastante semelhante ao exemplo anterior. Também vou juntar a matriz de pesquisa. Então, tipo de quarto, alcance, porcentagem e tipo de placa. Depois, o CVA Agora, a matriz de retorno. Portanto, na matriz de retorno, usaremos novamente X up. E a data seria essa, vírgula, e forneceremos o intervalo de datas para a matriz de pesquisa aqui, e a matriz de retorno seria essa Tudo seria semelhante e, se não fosse encontrado , podemos inserir zero e usaremos menos um em vez da correspondência exata Portanto, essa é a principal diferença relação ao exemplo anterior. No exemplo anterior, usamos a correspondência exata, mas agora estamos procurando um valor menor do intervalo de datas que fornecemos. Portanto, se o intervalo de datas entre duas datas significar de primeiro de janeiro a primeiro de março. Portanto, menos do que primeiro de março significa até o último dia de fevereiro, a taxa seria aplicável a esta coluna. Esse é o impacto desse valor de argumento menos um. Então, vamos selecionar isso, pressione tab, e não vamos selecionar nenhum modo de pesquisa, etc., então vamos fechar Pressionaremos Control Inter ou comandaremos Enter se você estiver usando o Mac, agora estou no sistema Mac Então você pode ver que temos prata cheia e 536 está chegando. Então, se eu mudar para outro tipo como executivo ou ouro , a taxa será alterada automaticamente. Portanto, essa é a principal diferença usando a pesquisa de três vias se usarmos correspondência exata ou um valor menor menos um no argumento Portanto, no próximo exemplo, aprenderemos mais um truque avançado usando o Excel cup para seu trabalho diário no escritório ou exemplos práticos da vida real. 15. Procure valores a partir da faixa: Olá, e bem-vindo mais uma vez ao novo episódio da Lookup Masterclass Então, hoje, vou explicar mais um truque do Excel. Eu tenho uma tabela de vendas aqui de itens diferentes mensalmente. Esses são os dados. Agora eu preciso buscar todas as colunas aqui usando o item. Eu já criei uma lista suspensa, então posso selecionar um item de estoque usando essa lista suspensa. Eu tenho dois casos. primeiro caso é que tenho o mesmo mês na mesma ordem dos dados de origem, mas tenho uma tabela diferente na qual preciso buscar os dados com base no mês escrito aqui, mas não é a mesma ordem da origem Temos 1º de abril em vez de janeiro. Então, como podemos conseguir, vou explicar neste vídeo. Vamos começar usando a função de pesquisa X. Então, vou digitar X lookup. O valor da pesquisa seria o mesmo. A matriz de pesquisa seria da mesma forma e a matriz de retorno. Então, em vez de fornecer uma única coluna, temos que fornecer a gama completa de dados, fechar o colchete Control plus inter, obtemos nossa resposta Portanto, se você alterar o item dessa lista suspensa, digamos, café. Portanto, ele retornará os dados dessa lista automaticamente. Portanto, é assim que você pode obter se tiver mesma ordem dos valores de pesquisa de origem. Agora vamos para o nosso próximo exemplo. Então, neste caso, eu vou usar Luka e desta vez o valor de busca seria este Coma A matriz Lou seria o título. Em seguida, o Coma, desta vez, vou usar a pesquisa X aqui. Então, vou selecionar o item daqui, e essa seria a matriz de pesquisa, e essa faixa completa seria a matriz de retorno. Agora feche o suporte para ambas as pesquisas. Agora pressione Enter e obtivemos a resposta. Vamos fazer a verificação cruzada. Então, aqui estou eu, mofins selecionados. Vou trocá-lo por outro item. Digamos que torrada. Então essa é a torrada, e é a roupa Então, na torrada, vamos para o vestuário em 733 e 7 de junho 733 e Os mesmos dados que estou recebendo. Então, vamos fazer algumas mudanças aqui. Então, se eu mudar porque é completamente dinâmico, o preço ou os dados serão obtidos automaticamente pela carpa Ax Então é assim que você pode realizar essa tarefa. Obrigado por assistir. Fique ligado no próximo vídeo 16. Automação de faturas com o XLOOKUP: Já discutimos muitas dicas e técnicas relacionadas ao Excel, especialmente no Excel nesta master class. Agora vamos implementar tudo o que aprendemos e discutimos no exemplo prático. Por exemplo, preciso selecionar a categoria na lista suspensa disponível aqui, que está conectada dinamicamente à planilha mestra Isso significa que sempre que eu atualizo os dados, se eu excluir alguma linha, ela deve ser atualizada automaticamente aqui. Portanto, ele está conectado dinamicamente e eu tenho que selecionar qualquer categoria Todos os itens que se enquadram nessa categoria específica serão filtrados aqui Isso também depende dessa lista suspensa. E sempre que eu seleciono qualquer coisa, o preço vem automaticamente, se eu inserir a quantidade, digamos, 75, o valor foi calculado. Também recebo o número de série automaticamente. Como faço isso, explicarei neste vídeo, então fique ligado. Comece do zero. Digamos que eu tenha esses dados. Então, vou copiar esses dados completos. Eu vou copiar. Vou inserir uma nova planilha. Vou renomear para master. Pressione Inter, e então eu vou colar esses dados aqui usando o passpatial Vou selecionar o texto e depois OK. Agora temos que atribuir o nome da tabela, item mestre. Agora, temos que buscar o nome exclusivo da categoria dessa tabela usando a função exclusiva e temos que transportar como cabeçalho nessa primeira linha Para isso, usaremos a função de transposição. No interior, usaremos uma função única e a matriz começará a partir daqui, a partir da segunda linha, deslocará o controle para heroki e fecharemos o colchete para exclusivo e para transporte, e além do Inter, obteremos um nome de categoria exclusivo Agora temos que buscar o nome do produto dependendo da categoria em cada coluna, que deve ser dinâmica, ou seja, sempre que adicionarmos ou excluirmos dados no estábulo, eles devem ser atualizados automaticamente Para isso, usaremos a função de filtro. Na matriz, temos que selecionar primeiro o nome do produto, mudar o controle para baixo. Em seguida, na inclusão, temos que selecionar a coluna completa da segunda linha para a categoria. Usaremos o controle de deslocamento para baixo de arochi, equalsine, temos que selecionar F um onde inserimos a função de transposição para selecionar os valores exclusivos das categorias Depois disso, temos que fechar e pressionar Enter. Pode levar algum tempo para buscar os dados. Agora, obtemos a lista de itens com base na categoria mencionada no cabeçalho. Vou copiar e colar essa função para buscar o nome do item com base no nome categoria mencionado como cabeçalho Vou selecionar o cabeçalho, vou colocá-lo em negrito. E a partir daqui, preciso fazer com que congele na fileira superior. Agora fizemos nossa metade. Agora vou vir aqui como uma amostra de fatura. Vou copiar essa área completa, vou copiar. Vou selecionar uma célula específica e, em seguida, acessar a guia inicial, selecionar a opção espacial de pagamento e, em seguida, selecionar o formato. E depois. Novamente, clicarei aqui, pagarei espacialmente e selecionarei a largura da coluna. OK. Agora, novamente, vou ver a amostra da fatura. Vou selecionar o cabeçalho, chegando à minha folha de fatura número dois Venha aqui e controle mais V para colar um erro. Vou aumentar o tamanho. Vou ampliar um pouco para ver a tabela completa. Vou para a guia Exibir. E remova as linhas da grade. Agora vou começar meu trabalho aqui. Então, primeiro, eu preciso de uma lista de categorias aqui. Para isso, em primeiro lugar, selecionarei uma coluna completa. Em seguida, vou para dados, dados, validação de dados, selecionarei a lista aqui e o sinal de igualdade, chegarei aos meus dados mestres. Vou dar a referência da célula em que inseri a função transport com Unique para buscar os valores exclusivos da categoria Depois disso, não se esqueça inserir Tem brilho e clicar em OK. Agora podemos ver o menu suspenso, que é totalmente dinâmico, vinculado ao mestre. Agora, minha tarefa é buscar o item com base na categoria selecionada aqui Vou selecionar essa coluna completa e, novamente, ir para tipo de dados, validação de dados, validação de dados, validação de dados mais uma vez , selecionarei a lista aqui e, desta vez, selecionarei a função de pesquisa. Sinal de igualdade. Preciso digitar nome completo da fórmula, iniciá-la e, desta vez, dependerá dessa célula C seis, a primeira categoria. Mas quando selecionamos por padrão, ele se torna constante, mas precisamos remover esse cifrão daqui. Caso contrário, você obterá os itens de estoque com base apenas nessa célula C six. Não dependerá da próxima célula, portanto, verifique se você a selecionou. Depois disso, para o cap array, chegamos ao master. Selecionaremos o cabeçalho na hierarquia principal de assentos, turnos, controle e gravação para selecionar a faixa completa Depois disso, Coma, temos que selecionar somente a segunda linha de onde inserimos nossa função de filtro para buscar o nome do item de estoque, dependendo do nome exclusivo da categoria aqui Portanto, apenas a segunda fila, e depois disso, você deve fechar e, no final, não se esqueça de mencionar o sinal de tem para torná-la dinâmica. Depois disso, clique em OK. E deixe-me ver se ele está buscando os dados ou não. Podemos ver o menu suspenso aqui, basta clicar nele. Estou recebendo alguns valores, mas precisamos verificar se está tudo bem ou não. Vou selecionar outra categoria. Digamos que forneça suprimentos porque tem um número de produtos listado. Vou clicar aqui e posso ver uma lista diferente. Muito poucos itens estão chegando. Deixe-me selecionar qualquer item específico. Depois disso, se eu clicar aqui, você verá que muitos itens estão chegando. Agora, passando para a próxima parte, sempre que eu insiro qualquer quantidade, o preço deve vir automaticamente. Portanto, sempre que eu seleciono qualquer item, preço deve vir automaticamente e, quando inserimos qualquer quantidade, o valor deve chegar. Além disso, preciso do número de série. Então, deixe-me começar com o número de série. Então selecione isso para isso, vou usar minha função, contar A com a função I. Começarei dizendo que a referência dessa célula não está em branco, então preciso contar A dessa célula e fornecer a referência da primeira célula de onde mencionamos a categoria. Faça com que seja constante. Então, novamente, temos que selecionar, mas desta vez, não vou torná-lo constante e depois fechar o colchete, caso contrário, temos que entrar Para marcar aspas duplas, pressione Inter para aceitá-la Então, estou comprando um. Agora eu preciso ir até a última linha. Então agora eu posso ver que tenho dois aqui. Agora, se eu selecionar outra categoria, três virão automaticamente. Agora, minha próxima tarefa é buscar o preço. Isso não está em branco. Então, precisamos usar a função X Lou. Isso depende desse valor. Portanto, o valor de pesquisa seria o item e a matriz de pesquisa seria o mestre. Então, aqui temos o nome do produto, eu seleciono Shift Aaroky, depois Coma, preciso buscar o preço Preciso selecionar essa coluna de preços na segunda linha, mudar o controle de mudança para baixo na tecla aro e, em seguida, fechar o colchete para a função I. Agora pressione Enter. Vou colocar essa fórmula na parte inferior também. Agora também estou recebendo o preço aqui. Então, deixe-me selecionar outro item, recebi o preço automaticamente. Agora preciso calcular a quantidade sempre que insiro a quantidade. Então, para isso, vou usar se este não for coma em branco, preciso fazer a multiplicação da quantidade pelo preço Caso contrário, ele deve estar em branco. Fechando o colchete, pressione Control plus enter, arrastando até essa função para baixo Agora vou inserir a quantidade como 15, dez, 20, cálculo foi feito automaticamente para fazer a agregação, para calcular a soma, vou usar a função de agregação Vou selecionar agregado. Eu quero fazer uma soma. Em seguida, selecionarei a opção aqui, ignorarei o subtotal aninhado e a função agregada Coma, darei a referência desta coluna fechando o colchete Control plus Enter Eu tenho o valor calculado aqui. Agora vamos alimentar alguns dados para verificar se tudo está funcionando bem ou não. Desta vez, vou selecionar a tecla de seta Alt para baixo para selecionar minha categoria nesse menu suspenso. Eu seleciono a categoria, pressiono a etapa, o número de série veio automaticamente. Vou selecionar o item a partir daqui. Portanto, todos os itens da categoria de fichários são filtrados automaticamente Vou selecionar qualquer item em particular. Podemos digitar o nome ou selecionar todos os fichários duráveis e pressionar Inter E podemos ver o preço primeiro automaticamente, mas o valor ainda não foi calculado. Depois disso, vou inserir alguma quantidade, digamos, 149, pressionar o cursor Inter para mover para a célula da categoria na próxima linha, e obtenho o valor na coluna de quantidade automaticamente e o subtotal também é calculado automaticamente É assim que você pode fazer a automação no Excel usando algumas dicas e técnicas avançadas. Espero que você goste deste vídeo, fique ligado no próximo Obrigada. 17. XLOOKUP com SUMIFS: Olá, bem-vindo a outro episódio da X Lookup Master Class. Neste vídeo, darei um exemplo de cálculo da soma usando a pesquisa X. Então, quando calculamos a soma dos valores, precisamos de números para calcular. Então, primeiro, darei um exemplo muito simples calcular o valor de venda dependendo de um item específico a partir daqui Depois disso, podemos usar a função Sum Is para vários critérios. Aqui na versão avançada, selecionarei um determinado mês e o item, e obteremos o total aqui. Para isso, usaremos a função SiS, mas antes disso, preciso calcular o valor total de um determinado mês fornecido aqui. Então, eu quero torná-lo dinâmico em vez de estático para que o usuário possa alterar o mês usando uma lista suspensa, como criar uma lista suspensa que já discutimos e criamos várias vezes Então, desta vez, eu não vou criar. Além disso, você precisa criar uma lista suspensa para sua prática aqui Então, vamos selecionar uma célula e digitar algumas, e então usaremos para buscar os números usando a pesquisa A pesquisa Valet estaria aqui, depois Coma, e os meses foram fornecidos na parte do cabeçalho, então selecionaremos esse cabeçalho completo e, em seguida, Coma Na matriz de devolução, precisamos selecionar somente esses dados de vendas. Fecharemos o colchete para consulta e também a soma. Depois de pressionar Enter, obtemos o total de mil 6.511 vendas totais para o mês de Então, deixe-me verificar. Então, se eu selecionar essas células, para que eu possa ver que alguns valores estão chegando aqui , está completamente correto. Agora vou mudar para março e ele será alterado para 6.603. Então é assim que você pode criar uma função dinâmica para calcular a soma, agora passando para nossa função avançada Essa é a soma Is. Então, como você sabe, os SIs são usados para calcular a soma com base em alguns critérios. Se você é novo na função Sumix, mostrarei como usamos SumixFunction e quais são os argumentos Portanto, alguns ifs são usados para calcular a soma de um determinado intervalo com base nos diferentes critérios Portanto, se for usado para um único critério, mas se tiver vários critérios , usaremos alguma função ifs. Desta vez, vou usar algumas funções ifs. Então, primeiro de tudo, temos que definir o intervalo da soma. Então, como eu disse, queremos torná-lo dinâmico, que significa que temos um mês diferente fornecido aqui. Temos dados de vendas de café na área um, depois na área dois, e esta é a área três. Então, eu quero calcular a soma com base em um item específico. Então, vou selecionar, digamos, em janeiro. Então, esse é o meu intervalo de soma para calcular a soma. Então, o próximo intervalo de critérios, qual é o intervalo de critérios? Então, aqui eu tenho itens, e então temos que definir um critério. Então, esse é o meu critério. Se eu fechar esse apresentador, recebo 2.155 Esses são os valores totais de vendas dos mopins. Assim, você pode cruzar, selecionar a área um, depois a área dois e, em seguida, a área três, e aqui podemos ver 2.155 Então esse é o uso da função Sis. Agora, vamos torná-lo dinâmico para cálculo com base no mês e no item a partir daqui Nossa função, que é a soma da função, não a soma, caso contrário, os argumentos são diferentes se você selecionar a semifunção Então, aqui eu tenho que definir meu intervalo de soma, que deve ser dinâmico. Dinâmico significa que também mantemos nosso mês aqui para que a coluna seja alterada de acordo com nossos requisitos. Então, para isso, usaremos a função de pesquisa X. Então, primeiro, selecionarei o mês, depois coma, depois a matriz de pesquisa seria a cabeça completa separada e, em seguida, coma, então temos que selecionar a área completa da tabela. Em seguida, fecharei a função de pesquisa e, em seguida, coma, agora preciso definir o intervalo de critérios. O intervalo de critérios seria os itens. Vou selecionar esta coluna e Coma e esse seria meu critério. E vou fechar o suporte para alguma função. Então, se eu pressionar Inter, agora é completamente dinâmico Portanto, a venda da Mopins no mês de março é de 2.556. Agora deixe-me mostrar que este é Mfanes e este é o mês de março, e vou selecionar minha segunda célula, e esta é minha terceira E podemos ver 2.556. É o total. Agora, se eu mudar para abril e se eu mudar qualquer outro item como X, é X, basicamente. Então, para X, isso é abril e isso é X, e eu selecionarei a área dois e a área três, e o total é 2.436 Então é assim que você pode usar função SiS com X Luka para calcular a soma Isso é muito útil quando você cria painéis porque precisamos colocar uma quantia e calcular a soma com base nessa área específica Espero que você goste deste vídeo. Obrigado por assistir. Fique ligado no próximo exemplo Obrigada.