Excel: top 10 fórmulas do Excel para trabalhar com mais inteligência | Leila Gharani | Skillshare

Velocidade de reprodução


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

Excel: top 10 fórmulas do Excel para trabalhar com mais inteligência

teacher avatar Leila Gharani, Excel Instructor, Business Consultant

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

13 aulas (2 h 57 min)
    • 1. Excel avançado: dez 10 fórmulas para trabalhar mais em mais forte

      2:26
    • 2. Fórmula 1 - SUMIFS, - TEde IVde para a conta para exceções

      14:03
    • 3. Fórmula 2 - SE QUE a de (Nested

      12:00
    • 4. Fórmula 3 para de credor

      5:53
    • 5. Fórmula 4 para uma pesquisa

      11:27
    • 6. Fórmula 5 - INDEX e correspondência para olhares flexíveis

      16:23
    • 7. Fórmula 5 - INDex e MATCH para casos reais

      18:45
    • 8. Fórmula 6 - as funções de texto mais mais úteis

      12:38
    • 9. Fórmula 6 - funções de texto avançadas

      17:27
    • 10. Fórmula 7

      20:24
    • 11. Fórmula 8 para cálculos dinâmicos

      14:47
    • 12. Fórmula 9 - FREa FREde - a PEQUENa e de grande

      15:26
    • 13. Fórmula 10: SUde SUMPRODUCT para o Gurus

      14: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.

2.881

Estudantes

6

Projetos

Sobre este curso

Este 10 de melhores de fórmulas de Excel apresenta as funções mais mais úteis que vai economizar tempo no trabalho. Você vai trabalhar mais rápido. Você vai trabalhar mais inteligente.

O curso inclui:

  • Cada de trabalho completa que eu uso para demonstração
  • Livro de exercícios (respostas incluídas)

A razão de eu criei este curso de Excel avançado para que o de Excel de Entendo que o quando você faz um treinamento no Excel pode ser tão amplo e muitos cursos abordando muitos tópicos. É fácil se perder Você também pode não apreciar imediatamente por que alguns recursos são úteis e como pode de aplicá-las de a forma de aplicá-los. Somos criaturas de hábito e é difícil dar nossa cabeça em aprender novas técnicas. Como resultado, você pode perder em algumas características que pode simplificar sua a maneira de usar o Excel.

Este curso vai ajudar você a preencher as gás!

Conheça seu professor

Teacher Profile Image

Leila Gharani

Excel Instructor, Business Consultant

Professor

I help companies use Excel better to improve their reports by providing customized training sessions, Dashboard Training and Enhanced Visualization Techniques. I also help improve processes, by designing interfaces and tools with VBA for Excel.

I have over 15 years of experience implementing and training users on Management Information Systems of different sizes and nature. Aside from onsite training, I offer comprehensive Excel online courses.I am also a Certified Microsoft Excel Expert and have a Masters degree in economics from University of Toronto.

I place great value on keeping my training sessions not only informative but also interesting. With technical topics like Excel, students learn the most when they attempt to solve a problem on their own. They are most engag... 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. Excel avançado: dez 10 fórmulas para trabalhar mais em mais forte: Olá. Meu nome é eles olham ao meu redor. Ajudo as empresas a usarem melhor o Excel, fornecendo treinamento personalizado e implementando painéis e aprimorando técnicas de visualização. Neste curso do Excel, mostrarei como ser melhor, mais rápido no design, folhas de cálculo sólidas que impressionarão os outros. Quando comecei a usar o Excel no, fui apresentado a ser Procurar, usei em todas as situações e muitas vezes tive que alterar a configuração dos meus dados na minha tabela para fazê-lo funcionar. Isso foi tão frustrante, mas nunca me ocorreu descobrir se há melhores maneiras de fazer as coisas. Meu principal objetivo neste treinamento é aumentar sua conscientização sobre as principais ferramentas e fórmulas no Excel para ajudá-lo a resolver problemas complexos. É só quando você sabe a extensão e as possibilidades que a Exel lhe oferece. Só então você é capaz de lidar com qualquer tipo de análise usando o melhor método. Agora há um punhado de fórmulas do Excel, que eu acredito que todos os usuários que lidam com o Excel em uma base freqüente devem conhecer nesta seção. Vou ensinar-te um olhar para cima, caso não saibas. Mas eu vou levá-lo além do olhar para cima e olhar para outras funções extremamente úteis que podem lidar com pesquisas mais complicadas para análise mais rápida é com essas funções. Você está bem equipado com o conhecimento necessário para lidar com qualquer situação que exija que você analise dados ou pegue um pedaço de informação de uma grande tabela. Aprender a ser rápido e eficaz no Excel leva tempo e prática, então não pule os exercícios. Eu também sei que treinamento técnico pode ser chato, especialmente treinamento técnico on-line, onde você está apenas olhando para a tela de alguém. Então eu tentei trazer a interatividade e essa atmosfera de ensino ao vivo de uma sala de aula para o meu treinamento on-line para garantir que você se beneficie. Você está pronto para aprender algumas técnicas avançadas? Então vamos começar. 2. Fórmula 1 - SUMIFS, - TEde IVde para a conta para exceções: alguma vez precisou de dar uma resposta a esta pergunta? Então, foi-lhe dito para fornecer um total de quatro, por exemplo, as suas vendas, mas não apenas para enganar alguns apenas para um determinado produto, um mês específico e para um país específico. Agora, basicamente, se você precisar fazer alguns com exceções e especialmente se essas exceções são mais do que uma coisa que normalmente é o caso na vida real do que isso alguma função se é para você. Aviso? Eu não estou dizendo alguns se, mas alguns se s agora, você já pode estar familiarizado com isso alguns se versão apenas não com o plus s um. Mas ele realmente apareceu desde o Excel 2007 e surpreendentemente muitas pessoas perderam isso. Mas é realmente uma função de economia de tempo, no entanto. Agora, qual é a diferença? Você pode perguntar? Bem, alguns se só podem verificar se há uma exceção, enquanto que a versão if s pode verificar até o que eu acredito que é 29 critérios. Agora que você vai precisar de tanto. - Não. Nesta palestra, eu vou mostrar-lhe a função de cúpula, bem como a soma se s versão a média se s é semelhante ao festival de cúpula, exceto que ele apenas verifica a média em vez da contagem alguns. A defesa é um pouco diferente. Eu vou te mostrar isso também. Se você nunca usou para alguns if função antes, Eu recomendo que você não aprender a usá-lo agora e apenas aprendeu se versão s. A razão para isso é que a versão if s pode fazer mais do que a versão if em. Porque a sintaxe dos dois é diferente. Você nem sempre tem que pensar qual deles devo usar agora? Eu pessoalmente uso a versão FS. Dito isto, vou começar o nosso exemplo, mostrando-lhe a soma se versão do mesmo e apenas explicar a você y d diferir e como eles diferem aqui temos um país de produtos mês e receita de vendas a partir desses dados gostaria de encontre nossa receita total de vendas para esses critérios. Então o primeiro anúncio que queremos fazer é quanta receita tivemos em fevereiro? Porque é apenas um critério que estava verificando. Eu posso usar para alguns se função os impostos pecado que primeiro vem o intervalo que intervalo. Não é o intervalo algum porque o intervalo algum está no final, Então o primeiro intervalo aqui é na verdade o intervalo de critérios. Então é o lugar onde esses critérios podem ser encontrados, e isso é basicamente aqui. Então eu vou parar aqui e então usar o meu pequeno turno de controle. Em seguida, é o critério em si, que é este e, em seguida, é o intervalo de soma. Então eu faço a mesma coisa. Controle, desligue e vamos verificar isso. Isso parece certo. Ok, então essa foi a soma Se função muito simples, você pode ver que o algum alcance veio no final. Não, temos que criar uma área que queremos verificar. Então nós queremos obter isso algum se é fevereiro e se o nosso produto é bastante camisa. E é aqui que alguns se a função s entra em jogo. Esta função foi introduzida desde o Excel 2007 e tornou a vida de tantas pessoas muito mais fácil porque eles não precisavam mais usar conflitos, fórmulas de matriz para fazer esse tipo de cálculos. Eles poderiam usar uma fórmula muito simples se s. E a maneira como funciona é que agora a certa gama está no início, porque quando minha própria programar esta fórmula. Eles não poderiam deixar o intervalo no final porque eles não sabem quantos critérios você vai colocar em sua fórmula. Então você tinha que vir para a frente e obviamente eles não poderiam mudar a fórmula antiga porque então não seria compatível com as versões mais antigas do Excel. É por isso que esta nova função foi introduzida. Então são alguns alcance é a nossa receita. E a seguir é o intervalo de critérios um critério de dois critérios para e espada, dependendo de quantos critérios diferentes você tem neste caso, nós temos que fazer. Então, a primeira gama de nossos critérios onde realmente encontramos fevereiro é esta aqui. Em seguida, vem os critérios reais. E agora vamos para a faixa de critérios também. Então, onde podemos encontrar isso aqui? E este é o critério real estava verificando para Vamos verificar isso também. Então temos uma camisa fabulosa. Branco. Esta é uma dessas quatro turnês para ajudar a turnê oito? - Não. No próximo exemplo, nos foi dado um critério adicional para dizer camisa branca de fevereiro e as vendas que tivemos nos EUA Então nós fazemos a mesma coisa que eu estou realmente indo para copiar isso. Basta mover estes para baixo as gamas, ar o mesmo. Então tudo que eu tenho que fazer é adicionar um critério adicional para os EUA A primeiro é o intervalo onde eu posso encontrá-lo, e então é o próprio critério. Ok, 16181618 Uma outra coisa que eu posso fazer é usar curingas e operadores de comparação como maiores ou menores ou uma combinação desses. Então, por exemplo, se em vez de camisa larga eu estava realmente procurando o texto lá tem que colocar aspas e colocar camisa w asiático, em seguida, colocar as aspas de sinal de estrela. Ele o encontra. Eles eram como se eu tirasse isso não encontrar nada então eu posso usar este sinal para encontrar qualquer coisa que começa com camisa e então tem wh nele. Agora deixe-me mostrar-lhe como usar os operadores de comparação. Então, se temos isso, Mas em vez de EUA, queremos adicionar a receita de vendas somente se for maior do que 500. Se não for maior que 500, não deve chegar ao topo. Então, agora vamos escrever essa. Entäo Me fazemos assim rápido. Algum alcance. Ainda é a nossa receita de vendas. Área maluca um. É fevereiro. Criar área para seus produtos. Certo, agora chegamos aos critérios. Três. Então nós queremos Onley algum se for se a receita for maior que 500. Então, neste caso, qual é a nossa área de caixas? É a receita. É este aqui. Então você pode ser ou algum alcance. Mas também pode ser nosso critério, e o critério em si é que é maior que 500. Vamos verificar isso. Temos a certeza, Dwight, que este não é maior que 500. Isso é sim, e que CS menos. Isso é março 112611 para 6. A única coisa que você precisa ter em mente quando você adiciona os operadores de comparação é que você precisa colocá-los se você não está colocando em uma célula e você realmente quer colocá-lo em uma fórmula, você não pode fazer isso. Receba um erro. Você precisa colocá-lo entre aspas porque é como texto. Você não tem que colocar necessariamente o seu número na cotação funciona. Você também pode fazer isso, ou você pode ter seu número em uma célula e fazer uma sub-referência para o número como este. Então, basicamente, ter o maior lá dentro e depois conectá-lo a esta célula. O caso de apenas ainda esquecer quando você está adicionando esses operadores, colocá-los entre aspas. Essa é a festa do verão. A média se s funciona na mesma. Exactamente. A única diferença é que leva a média em vez de alguns. Então, neste caso, você diria média se está bem, não está tomando a média da receita para Fev e camisas brancas. A contagem é um pouco diferente porque você não precisa de um desses critérios. Você não precisa de algum alcance. Então deixe-me mostrar-lhe a contagem. Se primeiro você só precisa de alcance e criar área. Então, somos apenas uma conta. Se neste intervalo há fevereiro então quantas ocorrências de fevereiro podemos encontrar aqui? Isso é um alcance. Esse é o nosso critério. Oito. Então temos 8 de fevereiro vezes. Isso é certo. Agora a versão if s nos permite verificar se há mais critérios e contar com base nestes. Então dizemos que os critérios variam um é o mesmo. Então esse é o nosso mês que a secretária arranjou. São os nossos produtos. Então quatro, basicamente temos quatro ocorrências de fevereiro e camisa branca aqui. É este, este, onde está o outro neste e o mesmo para este. Não temos três critérios para verificar novamente. Você faz a mesma coisa que três, um para e três fizeram. Tudo o que vou mostrar é a vantagem das tabelas do Excel. Então, digamos que neste exemplo alguém lhe diga, Oh, estávamos perdendo algo em fevereiro. Você tem que adicionar este número. Então eu vou colocar um grande número. Digamos que este. Obviamente, seus valores aqui em seu relatório não serão atualizados porque eles não estão fazendo referência a esta nova linha. Você terá que expandir manualmente estes, mas para evitar isso, você pode usar tabelas do Excel e eles vendem tabelas são ótimos para tais casos. Então tudo que você tem que fazer é clicar em qualquer lugar em uma célula e pressionar o controle T. Este é os dados de uma tabela, e ele tem cabeçalhos pode. Agora é uma mesa oficial. E você sabe que uma vez que você tem esta tabela ferramentas ativadas aqui e agora se eu adicionar meus novos dados a este tão curto, branco. Meus números são atualizados automaticamente. Vamos clicar em um, e você pode ver o intervalo foi atualizado dinamicamente. Essa é a vantagem das tabelas do Excel. Agora. Eu geralmente nunca gosto deste design que me dá assim para remover que você apenas ir para o design e você disse que claro. Você volta para a sua própria formatação fora da mesa. A outra necessidade de pensar sobre isso é que se sua tabela é realmente longa, você vê que ele altera seus cabeçalhos de coluna do Excel para os cabeçalhos da tabela. Quando você rolar para baixo diz tipo de como um parcial dores de congelamento, ele só faz isso por aqui. E uma vez que você está fora de sua tabela para ver os cabeçalhos de coluna usuais, como você pode ver que se a versão S permite que você adicione facilmente exceções aos seus alguns cálculos de média e contagem, mas não torne sua vida mais difícil tentando para usar filtros do Excel para chegar a seu algum uso, a versão FS em vez disso sabe retorno. Vá em frente e faça o exercício para esta palestra 3. Fórmula 2 - SE QUE a de (Nested: você usa se em suas frases diárias? Então, quando você fala com seus colegas, seu chefe, especialmente para o seu chefe, Bem, se você fizer, sua mais provável necessidade de usar se em seus arquivos do Excel também. Mas você provavelmente sabe a função if de um treinamento básico do Excel. Mas você pode não estar familiarizado com seu uso no atendido como fórmulas aninhadas reunião usando mais de um se em uma fórmula. Porque a realidade é que há sempre exceções às exceções. Ainda não vi uma empresa onde a análise e a estrutura eram realmente simples. Neste exemplo, temos nossos agentes de vendas, a quantidade que ele é vendido na receita que eles obtiveram. E gostaríamos de fazer algumas análises baseadas nisso. Gostaríamos de sinalizar os agentes de vendas que venderam três ou menos. Esse é o nosso primeiro passo, e nós vamos começar lá se a função a primeira parte é basicamente o que estamos verificando neste caso, queremos verificar se a quantidade é menor ou igual a três. Então nós queremos verificar este número, e se este número é menor ou igual 23 então o que nós queremos fazer. Se for verdade neste caso, quero sinalizá-los. Então eu realmente queria digitar a palavra bandeira lá. Tenho que colocar entre aspas. Eu também poderia fazer uma referência de celular e referenciar os agentes se eu quisesse ter o nome deles lá. O último argumento é o que fazer se for falso. Então, o que fazer se a quantidade for realmente maior que três? Neste caso, não quero fazer nada. Deixe a cela vazia porque eu gostaria de filtrar ou fazer análises sobre os agentes da bandeira para este caso. Eu não deveria ganhar nada, porque foi isso que eu coloquei lá. Nada. Mas para este, este e este aqui, eu deveria ter uma bandeira, o que eu faço. E não se esqueça de que a diferença entre a função if sozinha e a versão de cúpula é que com se você tem controle total para mudar todo o resultado da situação, enquanto que com alguns, se você está restrito a somar resultado, vamos passar para a nossa próxima análise. Queremos assinalar se o número que venderam é menor ou igual a três, e eles também ganharam dinheiro que era inferior a 200. Sempre que você tem suas frases não terminam nela. Suas fórmulas terão um fim nisso também. Vamos usar se, para um teste lógico. Vamos usar a função final porque estamos verificando se há duas coisas. Neste caso, uma é a quantidade do 1º 1 Esta célula é menor ou igual a árvore? E se a receita é inferior a 200 colchetes. Então esse era o nosso teste. O que deve fazer? Se isso for verdade, deve sinalizá-los. Se for falso, não faça nada. Não. Nós somos marcados para estes dois apenas porque este tem uma receita maior que 200. Mesmo que as quantidades menos de uma maneira semelhante, você também pode usar a função ou e ou verificar se quer fora desses critérios é significada. Então foi a bandeira passando para uma análise mais complexa, que na verdade é mais ou menos o caso na vida real. Então aqui queremos ver se a receita é maior ou igual a 300 se a quantidade é superior a quatro e devemos dar um presente a esses agentes para ficar comigo aqui. , Caso contrário, se a receita for ralada em 50, devemos elogiá-los. Caso contrário, deixamos vazio foi começar a escrever isso. Começamos com um If, não se esqueça, sua fórmula é realmente muito semelhante à maneira como você explicaria algo. Você sabe, como se esse for o caso, então nós fazemos isso. Caso contrário, fazemos isso. É o mesmo fluxo na fórmula. É só sem o texto. Então, como começaríamos a escrever esta sessão? Não tenho se e aqui não tenho. Então, se ambos forem atendidos, então vamos dar-lhes um presente. Começo com o fim. A receita é maior ou igual a 300. A quantidade é maior para igual a quatro. Esse é o meu teste lógico. O que deve fazer? Se for verdade, devemos dar-lhes um presente. Agora, se não é verdade, o que deve fazer? Deveríamos checar novamente. A receita é maior ou igual a 2 50? E essa é outra. caso afirmativo, verifique novamente. A receita é maior ou igual a 2 50? E se for, o que deve fazer? Devemos elogiar os agentes, caso contrário deixe vazio. Não pense que sempre cuidamos de seus suportes, muitos dois colchetes e há eu só aceito. E essa é a minha fórmula. O teste neste caso é a Árvore 100. Estou vendendo mais de quatro. É um presente. Este é um caso para um presente, e isso também é um presente. Estes tipos não cumprem os critérios, por isso deixamo-los vazios. O Sr. Fórmulas pode ser confuso. Você pode não obter os resultados imediatamente como fizemos agora, então você pode encontrar alguns erros e você precisa fazer alguma depuração. Então é aqui que minha dica anterior entra. Para desmascarar isso ou para verificar como isso está funcionando, podemos ir para fórmulas e avaliar isso, alguns violadores para vender. Isso está me dando um presente. Aqui estamos verificando se C 5 é maior do que isso está realmente traduzindo aquele C 5 para o número dois. O valor na célula e, neste caso, é 300 maior ou igual à árvore 100. Sim, é verdade. Seis é maior que quatro? Isso é verdade. Então o resultado da minha primeira parte é verdade, o que significa que minha resposta deve ser presente. Se eu for verificar. Um dos vazios é 210 maior do que 300 não é maior do que para novo. Isso é falso, então basicamente é falso. Tem que seguir em frente. Não é nada. Aqui está um erro. Está se movendo para verificar. O 2º 1 é a 10 maior do que 2 50 No. Então este vai ser o poço de uma era, então a Índia e o que resta não é nada. Então não se esqueça de usar a fórmula de avaliação. Se você não tem certeza por que está lhe dando uma certa resposta. Ele realmente ajuda, especialmente com fórmulas aninhadas. Agora eu também vou mostrar a vocês outro uso fora da função if. Recebi uma pergunta ontem sobre a Cora, e achei que seria bom incluí-la aqui. Acabei de fazer uma versão mais simples da pergunta. O que? A pessoa estava procurando Waas para obter o caminho sujo baseado na estrutura de pastas fora seus arquivos, e eles usaram a função de concatenação, que basicamente conecta os valores em uma célula para a próxima. Então eles tinham isso e o que eles fizeram foi porque eles queriam a estrutura de pastas correta com e barra está lá dentro. Eles adicionaram as barras entre os nomes. Ok, então, assim, você vê que ele está relatando o Excel, e eles fizeram o mesmo para o resto, ele disse. Isso é o que eles queriam alcançar. problema deles era que eles não queriam a barra se algum desses estivesse vazio, eles queriam que seu nome começasse com o nome fora da pasta e eles publicaram essa pergunta e havia uma resposta lá que lhes mostrasse como fazer isso com VB a Andi, Eu percebi que talvez a pessoa não queira usar V B A. E por que usar VB A. Quando você pode usar uma fórmula simples e a função if é realmente grande para isso, como nós usaríamos isso lá? Mas em vez de corrigir isso, barra criará um se para ele e dirá, se não houver valor aqui, nos dê nada. Caso contrário, nos dê uma barra ou você pode fazer o contrário. Se houver um valor aqui, dê-nos uma barra. Caso contrário, não nos dê nada. Então, fomos colocados isto. E se isso não é nada, em seguida, e eu acho que o contrário, dar-nos uma barra para frente e dependendo de quantos destes poderia BMT você apenas repetir que para os outros cortes. Então, neste caso também, nós dizemos, se pasta para não é nada, então nada de outra forma barra direta Ok, digamos que a pasta três sempre tem que ser preenchida para que possamos deixar isso vazio. E se eu copiar isso, faço com que funcione bem. Não há necessidade de V B A ou qualquer coisa complexa. Recapitular. A vantagem de usar uma função if aninhada é fazer exceções e alterar seu resultado. Baseado em uma situação agora é a sua vez de praticar. 4. Fórmula 3 para de credor: a função if error fornece uma maneira elegante e simples de ocultar erros em relatórios. Se você está se perguntando, como um erro parece enquanto ele é feio, seu chefe será muito provável para apontar isso para você. Mas aqui está o relatório que não tem erros. E aqui está um com erros. Se levares isto ao teu chefe, ele vai dizer-te que há algo de errado com o teu relatório. Você cometeu um erro. Você vai ser como novo? É tão certo. Isso é apenas o Excel. É como o Excel se comporta quando não há dados aqui ou está dividindo por zero. Nem tente porque a sua credibilidade já se foi há muito tempo e também saiba que os erros vêm de formas diferentes. Aqui está uma visão geral. Um homem. Vamos dar uma olhada em como podemos lidar com eles no Excel. Se você usa o Excel há algum tempo, você pode estar familiarizado com a função de erro is do Excel 2007. Microsoft acrescentou que se fórmula de erro, que é uma maneira muito mais elegante e mais rápida para corrigir este tipo de fórmulas, então dizer adeus a é erro. Agora, se você está usando em, deixe-me apresentá-lo à fórmula de erro if. Neste exemplo, temos os nossos clientes quantidades vendidas em 2016 e vendidas em contrário em 2015. Gostaríamos de calcular o percentual de mudança. Agora você pode ver que temos um problema aqui e aqui. Estes são dois erros diferentes. Este é porque está se dividindo por zero MT. Dá-te um zero de definição e este. O valor de hash é porque temos um texto com o qual estamos tentando dividir esse número, e obviamente isso não vai parecer credível. Se imprimirmos, decidiremos e entregamos ao nosso chefe. Ele vai pensar que cometemos erros neste relatório. Então, em vez de entrar e deletar isso manualmente, vamos mantê-los lá dentro. Mas vamos incluí-los lá dentro. E se a nossa fórmula é realmente, muito simples, tudo o que você tem a fazer é começar sua fórmula com a função de erro if. Então você basicamente digitar sua fórmula logo após o colchete, e então você só tem que especificar o que enquanto você faz, você quer ver se os resultados deste é um erro e digamos que neste caso não queremos ver nada, e isso parece muito melhor. Geralmente, quando você está projetando relatórios, você vai querer ter a maioria de suas fórmulas deixadas dentro. E se função de erro porque ele garante que no caso dos resultados da sua fórmula é um erro, por qualquer motivo, ele não vai mostrar em seu relatório e percebeu que a função de erro if não diferencia entre o tipo de erro, seja lá o que for. Se for um valor ou um árbitro devora, vai te dar isso. No meu caso, nada. Eu também poderia colocar algo como Verifique isso se eu quisesse Não, apenas anote isso. Você pode ver estes se tornarem. Verifica isto. Então é o que eu quero ver no caso de minha fórmula retornar um erro. Neste caso, tínhamos uma fórmula simples. Muitas fórmulas com as quais trabalharão provavelmente serão maiores, mais complexas. E a minha abordagem é sempre escrever a fórmula Lee primeiro quando eu terminar, eu, em seguida, envolvê-lo dentro da fórmula de erro if. Para aqueles que estão curiosos, como as pessoas usam a função de erro é e por que eu estou dizendo adeus a ela, apenas indo para mostrar-lhe um exemplo nisso. Então, se eu usar é erro e nós digitamos nossa fórmula aqui, a resposta é falsa ou verdadeira. Se for um erro, você se torna verdade. Não é um erro, é falso e como foi usado antes. Waas com um Se assim você diz Se isso é erro, então o que ele deve fazer? Se é um erro, não deve colocar nada. E se não for um erro, deve fornecer o resultado da fórmula. Você obtém o mesmo resultado, mas você pode ver que esta fórmula é muito mais complexa de escrever do que esta. Essa é a função if error, uma maneira elegante e simples de ocultar erros em seus relatórios, as notas retornam para fazer o seguinte exercício. 5. Fórmula 4 para uma pesquisa: Eu tinha que incluir o V, olhar para cima sobre a idade, olhar para cima funções aqui, mesmo que eu acho que a maioria dos usuários básicos do Excel estão familiarizados com eles. Mas é uma função que é meio que entre básico e avançado. Então, pensei que no caso de não ter percebido, vou incluí-lo aqui. Agora, o objetivo principal dessas duas funções é procurar um valor em uma tabela de dados grandes e dar-lhe um valor de Chris Ponding de volta que está sentado na mesma sala. Agora, uma coisa a ter em mente é que o valor que você está procurando tem que estar à esquerda. A maior parte da coluna e o valor que está sendo retornado tem que estar em uma coluna adjacente. A esquerda é a palavra-chave aqui, então você não pode procurar um valor no lado direito e retornar um valor no lado esquerdo que procuramos um trabalho H Procurar da mesma maneira. Exceto que nós é para pesquisas verticais, e H é para pesquisas horizontais. Então, se sua tabela de dados tem cabeçalhos de coluna, você vai precisar que nós olhemos para cima. E se você é tabela de dados tem cabeçalhos de função, você vai precisar de um estrangulamento. Então isso é olhar para um exemplo para cada uma para esta demonstração. Imagine que você recebeu um grande conjunto de dados e foi dito que poderia abandonar os agentes de vendas . A quantidade que eles venderam o dinheiro, eles têm o preço que eles cobraram em seus clientes. O que você gostaria de fazer é criar um relatório de agente de visão geral onde você seleciona o agente e encontra o preço e o cliente nesta tabela. Como primeiro passo, vamos adicionar a nossa lista. Foi o que aprendemos em uma palestra anterior sobre validação de dados. Então, vamos para a validação de dados em vez de qualquer lista de seleção de valor. E a fonte é que nosso agente suprime a mudança de controle e a hierarquia descendente. E embora possamos selecionar nossos agentes a partir daqui, portanto, o agente com seleção gostaria de encontrar o preço. É onde estamos olhando para a Fórmula entra. O primeiro argumento na fórmula de procura re é o valor de procura. O que estamos procurando neste caso, estamos procurando o agente, que está nesta cela. Em seguida, vem a matriz de tabela. Você tem que lembrar que esta é a mesa cheia. Então, a gama completa onde você está olhando para cima, valores dentro e suas respostas ar dentro, e é um intervalo. Então você não deve selecionar diferentes intervalos mantendo o controle pressionado e indo assim . Mas, em vez disso, você seleciona todo o intervalo que neste caso, é daqui até aqui. Estou realmente pressionando uma mudança de controle de ganho para baixo. Esse é o meu alcance e tenha em mente. Mas a chave? O principal é que este Agente D é o 1º 1 neste intervalo. Tem que estar do lado esquerdo, mesmo que eu não precise de quantidade e receita. Eu tenho que destacá-los porque eles estão no meu caminho lá no caminho fora de mim chegar ao preço e ao cliente. Então você sempre tem que vender como toda a gama, mesmo que suas coisas lá que você não precisa em seguida é o número do índice da coluna. E isso basicamente significa quanto deve se mover? Então ele assume que o primeiro 1 é um 12345 Então, basicamente, em qual coluna é a resposta que você quer, queremos o preço, e isso faz com que 1234 a quarta coluna. Temos que colocar estrangeiros lá. E este último argumento é importante para acertar. Se você estiver pesquisando, o valor é texto e não um número. Se você deixá-lo vazio, o padrão é verdadeiro. O que significa que uma correspondência exata não é necessária. Mas isso requer que sua coluna de pesquisa seja classificada em ordem crescente. Se ele não for classificado em ordem crescente e você deixar este argumento vazio, então você provavelmente vai receber uma hora. No nosso caso, temos que classificar como ascendente. Mas geralmente eu não quero ser fronteira para pensar que é este texto é É o número não está ordenado ou nota. Então eu sempre coloco falso aqui, que significa que isso deve ser uma correspondência exata. Eu recomendo que você geralmente colocar isso em falso A inchar. Ok, então vamos verificar este Agente D. O preço aqui é 39 arredondado 39. - Não. E quanto ao cliente? Posso seguir a mesma lógica de novo. Meu valor de olhar para cima é Asian D a tabela. Estou a procurar que isto está aqui. Meus clientes devem ser incluídos. Qual coluna é o cliente? O último, que o torna 1234 A quinta coluna e falso como o último argumento. Cliente D para Agente D. Cliente idade 37 para Agente H. Eu tenho um relatório muito simples e dinâmico usando Be Luca. Você também pode usar V Luke dentro da fórmula para que eles não têm que ser usados por conta própria como este. Então, por exemplo, se eu quisesse encontrar Price aqui e vamos supor que eu não tinha preços, meus dados de origem realmente precisam calculá-lo. Eu poderia escrever uma fórmula sobre isso e eu poderia dizer, Bem, dividir a receita pela quantidade e dizer qual receita dividir, por qual quantidade eu usarei. Se olharmos para cima fórmula e dizemos Olhe para cima, agente da idade nesta área, quero a receita primeiro. Então esse é o número três False como o último argumento. E divida isso pelos resultados da fórmula Blue Cup para quantidade. Quantidades número dois. E isso dá-me o meu preço. A idade do agente é 37. Como você pode ver, V olhar para cima não precisa ser usado por conta própria, mas pode ser usado dentro de outros agentes fórmulas, e é 42 cliente em. E agora deixe-me mostrar-lhe a versão H olhar para cima deste Então, no caso de seus dados de origem originais não são fornecidos desta forma, mas em vez de linhas. Então eu vou copiar isso. Vamos mudar-nos para cá. Vou colar isto como transposto. Então, no caso de você ter seus dados fornecidos a você dessa maneira, o que é menos provável, mas é possível. E você quer fazer o mesmo tipo de pesquisa no mesmo tipo de relatório de visão geral, você terá que usar a função de pesquisa H. Então, da mesma forma, se você tem nosso agente de vendas, deixe-me apenas copiar isso. Nós escrevemos novas fórmulas, então da mesma maneira, se você quiser encontrar o preço off agentes e a partir desta tabela, vamos escrever h olhar para cima para olhar para cima. O valor é este. Nossa matriz de mesa é esta. Não se esqueça que o que você está procurando aqui precisa ser o primeiro em sua matriz de tabela. Em seguida, é o número do índice da função em vez do índice da coluna. Então, onde está Price? E aqui 1234 É a quarta linha e novamente fazer falso o mesmo com o cliente. Eu posso realmente copiar este colar este aqui porque este permanece o mesmo. Isso é o que eu estou olhando para cima. A mesa é a mesma em vez de quatro. Que número eu preciso? Cinco. Cliente em ervilhas pombo aqui, mas no final dos anos quarenta. Maio Preço e cliente P está lá. Obter se, em vez de preço. Eu queria encontrar a quantidade. A única coisa que preciso mudar é o meu número de índice e a quantidade é o número dois. Então eu fico com oito. Essa é a idade Olhar para cima função para recapitular uma grande limitação fora do V Olhe para cima e as fórmulas da Copa HQ é que eles não podem olhar para a esquerda. Então, e se ele quisesse o contrário ao invés de selecionar o agente aqui, nós íamos selecionar o cliente e voltar para o agente aqui. Não seríamos capazes de usar a fórmula clássica de corte de villa. Teremos que mudar a mesa trazendo o cliente para o lado esquerdo, porque o valor que procuramos sempre tem que estar no canto esquerdo. Agora, existem outras maneiras de contornar isso usando outras funções dentro da fórmula de corte villa . Mas não se incomode em aprender isso porque na próxima palestra. Vou apresentá-los a uma das minhas fórmulas favoritas de pesquisa, que podem olhar para a esquerda e para a direita e também fazer pesquisas de matriz. Mas antes de chegarmos lá, é a sua vez de fazer um exercício. 6. Fórmula 5 - INDEX e correspondência para olhares flexíveis: Se há uma fórmula que você vai levar com você deste treinamento, deve ser o índice uma função de correspondência Eu realmente gostaria de ver você usar disfunção em suas faltas existentes porque isso vai economizar muito tempo e frustração. A pequena fórmula Yellow Cup tem suas limitações, e é aí que o índice de uma fórmula de correspondência, vem para o resgate. Por que é tão bom? Bem, a combinação A é menos propensa a erros do que imaginamos. Pode olhar para a esquerda ou para a direita na Lake Villa Cup, que só pode olhar para a direita. Você também pode fazer uma pesquisa bidirecional olhando ao longo das linhas e ao longo das colunas em uma matriz para encontrar a interseção. O desempenho costumava ser um fator nas versões mais antigas do Excel, mas não é muito mais. Lembre-se, Index and Match não é uma fórmula, mas são duas fórmulas usadas como uma só. Aqui está uma visão geral de como a função de índice funciona e os argumentos necessários. Você basicamente indexar ou destacar a área onde suas respostas e você usa o argumento sobre como Maney subiu para mover para baixo E quantas colunas para mover para encontrar a localização de sua resposta corresponde à função que você usa para tornar a função de índice dinâmica. Basicamente, muito retorna a posição de sua resposta para a função de índice. É uma visão geral ampla. Então agora vamos direto a ele e começar com a função de índice. Vou ensinar-te a indexar e combinar com base na mesma tabela. Um exemplo que tivemos para o V. Olhe para cima. Então, se você se lembra na última palestra nós olhamos para o Villa Cup e nós tínhamos a tabela na quantidade de agentes de vendas, preço e assim por diante, e nós queríamos ter um relatório de agente onde nós realmente selecionamos o agente. E com nós olhamos para cima, encontramos o preço que eles cobraram sobre o cliente que eles têm. Vamos fazer a mesma coisa agora neste exemplo, exceto que vamos usar o índice e a correspondência. Primeiro, vou mostrar-lhe a função de índice. Então vamos aqui e começar com o índice. Não, a primeira coisa que você precisa lembrar é que logo após o suporte, o intervalo que você coloca tem que ser o lugar onde sua resposta está. Então, neste caso, queremos encontrar um preço no intervalo para que está aqui. Depois disso, eu preciso especificar quantas linhas eu quero mover para baixo Neste caso, eu vou fazer isso manual agora, então eu estou procurando o Agente A e esse é um. Começa a partir de um índice é 12345 e então eu quero um. Quantas colunas eu quero mover? Nada. Quero ficar nesta coluna, e agora tenho a opção de não adiar nada. Eu também posso colocar zero, ou eu também posso colocar um. Todos eles significam a mesma coisa porque eu só tenho uma coluna selecionada. Então agora vamos dar uma olhada no Agente D. Obviamente, isso não vai ser atualizado porque ainda não é dinâmico. Nesse caso, eu diria que a Agente Deace 4 é a quarta localização. Vou ter que colocar um quatro aqui. Andi, eu entendo isso agora. O que acontece se eu colocar em vez de um para a coluna? Se eu colocar isso para eles recebem um erro. A razão para isso é que eu não posso me mover fora do meu intervalo de índice. Se eu tiver indexado apenas uma coluna, eu só posso ficar aqui. Não posso sair dela. Se eu pensei que a minha resposta, digamos que também estava aqui eu posso colocá-lo para Então aqui tem índice. Conheça esta área. Eu digo para descer quatro filas e começa a partir daqui. Então é 1234 e mover duas colunas 12 dá-lhe de volta 39. Se eu troquei a coluna agora para um, ele dá-me 350 a minha receita. Então você vê como essa função de índice funciona por conta própria. Deixe-me colocar isso de volta para que eu não possa variar para ser Não, apenas o preço. Quatro e um está bem. Então, obviamente, meu objetivo é tornar este dinâmico em vez de digitar os quatro. Porque olhando para cima onde este agente está e fazendo isso manualmente, eu quero que a fórmula para descobrir, e é aí que a partida entra em jogo. Vou escrevê-lo aqui para que fique claro o que o jogo faz por conta própria. A única coisa que você deve lembrar com match é que não é uma fórmula normal no sentido que ele não fornece o valor na célula. Você sabe como você pode fazer fórmulas e você diz, como se fosse igual a esta venda O número que é devolvido é como 39. E se eu referenciei esta célula, o que é devolvido é encontrar preço. Match não faz isso. O que ele faz é que ele retorna a posição fora do seu valor. Então vamos dar uma olhada em como ele faz isso. Quero o endereço do Asian D aqui. Minha aparência de valor é Agente D. Minha matriz de pesquisa é como o intervalo onde posso encontrar agências do Agente D em algum lugar aqui no último argumento é se eu quiser uma exata, muito menos ou maior vareta para correspondência exata. Portanto, não se esqueça de zero como o último argumento para a função match. Eu entendo o quatro e o quatro significa que é o quarto lugar aqui no que você precisa para cuidar com a função de correspondência é que o intervalo que você selecionar tem que ser uma rua de mão única . Tem que ser uma coluna ou uma regra, porque a correspondência não pode fazer isso. Ele não sabe se deve olhar desta forma ou deve olhar, desta forma porque ele apenas retorna o número. Você precisa ter uma rua de mão única e você pode fazê-lo através de uma regra também. Então só para demonstrar se em vez desse intervalo, se eu tivesse assim, que número a fórmula deveria me dar neste caso? E um, porque o Agente D é a primeira posição no meu alcance. Então agora você vê como funciona a função de correspondência. Então, em vez dos quatro, eu vou dizer jogo o que eu ganho um jogo? Onde está esse agente? Aqui dentro. Quero uma correspondência exata. O resto eu posso deixar como é apenas Em vez deste quatro, eu coloquei em uma função para torná-lo dinâmico. Eu tenho 39, o que é bom. E agora, se eu escolhesse, é um agente Jay. Eu recebo 40. Se eu selecionar o Agente G, devo pegar 88. Isso funciona muito bem. E agora, da mesma forma, posso gostar da fórmula para encontrar o cliente. O que devo indexar o cliente? Esta é a área onde está a minha resposta. E é isso que preciso indexar. Como Maney se levantou. Devo me mudar para baixo? Depende de onde o Agente G está sentado. E como faço isso dinâmico? Eu uso a função de correspondência. Estou procurando o Agente G. Estou procurando por ele ou ela aqui em cima. Eu quero uma combinação perfeita, o último argumento porque é apenas uma coluna. Quando a minha resposta estiver dentro, posso deixá-la de fora. Cliente G. Vamos trocar que funciona bem. E agora você pode estar pensando, Por que eu usaria a função de índice de partida quando eu poderia usar uma função mais simples Villa Cup ? Neste exemplo exato, eu provavelmente também usaria a função Villa Cup. Mas se minha tabela era muito maior do que isso, então como uma extração de dados do sistema grande e eu precisava procurar um valor aqui e obter de volta uma resposta, eu estava sentado do lado direito. Vou usar a função de índice porque Villa Cup é mais propensa a erros. Teria que contar o número de colunas que preciso para chegar à minha. E se eu apagar uma coluna no meio, eu vou ter que revisar, talvez olhar para cima e eu poderia esquecer, mas eu não teria que fazer se eu usar índice e correspondência. Então essa é uma vantagem, certo? Pode não estar totalmente convencido. Então vamos passar para o próximo exemplo aqui. Neste caso, queremos encontrar nossos clientes, selecionar nosso cliente aqui e recuperar nosso agente. Lembre-se, nós não poderíamos fazer isso com o V. Olhe para cima porque nós olhamos para cima só podemos olhar em uma direção. Então teríamos que cortar essa coluna de clientes, colocá-la aqui e depois. Certo. Harvey, olha para cima. Nesse caso, não precisamos tocar em nossa tabela de dados, então vamos usar o índice deles. Que resposta eu quero aqui? Quero um nome de agente. Onde é que eles podem encontrar isso nesta coluna? Próximo. número do meu quarto. Quanto devo mover para baixo? Bem, isso depende de qual cliente eu quero. Então é por isso que eu usei a grande função que eu poderia olhar. O valor é o cliente. Onde está esse cliente? Este é o alcance onde eu posso encontrá-lo, e eu quero uma correspondência exata. Preciso mover alguma coluna? Não, só tenho uma coluna. Então coloque 01 ou deixe vazio. Feche o colchete. O Agente E foi verificado que é totalmente dinâmico. Isso funciona muito bem. Então veja, neste caso, não preciso tocar na minha tabela de dados. Só tenho que escrever essa fórmula para o meu índice. Agora, vamos para outro caso. Eu quero selecionar meu agente, mas eu também quero selecionar minha variável neste caso por variável. Quero dizer, quero definir se quero quantidade, receita, preço ou cliente. Vamos apenas fazer uma lista rápida para ele é Ok, Então eu basicamente quero selecionar o meu agente, e então eu quero selecionar. Sim. Quero ver a receita deste agente ou a quantidade que este asiático vendeu e ter minha resposta aqui. Isso significa que eu preciso de dois sentidos Olhe para cima. Eu preciso olhar o agente aparecer, e então eu preciso olhar a variável aqui uma ondulação e obter o índice de correspondência. Agora, o que eu indexar neste caso? Trabalhe na minha resposta. Fique bem, não pode estar aqui. Não pode ser agente. Eles não podem ser uma resposta. Mas a resposta pode ser qualquer um desses porque dependendo de qual variável, eu seleciono quantidade, receita, preço ou cliente. A minha resposta seria um destes dizer pode ser qualquer um destes. Próximo é o número da linha. Quantas estradas eu quero mover para baixo? Depende de onde meu agente está sentado, e esse é o que eu preciso olhar para cima. Procurei-os aqui. Eu quero uma correspondência exata e sempre tenha cuidado para que os intervalos que você selecionar para que seu intervalo de índice e seus intervalos de correspondência eles coincidam em termos do comprimento do intervalo. Porque lembre-se, a função de correspondência começa a contar. 123 aqui disseram que tinham. Tem que estar de acordo com o seu índice. Estranho agora para as colunas. O que devo fazer? Eu preciso fazer outra correspondência e eu preciso combinar esta variável em. Agora vou selecionar o papel aqui. Zero para a correspondência exata. Feche o colchete. Feche o colchete novamente. 3 50 Agente D Receita 3 50 Agente E. Cliente. Cliente. E você vê É totalmente dinâmico e funciona em ambos os sentidos. Agora vamos dar uma olhada na fórmula de avaliação apenas para usar algumas das dicas que aprendemos aqui. Vamos tentar analisar isso com mais detalhes. Então esta avaliação nós temos índice que está tentando igualar o Agente E. A resposta para a Rose é mover cinco notas para baixo tentando descobrir o segundo cliente. A resposta é quatro. Então basicamente o que ele está fazendo é que você tem esse alcance. Você está se movendo para baixo. 512345 e quatro em 1234 e você começa cliente e. Então não se esqueça de usar fórmula de avaliação se você ficar preso com suas fórmulas. 7. Fórmula 5 - INDex e MATCH para casos reais: agora passando para um exemplo mais complexo. Bem, não me deixe. Carly Complex é, na verdade, um exemplo mais real da vida. Casos mais práticos que você vai ter porque as faltas que você vai ter, vamos admitir que eles não vão ser todos perfeitos. Com apenas um cabeçalho de coluna, um cabeçalho de rolo, você pode ter casos em que você vai ter vários cabeçalhos de coluna e uma combinação de thes. É o que vai torná-los únicos neste exemplo. Eu tenho a quantidade real, a receita real e, em seguida, a quantidade do orçamento. Foi essa receita e assim por diante e você poderia até ter três. Assim, você pode ter a quantidade real de janeiro, quantidade de fevereiro real e assim por diante para cada mês. E você também pode ter muitos cabeçalhos de função. Neste caso, não tenho. Mas se você fizer isso, você tem que usar a mesma técnica. Eu vou mostrar a vocês aqui, e eu não vou mostrar a vocês uma técnica, mas três técnicas diferentes sobre como vocês podem fazer isso e o que realmente pretendiam fazer é que esta é realmente uma tabela de dados e queremos obter um relatório. Então imagine que este é um bom relatório extravagante em outra página do arquivo Excel que queremos imprimir . O que queremos que o usuário faça é selecionar agente de sua lista no relatório fornece a quantidade real, a quantidade de orçamento e assim por diante a partir desta tabela de dados. Agora, como você pode ver, eu pé método 12 e 3 aqui em baixo. A ordem importa, porque eu começo com a mais simples Primeiro. Se você não estiver familiarizado com o índice e a fórmula de correspondência, fique com o Método um do início. Eu quero começar a usá-lo e realmente, realmente compreendê-lo. Em seguida, você pode continuar no método para e método três. Se você quiser. Vamos começar com o primeiro método que eu chamei de seu índice simples de correspondência. Na verdade, é como uma correspondência de índice, assim como o que fizemos no exemplo anterior, porque queremos procurar esse agente na Rose e então queremos procurar essa quantidade real nas colunas aqui, então precisamos para descer e atravessar. Mas a diferença entre este e o exemplo anterior é que nós precisamos ter este cantor único, que nós não temos neste caso em uma maneira simples de contornar Isto é criar esse cabeçalho único apenas dizendo que esta célula nesta célula é combinando-os juntos. Porque, lembre-se, jogo era uma rua de sentido único. Não pode procurar algo assim, mas ele tem que procurar algo. Isso é só uma coisa. E podemos criar isso usando esta fórmula. Agora podemos escrever nossa correspondência de índice. O que precisamos indexar? Qual é a nossa resposta? Pode ser qualquer um desses. Pode ser uma quantidade, uma receita, um cliente, qualquer um desses. Porque, lembre-se, eu quero puxar esta fórmula para baixo e para o outro lado. Deve aplicar-se a todos estes na resposta pode ser em qualquer lugar aqui. Então vamos pensar sobre a fixação. Eu não consertaria isso. Quantas linhas eu preciso mover para baixo? Depende de onde meu agente está sentado. Preciso de um fósforo aqui. Encontre este agente. Vamos consertar isso aqui. Conserte isso, e eu quero uma combinação perfeita, não uma coluna. Bem, eu tenho um cabeçalho único. Agora. Eu só preciso fazer o meu valor olhar para cima também único, e isso é basicamente deixe-me começar com uma correspondência. Essa é a combinação entre isto e isto. Então vamos pensar sobre a fixação. Estou planejando retirar minha fórmula para que eles precisem consertar a quantidade de 46 e esse caso no planejamento para movê-la. Preciso consertar o M. É o que estou combinando. Onde isso é fácil. Agora, está aqui quando você conserta isso e uma combinação perfeita. Seis Agente ser a quantidade real às seis. Então vamos ver se fizemos nossa correção corretamente. Isso é 300 50 clientes Seja 73 30. Isso parece bom. Porque se essa é a maneira simples de escrever seu índice e fórmula de correspondência no caso de você não ter cabeçalhos exclusivos, basta criar essa chave única o que puder, então faça com ele. Se não queres incomodar ninguém, não podes agrupar isto e escondê-lo de ti. Certo, vamos checar isso com outros agentes. Vamos verificar a idade do agente. Isso é nove 333. Isso é bom. Então agora vamos passar para o método número dois, e com este método, vamos pular este passo aqui, e a maneira como vamos ignorá-lo é Regan, adicionar uma função de índice adicional dentro desta última correspondência função. Vou te mostrar como isso vai funcionar. Deixe-me copiar esta fórmula aqui e baixar nossas referências de celular. O resto está bem. Esta parte está boa, certo? Esta parte está boa, Nós concordamos. Não precisamos tocar nisso. É a última parte. Isso é um problema para nós porque não é único. Então sabemos que estamos procurando essa combinação. Agora, em vez de ter este adicional, você precisa de uma chave. Vamos usar uma função de índice para nos dar quantidade real, receita real, preço real em células separadas que vai ser escrito dentro desta matriz. Não serão células visíveis. Como isso vai funcionar é nós vamos indexar isso e então nós vamos adicionar um E então olhar sobre o que eu não estou fazendo não é eu não estou indexando isso, mas isso não é o que eu preciso fazer, porque isso não vai funcionar para combinar. Lembre-se, ruas de um jeito que estou indexando isso. E isso e o que esta fórmula faz é que ela vai quantidade real como posição um. São memórias na sua posição de receita extra para a posição de preço real três. Então ele meio que faz cria uma rua unidirecional fora desta combinação na memória aqui e então eu preciso especificar o número da linha. Mas eu queria fazer isso com toda Rose. Quero dizer, não é aplicável aqui. Então, ou eu posso colocar um zero. Posso colocar a vírgula e deixar de fora. E para a coluna também. Eu posso colocar um significado zero apenas fazê-lo para todas as colunas e fechar esse índice. Agora, esta parte pertence a alguém que combina com a função de antes. Então tudo o que eu fiz foi substituir esse intervalo aqui, isso ajudar seu alcance que nós agrupámos antes com esta fórmula de índice, a imprensa entra e eles obtêm o mesmo resultado. Então, estou planejando copiar isso. Tenho que consertar que está consertando é uma das minhas fraquezas. Sempre. Percebi tarde demais. Ok. E isso funciona bem é bem, então agora vamos passar para o método número três, e isso é usando uma matriz C S E. Se você nunca usou isso, C S. E. Esta mudança de controle entra, Vai ser difícil para você digerir esta fórmula. Basicamente, no Excel, você é capaz de transformar qualquer fórmula em uma fórmula de matriz. Se você pressionar control shift, digite no final em vez de apenas pressionar Enter. O que isso significa é que ele faz exatamente o que nossa função de índice fez aqui, mas sem ter que escrever índice para que você possa transformar qualquer fórmula em uma matriz uma vez que você faz controle, deslocamento, enter. E ele realmente faz essa coisa que Index fez com dizer isso sobre isso como posição um na memória, memória, este neste tipo de sobremesa em loop nessas células. Vou mostrar-te isto, mas vou avisar-te. Por isso, não desanime quando vir esta fórmula. Se você não sabe, continue com as próximas palestras. Usado o método simples Sempre que você se deparar com o de uma vez que você está pronto para digerir thes do que conforto é novamente e se referir a este material. Deixa-me mostrar-te isto em acção. Vou copiar este ganho. Vamos atualizar nossas referências de celular. E agora, em vez de digitar o índice aqui para que você possa tirar tudo isso daqui. Também não preciso do resto do índice. Só preciso dizer que de uma partida, isto é “Pode olhar para cima “, Harry. É isso aqui, mas não vejo, se eu pressionar enter, eu ganho valor. Ele não entende o que deve fazer para fazer isso fazer o que apenas nossa fórmula de índice. Pressionei turno de controle? Segure-o. Digite que Ele funciona. Fazendo cópia para baixo e para o outro lado. Quem mudou isso? Agente K 10 3 20 Tudo parece bom. Como você pode ver, esta é a maneira mais curta de escrever isso porque eu transformei isso em um convento. Lá você pode ver. Quer dizer, uma raça tem vantagens, mas também tem suas desvantagens. A principal desvantagem que vejo com um aumento é que eles tornam suas planilhas mais difíceis para os outros entenderem. Então, se você gostaria de desistir da planilha para alguém para seus colegas ou alguém que não está familiarizado com isso e você está tentando olhar para oh, como esta fórmula é calculada? Olá, está a levar esta cela. E isso soou a imprensa entrar. Ele se foi. Não funciona mais porque eles têm que pressionar a tecla Control Shift Enter. A outra coisa é que eles podem torná-lo especialmente lento. Se você tem um monte de dados, é algo que você tem que levar em conta também. Mas, caso contrário, eles são método muito limpo e magra de escrever esta fórmula. Não pessoalmente quando eu desenhei planilhas para outras empresas e eu tenho que fazer algo assim. Irei com o Método 1 porque o meu público final pode ser diferente. Alguns podem ser muito avançando, podem ser avançados em alguns não tão avançados e método. Um seria o mais fácil para qualquer um deles seguir. Então eu diria que seu método e, por último, é Method Tree, que é mais propenso a erros. Então, como eu disse antes de uma vara para o Método um, se você não está acostumado a dias e voltar e rever esses métodos uma vez que você está pronto para eles. Então agora vamos passar para o nosso último exemplo da função de índice e correspondência. E este é um teste que eu estava fazendo para uma empresa quando eu estava projetando um modelo para eles e eles tinham uma tabela como esta. Eles queriam que o usuário selecionasse um produto do que selecionar um tipo, e o tipo é qualquer um desses dentro da tabela, e eles queriam que o modelo forneça o cabeçalho. Vou remover isso para que possamos fazê-lo juntos. Então agora temos uma pesquisa inversa em vez de olhar para cima desta maneira e desta maneira para obter isso. Vamos procurar isto e isto para obtermos isto, para veres que o D pode estar na segunda categoria. E também está na terceira Guerry do Câncer. E também está na quarta categoria. Mas dependendo do produto que você está usando, se você tiver um caso como este, também podemos usar para indexar e combinar a função. Vamos pensar como podemos fazer isso. O que precisamos indexar? Então olhe sempre para a sua pergunta. É só encontrar categoria. Nossa resposta é que uma categoria era tudo o que precisamos para indexar. Ignore o resto. Quantas filas queremos mover aqui dentro? Nada. Queremos levar em conta esse papel e é isso. Para que eu possa colocar outra vírgula. Passo para o próximo número da coluna do argumento. Então vamos começar de forma simples. Neste caso, eu quero a segunda coluna porque meu produto e D estão na segunda coluna. São só estudantes, veja, temos categoria dois. Sim, então agora o nosso objectivo é tornar o to dinâmico. Então, como podemos fazer isso? Qual função pode ser usada aqui? Os dois é uma posição direita, então nós realmente precisamos da função de correspondência porque jogo nos dá uma posição em vez dos dois. Vou datilografar. O que precisamos para igualar? Bem, eu preciso igualar o D aqui. Vou começar com isso. Por que precisamos compará-lo onde pode estar em qualquer lugar aqui? Certo. Mas lembre-se, Match era uma rua de sentido único, certo? Então eu vou começar de novo, não fazendo essa dinâmica ou semi dinâmica, e então nós vamos fazer uma dinâmica. Então eu preciso neste caso, Será que o meu d está aqui porque o meu produto para está aqui. Então, eu vou igualar este. Andi, preciso de uma combinação perfeita para conseguir a categoria, o que é bom. Então agora eu sou semi dinâmico, não totalmente dinâmico porque eu conserto isso. Eu trapaceei e disse que sei que está aqui porque essa é a combinação. Agora, em vez disso, vou usar uma fórmula dinâmica. Qual é a fórmula que posso usar para intervalos ou um aumento? É a função de índice. O que posso indexar é a coisa toda. Porque no fim das contas eu quero este ou este ou o último, certo? Então eu vou começar com a indexação da coisa toda. Então vem o número do meu quarto. Isso depende dos meus produtos para que eu possa combinar o meu produto desta lista. Então vem minha coluna, e neste caso eu queria incluir todas as colunas. Posso deixar de fora ou colocar zero lá dentro. Feche a categoria de colchetes dois. Porque o que acontece com esta fórmula é que eu indexar este direito. Mas porque eu mudei para baixo para, eu estou realmente terminando apenas para indexar isso, e isso funciona perfeitamente bem para alimentar minha função de correspondência com apenas precisa de uma fórmula unidirecional. Então vamos apenas dar uma olhada rápida na fórmula de valor neste caso. Isso pode ajudar aqui? Então estamos combinando D. Estamos indexando toda essa região B 77 para G 79 e agora estamos combinando produto para, então isso deve nos dar muito certo. É a segunda posição que nos dá duas, e nosso índice ainda está aqui, e eu vejo que diz Match D em B 78 B 78 para G 78. Então mudou isso para apenas uma rua de sentido único, com a qual o jogo gosta e pode trabalhar. Essa é outra maneira que você pode usar o índice que corresponde a função. Esta é uma das minhas fórmulas favoritas. Realmente? Realmente. Compreender a disfunção é o que separa dois gurus das normas. E você não vai ser um guru se não praticar. Agora, Sr. Regresso ao treino. Mas além deste exercício, eu gostaria de ver você dar um passo adiante e realmente tentar ver praticamente onde você pode usar o índice de muita função em suas quedas existentes e, em seguida, realmente usá-lo. 8. Fórmula 6 - as funções de texto mais mais úteis: fórmulas de texto podem economizar muito tempo limpando seus dados e colocando-os no formato que você precisa. Mas, às vezes, você precisa de menu policial ou limpar as descrições de seus produtos, seus clientes ou suas contas. Ou você precisa cortar substitutos ou substituir partes de suas citações de produtos por outro revestimento. Nesta palestra, vou mostrar-lhe as fórmulas de texto mais úteis para confiar em mim. São muito fáceis de usar. O ponto chave é que você sabe que eles existem e você sabe como fazê-los ser dinâmicos. Então, sempre que você se deparar com uma tarefa onde você precisa manipular partes de seu texto, você vai dizer, Oh, eu sei que há uma fórmula que eu posso usar para isso e você não precisa fazer isso manualmente. Esta é a lista do que eu acredito ser as funções de texto mais úteis. Vou te mostrar cada um na demo. Vamos começar com a fórmula superior. Então, digamos que temos nossa lista de nomes de clientes aqui que extraímos espaço do nosso sistema e eles estão meio misturados no verão minúsculo. Alguns são devidamente devolvidos e mistura de verão de maiúsculas e minúsculas. O que a fórmula superior faz é que você apenas digitando seu colchete aberto e texto significa destacar a célula que tem o seu texto ou você pode realmente inserir texto como este lá dentro. Mas queremos ser Dana faz, então vamos destacar essa célula. Você tem tudo em maiúsculas Lou. Onde faz o oposto? Ele coloca tudo em minúsculas e apoie ela coloca as coisas em um formato adequado. Qual é o formato adequado? É assim. Então a primeira letra é maiúscula. O resto é minúsculo. Essa é a fórmula adequada. Então, para um texto como este, isso é tudo confuso. Ele parece muito melhor nesta forma. Não, esqueça o comprimento. É a função Len. O que a função de empréstimo faz é que ele fornece o número de caracteres em seu texto. Então basta digitar Len Reference, o apresentador de celular ligado. Neste caso, tenho 13 caracteres nesta célula. A função de empréstimo você pode agora pensar bem, o que eles podem usá-lo para todos. Use é se você tem um modelo específico que você quer que as pessoas sintam e eles não devem passar por cima de um certo número de caracteres. Você pode usar essa função para rastrear quantos personagens Aaron colocou na célula. Há outros usos úteis para isso que ajudam a tornar suas outras fórmulas que vamos aprender aqui mais dinâmicas. Então vamos voltar ao comprimento do dedo algumas vezes neste exercício. Agora, neste exemplo, temos um monte de nomes de clientes que foram extraídos, digamos, de um sistema, e eles vêm com ambos os espaços finais. Olhe para este. Ou seria espaços antes ou com muitos espaços entre os personagens. Eu vou ter comprimento antes disso porque não se esqueça, um espaço é considerado um personagem. Agora, se eu aparar o que é guarnição fazer guarnição tira estes espaços adicionais, este arrastamento. Então eu só aparei estes. Agora eu vou ter o comprimento depois que eu aparado só para mostrar que ele tirou esses personagens adicionais. Então você vê, aqui eu tinha 17 porque eu tinha esses espaços à direita, e aqui eu tenho 13 porque eu cortei. Levou-os para fora, e neste caso tirou este espaço extra que eu tinha entre o Toby e o Carter. Lembro-me de quando dei um treino há algum tempo. Algumas pessoas neste treinamento riram dessa função de corte quando riram porque soa meio engraçado, mas também riem que achavam que era completamente inútil, eu acho. O que? Eles acabaram usando isso em praticamente todos os arquivos. Por quê? Porque eles me disseram que eles estão tentando usar a função de pesquisa re. Lembras-te daquela que aprendemos como a nossa quarta função? Eles estavam tentando usar isso, mas funcionou em seus dados e eles estavam extraindo os dados de um SAPI. E ele disse: “ Não funciona nesse datum. Não sabemos porquê. Adivinha o que precisavam para aparar. Eles são baixados porque quando eles extraíram em seu estojo, seus códigos de produto, vieram com espaços à direita. E quando eles estavam criando seu relatório, este não tinha os mesmos espaços à direita, então não podia coincidir com ele. Então eles tiveram que aparar estes primeiro e, em seguida, fazer o seu olhar para cima para aparar pode realmente ser uma função muito útil. Agora, passando para as funções esquerda, direita e intermediária, elas podem ser bastante úteis quando você está manipulando seus dados, suas descrições e você só quer um pedaço de seu texto de um texto maior. Então vamos começar com a função esquerda I gravando à esquerda, em seguida, detecta para o Texas basicamente, a célula em que você tem o seu texto. E o último argumento é como os personagens Maney querem tirar isso? Então, digamos que neste caso eu quero os primeiros nomes que eu quero. Toby, Como os personagens Maney são quatro para que eu possa digitar quatro. Isso é o que a esquerda faz. Então, se eu digitar o Lago 8 ver, é Toby Jen. O que é feito fazer meio faz uma coisa semelhante, exceto que temos um número inicial e o número de caracteres que queremos. Então eu escolheria este texto. Então eu posso dizer, de onde você quer começar a ler? É a primeira posição? Não é este aqui onde, neste caso, eu quero começar a ler. Não, do 45 está vazio para o sexto. Eu quero começar a ler no sexto, e para quantos personagens eu quero ler? Então basicamente começa a ler aqui e é 1234 Vamos sacudir esse último tema Jane. Eu posso usar a função certa novamente em seu texto final de maio. E quantos personagens eu quero ler deste site? Se eu quisesse o caso Carter seis? Se eu expandir isso e fazer oito Você vê, eu pego o E lá e depois Carter. Então eu quero seis “Que “? Não. Obviamente você está pensando, bem, onde eu vou olhar através de cada nome e colocar um quatro e então o próximo nome é talvez mais longo e eu preciso colocar como, um seis ou cinco. Não vou fazer isso manualmente. Não. E é aí que o achado e as funções de busca entram, assim como nossa função de comprimento, que pode tornar essa coisa toda dinâmica. Qual é a função find? Fazer? Vamos experimentá-lo. Eu vou encontrar o que eu quero encontrar? Então o texto que eu quero encontrar neste caso eu estou procurando chá, então eu vou colocar t entre aspas. Se eu tivesse chá em uma cela separada, eu posso fazer referência a isso também dentro do texto. Que texto é este e número de início de novo? Você pode dizer, Onde ele deve começar a ler desde o primeiro? Ou você pode começar a ler a partir da quinta posição, seis posições e assim por diante. Se você só queria ler desde o início, você pode deixar esse argumento vazio. Eu recebo 14. Então diz que t é a 14ª posição. Então isso é olhar, eu tenho um pequeno t lá. 123456789 10 11 12 13 14. E note que ele não encontrou o T maiúsculo porque eu estava procurando o chá pequeno. Então, agora vamos ver o que nos procurou. Antes de explicar a diferença entre os dois, vamos fazer a mesma coisa. Veja, os argumentos são os mesmos que encontrar. Então vou procurar chá. Vou procurar dentro deste texto na minha posição inicial. Na verdade, vou deixar isso vazio porque queria começar do início. Ele devolve um, embora você veja que aqui e aqui é a mesma fórmula. Só estou usando uma função diferente. Mas aqui eu tenho um, e aqui eu tenho um 14. Isso destaca uma das diferenças entre busca e pesquisa não diferencia maiúsculas de minúsculas , então a pesquisa não se importa se você está procurando um chá pequeno ou um T maiúsculo ou este é um chá pequeno ou maiúsculo T. Ele encontra a posição onde ocorre primeiro, enquanto find diferencia maiúsculas de minúsculas. Então, se eu estava procurando por T maiúsculo, eu acho que é a primeira posição. E aqui, se eu estava procurando um T maiúsculo, não se importa. Isso não importa. O Capital T ou um chá pequeno a primeira vez que ocorre é no início. Essa é uma das diferenças entre encontrar e procurar, e a outra diferença entre eles é que a pesquisa permite curinga, enquanto encontrar não, neste caso, digamos que eu estava procurando por T E. R. Ok, então vamos dizer busca TR. É a 14ª posição aqui, e eu estava encontrando também TR 14ª posição. Mas agora vamos imaginar que eu quero usar um curinga em vez do E. Então eu vou usar o ponto de interrogação no trabalho de perguntas. É como um espaço reservado para qualquer texto que possa ocorrer entre T e R. Isso me dá de volta 14. Se eu usar o curinga aqui, não funciona para recapitular. A diferença é que a busca não se importa se é maiúscula ou minúscula. Não é sensível a maiúsculas e minúsculas, e a pesquisa permite curingas. Assim, a função find é na verdade mais restrita do que a função de pesquisa. E por causa disso, eu geralmente usei a função de pesquisa sobre isso. Claro, eu realmente preciso prestar atenção ao Capitólio e em minúsculas. Então, agora, seguindo em frente, como pode ser usado esta função juntamente com esta função para obter nossos cálculos dinâmicos de sobrenome nome sobrenome feitos para nós. 9. Fórmula 6 - funções de texto avançadas: para obter o primeiro nome de uma forma dinâmica, vamos usar a função esquerda. Esse é o nosso texto. E agora vamos usar a função de busca para obter quantos atores de cuidados queremos ser despojados do lado esquerdo para cima. O que estamos procurando é o espaço dentro deste texto. Deve começar a ler desde o início. Eu posso deixar essa discussão de fora e eu pego John agora. Os resultados da minha pesquisa aqui são cinco porque o espaço é o quinto conector neste nome. Então deve ter tirado o espaço também com isso. Então você luta testículos em um obter o comprimento deste. Veja, são cinco e não. Quatro. Então o que eu precisaria fazer é fazer uma correção para isso e fazer um menos um, ou a maneira que eu prefiro fazer isso neste caso é cortar isso e se livrar desse espaço extra . Então eu estou basicamente cortando os resultados desta fórmula, e você pode ver que é por enquanto. Além disso, para obter a primeira letra para nos mostrar o capital, eu posso adicionar a função adequada a isso. Vamos ver se parece bem. Agora, a maneira como eu trabalho com funções de texto que ficam grandes ou realmente com qualquer fórmula excel que acaba ficando maior e maior, eu sempre começo com o interior primeiro. Então, com a coisa mais básica primeiro e então eu começo a torná-lo mais dinâmico, e eu adiciono outras funções a isso. Então, para o sobrenome, precisamos da função certa. Esse é o nosso texto. Eu sei novamente, quantos colecionadores devem ser retirados do lado direito. Neste caso Anderson, precisamos de oito. Mas precisamos da dinâmica da AIDS. E para conseguir isso, temos que pensar um pouco. Como conseguimos idade neste caso e depois quatro? Neste caso, a forma como podemos fazer isso é obter o comprimento da coisa toda e, em seguida, deduzir o comprimento do primeiro nome e o espaço. Então, basicamente, se encontrarmos a localização desse espaço como fizemos aqui, podemos deduzir a espuma do comprimento do nome inteiro. Eu vou fazer isso separadamente para que você possa ver os resultados desta fórmula antes de uma copiá-lo em assim, se eles obter o seu comprimento de toda a coisa, é 13. Agora, se eu deduzir essa posição desse espaço, que é cinco, eu deveria ter oito. Então eu vou procurar esse espaço dentro deste número de início de texto. Pode deixar isso vazio. Você pode colocar um que é oito. E agora vou copiar isto e colar isto em vez dos meus oito. Observe que eu não preciso fazer uma correção menos uma ou adicionar uma função de corte aqui porque eu começo a contar depois do espaço. Então agora vamos adicionar a função adequada a isso uma onda que funciona muito bem. O que acontece se ao invés de John Anderson, eu tiver John Andrew Anderson. Então eles têm um nome do meio. Agora isso tudo vai ser tomado como sobrenome porque, lembre-se, nós procuramos o primeiro espaço, e o resto é o sobrenome. Então isso nos leva ao nosso próximo exemplo. O primeiro nome é fácil. É basicamente fórmula idêntica que temos aqui, então eu vou apenas copiar e colar isso aqui agora para o nome do meio, suas diferentes maneiras de escrever isso Eu vou usar a função intermediária neste caso. Então vamos praticar um pouco com isso. O texto de que precisamos está aqui. O número inicial. Está bem, vou avisar-te, isto vai ficar um pouco complexo. Se você é muito novo nessas funções de texto, é melhor você não estar assistindo isso à noite ou se você está cansado. Então talvez você pare isso, assista isso de manhã ou tome um café agora só para avisá-lo. Então número de início neste caso, número de início seria aqui onde o J está. Então essa seria a nossa sexta posição. É isso que queremos, afinal, seis. E neste caso para Jane Issue, vá mais de quatro. Vamos testar isso antes, ver se funciona. Está tudo bem. Vamos obter o comprimento para ter certeza de que não estamos adicionando espaços. Neste caso, parece bom. Agora a Amy vai substituir o seis e um quatro por uma fórmula dinâmica. Então vamos começar com o número inicial. A posição para começar a ler o nome do meio é depois que o primeiro nome é longo, e eu já tenho o primeiro nome aqui. Então o comprimento do meu primeiro nome é o que eu preciso neste caso. Isso é suficiente? Se eu continuar assim, vamos tentar. Não, porque o comprimento é quatro e a função intermediária começa a contar com a quarta incluída. Então é o espaço mais amplo para o J e o A. Então eu não quero isso. Quero duas acusações, o primeiro nome. Mas então ele deve passar para depois porque eu tenho um espaço e ele não deve contar a última letra também, então eu posso adicionar um mais dois. Isso parece bom. Agora precisamos substituir os quatro. Então vamos pensar sobre como podemos colocar os quatro lá de uma maneira dinâmica. Eu vou fazer isso em uma cela separada, e então eu vou copiá-lo aqui. Então, como vamos conseguir quatro se eu conseguir a posição do segundo espaço aqui? Então isso seria 10. E então deduzo o primeiro nome o comprimento do primeiro nome. Então são quatro. E então os dois espaços que devem me dar quatro. Então vamos apenas verificar se eu vou fazer isso separadamente. Então vamos procurar o segundo espaço. Como eu fiz isso? A maneira que eu posso fazer isso é dizer, começar a ler depois que você terminar de ler isso no espaço. Porque lembre-se, eu tenho o número inicial que eu tenho deixado de fora até agora porque eu sempre quis que minha pesquisa começasse do início. Agora eu não sei. Quero que a busca comece daqui a partir do J. e posso fazer isso dizendo para começar a ler. Depois de chegar ao comprimento deste, mais dois para eles, encontre o próximo espaço 10. Bom. Essa é a posição deste espaço. Agora queremos dizer, deduzir a quantidade dos primeiros nomes para deduzir os quatro. Aquele seis. E agora tenho dois espaços aqui. Um aqui, outro aqui. Assim como os atores também, isso é para. E isto é o que posso copiar em vez destes quatro. Aqui eu pego Jane. Então agora veja o que acontece quando uma cópia esta erros para baixo. Por quê? Porque não tenho um segundo espaço aqui. Então, o que posso fazer para lidar com esse erro? Espero que ele disse, se erro no caso de isso resultar em um erro, o que significa que eu não tenho um segundo espaço. Não quero nada porque essa pessoa não tem um nome do meio. Estamos sentados bem aqui. Vamos verificar por ter John Andrew Anderson, isso também parece bom. Agora, para o sobrenome, vou usar o texto de exibição da função correta. Agora, não posso procurar o primeiro espaço. Preciso procurar o segundo espaço e depois tirar tudo o que vem depois disso. Então, uma maneira que eu posso fazer isso é usar a informação que eu já tenho do primeiro tema e nome do meio para obter minha última coisa. Basicamente, se eu disser me dê todo o comprimento disso, deduza o comprimento do primeiro nome. Deduzo o comprimento do nome do meio. Ainda tenho dois espaços aqui, posso dizer menos dois. Mas se olhares para aqui, não preciso de espaços. Vamos deixar com menos dois. Vou mostrar-te o que acontece quando trabalhas. Tudo bem, é Carter. Mas quando eu copio isso, você vê que foi tirado do A e do M aqui. E a razão é que esses caras não precisam de espaços só têm um. Então, se olharmos para estes, eles sempre têm pelo menos um espaço. Então o que eu posso fazer é fazer uma correção de menos um para que estes funcionem bem. E fazer este funcionar corretamente é adicionar a função de corte a toda a fórmula, porque se ele tem um espaço, ele corta. Se não o tem, deixa-o em paz. É assim que você pode lidar com casos em que você tem nomes do meio para algumas pessoas e não para outras. Mas como eu disse antes, há maneiras diferentes de chegar à dissolução, e vai ficar muito feliz se você compartilhar o seu caminho com os outros também. Então, passando para a nossa última parte, isso é o que eu apresentaria a vocês o substituto e a função de substituição. Eles podem vir a calhar dependendo da sua tarefa. Às vezes você não quer usar o Excel em uma pesquisa e substituir se eu gosto de controle h, eu obter o localizar e substituir. Você não quer que seja difícil. Cite isso em sua falta, mas você pode querer que ele de uma forma dinâmica para rastreá-lo separadamente em fórmulas. É quando você pode usar a função substituto. Primeiro, você tem que selecionar qual é a célula que você está procurando substituir. Então qual é o texto antigo que você tem neste caso? Eu quero um substituto 80 com D para estes códigos, meu velho Texas 80. Digite-o entre aspas ou pode fazer referência a uma célula. E o novo texto que eu quero é D Quantas instâncias eu quero. Vou deixar isso de fora. Meu código 80 se transformou em um, senhorita, dê uma olhada no resto. Este não foi tocado. Este não foi tocado em Lee, onde eu tenho 80 eu recebo d. Essa é a função substituto. Substituir é um pouco diferente porque você não está procurando o texto real que está substituindo. Em vez disso, você está procurando uma posição dentro deste texto para substituir, então para demonstrar, vai começar iria substituir novamente. Você referenciou o self primeiro, e agora você precisa selecionar o número inicial. Então, qual é a posição que você quer começar a substituir? Neste caso, é um. E quantos caracteres você deseja substituir? Então, se eu quiser substituir os primeiros 2 caracteres por D, eu diria para e qual é o texto que eu quero substituí-lo com seu D Se eu copiar isso para baixo , observe que eu não estou procurando 80 e substituindo. Só estou procurando a posição e dizendo: “ Comece a ler em uma.” Substituídos os 2 primeiros caracteres por D e assim ele faz isso para todos. Dado isso, você tem mais flexibilidades. Então, se eu quisesse substituir os dois últimos dígitos por 10 para esses códigos, eu poderia fazer esse número inicial. Qual seria o meu número inicial neste caso, se todos os códigos tiverem o mesmo comprimento? Quando eu posso consertá-lo, eu posso dizer no total, o que é isso? É 1234567 Então eu quero substituir os dois últimos. E isso é seis e sete para que eu possa ver. Comece às seis. Quantos personagens não deveriam ler também? Qual deve ser o novo texto? Zero 10 Sim, então ele vira meu 80 25 0 92 Ele tira o 92 e coloca 10 lá. Eu também posso dizer substituir os dois últimos dígitos por dois zeros. Tudo o que eu teria que fazer é fazer uma substituição novamente texto da escola, número inicial é o mesmo. Seis. O número de caracteres a serem lidos para substituir também é . E agora temos dois zeros. Eu tenho que colocar entre aspas para dizer que é texto quando eles colocam em zeros. Portanto, essas duas funções, substitutos e substituições podem ser úteis, especialmente se você estiver extraindo dados do sistema. E você pode precisar alterá-las um pouco e carregá-las em outro sistema. Agora, com esta função substituto, agora que aprendemos isso, também podemos usá-lo aqui para o sobrenome. Como podemos fazer isso? Eu posso ser usado substituto aqui. Podemos usá-lo de forma inteligente dizendo a partir deste nome completo, não vamos substituir o primeiro nome no nome do meio se eles têm um sem nada, e isso significa que ficamos com o sobrenome. Qual é o meu texto antigo? As minhas velhas tomadas são basicamente o meu primeiro nome e o meu nome do meio. É o que estou substituindo por nada. E isso, basicamente, é isso. E lembre-se, há um espaço entre eles, então eu tenho que explicar o espaço. A Andi. Nome do meio. É isso que me dá Toby Space Jane. Por que estou substituindo isso por nada, então vamos ver o que isso faz. Eu pego Carter com um espaço na frente antes de cortar isso. Vamos nos certificar de que funciona para o resto. O Anderson. Funciona bem. Este funciona bem. É tudo o que eu vou fazer é em uma função de corte antes, e ele vai trabalhar bem para todos eles. Então é assim que você também pode usar a função substituto. Agora. Esta não é uma lista exaustiva de funções de texto disponíveis. É exatamente o que eu acho ser as funções de texto mais úteis no Excel. Então, sempre que você precisar manipular descrições de códigos de texto, consulte esta palestra porque você provavelmente vai encontrar sua solução para ser uma mistura de técnicas aqui. Então, não, é a sua vez de completar um exercício. 10. Fórmula 7: são ótimas quando você está criando uma linha de tempo para seu projeto, onde você está montando uma linha de tempo de relatório que deseja enviar agora. O que vejo muitas vezes as pessoas fazerem é que usam um calendário para fazer isso. Não faça isso porque pode cometer erros. Em vez disso, use funções de estado do Excel. Há um monte de realmente úteis. O primeiro passo é saber que eles existem, e o segundo passo é aprender a usá-los. Aqui está uma lista das funções de data que eu acho que são benéficas, e eu pessoalmente usei para economizar tempo como um primeiro passo. Vamos começar simples. Suponha que você tenha o ano, mês e dia cada uma em uma coluna separada e você deseja obter a data em uma coluna. Tudo o que você precisa fazer é usar a função de data. Você só tem que selecionar qual coluna tem seu ano nele? Esse é esse. Depois o mês. Então hoje feche o suporte. Agora você pode ver aqui que eu tenho uma formatação personalizada. Vamos voltar à formatação com nossos atalhos, Controle um. E aqui você pode ver a formatação especial que eu usei neste caso, você pode encontrar alguma formatação de data geral aqui é bem, Mas se você não encontrar o que você está procurando, você pode criar o seu próprio. Tudo que você precisa saber é que para o dia que você usa D para mês é m para ano é por que e, em seguida, a combinação e a maneira como você usa define se você vê o número ou você vê o texto. Por exemplo, se eu digitar d d d e, em seguida, outro d eu vejo o espaço de vírgula de texto completo, então eu quero ver a data real e, em seguida, eu quero o mês que foi Mm. E se eu digitar outro M m a r, eu quero a marcha completa, eu tenho que digitar o M quatro vezes. Então eu posso colocar o ano para o ano inteiro. Você é bastante flexível sobre como deseja formatar suas datas se você usar a formatação personalizada . Agora, se isso foi formatado como geral, você vê um número e esse número é basicamente o número de dias desde janeiro de 1900. Como o Excel começa a computação dias a partir do ano 1900 você pode ver que se eu mudar isso para 1900 este 1º 1º este se torna um. Vamos colocar isso de volta. E agora, de uma maneira semelhante, posso fazer o que acabei de fazer acima do contrário. Então suponha que você tem a data. Não vai chamar, Hum, e você realmente precisa dividi-lo em três colunas separadas. O que você pode fazer é usar essas funções. Então, por um ano, basta usar o ano andan. Sua referência. A célula que tem os seus dados, Newt há meses, a mesma coisa. A última função para o dia é dia e, em seguida, você faz referência à data. A próxima é a semana em que funcionam. Esta é uma ótima fórmula para descobrir o dia de folga da semana. Então, se você quer saber foi o 21 de março e segunda-feira ou terça-feira ou quinta-feira, você pode usar a semana que eles funcionam. Referenciou a célula que o tem datado. Seu tipo de retorno é importante porque é isso que define se sua semana começa em um domingo em uma segunda-feira, terça-feira e assim por diante por padrão. Excel considera suas semanas para começar a partir de domingo. Isto para, neste caso, para considerar as estrelas de cinema do domingo significa que esta é uma segunda-feira. Se eu deixar este argumento vazio, é a mesma coisa porque o Excel usa o padrão duas semanas começando em um domingo, que é o único que eu poderia mudar isso. E se eu colocar em que número seria esse agora? Aquele porque minha semana é estrelado por uma segunda-feira e isso significa que esta é uma segunda-feira. Agora eu posso ficar confuso se você sempre tem que pensar, significa que é uma segunda-feira ou um domingo porque este foi um ou dois? O que você pode fazer é sair desta cela para mostrar o dia. Mas você tem que ter muito cuidado aqui porque se você estiver indo para fazer isso, você precisa usar um padrão do Excel ou basicamente nada, o que padrão é um. Voltamos para nossas células de formato, vamos para personalizado. Nós vamos mudar isso para tratados ou quarenta, e então nós realmente vemos o texto que está associado ao dia da semana. Mas como eu disse antes, para obter estes corretamente, você tem que estar usando um padrão do Excel, porque se eu usar um para ele não funciona. Acha que o dia 21 é domingo e não é. É uma segunda-feira. Basta ter isso em mente se você está mudando a formatação de vocês mesmos para refletir o texto do dia da semana. Próximo na linha do número da semana. Agora, para disfunção, você não precisa olhar no calendário para ver qual número de semana está associado a esse estado . Você pode usar função de número de semana excels. Você acabou de referenciar a célula com o Dayton. É como fizemos acima. Precisas de definir como as estrelas da tua semana deram o meu padrão. Um é um domingo para é uma segunda-feira. Vou deixar isso vazio, para que reverta. Um padrão. Semana de Maio estrelas de um domingo, 21 de março de 2016. Vamos dar uma olhada no calendário que é 20 de março 1º é a 13ª semana, mas não no meu calendário. Tenho a semana a partir de segunda-feira, por isso tens de ter muito cuidado quando estiveres a usar isto. Então, se eu mudasse isso não para um para obviamente isso ainda será 13. Mas se meus encontros mudarem para 20 por semana, o número muda para 12. Isso se torna domingo, e isso é na 12ª semana. Se a sua semana começa na segunda-feira. Então, sempre que você usar a função de número de semana, sempre leve isso em consideração e também seja muito transparente sobre como você é fraco. Os números estão começando, especialmente se você estiver usando isso para definir prazos. Ou você está tendo discussões com outros colegas que podem assumir que o número da semana começa a partir do dia indiferente da semana. Mas passando para a função do dia de trabalho. Essa é uma ótima função se você estiver criando um plano de projeto ou se estiver criando um cronograma de relatórios ou qualquer modelo que tenha datas e o número de dias úteis que as pessoas precisam ou você precisa concluir essa tarefa. Então não confie no calendário. Porque se cometeres erros, Excel é o caminho mais seguro. Neste caso, temos uma data de início. Sabemos o número de dias úteis. Precisamos completar esta tarefa. Queremos saber em que data precisamos terminar esta tarefa por, exceto que tem uma grande função chamada função de dia de trabalho. Por padrão, o dia útil exclui fins de semana, portanto, os sábados e domingos são excluídos. E se você quiser, você também pode excluir quaisquer feriados públicos que são definidos por você em um intervalo separado. Este é o meu dia de início. Tenho três dias úteis para esta tarefa. Vamos esquecer os dias santos agora. Vamos supor que não há feriados aqui. Preciso terminar esta tarefa até terça-feira, dia 5. E como isso funciona? Porque o fim de semana é automaticamente excluído. Agora deixe-me mostrar-lhe como você também pode excluir feriados. Então temos trabalhado. Dia este. Esse é o número de dias. E agora este é o argumento opcional para os Dias Santos. Tudo que você tem a fazer é digitar ou copiar e colar suas férias de algum modelo em excel em algum lugar em uma raiva específica e apenas destacá-lo. Isso significa que vou acabar na quinta-feira, 7 de janeiro. Podemos checar isso aqui. Só quero que isso nos dê uma boa visão geral na quinta-feira. Começamos aqui três dias, mas as férias devem ser excluídas. Significa que não podemos levar isso em conta. Ele também não é assim que são 12. É um dia santo três. Então nós acabamos na verdade, uma semana depois, de quinta-feira a quinta-feira. São três dias úteis. No último caso, eu só queria mostrar que você também pode fazer o contrário. Então você também pode voltar dias. Você tem sua data final e seus dias podem ser menos três. Então volte três dias e terá esses dias sagrados e acabará na quinta-feira 31. Ele trabalha para os dois lados. Outra função útil é a função dia de rede e esse pior tipo de semelhante a esta exceto que você tem uma data de início em uma data de término. Ambas são datas, e você deseja calcular o número de dias úteis disponíveis para você entre essas datas. Dias de rede, data de início. É a nossa data de início. A data final é esta. E embora ganhe, feriados são opcionais. Então deixe-me acreditar nessa discussão. Eu fico com quatro. Então, entre esta quinta e terça, tenho quatro dias úteis. O que você precisa levar em conta quando você usa disfunção é que ele conta a data de início e a data de término também. Então aqui está a corrida de quinta-feira. Então isso é um dois porque eu não excluí os dias santos ainda 34 Agora vamos excluir os Dias Santos também. Então, temos dias de rede começar e estes ar nossos feriados sexta-feira é excluído. Isso é um, 23 e novamente aqui. Quero mostrar-lhe que você pode ir para trás. Então aqui eu tenho um encontro em 2016. Eu vou voltar para 2015 e eu quero ver quantos dias eu tenho que voltar. Então essa é minha data de início um ganho, minha data final, minhas férias. Você pode ver que estas fórmulas de ar muito simples. Mas eles vieram realmente, realmente, não só economizam tempo, mas também salvam erros. Você deve ter notado que quando eu digitar essas fórmulas, eu tenho uma segunda opção. Então, quando eu estava digitando na função do dia de trabalho, você vende o Pior Oito Internacional. Esta função foi adicionada desde o Excel 2010 e a diferença entre esta e a função normal do dia de trabalho é que você pode controlar seus fins de semana para que você possa definir se seu fim de semana é um fim de semana de sábado normal ou é outra coisa e é sexta-feira Só. Vamos tirar sexta-feira. Só que cedo presume que sexta-feira é um fim de semana. Mas sábado e domingo são dias úteis, então isso foi introduzido desde o Excel 2010. Para ter cuidado se você estiver usando essa função, mas distribuir seu arquivo para pessoas que têm versões mais antigas do Excel a mesma coisa está disponível para dias de rede. Você tem a rede Days International, onde você pode definir o fim de semana. Alguns fins de semana serão sempre sábado domingo. Basta usar a função de dia de rede padrão e o trabalho que eles funcionam agora apenas para combinar estes, talvez para um caso mais realista. Então vamos dizer que você tem um dia de início e uma data de fim. Você tem empresas em diferentes países, neste caso na Áustria e na Tailândia, e você quer calcular quantos dias úteis cada pessoa nesses países tem à sua disposição para fazer essa tarefa. E você tem os dias santos listados em algum lugar no Excel. Queremos escrever a fórmula de uma forma dinâmica. Como primeiro passo, a fórmula que precisamos calcular são dias de trabalho é dias de rede. Data de início que sabíamos e data. Sabemos agora os dias santos. Então, obviamente, eu posso escrever uma fórmula diferente para a Áustria e uma fórmula diferente para Tailândia. Eu também poderia usar as funções. R. Se este for Áustria usado este intervalo de outra forma, use este intervalo. Mas vamos apenas supor que eu poderia adicionar em muitos países a isso, eu não usaria outra função, e eu vou sugerir o que você acha que a função de índice? Por quê? Porque eu quero mostrar a vocês outro uso fora da função de índice aqui. Eu quero dizer usado este intervalo. Se for a Áustria usou este intervalo se for a Tailândia. Então o que eu quero mostrar a vocês é o outro uso fora da função de índice usando o número da área. O que você pode fazer aqui é selecionar diferentes áreas separando-os com o Kama que você só tem que ter cuidado porque o movimento para colocar a vírgula aqui sobressai as coisas. Estás a passar para o teu número, mas não quero passar para o número do meu quarto. Na verdade, quero dizer que é isso que estou indexando. Pertencem ao mesmo argumento. Então eu tenho que trabalhar com meus suportes aqui e coloca o suporte depois de um acabado de selecionar o último intervalo. E então, quando eles colocam a vírgula, você vê, então ele passa para o número da estrada. Quantas linhas eu quero mover? Bem, eu não quero mover nenhuma regra. Eu só queria levar em conta todas as datas que eu tenho em cada linha para que você possa pular esse argumento. O mesmo para a coluna. Agora vem o número da área. Um número de área por padrão é este é um. Então essa é a minha primeira área. Essa é a minha segunda área porque eu selecionei a segunda. Se eu apenas digitar. Não, manualmente, eu colocaria um aqui. Estou escrevendo para a Áustria. Vamos pensar nos nossos suportes. Preciso fechar este. Quem é mesmo? Tenho quatro dias úteis. Vamos verificar isso desta quinta a esta quinta. E lembre-se, dia da rede também conta as datas de início e término. Então esse é o único. É um feriado na Áustria. São os fins de semana. São 123 férias Áustria quatro dias. Se eu colocar na área número dois, que significa levar para fora a Tailândia em conta, eu fico com cinco, porque aqui eu tenho o feriado comum entre a Tailândia austríaca. Mas este é apenas um feriado na Áustria e não na Tailândia. Então esse é um, 2345 Obviamente, eu quero que isso seja dinâmico, dependendo se isso é Áustria ou Tailândia, que função retorna um número ou posição a função match. Combine isto. Onde eu quero combiná-lo aqui? Que consertou isso porque eu quero atravessar. Na verdade, vou consertar esses também. Então, eles não mudam nesses também. Vou igualar isto a este alcance, e quero ter uma correspondência exacta. O que isso iria retornar é que olha isso aqui em cima e esta é a posição um lado retorna o que automaticamente significa área número um. Se for Tailândia, voltaria para o que significa área número dois. Então isso deve funcionar. Bem, se eu fiz a minha correção corretamente funciona muito bem. Então, neste exemplo, você pode ver como podemos usar sempre funções diferentes uns com os outros para obter resultados dinâmicos . Lembre-se de consultar essas funções sempre que você estiver criando um plano de projeto, uma lista de tarefas ou um cronograma de relatórios porque elas não apenas economizam tempo, mas também o evitam de cometer erros. Voltei a praticar 11. Fórmula 8 para cálculos dinâmicos: offset é uma fórmula interessante, e é uma que pode fazer muito mais do que parece. O deslocamento é uma maneira de dar ao Excel um endereço para ir. Então você começa contando como Maney subiu para se mover e, em seguida, como colunas maney para mover para chegar ao seu destino. Agora este destino pode ser uma única casa, como uma única célula. Podem ser as ruas, como muitas celas em uma gaveta, uma coluna. Mas também pode ser como uma cidade como uma área. Organize em excel. Agora, você pode pensar, para que diabos isso pode ser útil? Por que eu precisaria usar um endereço em meus arquivos do Excel neste exemplo? Eu tenho meses aqui e receita de vendas aqui, e eu quero obter a média fora dos últimos seis meses. Então, obviamente, uma maneira de fazer isso é usar a função média e, em seguida, destacar este Blake isto. Mas agora o problema é que se trata de um relatório dinâmico. Então, a cada mês novos dados chegam para agosto, eu coloco em dados aqui para obter essa média dos últimos seis meses para funcionar corretamente. Eu tenho que arrastar este alcance para baixo manualmente, e isso é algo que eu quero evitar. E a função offset é o que me ajuda a evitar isso. Para te mostrar como podemos fazer isto, vou ensinar-te a compensar sozinho. Como um primeiro passo como um segundo passo, vamos integrar a função média. A sintaxe da função offset é que você sempre precisa de um ponto de partida uma referência. É como antes de começares a andar. Por onde você vai começar? Você pode escolher qualquer célula, mas deve estar perto do intervalo em que você quer acabar. Então eu vou pegar essa cela e então como Maney Rose, eu quero me mudar para baixo? Então, digamos que eu não baixaria um. Quantas colunas eu quero mover? Vou colocar zero aqui porque quero ficar nesta coluna. E os dois últimos argumentos são a altura e o com um 11 significa uma célula. Estes dois últimos. Eles nunca podem ser 00 No mínimo, eles podem ser um 11 Se eu quisesse, como esses três, eu colocaria 31 na vara. Será que 11 1º nós temos 100 então começamos aqui. Desça um, não se mexa. Colunas, dê-nos uma célula. É 100. Se eu descesse três. Eu recebo 30 se mover colunas. Se eu realmente colocar um menos um. Então, o que significa daqui. Vá. Toma, tenho o meu par de volta. Só que eu teria que mudar a formatação. Se eu fizer um acompanhante, recebo um zero. Porque onde eu estou? Eu estou aqui. Vamos colocar para ver se estou aqui. Isso é basicamente o que o deslocamento faz. Se eu quisesse um alcance, vou colocar isto de volta ao zero. E em vez de ter um para a altura, vou colocar três para as alturas. Eu ganho valor porque o que ele faz é que eu estou começando aqui. Desceu 3123 e me dê três células para não colocar três células em uma célula. Agora, sempre que você usa offset de uma forma que esses dois últimos argumentos são intervalos, eles não estão apenas retornando uma célula, mas um intervalo de células. Você precisa envolver sua fórmula offset em uma fórmula que eles possam lidar com intervalos. O que seria essa fórmula B, por exemplo, A fórmula média, o que é alguma fórmula ou fórmula de conta? Qualquer coisa que eles possam lidar com intervalos. Então, neste caso, vamos usar a fórmula média 108. Então essa seria a média desses três. Agora, vamos expandir esta fórmula para obter os últimos seis meses. As diferentes maneiras de escrever esta maneira é que você pode primeiro ver qual é a última célula aqui usando a função de contagem e, em seguida, descer para a última célula e, em seguida, como sua altura, você tipo de voltar e destacar o menos seis ou seis linhas antes disso, ou você basicamente encontra sua última célula e então você volta para o seis para remar e sua altura é eles mais seis. Não se confunda com isso. Vou mostrar-te como um primeiro passo. O simples é o que eu prefiro e, em seguida, o outro lado também. Mais tarde, você também pode brincar com ele e ver como eles funcionam como o primeiro método de escrever isso . Agora começo bem aqui. Eu preciso saber quanto devo mover para baixo eu posso fazer isso contando o número de células preenchidas que eu tenho aqui eu posso usar aqui a função da contagem. Quando conta uma função contar um basicamente conta. Mesmo que seu texto, a contagem só conta. Se estes são valores neste caso, não importa, contar ou contar A e, em seguida, eles destacam isso. E o importante é que você destaque mais do que o que você tem, porque no futuro eu terei dados aqui. Então eu preciso levá-los em conta também. O resultado desta contagem de uma função vai ser sete. Vai me dizer para descer! Sete, que é 1234567 Eu acabo aqui. Quantas colunas devem ser movidas? - Nada. Quero ficar nesta coluna e qual é a minha altura? É aí que menos seis entra porque eu quero que a média dos últimos seis meses menos seis será este daqui até aqui, e um é que sou eu com 113. Vamos verificar que 113. Agora, quando adicionam novos meses, dois dias são 1 a 7. Fui para sete atualizações de intervalo de maio automaticamente também pode verificar isso indo para fórmulas. Avaliar a fórmula. Eu comecei aqui, eu desço oito. Não se mova. Qualquer coluna menos 61 Então o intervalo final que ele está tirando a média off é de seis para ser 11 . O que é isto para isto? A outra forma de escrever isto. Como eu mencionei antes, apenas mostre para você. A média é compensada? Agora podemos começar aqui. Neste caso, o número de linhas que eu quero ir para baixo é que eu vou fazer conta uma bengala ou vamos fazer conta. Agora eu gosto disso e eu faço um menos seis. Então, basicamente, os resultados da fórmula de contagem neste caso agora vai ser oito. Eles adicionaram mais um mês menos seis. É também. Ele só se move para baixo para a rosa 12 para as minhas colunas. É zero Eu não quero mover nenhuma coluna. E para a minha altura, eu não faço um menos seis, mas um passado seis. Eu sou um com é um. Eu entendo o que são os últimos seis meses. Duas maneiras diferentes de escrever isso Você chega à mesma conclusão agora E se seus dados foram organizados dessa maneira em vez disso? Então nós nos perguntamos Média dos últimos seis meses, mas nós os temos em fileiras. Fazemos a mesma coisa. Eu começo com a função média, depois o deslocamento. E a minha referência está aqui. Digamos que eu queira começar aqui. Quantas linhas eu quero mover? - Nada. Então esse zero. Quantas colunas eu quero mover novamente? Isso depende de quantas colunas eu tenho números em que eu vou usar para contar função quatro a função contagem A. E eu vou destacar até onde eu acho que eu vou ter números agora para a altura. Deveria ser só um agora, mas com o que preciso daqui para ir aqui, eu precisava ser menos seis. Certo, então foi ao contrário. Mas faz a mesma coisa ao tirar a média dos últimos seis meses. Então, se eu adicionar dados aqui para agosto, eu recebo de 1 a 7. Então seja criativo e pratique isso em todas as direções até pegar o jeito. Agora, outro bom exemplo sobre isso é se você tem seus meses e sua receita de vendas e você tem um relatório onde você sempre quer a média dos próximos três meses com base em uma seleção e dizer que você incluiu um menu suspenso aqui onde você pode selecionar o seu mês a partir desta lista e você quer obter a média fora dos próximos três meses, nós vamos fazer a mesma coisa. Então vamos usar a função média junto com offset como um ponto de referência que pode ser corrigido. Podemos sempre começar a partir daqui. E então quantas filas queremos descer? Bem, neste caso, queremos descer até chegarmos ao 414, que é aqui. Queremos descer tanto assim. Qual função pode um uso que me dá de volta um número a função de correspondência? Porque isso nos dá a posição off 90 nesta lista e os resultados seriam a quarta posição. Então eu sei que preciso me mudar para a Rose primeiro. Eu preciso ter o valor de pesquisa. Estou procurando isso aqui. Quero uma combinação perfeita. Nunca se esqueça disso. Isso é quantas filas eu quero mover para baixo. Porque os resultados deste é quatro 1234 Agora para as colunas. Eu não quero mover nenhuma coluna. Eu quero ficar nesta coluna, então isso vai ser zero para a altura. Depende da minha pergunta. Se eu quisesse incluir este mês também, eu colocaria um três e um de um com 102. Então vamos verificar isso. Esse é esse. Estes três. São 100 e dois. Se eu não queria incluir este mês, eu só tenho que adicionar um mais um para o meu jogo para que eu possa dizer Encontre isso, em seguida, ir para baixo um extra. Isso seria os três meses depois disso. Então, isso seria estes. Isto é puramente dinâmico. Então, se eu iria para baixo em seleto dezembro 2014 eu chegar a 32 Neste caso, eu estou fazendo os próximos três meses a três meses após dias. Seria esses três, 232 É assim que você pode usar a função média, a função de deslocamento, a função de correspondência todos juntos para criar esse efeito dinâmico. Uma palavra de cautela é que offset é uma fórmula volátil, que significa que ele calcula cada vez que você faz ele se mover no Excel. Não para ser honesto com você nas faltas que eu usei para compensar. Eu nunca experimentei o meu jejum para se tornar lento por causa de mim usando offset. Mas eu também não tenho overdose neles. Então eu também recomendo que você não os use demais agora. Como você pode ver, offset é uma função que pode ser usada dentro de muitas fórmulas para obtê-los a ter intervalos flexíveis em vez de fixos, que torna a disfunção um candidato perfeito para seu uso em relatórios dinâmicos em painéis. Agora é sobre você fazer um exercício. 12. Fórmula 9 - FREa FREde - a PEQUENa e de grande: você já precisou organizar seus dados em categorias e, em seguida, relatar sobre essas categorias ? Então, por exemplo, suponha que você foi solicitado para relatar isso. Quantas pessoas estão dentro desses grupos etários em sua empresa? Pergunte ao seu departamento de RH, diga a eles. Por favor, me dê a idade de cada pessoa que trabalha aqui, e então eles lhe dão isso. Agora, o que você faz? Você percorre a lista manualmente e, em seguida, atribui cada uma a uma categoria e adiciona-as ? Ou você vai sobre filtrar sua lista, ou você usa uma função A complexa? Depois de vigiar o setor, não fará nada disso. Vais usar a função de frequência. A fórmula de frequência é muitas vezes negligenciada porque não é uma fórmula normal. Está sob muito funcionamento. Isso significa que ele tem que ser digitado de uma maneira especial. Mas a coisa boa sobre isso é que é uma fórmula extremamente simples, e você pode economizar muito tempo. Então, neste exemplo, queremos descobrir muito rapidamente quantas pessoas temos que caem entre 20 a 30 anos idade, 30 a 40 a 50 e de 6200 e abaixo de 20 e vamos usar nossa função de freqüência para fazer isso. Agora, é muito importante que você preste atenção ao primeiro passo para o último passo porque esses ar devido a passos que a maioria das pessoas perde e, em seguida, a fórmula não funciona. Então o primeiro passo é você não apenas digitar sua fórmula em uma única célula, mas você destacar a área onde você quer que suas respostas sejam seus números para ser. Você vê como isso difere de uma fórmula normal. E então eu coloquei minha fórmula. Primeiro é a taxa de dados. Estes são os dados de sua função que você recebeu. Foi-nos dado isto. Em seguida, vem a sua matriz de caixas e sido zari é seus intervalos. Você tem que digitá-los como este obviamente pode destacar isso. Este é o texto que apoia esta questão. E o que isso se traduz é o que escrevi aqui. Então, se você tiver 2030 40 basta notar que 30 significa de 20 a 30 e trinta incluídos. Tudo o que sei é fechar o suporte e agora é o último passo. Preste atenção aqui, não pressione enter como você pressiona em uma fórmula normal. Em vez disso, você vai pressionar o controle, segurá-lo e então pressionar Shift. Segure isso para baixo. Sem imprensa. Entrar. Lá vai você. É uma fórmula de matriz. Agora, vamos verificar se acertou, então devemos ter três pessoas que estão lá em baixo, incluindo 20. Então essas três Carrie só ganha mais. Então, se eu pudesse, por exemplo, mudar este 21 para 18 isso é para ele em sete. Disse isso tratado como uma fórmula porque ele tem que olhar para todo o conjunto de dados e realocar, e você não pode alterar partes desta fórmula, então eu não posso entrar aqui e digitar qualquer menos um. Como você pode ver, a função de frequência é uma fórmula muito simples que lhe dá o número off elementos dentro de seus dados que se enquadram entre intervalos específicos. Mas você pode usar isso para salários, receitas, quantidades, quantidades, qualquer coisa que seja numérica. E não se esqueça da mudança de controle entrar. Nesta palestra, também incluí muitas funções que ajudam a classificar e categorizar seus dados. Aqui está uma lista deles. Eu tenho uma lista de empresas e a receita, e eu quero criar um relatório que mostra o melhor o pior terceiro melhor e faired pior receita e empresa para o melhor. Você provavelmente conhece essa fórmula. É a função máxima. Então, Max, você só tem que destacar o intervalo onde você quer encontrar o máximo em. Esse é um colchete, e esse é o meu valor. Para a Companhia J, o pior é a função principal. Mais uma vez, fazemos a mesma coisa. Isso é 1000 Companhia K, exceto que tem algumas outras fórmulas realmente úteis que você pode usar no ranking. Então, se você quiser o número 1234 melhores empresas ou a terceira empresa de pior desempenho, você pode usar essas fórmulas para o terceiro melhor que estaríamos usando a fórmula grande. Esta função é semelhante a marcas, exceto que você tem a opção de decidir os números. É como o terceiro melhor, o quarto melhor, e assim por diante. A primeira coisa é destacar o intervalo e, em seguida, colocar no número o ranking que você deseja obter . Então, neste caso, foram os terceiro melhores. Vamos colocar um três muito procurado, o segundo melhor que eu vou colocar. E, claro, você não tem que digitar. Você também pode fazer algumas referências, Então, se você tinha 1234 em uma coluna separada você está fazendo referência a essas células. Esse deve ser o meu terceiro melhor. Então este é o meu número mais alto. E depois é este. E então é este para o terceiro pior. É a pequena função. Você faz a mesma coisa. Deixei o seu alcance. Então eu coloquei três. Se quero encontrar o pior, devem ser os meus homens. Certo? Veja, esse é o mesmo. Se eu quisesse o segundo pior, são 9000. E o terceiro pior nos leva a 11.000. Certo, então estamos no meio do caminho. Mas também queremos tirar o nome do melhor. O pior. Terceira, melhor e terceira guerras. Qual função pode ser usada para isso? O índice funciona tanto assim? O que? Alguma coisa para indexar aqui? O que eu quero de volta uma empresa, certo? Então eu só preciso indexar as empresas. Quantas linhas eu quero mover para baixo? Bem, isso depende de assassinatos. O número está sentado agora. Eu posso referenciar a célula onde eu posso escrever a fórmula em uma fórmula. A primeira coisa é que eu quero as posições que eu preciso para usar a função muito. E dentro do jogo Em vez de dizer combinar este, eu também posso dizer combinar o máximo deste em. Onde ele deve olhar novamente? Aqui, O tipo muito é zero empresa J. Ok, então não se esqueça que você pode fazer referências celebridades ou escrever a função na função para que eu possa copiar este ritmo este ano. Mude isso para a empresa masculina K. Mude esta fórmula aqui para o que era grande? Mas agora eu tenho que definir qual 13 E aqui era pequeno três. Esse é o meu relatório. Mas qual é o problema aqui? Bem, o que acontece se duas empresas tiverem receita idêntica? Essa é uma empresa G é 2500. E se a companhia A também tivesse que o dia 3 de maio melhor será a companhia A. Mas se eu mudar isso para o meu segundo melhor também seria companhia. Uma empresa G não vai aparecer no meu relatório porque o índice que uma correspondência vai parar aqui vai ver isso. Vai combinar com a companhia A. Vai deixar a fórmula para evitar isso. O que você pode fazer é que você pode introduzir uma coluna auxiliar em sua tabela de dados. Digamos que chamamos de ajuda para a receita. Você manipula os números em uma quantidade muito pequena para torná-los únicos. São formas diferentes de os manipular. A maneira que eu normalmente faço é adicionar, e eu uso a função de função. A função de função basicamente retorna o número da regra, mas eu recebo isso é mais papel? Um 47 basicamente retorna 47. É o número da linha de um 47 que é 47 alguns adicionando 47 a este número. Isto é o que a função de função faz. Não quero acrescentar isto. Vou deixar isso de fora. Se você deixá-lo vazio assim, ele basicamente leva a linha. Você estava escrevendo ou formulando? Se eu deixasse que você vê isso acrescenta 49 a isso. Então eu usei isso e então você diz, eu não quero pedir um número tão grande de dois dias. Eu divido isso por uns 1.000.000 e acabo adicionando um número muito pequeno a isso. E a razão pela qual eu uso papel é porque quando eu arrasto isso para baixo, obviamente meu papel muda porque aqui é 50. Aqui está 51 alguns sempre adicionando um pequeno número diferente para cada um deles, o que os torna únicos. Olhamos para este agora, este tem um seis anos, enquanto este tem um cinco. Baseado nisso, eu faço o ranking. Voltando à nossa fórmula aqui, vamos nos livrar disso. O que você não quer fazer é fazer isso. Isto parece bem. Mas algum colega ou alguém pode se deparar com este relatório e eles fazem assim. E eles estavam tipo, o quê? Por que temos essas coisas que adicionaram esses números? Por que nossos números não correspondem exatamente aos do sistema que está manipulando isso e assim por diante? Então, para evitar qualquer discussão que possa surgir a partir disso e qualquer credibilidade que possa se perder Por causa disso, eu não sugiro que você faça isso. Em vez disso, você encontra o número baseado nos domingos, mas, em seguida, mostra o número original. E como podemos fazer isso? Qual fórmula pode ser usada para isso? Eles são maneiras diferentes de fazer isso para que você possa usar offset. Uma inchação. Você é. Você pode usar o índice. E já que estamos usando o índice agora, vamos nos ater a isso. O intervalo que preciso para indexar é onde está a minha resposta aqui. Isso é o que eu quero mostrar e quantas linhas eu preciso mover para baixo para mostrar que é baseado nisso. Então, novamente, começamos com o jogo. Estamos procurando por isso. Então estamos procurando o terceiro maior terceiro melhor neste caso. Onde é que estamos a procurar aqui em cima? Quer uma correspondência exata? Porque se eu abrir isto, está tudo bem. É o nosso número original. - Não. Da mesma forma, vamos fazer a pequena função, não vem índice de ar uma correspondência. O que eu preciso aqui? Uma empresa direita em Lee Need fazer é destacar esta área. Não, outra vez. São muito é baseado em não este número mais. Como eu não posso destacar a célula neste caso, eu preciso encontrar novamente o terceiro com base na minha coluna Receita auxiliar neste. E eu quero uma correspondência exata para as colunas. Eu posso pular isso. Eu tenho companhia A como agora. Meu terceiro oficial, é realmente o meu terceiro? Esse é o meu primeiro não, só o segundo é este porque este número é um pouco maior do que este número. Agora vem o teste. Eu quero olhar para o segundo melhor que o número é o mesmo. Mas agora devo chamar a Companhia G. E eles fazem isso. Funciona muito bem. Eu sei. Eu só vou copiar isso. Altere isso para pequeno altere isso para três. Essa é a minha empresa. E. Vamos colocar isso de volta também. E é assim que você pode usar eficazmente as funções grandes e pequenas para classificar seus dados. Mesmo que você tenha duplicatas, não, para voltar a fazer um exercício. 13. Fórmula 10: SUde SUMPRODUCT para o Gurus: tenha em mente que a sequência do meu top 10 não é importante. O que é importante é que você conhece essas funções e você sabe como usá-las quando chegar a hora. Dito isso, mantive o melhor para o fim. Eu não tenho certeza se algum produto foi realmente projetado para ser tão grande ou se ele apenas aconteceu para se tornar assim. Você tem que assistir a essa palestra para descobrir. O uso clássico de algum produto é fazer uma soma de multiplicação. Mas para dar um exemplo, você tem aqui preço e quantidade para calcular sua receita total. Você vai multiplicar preço por quantidade e, em seguida, você somar os valores na Coluna Receita . Agora você tem uma coluna calculada e um total para essa coluna calculada, que é sua receita total com algum produto. Por outro lado, ele só tem uma fórmula que faz tudo por você. Você começa digitando em algum produto. Em seguida, basta destacar sua coluna de preço Atacama para passar para o próximo argumento, seguido por sua quantidade entre parêntesis e você tem seus resultados. A coisa boa sobre algum produto é que ele pode trabalhar em um aumento de valores, e ele não precisa ser inserido como uma função de matriz. Então não há necessidade de pressionar esta mudança de controle entrar como fizemos quando aprendemos que a função de freqüência é uma espécie de muito elegante cada função. O que realmente está fazendo é pegar a primeira célula, multiplicando-a pelo 2º 1 fazendo a mesma coisa para o próximo papel e adicionando o resultado. Então você basicamente se multiplica e depois soma. É por isso que é importante que suas gamas tenham o mesmo tamanho. Vamos dar uma olhada mais detalhada nessa função. Então, do jeito que escrevi aqui, eu uso a vírgula, certo? Isso é por padrão. É assim agora, da maneira que eu geralmente direito, Minhas algumas fórmulas de produto é assim, e eu vou explicar para vocês por que, passando por este exemplo, este foi um caso simples. Se você já usou algum produto, você provavelmente o usou neste formulário com algum produto. O que algumas pessoas não sabem é que você também pode fazer exceções para que eu possa obter a soma de uma palavra a dizer neste caso quantidade apenas para o produto A e você pode usar a mesma fórmula e agora porque eu estou fazendo uma exceção. Só estou à procura de produtos. Melhor produto A. Tenho que destacar isso. Então eles vêem igual a isso. Isso é algo com que você tem que ser tão cuidadoso. Os suportes estão aqui? Portanto, sempre que você usar esse sinal de igual em algum produto, certifique-se de que seu argumento está separadamente dentro de colchetes. E então vamos dizer que eu usei uma discussão normal com este caso vírgula. Quero passar para o próximo Array. Eu quero quantidade. Então tudo o que eu tenho que fazer é destacar isso e você vê, certifique-se de que seus intervalos são obviamente do mesmo tamanho. Então, vou fechar isto em termos. Será que ele funciona? - Não. Deixa-me mudar isto para isto. Funciona. Por quê? Porque o que isso faz aqui é verificar se este é um produto um verdadeiro ou falso. Então esse é o resultado de “Excels”. O cheque é neste caso, é verdade. Verdadeiro, verdadeiro falso cai Verdadeiro. Quando você o deixa com o Kama, ele deixa o verdadeiro falso é texto e pode fazer qualquer coisa com ele. Portanto, é zero quando você usa o sinal de multiplicação e o Excel tenta multiplicar uma célula. É o Bolin. Então isso é verdade ou falso. Transforma-o em um e zeros. Então, basicamente, isso se torna 101? Então ele faz. Um multiplicado por 200. Isso é 200 mais um, multiplicado por 8 33 mais um, multiplicado por 100 assim por diante. É por isso que eu uso o site mais aplicar. Vamos ver se o nosso número está correto. Então vamos somar onde quer que tenhamos produto. 80 quantidades. Isso é para três. Isso funciona. - Tudo bem. Se você quiser usar a vírgula, o que você pode fazer é ter dois novos operadores numéricos nesta célula. O que algumas pessoas fazem é que façam isso. Isso é basicamente menos para obter isso. Então é como se você estivesse multiplicando por um menos um, revertendo. Você também pode fazer como um multiplicado por isso. Enquanto você estiver fazendo algum tipo de multiplicação neste Excel vai traduzir um dedo falso verdadeiro, um zeros. Então, para evitar tudo isso, eu mudo essa vírgula para isso. A mudança vai calcular uma receita. Eu faço a mesma coisa. Eu tenho algum produto. Também quero a minha excepção lá de novo. Não se esqueça do suporte. Vamos supor que eu não tenho isso. Ligue para ele aqui. Eu vou fazer o preço e então eu posso fazer um ganho duas vezes quantidade para verificar novamente. Isso vai ser estes e este aqui. É que agora o que a maioria das pessoas não sabe é que algum produto pode fazer muito mais do que isso por causa de sua natureza fora da forma como ele é projetado, ele pode fazer o que nós procuramos ou indexar uma correspondência pode fazer. Ele também pode fazer o que alguns efeitos podem fazer e fazê-lo ainda melhor do que alguns se s podem agora ficar comigo neste, porque entender esta função vai salvá-lo de fazer muito fora entre cálculos e desnecessários soluções alternativas em seus arquivos existentes do Excel. A coisa boa sobre algum produto é que ele também pode fazer ou condições. E isso é algo que você não pode fazer. Por exemplo, alguns se s ou contar se ISS Se você se lembrar, quando cobrimos esta função um Murray cobrir alguns efeitos, nós só poderia verificar para uma coisa para um critério em cada coluna. Então só podíamos verificar se as empresas A, mas não podíamos fazer as duas coisas que não podíamos fazer e ou A ou B . Éramos restritos em cada coluna a uma condição. Nós não estamos com algum produto porque você pode usar ou condições aqui, Então vamos ver como você pode fazer isso. Neste caso, quero ver se é a empresa A ou empresas. Esse é o meu ano no meu mês. Olha, eu estou fazendo conta. Vou usar algum produto para contar. Eu vou te mostrar como. Agora eu estou dizendo que se a empresa é um ou B Então novamente, colchetes são muito importantes neste caso, esta é a minha empresa. Se for igual a esta vez, vou fechar isso. É o meu primeiro argumento. Agora o argumento ou é uma vantagem aqui. Esse é o ou este é. E isto é ou então se este intervalo é igual a este um colchete próximo, meu todo ou condição precisa estar em parênteses separados. Você pode ver que a lista apenas pessoa virar para ver o que acontece? Eu recebo um número. Então isso deve significar que são sete casos fora da Companhia A e C aqui. Isso é certo. Na verdade, eu também estou fazendo conta, mas eu não estou feito com o meu argumento porque eu ainda quero verificar para o ano 2014 e meses para ser janeiro. Então aqui vou dar as boas vindas ao Ano Bracket é igual a isso. Lembre-se, sempre que tiver o sinal de igual, coloque essa parte entre parênteses e meu mês. Portanto, deve ser cinco instâncias para esta combinação. Agora o que? Eu vou te mostrar, vamos ver isso. Mas vamos verificar isso aqui. Porque o que eu fiz aqui foi mostrar a vocês o algum produto em câmera lenta o que ele está fazendo, o que quer que a fórmula faça, é transformar dois verdadeiros falsos como a primeira instância. Então, no nosso caso, vimos, como a empresa A ou C 2014 encravar essas linhas, ou aquelas em que tudo é verdade como um segundo passo porque estamos multiplicando, está transformando em um e zero. Então basicamente passa por isso e faz um vezes um tempo zero que é 00 vezes. Quer insulina? Zero. Estes são um e este ar um. Então, no total, você pode ver que eu tenho cinco instâncias em que eu tenho um quando é assim que ele funciona da mesma maneira que nós temos o alguns para quantidades de uma de alguma quantidade. Mas este ainda é o nosso critério para simplificar as coisas. Vou copiar esta fórmula colá-la aqui, está bem? Porque até aqui, isso é o que precisamos é idêntico ao que precisávamos para ter em conta. Mas agora só preciso trazer minha quantidade. Ligue para ele. Isso é por 70. Porque vamos que vai verificar Aqui ele faz este sistema de tempo desde e então vezes isso, que basicamente é uma vez esta uma vez 91 vezes 100. E isso nos dá 70. Esse é o nosso 470 aqui. Agora vou passar para as receitas. Só quero multiplicar a quantidade pelo preço. Vamos supor que não temos essa coluna. Então tudo o que tenho que fazer é trazer o preço. Basta expandir sobre ele e você ganha mais. Essa é a minha receita. Esse é o último a preço médio. Como eu poderia calcular isso neste caso? Porque lembre-se, para alguns dos produtos, está sempre adicionando soma. Então, como eu poderia obter o preço médio? Bem, eu poderia obter a soma do preço e então dividir pela minha contagem. Novamente, é a mesma coisa, mas não sejamos preguiçosos. Vamos escrever isso bem rápido. São exceções. Venha primeiro. Eu sei que eu quero as condições ou, então eu vou apenas fazer para suportes adicionais. Isso é igual a este mais suporte de jogo. Continua a esquecer-te disso. Mas quando eu vou te mostrar o que acontece se você não fizer o suporte, então vamos fazer isso é igual a isso sem suporte enquanto você apenas colocar os colchetes. Então agora eu só tenho que adicionar este que você vê aqui. Eu não preciso de um suporte. Eu não tenho um sinal de igual, então eu posso deixar isso como está, e isso vai ser maio algum preço de desconto. Então eu posso dizer, dividido por este. Eu já fiz o cálculo aqui. Esse vai ser o meu preço médio. É só checar o preço médio. Obviamente, essas dinâmicas de ar. Então, se eu mudar isso para a Companhia B ou C, os resultados mudam bem. Você vê por que algum produto é uma função tão incomum e útil? Não é apenas a soma de produtos, mas você pode usá-lo como uma contagem se a função é alguma função if e também uma função de olhar para cima. Agora, preste atenção aos seus suportes , , porque a colocação deles é muito importante. Se você estiver escrevendo isso pela primeira vez, isso irá verificar seus resultados e depois confiar nele. Se você esquecer a sintaxe como qual foi o mais quatro? Eles podem usar isso? Foram os traços do rabo, o coma ou os suportes? Volte para a pasta de trabalho de demonstração para atualizar sua memória. Não há vergonha de eu fazer isso sozinho agora este exercício de retorno, amigo.