Adicionando componentes de fórmula (líquido vs. bruto)

Question

Gostaria de adicionar os componentes de uma fórmula em um intervalo. Temos uma seção que mostra os fluxos de caixa líquidos para itens específicos e vários itens específicos.

Por exemplo, =8+9-5 mostra "12" em várias células. Se os valores não tivessem sido capturados em um nível de célula, seria fácil usar uma fórmula SUMIF para adicionar apenas as células positivas e negativas, mas, como ela é capturada no nível da célula, estou tentando acessar as células subjacentes. valores e use o equivalente à fórmula SUMIF , mas para todos os componentes subjacentes.

Para o exemplo acima, estou tentando obter uma célula de resumo para o intervalo para dizer "+17" e uma célula para mostrar "-5".

Como posso fazer isso?

    

Answer 1

O problema principal é como acessar a string de fórmula de outra célula da planilha. Não há nenhuma função de planilha existente que possa fazer isso. Assim, você terá que fazer uma das duas coisas para começar: 1) use localizar e substituir para alterar o conteúdo das células para que elas não tenham mais fórmulas, mas mantenha sequências de caracteres, ou 2) use o VBA para acesse a string de fórmula das células.

(1) pode ser feito selecionando as células que você quer manipular e então pressionando Ctrl + H para abrir a caixa de diálogo Substituir. Pesquise por = (assumindo que as únicas fórmulas em que você está interessado são como seu exemplo e não inclua sinais de igual subseqüentes) e substitua por nada. Isso mudará uma célula com a fórmula =8+9-5 mostrando o valor 12 para uma célula mostrando a string 8+9-5 .

(2) pode ser realizado acessando a propriedade da célula .Formula . Por exemplo, para obter a fórmula da célula A1, você usaria uma UDF simples como esta:

Public Function GetFormula(r as Range) as String
   GetFormula = r.Formula
End Function

Depois de ter essas sequências, por qualquer método, você terá que ficar esperto com as funções de manipulação de string, por exemplo, FIND , LEN e VALUE , para extrair os valores desejados. Como isso seria um desperdício do seu tempo e meu, vou me abster de fazer isso aqui.

    

Answer 2

Adicione uma folha à sua pasta de trabalho; vamos chamar de "Setup".

Em A1 coloque 8 , em A2 coloque 9 , em A3 coloque -5 . Copie entre tantas colunas quanto essa fórmula com constantes nas colunas da sua planilha original. (Dependendo de como o intervalo é organizado, você pode precisar da folha de configuração para usar A1, B1, C1 e copiar para baixo. A idéia é criar um intervalo que corresponda à forma do intervalo na planilha original.)

Na folha original, substitua todas as fórmulas =8+9-5 por =Setup!A1+Setup!A2+Setup!A3 . Isso pode ser feito com a operação Localizar e substituir que avalia as fórmulas.

Agora você pode criar fórmulas condicionais na planilha Configuração, em vez dos valores codificados na planilha original.

=sumif(Setup!A1:A3,">0")

=sumif(Setup!A1:A3,"<0")