Формула на Excel: Намерете най-често срещания текст с критерии
В някои случаи може да искате да намерите текста, който се появява най-често въз основа на критерий в Excel. Този урок въвежда формула за масив за справяне с тази задача и дава обяснение за аргументите на тази формула.
Обща формула:
| =INDEX(rng_1,MODE(IF(rng_2=criteria,MATCH(rng_1,rng_1,0)))) |
Аргументи
| Rng_1: the range of cells that you want to find the most frequent text. |
| Rng_2: the range of cells that contain the criteria you want to use. |
| Criteria: the condition you want to find text based on. |
Върната стойност
Тази формула връща най-често срещания текст с конкретен критерий.
Как работи тази формула
Пример: Има набор от клетки, изброяващи продукти, инструменти и потребители, сега искате да намерите най-често използвания инструмент за всеки продукт. Моля, използвайте формулата по-долу в клетка G3:
| =INDEX($C$3:$C$12,MODE(IF($B$3:$B$12=F3,MATCH($C$3:$C$12,$C$3:$C$12,0)))) |
Натискане Shift + Ctrl + Enter ключовете заедно, за да получите правилния резултат. След това плъзнете манипулатора за запълване надолу, за да приложите тази формула.
Обяснение
MATCH($C$3:$C$12,$C$3:$C$12,0): На MATCH функцията връща местоположението на lookup_value в ред или колона. Тук формулата връща резултата от масива {1;2;3;4;2;1;7;8;9;7}, който намира позицията на всеки текст в диапазон $C$3:$C$12.
IF($B$3:$B$12=F3,MATCH($C$3:$C$12,$C$3:$C$12,0)): На IF функция се използва за задаване на условие. Тук тази формула се разглежда като IF($B$3:$B$12=”KTE”,{1;2;3;4;2;1;7;8;9;7}) и резултатът от масива се връща ={1;FALSE;3;FALSE;FALSE;1;FALSE;FALSE;9;FALSE}.
MODE(IF($B$3:$B$12=F3,MATCH($C$3:$C$12,$C$3:$C$12,0))): Функцията MODE намира най-често срещания текст в диапазон. Тук тази формула ще намери най-често срещаното число в резултата от масива на функцията IF, което може да се види като MODE({1;FALSE;3;FALSE;FALSE;1;FALSE;FALSE;9;FALSE}) и връща 1.
INDEX function: На INDEX функцията връща стойността в таблица или масив въз основа на даденото местоположение. Ето формулата. INDEX($C$3:$C$12,MODE(IF($B$3:$B$12=F3,MATCH($C$3:$C$12,$C$3:$C$12,0)))) ще бъде свито до INDEX($C$3:$C$12,1).
Забележка
Ако има два или повече най-чести текста, формулата ще върне стойността, която се появява първа.
Примерен файл
Кликнете, за да изтеглите примерен файл
Относителни формули
- Проверете дали дадена клетка съдържа определен текст
За да проверите дали дадена клетка съдържа някои текстове в диапазон A, но не съдържа текстовете в диапазон B, можете да използвате формула за масив, която комбинира функцията COUNT, SEARCH и AND в Excel - Проверете дали дадена клетка съдържа една от няколко стойности, но изключва други стойности
Този урок ще предостави формула за бързо справяне със задачата, която проверява дали дадена клетка съдържа едно от нещата, но изключва други стойности в Excel и обяснява аргументите на формулата. - Проверете дали клетката съдържа едно от нещата
Да предположим, че в Excel има списък със стойности в колона E, искате да проверите дали клетките в колона B съдържат всички стойности в колона E и да върнете TRUE или FALSE. - Проверете дали клетката съдържа число
Понякога може да искате да проверите дали дадена клетка съдържа цифрови знаци. Този урок предоставя формула, която ще върне TRUE, ако клетката съдържа число, FALSE, ако клетката не съдържа число.
Най-добрите инструменти за производителност в офиса
Kutools за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
