Формула на Excel: Проверете дали клетката съдържа всички много неща
Да предположим, че в Excel има списък със стойности в колона E, искате да проверите дали клетките в колона B съдържат всички стойности в колона E и да върнете TRUE или FALSE, както е показано на екранната снимка по-долу. Тук, в този урок, той предоставя формула за решаване на тази задача.
Обща формула:
| =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) |
Аргументи
| Things: the list of values that you want to use to check if argument text contains. |
| Text: the cell or text string you want to check if containing argument things. |
Върната стойност:
Тази формула връща логическа стойност FALSE: клетката не съдържа всички неща; ВЯРНО: клетката съдържа всички неща.
Как работи тази формула
Например в колона B има списък с текстови низове, които искате да проверите дали съдържат всички стойности в диапазона E3:E5, моля, използвайте формулата по-долу.
| =SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))=COUNTA($E$3:$E$5) |
Натискане Въведете клавиш, плъзнете манипулатора за запълване върху клетките, които искате да проверите. FALSE показва, че клетката не съдържа всички стойности в диапазона E3:E5, докато TURE показва, че относителната клетка съдържа всички стойности.
Обяснение
Функция SEARCH: функцията SEARCH ще върне местоположението на първия знак от текстовия низ в друг. Ако функцията SEARCH намери текста, тя връща позицията, ако не, връща #VALUE! грешка. Ето например формулата SEARCH($E$3:$E$5,B4) ще търси всяка стойност от диапазона E3:E5 в клетка B4 и връща местоположението на всеки текстов низ в клетка B4. Той ще върне резултат от масив като този: {1;7;12}
Функция ISNUMBER: функцията ISNUMBER ще провери дали дадена стойност е число и ще върне TRUE или False. Тук ISNUMBER(SEARCH($E$3:$E$5,B4)) ще върне резултат от масив като {true;true;true} тъй като функцията ТЪРСЕНЕ намира 3 числа.
--ISNUMBER(SEARCH($E$3:$E$5,B4)) ще преобразува стойността TRUE в 1 и ще преобразува стойността FALSE в 0, така че тази формула променя резултата от масива на {1;1;1}.
Функция SUMPRODUCT: използва се за умножаване на диапазони или сумиране на масиви и връща сумата от продуктите. The SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) връща 1+1+1=3.
Функция COUNTA: връща броя на непразните клетки. COUNTA($E$3:$E$5) връща 3, така че резултатът от SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) е равно на 3, цялата формула ще върне TRUE или ще върне FALSE.
Забележки:
Формулата =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) не е точно проверка. Вижте екранна снимка:
Примерен файл
Кликнете, за да изтеглите примерен файл
Относителни формули
- Брой клетки, равни на
С функцията COUNTIF можете да преброите клетки, които са равни или не съдържат определена стойност. - Пребройте клетки, които са равни на x или y
В някои случаи може да искате да преброите броя на клетките, които отговарят на един от двата критерия, в този случай можете да използвате функцията COUNTIF. - Пребройте клетки, равни на x и y
Тук тази статия представя формулата за преброяване на клетки, които междувременно отговарят на два критерия. - Брой клетки, които не са равни на
Тази статия говори за това как да използвате функцията COUNTIF, за да преброите броя клетки, които не са равни на конкретна стойност
Най-добрите инструменти за производителност в офиса
Kutools за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
