☰

Добавление компонентов формулы (нетто против брутто)

tag-icon • tag-icon microsoft-excel worksheet-function
Добавление компонентов формулы (нетто против брутто)

Я хотел бы добавить компоненты формулы по всему диапазону. У нас есть один раздел, который показывает чистые денежные потоки для определенных элементов, и несколько определенных элементов.

Например, =8+9-5показывает "12" в нескольких ячейках. Если бы значения не были объединены на уровне ячеек, было бы легко использовать формулу, SUMIFчтобы сложить только положительные и отрицательные ячейки, но поскольку это объединено на уровне ячеек, я пытаюсь разобраться в базовых значениях и использовать эквивалент формулы, SUMIFно для всех базовых компонентов.

В приведенном выше примере я пытаюсь получить ячейку сводки для диапазона со значением «+17» и одну ячейку со значением «-5».

Как я могу это сделать?

решение1

Основная проблема заключается в том, как получить доступ к строке формулы другой ячейки из рабочего листа. Не существует функции рабочего листа, которая могла бы это сделать. Поэтому вам придется сделать одно из двух для начала: 1) использовать поиск и замену, чтобы изменить содержимое ячеек так, чтобы они больше не содержали формулы, а содержали строки, или 2) использовать VBA для доступа к строке формулы ячеек.

(1) можно выполнить, выбрав ячейки, которые вы хотите обработать, а затем нажав Ctrl+ H, чтобы открыть диалоговое окно «Заменить». Найдите =(предполагая, что единственные формулы, которые вас интересуют, похожи на ваш пример и не содержат последовательных знаков равенства) и замените ничем. Это изменит ячейку с формулой, =8+9-5отображающей значение 12, на ячейку, отображающую строку 8+9-5.

(2) можно выполнить, обратившись к свойству ячейки .Formula. Например, чтобы получить формулу ячейки A1, вы бы использовали простую UDF, например, такую:

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

Получив эти строки любым из методов, вам придется повозиться с функциями обработки строк, например, FIND, LEN, и VALUE, чтобы извлечь нужные вам значения. Поскольку это было бы пустой тратой вашего и моего времени, я воздержусь от этого здесь.

решение2

Добавьте лист в свою рабочую книгу; назовем его «Настройка».

В ячейку A1 поместите 8, в ячейку A2 поместите 9, в ячейку A3 поместите -5. Скопируйте по такому количеству столбцов, по которому эта формула с константами существует в столбцах на исходном листе. (В зависимости от того, как организован диапазон, вам может потребоваться лист настройки, чтобы использовать ячейки A1, B1, C1 и копировать вниз вместо этого. Идея состоит в том, чтобы создать диапазон, который соответствует форме диапазона на исходном листе.)

На исходном листе замените все формулы =8+9-5на =Setup!A1+Setup!A2+Setup!A3. Это можно сделать с помощью операции Find&Replace, которая вычисляет формулы.

Теперь вы можете создавать условные формулы на листе настройки вместо жестко запрограммированных значений на исходном листе.

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

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

Связанный контент