Note: The other languages of the website are Google-translated. Back to English

Як vlookup значення з кількома критеріями в Excel?

В Excel ми можемо швидко переглядати відповідні дані у списку на основі конкретного критерію, який ви встановили за допомогою функції vlookup. Але якщо вам потрібно шукати відносне значення на основі кількох критеріїв, як показано на знімку екрана, як ви можете з цим боротися?

Значення перегляду з кількома критеріями з функцією LOOKUP

Значення перегляду з кількома критеріями з функцією INDEXT та MATCH

Значення перегляду з кількома критеріями з корисною функцією


Припустимо, у мене є такий діапазон даних, який я хочу використовувати за двома критеріями для повернення відносного значення, наприклад, я знаю, що товар і колір повинні повернути відповідного продавця в тому ж рядку:


Значення перегляду з кількома критеріями з функцією LOOKUP

Функція LOOKUP може допомогти вам вирішити цю проблему, введіть цю формулу у вказану клітинку та натисніть вводити ключ, щоб отримати правильний результат, який ви хотіли б, див. скріншот:

=LOOKUP(2,1/($A$2:$A$12=G2)/($C$2:$C$12=H2),($E$2:$E$12))

примітки: У наведеній вище формулі:

  • A2: A12 = G2: що означає пошук критеріїв G2 в діапазоні A2: A12;
  • C2: C12 = H2: означає пошук критеріїв H2 в діапазоні C2: C12;
  • E2: E12: відноситься до діапазону, якому потрібно повернути відповідне значення.

чайові: Якщо у вас є більше двох критеріїв, вам просто потрібно додати критерії у формулу так: =LOOKUP(2,1/($A$2:$A$12=G2)/($B$2:$B$12=H2)/($C$2:$C$12=I2),($E$2:$E$12)).


Значення перегляду з кількома критеріями з функцією INDEXT та MATCH

У Excel змішана функція INDEXT та MATCH є потужною для нас, щоб виконувати пошук значень на основі одного або декількох критеріїв, щоб знати цю формулу, виконайте наступне:

Введіть формулу нижче у порожню комірку та натисніть Ctrl + Shift + Enter клавіші разом, тоді ви отримаєте відносне значення, як хочете, див. скріншот:

=INDEX($E$2:$E$12,MATCH(1,($A$2:$A$12=G2)*($C$2:$C$12=H2),0))

примітки: У наведеній вище формулі:

  • A2: A12 = G2: що означає пошук критеріїв G2 в діапазоні A2: A12;
  • C2: C12 = H2: означає пошук критеріїв H2 в діапазоні C2: C12;
  • E2: E12: відноситься до діапазону, якому потрібно повернути відповідне значення.

чайові: Якщо у вас є більше двох критеріїв, вам просто потрібно додати критерії у формулу так: =INDEX($E$2:$E$12,MATCH(1,($A$2:$A$12=G2)*($B$2:$B$12=H2)*($C$2:$C$12=I2),0)).


Значення перегляду з кількома критеріями з корисною функцією

Якщо у вас є Kutools для Excel, З його Пошук кількох умов Ви можете швидко повернути відповідні значення на основі кількох критеріїв, скільки вам потрібно.

Примітка:Щоб застосувати це Пошук кількох умов, по-перше, вам слід завантажити Kutools для Excel, а потім швидко та легко застосувати функцію.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Клацання Кутулс > Супер ПОГЛЯД > Багатокондиційний пошук, див. скріншот:

2, в Пошук кількох умов діалоговому вікні, виконайте такі дії:

  • (1.) У Значення пошуку розділ, вкажіть діапазон значень підстановки або виберіть по черзі стовпець значень підстановки, утримуючи Ctrl ключ, на основі якого ви бажаєте переглянути значення vlookup;
  • (2.) У Вихідний діапазон розділ, виберіть діапазон виводу, куди ви хочете помістити відповідні результати;
  • (3.) У Ключова колонка розділ, будь ласка, виберіть відповідні стовпці ключів, які містять значення пошуку по одному, утримуючи Ctrl ключ;
  • примітки: Кількість стовпців, вибраних у Ключова колонка поле повинно дорівнювати кількості стовпців, вибраних у полі Значення пошуку і порядок кожного вибраного стовпця в полі Ключова колонка поле повинно відповідати одному до одного із стовпцями критеріїв у Значення пошуку поле.
  • (4.) У Колонка повернення розділ, виберіть стовпець, що містить повернені значення, які вам потрібні.

3. Потім натисніть кнопку OK or Застосовувати кнопку, всі зіставлені значення на основі кількох критеріїв були витягнуті відразу, див. знімок екрана:


Більше відносних статей:

  • Значення перегляду на кількох робочих аркушах
  • У Excel ми можемо легко застосувати функцію vlookup для повернення відповідних значень в одній таблиці робочого аркуша. Але чи замислювались ви коли-небудь про те, як здійснити пошук значення на кількох робочих аркушах? Припустимо, що у мене є такі три аркуші з діапазоном даних, і тепер я хочу отримати частину відповідних значень на основі критеріїв з цих трьох аркушів, як вирішити цю роботу в Excel?
  • Vlookup для повернення порожнього або конкретного значення замість 0 або N / A в Excel
  • Зазвичай, коли ви застосовуєте функцію vlookup для повернення відповідного значення, якщо ваша відповідна комірка порожня, вона поверне 0, а якщо ваше відповідне значення не знайдено, ви отримаєте значення помилки # N / A, як показано на знімку екрана нижче. Замість того, щоб відображати значення 0 або # N / A, як ви можете зробити так, щоб воно відображало порожню комірку або інше конкретне текстове значення?
  • Перегляд та повернення відповідних даних між двома значеннями
  • В Excel ми можемо застосувати звичайну функцію Vlookup, щоб отримати відповідне значення на основі даних. Але, іноді, ми хочемо здійснити пошук і повернути значення відповідності між двома значеннями, як показано на наведеному нижче знімку екрана, як би ви могли впоратися з цим завданням у Excel?

Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (10)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
Привіт, дякую за цей підручник, він дуже корисний. Наступна формула чудово працює. =ПОШУК(2,1/(B:B=H97)/(I:I=H98),E:E). У мене просте запитання. Я хочу, щоб клітинка отримала значення if (H98 = відкрито) Якщо "open" немає в (I:I) збігається (H99 = Під спостереженням) з (I:I) і отримати значення, якщо можливо отримати ряд. Я хочу, щоб формула була максимально спрощеною. Оскільки я буду копіювати цю формулу в багатьох клітинках. Також будь ласка, запропонуйте, яка з наведених вище формул (ПОШУК/ПРОИЗВОД/ІНДЕКС) менш інтенсивна для процесора.
Цей коментар був мінімізований модератором на сайті
=ПОШУК(2,1/(A2:A10=G2)/(B2:B10=G3),(D2:D10)) що означає 2?
Цей коментар був мінімізований модератором на сайті
Дякую за цей підручник; :-) У мене є питання. Яку формулу я повинен використовувати? У мене є ряд даних у рядку, наприклад A1:M1, я хотів би отримати результат: якщо є/є дані, які є/є < або > у певному числі, це призведе до "Дискваліфіковано", якщо це правда або " " (пробіл), якщо false.
Цей коментар був мінімізований модератором на сайті
Це елегантна формула, яка також легко розширюється до інших критеріїв. Єдиним недоліком формул INDEX+MATCH є те, що вони дійсно повільні у великих наборах даних.
Цей коментар був мінімізований модератором на сайті
На мою особисту думку, збіг індексів має бути швидшим. Це також перевірено багатьма. Якщо в масиві використовується відповідність індексу, це, безумовно, буде повільніше, оскільки стане схожим на нестабільну формулу. У наведеній вище формулі використовується відповідність індексу в масиві для умови кількох критеріїв, які насправді також можна змінити на тип без масиву;)
Цей коментар був мінімізований модератором на сайті
ти занадто геній, ти вирішив мою проблему.
Цей коментар був мінімізований модератором на сайті
Пошук не спрацює, якщо в комірці є формула, що таке засіб??
Цей коментар був мінімізований модератором на сайті
Як мені це зробити?
100 100100 ABC100101 DEF101102103 HIJ103
Результати, які я хочу
100 ABC
100 ABC
100 ABC
100 ABC
101 ЗАХВ
101 ЗАХВ
102
103 HIJ
103 HIJ

яку формулу я повинен використовувати?
Дякую
Цей коментар був мінімізований модератором на сайті
У мене є аркуш, де 2 значення мають бути перевірені з таблиці, доступної в іншому файлі, в якому 2 значення з аркуша є загальними, і після відповідності обома критеріям, наприклад, розміру та типу з таблиці, він повинен фіксувати ціну
Цей коментар був мінімізований модератором на сайті
Привіт Excelmaster,
Як ти? Ви можете знайти значення в іншому файлі. Дозвольте мені показати вам два шляхи. 
Рішення 1:
На фото 1 аркуш 1 містить оригінальні дані про товар. На фото 2 нам потрібно знати ціну деяких предметів. Ми можемо скористатися допомогою нової функції Excel XLOOKUP, щоб зробити трюк. Синтаксис: =XLOOKUP(значення_шукання, масив_пошуку, масив_повернення, [якщо_не_знайдено], [режим_відповідності], [режим_пошуку]). Пропускаючи додаткові аргументи, =XLOOKUP(значення_шукання) , lookup_array, return_array)У клітинку E2 аркуша2, будь ласка, введіть формулу: =XLOOKUP(A2&B2&C2,Sheet1!A2:A12&Sheet1!B2:B12&Sheet1!C2:C12,Sheet1!D2:D12) Тоді ви отримаєте ціну. E2. Щоб отримати решту результату, нам потрібно зберегти абсолютні масиви у формулі E2. Тоді формула виглядає так:=XLOOKUP(A2&B2&C2,Sheet1!$A$2:$A$12&Sheet1!$B$2:$B$12&Sheet1!$ C$2:$C$12,Аркуш1!$D$2:$D$12)
Потім перетягніть маркер автозаповнення вниз, щоб отримати решту результатів.
Рішення 2: Використовуйте функцію пошуку за багатьма умовами в Kutools for Excel. Усі результати будуть повернуті одночасно. Будь ласка, дивіться фотографію 3, встановіть значення у діалоговому вікні пошуку кількох умов. Натисніть кнопку OK, щоб отримати результати. Перегляньте фотографію 4, результати на аркуші 2 повертаються на основі даних на аркуші 1.
Сподіваюся, мої два рішення допоможуть вам. Гарного дня.
З повагою, Менді 
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця