Fora de férias? Correndo uma maratona? Construindo aquele depósito de casa dos sonhos? Mantenha o controle de seu objetivo financeiro 10 Modelos de planilha surpreendentemente útil para organizar sua vida 10 Modelos de planilha surpreendentemente útil para organizar sua vida É a sua vida um hotch-potch de prazos perdidos, compras esquecidas e compromissos renegados? Parece que você precisa se organizar. Leia mais com um gráfico de termômetro do Excel. É uma maneira simples e eficaz de rastrear uma única variável, e uma que você pode compartilhar com sua equipe 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 formato de planilha simples não é atraente 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, parceiro, amigos ou apenas mantenha-se. Deixe seu próprio progresso inspirá-lo a fazer mais!
Nós estaremos construindo no Excel 2013, mas você deve ser capaz de acompanhar as versões anteriores sem problemas. Este tutorial explicará como criar um gráfico básico de termômetro com uma porcentagem geral e um total geral da meta definida. Em seguida, veremos como usar as funções SUM e SUMIFS Encontre qualquer coisa em sua planilha do Excel com funções de pesquisa Encontre algo em sua planilha do Excel com funções de pesquisa Em uma planilha gigante do Excel, CTRL + F só vai chegar até você. 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. Leia mais para acompanhar nosso progresso durante um período específico.
Configuração de planilha
Antes de construir nosso termômetro, precisamos estabelecer nosso objetivo. Neste caso, estou coletando fundos em torno do escritório virtual MakeUseOf para nossa tão esperada viagem de desenvolvimento de equipes a Andorra - mas você pode usar seus próprios objetivos financeiros e marcadores.
Abra o Excel 2013 e abra uma nova planilha. Nesta parte do tutorial, usaremos apenas duas colunas: uma para meses e outra para o valor depositado. O meu parece com isso:
Você observará as células de segmentação, total e percentual abaixo de nossa tabela. É aqui que vamos criar as fórmulas para o nosso termômetro.
Na célula B20, você precisará escrever = sum (B5: B16), usando o equivalente para a posição da sua mesa. Esta fórmula nos dá o total para os números na coluna B. Depois de estabelecer seu total atual, podemos calcular seu progresso como uma porcentagem usando outra fórmula básica.
Na célula B21, vou digitar = B20 / B19 e pressione Enter. Isso lhe dará um valor decimal. Podemos facilmente alterar isso para o formato desejado - porcentagem - clicando com o botão direito do mouse na célula em questão e selecionando Formatar células . Isso deve abrir imediatamente a caixa de contexto Formatar células na guia números. Altere a categoria para porcentagem e pressione OK . Sua planilha agora deve ser semelhante a esta:
Configuração do termômetro
Agora nós cuidamos da tabela, total e porcentagem, e definimos nossas fórmulas Mini Excel Tutorial: Usando Contagem Avançada e Adicionando Funções no Excel Mini Excel Tutorial: Utilizando Contagem Avançada e Adicionando Funções no Excel Contar e adicionar fórmulas pode parecer banal em comparação com fórmulas do Excel mais avançadas. Mas eles podem ajudar você a economizar muito tempo quando precisar coletar informações sobre os dados em sua planilha. Leia mais, podemos nos concentrar no termômetro.
Siga para Inserir> Coluna> Coluna 2D> Coluna de Cluster . Isso abrirá um gráfico vazio ao lado da nossa tabela. Adicionar dados ao gráfico Como usar uma tabela dinâmica do Excel para análise de dados Como usar uma tabela dinâmica do Excel para análise de dados A tabela dinâmica é uma das ferramentas mais poderosas do repertório do Excel 2013. É freqüentemente usado para grandes análises de dados. Siga nossa demonstração passo-a-passo para aprender tudo sobre isso. Leia mais usando Selecionar dados . Selecione a célula que contém a porcentagem do seu total. Para mim, esta é a célula B21. Pressione OK para preencher o gráfico e você chegará a essa tela:
Agora podemos retirar o gráfico. Clique com o botão direito do mouse no título do gráfico e exclua. Faça o mesmo para o título da coluna e as linhas horizontais. Clique duas vezes no eixo y (porcentagens) para abrir a caixa de diálogo. A partir daqui você pode alterar os limites mínimo e máximo do gráfico para 0, 0 e 1, 0, respectivamente. Enquanto estiver aqui, role para baixo e selecione Números . Altere as casas decimais para 0.
Clique com o botão direito do mouse na coluna e selecione Formatar série de dados . Ajuste a largura de intervalo para 0. Isso garantirá que sua coluna preenche a área do gráfico, em vez de tentar se esconder no canto. Agora você pode reduzir o gráfico para um tamanho mais parecido com um termômetro. Por fim, volte para a guia Inserir, selecione formas e encontre um belo oval. Desenhe um oval e adicione-o à parte inferior do gráfico do termômetro e redimensione a área do gráfico. Deve caber bem em torno do sino do termômetro, assim:
Nós fizemos isso! Você pode mudar seu termômetro para vermelho clicando com o botão direito do mouse e alterando a cor de preenchimento.
Expandindo seu termômetro
Se você está acompanhando uma grande quantia de dinheiro durante um período prolongado de tempo, pode ser útil fazer uma retrospectiva dos dias em que você levantou mais dinheiro. Isso pode ser especialmente útil para campanhas de caridade - você pode analisar o que sua equipe fez diferente nessas ocasiões e associá-la ao seu próximo evento de angariação de fundos!
Primeiro, vamos alterar nossa tabela de dados Mini Excel Tutorial: Usar lógica booleana para processar dados complexos Mini Excel Tutorial: Usar lógica booleana para processar dados complexos Operadores lógicos SE, NOT, AND e OR podem ajudá-lo a obter do novato do Excel para poder usuário. Nós explicamos os fundamentos de cada função e demonstramos como você pode usá-los para resultados máximos. Consulte Mais informação . Precisamos de uma tabela mais detalhada, incluindo datas. Também incluiremos nomes, porque rastrear nossos doadores é divertido. Eu não vou detalhar a transformação da mesa pequena para a mesa grande, mas você deve acabar com uma mesa semelhante à que está abaixo. Eu vou te mostrar como obter este formato de tabela em um momento.
Lembre-se: você precisa de colunas separadas para a data e o valor recebido. Desta forma, podemos monitorar cada variável. Também precisaremos definir um Intervalo Nomeado Dinâmico. Intervalos nomeados são úteis para nos dar o poder de um conjunto de células sem ter que atualizar constantemente nossas fórmulas Precisa de ajuda com fórmulas do Excel? 7 Recursos para Consultar Precisa de Ajuda com Fórmulas Excel? 7 Recursos para Consultar o Excel é o padrão ouro da planilha. Se você precisar usar o Excel e ainda assim precisar se familiarizar com ele, esses recursos apresentarão rapidamente o básico e muito mais. Consulte Mais informação . Podemos solicitar automaticamente a nossa fórmula para contabilizar quaisquer adições à nossa tabela.
Faixa nomeada dinâmica
Para facilitar as coisas para nós mais tarde, tornei a nossa mesa básica em oficial. Faça isso selecionando toda a área da sua mesa. Selecione a guia Inserir e você deve ver imediatamente a Tabela . Selecione OK. Você fez uma tabela pesquisável com cabeçalhos. Os cabeçalhos serão úteis depois!
Lembre-se de nossas células de destino, total e porcentagem de antes? Agora podemos vincular nossa tabela ao nosso total. Em sua célula total, input = SUM (Table1 [Amount]) . Essa fórmula solicita que a célula totalize a coluna Valor. As informações de porcentagem ainda podem ser encontradas dividindo o total pelo alvo e ainda estão vinculadas ao nosso termômetro.
Selecione o conteúdo da sua coluna Valor . Para mim, é C 26: C 38. Selecione a guia Fórmulas e localize Gerenciador de nomes . Clique em Novo Observe a caixa Refere-se a . Se você tiver selecionado a coluna Valor, = Tabela1 [Valor], deverá aparecer. Precisamos adicionar a esta fórmula:
OFFSET (Folha1! $ C $ 1, 0, 0, COUNTA (Folha1! $ C: $ C), 1)
Sempre que você adicionar um valor à coluna Valor, seu total aumentará automaticamente.
Sua fórmula deve ser muito semelhante a esta:
= Tabela1 [Valor], OFFSET (Folha1! $ C $ 1, 0, 0, COUNTA (Folha1! $ C: $ C), 1)
Você pode ter que adaptar a letra da coluna; aqui é C.
Adicionando datas usando SUMIFS
SUMIFS é uma fórmula poderosa que nos permite correlacionar informações de duas ou mais fontes. Nós vamos usar o SUMIFS para descobrir quantas doações nós tiramos dentro de um período de 14 dias, entre duas datas especificadas. É assim que o produto final será:
Digite a data de início desejada. Para mim, esta é a célula B10. Na célula B11, digite = B10 + 14 . O Excel irá inserir automaticamente a data para você e continuar atualizando-a com base na célula B10. Ajuste o período de tempo alterando +14 para qualquer número que você precisar.
A célula B12 conterá nossa fórmula SUMIFS. Na célula, digite:
= SUMIFS ($ C $ 26: $ C $ 95, $ A $ 26: $ A $ 95, "> =" & $ B $ 10, $ A $ 26: $ A $ 95, "<=" e $ B $ 11)
Whoah - isso é um monte de números, o que eles fazem? Eu vou te mostrar!
- $ C $ 26: $ C $ 95: o intervalo de células que queremos incluir. Eu pedi para incluir a célula C95, no caso de nossas doações chegarem até aqui.
- $ A $ 26: $ A $ 95, ”> =” & $ B $ 10 : Diz ao SUMIFS para verificar a coluna A para quaisquer datas depois ou depois
- $ A $ 26: $ A $ 95, ”<=” & $ B $ 11 : Diz ao SUMIFS para verificar a coluna A para quaisquer datas em ou antes
A célula B12 deve agora expressar o valor das doações recebidas entre as datas especificadas.
Arredondar para cima
Nós conseguimos algumas coisas legais hoje. Você usou fórmulas básicas, editou a área e o eixo do gráfico, criou tabelas, aprendeu sobre intervalos nomeados dinâmicos e teve um gostinho do incrível poder do SUMIFS.
Embora, como meu amigo sempre diz, o SUMIFS seja apenas um SUMPRODUCT de um homem pobre; mas essa é outra história do Excel, para outro dia.
Acompanhando sua causa de caridade? Espero que isso tenha ajudado? Qual fórmula ou função do Excel você gostaria que o MakeUseOf explicasse para você? Deixe-nos saber abaixo!
Créditos da Imagem: termômetro via Shutterstock