Fórmulas e funções do Excel parte 5: pesquisa e funções de referência | Chris Dutton | Skillshare

Velocidade de reprodução


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Fórmulas e funções do Excel parte 5: pesquisa e funções de referência

teacher avatar Chris Dutton, Founder, Excel Maven

Assista a este curso e milhares de outros

Tenha acesso ilimitado a todos os cursos
Oferecidos por líderes do setor e profissionais do mercado
Os temas incluem ilustração, design, fotografia e muito mais

Assista a este curso e milhares de outros

Tenha acesso ilimitado a todos os cursos
Oferecidos por líderes do setor e profissionais do mercado
Os temas incluem ilustração, design, fotografia e muito mais

Aulas neste curso

16 aulas (1 h 8 min)
    • 1. Introdução a as funções de pesquisa e de referência

      1:23
    • 2. Como trabalhar com matrizes nomeadas

      3:30
    • 3. FILas e COLUNa/

      3:18
    • 4. VLOOKUP/HLOOKUP

      5:54
    • 5. Juntando dados com o VLOOKUP

      6:23
    • 6. Como corrigir erros com o IFERROR e VLOOKUP

      4:30
    • 7. Opções de referência em VLOOKUP

      6:18
    • 8. Função de INDEX

      1:59
    • 9. Função de correspondência

      2:32
    • 10. Como usar o INDEX e combinação

      6:08
    • 11. Combinando MATCH o with

      4:47
    • 12. UPDATE: atualização: VLOOKUP

      5:05
    • 13. A função OFSET

      2:05
    • 14. Combinando OFSET com COde

      2:58
    • 15. MOSTRA de PROJETO de PROJETO: como usar o OFFSET para criar uma gráfico de rolagem dinâmico

      9:51
    • 16. HOMEWORK: casa: olhada/de de referência

      0:55
  • --
  • Nível iniciante
  • Nível intermediário
  • Nível avançado
  • Todos os níveis

Gerado pela comunidade

O nível é determinado pela opinião da maioria dos estudantes que avaliaram este curso. Mostramos a recomendação do professor até que sejam coletadas as respostas de pelo menos 5 estudantes.

1.019

Estudantes

--

Projeto

Sobre este curso

Este curso é parte de uma série de 9 parte em Fórmulas e funções do Excel e em

As funções de pesquisa e referência são algumas as ferramentas mais & Excel para adicionar e de analisar dados. Nesta seção vamos mergulhar em fórmulas VLOOKUP e HLOOKUP e a combinação de

Sobre o série de fórmulas e funções do Excel

Nesta série, você vai desenvolver ferramentas para transformar um programa de planilha básico em uma ferramenta de análise dinâmico e forte. Os cursos abordam 75+ fórmulas e de quem as de

Você vai aprender como escrever funções complexas e poderosas do zero, para que a medida que lhe permite

  • Crie ferramentas e painéis dinâmicos para filtrar, exibir e analisar seus dados
  • Junte-se de datasets de várias fontes de segundos com funções de LOOKUP, INDEX e MATCH
  • Faça uma pessoa em tempo real de APIs no Excel usando o WEBSERVICE e FILterXML
  • Manipule datas, de datas, de tempo, texto e matrizes com a facilidade
  • Automatize as tarefas de análise tede e de demores e em de de a
  • E muito mais

Se você estiver procurando a série UMA cobrem todas as fórmulas e funções avançadas para se tornar uma estrela de rock de rock de Excel de que você está a e a sua de parte!

Conheça seu professor

Teacher Profile Image

Chris Dutton

Founder, Excel Maven

Professor

Chris Dutton is a Certified Microsoft Expert and Founder of Excel Maven, with more than a decade of experience specializing in data science and business intelligence. His work has been featured by Microsoft, the Society of American Baseball Research (SABR) and the New York Times.

Excel Maven provides high-quality online analytics training, hands-on workshops, and project-based consulting services to more than 100,000 students across 180+ countries.

Visualizar o perfil completo

Nota do curso

As expectativas foram atingidas?
    Superou!
  • 0%
  • Sim
  • 0%
  • Um pouco
  • 0%
  • Não
  • 0%
Arquivo de avaliações

Em outubro de 2018, atualizamos nosso sistema de avaliações para melhorar a forma como coletamos feedback. Abaixo estão as avaliações escritas antes dessa atualização.

Por que fazer parte da Skillshare?

Faça cursos premiados Skillshare Original

Cada curso possui cursos curtas e projetos práticos

Sua assinatura apoia os professores da Skillshare

Aprenda em qualquer lugar

Faça cursos em qualquer lugar com o aplicativo da Skillshare. Assista no avião, no metrô ou em qualquer lugar que funcione melhor para você, por streaming ou download.

Transcrições

