Fórmula e funções do Excel Parte 8: formatação baseada em fórmulas

1. Introdução a formatação baseada em fórmulas: todos bem-vindos à Seção oito fórmulas formatando as seções um pouco diferentes porque em vez de cobrir uma nova categoria de fórmulas e aprender novas funções e sintaxe, nós vamos realmente estar usando as fórmulas que temos já aprendidas em um novo contexto neste caso para criar regras de formatação baseadas em fórmulas personalizadas. Então vamos começar com apenas uma seção rápida sobre como criar e editar suas regras de formatação . Então vamos entrar em uma série de demos. Demo One é sobre destacar todas as outras linhas usando a função mod. A segunda demonstração vai cobrir células de formatação com base no valor de outra célula, o que é muito útil. A terceira demo vai ser sobre formatação rosa usando funções estatísticas. E por último, mas não menos importante, vou mostrar-lhe como formatar rosa usando funções de texto em. E estes são apenas alguns exemplos. A beleza disso é que, assim como há um número infinito de maneiras de usar as fórmulas que aprendemos, também podemos aplicar essas fórmulas a um número infinito de opções de formatação personalizadas. Então coisas criativas muito legais que você pode fazer aqui por último, mas não menos importante, como sempre, para arquivos para baixar, temos o nosso excel para analistas formatação fórmula. PDF. Vá em frente e imprima isso e, em seguida, a seção oito fórmula formatação arquivo Excel. Então, novamente, esta vai ser realmente uma seção de mãos na mão. Muito trabalho com o Excel. Então vá em frente e abra isso, se familiarize e vamos. 2. Criando, editar e gerenciar regras baseadas em fórmulas: então eu quero ter um minuto para falar sobre formatação condicional e excel. É uma das minhas coisas favoritas. Eu amo formatação condicional porque uma vez que você aprende, é muito fácil de fazer, e absolutamente surpreende as pessoas. Eu juro. Basta colocar uma escala de cores em uma coluna e as pessoas vão pensar que você é uma estrela do rock absoluto . Muitos de vocês provavelmente estão familiarizados com as regras de formatação condicional do estoque. Então, realçar regras de barras de dados, escalas de cores, conjuntos de ícones que são tão ótimos, tão bons e bons. Mas vamos deixar isso para as crianças. Vamos falar sobre como nos desonestarmos e realmente criar nossos próprios formatos condicionais. E vamos fazer isso usando regras baseadas em fórmulas. Esta é uma espécie de um daqueles truques de Excel próximo nível que realmente abre a porta para fazer algumas coisas muito incríveis. Então, primeiro, só quero cobrir a criação e edição dessas regras baseadas em fórmulas basicamente na guia inicial . Se você clicar no menu suspenso de formatação condicional, verá este menu aqui à esquerda. As 5 primeiras opções aqui são aquelas regras básicas de formatação condicional que vamos deixar para outra hora. As 5 primeiras opções aqui são aquelas regras básicas de formatação condicional que Estas três opções aqui em baixo. Esta é a nossa caixa de areia. É aqui que vamos jogar. Então você poderia apenas criar uma nova regra aqui, e esta pequena caixa pop-up vai aparecer. E o que nos interessa é esta última opção Aqui, sob a janela de seleção de um tipo de regra, use uma fórmula para determinar qual vende para formatar. Assim que clicarmos que essa janela aparece aqui, e é aqui que escrevemos nossa fórmula, então é um pouco mais complicado porque não temos a pequena caixa de ajuda para nos guiar . Mas neste momento , estamosna seção oito. Mas neste momento , estamos Vocês deviam ser especialistas. Até agora, vamos ter que confiar no nosso intelecto, na nossa memória e no nosso puro instinto. Então isso vai ser divertido. Então é aqui que você escreve sua fórmula, que é basicamente dizendo, se esta fórmula é verdadeira, então eu quero formar nas células. Como tal, você clica no botão de formatação, escolhe as opções, preenche a borda, fonte, todas as coisas boas e, em seguida, pressione. OK, então aqui nós mostramos apenas um exemplo de uma regra de formatação baseada em fórmulas. Neste exemplo, estamos formatando as células em colunas através de H com um Phil verde e uma fonte em negrito, mas somente quando o nome do estado é igual ao valor e venda. Veja dois, qual é essa lista? Vender aqui? O Eso. Uma coisa a observar é que quando você está trabalhando com regras de formatação baseadas em fórmulas, os tipos de referência se tornam incrivelmente importantes. Portanto, entender quando usar fixo versus um para usar referências relativas é fundamental. Então vou passar muito tempo falando sobre isso. Então vamos saltar para o Excel e realmente ver como isso se parece. Eu abri a Seção 8 fórmula formatando Arquivo Excel. Vá em frente e faça a mesma coisa. Vamos dar uma olhada no que estamos trabalhando com duas guias aqui. O primeiro deve parecer familiar. Este é o nosso exemplo de verificador de preços que construímos durante a seção de referência de pesquisa, especialmente quando recuperamos as funções de índice e correspondência. A segunda guia é chamada de Dados de Estado. Este é apenas alguns dados de estado bruto e nível de ano mostrando população total e população estudantil . Então estas são as duas guias que vamos usar para as demonstrações nas próximas palestras . Então, se eu quiser criar uma nova regra de formatação baseada em fórmula. A primeira coisa que eu quero fazer é selecionar as células onde eu quero que a regra seja aplicada. Neste caso, eu vou apenas selecionar o assento 3 37 Eu peguei a guia inicial. A formatação condicional eliminou a nova regra. Use uma fórmula para determinar quais são as vendas a serem formatadas. E aqui é onde vou digitar minha fórmula. E depois, uma vez escrito, vou clicar no formato. E aqui é onde vou selecionar meu formato real para aplicar quando minhas fórmulas forem verdadeiras. Então cancele fora disso e, em seguida, uma vez que eu tenho regras escritas apenas ir para a formatação condicional novamente indo para gerenciar regras. E então eu posso olhar para as regras para a seleção atual, que agora é vender idade 10. Ou posso dizer, mostre-me todas as regras nesta planilha atual. momento, não há regras aplicadas, mas à medida que começamos a escrevê-las, você verá essa lista começar a ser preenchida, e é aqui que você pode editar suas regras. Você conectou as fórmulas dentro de suas regras. Você pode editar as configurações de formatação ou excluir suas regras juntas. Hum, então lá vai você. Vamos nos preparar para a demonstração número um 3. Destacando todas as outras fileiras usando a função MOD: Tudo bem, vamos em frente e mergulhar na nossa primeira demo. Então vá em frente e abra o arquivo do Excel da Seção 8. Vamos começar na guia de dados do estado. Nosso desafio será criar uma regra de formatação baseada em fórmulas que destaca todas as outras linhas nesses dados aqui. Eu posso ter mentido na última palestra quando eu disse que você aprendeu todas as fórmulas que você precisa usar para isso. Nós vamos aprender mais um, e é chamado de mod M o D. E tudo o que mod faz é retornar o restante quando você divide um número por outro. Então, por exemplo, se um tipo é igual a mod, e então você verá que esta é uma fórmula bastante simples apenas dois componentes, o número e seu divisor. Então, se eu colocar um quatro e um para fechá-lo, eu vou obter um zero porque quatro se divide uniformemente em dois. Se eu mudar isso 4 para 5 agora, eu recebo um restante de um porque qualquer número ímpar dividido por dois irá retornar um restante fora um. Então essa é a função mod. E isso vai ser uma peça crítica para criar essa regra de formatação baseada em fórmulas. Então o primeiro passo é criar a matriz de dados que eu quero. Esta regra a ser aplicada neste caso selecionaria um para controlar a seta de deslocamento para baixo dois d 52 . Então eu tenho todo o meu conjunto selecionado agora na cidade natal. Vou acertar o menu suspenso de formatação condicional, criar uma nova regra, usar uma fórmula para determinar quais vende para formatar. Assim, cada regra de formatação baseada em fórmula começa com o sinal de igual. Eu ia começar com a minha função mod e você pode ver como é excelente não me ajudar aqui, meio que por conta própria. Portanto, certifique-se de saber exatamente como sua sintaxe de fórmula parece antes de pular para esta etapa . Então, a primeira parte do mod é o meu primeiro número, e esse número será apenas a função de linha com um parêntese aberto e fechado. E isso basicamente vai descer e dizer para cada linha sequencial, vamos ver qual é o número da estrada, e o segundo número vai ser também. Então basicamente o que eu estou fazendo é um problema de divisão simples onde eu estou dividindo o número da estrada atual linha por linha, dividido por dois. E a última parte desta função será igual a um. Então, novamente, dividindo o número da linha atual por dois e verificando quando o restante é um. Em outras palavras, isso vai impactar nas linhas Lee Odd. E quando for esse o caso, vou escolher a formatação que quero aplicar. Então pressione o formato. Neste caso, tudo o que quero é um Phil verde claro. Então, eu também posso. Você vai financiar este caso? Só um Phil verde vai fazer muito bem. Assim que eu pressionar OK, volte aqui uma imprensa, OK, mais uma vez. E lá vai você. A regra foi aplicada, e como você pode ver, é aplicar a formatação personalizada ensinar linha ímpar porque quando cada número ímpar é dividido por dois, ele retorna um um. Então, isso parece funcionar corretamente e todos queriam editar esta regra. Vamos voltar às regras de gerenciamento de formatação condicional. Aí está a minha regra. Clique nele e agora se eu mudar isso para zero, eu deveria ver a mesma formatação aplicada às linhas pares, porque nesses casos a função mod retornará zero porque não há nenhum restante, então pressione ok novamente e eu vou apertar isso aplicar o botão aqui. E lá vai você. Você vê a formatação deslocar para cima, apenas as linhas pares. De última hora, pelo menos se eu quiser apagar isso, você pode pressionar, apagar e aplicar. E agora ele se foi. Então lá vai você, destacando todas as outras linhas usando uma combinação das funções mod e linha. 4. Formatação de células com base em o valor de outra célula: Tudo bem, então vamos começar com a demonstração número dois formatando células com base no valor de outra célula . Esta é uma divertida. E é muito útil só porque há tantas oportunidades para aplicar regras semelhantes em todos os tipos de contextos diferentes. Então, neste exemplo, vamos usar o verificador de preços que construímos durante a pesquisa na seção de referência . E se você se lembrar, usamos uma função de correspondência de índice para retornar um preço de uma determinada matriz neste caso C três G sete, com base em seleções de produto e tamanho. Então, o que queremos fazer aqui é criar uma regra de formatação personalizada que procura o preço selecionado em comer 10 e destaca na matriz de C 33 g sete eso. O que vamos fazer é começar apenas selecionando a matriz que queremos que a regra seja aplicada para ir para casa formatação condicional nova regra. Vamos usar uma fórmula para determinar qual vende para formatar. E agora, como sempre, vamos começar com um igual e apenas vamos selecionar a célula superior esquerda na minha matriz . Então venda C 3 set igual a vender E 10 e eu vou deixar assim por enquanto só para mostrar o que acontece se deixarmos as referências fixas. Então, no caso desta forma, era verdade. Que formatação queremos aplicar? Bem, vamos escolher um amarelo brilhante agradável e uma fonte ousada apenas para fazer o pock seleção, então impressionar. Certo, isso aplicará a regra. E como pode ver, nada aconteceu. Então, agora temos suéter extra pequeno selecionado, que retorna o preço de US $30. Então, em teoria, esperaríamos auto C sete a B, exibindo as regras de formatação personalizadas que acabamos de definir. O que acontece se escolhermos meias extra pequenas, que é $4 que conjuntos vendem E 10 igual a C três? Agora estamos vendo a regra de formatação aplicada a toda a matriz. E para entender o que está acontecendo, vamos voltar às regras de gerenciamento de formatação condicional. Não há nada na nossa seleção atual, que é Selby 10. Então eu preciso mudar isso para esta worksheet. Aí está a minha regra, vou selecioná-la e ter a ela. E agora só pensando no que esta regra está nos dizendo é basicamente apenas dizendo que qualquer vez que a cela C 3 e Onley assento três desde que é fixo é igual a vender, comer 10. Aplique esta formatação e lembre-se, estamos aplicando-a a toda a matriz que selecionamos. Então o Excel está realmente fazendo seu trabalho porque neste caso, quando eu tenho meias extra pequenas selecionadas, C três faz, de fato, igualmente 10. Portanto, esta regra de formatação está sendo aplicada. Só não está funcionando como queremos. E a chave é remover as referências fixas aqui, que permitirá que o C 3 mude qualquer célula, qualquer linha, qualquer coluna dentro da matriz que definimos. Isso é uma referência relativa. Então, agora, quando eu pressionar OK e apertar, aplicar Onley, o quatro é selecionado, e agora para testar isso, podemos escolher uma série de produtos e tamanhos diferentes e assistir que destacado, então mude de acordo. Então lá vai você, apenas um exemplo de formatação e célula com base no valor de outra célula 5. Formatando células usando as funções estatisticamente estatísticas: Tudo bem. Então, para esta próxima demonstração, eu quero mostrar alguns exemplos de como você está em conformidade com as células com base em regras ou funções estatísticas . Então, vamos voltar para a seção oito pasta de trabalho do Excel na guia de dados de estado. E o primeiro exemplo que quero mostrar é realçar células dentro de uma coluna específica que atendem a um determinado valor. E o primeiro exemplo que quero mostrar é realçar células dentro de uma coluna específica Então, neste caso, vamos apenas destacar as células onde a população total é maior que cinco milhões. Para fazer isso, você seleciona C dois deslocamento de controle para C 52. Vou selecionar nova regra de formatação condicional. Use uma fórmula agora que eu vou fazer é tipo igual. Si dois é maior que cinco 1.000.000. Agora. A única peça que eu preciso mudar antes de enviar isso é converter a referência de linha de fixo para relativo. Então o mar está bem. Na verdade, ele vê a única coluna nesta matriz. Mas a linha dois precisa ser relativa porque eu preciso verificar o valor da população em cada linha, não apenas escrevi para alguém excluir o dólar, assinar lá e, em seguida, selecionar meu formato. E vamos apenas fazer um tipo de ah, luz laranja sombra aqui Press. Ok, eu vou pressionar. OK, lá vai você. Agora que vemos que apenas dentro desta coluna que eu apliquei a regra a todos os valores cerca de cinco milhões estão agora destacados. Eu também posso selecionar o mesmo intervalo, indo para formatação condicional. É em uma nova regra usando uma fórmula desta vez eu vou dizer igual a C dois novamente Fazendo a referência da linha relativa é igual ao máximo de toda a coluna C. E o que eu quero fazer aqui é basicamente identificar qual linha e coluna C é o máximo e formatá-lo de acordo. Portanto, escolha uma regra de formatação. Vamos para a fronteira. E, uh, vamos dar um esboço completo. Imprensa fronteiriça. OK, eu sou a imprensa ok agora, como você pode ver, o valor de 33,8 milhões, que é a população da Califórnia, é o maior valor na coluna C. Portanto, é o único um que recebe essa sala de formatação. Hum, então vamos em frente e indo para gerenciar regras para toda a planilha e vamos excluir esses dois. E agora o que eu quero mostrar é se nós basicamente tomarmos a mesma regra onde a população total é maior que cinco milhões, mas em vez de apenas destacar as células. Na coluna C, quero destacar toda a linha em que a população total é superior a cinco milhões. O que eu posso fazer é basicamente certo, a mesma regra. Mas antes de eu selecionar toda a matriz de oitavo ou D em vez de apenas C dois até C 52 eso novamente formatação condicional nova regra usar a fórmula e, em seguida, mesma fórmula exata é igual a C dois com uma referência de linha relativa maior do que cinco milhões. Formate-o com show laranja claro. E agora, como podem ver, ainda estamos identificando as estradas. populações totais foram superiores a cinco milhões? A única diferença é que, neste caso, a formatação é aplicada a toda a linha. Porque quando criamos a sala de formatação baseada em fórmulas, nossa seleção incluiu todas as colunas de um a D. Hum, e agora podemos ir em frente e editar essa regra facilmente, então eu vou em frente e editá-la. Então, digamos que se quiséssemos, em vez de selecionar Rose, onde a população total é grande em cinco milhões. Se você quisesse selecionar Rose onde a população estudantil total é maior do que cinco milhões, Eu iria apenas mudar o mar para um D pressione OK e aplicar. E como você pode ver, há apenas um estado onde a população estudantil era maior que cinco milhões e que era a Califórnia. Então, por último, mas não menos importante, eu também posso criar fórmulas estatísticas adicionais aqui. Então, em vez de apenas verificar maior que ou menor ou igual a, eu posso editar e salvar. Vamos verificar se a proporção de estudantes para população total tão d para dividido por C dois com um parente ou uma inferência de referência. A doença é maior do que 25% 250,25 Agora eu pressiona o fornecimento. Como você pode ver, ele é atualizado para incluir muitas outras linhas. E para verificar isso, você sabe, nós podemos adicionar uma nova coluna chamada Aluno Top Percentual, que é apenas igual a D. Dividido por C, formatá-lo como uma porcentagem e aplicá-lo para baixo. E então, como você pode ver em Lee, que subiu melhor do que 25 aplicaram a formatação, então a regra parece estar funcionando corretamente. Hum, então lá vão você apenas alguns exemplos de como você pode usar as funções estatísticas que nós cobrimos como parte de regras de formatação baseadas em fórmulas 6. Formatando células usando funções de texto e operadores lógicos: Então, para a demonstração final, eu quero mostrar a vocês como você se conformam em células com base em funções de texto, bem como operadores lógicos . Vou voltar para a guia de dados do estado. Eliminei todas as minhas regras existentes eram para começar do zero. E a primeira coisa que eu quero fazer é apenas destacar todos os nomes de estados que começam com a letra I. Então eu vou selecionar um para controlar a mudança para uma formatação condicional 52 Nova regra. Use uma fórmula. E agora, desde que eu quero retornar o valor do primeiro caractere que eu preciso usar, eles deixaram a função. Então digite é igual à esquerda aberta o parêntese. Selecione um para, mas permita que a referência de função seja movida. Então, faça com que seja relativo. E agora, uma pequena nota lateral Aqui você pode ter notado que, como eu movido através dessas fórmulas atender a clicar para escolher posições diferentes em vez de usar um teclado setas. Isso porque se você usar as setas, ele tipo de automaticamente tenta inserir referências para você, que pode ser muito frustrante se você não quiser referências. Eso Minha recomendação é quando você precisa fazer coisas como mudar certos caracteres e uma função apenas para usar o mouse e o cursor para tentar saltar para a direita em que parte da função e editá-lo diretamente. Você provavelmente já se deparou com esse problema e percebeu que poderia ser realmente frustrante. Às vezes é uma pequena gorjeta lá. Em qualquer caso, estamos dizendo que a esquerda de um para e o número de caracteres que estavam olhando é apenas um. Alguém faça vírgula. Um perto. O parêntese é igual à letra I entre aspas. Então é isso. Então, a função é deixada de um caractere 21 igual a I. E então, quando esta fórmula for verdadeira, o formato será, digamos, negrito com uma bolsa de fundo azul. OK, e lá vai você. Está destacado. Idaho, Illinois, Indiana e Iowa. Então, trabalhando corretamente agora, uma outra dica. Se você estiver chamando a última palestra, nós aplicamos a função ou formatar duas colunas C e, em seguida, a fim de aplicá-la a toda a linha , nós excluímos, fizemos uma nova seleção e, em seguida, reescrevemos a fórmula. Desta vez, quero mostrar-vos uma maneira mais rápida de fazerem a mesma coisa. E para isso, vou entrar em regras de gestão. Verifique esta planilha para que eu possa ver todos eles selecionando minha regra. E então você vê onde diz se aplica a um dois a um 52. Só vou escolher o seletor, e eu posso arrastar um novo array para fora. Ou ele poderia apenas clicar e mudar o A para D, e então quando ele bateu, aplicá-lo aplica a sala de formatação todo o caminho através da coluna de em oposição a apenas coluna uma maneira muito mais rápida de fazer a mesma coisa. Essa é a primeira regra. E agora se quisermos ficar um pouco mais complicados e inserir alguns operadores lógicos aqui? E agora se quisermos ficar um pouco mais complicados e inserir alguns operadores lógicos Então, o que eu quero fazer agora é dizer, vamos destacar a Rosa onde o nome do estado começa com um I e termina com um A. Então nós dois somos essas condições são verdadeiras, então vamos conseguir. Minhas regras. Vá para editar este. E se você se lembra que um critério um e dois precisam ser verdadeiros, eu preciso usar uma declaração e. Vou começar logo após o sinal de igual, pegue o meu e abra o parêntese. A função esquerda é condicionada, uma em uma vírgula de imprensa para chegar à condição, também. E assim, se a minha primeira condição é que a esquerda mais personagem é I, A segunda condição que eu preciso conhecer é que a direita mais personagem é um uso uma função direita aqui. Mesma referência aqui. Eu só vou fazer um dois, e eu posso ir para cima e selecionar o A para e, em seguida, excluir o cifrão. Ou, você sabe, eu poderia apenas digitar isso à mão também. Bastante simples um para e depois um único direito. A maioria dos personagens, perto aprendizes Bruce é igual e, em seguida, minúsculas A entre aspas Feche a princesa e agora tendo e declaração que inclui dois componentes esquerda. A maioria de um caractere é igual a I, e a direita mais um caractere é igual a minúsculas A. Eu apertei OK e aplicar. Como você pode ver, a formatação agora está em Lee aplicando para os dois estados que atendem a este critério Indiana e Iowa. Então, apenas alguns exemplos aleatórios, mas novamente, um número praticamente infinito de aplicativos para esse tipo de coisa. Então, isso encerra a seção de formatação condicional baseada em fórmula. Sinta-se à vontade para entrar em contato se tiver alguma dúvida. Em seguida, temos um raio funções, então prepare-se 7. HOMEWORK: formatação baseada em fórmulas: Tudo bem, você encerrou o capítulo oito. Você está viajando junto. Você terminou a seção de formatação de fórmula. Agora é hora do seu próximo dever de casa. Abra o arquivo de exercícios de lição de casa do Excel, vá para a guia de formatação da fórmula e dê uma olhada nas instruções. Temos seis etapas aqui que vão permitir que você transforme esse intervalo de dados de um 12 G 25 usando ferramentas como o pintor de formatos, regras de formatação condicional, escalas de cores, conjuntos de ícones e, em seguida, algumas fórmulas avançadas regras de formatação baseadas em valores e texto. Boa sorte.