Обща формула
=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.
Забележка: можете също да използвате условно форматиране, за да подчертаете валидно или невалидно въвеждане, и проверка на данни, за да ограничите въвеждането, за да разрешите само валидни данни.
