Tenho pesquisado e estou lutando para descobrir por que minhas fórmulas não estão funcionando.
Antes de postar minhas fórmulas preciso explicar o que estou tentando fazer e é um pouco complicado (pelo menos é a única maneira que consigo pensar em fazer).
**Fundo**
Preciso criar uma planilha sobre o orçamento da minha divisão (classificado por categoria), atualmente quando faço o download das receitas/despesas do nosso sistema Financeiro ele apenas bombeia linhas de dados (DADOS BRUTOS), classificados por data.
Meta 1- Faça com que os DADOS RAW exportados sejam classificados automaticamente em categorias e, em seguida, uma tabela mostre claramente quanto foi gasto em cada categoriaALCANÇOU
Meta 2- Faça com que os DADOS RAW exportados sejam classificados em categorias e mostrem SOMENTE meses específicos com base na seleção do usuárioPRESO
EXEMPLO DE EXPORTAÇÃO DE DADOS BRUTOS:
TABELA: Dados2015
Date | Description of Expense | Amount | Codes
Jan-15 | Workshop for employee | 100000 | WRKS
Jan-15 | Workshop for employee | 100000 | WRKS
Feb-15 | Sponsors for employee | 200000 | SPON
Feb-15 | Sponsors for employee | 100000 | SPON
Feb-15 | Workshop for employee | 300000 | WRKS
Mar-15 | Sponsors for employee | 100000 | SPON
Se usarmos a tabela acima como exemplo dos DADOS RAW (denominados "Data2015") que obtenho quando exporto de nossos sistemas financeiros (que converti para uma tabela dentro do Excel).
O que fiz para atingir o Objetivo 1 foi criar uma nova tabela (Nomeada “Categorias”) com as descrições das categorias.
TABELA: Categorias
Categories | Codes
Workshop | WRKS
Sponsors | SPON
Em seguida, criei outra tabela (chamada "Orçamento") como abaixo:
TABELA: Orçamento
Categories | Codes | Amount
Workshop | WRKS | 500000
Sponsors | SPON | 400000
A fórmula que usei para a coluna Valor na tabela Orçamento é
=SUMIF(Data2015[Codes],[@Codes],Data2015[Amount])
Portanto, esta fórmula me ajuda a atingir a Meta 1.
Na minha tentativa de alcançar o Objetivo 2, tentei o seguinte.
Criei uma tabela para capturar os meses que o usuário deseja ver:
TABELA: Condições
Month | X | Background
Jan-15 | x | 1/01/2015
Feb-15 | x | 1/02/2015
Mar-15 | x | 1/03/2015
O usuário coloca um “x” na coluna X se quiser ver as despesas daquele mês específico.
Usei esta fórmula na coluna Plano de fundo:
=IF([@X]="x",TEXT([@Month], "d/mm/yyyy"),"")
Quero filtrar ainda mais os meses em categorias como antes.
Semelhante à tabela de categorias acima, mas para meses específicos com base no que o usuário deseja ver.
Consegui fazer funcionar, mas apenas para uma linha, não para a tabela inteira.
Gostaria que você revisasse minha fórmula e me informasse o que posso estar fazendo de incorreto ou talvez até mesmo minha lógica para atingir a meta 2 esteja incorreta. Sua visão será muito útil e peço desculpas pela situação muito confusa, não consigo pensar em uma maneira mais fácil de explicar.
Criei outra planilha que irá imitar a planilha Data2015 com a seguinte fórmula
=IF(ISNUMBER(SEARCH(Month_View!$D$5, $A5)),Data2015[@Account], IF(ISNUMBER(SEARCH(Month_View!$D$6,$A5)),Data2015[@Account],""))
Mês_Visualizar! é ondeTABELA: Condiçõesestá localizado e $D$5 é 15 de janeiro, $D$6 é 15 de fevereiro e assim por diante.
Esta fórmula praticamente preenche a tabela se detectar que A5 (Coluna onde está a data) é igual àTABELA: Dados2015coluna Data (na mesma linha, daí o @).
Até agora está tudo bem, funciona, desde que eu tenha "15 de janeiro" ou "15 de fevereiro" na célula A5. Para preencher esta célula com base na preferência do usuário usei a seguinte fórmula (que não produz o resultado desejado):
=IF(Condition[Background]=TEXT(Data2015[@Date],"d/mm/yyyy",Data2015[@Date],"")
Então, para mim, essa fórmula pretende fazer o seguinte:
SE a data noDados2015tabela corresponde a uma das datas/valores (no formato "d/mm/aaaa") emDoençacoluna chamada "Fundo" (que só será exibida se o usuário colocar um "x" no mês que deseja ver) e preencha esta célula com o valor deDados2015coluna Data.
Se a célula for preenchida com "15 de janeiro" ou "01/01/2015", o restante da tabela será preenchido, o que poderei usar para filtrar ainda mais por categoria com a fórmula que usei para atingir a Meta 1.
Mas a célula não está sendo preenchida com "15 de janeiro" ou "01/01/2015", mas está em branco ou #VALUE!
Parece estar fazendo referência aoDoençatabela com @ também, o que eu não quero.
Por mais confuso que seja... Se você conseguir entender o que estou tentando alcançar, qualquer insight ou discussão ajudará. Talvez meu cérebro esteja muito sobrecarregado nesta fase.
Responder1
Eu usaria a funcionalidade Tabelas incorporada ao Excel - nenhuma fórmula é necessária. Selecione seu intervalo e pressione Ctrl-T para começar.
Isso ativa os filtros na linha superior - você/seus usuários podem usá-los para filtrar datas por anos e/ou meses.
Para a funcionalidade de totalização, adicione uma linha Totais (usando a faixa Ferramentas de Tabela/Design) e nessa linha você pode escolher qual agregação deseja para cada coluna (Soma, Contagem, etc.). Isso será ajustado automaticamente para seguir sua filtragem.
Responder2
Ok, então resolvi!
Aqui está a fórmula que usei:
=SUM(SUMIF(Data2015BG1[Category],[@Codes],Data2015GB1[Amount]))
*PS: Desculpe se as referências são diferentes dos exemplos dados na minha pergunta.
A planilha Data2015BG1 possui fórmulas que detectam se a coluna Trigger contém um “x” ou não e apresenta os dados necessários com base no mês selecionado. A fórmula que usei é:
=IF(ISNUMBER(MATCH(TEXT(Data2015[@Period],"d/mm/yyyy"),Condition2015A[Background],0))=TRUE,Data2015[@Period],"")
Espero que faça sentido, eu teria postado fotos para mostrar exatamente o que consegui alcançar visualmente porque essencialmente o que fiz foi facilitar o uso de qualquer pessoa, mas ainda não tenho reputação suficiente para postar imagens! Talvez na próxima vez.
Filtros teriam funcionado, mas estou criando isso para pessoas que não têm muita experiência em Excel, então até pedir para filtrarem é um pouco demais (... eu sei, eu sei...)