Espero receber ajuda para melhorar a abordagem que estou usando atualmente para somar e contar linhas de dados com base em filtros que meus usuários podem selecionar.
Atualmente, tenho 7 filtros que verifico se devo incluir uma linha no cálculo. Queremos adicionar filtros adicionais, mas não estou colidindo com o máximo de ifs aninhados. Eu também estou preocupado que eu possa ter erros na minha fórmula, devido à sua complexidade.
Assim, cada linha de dados tem características (dados de vendas) que estou verificando se devo incluir a linha no cálculo. Assim, uma linha de dados teria os seguintes tipos de características que eu filtraria:
Tipo de oferta (tudo, novo, renovação ou addon), Região (todos, leste, oeste, central), Concorrente (todos os nomes de concorrentes específicos), Filtro de data (data de início e de término) e Tamanho da oferta (tamanho inicial e tamanho final).
Em seguida, identificamos por que ganhamos ou perdemos o acordo em um amplo espectro de características. Indicamos se uma característica foi uma causa do resultado do negócio com o uso de "1" na célula para perdas e "0" para ganhos. Então, eu tenho colunas intituladas Preço, proposta, apresentação, referências da empresa, etc.
Se perdermos um contrato e acreditarmos que perdemos o acordo por causa do preço e das referências, eu teria um 1 em cada uma dessas colunas para essa linha de dados.
Depois, tenho uma seção de resumo em que estou tentando descobrir quantas transações perdemos devido a "preço" (conto o número de 1s que também atendem aos critérios de filtro) e quanto de receita perdemos devido ao preço (mesma fórmula, mas somando o tamanho da oferta em vez de ofertas de contagem).
Espero que isso lhe dê uma boa ideia do que estou trabalhando. Gostaria de poder aprimorar as fórmulas para que eu possa adicionar filtros adicionais conforme necessário, além de simplificá-las, se possível, para facilitar a manutenção.
A fórmula que estou usando agora para contar as perdas por um motivo específico é semelhante à seguinte (é basicamente a mesma fórmula para vitórias, mas eu conto zeros em vez de uns):
=IF(AND($CU$1="ALL", $CR$2="ALL", $CU$2="ALL"),COUNTIFS($Y$7:$Y$1000,"=1",$A$7:$A$1000,">="&$CN$1,$A$7:$A$1000,"<="&$CP$1,$J$7:$J$1000,">="&$CN$2,$J$7:$J$1000,"<="&$CP$2),
IF(AND($CU$1="ALL", $CR$2="ALL", NOT($CU$2="ALL")),COUNTIFS($Y$7:$Y$1000,"=1",$A$7:$A$1000,">="&$CN$1,$A$7:$A$1000,"<="&$CP$1,$J$7:$J$1000,">="&$CN$2,$J$7:$J$1000,"<="&$CP$2,$D$7:$D$1000,"="&$CU$2),
IF(AND($CU$1="ALL", NOT($CR$2="ALL"), $CU$2="ALL"),COUNTIFS($Y$7:$Y$1000,"=1",$A$7:$A$1000,">="&$CN$1,$A$7:$A$1000,"<="&$CP$1,$J$7:$J$1000,">="&$CN$2,$J$7:$J$1000,"<="&$CP$2,$K$7:$K$1000,"="&$CR$2),
IF(AND(NOT($CU$1="ALL"), $CR$2="ALL", $CU$2="ALL"),COUNTIFS($Y$7:$Y$1000,"=1",$A$7:$A$1000,">="&$CN$1,$A$7:$A$1000,"<="&$CP$1,$J$7:$J$1000,">="&$CN$2,$J$7:$J$1000,"<="&$CP$2,$M$7:$M$1000,"="&$CU$1),
IF(AND(NOT($CU$1="ALL"), NOT($CR$2="ALL"), $CU$2="ALL"),COUNTIFS($Y$7:$Y$1000,"=1",$A$7:$A$1000,">="&$CN$1,$A$7:$A$1000,"<="&$CP$1,$J$7:$J$1000,">="&$CN$2,$J$7:$J$1000,"<="&$CP$2,$M$7:$M$1000,"="&$CU$1,$K$7:$K$1000,"="&$CR$2),
IF(AND(NOT($CU$1="ALL"), NOT($CR$2="ALL"), NOT($CU$2="ALL")),COUNTIFS($Y$7:$Y$1000,"=1",$A$7:$A$1000,">="&$CN$1,$A$7:$A$1000,"<="&$CP$1,$J$7:$J$1000,">="&$CN$2,$J$7:$J$1000,"<="&$CP$2,$M$7:$M$1000,"="&$CU$1,$K$7:$K$1000,"="&$CR$2,$D$7:$D$1000,"="&$CU$2),
IF(AND($CU$1="ALL", NOT($CR$2="ALL"), NOT($CU$2="ALL")),COUNTIFS($Y$7:$Y$1000,"=1",$A$7:$A$1000,">="&$CN$1,$A$7:$A$1000,"<="&$CP$1,$J$7:$J$1000,">="&$CN$2,$J$7:$J$1000,"<="&$CP$2,$D$7:$D$1000,"="&$CU$2,$K$7:$K$1000,"="&$CR$2),
COUNTIFS($Y$7:$Y$1000,"=1",$A$7:$A$1000,">="&$CN$1,$A$7:$A$1000,"<="&$CP$1,$J$7:$J$1000,">="&$CN$2,$J$7:$J$1000,"<="&$CP$2,$M$7:$M$1000,"="&$CU$1,$D$7:$D$1000,"="&$CU$2))))))))
Portanto, para cada motivo do resultado (preço, proposta, referências, etc.), tenho que duplicar essa fórmula quatro vezes. Uma vez para contar as perdas, uma vez para somar as perdas, uma vez para contar as vitórias e uma vez para somar as vitórias.
O resultado final é que obtenho uma tabela parecida com a seguinte:
Decision Driver | Win Frequency | Won Revenue | Loss Frequency | Lost Revenue ----------------------------------------------------------------------------- Price | 23 | $230,000 | 12 | $165,000 Proposal | 12 | $140,000 | 16 | $195,000 etc...
Qualquer ajuda seria muito apreciada.
Tags microsoft-excel