Sugestões para criar e gerenciar funções complexas no Excel

Question

Com muita frequência, eu tenho uma fórmula que envolve funções dentro de funções dentro de funções, e quando alguma nova condição requer que eu envolva outra função em torno dela, eu frequentemente me vejo perdendo completamente de quais parênteses vão para onde, qual função se aplica a qual conjunto de parâmetros e geralmente me encontro depurando uma declaração por meia hora depois que minha modificação produz resultados inesperados.

O que eu tenho feito é cortar a função em suas partes individuais em uma coluna ... cada linha representando uma única função, e quando estou satisfeito que o resultado final é o que ele precisa ser, eu copio cada parte de volta em seu respectivo ponto na célula anterior até que eu tenha uma função completa e esperançosamente funcional.

CONSIDERAR:

         =IF((ISERROR((VLOOKUP(D2,$A$2:$A9,1,0))),(IF((D2=(VLOOKUP(D2,$A$2:$A$9,1,0))),0,D2)),E2)
0        =IF((ISERROR((VLOOKUP(D2,$A$2:$A9,1,0))),L17,E2)
TRUE     =ISERROR((VLOOKUP(D2,$A$2:$A9,1,0))
0        =IF((D2=L18),0,D2)
the      =VLOOKUP(D2,$A$2:$A$9,1,0)

Seria ótimo poder documentar in-line ou ter algo semelhante a um popup do VBA que edite fórmulas, mas como isso não existe, eu estaria interessado em saber Quais outras técnicas para efetivamente construir funções complexas que você achou úteis. Pensamentos?

    

Answer 1

Aqui estão algumas regras principais que uso sozinha e sugiro para a equipe durante os treinamentos:

1. Crie fórmulas complexas (especialmente IF s) parte por peça (ou argumento por argumento), ou seja, se você tiver uma coluna de dados que deseja analisar de acordo com determinada condição, comece com simples =IF(LEFT(A1,2)="AB",1,0) - assim você tem certeza de que a condição funciona como esperado e retorna resultados corretos para cada tipo de valores. Além disso, você substitui cada argumento por seus próprios cálculos (veja abaixo), mas você tem certeza de que qualquer erro / resultado errado NÃO é causado por condições errôneas em si, caso contrário os erros podem interferir e os resultados gerais das fórmulas são totalmente inesperados e extremamente difíceis. correto.
2. Cada argumento é preparado em colunas separadas como fórmula independente e, somente se funcionar, é mesclado à fórmula principal (ou megaformula como é chamado em algum Excel Bíblias).
3. Use valores codificados como soluções intermediárias . Por exemplo. Se você estiver criando um gráfico dinâmico, o intervalo geralmente é calculado usando as funções OFFSET e COUNTA . Então, primeiro construa seu OFFSET usando números puros, por exemplo =OFFSET(A1,0,0,1,10) , certifique-se de que retorna o que você realmente quer e só então comece a criar a parte calculada, por exemplo =OFFSET(A1,0,0,1,COUNTA(A:A)-1) .
4. Não negligencie o avaliador de fórmulas (Faixa de opções Formulas > Evaluate Formula ): para pessoas que o veem pela primeira vez é como um raio de luz) De fato, ter a sintaxe correta NÃO significa, em geral, esperar / corrigir resultados, e isso é como o depurador do Excel.

5. Para melhorar a legibilidade de fórmulas complexas, você pode usar novas separações de linha (podem ser adicionadas via ALT + ENTER na barra de fórmulas) bem como espaços extras para fazê-los parecer um código com recuos (todos esses são ignorados quando a fórmula é avaliada):

   =IF(
       LEFT(A1,2)="AB",
          MONTH(TODAY()),
          MONTH(B1)
      )
   

Espero que essas sugestões tornem a vida mais fácil. Certamente, outros respondentes adicionarão alguns pontos mais úteis.

    

Answer 2

Geralmente, dividir fórmulas maiores entre colunas é, na minha opinião, as melhores práticas!

No entanto, se você precisar criar fórmulas maiores, há algumas coisas que eu recomendaria:

1. Use intervalos nomeados para constantes - facilita a leitura de fórmulas - é melhor ter =IF(A1>SalesThreshold,A1,0) than =IF(D2>Assumptions!$B$239,D2,0)
2. Use as tabelas do Excel e a substituição das referências normais pelos nomes das colunas. Dessa forma, =IF(AND(YEAR(B2)=2013,C2="MyProduct",D2>SalesThreshold),D2,0) se torna =IF(AND(YEAR([Date])=2013,[Product]="MyProduct",[Sales]>SalesThreshold),[Sales],0)
3. Se a fórmula ainda for complexa, você poderá usar a função N() para comentários. A função irá converter qualquer texto para 0, assim você pode ter uma função como esta:

   =IF(
   N4>N20+N("A quick check"),
   N4^2+N("Nees to be squared because I cannot think of a better example"),
   N4/2+N("No idea what to comment here!"))
   

Espero que ajude!

    

Answer 3

FormulaDesk é uma nova ferramenta gratuita projetada para fazer exatamente o que você quer. Ele formata e exibe a fórmula de forma aninhada, com o resultado de cada sub-parte exibida, permitindo que você compreenda a massa rapidamente.

[Divulgação: eu sou o autor de FormulaDesk]