Загальна формула
=IF(logical_test,"P","")
Резюме
Щоб відобразити галочку, якщо значення "дозволено" на основі існуючого списку допустимих значень, ви можете використовувати формулу на основі функції IF разом із функцією COUNTIF. У прикладі шоу формула в C5 має вигляд:
=IF(COUNTIF(allowed,B5),"✓","")
де дозволений - названий діапазон E5: E9.
Пояснення
Ця формула є хорошим прикладом вкладання однієї функції всередину іншої. По суті, ця формула використовує функцію IF, встановлену для повернення позначки (✓), коли логічний тест повертає TRUE:
=IF(logical_test,"✓","")
Якщо тест повертає FALSE, формула повертає порожній рядок (""). Для логічного тесту ми використовуємо функцію COUNTIF так:
COUNTIF(allowed,B5)
COUNTIF підраховує випадки появи значення в B5 у дозволеному іменованому діапазоні (E5: E9). Це може здатися вам «назад», але якщо ви задумаєтесь, це має сенс. Якщо значення в B5 знайдено в дозволеному списку значень, COUNTIF поверне позитивне число (у цьому випадку 1). Якщо ні, COUNTIF поверне нуль. Excel оцінить будь-яке ненульове число як TRUE, тому це чудово працює як логічний тест для IF.
IF поверне значення TRUE, лише якщо значення знайдено у дозволеному списку, і, якщо так, кінцевим результатом є галочка (✓). Якщо значення не знайдено в дозволеному списку, COUNTIF повертає нуль, який обчислюється як FALSE. У цьому випадку кінцевим результатом є порожній рядок (""), який нічого не відображає.
З фіксованими значеннями
У наведеному вище прикладі показано дозволені значення в діапазоні комірок, але дозволені значення також можуть бути жорстко закодовані у формулах як константа масиву, як це:
=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")),"✓","")
Функція NOT повертає результат з COUNTIF. Якщо ви вважаєте це заплутаним, ви можете по черзі реструктуризувати формулу IF так:
=IF(COUNTIF(B5:F5,"<65"),"","✓")
У версії формули логіка більш схожа на вихідну формулу вище. Однак ми перемістили галочку до аргументу value_if_false, тому позначка з'явиться лише в тому випадку, якщо відлік від COUNTIF дорівнює нулю. Іншими словами, галочка з’явиться лише тоді, коли не знайдено значень менше 65.
Примітка: Ви також можете використовувати умовне форматування, щоб виділити дійсне або недійсне введення, а перевірку даних обмежити для введення, щоб дозволити лише дійсні дані.
