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

Як повернути кілька відповідних значень на основі одного чи кількох критеріїв у Excel?

Зазвичай пошук певного значення та повернення відповідного елемента є простим для більшості з нас за допомогою функції VLOOKUP. Але, ви коли-небудь намагалися повернути кілька відповідних значень на основі одного або декількох критеріїв, як показано на наступному скріншоті? У цій статті я представив кілька формул для вирішення цього складного завдання в Excel.

Повернути декілька значень відповідності на основі одного або декількох критеріїв за допомогою формул масиву


Повернути декілька значень відповідності на основі одного або декількох критеріїв за допомогою формул масиву

Наприклад, я хочу вилучити всі імена, яким 28 років і походять із США, застосуйте таку формулу:

1. Скопіюйте або введіть формулу нижче в порожню комірку, де ви хочете знайти результат:

=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

примітки: У наведеній вище формулі, B2: B11 - стовпець, з якого повертається відповідне значення; F2, C2: C11 є першою умовою та даними стовпця, що містить першу умову; G2, D2: D11 є другою умовою та даними стовпця, що містить цю умову, будь ласка, змініть їх відповідно до ваших потреб.

2. Потім натисніть Ctrl + Shift + Enter ключі, щоб отримати перший результат відповідності, а потім виберіть першу комірку формули і перетягніть маркер заповнення вниз до комірок, поки не відобразиться значення помилки, тепер усі відповідні значення повертаються, як показано на знімку екрана:

чайові: Якщо вам просто потрібно повернути всі відповідні значення на основі однієї умови, застосуйте наведену нижче формулу масиву:

=IFERROR(INDEX($B$2:$B$11, SMALL(IF($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1), ROW(1:1))),"" )


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

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

 


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

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

  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці та Ведення даних; Вміст розділених комірок; Поєднуйте повторювані рядки та суму / середнє... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Улюблені та швидко вставлені формули, Діапазони, діаграми та зображення; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Групування зведеної таблиці за номер тижня, день тижня та багато іншого ... Показати розблоковані, заблоковані клітини за різними кольорами; Виділіть клітини, які мають формулу / назву...
вкладка kte 201905
  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці

 

Коментарі (25)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
Я спробував цю саму формулу; скопійовано на 100%. Єдине, що я змінив, це дані, які збігаються та повертаються. Коли я використовую цю формулу, Excel каже: "Ви ввели забагато аргументів для цієї функції).=INDEX('Звіт про обсяг 2020 року'!$B$3:$B$100,SMALL(IF(COUNTIF($A$1,'2020 Volume) Report'!$A$3:$A$100)*COUNTIF($A$3,'2020 Volume Report'!$D$3:$D$100),ROW('2020 Volume Report'!$A$3:$G$100)- MIN(ROW('Звіт про обсяг 2020 року'!$A$3:$G$100))+1,"0"),ROW(A1),COLUMN(A1))
Цей коментар був мінімізований модератором на сайті
Привіт. Чи не могли б ви вказати свої дані та помилку формули як знімок екрана?
Цей коментар був мінімізований модератором на сайті
Привіт, як я можу використовувати його для горизонтального стану.
Цей коментар був мінімізований модератором на сайті
Що означає «0» після +1 у формулі? У першому прикладі цього немає.
Цей коментар був мінімізований модератором на сайті
Привіт, я пробував ту саму формулу. я отримую результат, але коли дає CSE, він не надає жодних кількох відповідей
Цей коментар був мінімізований модератором на сайті

Цей коментар був мінімізований модератором на сайті
Щодо повернення кількох відповідних значень на основі одного або кількох критеріїв із формулами масиву: чому, якщо я маю дані де-небудь ще, крім A1, вони не працюють, навіть якщо я оновлю всі посилання на клітинки у формулі?
Цей коментар був мінімізований модератором на сайті
У першому прикладі, які зміни до формули знадобляться, щоб повернути всіх, кому не виповнилося 28 років?
Цей коментар був мінімізований модератором на сайті
привіт,

Мені було цікаво, чи можна взагалі ввести 2-й критерій, але з того самого діапазону, що й 1-й критерій,

Наприклад, у наведеному вище прикладі я хотів би шукати імена людей як з Америки, так і з Франції. Тож клітинка F3 матиме Францію, Скарлет та Ендрю також заповнюють список у стовпці G

