Показывать результат формулы, только если он соответствует заданным критериям

Question

Мне нравится, чтобы мои таблицы Excel были достаточно чистыми. Поэтому часто я заканчиваю тем, что пишу такие заявления:

IF([formula x]=[value],"",[formula x])

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

В некоторых случаях [formula x] повторяется по всему столбцу ячеек, и каждая ячейка имеет ссылку в [formula x] которая указывает на ячейку над ней. Чтобы избежать ошибок формул, я должен добавить еще один слой, например:

IF(C2="","",IF([formula x]=[value],"",[formula x])

Однако, особенно, когда [formula x] очень длинная, это может привести к тому, что конечная формула будет выглядеть намного сложнее, чем она есть, и ее будет намного сложнее устранять и устранять, чем следует.

Вот один ужасающий пример ...

Базовая формула:

=IF(A3>=$E$11,C2+(C2*($F$2/12))-$E$9,C2+(C2*($F$2/12))-$E$7)

Добавить условное гашение:

=IF(C2="","",IF(IF(A3>=$E$11,C2+(C2*($F$2/12)-$E$9),C2+(C2*($F$2/12))-$E$7)<=0,"",IF(A3>=$E$11,C2+(C2*($F$2/12))-$E$9,C2+(C2*($F$2/12))-$E$7)))

Уже очень длинная формула более чем удвоена по размеру, так что я могу отображать пробел (или любое другое выбранное значение, если на то пошло), когда эта же формула удовлетворяет заданному условию. Если я попытаюсь сделать это по всей строке, в зависимости от начальной формулы, я легко столкнусь с ошибками циклических ссылок.

Есть ли способ самостоятельно ссылаться на существующую формулу или аргумент в той же ячейке, или, может быть, другую функцию или функцию, которые можно использовать для более чистого достижения этого результата?

Функция, которая делает то, что я ищу, может быть такой:

=FnName([base formula],[match condition],[condition result])

Аргумент 1 - это базовая формула, аргумент 2 - это условие, которое мне интересно сопоставить. Аргумент 3 - это результат отображения, если условие соответствует. В случае, если условие не соответствует, функция возвращает результат базовой формулы.

Answer 1

Задумывались ли вы об оценке формулы в скрытой ячейке (или в ячейке на другом рабочем листе), а затем выполните условное гашение на основе значения в скрытой ячейке вместо того, чтобы вводить формулу дважды. Я не знаю всего контекста, в котором вы работаете, но я делал нечто подобное с некоторым успехом в прошлом.

Answer 2

Вы можете прибегнуть к функции VBA для очистки синтаксиса формулы. Например, вы можете поместить что-то вроде этого в модуль (нажмите Alt+F11, затем Insert >> Module):

Option Explicit
Public Function BLANKIF(checkcell As String, notb As Variant, Optional checkcond As String) As Variant
If checkcell = checkcond Then
    BLANKIF = ""
Else
    BLANKIF = notb
End If
End Function

Таким образом, чтобы использовать это, чтобы применить условное гашение к

=IF(A3>=$E$11,C2+(C2*($F$2/12))-$E$9,C2+(C2*($F$2/12))-$E$7)

Вы бы использовали

=BLANKIF(C2,IF(A3>=$E$11,C2+(C2*($F$2/12))-$E$9,C2+(C2*($F$2/12))-$E$7))

Если вы хотите очистить ячейку, если C2 = "omg", вы бы добавили третий необязательный аргумент:

=BLANKIF(C2,IF(A3>=$E$11,C2+(C2*($F$2/12))-$E$9,C2+(C2*($F$2/12))-$E$7),"omg")

Answer 3

Вы можете использовать Conditional Formatting для достижения своей цели.

Удалите все лишние элементы из формулы, оставив только базовую формулу

Примените Format only cells that contain условие с вашими критериями blnaking.

Если условие гашения истинно, применяется формат нестандартного номера ;;;

Альтернатива для Excel 2007 или более поздней

IFERROR(value, value_if_error)

Из справки Excel:

Возвращает значение, указанное вами, если формула приводит к ошибке; в противном случае возвращает результат формулы. Используйте функцию IFERROR для перехвата и обработки ошибок в формуле.