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

Question

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

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

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

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

Answer 1

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

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

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

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

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

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

Answer 2

Главная проблема заключается в том, как получить доступ к строке формулы другой ячейки из рабочего листа. Нет существующей функции рабочего листа, которая могла бы сделать это. Итак, для начала вам нужно будет выполнить одно из двух: 1) использовать find-and-replace, чтобы изменить содержимое ячеек, чтобы они больше не содержали формулы, а вместо этого содержали строки, или 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 , чтобы извлечь нужные значения. Поскольку делать это было бы пустой тратой вашего и моего времени, я воздержусь от этого здесь.