Извличане на първата съответстваща стойност в клетка срещу списък
Да предположим, че имате списък с ключови думи, за да извлечете първата ключова дума, която се е появила в конкретна клетка, докато клетката съдържа няколко други стойности, ще трябва да използвате INDEX намлява MATCH формула с помощта на АГРЕГАТ намлява ТЪРСИ функции.
Как да извлека първата съответстваща стойност в клетка срещу списък?
За да получите първата съответстваща ключова дума в клетка срещу списъка с ключови думи както е показано в таблицата по-горе, това означава, че трябва да извършим съдържателно съвпадение, а не точно съвпадение. За да направите това, можете да използвате функцията ТЪРСЕНЕ, за да подадете позициите на ключовите думи, появили се в клетката в цифрови числа, във функцията АГРЕГАТ. Тогава AGGREGATE ще получи най-малкото число, като зададе неговото function_num на 15 и аргумента ref2 на 1. След това използвайте MATCH, за да намерите първата най-малка стойност и подайте номера на позиция към INDEX, за да извлечете стойността на тази позиция.
Общ синтаксис
=INDEX(keyword_rng,MATCH(AGGREGATE(15,6,SEARCH(keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))
√ Забележка: Това е формула за масив, която изисква да въведете с Ctrl + Превключване + Въведете.
- ключова дума: Диапазонът от клетки с ключови думи.
- търсеща_клетка: Клетката за търсене, ако съдържа ключовите думи.
За да извлечете първата съответстваща ключова дума се появи в клетка B5 срещу колоната Ключови думи, моля, копирайте или въведете формулата по-долу в клетка C5 и натиснете Ctrl + Превключване + Въведете за да получите резултата:
=ИНДЕКС($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH()$E$5:$E$7,B5),1),ТЪРСЕНЕ($E$5:$E$7,B5),0))
√ Забележка: Знаците за долар ($) по-горе показват абсолютни препратки, което означава ключова дума_rng във формулата няма да се промени, когато преместите или копирате формулата в други клетки. Въпреки това няма добавени знаци за долар търсеща_клетка тъй като искате да е динамично. След като въведете формулата, плъзнете манипулатора за попълване надолу, за да приложите формулата към клетките по-долу.
Обяснение на формулата
=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))
- ТЪРСЕНЕ($E$5:$E$7,B5): Функцията SEARCH връща позицията на всяка ключова дума от диапазона $E$5:$E$7 когато се намира в цифрови числа, и #VALUE! грешка, ако не бъде намерена. Резултатът ще бъде в масив като този: {15;11;#VALUE!}.
- AGGREGATE(15,6;XNUMX,ТЪРСЕНЕ($E$5:$E$7,B5), 1) = AGGREGATE(15,6;XNUMX,{15;11;#VALUE!}, 1): Функцията AGGREGATE с a функция_номер от 15 намлява вариант от 6 ще върне най-малката стойност в масива според ref2 аргумент 1, като същевременно игнорира стойностите на грешката. Така че фрагментът ще се върне 11.
- СЪВПАДА(AGGREGATE(15,6;XNUMX,ТЪРСЕНЕ($E$5:$E$7,B5), 1),ТЪРСЕНЕ($E$5:$E$7,B5), 0) = СЪВПАДА(11,{15;11;#VALUE!}, 0): - match_type 0 принуждава функцията MATCH да извърши точно съвпадение и да върне позицията на 11 в масива {15;11;#VALUE!}. Така че функцията ще се върне 2.
- ИНДЕКС($E$5:$E$7,СЪВПАДА(AGGREGATE(15,6;XNUMX,ТЪРСЕНЕ($E$5:$E$7,B5), 1),ТЪРСЕНЕ($E$5:$E$7,B5), 0)) = ИНДЕКС($E$5:$E$7,2): След това функцията INDEX връща 2рата стойност в диапазона $E$5:$E$7, кое е BBB.
Забележка
- Ако в клетка няма ключови думи, a #БРОЙ! ще бъде върната грешка.
- Формулата не е чувствителна към главни и малки букви. За да извършите съвпадение с разлика между главни и малки букви, можете просто да замените функцията ТЪРСЕНЕ с НАМЕРИ.
Свързани функции
Функцията ИНДЕКС на Excel връща показаната стойност въз основа на дадена позиция от диапазон или масив.
Функцията MATCH на Excel търси конкретна стойност в диапазон от клетки и връща относителната позиция на стойността.
В Excel функцията SEARCH може да ви помогне да намерите позицията на конкретен знак или подниз от дадения текстов низ, както е показано на следната екранна снимка. Този урок ще представя как да използвате тази функция ТЪРСЕНЕ в Excel.
Функцията AGGREGATE на Excel връща съвкупност от изчисления като SUM, COUNT, SMALLL и т.н. с опция за игнориране на грешки и скрити редове.
Свързани формули
Извличане на първата списъкна стойност от клетка
За да извлечете първата ключова дума, която е изброена в диапазон от конкретна клетка, докато клетката съдържа една от няколко стойности, ще трябва да използвате доста сложна формула за масив с функциите INDEX, MATCH, ISNUMBER и SEARCH.
Точно съвпадение с INDEX и MATCH
Ако трябва да разберете информацията, посочена в Excel за конкретен продукт, филм или човек и т.н., трябва да използвате добре комбинацията от функции INDEX и MATCH.
Проверете дали дадена клетка съдържа определен текст
Тук урокът предоставя някои формули за проверка дали дадена клетка съдържа конкретен текст и връща TRUE и FALSE, както е показано на екранната снимка по-долу, и обяснява аргументите и как работят формулите.
Проверете дали клетката съдържа всички много неща
Да предположим, че в Excel има списък със стойности в колона E, искате да проверите дали клетките в колона B съдържат всички стойности в колона E и да върнете TRUE или FALSE, както е показано на екранната снимка по-долу. Тук, в този урок, той предоставя формула за решаване на тази задача.
Проверете дали клетката съдържа едно от много неща
Този урок предоставя формула за проверка дали дадена клетка съдържа една от няколко стойности в Excel и обяснява аргументите във формулата и как работи формулата.
Най-добрите инструменти за производителност в офиса
Kutools за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
