☰

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

tag-icon • tag-icon microsoft-excel worksheet-function
Показывать результат формулы только в том случае, если он соответствует заданным критериям

Мне нравится поддерживать чистоту в своих таблицах 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 — это результат, который нужно отобразить, если условие совпало. В случае, если условие не совпало, функция возвращает результат базовой формулы.

решение1

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

решение2

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

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))

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

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

решение3

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

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

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

Если условие гашения истинно, примените пользовательский числовой формат;;;

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

IFERROR(value, value_if_error)

Из справки Excel:

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

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