Наперед дякую за допомогу.
Цей коментар був мінімізований модератором на сайті
Привіт Нік,

Рада допомогти. Якщо ви хочете отримати імена людей як з Америки, так і з Франції, я раджу вам скористатися нашою формулою двічі, щоб отримати результат. Будь ласка, подивіться скріншот, у F2 і G2 значення "Сполучені Штати" і "Франція". Застосувати формулу =IFERROR(INDEX($B$2:$B$11, SMALL($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1) ), ROW(1:1))),"" ), щоб отримати результати для Америки. І застосувати формулу =IFERROR(INDEX($B$2:$B$11, SMALL($G$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+) 1), ROW(1:1))),"" ), щоб отримати результати для Франції. Це просто. Будь ласка, спробуйте.

З повагою,
Менді
Цей коментар був мінімізований модератором на сайті
Коли я використовую другу формулу та перетягую вниз, нічого не відображається. Результат формули (fx) говорить, що він має щось повертати, але він порожній. Як це виправити?
Цей коментар був мінімізований модератором на сайті
Привіт Алісія,

Рада допомогти. Я спробував другу формулу в статті та перетягнув формулу вниз, решту результатів було повернуто. Я думаю, що у вашої проблеми може бути дві причини. По-перше, можливо, ви забули натиснути клавіші Ctrl + Shift + Enter, щоб ввести формулу. По-друге, відповідний результат лише один, тому інші результати не повертаються. Будь ласка, візьміть чек.

З повагою,
Менді
Цей коментар був мінімізований модератором на сайті
Здравствуйте,
Я спробував використати формулу, і вона або генерує значення 0, або прикріплене зображення
Цей коментар був мінімізований модератором на сайті
Привіт, Мілку
На знімку екрана показано програмне забезпечення WPS версії MAC, тому я не впевнений, чи доступна наша формула.
Я завантажив сюди файл Excel, ви можете спробувати перевірити, чи він правильно обчислює у вашому середовищі.
Дякую!
Цей коментар був мінімізований модератором на сайті
Здравствуйте,
що знадобиться для розширення першої формули в такому випадку:
Деякі ідентифікатори пусті (наприклад, клітинка A5 пуста), і я хотів би, щоб додаткова умова виводила рядки лише тоді, коли ідентифікатори не пусті. (Тож на виході повинні бути Джеймс і Абдул.
Спасибо!
Цей коментар був мінімізований модератором на сайті
Привіт, Джо,
Щоб вирішити вашу проблему, застосуйте наведену нижче формулу:
=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11)*($A$2:$A$11<>0), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Спробуйте, сподіваюся, це допоможе вам!
Цей коментар був мінімізований модератором на сайті
привіт,

якби я написав у комірці H1 «Ім’я» і хотів би пов’язати це з формулою, як би це спрацювало?
Тоді я міг би написати «ID» у комірці H1 і автоматично отримав би в результаті: AA1004; DD1009; PP1023 (для першої формули)

