☰

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

tag-icon • tag-icon microsoft-excel worksheet-function
Adicionando componentes da fórmula (líquido vs. bruto)

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-5mostra "12" em várias células. Se os valores não fossem compensados ​​no nível da célula, seria fácil usar uma SUMIFfórmula para adicionar apenas as células positivas e negativas, mas como ela é compensada no nível da célula, estou tentando me aprofundar nos valores subjacentes e usar o equivalente à SUMIFfórmula, mas para todos os componentes subjacentes.

Para o exemplo acima, estou tentando fazer com que uma célula de resumo do intervalo diga "+17" e uma célula mostre "-5".

Como posso fazer isso?

Responder1

A questão principal é como acessar a sequência de fórmulas de outra célula da planilha. Não existe nenhuma função de planilha que possa fazer isso. Então, você terá que fazer uma de duas coisas para começar: 1) usar localizar e substituir para alterar o conteúdo das células para que elas não contenham mais fórmulas, mas sim strings, ou 2) usar VBA para acesse a sequência de fórmulas das células.

(1) pode ser feito selecionando as células que deseja manipular e pressionando Ctrl+ Hpara abrir a caixa de diálogo Substituir. Pesquise =(supondo que as únicas fórmulas nas quais você está interessado sejam como o seu exemplo e não incluam sinais de igual subsequentes) e substitua por nada. Isso mudará uma célula com fórmula =8+9-5mostrando 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, mais ou menos assim:

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

Depois de ter essas strings, por qualquer método, você terá que ser esperto com as funções de manipulação de strings, por exemplo, FIND, LEN, e VALUE, para extrair os valores desejados. Como fazer isso seria uma perda de tempo tanto para você quanto para mim, vou me abster de fazer isso aqui.

Responder2

Adicione uma planilha à sua pasta de trabalho; vamos chamá-lo de "Configuração".

Em A1 coloque 8, em A2 coloque 9, em A3 coloque -5. Copie tantas colunas quanto a fórmula com constantes existir nas colunas da sua planilha original. (Dependendo de como o intervalo está organizado, você pode precisar que a planilha de configuração use A1, B1, C1 e copie. A ideia é criar um intervalo que corresponda ao formato do intervalo na planilha original.)

Na planilha original, substitua todas as fórmulas =8+9-5por =Setup!A1+Setup!A2+Setup!A3. Isto pode ser feito com uma operação Find&Replace 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")

informação relacionada