Atualizado por Ryan Dube em outubro de 2017.
Eu sempre acreditei que o Microsoft Excel é uma das ferramentas de software mais poderosas do mercado. Não é apenas o fato de que é um poderoso aplicativo de planilha. O próprio Microsoft Excel possui uma coleção impressionante de ferramentas e recursos integrados. Neste artigo, planejo mostrar como fórmulas poderosas e formatação condicional podem ser, com três exemplos úteis.
Cobrimos várias maneiras diferentes de usar melhor o Excel, como usá-lo para criar seu próprio modelo de calendário Como criar um modelo de calendário no Excel Como criar um modelo de calendário no Excel Você sempre precisará de um calendário. Mostramos a você como criar seu próprio modelo personalizado gratuitamente com o Microsoft Excel. Leia mais, usando-o como uma ferramenta de gerenciamento de metas. 10+ Modelos úteis do Excel para Gerenciamento de Projetos e Rastreamento 10+ Modelos úteis do Excel para Gerenciamento de Projetos e Modelos de Rastreamento são para gerentes de projeto quais facas são para chefs profissionais: indispensáveis. Mostramos como você pode replicar projetos bem-sucedidos usando modelos prontos no Microsoft Excel e além. Leia mais e outras maneiras exclusivas que você pode usar para gerenciar sua vida. Basta ler esses artigos e você verá o quão poderoso o Microsoft Excel pode ser.
Grande parte do poder está realmente por trás das fórmulas e regras que você pode escrever para manipular dados e informações automaticamente, independentemente de quais dados você insere na planilha.
Cavando no Microsoft Excel
Com as informações corretas, você pode criar um sistema que calcule e recalcule automaticamente os resultados e os relatórios desses dados brutos. Hoje, gostaria de pesquisar um pouco mais sobre a superfície e mostrar como você pode usar algumas das fórmulas subjacentes e outras ferramentas para aproveitar melhor o Microsoft Excel.
Cool formatação condicional com fórmulas
Uma das ferramentas que eu acho que as pessoas não usam o suficiente é a formatação condicional. Se você está procurando informações mais avançadas sobre formatação condicional no Microsoft Excel, verifique o artigo da Sandy sobre formatação de dados no Microsoft Excel com formatação condicional Formatar automaticamente dados em planilhas do Excel com formatação condicional Formatar automaticamente dados em planilhas do Excel com formatação condicional O recurso de formatação condicional do Excel permite que você formate células individuais em uma planilha do Excel com base em seu valor. Nós mostramos a você como usar isso para várias tarefas diárias. Consulte Mais informação .
Com o uso de fórmulas, regras ou apenas algumas configurações realmente simples, você pode transformar uma planilha em um painel automatizado que mostra muito sobre as informações na planilha em um piscar de olhos.
Para obter a formatação condicional, basta clicar na guia Início e clicar no ícone da barra de ferramentas “Formatação condicional”.
Em formatação condicional, há várias opções. A maioria deles está além do escopo deste artigo específico, mas a maioria deles trata de realçar, colorir ou sombrear células com base nos dados dessa célula. Este é provavelmente o uso mais comum da formatação condicional - fazendo coisas como transformar uma célula vermelha usando fórmulas lógicas menor que ou maior que. Brad Jones descreve como usar muitos deles em seu artigo sobre a criação de um painel do Excel Visualize seus dados e faça suas planilhas Acessível com um painel do Excel Visualize seus dados e faça suas planilhas Acessível com um painel do Excel Às vezes, um simples formato de planilha não é envolvente o suficiente para tornar seus dados acessíveis. Um painel permite que você apresente seus dados mais importantes em um formato fácil de digerir. Leia mais - vale a pena conferir se esse é o seu objetivo.
Uma das menos usadas ferramentas de formatação condicional é a opção de conjuntos de ícones, que oferece um excelente conjunto de ícones que você pode usar para transformar uma célula de dados do Excel em um ícone de exibição do painel.
Descobri isso assim que atualizei o Microsoft Office 13+ Razões para você atualizar para o Microsoft Office 2016 13+ razões para atualizar para o Microsoft Office 2016 O Microsoft Office 2016 está aqui e é hora de você tomar uma decisão. A questão da produtividade é - você deveria atualizar? Fornecemos os novos recursos e as novas razões para ajudá-lo ... Leia mais e use a formatação condicional no Microsoft Excel pela primeira vez.
Eu verifiquei os conjuntos de ícones e vi essas luzes indicadoras de LED que eu só tinha visto antes em alguns dos displays de automação de fábrica que eu já programou no passado. Ao clicar em "Gerenciar regras", você será direcionado ao Gerenciador de regras de formatação condicional. Dependendo dos dados que você selecionou antes de escolher o conjunto de ícones, você verá a célula indicada na janela Gerenciador, com o conjunto de ícones que você acabou de escolher.
Quando você clica em "Editar regra ...", você vê a caixa de diálogo onde a mágica acontece. É aqui que você pode criar a fórmula lógica e as equações que exibirão o ícone do painel que você deseja. No meu exemplo, monitorei o tempo gasto em tarefas diferentes em relação ao meu tempo orçado. Se eu tiver gasto metade do meu orçamento, quero que uma luz amarela seja exibida e, se estiver acima do orçamento, quero que ela fique vermelha.
Como você pode ver, eu não estou realmente orçando meu tempo muito bem. Quase metade do meu tempo é gasto muito acima do meu orçamento.
Tempo para focar novamente e gerenciar melhor meu tempo Use a regra de gerenciamento de tempo 80/20 para priorizar suas tarefas Use a regra de gerenciamento de tempo 80/20 para priorizar suas tarefas Como você maximiza seu tempo? Se você já prioriza, delega e ainda luta para fazer tudo, você deve tentar a regra 80/20, também conhecida como Princípio de Pareto. Consulte Mais informação !
Pesquisar itens com a função VLookup
Ok, talvez isso não seja louco o suficiente para você. Talvez você não esteja tão empolgado com as fórmulas lógicas simples que ligam e desligam as luzes. Se você gosta de usar funções mais avançadas do Microsoft Excel, então eu tenho outro para você.
Você provavelmente está familiarizado com a função VLookup, que permite procurar em uma lista por um item específico em uma coluna e retornar os dados de uma coluna diferente na mesma linha daquele item. Infelizmente, a função requer que o item que você está procurando na lista esteja na coluna da esquerda, e os dados que você está procurando estão à direita, mas e se eles forem trocados?
No exemplo abaixo, e se eu quiser encontrar a tarefa que realizei em 25/06/2013 com os dados a seguir?
Nesse caso, você está pesquisando os valores à direita e deseja retornar o valor correspondente à esquerda - do lado oposto ao modo como o VLookup normalmente funciona. Pesquise planilhas do Excel mais rápido: substitua VLOOKUP com INDEX e MATCH Pesquise planilhas do Excel mais rápido: substitua o VLOOKUP Com INDEX e MATCH Ainda usando o VLOOKUP para procurar informações na sua planilha? Veja como o INDEX e MATCH podem fornecer uma solução melhor. Consulte Mais informação . Se você ler fóruns de usuários do Microsoft Excel, você encontrará muitas pessoas dizendo que isso não é possível com o VLookup e que você tem que usar uma combinação de funções Index e Match para fazer isso. Isso não é inteiramente verdade.
Você pode fazer com que o VLookup funcione dessa forma aninhando uma função CHOOSE nele. Nesse caso, a fórmula ficaria assim:
"=VLOOKUP(DATE(2013, 6, 25), CHOOSE({1, 2}, E2:E8, A2:A8), 2, 0)" O que esta função significa é que você deseja encontrar a data 25/06/2013 na lista de pesquisa e, em seguida, retornar o valor correspondente do índice da coluna. Nesse caso, você notará que o índice da coluna é "2", mas como você pode ver, a coluna na tabela acima é, na verdade, 1, certo?
Isso é verdade, mas o que você está fazendo com a função “CHOOSE” é manipular os dois campos. Você está atribuindo números de referência "index" a intervalos de dados - atribuindo as datas ao índice 1 e as tarefas ao número 2 do índice. Então, quando você digita "2" na função VLookup, na verdade você está se referindo ao número de índice 2 na função CHOOSE. Louco, né?
Portanto, agora o VLookup usa a coluna Data e retorna os dados da coluna Task, mesmo que Task esteja à esquerda. Agora que você conhece esse pequeno detalhe, imagine o que você pode fazer!
Se você está tentando fazer outras tarefas avançadas de pesquisa de dados, não deixe de conferir o artigo completo de Dann sobre como localizar dados no Excel usando funções de pesquisa Encontre algo na planilha do Excel com funções de pesquisa Encontre algo na planilha do Excel com funções de pesquisa em um gigante Planilha do Excel, CTRL + F só vai te levar até agora. Seja esperto e deixe as fórmulas fazerem o trabalho duro. As fórmulas de pesquisa economizam tempo e são fáceis de aplicar. Consulte Mais informação .
Fórmulas aninhadas insanas para analisar cadeias de caracteres
Como você pode ver, eu estou tentando ficar um pouco mais louco à medida que vamos, porque eu sei que há alguns de vocês lá fora pensando, "... bem, isso não é loucura em tudo !!" Eu sei, seus padrões são altos. Estou tentando viver com eles. Aqui está mais uma fórmula maluca para você.
Pode haver casos em que você quer importar dados para o Microsoft Excel de uma fonte externa que consiste em uma cadeia de dados delimitados. Depois de trazer os dados, você deseja analisar esses dados para os componentes individuais. Veja um exemplo de nome, endereço e número de telefone, delimitados pelo caractere “;”.
Veja como você pode analisar essas informações usando uma fórmula do Excel (veja se você pode acompanhar mentalmente essa insanidade):
Para o primeiro campo, para extrair o item mais à esquerda (o nome da pessoa), basta usar uma função LEFT na fórmula.
"=LEFT(A2, FIND(";", A2, 1)-1)" Isso pesquisa a sequência de texto de A2, localiza o símbolo delimitador “;”, subtrai um para a localização correta do final dessa seção de sequência e, em seguida, captura o texto mais à esquerda até esse ponto. Neste caso, isso é "Ryan". Missão cumprida.
Aninhando fórmulas do Excel
Mas e as outras seções? Bem, para extrair as partes à direita, você precisa aninhar várias funções RIGHT para pegar a seção de texto até o primeiro símbolo “;” e executar a função LEFT nela novamente. Aqui está o que parece para extrair a parte do número da rua do endereço.
"=LEFT((RIGHT(A2, LEN(A2)-FIND(";", A2))), FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2))), 1)-1)" Parece loucura, mas não é difícil de montar. Tudo que fiz foi pegar essa função:
"RIGHT(A2, LEN(A2)-FIND(";", A2))" E inseri-lo em todos os lugares na função LEFT acima, onde há um "A2". Isso corretamente extrai a segunda seção da seqüência de caracteres.
Cada seção subseqüente da string precisa de outro ninho criado. Então agora você apenas pega a louca equação “DIREITA” que você criou para a última seção, e então passa para uma nova fórmula RIGHT com a fórmula anterior à direita colada em si mesma onde quer que você veja “A2”. Aqui está o que parece.
"(RIGHT((RIGHT(A2, LEN(A2)-FIND(";", A2))), LEN((RIGHT(A2, LEN(A2)-FIND(";", A2))))-FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2))))))" Então você pega essa fórmula, e colocá-lo na fórmula original de esquerda onde há um "A2". A fórmula final de curiosidade se parece com isso:
"=LEFT((RIGHT((RIGHT(A2, LEN(A2)-FIND(";", A2))), LEN((RIGHT(A2, LEN(A2)-FIND(";", A2))))-FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2)))))), FIND(";", (RIGHT((RIGHT(A2, LEN(A2)-FIND(";", A2))), LEN((RIGHT(A2, LEN(A2)-FIND(";", A2))))-FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2)))))), 1)-1)" Essa fórmula extrai corretamente “Portland, ME 04076” da string original.
Para extrair a próxima seção, repita o processo acima novamente. Suas fórmulas podem ficar realmente malucas, mas tudo o que você está fazendo é cortar e colar fórmulas longas em si, fazer longos ninhos que funcionam muito bem!
Sim, isso atende ao requisito de “loucura”, mas vamos ser honestos… existe uma maneira muito mais simples de realizar a mesma coisa com uma função. Basta selecionar a coluna com os dados delimitados e, em seguida, no item de menu Dados, selecione Texto em Colunas . Isso abrirá uma janela na qual você poderá dividir a string por qualquer delimitador desejado.
Em alguns cliques, você pode fazer a mesma coisa que a fórmula acima ... mas qual é a graça disso?
Ficando louco com o Microsoft Excel
Então você tem isso. As fórmulas acima demonstram o quão exagerada uma pessoa pode obter ao criar fórmulas do Microsoft Excel para realizar determinadas tarefas. Às vezes, essas fórmulas não são a maneira mais fácil (ou melhor) de realizar as coisas. A maioria dos programadores irá dizer-lhe para mantê-lo simples, e isso é verdade com fórmulas do Excel 16 Excel fórmulas que ajudarão você a resolver problemas da vida real A ferramenta certa é metade do trabalho. O Excel pode resolver cálculos e processar dados mais rapidamente do que você pode encontrar sua calculadora. Mostramos as principais fórmulas do Excel e demonstramos como usá-las. Leia mais como qualquer outra coisa.
Essas fórmulas e técnicas eram suficientemente estranhas? Você tem truques de fórmulas incríveis que compõem sua caixa de ferramentas superusuário do Microsoft Excel? Compartilhe sua entrada e feedback na seção de comentários abaixo!
Crédito da imagem: kues / Depositphotos