Заранее спасибо!
Цей коментар був мінімізований модератором на сайті
Привіт, Марі
Вибачте, я не можу зрозуміти суть вашої першої проблеми. Не могли б ви пояснити свою проблему більш чітко та детально? Або ви можете вставити сюди знімок екрана, щоб описати свою проблему.
Що стосується другого запитання, вам просто потрібно змінити посилання на клітинку таким чином:
=INDEX($A$2:$A$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Не забудьте натиснути Ctrl + Shift + Enter ключі разом.
Будь ласка, спробуйте, сподіваюся, це допоможе вам!
Цей коментар був мінімізований модератором на сайті
Гей, дякую за формулу. Це працювало для "фіксованих" значень / тексту як критеріїв. Однак одним із критеріїв, які я намагаюся використати, є умова (значення <>0 ), але описана формула не працює. Чи знаєте ви, хлопці, що мені слід змінити, щоб адаптувати формулу, щоб я міг мати умову як один із критеріїв?

кращий,

Джон
Цей коментар був мінімізований модератором на сайті
Привіт, Маркус
Щоб вирішити вашу проблему, перегляньте цю статтю:
https://www.extendoffice.com/documents/excel/6393-excel-vlookup-function.html#b3-2
Є кілька детальних пояснень цього завдання. Вам просто потрібно змінити criteira на свій власний.
Дякую!
Цей коментар був мінімізований модератором на сайті
привіт,

По-перше, дякую, що поділилися!

Чи можете ви надати рішення для наведеного нижче випадку:

У мене є 3 стовпці (A: містить довідкову інформацію, B: містить інформацію для пошуку, C: результат пошуку)

URL-адресу зображення наведено нижче

https://ibb.co/VHCd09K

Стовпець A------------------------ Стовпець B------------Стовпець C
Ім'я файлу------------------------Ім'я---------------- Ім'я файлу, Ім'я документа, Назва елемента, Ім'я
Змінений елемент-----------------Елемент--------------Змінений елемент, назва елемента, ідентифікатор елемента
Розташування колонки
Назва документа
Назва елемента
Назва
Категорія
гарантія
Схил
ID елемента

Мені потрібно знайти в стовпці A будь-який частковий збіг із клітинкою B2 (ім’я) або B3 (елемент) і отримати результат в одній клітинці,

Дякую, Бехзаде
Цей коментар був мінімізований модератором на сайті
Привіт, Бехзаде
Можливо, наведена нижче функція, визначена користувачем, допоможе вам.
Public Function ConcatPartLookUp(rngInput As Range, rngSource As Range, Optional strDelimiter As String, Optional blCaseSensitive)
Dim rng As Range
If strDelimiter = "" Then strDelimiter = ","
If IsMissing(blCaseSensitive) Then
    blCaseSensitive = False
Else
    blCaseSensitive = True
End If
For Each rng In rngSource
    If blCaseSensitive Then
        If InStr(1, rng.Value, rngInput.Value, vbBinaryCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    Else
        If InStr(1, rng.Value, rngInput.Value, vbTextCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    End If
Next
If Len(ConcatPartLookUp) > 0 Then ConcatPartLookUp = Mid(ConcatPartLookUp, 2, Len(ConcatPartLookUp))
End Function


Скопіюйте та вставте цей код, а потім скористайтеся цією формулою:=ConcatPartLookUp(B2,$A$2:$A$8) щоб отримати потрібний результат.
Будь ласка, спробуйте, сподіваюся, це допоможе вам!
Цей коментар був мінімізований модератором на сайті
привіт,

Дякуємо за розміщення цих прикладів.
Я намагаюся реалізувати це у власному аркуші, але не можу це спрацювати (можливо, тому що я використовую європейську версію excel)?

Я хочу отримати дати днів, коли я мав свої зміни або коли я працював «декілька» (>0) годин для клієнта.

Отже, в I3 є назва, а в J3 місяць. K3 і L3 – це зміни (1 відпрацьована) і години (не знаю, як це встановити, має бути більше нуля)

Мої очікувані результати:
Зміни: I7 і I8
години: J7

Отже, я працював більше 0 годин для "особи 2" у жовтні 3-10-2022
були зміни для особи 2 «10-10-2022» та 28-10-2022

Коли я додаю '=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW ($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))' у моєму аркуші Excel, він не дозволяє кома між різними частинами формули.
Тому мені потрібно змінити їх на ";".
Але коли я пробую це, він завжди каже: "#NAME?"

Тож хтось може допомогти мені з цим?

З повагою,

Bas
[img]https://drive.google.com/file/d/1iIPQKuj_PNhqWyWlwJ4IQTqGNEd6B9Hw/view?usp=share_link[/img]
Цей коментар був мінімізований модератором на сайті
Привіт, якщо є повторювані значення (наприклад, два адами), як переконатися, що він повертає лише 1 адам, а не 2?
Цей коментар був мінімізований модератором на сайті
Привіт, Боббі,
Щоб отримати лише унікальні відповідні значення, слід застосувати наведену нижче формулу:
Після вставлення формули натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати правильний результат.
=IFERROR(INDEX($B$2:$B$5, MATCH(0, COUNTIF(H1:$H$1, $B$2:$B$5))+IF($D$2:$D$5<>$G$2, 1 , 0)+ЯКЩО($C$2:$C$5<>$F$2, 1, 0), 0)), "")

Будь ласка, спробуйте, сподіваюся, це допоможе вам!
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL