DICAS Excel PRO Parte 3: fórmulas

1. Introdução à fórmula dicas: Muito bem, bem-vindo a uma das minhas seções favoritas dicas de fórmula. Agora, esta é uma das partes mais desafiadoras e técnicas do curso e abrange tópicos que vão desde os modos básicos de cálculo e ferramentas de auditoria até bastante avançado, como muitos buscas e alguns produtos para aumentar. Tenha em mente, que vai estar se movendo muito rapidamente, por isso pode ser difícil acompanhar. Se você ainda não está confortável com o básico, vamos mergulhar. 2. Manual vs. Cálculo automático: Tudo bem. A próxima dica é uma dica de fórmula. Este é um conceito de uma estrela, bastante básico, mas é algo que nem todos os usuários do Excel estão cientes. E é como mudar sua fórmula. Os modos de cálculo agora o Excel tem dois modos de cálculo primários. Automático, que geralmente é o padrão e manual. Portanto, se você for até a guia de fórmula à direita, verá suas opções de cálculo. Na verdade, existem três opções diferentes. Eles são os dois em que vamos nos concentrar agora. São automáticos, que permite que o seu cálculo formalista por conta própria com qualquer trabalho de mudança e manual, que essencialmente congela esses cálculos de fórmula até que você decida atualizar um recalculá-los. E você pode fazer isso clicando no botão calcular agora na guia fórmula ou usando o atalho F nove. Agora que terceira opção automática, exceto para tabelas de dados, vamos falar sobre tabelas de dados na seção analítica deste curso. Esses são frequentemente usados para randomizar e executar um grande número de simulações. É parte de excelência. E se ferramentas de análise e porque eles geralmente iteraram em um número muito, muito grande de linhas, eles podem retardar as coisas se eles estão recalculando constantemente. Então essa terceira opção basicamente lhe dá a escolha de dizer, você sabe, tratar todas as minhas outras fórmulas de pasta de trabalho como automáticas. Mas deixe-me decidir quando eu quero atualizar essas tabelas de dados especificamente. Então vamos dizer que você tem seu modo de cálculo definido para manual como vemos aqui, e então você escreve algum tipo de função como esta. Esta é a fórmula profeta que vamos escrever nesta demonstração e basicamente contabiliza a quantidade,preço de quantidade, varejo e custos unitários. Você arrasta essa fórmula para baixo, aplica-a a várias células e vê o mesmo valor retornado em todos os casos. primeiro lugar a procurar é o modo de cálculo, porque nove em cada 10 vezes isso significa que foi acidentalmente mudado para manual. Mas há boas notícias. É uma solução muito fácil. Tudo o que você faz é voltar para essa guia, verificar a opção automática, e então essas fórmulas serão automaticamente recalculadas por conta própria. Então, para resumir alguns casos de uso comuns, em alguns casos você pode ter uma pasta de trabalho muito grande, muito pesada fórmula, usando coisas como olhar para cima e índice e funções de referência, ou um aumento que iterar muitos, muitas vezes mais. Um grande número de linhas Nos casos em que os cálculos demoram bastante tempo, talvez você queira alternar temporariamente para o modo manual como você está fazendo outras edições da pasta de trabalho . Apenas por uma questão de velocidade e eficiência, apenas certifique-se de mudá-lo de volta para automático quando você quiser que essas fórmulas para recalcular em. O segundo caso de uso comum. Novamente, como eu disse, é permitir que suas fórmulas calculem por conta própria automaticamente. Mas trate suas tabelas de dados separadamente. Então vamos saltar para uma pasta de trabalho profissional e eu vou mostrar-lhe como isso é. Tudo bem, então do nosso índice, vamos olhar na nossa categoria verde ou dica de fórmula aqui. Os modos de cálculo do primeiro item vão em frente e se conectam a essa cidade, e aqui temos uma amostra de cerca de 100 produtos, juntamente com a quantidade vendida. Você tem o preço de varejo e o custo unitário para cada um desses produtos, e nós só queremos calcular o lucro de vendas aqui. Agora, antes de o fazer, vamos entrar na aba das fórmulas dela. Faça drill-down em nossas opções de cálculo. Você verá que, por padrão, minha pasta de trabalho definida como automática. Vá em frente e apenas por causa das demonstrações, mude isso para manual e, em seguida, para fora ou função aqui. Então nossa fórmula de lucro de vendas será a quantidade vezes o preço menos o custo, então digamos, igual a B duas vezes parênteses abertas. Preço de varejo menos o custo unitário. Feche isso. Pressione Enter. Isso retorna $12,058.25 que é o valor correto para estrada para. Mas como eu mostrei a vocês, se aplicarmos isso até o fim, veremos o mesmo valor duplicado de repetição, o que obviamente está incorreto. Então eu vou começar a fazer de volta para fórmulas opções de cálculo automático. E lá vai você, tudo. Recalcula para reescrever a fórmula ou algo assim, e estamos prontos. Novamente, conceito muito simples, mas muito útil. Um para ter em mente uma vez que você saiba que esses dois modos de cálculo existem 3. Quebras de linha de fórmula: esta dica profissional é tudo sobre o uso de quebras de Linha de Fórmula, que é uma ótima maneira de melhorar a legibilidade e organizar suas fórmulas. Agora é uma ponta de uma estrela muito, muito básica. Mas é algo que eu realmente não estava ciente de há algum tempo. E a idéia é que, enquanto você está realmente digitando ou editando dentro da barra de fórmulas Excel em vez de apenas pressionar, enter, que irá enviar sua fórmula, segure Alz primeiro para que quando você pressionar ault e digite seu criar uma quebra de linha e dividir sua fórmula em várias linhas. Então, para dar um exemplo nesta demonstração que irá percorrer, temos esta declaração if aninhada, que não é extremamente longa. Mas é bastante longo, e o que vamos fazer é pegar essa fórmula usada quebra de linha para quebrar cada condição, cada condição, indivíduo se declaração em sua própria ova. E isso não vai mudar nada sobre como a fórmula calcula ou os resultados que ela produz. Tudo o que está fazendo é ajudar a melhorar a organização e a legibilidade. Coisas muito diretas aqui. Casos de uso comuns novamente, detalhando funções, especialmente como funções condicionais como essa nessas múltiplas linhas para isolar critérios individuais ou tornar fórmulas aninhadas mais complexas mais fáceis de interpretar introduzindo cada uma das novas funções de componente em uma nova linha. Então você vai chegar ao ponto como você fica muito avançado com fórmulas e funções onde você está combinando todos os tipos de coisas como Offset e Index e Count Day e Rose. E se você perceber todos eles dentro de uma única cadeia interminável de funções, eles podem ser muito, muito difíceis de interpretar e diagnosticar. Alguma coisa está a correr mal. Então vamos entrar no Excel e dar uma chance a isso. Tudo bem, então a partir do nosso índice, eu procuro a linha de fórmula, break demo e vá em frente e pressione link, e isso irá levá-lo para uma guia que contém informações sobre 24 lojas diferentes ou supermercados. Eu tenho uma região I D. Você tem City State, país, país, e então esta coluna Total Square Foot, que está dirigindo esta fórmula na coluna H. E esta é uma declaração aninhada se, nós estamos testando vários critérios e essencialmente aplicando algum tipo de rótulo baseado no total de pés quadrados, portanto, dependendo do tamanho da loja, foram rotulá-lo como pequeno, médio, grande ou muito grande. Então este será um bom candidato para as quebras da linha Ault aqui na Fórmula Bar. Agora vamos clicar na barra de fórmulas. A primeira coisa que podemos fazer é passar o mouse sobre a borda inferior até que você veja que dois apontam um clique de seta e segure e arraste-o para baixo. Isso só vai nos dar um pouco mais de espaço para trabalhar dentro daquela barra de fórmulas. E agora o que queremos fazer é criar quebras de linha entre cada uma das instruções if, cada uma das condições individuais ou critérios que estavam testando. Então, coloque o cursor logo antes disso. Primeiro, se, depois da vírgula e segurar ALTs enquanto você pressiona Enter, isso vai em frente e mover a corrida. Essa fórmula até a próxima linha. A mesma coisa aqui antes da próxima. Se declaração Ault entrar e, em seguida, mais uma vez para a final. If declaração Ault digite. Então agora podemos ver muito mais claramente. Ok, estamos testando os valores em G para escrever no primeiro teste. A metragem quadrada é maior que 35.000? Se sim, vamos rotular aquela loja muito grande. Se isso não for verdade, vamos passar para a próxima linha do tamanho maior que 30.000. caso afirmativo, rotular em geral. E este é o tipo de como estes aninhados se as declarações funcionam é que eles progridem através de uma série de verdadeiros testes falsos. E então, se nenhum desses ar verdadeiro, você tem esse tipo de captura todo valor falso. E eu suponho que nós poderíamos realmente baixar isso para suas próprias ovas, bem como, porque esse é o resultado final. O rótulo final, que é outro. E lá você não tem nós pressionamos, entramos e aplicamos isso para baixo. Nada deve mudar, com exceção do fato de que tornamos nossa fórmula um pouco mais legível, um pouco mais fácil de usar. Então lá vai você, adicionando linha quebra suas fórmulas usando tudo para entrar. 4. Convergência unidades de medição: Então esta próxima demo é uma que eu estou realmente animado porque é uma função que eu realmente descobri pela primeira vez alguns meses atrás. Chamado Convert, podemos falar sobre a mudança de suas unidades de medida usando esta função de conversão Então a fórmula age exatamente como soa permite que você altere os valores de uma unidade de medida para outra. Então você pode ter dados que estão em libras, e você precisa converter em quilogramas. Talvez você tenha leituras de temperatura e Celsius, e você quer Fahrenheit dado aplicar, converter para campos de data ou hora, convertendo minutos em segundos sem ter que multiplicar por 60 ou adicionar constantes extras em suas fórmulas. Então vamos falar lá algumas demonstrações diferentes, uma das quais será a temperatura média registrada em Fahrenheit. E o que vamos fazer é simplesmente usar essa função de conversão. Você verá uma caixa pop-up com todas as suas opções, e o que é importante notar é que você não verá a lista completa de unidades de medida toda vez que essa lista for dinâmica, e isso vai depender da sua unidade de. Então, neste caso, as únicas opções que eu vejo, dado que eu inseri Fahrenheit. As unidades de temperatura são Celsius, Fahrenheit Kelvin e os outros, que eu não estou familiarizado com, mas suas opções específicas de temperatura. Então, é um simples é selecionar a opção que você deseja neste caso, o rótulo do mar para Celsius. E lá vai você. Você criou uma nova coluna calculada convertida. Casos de uso bastante simples aqui. Número um. Fazer essas conversões rápidas sem a necessidade de procurar taxas de conversão ou calculadoras e, em segundo lugar, converter valores específicos de data ou hora sem precisar adicionar constantes de fórmula. Vamos entrar na nossa pasta de trabalho do Dicas profissionais e fazer algumas conversões. Então, do seu índice, estava olhando para a seção de dicas de fórmula dela aqui. Procure a demonstração de conversão de medição PressLink, e quando você pousar nessa folha, você verá duas tabelas diferentes. Os dados têm temperaturas mensais médias aqui, que são registradas em termos de Fahrenheit Coluna B. Nós vamos usar converter para transformar esses valores Fahrenheit em Celsius, e nós também temos alguns indivíduos aqui com métricas de altura e peso, e temos altura em centímetros e peso em libras só porque os EUA gostam de confundir as pessoas e não usar o sistema métrico, então vamos transformar esses centímetros em dois centímetros eram, por sua vez, os quilos em quilogramas . Então vamos começar a temperatura em Celsius, vamos começar com um igual a converter Abrir o príncipe é o número que queremos converter vidas aqui e ser três, e vamos converter de Fahrenheit. Então, se você conhece o rótulo, você pode simplesmente digitar f entre aspas ou você pode pesquisar. Para ser honesto, eu não tenho certeza que ordem estes, Aaron e eu não sabemos uma maneira rápida de procurar, mas aqui é meio que no meio um pouco em direção ao fundo. Em seguida, você pode clicar nessa opção ou clique duas vezes ou guia para puxá-lo para a fórmula. E então o último argumento é, que você converte Fahrenheit? E agora, pelo menos ele meio que encolhe dinamicamente essa lista no reboque de Lee, as opções específicas de temperatura. Então Celsius é o único que eles precisam desta vez todos tablet em perto, o parêntese e pressione OK, e aí você tem isso. Então 41,7 graus Fahrenheit é cerca de 5,39 graus Celsius. E eu verifiquei estes com uma calculadora de conversão apenas para ter certeza Mas você pode fazer o mesmo se você gosta e clique duas vezes e sua atrás. Convertemos todas as nossas temperaturas Fahrenheit em Celsius com o clique de um botão . Agora vamos passar para alturas e pesos, então vamos começar com altura. Vamos converter polegadas em dois centímetros. Então é igual a converter. Estamos convertendo F três de polegadas. Procurem um pouco por isso. Aí está, eu sou tablet e a unidade que queremos converter essas polegadas muito bem. Aqui estão as opções. Tem metros, Miles? Milhas náuticas de volta em centímetros, pés, jardas, Angstrom. Mas você vai notar que não vemos centímetros aqui. Fato. A opção mais próxima que temos é medidores, então você pode estar tentado a dizer, tudo bem, bem, eu não sou uma sorte. Vamos soltar metros, fechá-lo e depois multiplicar por 100 para transformá-los em centímetros, que lhe dará a resposta certa. 83 polegadas é 210,2 centímetros. Mas aqui está o que eu descobri, é que mesmo que a opção não exista em muitos casos, digitar a abreviatura funcionará independentemente. Então eu vou dizer, Ouça, Ouça, eu não quero metros eu quero centímetros que é universalmente observado como cm colocar isso em, aspas fechar minha fórmula pressione enter e eles vão obter a resposta correta sem ter que introduzir essa constante na multiplicação por 100 em nossa fórmula. Então lá vai você, aplique-o para baixo. E agora, caso muito semelhante vamos lidar com a conversão de libras dois quilos igual a converter o número que estava convertendo sua célula h três Nós estamos indo de libras, Sra. Libras Mass El bien Double click, e estamos convertendo em quilogramas que não vivem nesta lista. Temos gramas. Assim como antes, uma opção poderia ser usar gramas e, em seguida, dividido por 1000 para se transformar em quilogramas. Ou podemos simplesmente dizer, Ei, o símbolo para quilogramas é K g. Feche o parêntese. Lá vai você. Temos a resposta correta. £260 é igual a pouco menos de 118 quilogramas. Então lá vai você algumas demonstrações de como você pode usar esta função de conversão super útil para mudar suas unidades de medida 5. Ferramentas em tempo real com HOJE e AGORA: Tudo bem. Esta próxima demo é divertida. Vamos usar funções voláteis hoje e agora para adicionar tempo real sem forma para nossas pastas de trabalho para que hoje e agora fórmulas basicamente retornem a data e hora atuais . E porque eles são funções voláteis, isso significa que eles automaticamente re calcular com qualquer mudança de pasta de trabalho. Agora, se por alguma razão você não quer que eles mudem, você só quer carimbar a data ou hora fixa atual. Você pode usar o atalho de ponto-e-vírgula de controle para retornar o dia atual ou os semi dois-pontos do turno de controle para retornar a hora atual. E ao contrário das funções de hoje e de agora, elas ficarão codificadas. Isso não vai mudar dinamicamente. Agora, para esta demonstração, vamos construir um par de ferramentas de ritmo de projetos bem rápidas. E no primeiro caso, vamos usar uma função Today para construir um rastreador de projeto com base nos dias passados e nos dias restantes. E então usaremos uma função agora para construir uma ferramenta semelhante baseada em horas passadas e horas restantes para projetos de curto prazo. Agora, casos de uso comuns. Sempre que desejar exibir a data ou hora atual em uma célula de planilha, você pode usar hoje ou agora e criar ferramentas como essa, como ferramentas de programação ou linha de tempo que deseja atualizar com base no dia ou hora atual. Então vamos saltar para o Excel e realmente praticar trabalhar com essas funções hoje e agora. Tudo bem, Então, a partir de sua guia de índice, procure por isso hoje e agora Demo em sua seção Dicas de fórmula e vá em frente e pressione link para saltar para aquela guia verde. E a partir daqui você verá que eu construí um modelo de espaço reservado em branco para essas duas ferramentas de rastreamento de projeto que vamos construir, e o primeiro 1 será baseado na função Today. Então, selecionando Célula C 4 se usássemos o controle semi cólon. Esse atalho irá cair no dia atual, e esse dia atual está correto. É 16 de outubro, mas o problema é que, se eu voltar amanhã ou na próxima semana, este valor continuará a ser 10 16. Não é dinâmico. É estática e codificada. E isso não é o que eu quero aqui, porque queremos construir uma ferramenta dinâmica que muda com base no dia atual e hora atual. Então, por causa disso, eu vou usar a função de hoje, e eu nem preciso inserir nenhum argumento. Basta abrir e fechar os parênteses, pressione enter. E lá vai você. Agora temos o mesmo valor. 10 16 2018. Mas mudará todos os dias que eu acessar esta pasta de trabalho. Então eu deixei cair em uma amostra, início, data e data final aqui. Você pode alterar esses valores como achar melhor se quiser explorar e jogar com isso. Agora, uma coisa que é importante saber sobre datas do Excel é que cada data, assim como esta tem um valor de data subjacente. É um número inteiro. E esse número representa o número de dias que se passaram desde o início do tempo, apenas 1º de janeiro de 1900. E porque cada data é apoiada por esse valor de data número inteiro, isso significa que podemos aplicar operações matemáticas e estatísticas simples contra células de data para fazer coisas como funções de inteligência de tempo. Então, para dar um exemplo disso, se quisermos calcular a duração do projeto em termos de dias, podemos simplesmente subtrair a data de vencimento e a data de início. Isso vai nos dizer tudo bem. 728 dias em todo o projeto. Linha do tempo. Agora, mesma lógica para as outras fórmulas. Número de dias que se passaram. Bem, isso é apenas a data de hoje, menos a Data de início do projeto C quatro menos C seis e os dias restantes é um simples como a data de vencimento menos a data de hoje C sete menos C quatro. Quando você pressionar OK, você verá este gráfico de rosquinhas ser preenchido, o que agora nos diz. Ok, você está 288 dias neste projeto que representa 40% de toda a linha do tempo do projeto. Boa maneira de manter o ritmo do progresso, especialmente para projetos de longo prazo como este. Agora, o único ajuste que eu vou fazer aqui é que se nós preenchemos uma data de vencimento que já passou como 1º de outubro de 2018, por exemplo, você vai ver algumas coisas estranhas aconteceram. Temos um dia negativo restante que é traçado no gráfico e uma maneira estranha de evitar que isso aconteça. Vamos em frente e fórmula modificadora aqui e vender. Veja 11 e eu vou colocar o cursor logo após o sinal de igual e acrescentou se declaração simplesmente para dizer se a data de hoje é maior do que a data de vencimento. A data de vencimento já passou em uma vírgula para o nosso valor. Se isso é verdade, e se os valores verdadeiros, se a data de vencimento passou, então nós vamos apenas inserir um zero porque não há dias restantes neste projeto comum mais uma vez para o valor de false. Caso contrário, vamos executar nossa fórmula C 7 menos C 4 como antes. Feche a princesa pressione Enter. E lá vai você agora, ou enxertar tipo de tampas a 100%. E podemos ir em frente e adicionar qualquer data que você quiser no futuro e fazer 11. 25 2019, por exemplo, Esse caso foram 42% do caminho através desse projeto. Então esse é um bom exemplo de como usar a função Today com cálculos de números inteiros. Agora vamos ficar um pouco mais granulares e praticar trabalhar com datas e horários também . E porque queremos a data e hora, não apenas a data como um número inteiro, vamos usar a função agora novamente. Sem argumentos de fórmula apenas abertos. Feche esses parênteses, pressione enter e você verá que ele me dá um valor de 10 16 2018 15 42. Isso é tempo militar para 1542 PM e lembre-se que isso não está apenas me dando apenas o componente do tempo . Está me dando a data e a hora juntos. Então eu tenho exatamente como antes eu entrei em algumas restrições iniciais para este projeto. Vou começar a hora das 9 da manhã. Hora das 5 da tarde, então estamos lidando com uma linha de tempo muito mais curta aqui dentro de um único dia. E a única coisa que eu preciso fazer antes que ele possa seguir em frente é realmente tirar o tempo disso agora. Caso contrário, não serei capaz de fazer comparações diretas com esses valores. 9 da manhã e 5 da tarde. Aqui, na cela C 21, quero que esta célula devolva apenas o componente de hora dessa data. Vou te mostrar duas maneiras de fazer isso. O primeiro está usando função de tempo Excels, e ele olha para uma hora em colocar uma entrada de minuto e uma segunda entrada, e eu vou obter essas entradas usando funções de tempo e apontando para o meu agora. Então, o tempo que eu quero retornar é baseado no nosso atual que é a hora de agora o minuto atual, que é o minuto de agora, e o segundo atual, que é o segundo atual de agora. Feche entre parênteses, Centro de imprensa. Isso traduz toda a cadeia de data e hora em apenas o relógio apenas 344 PM, que é exatamente o que eu quero agora. Há uma maneira mais rápida de fazer isso, mas requer um pouco de compreensão mais profunda de como essas funções de data e hora e valores de data estão realmente funcionando. A questão é que os dias individuais e o Excel serão tratados como números inteiros, certo? Se eu clicar com o botão direito nesta data, vender 10 16 2018 formato, as células vão para a categoria geral. Essa é uma boa maneira de ver como é o valor real da data subjacente. 43389 Novamente, esse é o número de dias que se passaram desde 1º de janeiro de 1900. Note que é um número inteiro se eu comparar isso, então isso é cancelar para não aplicarmos esse formato. Vamos comparar isso com a função agora, que se lembra, é o dia atual, além do relógio também. Consegue adivinhar como isso vai parecer? Vá em geral. Observe que é o mesmo valor de data com valores adicionais após o ponto decimal. E isso porque os tempos são tratados como frações de dias e frações de dias. Em termos de data, os valores parecem apenas frações de números inteiros. Então, se quisermos apenas o relógio, que seria apenas o 65577 seguindo o número inteiro, tudo o que precisamos fazer é literalmente escrever uma fórmula que leva o cálculo agora e subtrai o dia, que é o número inteiro, e você é deixado com apenas o restante, que um minuto se passou. Então agora é 346 PM maneira ligeiramente mais fácil de fazê-lo, mas novamente, ele requer esse tipo de compreensão mais profunda dos valores de data. Tudo bem, então de volta ao projeto, tudo o que precisamos fazer agora para levar isso para a linha de chegada é calcular o comprimento do projeto em horas, que é simplesmente o tempo devido menos dois horários de início. 19 milhas, 18 horas passadas será nossa hora atual menos a hora de início. Isso é exatamente como aqueles cálculos de data que acabamos de fazer acima e, em seguida, por último, mas não menos importante, as horas restantes vai ser o tempo devido menos o tempo atual C 19 menos C 21 Pressione enter e lá vamos nós. Então nosso projeto começou às nove. Vai acabar às cinco. Atualmente, são 346, o que significa que só me restam uma hora e 13 minutos em 85% do caminho. Através deste cronograma, é melhor eu começar a quebrar se eu quiser terminar este projeto agora. Por último, mas não menos importante, queremos criar essa mesma modificação se declaração para controlar casos em que já passamos o tempo devido. Podemos fazer isso usando exatamente a mesma metodologia aqui. Se a nossa hora atual em C 21 é maior do que o tempo devido. Se passarmos o devido tempo, então não restam horas no projeto dela. Caso contrário, calcular C 19 menos C 21. Então esse foi um curso intensivo muito rápido em valores de data do Excel e funções de hoje e agora. Mas espero que isso esteja lhe dando algumas boas idéias de como você pode usar essas funções voláteis para criar seus próprios cálculos em tempo real. 6. Ferramentas de auditoria de Fórmula: Esta próxima dica é tudo sobre encontrar e corrigir erros em sua planilha usando ferramentas de auditoria de fórmulas . Agora, na guia fórmulas, você encontrará um grupo de ferramentas individuais na categoria de auditoria de fórmulas que foram projetadas para ajudá-lo a rastrear referências, avaliar sua fórmula, cálculos e, finalmente, diagnóstico e corrigir quaisquer erros na sua worksheet. Então vamos falar sobre todas essas opções diferentes nesta demo. Vamos começar rastreando a Independência da Precedência, que basicamente desenha setas para qualquer célula que tenha impacto ou impacto pelo valor selecionado, como uma relação pai filho. E aqui está um exemplo de como isso se parece neste caso, que vai falar lá em nossa demo. Estamos rastreando a precedência que impacta esse dinheiro para valores de despesas fechadas e mensais para que possamos entender quais células de entrada afetarão esses valores ou essas saídas. Nós também temos um número de outras ferramentas que vamos falar sobre mostrar fórmulas, é simplesmente vai exibir temporariamente quaisquer fórmulas de planilha como texto, e, em seguida, verificação de erros vai verificar a planilha para erros, nos ajudar a rastrear a fonte de volta para quaisquer células precedentes. E finalmente, meu favorito pessoal, a ferramenta de fórmula de avaliação que nos permitirá avaliar cada componente individual de uma função ou fórmula passo a passo. E isso é ótimo para identificar onde uma fórmula pode estar quebrando, especialmente se você tiver uma fórmula muito complexa ou um número de funções aninhadas. Então, uma tonelada de casos de uso práticos aqui para um, simplesmente entendendo como algumas dessas fórmulas e funções complexas podem estar operando número dois. Visualizando, que vende fator em uma determinada fórmula, saída ou venda, e depois três. Rastreando, diagnosticando e, espero, corrigindo a fonte de quaisquer erros que você encontrar para que vamos saltar para a demonstração. Vamos colocar as mãos em nossa pasta de trabalho pro tip e praticar o trabalho com algumas dessas ferramentas de auditoria . Tudo bem, então se você está acompanhando, vá em frente e abra seu livro de dicas profissionais. Procure a demonstração das ferramentas de auditoria de fórmulas na seção Dicas de Fórmula e vá em frente e pressione vinculado para saltar direto para essa planilha. E o que estamos vendo aqui é uma calculadora de propriedades. É o modelo básico que eu realmente usei para avaliar os custos de propriedade e empréstimo, e essencialmente o que está acontecendo aqui é que você tem algumas informações básicas sobre uma propriedade , tem um preço de compra e uma taxa de imposto. Você pode inserir alguns termos de empréstimo aqui como um pagamento inicial e taxa de juros e um termo como E esses valores serão executados através de uma série de cálculos e, finalmente, cuspir o dinheiro necessário para fechar a propriedade e uma estimativa para despesas mensais. Mas, obviamente, não estamos aqui para falar sobre pagamentos de hipotecas e custos de propriedade. Neste momento, estamos aqui para falar sobre a avaliação de fórmulas. Então, com isso, vamos para a nossa guia fórmulas. Dê uma olhada no nosso grupo de auditoria de Fórmula, que contém as ferramentas de que falamos. E vamos começar falando sobre precedência e dependência porque parece muito complicado. Na verdade, é bastante simples, e uma maneira de pensar sobre isso é, você sabe, para este valor de despesa mensal, a célula que eu selecionei isso é meio que o fim do fluxo de cálculo. Este número de despesa mensal não alimenta quaisquer outras fórmulas. Nenhuma outra célula de saída é afetada por esse valor de despesa mensal. Em outras palavras, esta célula não tem células dependentes, e eu posso provar isso clicando em dependência de rastreamento, e eu vou receber esse tipo de mensagem de aviso que diz que não há fórmulas que se referem a esta célula ativa. Mas há células,é claro, é claro, que o serviço incorre para este valor de despesa mensal. Em outras palavras, existem células. Esse ato é precedência para a célula selecionada. E para mostrar esses precedentes, tudo o que eu preciso fazer é clicar nesse botão e ele vai dizer, Sim, Sim, esses cinco valores aqui H 14 a H 18 todos impactam esse número de despesas mensais. Em outras palavras, se você alterar qualquer um desses valores, as despesas mensais também serão alteradas. Mesma história aqui, com dinheiro para fechar, não há dependência. Este é o fim do fluxo de cálculo, mas há uma série de precedência, de modo que o dinheiro para fechar o valor é uma função do preço de compra, pagamento inicial e custos de fechamento estimados. Agora, outra maneira de contar uma história semelhante aqui que podemos remover os árabes é selecionar uma fórmula, vender e clicar na barra de fórmulas ou usar o F para atalho para editar. E o que isso vai fazer. Você não vai obter as setas, mas você ainda verá células selecionadas que são referenciadas dentro da sua fórmula e o que eu realmente gosto sobre essa abordagem é que elas são codificadas por cores, bem como, então você pode tipo de mapeá-las para o indivíduo dentro dessa fórmula, apenas mais uma ferramenta que você pode colocar em seu bolso traseiro para ajudá-lo a diagnosticar e entender suas fórmulas. Então vá em frente, pressione Enter, e nós rastreamos a precedência dessas células. Mas o relacionamento oposto também é verdade, então sabemos que o dinheiro para fechar era um dependente do preço de compra. Portanto, preço de compra é um precedente para fechar o dinheiro, e podemos mostrar isso rastreando a dependência desta célula. E agora o que isso está nos mostrando é que se mudarmos o preço de compra, há 1234 cinco células ou saídas que vão mudar como resultado. Há cinco células dependentes com base neste valor e, ah, uma dica legal que eu não descobri até recentemente é que isso só mostra o primeiro passo do caminho de cálculo quando, por sua vez, alguns destes células dependentes também têm dependência adicional de lá, de modo que os impactos dos preços de compra, montante do empréstimo, custos de fechamento e imposto sobre a propriedade. Mas se clicarmos na dependência de rastreamento novamente agora, podemos ver que há outra camada aqui onde o valor do imposto de propriedade, como mostramos, também afeta essa saída de despesa mensal. Então agora estamos vendo uma espécie de toda a cadeia de eventos e o fluxo de cálculo completo em várias etapas. Uma ferramenta muito poderosa. Vá em frente e remova essas flechas. Vou te mostrar como é a ferramenta de fórmulas do show. Bastante direto. Ele só se estende para que ele possa mostrar as fórmulas reais. Perguntado tipo de texto todo lado a lado. Boa maneira de comparar. Você sabe que vende em uma folha são constantes e quais são fórmulas e, em seguida, poderia simplesmente desativar essa honra. Agora, na minha opção favorita, a ferramenta de fórmula de avaliação. Vamos escolher uma destas fórmulas aqui, , algo como imposto sobre a propriedade. Clique em Avaliar fórmula. Ele vai puxar para cima esta caixa de diálogo e você verá desde o início exatamente a mesma fórmula que você verá em sua barra de fórmulas. Mas o que você pode fazer agora é realmente olhar para o componente sublinhado quando você clicar em avaliar aqui apenas que componente subjacente irá avaliar. Então ele só disse que H três avalia para 499.000 porque H três que o preço de compra e seguida, quando isso é dividido por 1000 que avalia para 4 99 Então, como nós passo através, nós podemos continuar o processo. H quatro é 9,5, 9,5 vezes para 99. Este 47 40,5, dividido por 12 dá-nos que 3 95 pagamento mensal de imposto sobre a propriedade. Então, novamente, ótima maneira de não entender suas fórmulas em um nível mais profundo. E há uma outra característica para essa fórmula de avaliação. Ferramenta que pode ser útil. Mostrar-lhe que vamos para o nosso dinheiro para fechar a fórmula, e vamos avaliar essa. E é bem simples. Fórmula H três vezes H sete mais H 11 h três é para 99 h sete Este ponto para multiplicar juntos. É 99 800. Agora aqui está a coisa que chegamos ao H 11. H 11 contém um valor de 99 80, mas que 99 80 não é um valor estático, codificado rígido. Produz-se a partir de outra função. Então este passo no botão permite que você diga Ok, cada 11 é um pouco mais complicado. Vamos ver como H 11 em si está sendo produzido e indo um nível mais profundo descascando para trás tipo de mais uma camada da cebola podemos agora ver que H 11 é calculado como H três vezes 30.2 pode avaliar esta fórmula tipo de aninhada dentro do nosso original e, em seguida, dar um passo para trás e dizer, OK, é assim que estamos chegando ao 99 80. Então, quando os somamos, chegamos ao nosso produto final, que é 1097 80. Então, aí está. Essa é a fórmula de avaliação. Obviamente, tudo parece muito mais fácil e simples quando as coisas estavam indo bem. Então vamos passar por uma amostra rápida de quando as coisas podem não estar funcionando como você esperaria. Então, digamos que queremos inserir porcentagem de pagamento de 10%, mas nosso dedo desliza chicotes e digitamos 10 p por cento. Você sabe, obviamente algo está errado aqui. Temos quatro células que agora estão cuspindo erro de valor. Este é um bom momento para usar essa ferramenta de verificação de erros aqui, que na verdade percorrerá sua planilha e dirá, Ei, encontramos um monte de erros. Os primeiros na cela J 9. Você clica em Avançar. É outro em h 10 Tem um em cada 14 um em J três e então você terá um monte de opções aqui para cada um dos erros. Ah, a ajuda sobre este erro irá levá-lo para o site de suporte do escritório. Mostrar etapas de cálculo irá realmente levá-lo para a ferramenta de fórmula de avaliação e especificamente para a etapa que produz o erro. E isso aqui vai me mostrar que estamos tentando multiplicar 499.000 que é um valor por essa string de texto cercada por aspas. 10 p percentual que avalia para esse erro de valor que, obviamente, enquanto você o carrega, só vai produzir um erro final no final do dia. Então é fechado que uma outra maneira que você pode usar esta ferramenta de verificação de erros se formos para o próximo erro. O que é isso? Completou. Este é o que queremos despesas mensais. Voltar e verificar erros. Às vezes você obtém essa opção realmente rastreou o erro também. E quando você fizer isso, ele irá preencher essas setas precedentes com uma diferença. Ele vai virar a seta vermelha no estágio onde os erros que ocorrem e esta é uma boa maneira de rastrear as coisas de volta e dizer Ok, primeiro passo está bem. Passo dois está bem. Ponto onde a etapa três deve ser avaliada. É aí que está ocorrendo um erro. Então eu sei que é o valor de entrada antes do passo três que está causando meu problema, que neste caso é vender H sete. É aquela percentagem de pagamento desordenada, e isolamos o problema para podermos ir em frente e fechar isto. Podemos remover nossas flechas e podemos corrigir essa porcentagem para 10% e pressionar enter e tudo está bem com o mundo. Então, um monte de ferramentas lá muito para cobrir, mas realmente útil opções valiosas para manter em seu bolso traseiro se você trabalhar com fórmulas e funções e se destacar. 7. Relatórios de estilo dinâmico, com fórmulas: Tudo bem. Esta dica profissional é sobre o uso de estatísticas, funções e especificamente funções de estatísticas condicionais para gerar relatórios de estilo de pivô. Então você deve estar se perguntando sobre o que diabos eu estou falando sobre alguns “se”. Conde. Se a média se, estas funções de estatísticas condicionais que permitem resumir os dados, dada uma determinada condição ou conjunto de condições. E quando você pensa sobre isso, é exatamente assim que a tabela dinâmica opera, onde essas condições são definidas por rótulos de linha, rótulos de coluna e seus filtros. Então, para dar um exemplo, nós vamos estar olhando para um subconjunto dos dados do filme I M D B aqui e Colunas A a F e o que temos é uma pequena visualização de tabela dinâmica como esta, que basicamente é filtrada para baixo para gênero igual a ação. Temos os cinco principais países por contagem de título, e estamos resumindo três métricas ou valores diferentes aqui. Temos a contagem dos títulos surgiu. Nós temos a parte da coluna de receita, e nós temos a média da coluna IMDB Score, e a idéia aqui é produzir algo como isso, que é essencialmente replicar o mesmo layout de tabela e funcionalidade. Onley usará fórmulas de célula fora do ambiente de tabela dinâmica. Para fazer isso, vamos usar essas estatísticas condicionais de confiança. Funções, renova contagem, ifs para calcular a contagem de título alguns ifs para calcular a receita e média ifs para calcular a pontuação média IMDb. Agora você pode estar se perguntando se você poderia fazê-lo em um pivô sem escrever uma fórmula. Por que você passaria por todo esse trabalho extra e basicamente recriar a roda do zero ? Então, minha opinião é que os pivôs são uma ótima ferramenta para tipo de análise não-estruturada não-guiada, significa que eles são ótimos quando você realmente não sabe o que você está procurando e você quer apenas fatiar e explorar as coisas do dedo do pé. Saiba mais sobre seus dados. Por outro lado, se você sabe como deseja segmentar seus dados e você sabe quais valores você deseja mostrar como parte de algo como um relatório ou painel, usando essas funções de estatísticas permite que você faça isso dentro de células de pasta de trabalho, que lhe dará muito mais flexibilidade para projetar, formatar e planejar seu relatório exatamente como você escolher, especialmente em comparação com a criação de um relatório de estilo de pivô com itens como segmentadores e gráficos dinâmicos , que também são ótimo por suas próprias razões. Então, os casos de uso aqui que eu vou focar em nosso projeto de relatórios dinâmicos personalizados formatados sem usar pivôs e filtrar ou segmentar seus dados brutos com base em um determinado conjunto de critérios, que é exatamente o que essas funções são projetado para fazer. Então vamos em frente à nossa demonstração em nossa pasta de trabalho pro tip e ver se podemos fazer isso funcionar. Ok, então a partir do seu índice, você vai procurar os relatórios de estilo pivô Demonstrar o IP de reinicialização. Esta é uma dificuldade moderada, por isso ajuda a ter um pouco de fundo nessas funções estatísticas. Ah, mas mesmo se você não fizer, espero que ele será capaz de acompanhar bem de perto. Vamos vincular a essa planilha. E assim como eu descrevi aqui, nós temos um subconjunto dos dados do IMDB. Tem cerca de 3700 títulos, e as colunas com as quais temos que trabalhar são os títulos em si, o país da língua do gênero. Eu sou DB pontuação receita e orçamento. E aqui temos uma tabela dinâmica para ir para Rick Tools alterar fonte de dados. Você verá que este pivô está realmente conectado aos nossos dados e colunas A a G. Vá em frente. Pressione OK, E novamente temos aqui é Ah, vista rápida mostrando a contagem dos títulos. Algumas das receitas e a pontuação média do IMDB são filtradas especificamente em títulos de ação. E nós temos um filtro de valor para mostrar os cinco itens principais ou, neste caso, países com base na conta do título. Então pressione ok e aqui acabaram de adicionar um pouco de ah, espaço reservado modelo em branco onde vamos usar essas fórmulas de estatísticas condicionais para basicamente replicar os valores exatos que estamos vendo aqui em cima. Então eu acabei de copiar caiu nos mesmos nomes de país. Eu adicionei uma célula aqui que apenas contém a palavra ação. Por enquanto, você pode transformar isso em validação de dados. Desça, se quiser. Tudo o que vamos fazer aqui é tentar replicar esses valores, então começar com a contagem do título obviamente são resumos. modo de ação aqui é uma contagem. Então, por essa razão, vamos usar uma função count if e porque estamos testando para dois critérios, queremos contar linhas em dois casos. Um em que o país é igual EUA EUA a EUA e onde o gênero é igual a ação porque temos várias condições. Vamos usar a versão plural dessa função contada chamada Count Ifs, e vá em frente e abra o parêntese. E aqui é onde nós apenas alimentamos esta fórmula. Estes pares de critérios intervalos e criterias. Então o nosso primeiro critério é o país. Há valores de país Living coluna d pressione F quatro para bloqueá-lo e os critérios que estamos procurando, o que estamos tentando filtrar dentro dessa coluna D neste caso é o país chamado EUA. Podemos deixar este parente neste caso, ou você pode pedalar com F quatro até que você bloqueou sua coluna, porque os rótulos de país sempre viverão aqui na coluna I. Mas queremos que o caminho para baixo para o Reino Unido e subiu 17 França e Fila 18 e assim por diante e assim por diante. E isso vem para o nosso próximo Criteria Range, que é um gênero Range vive aqui e coluna B F quatro para trancá-lo nos critérios para o gênero vive aqui e vender J 13 e ele sempre vai viver aqui em J 13 para que possamos corrigir essa referência inteira. Feche o parêntese pressione enter. E lá vai você. 700 para títulos ou subiu para nós. A filmes de ação que corresponde são tabela dinâmica acima. E como definimos nossos tipos de referência corretamente, podemos simplesmente arrastar esse valor para baixo e obter 69 27 24 14. Então temos uma combinação. Tudo está bem lá. Agora, vamos seguir uma abordagem muito, muito semelhante aqui para calcular a receita. Exceto que não estamos contando desta vez. Estamos resumindo a mesma lógica exata. Vamos usar alguns “ses”. E a única diferença vai começar com alguns valores de intervalo que queremos somar . Viver aqui na coluna F os valores da receita. E isso iria apenas inserir esses critérios, intervalos e valores exatamente como fizemos antes. Então primeiro criterias país e D critérios é U S A bloco. O segundo critério de referência I é o gênero e ser e o critério final para é ação em J 13. Bloqueá-lo perto do parênteses pressione Enter. Lá vai você. $59 bilhões aplique-o para baixo. 3.991 ponto 766 49 8 43 Boom. Temos um fósforo. Essencialmente, acabamos de fazer o trabalho de uma tabela dinâmica usando essas funções de estatísticas condicionais . Agora, por último, mas não menos importante, queremos a média da pontuação do IMDb. Então você adivinhou. Vamos usar a média dos “ses “desta vez. O mesmo que alguns “ses”. Exceto que em vez de um alcance, nós vamos ligar em um alcance médio. Este caso está ligado. Passar pelo mesmo processo. Os critérios variam de um país. O que os critérios dos EUA variam para o gênero. E queremos que a ação bloqueie, feche pressione Enter e arraste para baixo. 623656 Todo o caminho até 648 E aí está. E agora, essas funções de estatísticas condicionais estão apontando para células que os usuários podem realmente atualizar ou alterar, isso significa que nossos valores neste pequeno relatório que criamos são completamente dinâmicos, apenas como um pivô. Então, se, por exemplo, filtrado em filmes de aventura aqui, você poderia mudar seu critério Cell J 13 para aventura. E lá vai você. Você obtém os mesmos valores correspondentes. Então, nós quase, tipo, construímos nossa própria pequena tabela dinâmica, hum ou relatório de estilo pivots. Pelo menos usando essas estatísticas condicionais, funções como contagem Se cúpula e médias ferramenta realmente útil, especialmente se você precisa construir relatórios ou painéis para resumir seus dados e quer um pouco mais de flexibilidade em termos de o layout e o design. 8. Contando palavras em uma pilha: Esta próxima dica profissional é uma das minhas favoritas. É uma maneira muito inteligente de combinar fórmulas de texto no Excel para contar o número de palavras dentro de uma célula. E qualquer pessoa que esteja familiarizada com funções de texto provavelmente já ouviu falar do comprimento ou fórmula L E N , que essencialmente apenas calcula o número de caracteres em uma string de palavra ou uma string de texto . E isso é útil, mas não é bem o que precisamos aqui, já que estamos tentando calcular contas de palavras inteiras. Então, para fazer o que precisamos fazer, vamos precisar ser um pouco criativos aqui e combinar algumas funções adicionais como substituto e aparar para obter o que precisamos. Então, para nossa demonstração, vamos estar olhando para alguns dados de degustação de vinhos onde temos essas descrições de provadores na coluna F. E nosso objetivo será criar um contador de palavras na coluna G que conta. As palavras da coluna F Simple é que agora o que vai acabar com é algo que se parece com isso, que é um pouco de uma besta. Pode não fazer muito sentido à primeira vista. Não te preocupes, vou guiar-te por cada passo desta função até perceberes exactamente como está a funcionar agora. Casos de uso aqui, obviamente, como estamos falando sobre adicionar contadores em casos em que a contagem de palavras é importante ou analisar campos de texto coisas como cópia do anúncio de pesquisa do blog de tweets em qualquer que possa ser baseado no comprimento. Então, um monte de diferentes lugares práticos onde você poderia usar esta dica. Agora vamos pular em nossa pasta de trabalho de dicas profissionais e realmente construir um contador de palavras próprio. Certo, então estamos procurando a demonstração de palavras para uma de nossas guias verdes. Se você estiver no seu índice, vá em frente e pressione link e saltaremos direto para a aba. E aqui temos nossos dados de degustação de vinhos. Estamos olhando para o nome do vinho e coluna A. A variedade e coluna B tem os nomes de provadores individuais aqui na coluna C e Colin D é o que nos importa com isso. Existe uma descrição escrita real de lá degustação e o que estamos olhando para fazer é usar essas funções de texto aqui na coluna e calcular o número de palavras nessas descrições. Então o que eu vou fazer é quebrar isso pouco a pouco, e então nós vamos montar as peças juntas, a fim de criar o que precisamos. Então, talvez a peça individual mais simples é a função Eliane ou comprimento, e mostrar-lhe o que isso faz. Vamos pegar o comprimento da célula D dois com um simples é que você apenas aponta para uma célula contendo algum texto pressione, enter e retorna 58 que não é a contagem de palavras. É a contagem de personagens. Então, mantenha esse número atrás. Sua mente. Esta descrição original contém 58 caracteres no total. Agora, a próxima peça que vamos usar é uma fórmula de substitutos de culto. Vou sobrescrever essa função de comprimento com substituto. Sempre tenho dificuldade em soletrar isso por alguma razão. E a maneira como esta função substituto funciona, lá vamos nós. Podemos ver um pouco melhor é que vamos apontar para o nosso campo de texto novamente. Direito D para. E eu estava dizendo, o que na string de texto original estamos procurando substituir? Qual é o texto original antigo? Pode ser um personagem individual. Pode ser uma palavra inteira, uma sequência inteira de palavras neste caso, o que realmente queremos substituir é um espaço. Então eu vou abrir aspas em um espaço e fechá-lo e, em seguida, comentar para o próximo argumento, que é ok, essa é a textura. Procurando por agora? O que você quer substituir por esse espaço? E neste caso, é aqui que fica um pouco inteligente. Certo é que na verdade não queremos substituir esse espaço por nada. Queremos nos livrar completamente do espaço e contar ao Excel. Isso é o que queremos. Eu vou digitar duas aspas consecutivas, basicamente, como eu fiz, mas sem o espaço entre eles e porque queremos substituir ou substituir cada instância desses espaços, nós não realmente se preocupam com este último argumento opcional. O número da instância. Vamos fechar o centro de imprensa entre parênteses e o que você acaba com algo assim . Os aromas de baga e cereja eram surpreendentemente resistentes e limpos. É a cadeia de texto que tínhamos originalmente em D dois sem nenhum dos espaços. Então, agora, se formos em frente e modificar essa fórmula, clique logo após o sinal de igual. E se tomarmos o comprimento desta nova string modificada que criamos aprendizes fechados no final Pressione enter 50. Certo, então sabemos que a corda original era 58. Agora sabemos que a versão da string sem espaços é 50. Tomando a diferença desses números, podemos essencialmente contar o número de espaços que retiramos, removemos ou substituímos . Então é um Ziff. Usamos uma função como contagem se o texto é igual ao espaço. Mas, infelizmente, isso não existe no Excel, é por isso que estamos tomando essa abordagem. Então vamos em frente e realmente tomar essa diferença na fórmula novamente. Vou modificar logo após o sinal de igual, pegamos o comprimento da nossa string original de Tu menos o nosso novo comprimento daquela versão substituída da string, e isso nos dará o número oito. Vamos arrastar isto de volta para cá. Então removemos oito espaços e podemos verificar porque esta é uma descrição bem curta. 12345678 espaços foram, de fato, removidos. Mas não estamos lá porque se você contar as palavras, elas são na verdade nove palavras porque cada frase vai começar e terminar com uma palavra. Então sempre haverá uma palavra a mais do que o número de espaços. Ajuste muito simples de fazer. Basta adicionar um para você nunca fórmula pressione enter. E lá vai você. Você tem uma contagem de palavras de 9123456789 e nós devemos ser bons para ir simplesmente clicando duas vezes, aplicando-o para baixo. E lá vai você. Vai levar a contagem de palavras para cada uma dessas descrições. Pode contá-los para confirmar se quiser, mas acredite, eles estão fazendo o trabalho. Agora, a única outra peça que podemos adicionar se quisermos tomar mais uma medida da da segurança da Angus, você poderia chamá-la para explicar casos em que, você sabe, pode haver um espaço principal, você sabe, acidentalmente assim, que indicaria que há 1/10 palavra quando eles são, na verdade, não é para explicar isso. Vamos usar essa função de aparar, e tudo o que vamos fazer é aninhar aqui. Então, em vez do comprimento de D dois, vamos pegar o comprimento da versão aparada de D dois e a mesma lógica, em vez de substituir D para nós vamos substituir a versão aparada F D para bem aqui também. Então eu estou apenas aninhando isso dentro da função aqui. E acho que precisamos de mais uma princesa. E vamos dar uma chance e pressionar Enter. Lá vamos nós. Então agora foi corrigido para nove, que é o que queremos aqui. A mesma coisa se adicionarmos espaços à direita como este, seja um whoops ou dois, três ou quatro, não importa. Essa guarnição vai explicar isso para nós e tirar os espaços de liderança ou de fuga . Então agora podemos aplicar isso. E enquanto um temos um contador de palavras totalmente funcional no Excel. 9. Virada dependente com INDIRECTS com INDIRECTS: Tudo bem. Esta próxima dica profissional foi inspirada por uma pergunta que recebi há algum tempo. Consegui algumas vezes desde então. Qual é? Como faço para criar um menu suspenso ou validação de dados que depende de outro? Então, em outras palavras, como faço para criar um menu suspenso primário e, em seguida, um dependente onde a lista será alterada com base na seleção do usuário? E é uma excelente pergunta e uma ótima oportunidade para usar uma função chamada Indireto. Então esta é uma ponta de quatro estrelas, definitivamente ficando um pouco em direção ao extremo avançado do espectro. Mas deixe-me explicar como isso vai funcionar. Então, essencialmente, podemos usar indiretos combinados com regras de validação de dados para criar essas listas suspensas que atualizam com base em uma seleção de usuário. Então, vamos passar por três passos aqui na nossa demo. Vamos começar criando listas individuais contendo todos os conjuntos de seleções possíveis . Então nós temos nossa lista principal, que neste caso é a temporada ou verão ou inverno. E então temos as listas que poderiam potencialmente preencher a segunda lista ou dependente , que é a lista de esportes específicos de verão e esportes de inverno específicos. Então, uma vez que você tenha criado essas listas de valores exclusivos que capturam essas seleções possíveis , a Etapa dois é transformá-las em intervalos nomeados reais. Você pode usar o gerenciador de nomes na guia fórmulas ou simplesmente digitar o nome na caixa de nome à esquerda da barra de fórmulas. Então, se você olhar para as duas opções no verão primário ou inverno, você vai querer combinar exatamente essa frase. Então, em vez de esportes de verão ou esportes de inverno, estamos usando essas palavras exatas verão e inverno. E a partir daí, tudo o que vamos fazer é criar uma gota. downs são primários e secundários e, em seguida, configurar a origem da lista para o secundário. Descarregue o dependente. Drop down para fazer referência a essa primeira célula como parte de uma função indireta. Vou falar sobre o porquê e como fazemos isso quando entrarmos na demo, mas esse é o resumo rápido da abordagem que estamos prestes a tomar. Portanto, alguns casos de uso comuns aqui criando relatórios dinâmicos com várias entradas de usuário com validação de dados ou talvez mais comumente, configurando suas listas suspensas para impedir que os usuários selecionem combinações inválidas de opções. Então, nesta demonstração em particular, é exatamente o que estamos fazendo. Nós não queremos que o usuário seja capaz de selecionar a temporada de verão e hóquei no gelo ou esqui alpino como o esporte, porque quaisquer células dependem dessas seleções de validação de dados, eles obviamente apenas aparecem como em branco nesses casos. Chega de falar teoria suficiente. Vamos arregaçar as mangas, ir ao livro de dicas profissionais dela e construir alguns dropdowns dependentes. Tudo bem, então, da sua guia de índice, você vai procurar a demonstração de Drops Dependentes na nossa fórmula verde Seção 4 Star Tip. Vá em frente e clique no link Ir para a cidade e o que você verá aqui é que temos alguns dados de atleta olímpico divididos por temporadas e esportes e Collins e estar com os nomes de atleta e venha ver e eu adicionei apenas um tipo de modelo em branco ou espaço reservado onde vamos preencher as nossas informações, a fim de criá-las. Para cair para baixo vende a temporada que é o nosso principal drop down, eo esporte que é dependente drop down. Em outras palavras, queremos que este esporte caiu para exibir uma lista de esportes de verão ou inverno, dependendo do que foi selecionado aqui e vender F três e, em seguida, eu adicionei uma fórmula aqui . Número de atletas. Esta é apenas conta ifs função que diz, Ok, vamos contar as linhas dadas estes dois critérios para que possamos ver o número de atletas para um determinado esporte e temporada de modo que vamos começar Passo um é simplesmente preencher todos os nossos listas, certo? Vamos começar com a temporada para uma queda primária apenas dois valores aqui. É um simples é aquele inverno de verão. Digite-os manualmente. É provavelmente a maneira mais rápida e como obter os esportes de verão exclusivos e esportes de inverno algumas maneiras de fazê-lo. Poderíamos usar a opção de filtro avançado, que recuperou outra dica profissional. Ou neste caso eu vou fazer é filtrar meus dados fonte aqui. Vamos apenas olhar para o minuto de verão. Agarre Selby e controle Shift Seta para baixo para selecionar toda a Coluna de Controle de Valores. Veja para copiar, voltar para cima, selecione I para e controle V para Colar e, em seguida, na minha cidade de dados, vai remover duplicatas e continuou apenas com essa seleção atual e calma. Eu pressiono remover. Ok. E lá vai você. Temos 36 esportes exclusivos de verão listados aqui, e vamos seguir o mesmo processo para os esportes de inverno. Então, em vez de filtrar no filtro de verão no inverno, pegue a primeira mudança de controle de valor. Seta para baixo Controle. Veja para copiar. Eu vou rolar para cima, e nós vamos precisar desfiltrar isso para que possamos ter acesso a linha para novamente. Limpe esse filtro. Selecione J para controlar V para colar dados duplicados removidos e lembre-se de continuar apenas com essa seleção atual e pressione para remover. Então lá estavam indo para obter 15 esportes de inverno únicos. Então, aí está. O primeiro passo está completo. Temos a nossa lista única de opções. Agora, segundo passo é realmente dar a essas listas nomes significativos. Então o que vamos fazer é selecionar todos os nossos esportes de verão primeiro todo o caminho através da luta livre. A maneira mais fácil de fazer isso não é passar por fórmulas nome definido, mas simplesmente digitar o nome aqui na caixa nome à esquerda da barra de fórmulas. E lembre-se, eu quero nomeá-lo exatamente consistente com o meu drop down principal para o verão entrar na mesma coisa com o inverno. Selecione esses esportes neste inverno entrar e esse é o passo dois. último passo é realmente configurar os nossos descidas aqui em F três e F cinco. Então, nosso 1º 1 é uma seleção experiente. Muito, muito simples. Vamos à validação de dados. Vamos permitir uma lista aqui e essa lista. Contém duas temporadas imprensa. OK, então agora você pode selecionar o verão nosso inverno. Lá vai você. Agora aqui está a chave. Tudo depende disso, certo, porque esta certo, seleção de esporte e F 5 quando vamos para validação de dados, vamos permitir uma lista como normalmente fariamos. Mas em vez de selecionar um intervalo que criaria uma referência fixa para organizar, isso não mudaria, não seria dinâmico. É aqui que precisamos usar essa função indireta. E o que estamos tentando fazer é indireta e referenciar a célula F 3 agora antes que eu atinja. OK, o que estamos fazendo aqui é dizer ao Excel. Olha para a cela F três. Você vai ver uma palavra ou verão ou inverno usando indireto o que estamos dizendo XLs que há mais a ela. Não é apenas uma string de texto que tem a palavra verão ou inverno. Essas palavras são mais significativas. Seus objetos de pasta de trabalho definidos reais, portanto, usando indiretos, estavam dizendo Excel Toe realmente tratar aqueles como os intervalos nomeados que eles são. E quando pressionamos OK, tem verão selecionado? Dê uma olhada que a lista contém todos os nossos esportes de verão e nenhum de nossos esportes de inverno , certo? E se mudarmos isso para inverno agora, dê uma olhada. Alpine Ski Biathlon Bobsleigh Estes são nossos esportes de inverno todo o caminho até patinação velocidade , modo que a função indireta é capaz de reconhecer esta palavra como faixa sem nome e preencher a lista de taxas em conformidade. Então, agora dê uma olhada. Temos nosso pequeno contador de atleta nos dizendo que havia 645 esquiadores alpinos nos Jogos de Inverno . Podemos mudar para o verão e selecionar um dos nossos esportes de verão como atletas de beisebol 191 . Então, aí está. Esta é uma das minhas dicas favoritas no curso, porque não é tão difícil, mas é extremamente poderosa e uma ótima opção quando você está criando relatórios e painéis dinâmicos . Então lá vai você, criando validação de dados dependentes, drop downs usando a função indireta 10. Hiperlinks entre planilhas: Tudo bem para esta dica profissional. Quero mostrar-lhe como criar links dinâmicos de pasta de trabalho usando a função de hiperlink. Então o que vamos fazer aqui é usar hiperlink para conectar usuários diretamente a locais específicos de pasta de trabalho ou planilha. Agora, se você está seguindo junto com o curso, isso deve soar muito, muito familiar porque isso é exatamente o que eu fiz na guia Índice na pasta de trabalho pro tip, você verá uma fórmula como este que é impulsionado por essa função de hiperlink, e que irá apontar diretamente para vender um para uma determinada guia. Nesse caso, eles vão para a guia Opções. Agora, em teoria, isso é bastante simples, mas vocês notarão que é uma ponta de quatro estrelas. Então está no extremo avançado da escala. E a única razão pela qual eu rotulei isso como uma dica avançada é porque a sintaxe é extremamente específica e bastante inflexível. Então eu incluí um exemplo aqui. folha de hiperlink uma célula uma com um rótulo de nome de link e observe todos esses caracteres especiais lá. As aspas simples, o hash, o ponto de exclamação. Todos esses símbolos são críticos. E se você não tiver todos no lugar exato. Esta função não funcionará. Então, realmente mais do que qualquer coisa, fazer hiperlink funcionar é mais uma questão de atenção aos detalhes do que qualquer outra coisa em uma coisa a observar. Aqui, você pode digitar uma referência de folha real como o exemplo neste marcador. Ou você pode substituir isso por uma referência de célula como eu fiz na folha de índice . Quando você faz isso, isso torna as coisas um pouco mais complicadas porque você essencialmente precisa reverter a sintaxe exata usando Can Canton oito funções toe realmente conectam esse ponto de exclamação hash e aspas. Então, os casos de uso aqui, por exemplo, adicionando ferramentas para ajudar os usuários a navegar em pastas de trabalho maiores e complexas, como , por exemplo, uma pasta de trabalho profissional que tem cerca de 100 guias de informações nele. Hum, e dois. Essa é uma coisa que realmente faz bastante, criando relatórios que têm links para detalhes adicionais ou fontes de dados. Então talvez você tenha fornecido um relatório para um cliente. Essa é a visão resumida das linhas superiores. Você pode adicionar um link que pode ser controlado para uma planilha muito mais detalhada. Se o usuário quiser acessar esse nível de detalhe Ferramenta realmente útil nesses tipos de casos. Então vamos em frente e construir nosso próprio hiperlink na pasta de trabalho de dicas profissionais. Certo, então, apropriadamente, aqui estamos na nossa folha de conteúdo, que por acaso inclui dezenas e dezenas de exemplos desta demonstração de hiperlink que estamos prestes a praticar. Neste caso, vamos realmente ir para a demonstração de links de pasta de trabalho dinâmica aqui mesmo em nossas dicas de fórmula. E você adivinhou. Vamos hiperlink direto para aquela conta. E o que temos aqui é como uma pequena versão daquele índice que vamos praticar construir nós mesmos. Então a idéia vai ser preencher a coluna H com links baseados nesses nomes de pasta de trabalho ou nomes worksheet e coluna B. Mas antes de tentar ficar todo louco e avançado a partir do primeiro passo, vamos tentar apenas escrever um tipo simples de mais função básica de hiperlink para uma guia próxima. Então contar palavras está por perto. Vamos trabalhar com esse primeiro para podermos tentar. Apenas digitando é igual a localização do link de hiperlink vai ser contar palavras. Vender uma vírgula para o próximo argumento, que é o nome amigável como link. Feche aspas, feche o parêntese pressione enter. As coisas parecem boas, certo? Se apenas selecionarmos essa célula, estamos hipervinculando para a folha de palavras de contagem. Especificamente célula um e nós rotulamos link, e ele criou um link para nós. O problema é que, quando clicamos nele, obtemos isso não pode abrir o erro de arquivo especificado. E basicamente o que precisamos dizer ao Excel é Ei, Excel, não estamos tentando abrir um arquivo separado. Estamos tentando acessar apenas uma de nossas folhas ou guias dentro deste arquivo. Então, para dizer ao Excel, é isso que queremos. Precisamos adicionar alguns caracteres aqui, e logo após o Perrin aberto, vamos adicionar aspas duplas seguidas de uma marca de hash. E então no final, logo após a referência da célula, um nós vamos fechar outra aspas duplas e então pressionar Enter e verificar isso. Agora, quando clicamos no link boom, uma folha de palavras de uma contagem, funcionou perfeitamente. Então, adicionar essas aspas para cercar a referência da planilha, bem como essa marca de hash disse ao Excel que estamos realmente tentando navegar para um local da planilha dentro deste arquivo. Então vamos voltar para a nossa folha de links de pasta de trabalho dinâmica, e poderíamos seguir a mesma abordagem e fazê-lo, você sabe, mais 15 vezes para preencher o resto desses links. Mas obviamente isso é um pouco lento. É um pouco manual, e se você precisava preencher vários 100 links, essa não é realmente uma opção viável. E porque temos nossos nomes de worksheet aqui na coluna B, devemos ser capazes de simplesmente referenciar esses nomes como parte de nossa função de hiperlink. Mas aqui está a pegadinha. Nós simplesmente tentamos substituir essa referência de folha aqui por uma referência de célula como B 11 impressionado. Ok, nós nos ligamos diretamente para a localização da célula dentro da folha atual é excelente não lê-lo como o nome de uma guia. Então nós vamos precisar fazer é ficar um pouco inteligente, um pouco astuto com Can Canton oito funções para forçar a sintaxe que precisamos. Então, preste atenção colocar o seu por ALS populares. Vamos ficar bem e ampliados aqui porque estamos prestes a ficar bem detalhados com essa sintaxe e excluir o que tínhamos, e vamos começar com um parêntese aberto de tela limpa Siga-me de perto aqui. Aspas duplas, marca de hash. Citação simples. Então isso parece familiar até agora. É aqui que fica um pouco estranho. Vamos adicionar outra citação dupla um comercial que é um cifrão. E comercial em uma referência, a célula que queremos ser 11 e outra e por cento fechar a aspas duplas fora em outra única citação um ponto de exclamação, e então eles vendem referência que queremos. Então, se queremos deixar as pessoas fora de sua célula um que faz sentido, obter um tipo aqui e, em seguida, fechar mais uma aspas duplas vírgula sobre o nosso nome amigável ou rótulo como link. aspas, feche o parêntese e pressione enter. Agora vou passar o mouse sobre isso de novo para que você possa pausar sua tela. Certifique-se de que você cortou todos esses caracteres individuais no lugar. Aqui está a coisa. Eu adicionaria espaços para você torná-lo um pouco mais legível, mas mesmo isso quebraria a função tão, muito frágil sintaxe. E é por isso que esta é uma ponta de quatro estrelas. Mas agora vamos dar uma chance. Clique com o botão direito na célula do boom. Um na guia de contagem de palavras agora aqui está o momento da verdade, certo? Vamos voltar e simplesmente pegar isso e arrastá-lo para mais alguns. Tão dependente. Drops. Clique no link. Lá somos dependentes. célula suspensa vincula uma pasta de trabalho dinâmica. Essa é a cela em que estamos atualmente. Célula de boom A um índice e corresponde a um funcionando perfeitamente. Então agora nós basicamente apenas automatizamos o processo de ter que criar todos esses links um por um. E agora, como qualquer outra fórmula, você pode simplesmente clicar e arrastar e aplicá-la em todos os outros lugares. Deixe que as referências alterem para usuários de unidade com base nas folhas de pasta de trabalho e coluna B para que apenas sobre faz boa maneira de usar a função de hiperlink para direcionar os usuários para locais de planilha específicos em seu arquivo Excel. 11. Arrancamentos de "Partitura fuzzy": Eu quero tomar alguns minutos e falar sobre algo que eu chamo fuzzy jogo v pesquisas. E, especificamente, vou descrever como encontrar intervalos de valores em oposição a valores de pesquisa exata usando esses tipos de pesquisas. Agora correspondência fuzzy, que é mais formalmente conhecido como correspondência aproximada, pode ser usado para determinar onde um valor de pesquisa específico cai em um determinado conjunto de intervalos. Então a idéia aqui, o ponto chave é que em vez de tentar encontrar a correspondência exata para o nosso valor de pesquisa, o que estamos fazendo neste caso é procurar a correspondência mais próxima que seja igual ou menor do que o valor de pesquisa. E para fazer isso acontecer, tudo o que fazemos é modificar esse último argumento e a função de pesquisa. Observe o intervalo de pesquisa inserindo um valor verdadeiro ou o número um, que significa correspondência difusa ou correspondência aproximada. Agora, como a maioria das coisas no Excel, isso é melhor ilustrado com um exemplo contextual Rheal. Então dê uma olhada neste conjunto de dados aqui. Temos um monte de produtos sendo vendidos na coluna B. Temos uma quantidade de vendas ou volume de ordem e coluna C. Mas o preço de varejo e D A idéia é, estamos tentando calcular o nível de desconto na coluna e com base nas vendas, quantidade ou volume da ordem. E essencialmente, a idéia aqui é que pedidos de maior volume receberão um desconto maior. Assim, para pedidos de 0 a 5, não haverá desconto aplicado. 6 a 10 seria um desconto de 5%, até encomendas superiores a 50 unidades, que ganharia um desconto de 20%. Então, para fazer essa lógica realmente funcionar, vamos usar uma função de pesquisa V, e vamos dizer, Vamos procurar o valor na coluna. Veja a quantidade. Vamos procurá-lo nesta tabela de desconto de pedido. Especificamente colunas H a. Queremos a segunda coluna e aqui está a chave. Estamos usando o tipo de pesquisa de intervalo verdadeiro, que é correspondência aproximada. E quando entramos nessa fórmula, o que está acontecendo agora é que X Ellis pedalando do topo da coluna H para baixo novamente, não apenas procurando a correspondência exata neste caso 25, mas a correspondência mais próxima que é igual a 25 ou inferior a 25. Então, neste caso, ele pára em 21. O índice de coluna nos diz para passar para a próxima coluna na matriz de tabela, e, em seguida, que 15% desconto. Esse é o valor que é puxado para cima em nossa coluna de descontos. E. Então, o que fizemos essencialmente não corresponde exatamente ao valor da quantidade, mas identificar qual intervalo de valores que a quantidade cai em nossa matriz de tabela ou uma tabela de pesquisa . Então dica muito útil para ter no bolso de trás. Uma coisa a notar que é muito crítica aqui. Para fazer isso funcionar corretamente, seus agrupamentos devem ser definidos pelo valor mínimo, que é o número mínimo de ordem que, você vê que eles estão na coluna H na tabela de pesquisa e esses mínimos de ordem. Esses valores mínimos têm de ser ordenados ascendente de pequeno a grande porque lembre-se de como funciona uma pesquisa em V. Vai começar no topo. Vai ser célula por célula até encontrar a combinação mais próxima. Então a ordem é crítica aqui. Agora esta é uma ponta de quatro estrelas. É relativamente avançado. Se você não tem um bom controle na visão tradicional, procure funções ainda como isso pode ser um pouco confuso. Eu cobrir pesquisas V, bem como H Lookups Index Match offset, etcetera em minhas fórmulas e funções. Claro, vamos cobrir mais dos fundamentos em profundidade, por isso definitivamente encorajá-lo a verificar isso. Agora, alguns casos de uso aqui podemos definir algo como uma taxa de comissão variável baseada em um volume de vendas de agente. Ou podemos fazer algo como estamos mostrando aqui calculando descontos de lágrimas com base em algo como uma quantidade de compra. Então vamos saltar para a pasta de trabalho de dica profissional e praticar a escrever uma das correspondências difusas ou visualização aproximada. Procurar funções Tudo bem, Então, do nosso índice estavam na seção Dicas de Fórmula. Procure a demonstração de pesquisas de correspondência difusa e vá em frente e pressione o link para sair para aquela guia verde . E o que temos aqui basicamente encomendar registros com diferentes idéias de ordem em cada linha, e estamos olhando para qual produto foi vendido. Quantidade vendida, preço de varejo, que não vamos usar neste exemplo, e a chave é a coluna E. Essa coluna de desconto que precisamos preencher usando a função de pesquisa Avi que faz referência esta ordem e tabela de desconto aqui. Agora. Uma coisa para notar esta coluna h é a que realmente nos preocupamos, e este é o único que vai servir a entrada para a função. Observe que ele é definido com base no valor mínimo em cada grupo ou bucket, e é classificado ascendente esses rótulos aqui e coluna G. Estes não são realmente usados na função. Acabei de adicioná-los para um pouco de legibilidade, então pulando para vendê-lo para vamos pensar sobre como podemos abordar este problema certo? Há algumas maneiras de resolvermos isso. Você poderia usar um monte de declarações if aninhadas e dizer, Você sabe, se a quantidade é menor que X e maior do que por que, em seguida, desconto é igual a 10%. Isso é totalmente bom. Neste caso, você estaria aninhando. Cinco condições diferentes na mesma fórmula. Vai ficar um pouco longo, um pouco confuso, e você teria que modificar cada uma dessas condições sempre que quisesse mudar as coisas. Então essa não é realmente a opção mais eficiente aqui. E se usássemos uma correspondência exata para procurar o tipo certo da abordagem padrão? Nós dissemos: “ Vamos procurar a quantidade e sentar para “Vamos tentar encontrar essa quantidade”. Você está na nossa mesa cada um a três por seis, pressione quatro para trancar isso. E o que queremos é o desconto, que vive na segunda coluna desse array de tabela. E então, se fizéssemos nosso tipo padrão de falso ou zero para significar correspondência exata e fechá-lo, poderíamos pressionar, entrar e começar a meio que aplicar isso para baixo. E o que você verá é que alguns campos acontecem a preencher corretamente. Mas aqueles ar em Lee os que eram exatamente iguais aos mínimos de ordem 21 6, por exemplo, para exemplo, mudar essa lógica de uma correspondência exata para uma correspondência próxima e igual ou menor do que combinar tudo o que precisamos fazer. Agora ele mudou esse zero para um um ou um verdadeiro pressione enter, e agora ele se tornou uma correspondência aproximada ou difusa. Procure para cima que será aplicado corretamente como ele é aplicado a todas essas linhas, então é um duplo clique para aplicá-lo todo o caminho para baixo. Parece que temos cerca de 100amostras aqui e parece estar funcionando muito bem. Enfraquecer a verificação do local. Cinco seriam 25 seriam 15%. Uma quantidade de ordem de 75 seria 20. Parece ótimo. Então aí está. Esse é um ótimo exemplo de um caso de uso prático riel para correspondência aproximada ou partida difusa V funções de pesquisa. 12. Seleção aleatória com OFFSET e RANDBETWEEN: Muito bem, pessoal, estou tão entusiasmado com a próxima dica. É um dos meus favoritos de todos os tempos. Sei que digo muito isso, mas desta vez falo sério. É definitivamente top dois ou três. Pelo menos vamos falar sobre como aleatorizar itens de uma lista usando deslocamento e correr entre funções. Então, essencialmente, o que vamos fazer aqui é combinar offset com um gerador de números aleatórios inteiro usando rand entre para essencialmente saltar em torno de duas rosas aleatórias dentro de uma lista ou faixa de células. Agora, você pode estar se perguntando, por que eu iria querer fazer isso? Qual é o ponto? E posso garantir que há razões muito sérias e muito aplicáveis para aprender essas ferramentas, como construir seu próprio gerador de nomes de banda. Aqui temos duas listas de palavras. Nós temos adjetivos hilariantes na coluna A e nós temos hilariantes agora proprietários e coluna B, e nossa missão aqui é usar esses offset e correr entre funções para gerar aleatoriamente o nome da nossa próxima banda. Agora, a beleza disso é que porque correu entre é uma função volátil. Isso significa que nossos nomes de banda irá regenerar com qualquer alteração de pasta de trabalho ou se nós apertarmos que F nove calcular atalho. Então eu já perdi uma quantidade embaraçosa de tempo. Apenas refrescando isso, você sabe, tarde de uma sexta à noite, rindo para mim mesmo. Mas eu adoraria ouvir alguns nomes de bandas que vocês inventaram. Acho que os meus melhores agora são o boneco neve beligerante. Isso foi muito bom em gatinhos caprichosos era um bom. E, claro, os girassóis quentes brancos. Eu diria aqueles lá no meu top três, então me avise se você pode superar esses. Deixe um comentário. Adoraria ouvi-lo. Agora, é claro, existem outros casos de uso comuns, como a criação de modelos de negócios riel ou cenários onde você deseja aleatorizar determinadas entradas . Ou, claro, construir sua própria banda hilariante chamada Generator. Porque por que não Então com isso o suficiente falando sobre isso, Vamos saltar para o Excel e realmente construir esta ferramenta. Muito bem, assim que tiveres aberto o teu livro de dicas profissionais, vamos procurar os itens da lista aleatória. Demonstração É uma aba verde em nossa seção de dicas de fórmulas, ou você pode ir para o seu índice e vincular diretamente a ele. E aqui temos nosso gerador de nomes de banda para listar cada um contendo 30 itens, e tudo o que precisamos fazer é puxar esse item aleatório da lista um item aleatório da Lista dois e pode Canton oito ou combiná-los juntos em nosso nome de banda. Agora, existem algumas maneiras de fazer isso. Poderia usar funções de índice que você poderia usar offset tanto suas ferramentas perfeitamente válidas para extrair valores de um local específico dentro de um intervalo. Neste caso, vamos usar Offset. Vamos começar a digitar essa função. Nós estamos indo para deslocamento de uma determinada referência ou ponto de partida neste caso são cabeçalho da lista A célula? Um. Vamos bloqueá-lo com F quatro agora o número de linhas que queremos mover esta referência para baixo. É aqui que entra a organização aleatória. Nem sempre queremos descer uma ou duas ou 10 linhas. Queremos mover para baixo um número aleatório entre um determinado mínimo e máximo conhecido como os valores inferiores ou superiores. Então o número mínimo de linhas que queremos mover é um, porque isso nos levaria um caminho para baixo até o primeiro item da nossa lista Aquatic e o maior número que queremos retornar o valor superior é 30 que nos levaria todo o caminho para o final da nossa lista. Agora isso é tudo o que precisamos fazer para esta iteração da função. Podemos ir em frente e fechar a vírgula entre parênteses para o próximo argumento, que é colunas. E porque não queremos sair do nosso ponto de partida para a esquerda ou para a direita, vamos usar um zero aqui. E como não estamos olhando para retornar um intervalo ou array com uma determinada altura ou largura, podemos fechar a função aqui e pressionar enter. Então é puxado um item aleatório neste caso, elegante fora da lista um. E em vez de reinventar a roda, vamos em frente e copiar essa fórmula. Cole para dentro, também. E tudo o que precisamos fazer é mudar esse ponto de partida da célula um para ser um, e devemos estar prontos para ir. Tão feio e obras de arte são as duas palavras que foram retornadas aleatoriamente. último passo aqui é contaminá-los. Combine-os juntos no nome da banda, então digamos que é igual ao espaço entre aspas e comercial para inventar Nate. Pegue a primeira palavra e aspas abertas por cento em um espaço e, finalmente, pegue a segunda palavra pressione enter e lá vamos nós. The Magnificent Bubbles é o nosso primeiro nome de banda randomizado. E lembre-se, por causa do fato de que correu entre seu volátil, você pode pressionar o atalho F nove ou ir para fórmulas. Calcule agora para ver isso recalcular várias vezes e várias vezes para que pudéssemos chamar um dia bem ali se nossas listas não vamos crescer ou encolher em nenhum momento. Mas se quisermos ficar um pouco mais flexíveis, o que podemos fazer é substituir esse número aleatório de 30 e usar uma função como a contagem A para tornar essa referência mais dinâmica. E o que isso vai fazer é que ele vai contar o número de itens nessa lista, quer seja 30 ou 40 ou 50 ou 10 e usou isso como o intervalo máximo para a nossa função de corrida entre . Então, novamente, isso nos dá um pouco mais de flexibilidade para que possamos adicionar ou remover dessas listas . Então eu vou deletar a contagem de 30 tipos A e referenciar toda a coluna. Um travamento com F quatro e só pegar aqui é porque temos um cabeçalho que não é em branco também. Vamos fechar esse parêntese. Subtraia um a partir disso e devemos ser bons para ir suprimidos. Entra ali. Podemos ir em frente e copiar essa mesma fórmula, colá-lo e onde mudar as referências para ser um e a contagem da coluna B pressione enter , e estamos prontos para ir. Então, há o nosso pequeno gerador de nomes de banda aleatória que construímos aqui mesmo no Excel. 13. Combinando INDEX e MATCH: tudo bem para esta dica profissional vai falar sobre como combinar funções de índice e correspondência para servir como um olhar mais flexível para cima. Agora, esta é uma dica de fórmula de quatro estrelas, definitivamente um pouco mais avançada. Agora, se você não está confortável com os fundamentos de funções de pesquisa ou índice e correspondência, em mente que você pode ter dificuldade em seguir por aqui pode valer a pena voltar e fazer alguns desses cursos fundamentais. Essencialmente, eis o que vamos fazer. Vamos combinar índice e correspondência para substituir o propósito de um “V Look up “, e isso vai oferecer vários benefícios diferentes. Para começar, não precisaremos. Código rígido. Um índice de coluna. Em outras palavras, Match vai identificar automaticamente nossos cabeçalhos de coluna, encontrar a correspondência certa na tabela de pesquisa e puxar valores das colunas corretas. Segundo, olhe para cima. Os valores não precisarão viver na primeira coluna da matriz de tabela da mesma maneira que eles fazem com o V. Procure. Ele pode viver em qualquer lugar na matriz que estamos referindo. E, em terceiro lugar, isso nos dará mais flexibilidade para situações complexas como tentar encontrar a segunda ou terceira instância de um jogo, que cobrirá em um protesto separado. Então vamos tentar visualizar o que está acontecendo aqui antes de entrarmos em nossa demo. Considerado conjunto de dados como este obteve dados brutos e colunas A a C olhando para a receita por loja por ano. E o que estamos tentando fazer é preencher as colunas D, E e F para trazer a cidade, estado e país com base em uma tabela de pesquisa como esta. Tenho informações sobre cada loja uma etiqueta no endereço, uma propriedade da cidade e um país. Agora vamos ver como está tudo ligado. Vamos prestar atenção a uma célula em particular como D quatro, por exemplo, por exemplo, que está puxando o nome da cidade de Bremerton, e você verá esse índice complexo e função de correspondência bem ali na barra de Fórmula . Mas, essencialmente, aqui está o que está acontecendo. Estamos dizendo ao Excel para indexar toda a gama de valores potenciais de nossa matriz de tabela abaixo e, em seguida, usando funções de correspondência para isolar a linha específica que queremos e a coluna específica toe terra em um valor individual. Então, a primeira função de correspondência está procurando a loja I D número neste caso, Número três dentro da coluna I da nossa matriz de tabela. É encontrar a loja que eu d na terceira linha abaixo dos cabeçalhos e dizer Excel. Esta terceira fila é onde quero viver. É aqui que vamos extrair nossos valores para a função de índice. E então um processo muito semelhante acontece com a segunda função de correspondência, que determina nosso índice de coluna. Exceto nesse caso, estamos procurando por esses nome da cidade ou o nome do cabeçalho em D um dentro da linha um ou a linha de cabeçalho da nossa matriz de tabela. Neste caso, encontramos o cabeçalho da cidade. Nós combinamos esse nome de cabeçalho na quarta coluna, e isso diz a nossa função de índice. Precisamos de um valor da coluna número quatro. Então, juntos, essas duas funções de correspondência estão dizendo ao índice para se mover para baixo para a terceira linha e sobre a quarta coluna e retornar o resultado de interseção, que neste caso é Bremerton. E esse é o valor que é puxado de volta para a nossa mesa lá em cima e para vender Defour tão definitivamente complicado para pegar um jeito no início. Mas uma combinação realmente poderosa de funções para usar casos de uso tão comuns aqui, preenchendo muitas colunas de pesquisa sem ter que atualizar manualmente as fórmulas. E há números de índice de coluna e trabalhar com cenários mais complexos. Como mencionei, por exemplo, pesquisas, que podem ter muitas correspondências e múltiplas correspondências nessa matriz de tabela. Então vamos saltar para o Excel e praticar escrever uma dessas funções de correspondência de índice. Tudo bem, então em uma pasta de trabalho de dicas profissionais, procure combinar Índice e Combine Tab e nossas dicas de fórmula podem ser vinculadas diretamente a ela. E aqui temos nossos dados de receita por loja por ano e Collins A a C e essas três colunas que queremos preencher usando uma função de pesquisa em uma cidade pólo, estado e país para cada loja I d. Baseado nesta tabela de pesquisa aqui nas colunas h a eles. Então, por uma questão de demonstração, vamos começar com um simples V olhar para cima fórmula primeiro e ver como isso acontece. Então procuramos a loja de B para consertar apenas a coluna porque é onde a história que eu sempre viveria. Vamos procurá-lo dentro da tabela e anotar. Não posso começar na coluna H porque não é o meu olhar uma coluna inicial. Vou puxar para o m 11 pressionar F 4 para consertar essa referência e para a coluna da cidade aqui. Eu quero retornar o valor da 1ª 2ª coluna sobre. Esse é o meu número de índice de coluna e meu intervalo Procurar é correspondência exata porque eu quero coincidir com a loja exata I D. Nessa coluna, I de modo que preenche Acapulco, que parece bom, pode aplicá-lo para baixo, e tudo está funcionando como esperado. E agora é assim que a maioria das pessoas continuaria com isso. Precisamos preencher para mais colunas, estado e país. Você pode arrastar esta fórmula para cima, e como nós definimos nossas referências corretamente, tudo o que precisamos fazer é mudar o índice da coluna da terceira coluna para a quarta para o estado e da terceira para a quinta para o país. E lá vamos nós. Nós podemos agarrá-los para descer, e nós já povoamos todas essas células corretamente em questão de minutos, usando uma função tradicional ser olhar para cima. E não há absolutamente nada de errado em fazer isso. Gillick é incrivelmente poderoso. O único problema é que se tivéssemos que preencher Ah, 100 colunas em vez de apenas três. Agora, toda a atualização repentina de todos os números de índice de coluna manualmente começa a parecer uma dor de cabeça muito mais tediosa. Agora, uma opção é substituir o índice da coluna por uma correspondência e combinado V olhar para cima com correspondência, que neste caso funcionaria muito bem. A única desvantagem, lembre-se, é que ainda teríamos que estar referenciando que Procure aquela loja I d na primeira coluna da matriz de tabela. Então vamos em frente e excluir essas funções de pesquisa V. Quero mostrar-lhe a abordagem de correspondência de índice como uma alternativa. Então vamos começar com um índice. É sempre assim que começamos porque estamos dizendo ao Excel. Queremos pegar um valor de toda esta matriz, e agora podemos incluir a coluna H também, e vamos corrigir essa referência. E agora os dois únicos argumentos com os quais nos preocupamos aqui precisamos dizer ao índice qual estrada puxar e qual calma puxar de dentro dessa matriz. E lembre-se da demo, vamos usar uma função de correspondência em cada um desses casos. Então, para identificar a linha correta, vamos dizer, vamos combinar a loja. Eu estava aqui e ser sintonizado vai consertar a coluna. E onde procuramos a loja? Enquanto procuramos a loja, eu estava na matriz de mesa onde nossas idéias de história vivem,o que é calmo eu às quatro. Enquanto procuramos a loja, eu estava na matriz de mesa onde nossas idéias de história vivem, Para corrigir isso em, use um zero para correspondência exata e, em seguida, feche a primeira função de correspondência. É o número da linha que vamos alimentar no nosso índice. A única outra peça que precisamos é o número da coluna. Novamente. Vamos ter uma abordagem muito semelhante aqui. É que desta vez vamos coincidir com o nosso nome de cabeçalho, que é atualmente City. E vamos corrigir apenas a linha desta vez porque os nossos cabeçalhos vivem sempre na primeira fila. E onde estamos olhando para combinar com esse cabeçalho? Bem, estamos procurando combiná-lo no hétero da arte. Procure a matriz H um a M um a quatro. Para corrigir esse zero para correspondência exata. As roupas que combinam com a função fecharam toda a função de índice e pressione enter. Aí está, pegamos Acapulco de novo. E agora aqui está a beleza disso. Se pegarmos Acapulco, arraste-o. Boom. Nós preenchemos o estado e o país automaticamente sem ter que tocar nesses números de índice de coluna . E, como eu mencionei que eu d ou que procurar valor não precisa viver na primeira coluna neste caso que viveu na segunda coluna. E isso significa que podemos fazer coisas assim e dizer, em vez do país, me dê a etiqueta da loja e aí está. Ele vai puxar os rótulos corretos, mesmo que aqueles foram à esquerda da idéia da loja que estamos olhando para cima, que é algo que seria impossível com a função de pesquisa V. Então lá você vai curso rápido intensivo na combinação de índice e correspondência para criar funções de pesquisa mais flexíveis . 14. Combinando itens entre listas: Muito bem, a seguir, tenho uma dica profissional para vocês. É uma dica de fórmula de cinco estrelas, nível de perito. Estes são os tipos de dicas que você vai querer prestar atenção se você quiser se tornar um verdadeiro usuário avançado de fórmula no Excel. E o que vou mostrar a vocês é uma abordagem para contar itens correspondentes entre duas ou mais listas. E para fazer isso, vamos combinar estes algum produto com a função count if para retornar o número de valores correspondentes entre essas listas agora, nesta demonstração terá duas tabelas diferentes. Organiza produtos contendo, lista de produtos A e B. Eles podem ter diferentes números de itens neles, mas sabemos que há alguma sobreposição entre eles, e o objetivo é usar uma fórmula de célula para identificar e contar que se sobrepõem. Então o que vamos fazer é calcular alguns itens aqui. O número de itens na lista A e lista estar usando uma contagem simples de fórmulas. E aqui está a chave. Vamos calcular o número real de itens correspondentes. Em outras palavras, a sobreposição entre as listas A e B. Então o que está acontecendo aqui é a contagem. Se a função está iterando ou percorrendo a rosa em uma das listas, e está verificando se existe uma correspondência na outra lista. Para cada estrada que encontra uma contagem de correspondência, se sinais em uma e para cada linha onde uma correspondência não existe, contas um zero, e a partir daí, a função de algum produto basicamente apenas acrescenta. A matriz resultante de valores e esse total nos dá o número de itens correspondentes. Então, uma nota rápida aqui, neste caso, foram referenciando lista um primeiro, seguido por Lista B. Você pode trocar a ordem não importa. Qualquer uma das listas pode ser usada como base. Então, alguns casos de uso comuns aqui como estamos mostrando aqui, apenas calculando a sobreposição entre duas listas e excel, ou confirmando que listas são exclusivas para garantir que você está evitando qualquer tipo de erros de contagem dupla . Então vamos em frente e saltar para a pasta de trabalho dicas Pro e realmente praticar escrever uma dessas algumas funções contadas de produtos. Tudo bem, então em sua pasta de trabalho de protesto, estamos procurando a contagem de itens correspondentes guia Demo Verde em nossa seção Dicas de Fórmula, vá em frente e pressione link e saltará para nossas listas de produtos aqui e rolando através. Você pode ver que temos duas listas de produtos. Ah, lista do produto A tem cerca de 100 unidades de comprimento. I Lista B é de cerca de 89 ou 90. Na verdade, podemos usar funções de contagem Day apenas para tipo de retornar essas estatísticas descritivas. Então contar uma chamá-lo um menos o cabeçalho. Vai dar-nos 100 itens e coluna A e, em seguida, o mesmo processo. Aqui, conte uma coluna B menos o cabeçalho. 89 itens alistam carne. Agora, uma coisa que vai nos ajudar, Justus Faras legibilidade está preocupada é dar a essas listas nomes próprios. Então, em vez de referenciar um dois a um 101 em uma fórmula, eu gostaria de nomear isso algo como lista A para que eu possa selecionar o primeiro item mantenha a seta de deslocamento de controle para baixo para pegar essa lista completa e na caixa de nome aqui à esquerda da fórmula bar, eu vou entrar lista de um dado em um nome próprio. Faça a mesma coisa com essa seta de mudança de controle Be para baixo nesta lista B. Isso só vai tornar nossas fórmulas um pouco mais fáceis de trabalhar enquanto continuamos. Então, antes que eu vá em frente e comece a preencher uma função aqui e vender cinco. A maneira que eu gosto de aprender essas técnicas mais avançadas é começar com as peças componentes , entendê-las muito, muito bem e depois juntá-las no resultado final. Então vamos em frente e fazer isso. Acho que isso vai ser útil. Isso é certo. Clique em e adicione uma coluna aqui. Isto vai ser como um espaço de trabalho em branco. E se ignorarmos o componente algum produto completamente por enquanto e apenas nos concentrarmos no que a contagem se a paz está fazendo, vamos tentar escrever a função contabilizada aqui, e o primeiro intervalo será a lista A. E como você começar a digitar, você verá o chamado Intervalo Preencher Tab Injusta-lo em. E então nós vamos contar os itens na Lista A que correspondem a um determinado critério, que neste caso, vamos começar com Selby, também. Feche-o, pressione Enter. E agora se arrastarmos essa fórmula para baixo ou clicar duas vezes para aplicá-la, você verá uma série de uns e zeros indo até o final da Lista A. E o que está acontecendo aqui em cada linha é que a contagem? Se a função está dizendo, Ok, B existe dentro da lista A. Sim ou não? Se sim, retornar um um se não retornar um zero. E essas referências mudaram para três antes de serem cinco. Então, essencialmente, todos que você vê aqui na coluna c refletem uma correspondência. E, de fato, se selecionarmos toda a coluna C ou o intervalo específico e olharmos para o sol, essa soma é 33 que, em teoria, deveria nos dizer o número total de correspondências entre essas duas listas. Agora uma coisa a notar é que eu não tenho que apenas me referir a estar aqui. Ele pode alterar essa referência para toda a lista. Seja referência e pressione enter e assista O que acontece quando eu aplicar isso? Nada muda à medida que eu clico. Você não vê a fórmula mudando. Mas a referência real ao valor que estamos tentando corresponder está mudando linha por linha. Então ainda temos esse número. 33 partidas entre as duas listas e agora uma outra coisa. Se eu trocasse a ordem das listas aqui na coluna D. Mesma contagem de aproximação. Se esta lista de tempo for primeiro, seguido pela lista A. Você verá um padrão diferente de zeros e uns, porque agora estamos verificando os itens . Aliste A e mapee-os para a lista B, mas confira. O total quando você seleciona todo o intervalo ainda é 33 que é por isso que eu disse que você pode usar qualquer uma das listas como base. Então, vale a pena notar que você poderia parar aí e usar um normal tradicional algum dedo do pé função. Adicione esses valores e obtenha esse número de item correspondente de 33. Mas vamos levar as coisas um passo adiante e criar esse valor. Calcule esse valor sem ter que usar essas duas colunas auxiliares. Em vez disso, vamos usar algum produto que poderia essencialmente criar essas matrizes dentro da própria fórmula para calcular o mesmo alguns e retornar esse valor de 33. Então mostrar-lhe o que isso parece aqui e vender F 5. Vamos começar com o produto. E o array que estava somando neste caso é literalmente aquele array que acabamos de criar qualquer versão, que foi baseado fora da contagem. Se assim for, podemos digitar a mesma função que acabamos de fazer dentro da contagem de função de alguns produtos. Se lista. Um critério é uma lista. Ser outra vez. Qualquer ordem. Feche-o e pressione Enter. Lá vai você. 33 itens agora podemos realmente excluir essas colunas auxiliares. Não importa. Não vai impactar a fórmula. Ainda temos o valor correto de 33. Agora, para aqueles de vocês que não estão familiarizados com supera um estilo de corrida fórmulas. Você pode estar meio que coçando sua cabeça um pouco aqui porque nós essencialmente acabamos criar uma fórmula que se comporta de uma maneira muito única e incomum certo. Ele está criando sua própria matriz de valores nos bastidores e, em seguida, processando essa matriz para produzir um único valor. Agora, quando eu estava aprendendo pela primeira vez sobre funções de array, uma coisa que eu achei muito útil foi usar a ferramenta de fórmula de avaliação. E o que isso vai nos ajudar a fazer é quebrar as coisas e diagnosticar como todas essas peças estão trabalhando juntas. Então vamos começar de dentro para fora. Então esta é uma função aninhada, e nós vamos avaliar esta peça por peça. Então lista A, que é o argumento de intervalo da contagem. Se é um simples s o alcance de células que estavam referenciando um dois através um um um um e lista B será nossa lista de critérios. E então o que devemos ver quando avaliamos esta lista, ser argumento é uma matriz real contendo cada produto ou cada item dentro dessa lista, que é exatamente o que vemos aqui. 89 itens individuais todos embalados em um raio e, em seguida, o próximo componente sublinhado é toda a função contada. Agora que avaliamos ambos os argumentos, podemos avaliar a contagem em si e verificar isso. Esta corrida deve parecer muito familiar porque é exatamente o mesmo Siris de 89 uns e zeros que geramos por escrever conta. Se a fórmula celular na aplicação em 89 vezes ou 2 89 aumentou, as diferenças estavam fazendo isso aqui como parte de uma função de algum produto e essencialmente substituindo essas 89 funções por uma. Então ele começa a lhe dar uma noção de por que essas funções de estilo ar são tão poderosas Agora. A última peça aqui é que algum produto neste caso, estamos usando algum produto de uma forma bastante simples. Na verdade, nem estamos usando a parte do produto, porque estamos alimentando apenas uma única matriz, tudo o que alguns produtos vão fazer é adicionar esses valores para cima. Eu tenho uma dica. Vamos falar de vocês “niks “e “duplicatas “onde adicionamos um nível de complexidade a isto . Mas neste caso, mantendo-o simples. Só vou alimentar o algum produto, esta matriz, somar os uns e os zeros e avaliamos o passo final. Lá vai você. Temos os nossos 33 e isso deve bastar agora. Para aqueles de vocês que estão familiarizados com funções de matriz, a pergunta que eu garanto está em algumas de suas mentes é por que você não usou a mudança de controle ? Digite quando você digitar este produto que você está chamando função Honore. E a resposta é que algum produto é uma das poucas funções no Excel que se comporta como uma função de matriz, mas não requer a abordagem de mudança de controle enter. Assim como eu mostrei a vocês, simples é pressionar Enter e ele se comporta como qualquer outra função. Neste caso, eu realmente poderia substituir o algum produto por um simples alguns e realmente usou a abordagem de matriz e digite isso com deslocamento de controle enter e eu vou obter a resposta correta porque novamente, nós não estamos usando o componente do produto de algum produto. Portanto, qualquer abordagem é totalmente válida. Eu realmente prefiro o algum produto abordagem e controle z para voltar para ele só porque, você sabe, eu não gosto de lembrar que eu tenho que usar o controle shift enter. Prefiro tratá-la como uma fórmula normal. Então, aí está. Grande demonstração mostrando como você pode usar essas ferramentas como algum produto e contar se para fazer coisas realmente poderosas no excel. 15. Contando duplicatas com SUMPRODUCT: Tudo bem para esta dica profissional. Eu quero falar um pouco sobre contagem, duplicar ou rosa única em uma lista usando fórmulas auto. Agora é importante ter em mente que o Excel oferece várias maneiras diferentes de identificar ou contar valores duplicados e exclusivos. Você poderia usar filtros avançados. Você pode remover duplicatas e contar o número de linhas. Você pode conectar seus dados em uma tabela dinâmica e olhar para itens distintos ou até mesmo usar ferramentas como Power Query que tenham construído em contagem funções distintas. Mas até que haja uma fórmula equivalente para contar distinta, fazer isso com fórmulas celulares sozinho pode ser enganosamente complicado. Mas uma abordagem realmente fascinante que eu quero compartilhar é usar algum produto e contar se para contar esses valores únicos e, em seguida, subtrair do total para chegar ao número de duplicatas. Definitivamente não é a maneira mais simples de chegar ao resultado final, mas fascinante mesmo assim. Então o que vamos ver aqui é minha lista de compras, que está crescendo constantemente. Eu esqueço quais itens eu coloco lá e, como resultado, eu acabo comprando produtos duplicados. Então, o que eu quero fazer aqui é analisar essa lista e usar funções de célula para calcular o número total de itens surgidos nessa lista, o número de único e qualquer duplicado aumentou nos casos em que eu possa ter escrito o mesmo produto duas vezes. Então, para fazer isso, vamos usar algumas funções diferentes para o total de itens que estavam indo para usar Count Day. Neste caso, definimos o intervalo de células antes até B 52 como mantimentos. Agora, o complicado é este segundo item itens únicos aqui e para calcular esses únicos, então vamos usar uma combinação de algum produto e contar se eu vou quebrar isso e mostrar exatamente como ele está funcionando em nossa demo. Então, uma vez que você tem essas duas peças, nós seremos capazes de simplesmente pegar a diferença para chegar ao número de duplicatas. Então, novamente, não é a minha abordagem recomendada, necessariamente como uma ferramenta diária, mas uma ótima maneira de ver exatamente como funções como esta, especialmente como algum produto, estão realmente funcionando. Casos de uso diferentes aqui. Ele pode ser usado para calcular o número de produtos únicos ou distintos pedidos, por exemplo, ou você pode usar algo assim para ajudar com erros, verificando para evitar a inflação que pode ser causada por registros duplicados. Então vamos pular em nossa pasta de trabalho do Excel e ver se podemos escrever uma dessas algumas funções contadas do produto . Tudo bem, então nós vamos estar procurando estão contando uma demonstração única e duplicada. Uma guia verde dentro da seção de dicas de fórmula pode ser vinculada diretamente a ela a partir do índice . E aqui você pode ver minha lista de compras indo para baixo sobre Roe 52 e a primeira coisa que eu vou fazer antes de escrever uma única função é dar essa lista de compras intervalo e nome. Clique na seta de deslocamento de controle do primeiro item para pegar o último item e, na caixa de nome à esquerda da barra de fórmulas, dê um nome como mantimentos. Isso vai tornar muito mais fácil referenciar esta lista na nossa fórmula. Nomes avançando. Então, as primeiras coisas primeiro. Número total de itens. Isso é um simples é usar uma contagem de uma função contagem de um conselho, células não vazias em um intervalo e organizar vai ser que lista de compras que acabamos de criar Tab que em fechá-lo para fora, pressione enter. Então eu tenho 49 itens nesta lista, 49 subiu na minha lista de compras. Agora a chave é tentar descobrir como calcular, quantificar o número de únicos especificamente e assim como eu mostrei com a dica de itens correspondentes no início do curso. O que eu vou fazer aqui é quebrar essa abordagem em suas partes mais pequenas e mais componentes. Entenda exatamente como a mecânica dessas peças funciona, e então vamos remontá-las juntas em nossa fórmula final. Então vamos em frente e adicionar duas novas colunas aqui em inserir a Collins à esquerda do fundo. E isto vai ser como a nossa caixa de areia que podemos brincar aqui. E vamos começar com conta se função e vamos usar contagem se simplesmente retornar o número de instâncias de cada item dentro de toda a lista. Então, para fazer isso, estamos em uma contagem de tipos Se e nosso intervalo é toda a lista de mantimentos e, por enquanto , para critérios, vamos selecionar o primeiro item na lista. Feche esse parêntese. Pressione Enter. Agora. O que este número nos diz é o número de instâncias deste produto fora de toda a lista de compras e imediatamente, temos uma duplicata aqui. Isso me diz que os pêssegos enlatados de fita verde aparecem duas vezes na lista. Então, à medida que arrastamos isso para a lista inteira, vamos ver o número de instâncias associadas a cada item de linha na lista de compras. Então, o suco de cranberry Washington só aparece uma vez que a bebida de manga aparece duas vezes. Na verdade, aqui está a segunda instância de bebida de manga Washington, também com o rótulo de to. E então, por alguma razão louca, parece que entrei no clube de queijo quatro vezes diferentes. Acho que estava com fome de queijo. Então, como você pode ver, esta nova coluna basicamente nos disse o número de instâncias de cada produto em nossa lista. Agora, um pequeno ajuste que podemos fazer é uma dica útil aqui é que nós realmente não temos que fazer referência antes. Se referenciarmos a lista inteira novamente e aplicarmos isso para baixo. Mesmo que não vejamos a referência mudando linha por linha, ela está realizando exatamente a mesma coisa. É iteração, item por item, contando o número de instâncias. Agora isso é ótimo. É útil ter o número de instâncias, mas não nos leva até lá como um próximo passo. O que precisamos fazer, é realmente criar um novo cálculo aqui e pegar um e dividi-lo pelo valor que acabamos de criar usando essa função contada, e eu vou mostrar exatamente por que estamos fazendo isso em apenas um segundo. Vamos aplicar todo o caminho até a lista completa, e eu olho. O que está acontecendo quando você pode começar a notar é que estamos essencialmente atribuindo um valor de um a cada item único na lista cada produto único. Isso significa que os itens que só aparecem uma vez como Jeffers, aveia ou mapa Queen City teremos o valor de um aqui, já que um dividido por um é igual a um. Mas o problema é que, para itens que aparecem mais de uma vez, estamos essencialmente levando esse crédito para o produto e espalhando-o entre o número de instâncias. Então, a bebida de manga de Washington aparece duas vezes, e cada uma dessas instâncias recebe meio crédito ou 0,5. Dessa forma, quando resumirmos esta coluna, não estaremos contando duas vezes qualquer produto que apareça mais de uma vez. Na verdade, para os casos em que temos duas ou quatro instâncias. Essas frações nunca serão iguais a mais de uma. Então, se selecionarmos a lista inteira que podemos ver para baixo, aqui estão alguns são 44. Esse é o número dourado. Esse é o nosso número de itens únicos reais ou subiu em nossa lista de compras, então poderíamos simplesmente pegar a soma e conectar que alguma função aqui e ser feito com ele. Mas assim como nossos outros exemplos, não queremos ter que confiar nessas colunas auxiliares aqui. Vamos montar esta função contada em um algum produto, que é essencialmente vai criar esta matriz inteira e resumir tudo dentro da própria função . Vai agir como uma fórmula de matriz. Então vamos em frente e digite igual a grupos, algum produto. E tudo o que vamos fazer é escrever as mesmas funções que acabamos de usar nas colunas D e E, mas aninhá-las dentro desta fórmula. Então algum produto um dividido pela contagem. Se mantimentos vírgula mantimentos, vamos fechar entre parênteses e pressionar Enter. E aí está, 44 itens únicos ou subiu em nossa lista. E agora que sabemos o total e o único enfraquecimento simplesmente tomar a diferença e chegar ao número de rosa duplicado. Então temos o número cinco aqui. Isso significa que tivemos uma fila duplicada para pêssegos enlatados, uma duplicata para bebida de manga, e aquelas três duplicatas de queijo String também subiram na nossa lista, somando a luta. Agora posso apagar D e E, e ainda teremos o nosso valor adequado. E aí está. Então certamente não é a maneira mais fácil ou rápida de chegar a esse número, mas uma oportunidade realmente boa para praticar algumas dessas funções mais avançadas, como algum produto. 16. Mostramos poucas pesquisas: Tudo bem. Esta próxima dica é para aqueles de vocês que estão tentando mergulhar profundamente em território de nível de especialistas com suas fórmulas e funções do Excel, vamos falar sobre como usar contagem se, juntamente com índice e correspondência, para ajudar a lidar com muitos muitos olhares para cima e se sobressair. Agora, um modo geral, o Excel foi projetado para trabalhar com 1 para 1 ou um para muitos relacionamentos. Em outras palavras, se você estiver tentando se conectar ou unir duas tabelas usando as funções de pesquisa de referência , pelo menos uma dessas duas tabelas deve ter apenas uma instância do seu valor de pesquisa ou chave primária. Se esse não for o caso, ambas as tabelas têm várias instâncias desse valor de pesquisa. Então você tem o que é chamado de um relacionamento de muitos para muitos. Então, para dar um exemplo de como isso pode parecer, dê uma olhada neste conjunto de dados. Estamos olhando para os dados de receita aqui, rastreado de volta para um pedido i D. Na coluna A e uma loja i D e coluna B. E o objetivo é preencher esta coluna endereço com base em uma tabela olhar para cima como o que você vê abaixo. Agora, se você olhar atentamente você vai notar que duas dessas lojas ID número cinco e ID número oito, têm várias instâncias naquela tabela de pesquisa. E porque temos vários pedidos rastreados de cada uma dessas lojas, foram presos com um relacionamento muitos para muitos aqui. E para lhe dar uma noção de por que este é um problema comum. Pense sobre o que aconteceria se usássemos um tradicional V olhar para cima ou função de correspondência de índice sozinho nesta relação, você diz ao Excel dedo olhar para cima História D Número cinco. Ele iria procurar coluna G ea tabela de pesquisa, e iria parar a primeira instância, retornou 59 22 tribunal LaSalle e não tenho idéia de que nunca houve outra instância de loja loja ID número cinco em Aquela mesa. Então a boa notícia é que existem ferramentas que podemos usar para lidar com situações como esta, e na abordagem que estamos prestes a demonstrar, vamos usar a contagem. Se as funções de índice e correspondência, o dedo retorna valores de correspondências de pesquisa subsequente. Então, neste caso em particular, o endereço da segunda instância da loja número cinco ou loja número oito, então para dar a vocês uma idéia de como isso vai funcionar. Vamos rapidamente aprimorar uma dessas células. Cela E 4, que é 67 64 Glen Road, o endereço da loja número 8. E esse é o segundo endereço na mesa de pesquisa. Agora, a chave para conseguir esse segundo endereço é este novo assento na coluna. Um número de instâncias e essa coluna é orientada por conta. Função If. Isso é basicamente contando a loja I D em cada linha e verificando para ver quantas instâncias de que eu d existem na coluna g da nossa tabela de pesquisa. Então, para a maioria desses armazenam I DS, o número de instâncias e a coluna C é um. Mas para as idéias cinco e oito, essa função contada retorna para uma vez que suas duas instâncias de cada um desses I DS e o que essa peça de informação nos permite fazer é continuar escrevendo um índice e função de correspondência como normalmente faríamos. Mas em vez de parar no primeiro jogo da loja, I D foram capazes de saltar para baixo para subseqüente subiu nos casos em que há várias instâncias disso, eu d no olhar para cima. Então, neste caso, estamos combinando a loja número oito, pulando uma linha adicional e puxando o endereço resultante. Rua Glen, 67, 64. Agora nota muito importante aqui. Você tem que entender por que há várias instâncias de sua chave, e é muito importante confirmar que eles são, de fato, registros válidos. Porque muitas vezes quando as pessoas se deparam com esse problema de muitos ou muitos relacionamentos, é indicativo de problemas mais profundos com seu banco de dados, caso em que soluções alternativas como esta realmente não ajudarão a resolver o problema raiz. Dito isto, existem alguns casos de uso comuns que criam situações como esta. Um exemplo é se você estiver rastreando alterações históricas em uma tabela de pesquisa existente. Então talvez você tenha um produto cujo preço mudou ao longo do tempo, caso em que você tem o mesmo produto i d. Mas um de seus atributos, neste caso o preço de varejo mudou. Ou talvez no caso em que estamos olhando aqui, loja número cinco na loja número oito locais movidos, mas queremos rastreá-la como a mesma entidade. Ambos são explicações viáveis para o porquê de termos um relacionamento como o que vemos aqui. Então, com isso, vamos entrar no Excel. Abra nossa pasta de trabalho pro tip e vamos ver o que podemos fazer com um desses muitos e muitos relacionamentos. Tudo bem, então do nosso índice, vamos para a seção de dicas de fórmula verde. Procure a demonstração de muitos a muitos lookups. Vá em frente e conecte a essa folha. E aqui temos o rastreamento de receita baseado na ordem I DS mapear para uma determinada loja. Eu estava aqui na coluna B e como nós conversamos, nós vamos tentar preencher esta coluna de endereço usando a tabela de pesquisa aqui em Collins, F e G. Mas lembre-se, o problema é que nós temos que armazenar aqui cinco e oito com rosa duplicada na mesa de olhar para cima. Então, se nós fôssemos escrever uma função de correspondência de índice tradicional, por exemplo, nós vamos indexar que procurar intervalo direto de F G, bloqueá-lo no número de linha que queremos mover para onde quer que pudéssemos corresponder a essa loja I D e bater para e fora do hábito. Vou trancar a coluna B e onde estamos tentando igualar essa ferida? Bem, Bem, estamos tentando combiná-lo na lista de lojas na coluna F, tranque-o, venha com correspondência exata e roupas que combinam funcionam fora. Então estamos indexando esse intervalo. Estamos indo para a linha em que combinamos com a loja e a coluna que queremos neste caso é ligar para o número dois. E isso deve fazer isso para a função de correspondência de índice Pressione. Digite e aplique-o para baixo. E assim como você esperaria, estamos recebendo os valores corretos para lojas. 1234679 e 10. Comece a capitães estão longe. Loja seis. Mitchell Canyon. Aqui está a loja de captura oito Buenavista, que é a primeira instância. Armazene cinco LaSalle novamente primeira instância. Então, por conta própria, esta função de correspondência de índice mesmo com o V. Olhe para cima. Tinha fraco nessa rota não tem idéia de que estas rosas sequer existem na mesa de olhar para cima. Então precisamos construir outra informação para nos ajudar a explicar isso. E é aí que essa coluna de instância entra em jogo. Então eu vou adicioná-lo. Chamá-lo aqui entre B e D. Chamá-lo o número de instâncias e É um simples é conta se função e o intervalo em que estavam contando é o intervalo de loja I d a partir do olhar para cima. Vamos trancar isso nos critérios. O que estamos tentando contar dentro desse alcance é nossa loja. Neste caso, Selby também. Feche-o, digitou e arraste-o para baixo. E lá vamos nós. Então agora nós efetivamente sinalizamos ou rotulamos a loja I DS que aparece mais de uma vez. Tudo aqui é um, com exceção de nossos oitavos e cinco, que é exatamente o que esperaríamos ver aqui. E agora que temos essa informação adicional, podemos utilizar esses dados e integrá-los em nossa função de correspondência de índice para saltar para baixo uma linha extra, mas apenas para aqueles que armazenam I DS Onley por cinco e oito. Então, como podemos fazer isso? Bem, olhamos para a função de índice dela. O argumento de número de linha, que é onde nossa função de correspondência vive, é o que está dizendo excel de qual estrada extraiu dados. Então, precisamos upend algum valor após esta função de correspondência para saltar para baixo uma linha adicional quatro armazena cinco e oito, e vamos precisar fazer isso usando este novo número de colunas de instâncias que acabamos de criar agora na coluna C. Mas se nós apenas adicionar o valor da coluna C, dar uma olhada no que isso vai fazer. Na verdade, vai saltar uma linha extra para cada loja e duas rosas extras para lojas cinco e oito. E se fôssemos fazer isso, como podem ver, tudo é jogado fora. Então, a loja para está voltando agora. Ramsey Circle, que é um fora da loja 10 está retornando um erro de referência porque ele está empurrando-o para fora do alcance de pesquisa. E depois por cinco. Ainda estamos recebendo as respostas erradas porque agora estamos encontrando a primeira instância de cinco e pulando para mais linhas abaixo, o que é mais do que precisamos. Então, com um ajuste rápido aqui, tudo o que precisamos fazer é em vez de adicionar C dois, vamos adicionar C dois menos um. Feche o parêntese e pressione enter e agora confira isso. Perfeito. Então agora todos os 1234679 10 da nossa outra loja estão retornando os valores apropriados. E agora olhe para esta loja. Cinco para 90 Risdon Road, que é a última ou a segunda instância de I D número cinco em que olhar para cima número oito Glen Road Exatamente o que estamos procurando para que você possa continuar a personalizar isso tanto quanto você quiser. Você pode adicionar lógica adicional. Você pode aninhar funções adicionais, mas espero que isso lhe dê uma idéia de como algumas dessas ferramentas, como count ifs e index e match, podem ser usadas para acomodar casos em que você pode ter muitos ou muitos relacionamentos como este .