1. Introdução a as funções de pesquisa e de referência: Tudo bem. Bem-vindos à Seção 5. Procure as funções de referência. Agora estamos começando a entrar em algumas das fórmulas realmente, realmente poderoso bater pesado. Oh, eu mal posso esperar para começar nesta seção. Nós vamos falar sobre nomear uma raça, que é uma maneira de trabalhar de forma mais eficiente com suas fórmulas de pesquisa em casos em que você está usando os mesmos arrays várias vezes. Eu vou falar V, olhar para cima em idade, olhar para cima, que são tipo de dois clássicos olhar para cima funções que são usados o tempo todo e Excel. Vamos falar sobre algumas fórmulas como colunas de ovas e linhas, colunas , que poderiam ser componentes realmente interessantes. Como parte de outras funções de olhar para cima irá para índice e correspondência falar sobre como combinar aqueles como usar correspondência com uma função de pesquisa V para fazer algumas coisas realmente poderosas. E então vamos falar offset, que é um pouco complicado, mas realmente, muito interessante fórmula e vai fazer na demonstração do Excel durante aquela palestra onde vamos construir este gráfico dinâmico que pode rolar, ampliar e mover, basicamente fazer algumas coisas que a maioria das pessoas nunca pensaria que se destaca, capaz de alguns animado para mostrar que temos arquivos para download. Obteve o PDF Excel para analistas, procure Funções de Referência e o Arquivo do Excel, Seção cinco Procurar referência. Então vá em frente e baixe os dois e vamos nos preparar para rolar. 2. Como trabalhar com matrizes nomeadas: Então eu quero tomar apenas um segundo para falar sobre algo chamado Raise antes de mergulharmos em funções específicas de pesquisa. matrizes nomeadas são realmente boa ferramenta para ajudá-lo a simplificar suas funções de pesquisa, especialmente se você se encontrar usando a mesma matriz de dados exata em vários lugares ou através de várias fórmulas. Então, por exemplo, se eu tiver dados de nível de produto em células de 1 36 e eu estiver usando essa data exata uma referência em vários lugares, eu poderia dar um nome a ela. Neste caso, eu estou chamando isso de um perigo para que no futuro, quando eu escrever fórmulas que se referem a esta matriz, eu posso ou escrever um um Colin D seis ou eu posso apenas roupas direita, que é o nome que eu atribui a este array. Então se destaca. Reconheça isso e entenda que estou falando sobre o array de um 136 Hum, então vamos entrar no Excel e dar uma olhada no arquivo com o qual vamos trabalhar. Seção cinco. Procure referência. Temos quatro abas aqui. Primeira guia chamada Hitting Data, é uma de nossas guias de dados brutos. Temos nomes de jogadores, anos depois, anos depois, e depois um monte de dados de acerto, métricas e colunas veem através de dados de campo finais. Nós também temos um jogador em sua coluna e, em seguida, algumas métricas de dados de campo adicionais e C G. Temos uma guia de verificação de preços, que vamos preencher assim que aprendermos a usar índice e correspondência e combinar o dois e, em seguida, temos o gráfico de rolagem, que vai ser uma ferramenta realmente incrível que vamos construir usando a função offset em . Vamos criar um gráfico que poderia ser de ervas de rolagem e zoológico móvel e dinâmico e basicamente fazer coisas que a maioria das pessoas nunca pensaria que o Excel é capaz de fazer. Então, realmente animado para compartilhar isso, hum, mas voltando ao conceito de matrizes nomeadas vamos mostrar um exemplo. Vamos para a guia de dados de campo. Se eu quiser usar esta matriz inteira nesta guia para algo como uma função de olhar para cima, o que eu posso fazer é clicar em uma seta de deslocamento de controle de um hit, direita árabe para baixo para que eu tenha todo o array selecionado na primeira maneira de definir isso como um Array nomeado é clicar nesta caixa aqui, que é a caixa de nome e apenas dar-lhe um nome. Vou chamar-lhe Fielding. E agora, se você entrar na guia fórmulas, clique no gerenciador de nomes. Você verá que ele é criado um novo Array chamado Fielding que se refere à guia de dados de campo A 1/3 G 12,056 que é exatamente o que eu acabei de selecionar. Vou te mostrar outra maneira de fazer isso. Então, vou apagar isto. Sim, feche esta caixa. E agora, em vez disso, a outra opção é começar na guia fórmulas, clicar neste botão de nome definido, dar-lhe um nome ou chamá-lo fielding novamente. Posso acrescentar um comentário, se quiser. Isso é sentir dados e, em seguida, clique no botão Seletor de dados e, em seguida, selecione exatamente mesma matriz que eu acabei de fazer. Pressione o botão novamente e tranque-o. Então, se eu voltar para o gerente de nomes, você pode ver exatamente a mesma coisa acontecer. Ele criou um novo nome DeRay chamado Fielding. Isso se refere a um 1/3 G 12 56 na guia de dados de campo. Hum, então lá vai você. É assim que você cria raça nomeada 3. FILas e COLUNa/: Tudo bem. Quero começar com algumas das funções de referência de pesquisa mais simples. Eso Nós vamos falar ro ros colunas coluna, que normalmente não são realmente usados por conta própria. Eles são normalmente usados como componentes de fórmulas maiores e mais complicadas, mas eles servem um propósito muito interessante e, em alguns casos, muito útil. A função de linha simplesmente retorna o número de estrada de uma determinada referência. Então, se eu disser qual é a fila de um cinco, ele iria retornar cinco porque vender um cinco vidas na quinta fila. Da mesma forma, a versão plural izada Rose apenas me diz o número de rosa total dentro de uma determinada matriz. Hum, este terceiro exemplo aqui em baixo, ele usa uma matriz definida pelo usuário, que vamos falar sobre, eu acredito, seção nove, seção nove, a seção de fórmula de matriz, hum, Colin e colunas essencialmente fazem exatamente a mesma coisa. Eles apenas retornaram o número da coluna de uma determinada referência ou o número total de colunas dentro de s qualquer matriz. Mais uma gorjeta aqui. Se você deixar a referência fora e apenas a linha direita com parênteses de fechamento aberto ou a coluna abrir fechar parênteses, ele irá retornar o número de linha ou coluna da célula na qual a fórmula é escrita. Então vamos saltar para o Excel e dar uma olhada nisso. Tenho aqui uma pasta de trabalho em branco só para me dar alguns dados. Vou fazer uma rand rápida entre 1 e 100. Aplique isso para baixo e para cima. Então eu tenho um pedaço de dados que pode acertar. Cópia, Faça meu Ault H V Atalho. E agora eu tenho uma matriz de dados aleatórios de 1 a 100 entre células, um 1 e C 12. Então vamos brincar com Roe e colunas funções um pouco. Estou no E um agora e tipo é igual a linha. Abra, feche, entre. Essa é uma porque eu digitei na primeira fila. E quando eu aplicar isso, você verá como isso apenas atualiza para retornar qualquer estrada em que a fórmula está atualmente residindo, uh, uh, o que mais fazer uma fórmula de coluna aqui. Abra, feche, entre e seque para a direita e verá a mesma coisa. Só sobe um a cada vez. Então isso é linha e coluna sem referência. Se eu quisesse testar o número de linha ou coluna de uma referência específica, eu poderia fazer isso para que eu pudesse dizer O que é Thea Row de seis retornou um seis ou poderia dizer, Qual é a coluna? Grupos de, Ah, um três retorno um porque está na coluna A em seguida, por último, mas não menos importante, eu poderia fazer igual a Rose de todo este conjunto. Aqui, você retornou 12 porque tem 12 linhas de altura ou eu poderia fazer colunas de toda a matriz. Ele irá retornar um três desde suas três colunas de largura. Eso de novo. Estes não são normalmente usados por conta própria, como você pode ver há um tipo de funções simplistas, mas como componentes para fórmulas mais interessantes, eles conservam um propósito realmente valioso. Então lá vai você. Colunas coluna Ro Ros. 4. VLOOKUP/HLOOKUP: Está bem. Quero começar falando sobre duas das funções de pesquisa mais comuns e poderosas no Excel. Nós olhamos para cima e h olhamos para cima. Estes ar geralmente usado quando você precisa combinar dois dados diferentes levantam com um campo comum . Então, por exemplo, se eu tiver dados de nível de produto em um array onde eu tenho quantidade e produto, eu d. E, em seguida, ter outro array de dados com os mesmos produtos e as informações de preço, e eu quero mesclar essas fontes de dados para que eu tenha quantidade I D e Preço tudo em uma planilha. O olhar para cima é uma ótima ferramenta para usar para fazer isso. Portanto, existem alguns componentes da função de pesquisa. Começa com um valor de olhar para cima. Então este é o valor que você está tentando corresponder seu campo normalmente ser comum que existe entre os dois apagar a origem e a matriz de pesquisa. O segundo componente é a matriz de pesquisa. Então é aqui que você está tentando encontrar esse valor de pesquisa. E a terceira parte é o número do índice da coluna. Então, em sua matriz de tabela, onde você está procurando o seu valor de pesquisa Qual coluna de dados você gostaria de retornar foi, isso é normalmente onde sua variável é que você está tentando puxar para sua primeira fonte um direito e, em seguida, por último, mas não menos importante, você tem um alcance. Olhe para cima, , o que basicamente diz que eles estão tentando igualar o valor exato da pesquisa ou algo parecido. Você está tentando procurar o valor exato que você usará para texto, strings e palavras. Você vai colocar um zero ou a palavra falsa lá. Caso contrário, você pode colocar um. Por exemplo, se você está procurando um número com um monte de casas decimais e você não precisa dele para corresponder , exatamente, você poderia usar isso. Mas só para você saber, 99% das vezes, você vai usar um zero ou exato como você arranja, olhar para cima. Então, neste exemplo aqui novamente, eu tenho esses dados de nível de produto que ele acabou de descrever e em um array tem nome do produto , quantidade e produto I D. E no meu segundo array, eu tenho produto e preço. Então, como você pode ver, produto é o campo comum entre eles, e o que eu quero fazer é encontrar o preço na minha matriz de pesquisa e puxá-lo para a minha fonte. Tudo bem, então na célula D para ter criado uma coluna de preço, eu vou dizer V olhar para cima. O que estiver em um dois, que acontece de ser a camiseta do produto dentro da minha mesa G 1 a H 5. Então ele vai linha por linha até encontrar o valor de pesquisa, que neste caso, ele está encontrando na linha cinco. E então ele quer retornar os dados da segunda coluna sobre. Uma vez que essa é a coluna no próximo número Então ele encontra camiseta se move para a segunda coluna sobre , encontra o valor de 14 99 e retorna na célula D para. E então, quando você aplicar essa mesa de olhar para cima para baixo, ele vai estar olhando para cima suéter do que shorts do que meias, tudo dentro deste conjunto olhar para cima. Então esse é Velicka H Look up funciona quase exatamente da mesma maneira. É realmente a mesma sintaxe. A única diferença é que você precisa usar um H Look up. Se você estiver olhando para cima, Array é transposto, que é uma maneira estranha de mostrar os dados, mas às vezes você vai correr através de conjuntos de dados que são construídos dessa maneira, então ele funciona da mesma maneira. A única diferença é que ele irá mover coluna por coluna até encontrar o seu valor de procura e, em seguida, mover para baixo para uma linha especificada no próximo número, vez de mover linha por linha e mover-se para uma coluna e próximo número para o V. Olhe para cima. Então, novamente, o mesmo conceito exato. Basta usá-lo quando seus dados são transpostos. Então, duas regras muito, muito importantes que você precisa lembrar quando você usa V olhar para cima ou h olhar para cima funções. A primeira é que seu valor de pesquisa deve estar na primeira coluna. Se você estiver usando um V olhar para cima ou a primeira linha. Se você estiver usando um H, procure sua tabela, uma chuva para que você não possa ter uma matriz de tabela que vai da coluna oito D, onde seus valores de pesquisa ou uma coluna correspondente está chamando Be. O Excel não gosta disso. A segunda regra é que o Excel sempre retornará o valor da linha mais alta. Se estiver fazendo um B, olhe para cima ou para a esquerda. A maioria das colunas. Se você estiver fazendo uma idade, procure uma matriz de tabela se várias instâncias do seu valor de pesquisa estiverem presentes. Então esta é uma distinção muito, muito importante para fazer aqui, que é que, uh, se eu voltar para o meu V, procure o exemplo. Por exemplo, em eu estou olhando para a camiseta Value neste olhar para cima array, digamos que duas linhas em uma fileira tinha camiseta como o produto com preços diferentes. O V olhar para cima vai parar na primeira vez que encontrar o valor está procurando neste caso camiseta e retornar o preço na segunda coluna sobre essa primeira instância, ele nem vai saber que houve várias instâncias porque ele pára no 1º 1 Então, a maneira de contornar isso é identificar uma chave que é comum tanto ao sentido de dados quanto exclusivo para cada linha. Essa é a peça chave aqui, então normalmente toma a forma de uma contaminação de vários campos. Vamos falar mais sobre esses detalhes na seção de função de texto, mas basicamente pode Cat Nation, que pode ser realizado apenas usando o sinal e comercial e excel, apenas mashes juntos para campos de texto em um hum, então vamos ver em nosso exemplo de beisebol que temos jogadores e anos e vamos criar uma chave comum baseada em ambos para usar corretamente a função de pesquisa V. Então isso é se você olhar para cima e h olhar para cima na próxima palestra. Vamos falar sobre realmente colocar isso em prática, mesclando nossos dados. 5. Juntando dados com o VLOOKUP: Tudo bem. Chega de falar sobre V. Olhe para cima em hte. Olhe para cima. Vamos realmente usá-lo. É muito difícil aprender essas fórmulas até que você as pratique em dados reais. Então vamos em frente e colocar algumas mãos na prática usando a função V Look up Abra a seção cinco. Procure Reference Excel Duck e vai trabalhar fora de lá para recapitular Got my batendo guia dados, que é organizado por jogador por ano com um número de métricas bater da coluna C até fim e, em seguida, da mesma forma ter uma guia de dados sentimento por jogador por ano, com métricas de campo adicionais aqui, o objetivo deste exercício será puxar essas métricas de dados de campo para colunas. Oh, através de s na minha aba de dados de batida. Então isso vai ser como a minha guia fonte ou dados mestre que une esses dois conjuntos de métricas juntos em um só lugar. Então o que não posso fazer é começar a escrever sobre você. Procurar função no jogador chamado, por exemplo, como o meu valor de pesquisa porque eu tenho os dados por ano também. Então, se eu olhasse para o nome do jogador na matriz de dados de campo. Ele vai encontrar a primeira instância desse jogador, você sabe, você sabe, se você jogou em 2010, por exemplo, mas ignorar quaisquer instâncias posteriores. Então, se eu tiver um jogador que existe no conjunto de dados cinco vezes porque ele jogou todos os cinco anos dos dados, então essa função de pesquisa V com base no nome Leon Player vai perder quatro dessas instâncias ele vai encontrar o 1º 1 está indo para retornar os valores associados e ignorar os outros. Então o primeiro passo que precisamos fazer é criar uma coluna chave. Então, ao bater dados em um clique direito e inserir uma coluna à esquerda da coluna, uma chave chamá-lo e para definir esta função ou esta nova dimensão destruída é igual a B dois comercial e C dois. Isso é chamado de uma função Can Katyn oito, e ele apenas mescla essas strings de texto juntos para criar um campo novo e exclusivo. Então há duas coisas que são importantes aqui, uma é única. Há apenas uma instância de cada um desses valores em toda essa coluna, e para ela está na primeira coluna A. Isso é muito importante. Se você se lembrar da primeira regra das funções de pesquisa. Então eu vou pular para sentir dados e fazer exatamente a mesma coisa. Nova coluna. Uma chave de faculdade. Vai ser igual a ser comercial e C dois quieto. E agora eu tenho a minha chave comum única entre as duas guias que eu posso usar a partir de eu olhar para cima Então o próximo passo que eu gosto de fazer é apenas dizer o nosso que vamos saltar para os meus dados de pesquisa e encontrar quais campos ou quais variáveis eu estou olhando para puxar para a minha folha mestre. E vão ser estes cinco. Então eu vou apenas acompanhá-los. Aqui está espaços reservados e é aqui que todas as minhas funções de olhar para cima vão viver. Então vamos começar com a posição. , Colin P,vou começar a escrever. Cada olhar para cima é igual a V. Olhe para cima, abra o parêntese. Eu procuro valor vai ser qualquer que seja a minha chave aqui. Então um para eu procurar matriz tabela, Onde estou tentando encontrar este jogador? O nome chave está nos dados de campo de um todo o caminho e todo o caminho até H 12.000 e 56 antes de eu fazer mais alguma coisa? Vou pressionar F4, que conserta todo o conjunto no lugar para que eu possa aplicar essa fórmula de busca para baixo. Uma vez que eu escrevi e não ter essa matriz de dados mudar junto com ele, não é um número de índice de coluna vírgula imprensa. É basicamente a coluna onde minha posição se sente viva, porque essa é a métrica que eu estou tentando preencher agora, que está na coluna 1234 Então coluna índice 4 e então eu vou apenas fazer zero para correspondência exata . Feche a ocorrência de parênteses. Entrar. Então, agora, se eu aplicar isso todo o caminho para baixo, como você pode ver, ele está procurando o nome de cada jogador. Se eu pressionar f para mergulhar ainda, a matriz de tabela ou olhar para cima matriz não está mudando. Então lá vai você. Essa é a posição agora. Um outro truque que eu uso para poupar tempo. Você vai notar que se eu apenas tentar arrastar esta fórmula para a direita, eu vou receber um erro. E a razão é que se você olhar para como a fórmula muda, observe o valor de pesquisa primeiro. É um para ele muda para ser, também, porque era uma matriz completamente relativa. Então eu mudei para uma coluna à direita. Uma mudança para seria. Mas neste caso, não importa qual coluna eu estou tentando puxar com uma função de olhar para cima. Sempre vou procurar o mesmo campo chave. Então o A nunca vai mudar de em um Então o que eu vou fazer é pressionar F 43 vezes para apenas corrigir a coluna, mas deixar a linha relativa Assim eu posso aplicar isso de novo. A Rose deixará oito, que é o que eu quero. Mas agora, se eu mudar, fica um dois, que é o que eu quero. Agora você vai notar que é exatamente a mesma função porque nada está mudando para minhas referências. Neste ponto, tudo o que eu preciso fazer é mudar o número do índice da coluna porque lembre-se, posição estava puxando o valor na quarta coluna colocar para fora é na quinta coluna. Então eu só preciso mudar o 4 para 5. E lá vai você, cumpra isso. Mesma coisa. Eu poderia arrastar isso. Mude o cinco para um seis para puxar em assistências, que vivem na sexta coluna sobre E então eu vou fazer a mesma coisa para erros e jogadas duplas , que vivem nas colunas sete e oito, respectivamente. E então pegue todos os três. Clique duas vezes nesse canto para aplicá-los. Então lá vai você. Eu escrevi uma função de pesquisa para puxar meus dados de campo em uma guia fonte mestre. 6. Como corrigir erros com o IFERROR e VLOOKUP: Ok, então eu mostrei a você como usar uma instrução de erro if de volta na seção do operador lógico. Quero revisitar isso e mostrar como funciona exatamente da mesma maneira. Não importa qual fórmula usando neste caso, vamos aplicá-lo para ser função de pesquisa que já escrevemos. Mas é importante revisitá-lo também, porque ele se torna cada vez mais relevante e útil à medida que você começa a aplicá-lo para procurar em funções de referência especificamente. Por exemplo, se você não tiver uma correspondência exata de 1 a 1 entre seus dados e sua tabela de origem e sua tabela de pesquisa, isso irá gerar um N um valor e, neste caso, com os dados que estamos usando, Eu sei de fato que nós vamos conduzir erros porque há jogadores que aparecem na guia de dados batendo, mas não aparecerão na guia de dados de campo porque eles só acertam, por exemplo. Então, quando a função de pesquisa V tenta encontrar aquele jogador na matriz de pesquisa, que está em nossa tentativa de data de campo, ele vai aparecer de mãos vazias e retornar em N A. Então vamos ver se isso está realmente acontecendo, primeiro O que eu preciso fazer é aplicar filtros para o resto das colunas que acabamos criar. Selecione a linha um indo para a guia de dados desmarque o filtro e, em seguida, verifique-o novamente. Isso só vai reaplicar os filtros até o Conte. E agora, se eu olhar para qualquer uma das colunas foram escritas estas nós procuramos funções. Então P Q R, S t Eu vou apenas usar o menu suspenso e ver que existem, fato, en um erro sendo preenchido. Então o que eu vou fazer, assim como eu fiz na última seção, é que eu vou para a barra de fórmulas, clique logo após os iguais e direito e se declaração de erro. Então, se o erro for aberto, o valor do parêntese será a função inteira que acabei de escrever pulou a vírgula de imprensa final . E neste caso para o campo de posição, já que o jogador realmente não tem uma posição, se ele está apenas no conjunto de dados de batida, eu vou chamá-lo de outro por enquanto. Feche-o, pressione enter e, em seguida, aplique essa fórmula para baixo. E agora quando eu re filtro, você pode ver que o mudou para outro valor não para as estatísticas reais e colunas Q, R, R, S e T. Este é o lugar onde a declaração caso assume um ainda mais importante função. Então agora eu estou recebendo quaisquer valores em todas essas colunas. E o problema com isso é que se eu tentar selecionar a coluna inteira para ver o sol destaca a ser jogado fora porque ele está vendo essas mensagens de erro e está impedindo Excel de somar ou fazer qualquer outra operação estatística no valores legítimos. Assim, você pode comparar quando você seleciona. Você sabe, uma coluna de dados como M ou N, onde você está vendo contagem média, e alguns em comparação com nossos campos calculados, que você só está mostrando contagens porque eles têm esses erros construídos lá jogando eles fora. Então o que eu vou fazer é de novo, vou escrever em uma declaração justa, pular para o fim, e eu vou pegar zero. Eu não vou usar aspas, então isso vai inserir o valor numérico real de zero nos casos em que você está lá. Então, quando eu aplicá-lo para baixo, selecione a coluna inteira que você poderia ver agora se destaca, capaz de calcular uma contagem média e uma soma que me permite fazer qualquer tipo de operação estatística que eu quiser, apenas como se fosse qualquer outro campo numérico para que eu possa puxá-lo para uma tabela dinâmica. Posso executar estatísticas, funções nele, o que eu quiser. Então eu vou fazer exatamente a mesma coisa para as próximas três colunas. Há duas maneiras de fazer isso. Posso adicionar erros a cada um, que estou fazendo agora, ou pode adicionar o caso a este, aplicá-lo e depois mudar a coluna. Índice número 27 e dois e oito, assim como fizemos, apenas criou a função primeiro. Cabe a você, o que quer que esteja mais confortável. Mas ambas as abordagens funcionam. Então agora, como você pode ver, se eu selecionar a coluna R s ou chá, eu estou recebendo a contagem média em alguns. E se eu olhar para baixo no filtro que qualquer valor desapareceu e foi substituído por zero então novamente, se eles são realmente, muito importantes, especialmente quando se trata de procurar funções 7. Opções de referência em VLOOKUP: Tudo bem. Então você tem um belo V olhar para cima fórmula escrito. Está preenchendo os dados corretamente. Nós até envolvemos em uma declaração justa para levar em conta os valores que faltam. Está tudo bem. Eu quero falar sobre um outro ajuste que podemos fazer para a matriz de tabela, Hum, e duas outras abordagens que podemos usar que podem ser benéficas em certas situações. Por isso, neste momento, escrevemos o nosso olhar para cima com uma matriz fixa de mesa. Então agora, o olhar para cima está procurando o valor de pesquisa dentro da matriz de um a H 12.056 exatamente essa matriz. Há duas outras abordagens que poderíamos tomar. O primeiro é usar em Array nomeado. Então, se você se lembra de Ah, em uma das primeiras palestras nesta seção, criamos uma matriz nomeada com base em nossos dados de campo. Mas se você se lembra de quando a criamos, ainda não tínhamos definido essa coluna chave. Hum, então se você realmente selecionar ser um, chamá-lo h e então mudar de controle para baixo. Aí está a minha matriz de campo. Então, basicamente mudou minha matriz nomeada sobre, Hum, e neste caso, meu olhar para cima. matriz precisa incluir a coluna A porque é onde meus valores de pesquisa vão viver. Então, o que eu posso fazer é ir para a guia fórmulas, Selecionar nome Manager. Aqui está a minha matriz de campo que tínhamos criado. Vou editar isso. Tudo o que vou fazer é mudar o B 1. Exclua o ser mudando para uma ocorrência. Ok, quando eu fechei isso agora, se eu selecionar uma mudança de controle sempre seta para a direita para baixo, você pode ver que ele é definido como meu campo chamado Array. Então, agora que fizemos esse pequeno ajuste, eu posso voltar para o olhar para cima e apenas saltar para a mesa ou um pedaço da minha fórmula, excluir o todo eu acho, e apenas o fielding direito. E como você pode ver, ele aplicará todos os mesmos valores. Eu poderia fazer a mesma coisa aqui de novo. Eu vou excluir toda a matriz de tabela, começando com os dados de campo nome da guia seguido pelo ponto de exclamação para deixar tudo isso e apenas campo direito. E novamente, Excel reconhecerá isso como uma referência nomeada, e ele saberá exatamente qual referência que se refere e preencherá os erros corretos. Então, eu só estou passando por colunas. Q R S T. E apenas fazendo a mesma atualização aqui em todos os casos, note que eu não estou colocando fielding e aspas ou qualquer coisa porque não é uma string de texto. É uma referência a um Array chamado e, como você pode ver, X Ellis aparecendo esta pequena caixa porque como eu começo a digitar, fielding sua pesquisa através do gerenciador de nomes e vendo se eu criei algum raise nomeado com isso nome. E então, na verdade, ele descobre que é algo que você acabou de dar. Um clique irá soltá-lo, e estamos prontos para ir para a frente e aplicar todas as alterações que acabei de fazer para baixo. E quando este cálculo terminar, você verá que ele está em campo em todos os casos para que você possa ver que é um pouco mais compacto, um pouco de fórmula mais limpa. O problema que eu tenho com nomeado um aumento é que um eu preciso lembrar que criaram este nome para ele e usar o gerenciador de nomes para verificar exatamente o que vende que chamado Array se refere. A segunda é que está consertado. Então, se eu precisar modificar ou adicionar dados, eu vou precisar editar a referência de campo para ter em conta isso. Então, uma última opção para a matriz de tabela, que eu realmente prefiro e eu uso um pouco, é ao invés de usar e nomeado array sem nome. E ao invés de usar um array fixo, específico como um de um a H, o que quer que ele waas, o que eu vou fazer é selecionar dados de campo. Note que estou na mesa ou um pedaço da minha fórmula agora. Selecione os dados de campo. Basta clicar. Toda a coluna pode manter pressionada a tecla shift e toda a coluna H e pressione enter. Aplique isso para baixo. Então o mesmo resultado exato que você pode ver agora que está olhando para cima. Basicamente todas as linhas e colunas A a h não apenas para baixo para 12,056. E o benefício de fazer isso é que agora, se eu receber um novo ano adicional de dados ou se eu atualizar os dados na minha guia de dados de campo, eu não terei que tocar nessas pesquisas v. Eu quero tocar na referência e eu quero tocar no gerente de nomes porque cada vez que ele executa a pesquisa, ele vai procurar cada linha preenchida da coluna A a H s. Então é uma maneira mais eficaz e eficiente de usar. Procurar funções quando você tem uma tabela de referência de pesquisa dinamicamente constantemente mudando ou atualizando . Então essa é a minha pequena gorjeta. Eu uso um pouco. Vou mostrar-lhe exatamente a mesma coisa para as últimas quatro colunas só para que você se sinta confortável com ele até H. E mais uma vez, isso parece me poupar um pouco de tempo. Eso que eu não tenho que lembrar. Você sabe, todas essas nuances sobre como, se definido, certos nomearam um aumento ou exatamente o quê? Minha tabela de pesquisa, uh, está atualmente definida como em termos de referência de célula. Sempre sei que não importa o que aconteça, minhas pesquisas funcionarão corretamente. Então forneça esses para baixo, você sabe, tomar um minuto para calcular. E lá vai você. Então você vê que nada mudou na superfície porque todos os três métodos de escrever esta fórmula que mostraram que você faz exatamente a mesma coisa, é que todos eles têm benefícios e desvantagens muito específicos. Então, lá vai você. Isto é o que eu recomendaria usar apenas referências de coluna total em oposição ao nome para levantar ou fixar uma corrida para que você vá agora temos um realmente, muito eficiente, ser olhar para cima função 8. Função de INDEX: a próxima função de referência olhar para cima que eu quero compartilhar com você é chamado Index e antes mesmo de saltar para dentro, apenas me ouvir. Sei que vai parecer uma daquelas funções onde você lê. Você vê o que ele faz, e imediatamente você pensa em si mesmo. , Chris, por que vou precisar usar a função de índice? O que ele faz é tão simples que não há maneiras mais fáceis de fazê-lo. Hum, e você está certo. O fato é, assim como linha e coluna. É uma daquelas funções que ninguém usa sozinho. Por essa razão, é tão simplista. Por essa razão, Mas uma vez que você começar a combiná-lo e usar Index como um componente de outras fórmulas, você pode abrir a porta para começar a fazer alguma coisa muito legal para que a promessa vai valer a pena . Apenas me escute. Assim, a função de índice por si só. Tudo o que faz é retornar o valor de uma célula específica dentro de uma matriz. Então você só está dizendo que não é, Excel? Aqui está esta matriz de dados ou matriz de células. Eu quero que você vá para a linha número cinco e coluna número três e me diga o valor que você recebe. É isso. Então, sintaxe sábio, a fórmula de índice tem três componentes. A corrida. Então, que intervalo você está olhando para o número da linha, que é quantas linhas para baixo você quer ir? E o número da coluna, que é quantas colunas você quer mover? Então, neste caso aqui, você vai começar a partir do canto superior esquerdo de qualquer matriz que você especificar, e, em seguida, mover para baixo para o número da estrada e mais de dois números de coluna, e Excel irá dizer-lhe o que ele encontra nisso. Então, aqui. Estamos dizendo, vamos indexar a matriz de um a C cinco. Vamos descer para a quinta linha e passar para a terceira coluna. E quando você faz isso, ele retorna 234 porque esse é o valor que ele encontrou na célula em que aterrou. Mais uma vez, muito, muito simples. Confie em mim, isso fará mais sentido em um minuto, então segure seus chapéus 9. Função de correspondência: Muito bem, em seguida, temos a função de correspondência, que, assim como o Index, é muito simples que você não deve usá-lo por conta própria. Mas é um componente para fórmulas aninhadas mais complicadas. Assim, o tipo de função de correspondência de funciona na maneira oposta de índice retorna a posição de um valor específico dentro de uma coluna ou linha. Então, em inglês simples, basicamente, você está dizendo, tudo bem, Excel. Estou procurando esse valor, seja uma palavra ou número ou qualquer outra coisa, dentro desta coluna ou dentro desta linha, e ele retornará a posição desse valor. Então, hum, três componentes a função de correspondência. Você tem o seu valor de pesquisa, que é o valor. Estamos a tentar encontrar a posição da sua matriz de procura, que é para onde está a procurar? E note que tem que ser uma matriz unidimensional. Vou lhe dizer por que, em apenas um segundo, o terceiro componente é o tipo de correspondência. Então, se você está procurando o valor exato, que você será 99% do tempo, basta colocar zero. Se você está procurando algo semelhante, você pode usá-lo um ou negativo. Aqui está 02 exemplos aqui no primeiro caso estavam combinando a palavra alicate na coluna A eso. Ele está olhando através da coluna A começando no topo e trabalhando seu caminho para baixo até encontrar que olhar para cima alicate valor e retornar. Quatro porque o encontrou na quarta fila abaixo. Neste caso, estamos dizendo para combinar com o número 66 dentro da linha três. Então um três lá c três. Então o Excel começa na esquerda, move-se para a direita até encontrar um olhar para o Valor 66 retorna três porque ele encontrou na terceira coluna sobre Agora, lembre-se, lembre-se, eu mencionei que tem que ser uma matriz unidimensional. A razão pela qual esse é o caso é que se dissemos, você sabe, coincidir com o número 2.5 dentro da matriz de um a Be cinco, como o Excel nos diria a posição do número 2.5, neste caso? $2,50 dentro de uma matriz tridimensional? Certo. Não há nenhum número único que irá comunicar a posição desse valor, é por isso que você tem que dizer, olhar dentro de uma única coluna ou uma única linha e Excel irá dizer-lhe o número da estrada ou o número da coluna em que o valor foi encontrado. Então isso é igual na próxima palestra. Vou falar sobre como índice e correspondência podem ser combinados para fazer algumas coisas épicas. 10. Como usar o INDEX e combinação: Certo, conversamos sobre o Index. Falamos sobre fósforos. Agora é hora de falar sobre como combiná-los para fazer algumas coisas realmente legais. Na verdade, quando você combiná-los, eles agem quase como uma função de olhar para cima, mas em alguns casos com ainda mais flexibilidade. Então eles confinaram valores em qualquer coluna ou qualquer linha dentro de uma determinada matriz. Então vamos dar uma olhada em um exemplo. É um pouco complicado envolver sua cabeça no início, mas uma vez que você dividi-la em suas peças ou componentes individuais, você verá que na verdade faz um pouco de sentido. Então o que vamos fazer é escrever uma função de índice, mas vamos aninhar funções de correspondência dentro dela. Então, neste caso, estamos dizendo nosso Excel. Vamos encontrar um preço específico dentro deste pequeno array de preço de B a D quatro. Então, vai dizer indexar que array seja 34. Mas ao invés de apenas dizer, você sabe, mover para a terceira linha para baixo e a segunda coluna sobre a qual está dando direções muito fixas , eu quero torná-lo dinâmico. Eu quero que o usuário seja capaz de inserir. Algum valor que determinará em que célula aterrissaremos dentro dessa matriz. E é exatamente para isso que as funções de correspondência são usadas. Então, se você lembrar que a primeira parte da função índice é teoria, que é B para 34 a segunda peça é o número da estrada. Então, quantas linhas para baixo em um movimento? Neste caso, o número de linhas para baixo que queremos mover depende de quais produtos você se preocupa com suéter, jaqueta ou calças. Então o que podemos fazer é criar uma função de correspondência que diz, Ok, vamos deixar o usuário inserir qualquer produto que eles estão procurando no Selby seis e vamos dizer, tudo bem, combinar esse produto dentro do intervalo de um dois a um quatro. E, em seguida, a resposta irá dizer-lhe quantas linhas para baixo índice e, em seguida, permitir que o usuário para selecionar o tamanho que ele está interessado em. Pequeno, médio ou grande. E quando você insere isso em uma função de correspondência baseada na matriz de B um a D um , que irá dizer-lhe quantas colunas mais para mover para que pareça uma espécie de besta de uma função. Mas quando você considera cada fórmula de partida individual tipo de partida de dentro para fora e pensar sobre qual é a saída de cada uma dessas funções de correspondência. Você acaba com apenas um índice muito simples. Então, neste caso, se você selecionou calças e médias, isso só vai salvar o Índice B para 34. Vá até a terceira Roma e a Segunda Coluna e devolva o número 30 porque uma calça média dos pais custa 30 dólares. Então vamos saltar para o Excel e realmente escrever uma dessas coisas do zero. Exatamente o mesmo conceito. Aqui. Temos uma ferramenta hipotética de verificação de preços. Neste caso, temos cinco opções de tamanho diferentes e cinco opções de produto diferentes, e nosso objetivo será preencher esta célula aqui com o preço que estou usando uma função de índice com componentes de correspondência para puxar um determinado ou específico preço fora deste array. A primeira coisa que precisamos fazer é adicionar alguma validação de dados, porque eu quero que os anos serão capazes derrubar as opções para produtos e as opções para tamanhos em vez de ter um digitá-lo em. Então eu vou apenas selecionar ser 10 indo para validação de dados permitir uma lista. E essa lista vai ser apenas a lista de produtos que você pode escolher. Então como, OK, como você pode ver, isso funcionou. Mesma coisa com Validação de dados C 10. Permitir uma lista. E a fonte para essa lista vai ser todos os tamanhos que você pode escolher neste caso, aqueles que vivem e vêem 2 32 Então eu vou usar essa lista, OK? E agora eu sou capaz de selecionar qualquer combinação de produtos e tamanhos, então esse é o primeiro passo. O segundo passo é escrever a função de correspondência de índice. Então vamos começar com índice igual. E que raro re indexação vai ser o conjunto total de preços que podemos escolher . E uma vez que esta fórmula só vai viver nesta célula, eu posso pressionar que quatro para corrigir isso ou pode deixá-lo relativo realmente não importa neste caso, mas agora eu vou vírgula mais agora, excelência procurando o número da estrada. Então, quantas filas para baixo? E mais uma vez, não faz sentido. Basta dizer ao Excel, você sabe, Linha 123 ou quatro porque é uma função do produto que o usuário selecionou. Eu sou então eu vou inserir uma correspondência, função aqui, digamos, corresponder ao valor que o usuário é colocado em Selby 10. E onde vou procurá-la? Na lista de produtos de B três a B sete. Para que agora eu tenha meias selecionadas. Isto irá produzir o número um porque é o primeiro na matriz. Está na primeira linha dentro dessa matriz. E assim a função de índice permanecerá na linha um, que é o que precisamos. Por último, mas não menos importante, tipo de correspondência. Eu vou fazer zero, porque eu quero uma correspondência exata aqui. Feche isso fora em que a função de correspondência é feito. Vírgula acabou. Agora eu estou no componente de número de coluna da minha fórmula de índice, então eu vou apenas inserir outra função de correspondência. Agora vamos procurar o tamanho que o usuário se preocupa ou o tamanho que o usuário selecionou. Venha até a matriz de pesquisa, que será a lista de tamanhos de C a G dois e, novamente, correspondência exata para o tipo de correspondência. Venha até a matriz de pesquisa, que será a lista de tamanhos de C a G dois e, novamente, correspondência exata para o tipo de correspondência Feche isso para terminar a função de correspondência e, em seguida, mais um parêntese para fechar a função de índice, e isso deve fazê-lo. Então eu recebo por 25 agora. Ter meias pequenas selecionadas, o que parece certo, e eu poderia apenas, você sabe, papai arredonda valores diferentes para ter certeza que está funcionando calças pequenas. 25. Lá vamos nós. Calças extra grandes ou 28 camisetas extra grandes. 17. Lá vai você índice de correspondência. 11. Combinando MATCH o with: Então eu quero falar sobre mais uma aplicação da função match que pode ser incrivelmente útil para economizar tempo e trabalhar de forma mais eficiente com funções de pesquisa. Então vamos voltar para a fórmula de pesquisa que escrevemos nas palestras anteriores. Então eu estou na aba de dados de batida. Estou olhando para a célula P 2 e sei que você provavelmente está pensando, o que mais poderíamos fazer com essa função? Trabalhamos em tipos de referência. Nós mudamos diferentes matrizes de tabela. Nós embrulhamo-lo e se erro. Mas há uma última peça que podemos automatizar para economizar muito tempo no futuro, especialmente se vamos replicar essa função e vários lugares. Então a peça a que estou me referindo é a coluna em Next Number ou o número quatro. Se você se lembra quando nós escrevemos pela primeira vez este Siris de funções de pesquisa, nós aplicamos a primeira função que escrevemos sobre as cinco colunas à direita. Então nós aplicamos a fórmula de Colin P para Q R S e T, e a única coisa que tivemos que mudar o dedo atualizar essas funções foram os números de índice de coluna . Então mudamos o 4 para 55 para um 66 para 7, e assim por diante e assim por diante. Podemos fazer ainda melhor do que isso. E podemos substituir este número de índice de coluna por uma função de correspondência que fará a mesma coisa automaticamente. Então o que eu vou fazer é apagar os quatro aqui e eu vou escrever função match. Então, combinam. E o que procurar avaliador procurando? Vai ser o nome da variável ou o cabeçalho da coluna variável que eu estou procurando neste caso Pos ou posição. E onde vou tentar achar isso na cabeça? Uma linha da minha matriz de pesquisa, que está na guia de dados de campo. Então, vai ser o campo de dados de um a um. E, em seguida, lembre-se, a terceira peça da função de correspondência é o tipo de correspondência será zero e fechou a função de correspondência desligada. Então agora o que eu estou fazendo é, em vez de apenas dizer, me dê o valor da quarta coluna, eu estou dizendo para me dar o valor de qualquer chamada para ele em que você encontrar P.O.S P.O.S no hétero, que, neste caso, retornaria o número quatro, tal como tivemos. Então, quando eu apertei Enter, você vê, ele retorna p para arremessador. Tudo parece bom. Mas antes de aplicar isso para baixo e mais de duas colunas adicionais, eu tenho um pouco mais de trabalho para fazer em termos de meus tipos de referência e configurá-los corretamente. Então, a fim de aplicar esta função para baixo sem estragar nada, eu preciso ter certeza de que as referências na minha fórmula de correspondência estão corretamente definidas. Então, agora, eu só disse tudo como relativo. Mas o que precisamos fazer é deixar o P um relativo do que se eu arrastar esta fórmula para baixo uma linha, ele vai tentar combinar qualquer valor em P 2, que é absurdo. Então eu preciso percorrer f quatro. Quero dizer, acertei duas vezes até eu consertar a estrada. Agora, quando eu arrastar isso para baixo, ele vai sempre puxar o que quer que o rótulo do cabeçalho esteja na primeira linha, para que o número da linha nunca mude. Da mesma forma, independentemente de qual variável ou qual cabeçalho eu estou procurando, aqueles sempre viverão em um A um a h um. Então eu vou consertar isso completamente. E, em seguida, a última peça que eu preciso fazer aqui é fixada a referência A a H na tabela de pesquisa. Dessa forma eu posso arrastar esta fórmula para a direita sem o oitavo ou H mudando através algo fixo de A a H, e isso deve apenas fazê-lo. Então bata. Entrar. Teste isso aplicando-o para baixo. Tudo preenche corretamente. Isso é ótimo. E agora aqui está a chave. Quando eu aplicar isso, eu não deveria ter que mudar nada. Tudo deve ser atualizado corretamente, o que acontece agora. O que eu vou fazer é pegar esses quatro, aplicá-los para baixo. É calculista. Lá vai você. Então, como você pode ver, a única coisa que está mudando agora na minha fórmula de pesquisa é o valor de pesquisa na minha função de correspondência . Então, neste caso, está olhando para cima. O nome do cabeçalho put outs na matriz Fielding Data, que está na quinta coluna sobre neste caso, ele está olhando para cima são um que é o nome do cabeçalho assists, que está na sexta coluna sobre e assim por diante e assim por diante e assim por diante. Então ele está fazendo exatamente a mesma coisa que nosso número de coluna original, exceto que agora ele é completamente automatizado, então podemos escrever uma função de pesquisa e aplicá-la em um número de colunas e um número de linhas sem ter que tocar em nada. 12. UPDATE: atualização: VLOOKUP: Então, alguns meses depois da publicação, este estudante do curso me enviou uma mensagem e apontou que houve um erro na seção de exercícios de pesquisa, e ele está absolutamente certo, e eu vou mostrar a vocês o que está acontecendo aqui. Se você entrar na guia de dados de campo na pasta de trabalho de referência de pesquisa, você verá que existem várias instâncias de certas chaves de jogador, e a razão disso acontece é porque certos jogadores podem ter jogado em vários posições mesmo no mesmo ano. Então, por exemplo, Bobby, um valente em Rose quatro a seis, está registrado como um defensor esquerdo, um defensor externo e um defensor direito, todos gerando a mesma chave porque a posição não é incluído. E o que isso significa é que nossa função de olhar para cima está procurando por suas idéias de jogador. Tecla do jogador, parando na linha e retornando os apagões, erros de assistência e jogadas duplas associadas à primeira instância da chave, que não retorna dados. E se saltarmos para a conta, Bobby, um valente, está aqui na fileira quatro, e como você pode ver, ele tem zeros em toda a linha, mesmo que naquele ano em 2010 ele tivesse 240 para colocar sete assistências, seis erros em duas jogadas duplas. Esses dados são invisíveis para o V. Olhe para cima porque agora estamos lidando com uma relação de um para muitos. Temos uma chave em nossos dados de acerto, e três chaves aqui são dados de campo. Então, como lidamos com isso? Uma opção seria realmente transformar este conjunto de dados de campo e agregado no jogador em seu nível. Em outras palavras, remova a posição, campo e o agregado ou algumas métricas e colunas e até H. Mas há uma maneira mais fácil, e é realmente usar a função alguns ifs que cobrimos na fórmula de estatísticas do curso. Então vamos voltar para a aba de bater. Eu vou copiar os cabeçalhos de coluna e bonito 30 colá-los começando e coluna você. Vou dar-lhe uma cor diferente só para que eu possa manter as minhas funções de olhar para cima aqui e compará-las lado a lado com esta nova abordagem, usando alguns “ses”. Então vou começar digitando o sol. Esta fórmula é igual a alguns “ses”. Agora, se você se lembra da primeira peça, o primeiro componente da função de cúpulas é o intervalo de soma Quais são os dados que você está tentando? Na verdade, alguns aqui. Neste caso, são os meus dados de campo. Colin E. que é colocado para fora vai deixar aquele parente de referência, e eu vou te mostrar o porquê em um minuto. O segundo componente é o intervalo de critérios. Então, onde estou procurando atender a alguns critérios? Bem, é uma chave de jogador, que é a coluna A Eu vou pressionar F quatro para bloquear a coluna e, em seguida, o critério da última peça . Um. Que critérios o chama de necessidade de atender? E isso vai voltar para o meu acerto Data Player Key, que é e vender oito para. Neste caso, eu vou corrigir apenas o A apenas a referência da coluna para que a minha rosa concisa para baixo como eu aplicar esta fórmula para baixo. Então aí você tem, uma pressione enter e aplique-o para baixo. Agora que é calculado, você verá um monte de valores correspondentes com algumas exceções, Então uma exceção é Bobby, um valente na fileira quatro, onde estávamos gravando zero colocar outs com o V. Olhe para cima agora nós 're gravação 242 porque estamos essencialmente somando todos os valores na coluna e onde a coluna é igual a corajoso B 0012010 dos quais existem três instâncias um caso semelhante com o próximo jogador. Havia três exemplos de seu nome também. No caso dele, ele gerou 16 lançamentos na segunda base, 11 na terceira base e 16 no shortstop, que totaliza 43 lançamentos Com a abordagem de pesquisa V, capturamos 16 que é apenas a primeira instância. Mas agora que mudamos para uma aproximação de cimeiras, estamos capturando todos esses apagamentos, todos os 43. E porque eu defini meus tipos de referência corretamente, o que eu posso fazer agora é até mesmo mudar isso para as colunas restantes. Desde que eu sei que essas colunas são sequenciais e isso me permite basicamente apenas permitir que essa coluna coma uma mudança para F, G e H, e eu poderia apenas pegar essas células DoubleClick e aplicá-las para baixo. Então, lá temos. Nós explicamos o fato de que não tínhamos mais um relacionamento de 1 para 1 com nossa chave, e nós alcançamos os resultados que estamos procurando usando uma função estatística em vez disso . Agora, a única advertência que farei é que esse trabalho, porque estamos tentando retornar valores numéricos aqui, que alguns “se” podem entender. Se, em vez disso, estamos tentando retornar posição, por exemplo, que é um campo de texto. Não há nenhuma maneira que eu poderia ter usado alguns ses para conseguir isso, porque o Excel não sabe como adicionar P e ser um L f ou qualquer outra string de texto. Então, novamente, se você estiver trabalhando com valores, às vezes você pode usar o olhar para cima ou alguns ses de forma intercambiável. Outras vezes você precisará usar apenas um ou outro. 13. A função OFSET: Certo, então a última função de referência de pesquisa que eu quero falar é chamada offset tipo uma função menos conhecida. Mas é muito útil quando se trata de fazer algum tipo de coisas únicas e poderosas que lhe mostrarão na próxima palestra. Mas basicamente só para lhe dar algum fundo, é semelhante ao índice. Ele retorna o valor de uma célula dentro de uma matriz, mas também pode retornar uma referência a um intervalo específico de células, que pode ser muito útil quando você deseja criar aumento dinâmico. E eu sei que isso soa como um disparate. Fará mais sentido na próxima palestra prometida. Então, basicamente, compensação sábia imposto pecado começa apenas como uma função de índice. Você determina uma referência, qual é o seu ponto de partida? Normalmente, isso é uma única referência de célula, e então você tem um componente rosa, que diz ok, quantas linhas para baixo devo mover? E, em seguida, colunas, que é quantas colunas sobre a referência eu devo seguir em frente? E se você deixar assim, ele funcionará exatamente como um índice. Ele lhe dará o valor de qualquer venda em que cair. O que torna o deslocamento diferente é que ele inclui esses dois componentes adicionais altura e largura, uh, uh, que permite que você retorne uma matriz multidimensional em vez de apenas o valor dentro de uma única célula. Então estes são opcionais, e é por isso que eles são mostrados cercados por colchetes. E se você deixar esses em branco, se você apenas fechar sua fórmula após o indicador de colunas, uh , novamente, será apenas como um índice na altura e largura irá padrão dos dedos um e um, então você retorna o valor de uma célula que é uma linha de altura e uma coluna de largura, dica profissional rápida Aqui. Eu estou mostrando todas essas funções como Offset Index Match, que têm muito específico em e em alguns casos, aplicações muito particulares. Não fique muito preso em usá-los só por causa de usá-los. Portanto, não use um deslocamento ou na próxima partida, quando um simples olhar em V fará o truque. Então, com isso, é assim que a função offset funciona. Vou mostrar-lhe como ele pode ser usado para construir uma rolagem dinâmica realmente legal no gráfico na próxima palestra, então prepare-se 14. Combinando OFSET com COde: Então, antes de mergulharmos no exemplo do gráfico de esquilos, que é muito, muito legal, eu prometo. Eu quero mostrar uma outra aplicação muito comum da função offset, que é combiná-la com uma função de estatísticas simples chamada Count A. Agora há um exemplo disso no exercício de lição de casa no final do capítulo, mas eu quero mostrar-lhe um exemplo muito básico apenas para dar-lhe uma noção do que se trata . Então, nesta pasta de trabalho que eu estou olhando aqui, obviamente é extremamente complicado. Eu tenho seis itens em uma lista na coluna A O que eu posso fazer é usar a contagem de um tipo de função igual a dia de contagem para contar os valores não em branco ou células dentro de um intervalo. Neste caso, meus intervalos, toda a coluna A. Quando eu fechar isso fora, ele vai retornar um seis desde seus seis valores não em branco bem aqui e chamá-lo de um agora, o que isso tem a ver com offset? E se quiséssemos devolver o último item da lista? Não importa quanto tempo essa lista é? Então vamos dizer último item e aqui em D dois vamos começar com uma função de deslocamento desta vez. E então o que vamos fazer aqui é vamos compensar desde o início da lista 81 e o número de linhas que vamos mover para baixo é conta uma função. Então contar um essencialmente significa que estamos fazendo essa função de deslocamento dinâmica para que ele possa identificar o último item na lista, mesmo quando os dados são adicionados ou removidos e o tamanho dessa lista cresce ou diminui. Então vamos pegar o dia da contagem de toda a coluna A que sabemos retorna um seis. E agora, antes de seguirmos em frente, isso está me dizendo que eu vou mover seis linhas abaixo de uma que me levaria a um 234567 que está em branco. Fomos uma estrada longe demais. Então, tudo o que preciso fazer para explicar isso é Adam menos um ali. E eu não quero mudar nenhuma coluna porque eu só tenho uma coluna de dados. Então eu vou colocar zero lá e eu poderia ignorar a altura e a largura por enquanto. Feche as minhas instalações. Então, agora, quando eu apertei, Enter me diz que meu último item é um limão, o que é muito legal, mas a beleza disso é que eu posso adicionar valores aqui e esse valor de D para muda acordo. Então uva, cereja e mesmo vai como eu excluir valores que atualiza também, modo que é muito legal por conta própria. A outra aplicação realmente valiosa disso é na verdade com gráficos e gráficos onde você pode definir seus dados de origem usando um deslocamento com Count Day, de modo que, à medida que você adicionar novos dados ao seu array de origem, seu gráfico será atualizado automaticamente . Então, para obter mais informações sobre isso, confira Minha data Excel de é gráficos avançados e gráficos curso, e eu vou mostrar-lhe algumas demonstrações de como isso funciona. Mas lá vai você, combinação básica de Offset e Contagem a. 15. MOSTRA de PROJETO de PROJETO: como usar o OFFSET para criar uma gráfico de rolagem dinâmico: Certo, então é hora de testar o Excel. Vamos ver o que podemos fazer para criar um gráfico diferente da maioria das pessoas já viram no Excel. Vá em frente e abra a Seção 5. Procure o arquivo de referência. Vou pular para a última guia aqui, guia Esquilo gráfico. E nosso objetivo aqui será criar um gráfico com uma referência dinâmica de dados. Vamos fazer isso usando a função de deslocamento. E o objetivo será criar um gráfico que possamos ampliar e esquilizar para alterar a visão dos dados que estamos olhando. Então, vamos ultrapassar os gráficos estáticos que 99% dos usuários do Excel estão lá fora usando um pouco de itens de limpeza. A primeira é que vamos usar barras de rolagem. E para fazer isso, você precisa inseri-los através da guia desenvolvedor. Então dê uma olhada na parte superior da tela do celular se você não vir a guia do desenvolvedor onde você precisa fazer E isso é em 2013 olhos indo para a guia inicial, Clique opções personalizar faixa de opções. E, em seguida, aqui à direita, você deve ver uma caixa de seleção ao lado de Anos de desenvolvedor é provável em cheques para ir em frente e dar-lhe um cheque. Pressione OK? E você deveria vê-lo aparecer. Hum, se a experiência do usuário é um pouco diferente e Excel 2010 ou para usar uma versão mais antiga, honestamente, basta Google e certifique-se de ter sua guia de desenvolvedor ativada. Então, estou no Developer. Vou até o botão de inserção e encontrar o controle de formulário da barra de rolagem. Clique nele. Vou arrastar um ao lado desta bolsa de rolagem, está bem? E eu vou fazer exatamente a mesma coisa e arrastar um segundo ao lado da linha de zoom. Uh, e se você quiser editar a aparência deles, você pode controlar clique neles e fez aquele um pouco gordo. E eu sou um perfeccionista, então eu vou querer algum tipo de que é perto de Idêntico não pode. Isso é bom. Agora vamos editar o que os esquilos fazem. Então, Então, vou clicar com o botão direito do mouse no 1º 1 e clicar no controle de formato. Então isso está basicamente dizendo, que valores esse pé de cabra pode tomar? O que vende o ar que eles ligam basicamente ao que essas barras de rolagem vão controlar. Então, neste caso, a barra de rolagem. Quero ter um valor mínimo de zero. Vou definir o valor máximo para seis incremento de um e mudança de página. Isso é bom. Eu realmente não me importo com isso. O link da célula será a célula D 16 que está bem ao lado do texto do índice de rolagem. Então é isso para aquele. Vou clicar com o botão direito do mouse na barra de rolagem de zoom, clique no controle Formatar novamente. Neste caso, eu queria ter um valor mínimo de um Um valor máximo de 12 e você vai ver por que em um pouco o um no 10 ou multa. Neste caso, a venda será D 17 ao lado do texto do índice de zoom. Então bata. Ok. E lá vai você. Nós configuramos ambas as barras, e agora, se você apenas clicar, você verá que elas estão vinculadas aos valores das células em D 16 e 17. Então esse é o primeiro passo. O segundo passo vai ser trabalhar com uma raça chamada que vai alimentar a nossa referência de dados do gráfico . Então eu vou entrar em fórmulas nome Gerente. Este é o meu nome de campo. ou um eu posso apenas deixá-lo como vai criar um novo. O primeiro será chamado Scroll Index, e isso só vai se referir a vender D 16. Então pressionei o pequeno seletor de dados. Aperte de novo. Então, um gráfico de esquilo d 16 ok. E criar outro chamado Índice de Zoom, e isso só vai se referir ao 17. Então eu criei dois novos chamados uma raça chamada Índice de rolagem e Índice de zoom. Então é aqui que fica um pouco mais complicado. Tenho o meu índice escolar. Tenho o meu índice de zoom. A próxima peça que eu preciso é a matriz dinâmica que vai se alimentar no meu gráfico. E eu vou precisar de pelo menos dois desses para meus valores ex, que são os meses e um para meus valores y, que vamos começar por traçar impressões. Se quiséssemos plotar impressões e cliques, precisaríamos nomear matrizes para cada série de por que Valores. Então vamos começar com os valores X aqui. Vou criar uma nova matriz chamada. Eu vou chamá-lo de valores X e observar exatamente como você escreve esses nomes para levantar porque nós vamos precisar usar a mesma sintaxe exatamente no mesmo nome com as mesmas letras maiúsculas e minúsculas uma vez que nós alimentá-los para os gráficos. Então eu estou usando o maiúsculo X e em um V minúsculo Então meus valores ex vão se referir a uma fórmula, e essa fórmula vai ser um deslocamento. Então digite offset e meus valores ex vivem na coluna B. Então minha referência ou ponto de partida será três. E, em seguida, lembre-se da segunda parte da função de perturbação, que era quantas linhas para baixo você quer mover? É um pouco mais complicado agora porque não temos a caixa de ajuda tipo de guiar-nos através da fórmula. Isto é tipo o Oeste Selvagem. Estamos por nossa conta aqui, então precisamos lembrar como as fórmulas offset foram construídas. Em qualquer caso, a segunda peça é quantas linhas para baixo. E isso depende do que o valor da barra de rolagem é que vive em tão d 16 que está ligado ao índice de rolagem chamado Array. Então eu vou digitar índice de rolagem aqui a segunda parte, quantas colunas atrasadas? Eu quero mover neste caso, meus valores ex ou meus meses apenas vivendo na coluna B. Então eu não quero mover nenhuma coluna sobre tudo. Então, no tipo de zero lá, a próxima peça será a altura da matriz que eu quero retornar. Eso Isso é dependente da barra de rolagem de zoom eso qualquer valor, Uh, zoom barra de rolagem tem saída em D 17 é a altura da matriz que eu quero alimentar no gráfico porque esse é o número de meses que eu quero olhar para qualquer dada vista, ou triturado para baixo para um ou dois meses ou ampliado para fora para todos os 12. Então esse será apenas o meu índice de zoom chamado Array. E, em seguida, por último, mas não menos importante, a largura da minha referência será apenas um porque é uma coluna de largura próxima, o parêntese e atingiu OK, e isso criou o meu campo de valores ex. Hum, eu vou criar meu campo de valores y exatamente da mesma maneira, então vai ser uma função de deslocamento. A única diferença é que o ponto de partida, vez de ser B três, será qualquer campo que eu queira um enredo aqui. Então vamos traçar impressões para este exemplo. Então meu ponto de partida será C três e então lembre-se quantas linhas para baixo vai ser índice de rolagem? Quantas colunas acima de zero. Qual é a altura? Índice de zoom e qual é o com um? Feche o parêntese e aperte OK, e agora eu criei uma matriz de valores X e uma matriz de valores Y junto com meu índice de rolagem e meu índice de zoom. Então vá em frente e feche o gerente de nomes. A última parte que eu preciso fazer é preencher este gráfico que eu basicamente montei um shell bem aqui. Então, basta clicar com o botão direito em Selecionar dados. Vou adicionar um dado. A Siri está aqui. Nome sério vai ser impressões, já que esse é o campo que eu vou usar para meus valores Lai e os valores da Siri, em vez de apenas selecionar uma célula individual ou intervalo de células aqui, o que eu vou fazer é fazer uma seleção apenas para me dar essa parte inicial desta referência porque eu quero o gráfico de rolagem e o ponto de exclamação. Mas eu vou apagar tudo depois do ponto de exclamação, e eu vou substituir tudo isso por valores escritos exatamente como eu tinha que encontrá-lo. Então desmarque essa batida, certo? Você já pode ver em segundo plano coisas que eles estão começando a preencher. E agora, em meus rótulos de eixo horizontal ou categoria em uma pressão, o botão de edição e o intervalo será novamente em vez de este array fixo. Eu vou fazer uma seleção apenas para obter o ponto de partida no lugar, excluir tudo após o ponto de exclamação, e eu vou colocar valores X aqui. A minha cabeça. Ok, você verá que nosso eixo X foi atualizado para mostrar nomes meses e lá vamos nós. Então, como você pode ver, estamos desenhando impressões e agora, como uma barra de rolagem de zoom de movimento, a largura de nossa visão, que é realmente capturada pela altura desta matriz, olhos mudando. E da mesma forma, se eu mudar o índice de rolagem agora toda a nossa visão , por mais ampla ou ter retomado, dissemos que ele está se movendo da esquerda para a direita no gráfico. Então agora eu estou olhando para seis meses de dados e eu estou vendo como as tendências mês após mês enquanto eu amplio, eu posso olhar para apenas uma única data de cada vez ou dois meses ou três e assim por diante. Então, se quiséssemos incluir cliques como um secundário, por que valor isso é fácil de fazer. Vamos falar com o gerente de nomes. Crie um Y valores para Array nomeado e, em seguida, basta alterar o ponto inicial dessa função de deslocamento para D três. E o resto será exatamente o mesmo. Hum, então lá vai você um gráfico dinâmico de rolagem presumível usando a função de deslocamento. 16. HOMEWORK: casa: olhada/de de referência: Tudo bem. Muito bem feito. Você completou o capítulo de referência de pesquisa. O curso, Definitivamente Um dos meus favoritos espero que encontrou algumas coisas úteis lá. E para aqueles de vocês que querem um pouco mais de prática, eu tenho alguns ótimos exercícios de lição de casa para vocês. Então vá em frente e abra os exercícios de lição de casa do Excel. Pasta de trabalho, Nós estamos indo para começar na aba de funções de pesquisa. Temos três instruções aqui. Isso vai usar as próximas duas guias em abreviaturas de estado cinza e renda estadual. Depois de encerrar os exercícios de funções de pesquisa, vá em frente e vá direto para o índice e combine. Exemplo. E por último, mas não menos importante, eu tenho algumas grandes demos para Offset e Count Day. Hum, boas perguntas desafiadoras. Felizmente, ele irá ajudá-lo realmente a obter uma boa compreensão de como essas funções funcionam. Então, novamente, o arquivo de resposta está disponível no curso. O recurso é. Mas se precisares de ajuda, não hesites em contactar-te. Boa sorte