Отримати перше відповідне значення в клітинці зі списком
Припустимо, у вас є список ключових слів, щоб отримати перше ключове слово, яке з’явилося в певній клітинці, тоді як клітинка містить кілька інших значень, вам доведеться використовувати ІНДЕКС та 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 + Shift + Що натомість? Створіть віртуальну версію себе у .
- keyword_rng: Діапазон клітинок з ключовими словами.
- пошукова_комірка: Комірка для пошуку, якщо вона містить ключові слова.
Щоб отримати файл перше відповідне ключове слово з’явилося в клітинці B5 у стовпці Ключові слова, скопіюйте або введіть формулу нижче в клітинку C5 і натисніть Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у щоб отримати результат:
= ІНДЕКС ($ E $ 5: $ E $ 7,ВІДПОВІДНИК(АГРЕГАТ(15,6,ПОШУК($ E $ 5: $ E $ 7,B5),1),ШУКАТИ($ E $ 5: $ E $ 7,B5), 0))
√ Примітка. Знаки долара ($) вище вказують на абсолютні посилання, тобто keyword_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;#ЗНАЧЕННЯ!}.
- AGREGATE(15,6,ПОШУК($E$5:$E$7,B5), 1) = AGREGATE(15,6,{15;11;#ЗНАЧЕННЯ!}, 1): Функція AGGREGATE з a число_функції 15 та варіант 6 поверне найменше значення в масиві відповідно до ref2 аргумент 1, ігноруючи значення помилок. Отже, фрагмент повернеться 11.
- МАТЧ (AGREGATE(15,6,ПОШУК($E$5:$E$7,B5), 1),ПОШУК($E$5:$E$7,B5), 0) = МАТЧ (11,{15;11;#ЗНАЧЕННЯ!}, 0): Команда match_type 0 змушує функцію MATCH виконувати точну відповідність і повертати позицію 11 в масиві {15;11;#ЗНАЧЕННЯ!}. Отже, функція повернеться 2.
- ІНДЕКС($E$5:$E$7,МАТЧ (AGREGATE(15,6,ПОШУК($E$5:$E$7,B5), 1),ПОШУК($E$5:$E$7,B5), 0)) = ІНДЕКС($E$5:$E$7,2): Потім функція INDEX повертає значення 2nd значення в діапазоні $ E $ 5: $ E $ 7, Яка є БББ.
Зауваження
- Якщо в комірці немає ключових слів, a #NUM! буде повернута помилка.
- Формула не чутлива до регістру. Щоб виконати відповідність з урахуванням регістру, ви можете просто замінити функцію SEARCH на ЗНАЙТИ.
Супутні функції
Функція Excel INDEX повертає відображене значення на основі заданої позиції з діапазону або масиву.
Функція Excel MATCH здійснює пошук певного значення в діапазоні комірок і повертає відносне положення значення.
В Excel функція ПОШУК може допомогти вам знайти позицію певного символу або підрядка з даного текстового рядка, як показано на наступному знімку екрана. У цьому підручнику я розповім, як використовувати цю функцію ПОШУК в Excel.
Функція Excel AGGREGATE повертає сукупність обчислень, наприклад 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.
