Общая формула
=IF(logical_test,"P","")
Резюме
Чтобы отобразить галочку, если значение «разрешено» на основе существующего списка допустимых значений, вы можете использовать формулу, основанную на функции ЕСЛИ вместе с функцией СЧЁТЕСЛИ. В показанном примере формула в C5:
=IF(COUNTIF(allowed,B5),"✓","")
где разрешен именованный диапазон E5: E9.
Объяснение
Эта формула - хороший пример вложения одной функции в другую. По сути, эта формула использует функцию ЕСЛИ, настроенную для возврата галочки (✓), когда логический тест возвращает ИСТИНА:
=IF(logical_test,"✓","")
Если тест возвращает FALSE, формула возвращает пустую строку («»). Для логической проверки мы используем функцию СЧЁТЕСЛИ:
COUNTIF(allowed,B5)
СЧЁТЕСЛИ подсчитывает вхождения значения в B5 в разрешенном именованном диапазоне (E5: E9). Вам это может показаться "отсталым", но если вы задумаетесь, это имеет смысл. Если значение в B5 находится в разрешенном списке значений, СЧЁТЕСЛИ вернет положительное число (в данном случае 1). Если нет, СЧЁТЕСЛИ вернет ноль. Excel будет оценивать любое ненулевое число как ИСТИНА, поэтому это отлично работает в качестве логического теста на ЕСЛИ.
IF вернет TRUE только в том случае, если значение найдено в разрешенном списке, и, если это так, окончательным результатом будет галочка (✓). Если значение не найдено в разрешенном списке, СЧЁТЕСЛИ возвращает ноль, что оценивается как ЛОЖЬ. В этом случае конечным результатом будет пустая строка (""), которая ничего не отображает.
С фиксированными значениями
В приведенном выше примере показаны допустимые значения в диапазоне ячеек, но допустимые значения также могут быть жестко закодированы в формулы как константа массива, например:
=IF(COUNTIF(("red","blue","green"),B5),"✓","")
Символ галочки (✓)
Вставка символа галочки в Excel может оказаться на удивление сложной задачей, и вы найдете множество статей в Интернете, объясняющих различные подходы. Самый простой способ получить символ галочки (✓), используемый в этой формуле, в Excel - просто скопировать и вставить его. Если вы копируете с этой веб-страницы, вставьте в строку формул, чтобы избежать перетаскивания при нежелательном форматировании. Вы также можете копировать и вставлять прямо из прикрепленного листа.
Если у вас возникли проблемы с копированием и вставкой, попробуйте этот вариант. Сам символ - это Unicode 2713 (U + 2713), и его также можно ввести в Excel с помощью функции UNICHAR следующим образом:
=UNICHAR(10003) // returns "✓"
Итак, исходную формулу можно записать так:
=IF(COUNTIF(allowed,B5),UNICHAR(10003),"")
Примечание: функция UNICHAR была представлена в Excel 2013.
Расширение формулы
Основная идея этой формулы может быть расширена множеством хитрых способов. Например, на снимке экрана ниже показана формула, которая возвращает галочку только в том случае, если все результаты тестов составляют не менее 65:
Формула в G5:
=IF(NOT(COUNTIF(B5:F5,"<65")),"✓","")
Функция НЕ обращает результат СЧЁТЕСЛИ. Если это вас сбивает с толку, вы можете поочередно реструктурировать формулу ЕСЛИ следующим образом:
=IF(COUNTIF(B5:F5,"<65"),"","✓")
В версии формулы логика больше похожа на исходную формулу выше. Однако мы переместили галочку на аргумент value_if_false, поэтому галочка появится только в том случае, если счетчик COUNTIF равен нулю. Другими словами, галочка появится только тогда, когда не будет найдено ни одного значения меньше 65.
Примечание: вы также можете использовать условное форматирование, чтобы выделить допустимый или недопустимый ввод, и проверку данных, чтобы ограничить ввод, чтобы разрешить только действительные данные.
