Domine as funções de procura no Excel para análise de dados

1. Boas-vindas no curso de Lookups: Bem-vindo às pesquisas do Excel para o curso de análise de dados. Meu nome é Chen Do. Neste curso, você aprenderá a usar fórmulas do Excel como pesquisa do VLookup x, correspondência de índice, filtro HLookup. E como combinar essas funções para várias situações práticas em sua linha de trabalho. Começamos com o uso básico de todas as funções de pesquisa importantes no Excel. E então eu vou ensinar técnicas como a fórmula de correspondência de índice. Procuramos como fazer todos os resultados correspondentes com uma fórmula de pesquisa. Como executar uma pesquisa de vários critérios. Como consolidar dados em tabelas usando pesquisas sobre para executar pesquisa em derivação com colunas e como gravar pesquisas que retornam várias saídas de coluna. Também vamos analisar erros comuns que ocorrem quando você está escrevendo pesquisas para corrigi-los facilmente. Há um monte de conteúdo valioso neste curso, mas tudo é tão bem embalado e produzido de forma concisa para que você aprenda o máximo, no mínimo de tempo cada lição vem com um vídeo e um exemplo. Sugiro que você baixe as pastas de trabalho, pratique os conceitos de pesquisa e aprenda enquanto avançamos. Existem alguns problemas de exemplo de lição de casa para você descobrir, bem como um projeto de classe usando algumas fórmulas de pesquisa. Eu recomendo que você complete estes problemas de lição de casa e o projeto da turma e compartilhe seus resultados conosco na área da comunidade. Eu executo um site popular do Excel e do Power BI chamado Chengdu.org. Também tenho um canal no YouTube com o mesmo nome. Entre meu site e meu canal no YouTube. Ajudo mais de 0,5 milhões de pessoas a cada ano a se tornarem incríveis em sua linha de trabalho. É minha missão de vida torná-lo incrível em seu trabalho. Faço isso há mais de 13 anos. Eu vivo na bela, mas ocasionalmente ventosa Wellington na Nova Zelândia. É todo o caminho no canto do mundo. Quando eu não estou ensinando Excel, eu gosto de gastar meu tempo construindo Lego com meus filhos ou repetindo um dos jogos Zelda estão levando nosso cão, Excel em uma caminhada, ou fazer um curry bonito e delicioso com minha esposa, Joy. Preciso deles. Estou super animado para tê-lo neste curso. Desejo-lhe toda a grandiosidade nas pesquisas. 2. Noções básicas do VLOOKUP, XLOOKUP e XLOOKUP: Bem-vindo à nossa sessão de introdução sobre pesquisas no Excel. Neste vídeo, aprenderemos como usar três das fórmulas de pesquisa mais importantes no Excel Eles estão prontos, Xp e Hup. Neste vídeo, também apresentarei nosso conjunto de dados de amostra e, no restante da aula, usaremos mais ou menos esse conjunto É um conjunto de dados de funcionários composto em que eu tenho seus nomes, sexo, departamento em que trabalham, a data em que ingressaram na organização e quanto salário eles recebem. Agora, a primeira fórmula que aprenderemos é a fórmula hep. Hep é uma forma abreviada de pesquisa vertical. Isso permitirá que você faça uma pergunta específica sobre seus dados e obtenha a resposta. Por exemplo, com dados como esses, posso fazer uma pergunta como: qual é o salário de Hussain Auger e mais, você responderá Uma maneira simples de pensar em Weeks, você pode comparar isso com o ponteiro do mouse ou com o dedo indicador Agora imagine que isso está impresso e você está digitalizando esses dados para descobrir onde está Hussain Auger E então você simplesmente atravessa a tela. Para encontrar seu salário. Isso é exatamente o que o loop também faz. Agora, vamos ver como escrever essa função. Para escrever o calcanhar, você começa dizendo igual a L e depois abre Você pode digitar L e pressionar tecla tab e xL digitará a fórmula, abra o colchete Em seguida, o valor de pesquisa é o valor que você deseja pesquisar. Basta digitar o nome augur e agora a tabela A é onde estão seus dados, selecione seus dados Então, o número do índice da coluna é a coluna na qual seus dados estão. Nesse caso, quero procurar usinagur e depois receber o salário deles Usinagur é a coluna de nomes número um, sexo é dois, departamento é três, data conjunta é quatro e salário é Queremos que a quinta coluna seja devolvida. E então o último parâmetro é se você quer uma correspondência aproximada ou exata Agora, na maioria das situações de negócios, você sempre quer uma correspondência exata, então isso será falso. E quando você pressionar o centro de imprensa, você receberá o salário deles como um valor. Então aqui você pode ver que é 67.910. Isso é o que diz nossa fórmula. Agora, vamos voltar e observar essa fórmula. Aqui, estamos digitando um nome em códigos duplos, mas você também pode fazer com que o nome apareça em outra célula, que o nome apareça em outra célula, nesta planilha ou em alguma outra planilha , e vinculá-lo Vamos fazer isso digitando outro nome aqui. Vou copiar isso, colar aqui, e usaremos esse nome para procurar por Jan Morph, qualquer que seja o nome em oito, ou seja, a célula Queremos o departamento dessa pessoa. O departamento dessa pessoa estaria acima oito meus dados e o departamento é o índice da coluna três quedas. É assim que você pode configurá-lo. E obterei a resposta de qual departamento eles estão trabalhando. Agora que você entendeu o uso básico do copo de roda, vamos explorar a função de pesquisa X, que é como uma versão aprimorada da função heel up Então, para fazer a mesma coisa, exatamente o que fizemos agora para o Hoseinager, posso usar x lookup como este X. O valor de pesquisa é o que você deseja pesquisar. valor de pesquisa é o que você deseja pesquisar Nesse caso, auger e, em vez de selecionar todos os seus dados para o Xp, você precisa selecionar dois conjuntos de dados Você precisa informar ao EXL onde os dados que você deseja encontrar, neste caso, a coluna do nome, bem como qual é a coluna da qual você deseja retornar os resultados correspondentes Nesse caso, queremos o salário. Portanto, não precisamos selecionar todos os dados. Nós apenas selecionamos nome e salário. É isso aí, você nem precisa especificar mais nada na configuração padrão. Ao fornecer a coluna de nome e a coluna de salário, você obterá o resultado do salário. De certa forma, o XP é mais curto para gravar porque você está apenas escolhendo os dados necessários e especificando o que deseja e o EXL fornecerá Portanto, como o X Loup é uma versão aprimorada do cup no restante do curso, sempre que possível, ensinarei as abordagens baseadas no Xup para resolver os problemas Agora, vamos escrever a mesma fórmula do departamento de janeiro com Xp para que você se familiarize mais com isso. Vamos dizer até. Meu valor de pesquisa está aqui. Minha matriz de pesquisa é a coluna de nome e minha matriz de retorno é a coluna de departamento. Agora, ao digitar a fórmula xp, você percebe que na verdade existe uma opção chamada Não encontrei Vamos usar isso e depois especificar dentro de códigos duplos não encontrados. Obviamente, sendo Jan Morfor nosso funcionário, o departamento chega Mas como essa é uma célula de entrada, eu posso mudar isso. Vamos colocar isso como endo e depois ver isso. Agora, nossa fórmula original de copo de roda retorna um erro de hash, enquanto essa fórmula de copo XL , como especificamos o que queremos quando o valor não é encontrado, ela dirá não Na verdade, esse é outro aplicativo poderoso do X lookup. Xp adiciona esses recursos adicionais para que, como analista de dados, você não precise pensar no que fazer quando houver um erro. Você apenas lida com isso dentro da fórmula. Uma das maiores limitações da pesquisa é que ela só pode ir da esquerda para a direita. Com isso, quero dizer que , com dados como esses, posso procurar um nome e obter a data de adesão ou o salário. Mas não consigo pesquisar um salário e obter o nome da pessoa por meio do Whop porque hep está olhando para a coluna da esquerda e depois indo para a Esse tem sido um grande problema para os usuários do Excel por mais de uma década. É por isso que a Microsoft introduziu a função de pesquisa X, pois dessa forma, você pode pesquisar qualquer coluna e retornar outra coluna. Vamos procurar o salário de 48.170. Esse é o salário que eu quero pesquisar e a pessoa será X. Nesse caso, queremos pesquisar esse salário na coluna de salários e obter o nome da pessoa Observe como alteramos a ordem desses dois porque, desta vez, estou procurando na coluna de salário e retornando o nome. Instantaneamente, posso descobrir quem é essa pessoa e você pode verificar com seus dados se é realmente Jan Morfort Até agora, temos usado Vp e Xp em intervalos regulares de células do Excel Se você observar essas fórmulas, em todos os lugares selecionamos os dados como C cinco a G 20 ou C cinco a C 20 dessa forma Eles são chamados de intervalos regulares. Mas você também pode usar essas fórmulas quando seus dados estão em uma tabela Quando seus dados estão em formato de tabela ou tar, as fórmulas também se tornam mais naturais e simples em inglês Dessa forma, você não precisa pensar muito ao escrever a fórmula Vamos usar o mesmo na mesa. Só para você se familiarizar com o conceito. Vamos procurar um nome. Eu só vou copiar isso. Esse é o nome que queremos pesquisar e escreveremos duas fórmulas Um está em alta e o outro é X, quais são seus salários. Pesquisamos em Paty, e meus dados já estão em uma tabela chamada staff Tudo o que preciso fazer é dizer equipe, todos os meus dados, e então o salário é a quinta coluna. O último argumento seria falso e receberemos o salário de Kins. Com X, a fórmula se torna X L J oito nossa célula de entrada. Nome da equipe e salário da equipe. A principal diferença entre up e x look up aqui é que em up você especifica a tabela inteira, enquanto em Xp, você está especificando duas colunas individuais, a coluna de pesquisa e a coluna de retorno, e você obterá a resposta Usarei tabelas para demonstrar o restante das fórmulas desta classe porque é assim que os dados são mantidos em situações de negócios Embora a fórmula ascendente funcione, quando seus dados estão orientados verticalmente , como valores descendo pela tela, se você tiver valores que atravessam a tela, precisará usar a fórmula Vamos dar uma olhada nisso também. Digamos que recebemos nossos dados de vendas do último ano em um formato matricial como esse. Então eu recebi meu nome e depois obtive meus valores de vendas. E eu só quero saber quais são vendas de junho do Doughty Vamos colocar o nome do mês aqui, junho em uma célula. Observe a célula C seis e escreveremos duas fórmulas Uma é uma pesquisa dH e a outra é Xp, porque o Xp também é capaz de fazer pesquisas horizontais Outra razão pela qual o X Loup é melhor do que as pesquisas el cup e hedge Vamos começar com o hedge aqui em cima. Obtenha o valor da pesquisa, que é minha tabela June and Agora, como os nomes dos meses estão na parte principal da tabela, precisamos selecionar a tabela inteira, não apenas a parte dos dados, para que ela se torne o hash de vendas A. E então precisamos especificar o número do índice da linha porque precisamos pesquisar junho, mas descer e obter os dados de uma linha específica que pertence ao Doughty Nesse caso, Doughty está na linha número 13. Se você contar a partir do nome, dizemos 13 e, em seguida, dizemos A sintaxe é H e segue o mesmo padrão, mas em H sua orientação de dados é horizontal, você obterá a resposta, que é exatamente o que são as vendas de junho do Doughty Agora vamos fazer a mesma coisa com o X. X até junho. Desta vez, você deseja especificar a linha do cabeçalho da tabela porque eu quero pesquisar junho nos cabeçalhos da tabela de vendas e quero obter os dados do Doughty Strutle Selecionamos a linha que corresponde a Doughty e fechamos o colchete e obteremos o mesmo Neste ponto, você pode estar se perguntando, hein Chen, isso é bom. Mas e se eu tiver duas entradas, preciso ter a opinião de junho, mas também quero uma contribuição de Doughty Strut, para que eu possa consultar as vendas de maio Andrea Kimpton Essas pesquisas são chamadas de pesquisas bidirecionais ou bidirecionais, e eu tenho uma aula no plano de aula que também fala sobre essa técnica Então fique por aqui e assista isso também. Por, 3. A fórmula de INDEX+MATCH: Bem-vindo ao LevelUp Excel Loops Class. Neste vídeo, entenderemos a fórmula de correspondência de índices. Digamos que você tenha uma data inserida em uma célula e seus dados estejam aqui. Você gostaria de saber qual é o nome da pessoa que ingressou nesta data. Agora, conforme explicado no vídeo anterior, você pode usar a função Xp para fazer isso. Mas digamos que você não pode usar o X Loup porque não está no EXL 365 ou não quer usar o X Então, como você responde a essa pergunta? Para obter a resposta para isso, você precisa fazer isso em duas etapas porque a função p vai da esquerda para a direita, então não podemos realmente pesquisar a data e ir para o lado direito para obter o nome. A etapa número um é descobrir onde essa data ocorre nessa lista e obter a posição dessa data. Na verdade, essa é a quinta data na lista, então é o quinto item. A etapa número dois é obter o quinto nome. O problema se torna duplo, encontre a posição e obtenha o item correspondente Para encontrar a posição de um item em uma lista, podemos usar a função match. OK. Então, combine um valor em uma lista de valores. Portanto, neste caso, nossa lista de valores, a data em que a equipe ingressou e como você deseja combiná-la, você sempre deseja fazer uma correspondência exata a menos que esteja fazendo algo muito específico. Então, o último argumento se torna zero e feche o colchete e você obterá a posição dessa data Qualquer que seja essa data, se ela tiver uma posição nesta lista, ela aparecerá. Então, cinco, 19 de abril se torna cinco, mas 18 de maio de 2018 se torna a oitava data Agora, vamos buscar o nome da pessoa. Eu sei a posição. Quero obter o oitavo nome ou o quinto item da lista É quando o índice da outra função é usado. Índice, uma lista, finalize o item desejado. Oito. Isso lhe dará Andrea Kimpton como nome E se você voltar para a data original, cinco, 19 de abril, receberemos o nome de Curtis Advani Juntas, essas duas fórmulas criam uma nova construção chamada fórmula de correspondência de índice A maneira como isso funciona é escrever um índice rapidamente, especificar a lista da qual deseja obtê-lo. Estamos analisando a data e queremos o nome. Então, começamos com o nome e, em seguida, escrevemos a função de correspondência. Dizemos que esta é a data que estou procurando. Na coluna de data com uma correspondência exata, obteremos o nome. Agora que você entende a fórmula de correspondência de índices, vamos ver algumas substituições para isso A substituição número um que você deve tentar é a pesquisa X. Já vimos isso no vídeo anterior. Vou fazer uma demonstração rápida novamente. Essa data, na lista de datas em que entramos, e queremos o nome. Vamos pegar Curtis Sadvan. Outra opção que você pode usar é index plus x match. A função x match forma a mesma família de x. É uma das funções mais recentes, e funciona como esse nome de índice, data de correspondência na junção de datas Para x match, você não precisa especificar o modo de match. Sempre será uma correspondência exata por padrão, você receberá novamente Curtis Avan É assim que as funções de correspondência de índice 4. de duas vias (linha e coluna): Nesta lição, vamos entender como realizar pesquisas bidirecionais usando o Excel Vou mostrar três técnicas diferentes e, dependendo de como seus dados estão disponíveis e de qual versão do Excel você está usando, use um desses métodos. Para este exemplo de problema, gostaríamos de calcular as vendas garrafas de xadrez em março. Esse é o valor que estamos tentando obter. A primeira técnica que usaremos é a técnica de correspondência de índice. Já sei que a função de índice pode ser usada para obter um valor específico em uma lista. Se eu disser nomes e depois fornecer quatro, obterei o quarto nome, que é GG Mas a função de índice tem outro uso. Se você fornecer um A grande, ou seja, um grande intervalo em vez de apenas uma lista, se eu selecionar essa tabela inteira, a tabela de vendas, e depois dizer quatro K três, obterei o valor na linha número quatro, coluna número três. Então, obteremos as vendas de fevereiro da Gigi Boling porque esta é a coluna um, dois, três, e esse é o número 1234, e terminaremos Podemos usar esse método para descobrir qual é o valor aqui simplesmente usando duas fórmulas de correspondência em que números quatro e três estão disponíveis. Vamos tentar isso aqui. Vamos dizer índice. Precisamos selecionar apenas a parte dos dados da nossa tabela. Você também não deseja selecionar o nome porque suas fórmulas de pesquisa precisam ser um pouco diferentes E então diga que a primeira partida será no nível da linha, então queremos combinar xadrez bonel em todos os nomes com uma combinação exata A segunda fórmula de correspondência que queremos fazer é combinar março na lista de meses. Basta selecionar os meses em sua tabela e, novamente, uma correspondência exata e fechar a fórmula do índice. Observe como isso é configurado no índice de seus dados e uma correspondência no item no nível da linha e uma correspondência nos itens no nível da coluna. E chegaremos aos 36.415 como resposta. O segundo método para fazer isso também é, de longe, o meu favorito, que é usar duas funções X up. A fórmula do copo EXL, como você viu nos vídeos anteriores, substitui o up, mas também tem outro superpoder Vou demonstrar isso para que você possa entender o que quero dizer com isso. Então dizemos que sim. Vamos procurar o borel de xadrez na coluna do nome e retornar a tabela de vendas inteira Então, em vez de especificar uma única coluna, como vendas de janeiro ou vendas de março, dizemos que procure o nome C sete na coluna de nomes e, em seguida, obtenha a tabela inteira O que isso fará é dar a você aquela fileira inteira para a garrafa torácica. Excel 2000 x 3605 tem esse belo recurso de derramamento, o que ele fará é imprimir o valor e perceber que , no total, existem 13 valores, então ele apenas estenderá o resultado para essas 13 células automaticamente Esse é um dos poderes ocultos da Xcup. Na verdade, ele pode retornar uma faixa inteira de valores em vez de um único valor. Agora tudo que eu tenho que fazer é obter esse valor. Agora que temos essa lista inteira, se eu enviar isso para outra xícara XL e depois pedir o valor de março. Você receberá esse. Então, começamos com X. Procuraremos o osso torácico na coluna do nome e retornaremos um desses valores Quando você pressiona o centro, ele fornece todos os 12 valores. Não vai te dar o nome, mas vai te dar tudo. Você pode ver que esses valores passarão assim. Usaremos outro x x. Desta vez, procuraremos março nos nomes dos meses e, em seguida, retornaremos o valor correspondente. Então, isso vai te dar 36.415, o que é novamente o mesmo. Vamos entender essa fórmula um pouco mais de perto. Então, como você pode ver, estamos usando dois lops X. Essa XP interna fornecerá todos os 12 valores correspondentes ao osso torácico Se você selecionar tudo isso e pressionar control igual a, posso ver que ele retornará essa matriz e esse loop x externo procurará por march nos cabeçalhos Então, ele descobre que março é o terceiro cabeçalho, então ele obterá o terceiro valor correspondente a partir disso, que será esse valor. É assim que esse método Xp x loup funciona. Aqui está um truque bônus. Você poderia, em vez de pesquisar primeiro o número da linha, pesquisar primeiro a coluna e depois examinar a linha novamente. É assim que isso funcionaria. Diremos x look , procuraremos march nos cabeçalhos e obteremos os valores para march Como você pode adivinhar o que essa função faz, ela fornecerá todos os valores de marcha para todos. Então, essa pesquisa x, posso enviá-la para mais um X X procure xadrez bonel na coluna do nome e, em seguida, obtenha o valor correspondente a partir Isso também funcionará. Não importa para que lado você vá rápido, você pode ir para a linha rapidamente e depois para a coluna ou coluna rapidamente e depois para a linha. Mesmo resultado. Agora vamos ver o terceiro método, que na verdade é uma mistura de um e dois. Podemos usar index x match x match porque função x match é uma versão mais curta da função match, eu posso usar isso. Eu direi que indexe meus dados, apenas a parte numérica, X combine xadrez bonel na coluna de nomes, X combine março nos cabeçalhos do mês e obteremos a mesma resposta Aí estão três métodos diferentes. Agora, se você está se perguntando qual método usar, se estiver usando o Excel 3605, esse deve ser seu método preferido, pois é bastante simples e usa métodos modernos de Mas se você estiver usando uma versão mais antiga do Excel, essa se tornará sua abordagem padrão. Para se divertir, você também pode tentar o número três. O que for adequado, você pode experimentá-los. Espero que você tenha achado tudo isso útil. Nos vemos novamente no próximo vídeo. 5. Todos resultados iguais com Lookups: Uma das principais limitações do X L p ou de qualquer outro tipo de função de pesquisa em EXL. Eles só podem consultar o primeiro resultado correspondente. Digamos que você esteja procurando o departamento financeiro e queira encontrar todas as pessoas desse departamento. Você tem sua fórmula de pesquisa aqui, X L, o valor no departamento e no nome, e no momento em que você coloca finanças, ele simplesmente encontrará a primeira pessoa, que é Curtis Advani, e imprimirá que não há outra maneira de recuperarmos esses outros nomes por meio das Neste vídeo, deixe-me demonstrar uma técnica para gerar todos os resultados de correspondência possíveis ao digitar um departamento. Se eu disser vendas, vou pegar todas as pessoas no site do departamento de vendas, vou receber o site, se eu colocar um departamento que não existe em nossos dados, não obterei resultados. Vou mostrar duas técnicas, uma para fazer isso com o XCL 3605 e outra para a versão antiga do Excel Vamos ver como isso pode ser feito. O Excel 3605 introduziu um novo conjunto de funções chamado funções de matriz dinâmica Uma dessas funções é conhecida como filtro. O que a função de filtro faz é pegar alguns dados e filtrar com base nas condições desejadas. Podemos usar a função de filtro como uma função de pesquisa no EXCL 365. Por exemplo, você pode dizer dados da equipe em departamento da equipe é igual e, em seguida, codificar duplamente as finanças, e isso praticamente fornecerá um subconjunto de dados que pertence apenas ao pessoal financeiro Portanto, é quase como se você aplicasse filtros nos dados e escolhesse finanças. Seja qual for o resultado, é isso que você está obtendo aqui. Mas isso é uma fórmula. Assim, você pode parametrizá-lo, mudar a forma como aparece e tudo Então é assim que eu fiz essa parte. Eu coloquei meus critérios de entrada aqui para que eu possa digitar o que eu quiser. E aqui eu escrevi uma função de filtro simplesmente dizendo que filtrar o departamento de pessoal da equipe é igual a d três, que é minha célula de entrada. E se não houver resultados, basta imprimir a palavra sem resultados, e é assim que os resultados aparecerão. Uma das limitações da função de filtro é que ela não retornará os dados conforme a formatação original Aqui, as datas e as moedas estão bem formatadas, enquanto aqui a data aparecerá como um número, assim como a Você precisará selecionar as células em que seus resultados podem aparecer e aplicar a regra de formatação com antecedência Observe que sempre que você digita a função de filtro, ela é uma dessas funções de matriz dinâmica. Automaticamente, dependendo do tamanho do resultado, o Excel reduzirá ou aumentará a área de resultados dessa fórmula Isso é chamado de faixa de vazamento. E quando você tem uma fórmula que tem um intervalo de vazamento, Excel destacará isso com essa caixa azul Portanto, qualquer parte do conjunto de resultados que você selecionar, Excel mostrará aquele retângulo azul em torno desse ditado, tudo isso faz parte da faixa de vazamento Você pode examinar a fórmula que está retornando o intervalo de vazamento selecionando a célula e examinando a barra de fórmulas Agora, a barra de fórmulas ficará acinzentada se você selecionar qualquer célula. Mas se você selecionar a célula superior esquerda do intervalo de derramamento, verá que ela é editável, então eu também posso alterar a fórmula É assim que você pode usar a função de filtro para retornar todos os resultados correspondentes. Deixe-me mostrar outro exemplo. Digamos que eu não queira ver todos os detalhes deles. Eu só quero os nomes das pessoas no departamento financeiro. Veja como podemos fazer isso. Diremos os nomes e depois diremos os nomes dos funcionários do filtro. Então, desta vez, eu só quero que o nome da equipe seja filtrado com base no departamento de equipe igual a T três, e eu só vou pegar os nomes Além do filtro, a Microsoft também introduziu outras fórmulas, como sort uniq, etc. Então, por exemplo, posso enviar os resultados desse filtro para uma função de classificação para que eu possa ver esses nomes em ordem alfabética Isso é muito útil, especialmente para exibições em que seus dados podem estar em qualquer ordem, mas as saídas estão em uma ordem bem ordenada Se você estiver usando o Office 3605, eu recomendo que você experimente essas funções e as implemente em seus fluxos de trabalho Vamos entender como fazer a mesma coisa em uma versão mais antiga do Excel. O conjunto de fórmulas que usaremos nesta parte do vídeo é um pouco mais avançado e complicado se você nunca fez esse tipo de coisa Mas eu recomendo que você assista e veja quais são as ideias-chave que você pode adotar e implementar em seus fluxos de trabalho Se eu colocar o nome do departamento aqui, obterei os resultados. Para o propósito deste exemplo, estou mostrando apenas os cinco primeiros resultados. Se eu colocar vendas, obterei os dois primeiros e os resultados, e se eu colocar um site, obterei os cinco primeiros resultados. Para fazer esse trabalho, precisamos primeiro entender algumas das coisas internas. Então, vamos examiná-los primeiro. Então, eu configurei alguns cálculos já na planilha e apenas os escondi Vamos desembrulhar isso. Então, temos uma célula de entrada onde vou digitar meu departamento que eu quero examinar. Este é D três na planilha antiga do Excel que corresponde a A. A primeira etapa é descobrir se D três nessa folha é igual a um desses valores. Portanto, sempre que for igual, quero retornar o número específico da linha na tabela. É aqui que eu uso uma forma simples de vida. equipe do departamento de tarifas que é o departamento nesta linha é igual ao meu D três. Então eu quero obter o número da linha dessa célula específica. É aqui que entra a função de linha. Linha de funcionários no departamento menos o número da linha do valor do cabeçalho Dessa forma, posso gerar um número relativo. Caso contrário, isso vai ficar em branco. Aqui vou pegar para esse site em particular duas, três, seis, sete, oito, nove e 12 pessoas . Em qualquer lugar que não corresponda , ficará simplesmente em branco. Podemos pensar nisso porque eu tenho uma lista de números e só quero descobrir quais são os primeiros cinco números pequenos porque estamos mostrando apenas os cinco primeiros resultados. É aqui que eu gero os números de um a cinco. Basta digitá-los na célula. E eu quero descobrir qual é o primeiro menor número, segundo, menor número desse tipo. Excel também tem outra função chamada small, e você pode usá-la para fazer isso. Você diz pequeno desse retângulo azul. Isso tem todos os meus números, e qual é o primeiro, o menor número, o segundo, o menor número. Uma vez que os temos, tudo o que temos que fazer é pegar a segunda linha inteira, pegar a terceira linha inteira, obter a sexta linha inteira assim. Para fazer isso, podemos usar a função de índice. Mostrarei como obter um índice de linha específico. Então, vamos dizer índice, dados. Então, neste caso, equipe, e o número da linha que queremos é dois. Queremos todas as colunas dessa tabela que estão na segunda linha. Quando quiser todas as colunas, basta dizer coma e ignorar a parte do número da coluna da fórmula do índice. Se você simplesmente disser dois, não obterá a resposta, precisará dizer vírgula e fechar o colchete Nesse ponto, o que a função de índice faz é fornecer a segunda linha inteira. E como o EXL 3635 tem uma funcionalidade de derramamento, ele realmente pega o valor e o transborda Mas nas versões mais antigas do X, selecione todas essas cinco células, pegue-as e pressione control shift enter para obter o resultado. Mas agora essa função está dividida em cinco células, cada célula mostrando o valor da coluna correspondente. Esse é o método que estou usando aqui. Eu gerei os números de identificação 23678, e então eu os conecto aqui nessas células, cinco células, e aqui estou apenas usando o índice desses dados e depois deixo isso Esta parte gerará a linha inteira e estamos imprimindo isso por meio da tecla de controle shift enter. Agora, há apenas um problema extra, que é se o departamento não existir. Então, por exemplo, colocamos RH. Esses dados não estão na nossa tabela de funcionários. Em seguida, precisamos imprimir um resultado como nenhum resultado ou algo assim. É aqui que estou usando a função de erro. Se minha fórmula de índice aparecer como um erro, imprima esta mensagem amigável. É assim que você pode fazer todos os resultados de lotes em versões mais antigas do Excel Embora essa técnica específica não seja relevante para usuários do EXL 365, porque você tem a melhor fórmula de filtro Acho que aprender esse uso específico da fórmula de índice pode ajudá-lo em outras situações. Espero que tudo isso tenha sido muito útil. Nos vemos novamente na próxima lição. 6. Olhando em colunas derivadas: Neste vídeo, vamos entender como fazer pesquisas quando os critérios ou o cálculo são um pouco mais complicados Eu os chamo de pesquisa em colunas derivadas. Examinaremos como escrever fórmulas de pesquisa para essas cinco situações E então, no final do vídeo, também pedirei que você resolva mais três problemas como lição Vamos um de cada vez. Queremos descobrir a pessoa com salário máximo. Em outras palavras, eu gostaria de descobrir o nome dessa pessoa, mas tudo por meio de fórmulas Número um, será que o valor que você está procurando é o máximo na coluna de salário. Então você diria x lookup ou up. Vamos usar a pesquisa X. Máximo do salário. Esse é o valor que eu quero pesquisar na coluna de salário do pessoal. E então queremos saber o nome deles, então diremos o nome da equipe. Isso lhe dará o nome de Plaqan. segunda é a contratação de testes. Nesse caso, queremos descobrir quem é a última pessoa a ingressar na organização. Isso significa que quem tem a data de inscrição é a mais recente, essa é a pessoa que gostaríamos de reunir E, novamente, a fórmula se torna muito simples quando você entende a lógica Dizemos p no máximo na coluna associada à data, porque tecnicamente, as datas el são números, então podemos usar Max para fazer o mais recente, ou seja, fazer o mais antigo E então diremos que a data de ingresso da equipe. E, novamente, nome da equipe para obter seu nome. E vamos descobrir quem é essa pessoa. Nesse caso, é Barfony em seis de outubro de 2019. terceira é que gostaríamos de receber a pessoa que se juntou a nós no ano de 2017. Portanto, temos um valor de pesquisa, que é 2017, mas precisamos primeiro calcular o ano a partir da junção de datas. A junção de datas contém a data inteira e queremos apenas pesquisar com base apenas no componente do ano. Acontece que há apenas um valor, mas se houver vários valores, à medida que pesquisarmos ou X faria, eles fornecerão o primeiro resultado. É exatamente aqui que x up realmente brilha porque você pode fazer esses cálculos derivados facilmente Digamos que a pesquisa é 2017 e a barra de pesquisa é A data da junção, mas não é a data de junção direta. Não podemos usar a data de adesão como tal. Em vez disso, queremos pegar a data de junção e enviá-la para a função auditiva para que possamos descobrir todos os anos a partir disso. Quando o ouvido lê isso, ele gera uma lista ou uma matriz de números que será como 2000 1919 1918 assim, e então queremos retornar o nome Vou te dar Jan Morfor, que é a pessoa que ingressou em 2017. Essa é uma construção muito poderosa. Então, vou demonstrar isso novamente com o quarto exemplo, que é que as três primeiras letras são RAF Aqui, o valor que eu quero pesquisar é novamente fornecido. Queremos pesquisar a RAF. Mas o Luca para está no nome. Mas, em vez de apenas o nome, queremos apenas as três primeiras letras da coluna do nome. Podemos usar uma função como left na coluna do nome. Vamos dizer à esquerda do nome três, e então isso me dará todas as três primeiras letras como uma matriz, e então queremos obter o nome delas. Isso lhe dará novamente afata. Agora, vamos entender como a esquerda interna funcionará. Eu seleciono essa parte e a avalio sozinha com controle igual a, e posso ver todas as três letras, exceto Dongg that Então, como essas são todas as três letras, XL cup tentará igualar RAF e corresponderá ao valor e, em seguida, fornecerá o nome correspondente O quinto exemplo que precisamos calcular é que o salário é de 88.000 Agora, você pode ver na coluna de salários que essa pessoa não existe. E, de fato, quando você faz um X Lp ou até mesmo mais de 88.000 e apenas diz que quer o salário e quer o nome, você comete um erro porque essa pessoa não existe Mas a coisa real que queremos buscar é essa pessoa, Chest Bonel A questão agora precisamos primeiro arredondar o salário para 2000 e depois fazer a partida. É assim que você pode fazer isso. Você pode pegar X L, pesquisar 88 e, em seguida, pegar o salário em si enviá-lo para a função redonda. Pegamos a coluna salarial e, em seguida, dizemos que a rodada do salário do pessoal é dividida por 1.000 a zero dígitos Isso basicamente arredondará todos aos milhares, $88.050 se tornam 88, e então queremos o nome, e isso lhe dará Esse é outro exemplo. Esses três exemplos seguem o mesmo padrão, mas mostram que X L pode fazer cálculos em vez de colunas diretas, daí o nome das colunas derivadas desta lição, conforme prometido. Tenho três problemas de lição de casa para você Quero que você descubra a pessoa com o salário mínimo, pessoa cujas duas letras das duas últimas letras do nome são ND e a pessoa que ingressou no mês de agosto. Não importa em que ano. Tudo o que sabemos é que eles se juntaram no mês de agosto e só queremos saber seus nomes. Descubra as fórmulas do calcanhar ou as fórmulas do Excel que fornecerão essas respostas e compartilhe-as na seção de comentários. Isso é tudo por enquanto. Espero que você tenha gostado dessa lição. Nos vemos na próxima. Tchau tchau. 7. Vários critérios (as as de várias condições (as as quais a condição: Neste vídeo, aprenderemos como criar uma pesquisa com vários critérios usando o Excel. Especificamente, criaremos algo assim em que você poderá inserir um vendedor e o nome do país e, em seguida, obterá esses resultados Vamos fazer uma demonstração rápida aqui. Vou mudar isso para xadrez e deixar o país como Reino Unido, e você pode ver que a fórmula que escrevi calculou que existem dois registros e está exibindo o primeiro, que também está destacado aqui. Então, como fazemos uma pesquisa baseada em várias condições. Já sei disso se eu usar Xp ou algo assim. Então, por exemplo, sobe X e depois diz xadrez na coluna de nome da minha tabela de vendas e, em seguida, obtenho o valor, eu receberei a primeira quantia de xadrez. Portanto, não será necessariamente no valor do Reino Unido. Quero dizer, nesse caso, seria Reino Unido porque esse é o primeiro valor. Mas pode ser outra coisa , dependendo de como seus dados são ajustados. Por exemplo, se mudarmos isso para barra , queremos o valor das barras no Reino Unido, que é 2499, mas minha fórmula X L me dará o valor das barras nos EUA porque esse é o primeiro valor Então, como fazemos várias condições? Bem, a técnica é usar o conceito de colunas derivadas abordado no vídeo anterior e ampliá-lo. Por exemplo, diremos X look, procure um e, em seguida, a matriz de pesquisa precisa estar entre colchetes abertos A coluna de nome é igual ao meu nome, que é L seis. Multiplique isso pela coluna do país que é igual ao meu país. Então, a coluna de retorno deve estar porque é isso que queremos pesquisar. Agora, ao apresentar, você obterá o resultado correto, que seria 2499 Mas como isso funciona? OK. Bem, vamos entender isso. Aqui estamos dizendo: procure um e, em seguida, pegamos uma lista, que é minha coluna de nome de venda, e comparando-a com L seis. Se eu fizer essa parte e depois avaliar isso para avaliar uma parte da fórmula, você pode selecioná-la e pressionar controle igual ou a tecla F nove, e obterá os resultados da avaliação. Seria verdade onde quer que a pessoa estivesse no bar. Então, você pode ver que o segundo valor é verdadeiro porque estamos comparando com a barra e, em seguida, o quinto valor também é verdadeiro porque a quinta pessoa é a barra. Da mesma forma, essa lista também será um monte de valores verdadeiros e falsos. Quando você pega vários valores falsos verdadeiros aqui, multiplique-os pelos verdadeiros valores falsos ali Sempre que os valores correspondentes sejam verdadeiros em ambas as listas. Isso significa que o nome é igual a bar é verdadeiro. O país é igual ao Reino Unido também é verdade. O resultado líquido será duas vezes verdadeiro, o que será um no mundo do Excel. Como o Excel trata verdadeiro e cai como zero, um e zero, então uma vez um se torna um. É por isso que estamos procurando um. Estamos dizendo que procure um e , em seguida, faça toda essa multiplicação aqui e, em seguida, obtenha o valor Onde quer que seja um, ele produzirá uma lista de uns e zeros, essa coisa toda Se eu selecionar tudo isso e pressionar control igual a, você pode ver que na verdade é zero até o fim, exceto pelo quinto registro, que é bar e UK. A próxima pesquisa dirá que encontrei o item correspondente na quinta posição, então ele obterá o valor correspondente da coluna de valor, que seria meu 2499 Foi assim que escrevi essa fórmula. única coisa adicional que fiz foi inserir informações, nenhuma mensagem informativa sempre que não fosse encontrado nada. Depois fiz o mesmo com clientes e caixas. Quando colocarmos isso, isso me dará esse resultado. Agora vamos tentar o bar USA onde temos vários registros. Temos três discos. Como o XL encontrará apenas o primeiro, estou mostrando uma mensagem opcional aqui e imprimindo apenas os detalhes do primeiro registro Estou calculando o número de registros? Bem, isso é muito simples. Podemos usar a função countifs para pesquisar quantas vezes a combinação de barras e EUA apareceu Diremos que o nome é bar, país é EUA e então eles me darão três vezes. Aqui estou dizendo que se meu número de registros for maior que um, basta imprimir a mensagem mostrando o primeiro registro, caso contrário, fique quieto. É assim que isso é construído. Você pode usar essa técnica específica e estendê-la a qualquer número de situações. Agora, aqui está um projeto extra para você? Você pode tratar isso como um projeto de classe. Como você buscaria os três discos e os mostraria? Em vez de uma, quero que você mostre o número dois e o número três abaixo usando as outras técnicas que abordamos nos vídeos anteriores Trate isso como um desafio e, se tiver algum problema, verifique novamente a pasta de trabalho da solução nos links de descrição vemos novamente no próximo vídeo. Tchau tchau. 8. Como fazer IFs nested para sair com a função nested: Nesta lição, demonstrarei como calcular o bônus usando a fórmula de pesquisa. Aqui tenho os dados de nossos funcionários e gostaríamos de oferecer bônus com base nessas regras Salário de até $60.000, você receberá um bônus de 5% até 75, E se seu salário for de até 90.000, você recebe 3%, qualquer coisa acima, você recebe 2% Então, como calculamos o bônus? Você pode ficar tentado a escrever uma fórmula longa e aninhada. Mas você pode usar uma função de pesquisa mais curta para fazer isso. Antes de fazer isso, precisamos realmente configurar nossos dados de bônus em formato de tabela. Você deseja ajustar seus dados de forma que o item rápido seja zero, e isso lhe dirá qual é o bônus por isso. A maneira de ler isso é de zero a 60.000, você entendeu. 60 a 75, você pega isso, 75 90, você pega aquilo. Qualquer coisa acima de 90, você entendeu. É assim que você deseja ajustar seus dados e criar uma tabela de mapeamento. Quando isso estiver lá, você pode usar a função de pesquisa dessa forma. Procure, observe que isso não é up, H lookup ou x lookup, é uma função de pesquisa simples, selecione o salário e, em seguida, o vetor é a primeira coluna da sua tabela e, em seguida, o resultado é a segunda coluna dessa tabela. Agora, se esses dados não estiverem em formato de tabela, eles estarão em intervalos de venda. Certifique-se de fazer de tudo isso como referências absolutas, alterando-as para o formato em dólar. Você pode selecionar toda a faixa e pressionar F quatro, e o EXL adicionará os dólares necessários para você Feito isso, feche o colchete, pressione enter e o EXL calculará porcentagens de bônus necessárias e as imprimirá lá. Vamos verificar novamente. Essa pessoa está recebendo 75.000 salários, ela deveria estar com 3% porque está acima de 75.000 e não 75.000, então ela cairá Considerando que essa pessoa está ganhando mais de 90.000 , ela entrará em 2% Essa pessoa tem menos de 60.000, então ela recebe 5% assim É assim que posso calcular minha porcentagem de bônus. Também posso calcular valor do meu bônus em dólares pegando isso e multiplicando assim ou alterando essa fórmula e obtendo o resultado da pesquisa multiplicando pelo valor do salário da taxa Vá em frente e use função de pesquisa em vez de fórmulas aninhadas A única coisa que você precisa ter em mente é essa tabela precisa ser classificada em ordem crescente Você realmente não pode colocá-los fora de ordem. Você precisa ir do zero e definir os limites claramente para que os valores possam ser apresentados corretamente pelo Excel. Nos vemos novamente no próximo vídeo. 9. Uma e várias colunas como resultado: Neste vídeo, mostrarei como usar o loop x e up para buscar várias colunas com uma única função Portanto, para esse fim, colocaremos nosso nome de pesquisa na célula J four e veremos instantaneamente todos os detalhes desse funcionário na horizontal ou na vertical Então, vamos entender isso. Vou digitar uma garrafa de xadrez aqui e, instantaneamente, obterei todos os resultados. Agora, na verdade, não são cinco fórmulas. Essa é uma fórmula única. Então, como isso funciona? Vamos começar do zero. Dizemos valor de pesquisa x e, em seguida, onde está o item de pesquisa, então está no nome. Mas quando se trata de retornar uma matriz, em vez de selecionar uma única coluna, selecionaremos a tabela inteira e fecharemos o colchete Observe que a coluna de retorno não está restrita a uma única coluna, ela fornecerá a linha inteira do bonel de xadrez aqui e, em seguida, a imprimirá bem na tela Essa é uma funcionalidade introduzida no EXL 3605 junto com a funcionalidade L cup. Portanto, toda vez que você escrever o copo clo e fornecer mais de uma coluna como critério de saída, ele automaticamente pegará os valores e os espalhará na tela Esse comportamento é chamado de comportamento ortográfico, e toda essa funcionalidade é chamada de funcionalidade de matriz dinâmica Também vimos isso em outro lugar no exemplo da fórmula do filtro. Embora isso seja bom, como faço para pegar isso e girá-lo na vertical? Bem, você pode usar isso junto com a função de transposição. Aqui estou fazendo isso. Mais uma vez, vou mostrar do zero. Vamos dizer x, chapéu torácico na coluna do nome. Não vamos pegar a mesa inteira. Em vez disso, vamos obter apenas o gênero por meio do salário. Essa é a coluna de retorno e obteremos quatro resultados na tela. Agora, em vez de atravessar a tela, podemos usar a função de transposição no início e enviar os resultados do copo para a função de transposição O que a transpose faz é pegar vários valores e mudar a orientação Se você enviar várias linhas, ele as transformará em colunas, vice-versa. Aqui, a transposição pega isso e os inverte e os mostra na tela Agora, vamos ver isso aplicado a um formato de dados diferente. Aqui, recebi meus nomes e, todo mês, os valores de vendas são listados em formato de matriz. E eu quero saber, para um determinado nome, qual é o Q q total. Q q aqui seria outubro, novembro, dezembro. Aqui está minha fórmula de copo XL. Soma da xícara de um Kimpton de Andrea Kimpton na coluna do nome e, em seguida, queremos que os resultados venham das colunas de outubro a dezembro E então, uma vez que o copo fornece esses três valores, nós apenas os somamos. Você também pode usar isso com o bom e velho loop como este. Você diria que a soma da pesquisa Andrea Kimpton na tabela de vendas e o retorno deveria ser de 11, 12 e 13 colunas entre E então você dirá falso. O que o loop fará nessa situação é retornar todos os três valores como uma lista e, em seguida, a soma os resumirá. Agora, se você estiver usando essa função no Excel 365, basta pressionar enter. Mas em uma versão mais antiga do Excel, você ainda pode usar essa construção, mas deve pressionar control, shift, enter para obter o resultado. Nos vemos novamente no próximo vídeo. 10. Combine duas tabelas (consolida) com with: Nesta aula, nós vamos entender como combinar duas tabelas usando a fórmula de pesquisa. Aqui, tenho um conjunto de dados de funcionários um pouco mais longo , com 1.000 funcionários, e temos várias colunas de informações sobre funcionários Mas também temos outra parte do quebra-cabeça aqui com o nome e a data de nascimento disponíveis em uma tabela separada, e gostaríamos de mesclar esses dois para criar uma visualização combinada Então, aqui, por exemplo, posso dizer data de nascimento e, em seguida, usar up ou x lookup. Então, vamos dizer X, meu valor de pesquisa é nome e, em seguida, a matriz de pesquisa é a coluna de nome no DobStable e a coluna de retorno é minha data de nascimento DOBS E ao fechar isso, você receberá alguns valores, alguns lugares onde o funcionário não tem uma data de nascimento correspondente, você receberá um erro. Vamos corrigir o erro primeiro para que possamos apagá-lo ou colocar algo como a data de nascimento ausente e, em seguida, selecionar a coluna inteira, aplicar rapidamente um formato de data nela e teremos nossas informações de data de nascimento disponíveis. Então é assim que você pode usar a pesquisa Vp ou X para combinar duas tabelas Agora, vamos tornar isso um pouco mais complicado. E se sua tabela de data de nascimento não tiver o nome, mas tiver sobrenome e nome duas colunas separadas e o valor da data de nascimento. Veja como você pode fazer isso. Adicione uma nova coluna. Vamos chamar isso de DO two. Nesta coluna, escreveremos uma fórmula de pesquisa para que possamos pegar o nome completo aqui e compará-lo com a combinação do sobrenome e do primeiro nome. Vamos anotar esse nome estável. O estável é chamado de DOB S two. Podemos escrever a fórmula aqui diretamente agora valor do X Loop é meu nome. A matriz de pesquisa precisa ser a combinação das colunas rápidas de nome e sobrenome da minha tabela DOBS two Isso seria DOB, fname, ampersend space e, em seguida, ampersen DOBS two last Observe como, com o próprio Luka para, estamos pegando duas colunas diferentes dessa tabela e as combinando, e então a coluna de retorno precisa ser DOBS com duas datas de nascimento Então, se nenhum valor for encontrado, apenas imprimiremos um valor em branco e obteremos novamente o resultado. Vamos aplicar alguma formatação. Você pode aplicar a formatação de data em casa aqui, mas aqui está um atalho que eu normalmente gosto de usar Eu gosto de usar o Control shift three para transformar rapidamente os valores em formato de data com o formato DMM y. A ideia de usar duas colunas e combiná-las aqui é semelhante à ideia de colunas derivadas que você viu em algum outro vídeo. Ambas as abordagens oferecem uma maneira poderosa de combinar dados, mesmo que a formatação não seja consistente Espero que você tenha achado isso útil. Nos vemos novamente em outro vídeo. 11. Extract dados com looks em uma grande: Nesta lição, mostrarei como extrair algumas colunas e linhas de um grande conjunto de Então, aqui eu tenho dados de 1.000 funcionários, e temos várias colunas de informações, então vão até a coluna CD. E tudo isso é feito aleatoriamente, mas esse tipo de conjunto de dados é bastante comum Na verdade, tive que usar uma técnica semelhante na casa um cliente várias vezes ao longo do ano passado. Então, como fazemos isso? Deixe-me primeiro explicar o que está acontecendo aqui. Eu posso selecionar qualquer número de colunas. Eu posso especificá-los aqui, os números das colunas que eu quero extrair, e então eu posso dar os nomes, e esta parte aqui vai me mostrar os dados relevantes. Então, por exemplo, aqui, em vez de duas, quero a coluna número três, coloco três lá e, instantaneamente, recebo o título do trabalho e, em seguida, o título é impresso aqui. Eu posso mudar essa ordem. Eles não precisam ser assim. Então, por exemplo, depois de três, posso ter dois, depois sete e depois um, e obterei os detalhes conforme mostrado lá. Então, como fazemos isso? Primeiro, configure seus dados originais em uma tabela. Não precisa estar em uma mesa, mas tê-lo em uma mesa simplifica sua vida. Então, no nosso caso, coloquei isso na tabela chamada equipe. E agora vamos para a página de extração. É aqui que gostaríamos de extrair. O mínimo necessário são os nomes ou o identificador exclusivo Então, neste caso, eu tenho meus nomes listados na coluna C. E então eu também preciso identificar quais colunas eu quero extrair em qual ordem? Então, imprima-os na tela ao longo do caminho, assim. E agora, para a primeira linha, usaremos a fórmula do índice para obter o cabeçalho da segunda coluna, o cabeçalho sétima coluna, o cabeçalho da sexta coluna e imprimi-los lá. Essa fórmula de índice é como esse índice dos cabeçalhos hash da minha tabela de funcionários. Portanto, isso lhe dará acesso a todas as informações do cabeçalho na tabela de funcionários E então o cabeçalho que eu quero aqui é o número dois, então vamos apontar para ele e fechar o colchete. Vou pegar o cabeçalho da segunda coluna, que é gênero, e depois arrastamos isso lado para ver os cabeçalhos relevantes para Agora vem a parte em que temos que extrair isso. Vou simplesmente deletar isso e escreveremos a fórmula. Então, queremos dar uma olhada em X nesse valor na própria coluna do nome. Então, vamos dizer o nome da equipe. Mas o que devolvemos. Agora, a coluna que precisamos retornar seria duas aqui porque essa é a coluna de gênero, mas seria sete aqui, seis ali, 12 ali. Portanto, a coluna que queremos é dinâmica. É aqui que a função de índice é útil. Vamos dizer índice da tabela de funcionários. Queremos todas as linhas, então diremos apenas a para indicar que não quero uma linha específica, quero todas as linhas. Mas o número da coluna que eu quero precisa ser esse, então vamos apontá-lo para D quatro. Agora, você precisa fazer com que algumas dessas referências sejam referências mistas. Isso sempre estará na linha número quatro. Eu só vou mudar isso para D dólar quatro. Da mesma forma, isso precisa estar sempre na coluna C, então vou mudar essa referência para o dólar C sete. Novamente, para alterar esses estilos de referência, coloque o cursor lá e continue pressionando a tecla f quatro até obter o estilo desejado. Feito isso, fechamos a fórmula do índice e depois fechamos o colchete. É isso mesmo. Obteremos o gênero. Copie esta fórmula, selecione toda essa gama, controle e você obterá os resultados aqui de uma maneira bonita e agradável. Tudo isso é dinâmico. Na verdade, quando eu construí algo assim, um dos meus colegas na casa de um cliente viu isso, e ele ficou super impressionado ao pegar uma cópia disso, criar um modelo de planilha em branco para que ele pudesse usá-lo para fazer extratos da folha de pagamento sempre que precisasse modelo de planilha em branco para que ele pudesse usá-lo para fazer extratos da folha pudesse usá-lo para fazer de pagamento sempre que precisasse Então, espero que você tenha achado essa técnica útil. Agora, você não precisa usar o X Loup. Você também pode usar o hep para fazer isso. Não estou explicando essa parte aqui porque já abordamos detalhadamente o hop and lookup, mas sinta-se à vontade para encarar isso como um desafio e escrever a fórmula básica do Vp ou consultar o arquivo de download para aprender como fazer isso Espero que você tenha achado esta lição útil. vemos novamente na próxima. Tchau tchau. 12. 8 erros e remédios comuns: Nesta lição, falarei sobre oito erros do Looker nas correções Esses erros são erro de valor ausente, erro valor que não está realmente ausente, resposta incorreta, erro digitação, erro de dados, erro referência, erro de coluna e, em seguida, a fórmula não funcionará Vamos ver o que são. O erro de valor ausente é, de longe o erro mais comum que você vê ao usar a fórmula de pesquisa. Então, por exemplo, se eu estiver usando x lookup ou v lookup e tentar procurar algo que não está lá. Então, neste caso, estou procurando por Chandu na coluna de nome da minha equipe e tento obter o salário de Chandu. Receberei o hash A, que é o erro comum que você vê quando o você vê quando Então, como corrigir esse erro. No xp, você pode usar a opção não encontrada para corrigir isso. Por exemplo, posso dizer que não foi encontrado aqui e isso imprimirá a mensagem não encontrada. Dentro do Vp, a mesma fórmula se torna algo assim. Então, consultamos a equipe do Chando em cinco quedas, e isso será um erro, e podemos usar a função de erro if para evitar isso Quando a ajuda tem um erro if, isso significa que é um erro, então nem nosso funcionário será a mensagem, e ele imprimirá essa mensagem. Para o próximo tipo de erro, imagine que temos um valor de xadrez bonel aqui, e eu quero realmente procurar essa pessoa Então, vamos usar we look up. Procuraremos o nome na tabela de funcionários e, em seguida, eles descobrirão que seu sexo é falso. Agora, pudemos ver claramente que o xadrez Bonel está lá, mas nos enganamos Isso é uma espécie de valor, que não falta realmente. Isso é porque achamos que isso é xadrez Bonel. Mas quando você edita a célula, percebe que na verdade há um espaço extra no final, o que está causando esse problema. Então, como corrigir isso? Bem, a solução número um é remover o espaço, para que possamos excluí-lo e isso resolverá o problema. Mas se você não puder editar isso por qualquer motivo, a outra opção é usar uma função como aparar seus conjuntos de dados de entrada para que você possa remover espaços indesejados e obter o resultado correto Isso funcionará com Luka ou x. O terceiro tipo de erro é a resposta incorreta Aqui você pode ver que estou olhando para o peito, mas estou entendendo o sexo deles como mulher Já aqui na tabela, sexo deles diz masculino. Então, o que está acontecendo? Isso ocorre porque, quando você usa a fórmula de pesquisa, se você esqueceu o último parâmetro e o omite, o Excel o padronizará como verdadeiro, o que significa que ele procurará uma correspondência aproximada Isso significa que ele sairá do topo. Ele assumirá que sua lista está classificada em ordem alfabética, então procurará xadrez bonel primeira pessoa é B, a segunda pessoa é D. O Excel assume que, como B e depois C não estão mais lá e agora é D, eu lhe darei a resposta para B, que é mulher aqui Para corrigir isso, você precisa especificar o último parâmetro como falso. Outra alternativa é usar x lookup em que você nem precisa especificar o tipo de modo de pesquisa Sempre será uma consulta exata. Isso também lhe dará a resposta correta. Aqui está nosso próximo erro de tipo de erro. Sempre que você comete um erro de digitação em sua fórmula, é mais provável que receba o erro do nome de hash Então, quando você vê o erro do nome do hash, significa que você cometeu algum tipo de erro de digitação Aqui eu tenho minha fórmula Xp. Tudo parece bem, mas em uma inspeção mais detalhada, você pode ver que escrevemos incorretamente a pesquisa X como x LUK No momento em que você corrigir isso, isso resolverá o problema. Outro tipo comum de erro de digitação que as pessoas cometem é dar um nome errado na tabela Na tabela ou na própria coluna, se você digitar incorretamente, receberá o erro de tipo Agora, esses tipos de erros de digitação são fáceis detectar porque, no momento em que você tenta apresentar, o Excel emite uma mensagem de aviso dizendo que você está tentando digitar algo que não faz sentido É aí que também destacará que essa parte da fórmula não é significativa. Isso é muito fácil de pegar. Mas o outro tipo de erro de digitação aqui não é fácil de detectar e o XL gerará o erro do nome do hash O próximo tipo de erro que você cometeria é um erro de dados. Digamos que tenho um nome de funcionário como Douty Strutle aqui e queremos ver Então eu digo X L essa pessoa no nome da equipe e depois no departamento da equipe. E então obtemos um erro de valor de hash. O que está acontecendo aqui? Isso ocorre porque, quando você observa seus dados, percebe que o valor do departamento deles é, na verdade, o valor de hash Isso pode acontecer com bastante frequência quando você tenta copiar dados de outro sistema ou de outra planilha e, se houver um erro em um dos cálculos, meio que se infiltrará Agora, esse tipo de erro é difícil de detectar. Se eu colocar, por exemplo, departamento de pessoal e, em seguida, se não for encontrado como erro de valor. Isso ainda não vai aparecer. Ele fornecerá o valor do hash em vez dessa mensagem de erro específica Isso ocorre porque, tecnicamente, encontramos o Doughty Strutle, acontece que seus próprios dados estão É aqui que você pode usar a função de erro ou inspecionar a qualidade dos dados e corrigir quaisquer erros O tipo de erro de texto é um erro de referência. É quando você tenta se referir em sua fórmula a algo que não existe mais. Vamos fazer isso com uma fórmula simples aqui. Vamos procurar o salário de Doughty Strutle. Nome da equipe e , em seguida, salário da equipe. Nós obtemos a resposta aqui. Mas no momento em que eu excluir essa coluna, receberei um erro de hash aqui porque não tenho mais acesso a essa coluna em particular, então recebo o erro de hash rah Esse tipo de erro é bastante comum, especialmente se você se referir a coisas em outra planilha e fechar esse arquivo ou coisas assim Ao receber o erro de hash wrap, você precisa voltar e verificar se suas fórmulas têm o que precisam para funcionar sétimo erro é um erro de coluna, e isso é muito, muito comum nas fórmulas p Então, vamos escrever uma olhada aqui. Temos Dotty Strutle e depois colocamos Dotty Strutle na tabela de funcionários, e depois queremos a coluna salarial deles, que é cinco em quedas . E obtemos o resultado correto aqui. salário de Dotty é de 41.980, e é isso que estamos recebendo aqui Mas observe o que acontece se eu inserir uma coluna no meio. Não precisa estar entre os salários, mas pode estar em qualquer lugar. Desde que o salário não seja mais a coluna número cinco, agora seja a coluna número seis, esse valor se torna zero porque essa pessoa não tem ideia do que acabou de acontecer na tela. Ainda está se referindo alegremente à coluna número cinco, em vez de movê-la para seis Então, quando você faz esse tipo de coisa, isso será um colmeror novamente, isso é muito difícil de detectar porque não há indicação visual do que aconteceu, e isso pode ser bastante problemático, especialmente quando você está trabalhando em um ambiente de equipe onde outras pessoas estão adicionando colunas ou movendo Novamente, esse é outro motivo pelo qual você deve usar as funções de copo XL Vou desfazer as etapas e mostrar o que acontece com o X Lup X, meu valor é o nome da equipe, o salário da equipe, e receberemos a mesma resposta aqui e ali. Mas se eu inserir uma coluna, essa se torna zero, mas essa ainda fornece a resposta correta, porque ainda se refere tecnicamente à coluna de salário do pessoal e funcionará desde ninguém renomeie a Esta última é uma fórmula que nem vai funcionar. Essa é uma pergunta muito complicada . Vou escrever a fórmula e você verá que ela não funcionará. Se eu disser X, então procuraremos esse valor. Nome da equipe, salário da equipe. Apresentador, nada acontece, a fórmula permanece como está, como se fosse um valor de texto Isso ocorre porque aqui nessa célula específica, a formatação foi definida como formato de texto Agora, normalmente, a formatação da célula el seria geral ou numérica, data ou moeda Mas essa célula, eu já a configurei para texto. Ou você pode fazer isso acidentalmente ou outra pessoa pode ter feito isso por algum outro propósito Mas quando você tem uma célula como formatação de texto, qualquer fórmula digitada nessa célula não funcionará Correção fácil, altere a formatação de volta para geral, edite a célula e pressione enter, e agora a fórmula funcionará Então aí está, oito erros de pesquisa e soluções fáceis para eles. Espero que você tenha achado esta lição de todo o curso muito útil para aumentar o nível do seu loop e aumentar o nível do jogo Muito obrigado por assistir, e eu vou te encontrar novamente em outro lugar.