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

Как вернуть несколько совпадающих значений на основе одного или нескольких критериев в Excel?

Обычно для большинства из нас легко найти конкретное значение и вернуть соответствующий элемент с помощью функции ВПР. Но пытались ли вы когда-нибудь вернуть несколько совпадающих значений на основе одного или нескольких критериев, как показано на следующем снимке экрана? В этой статье я представлю несколько формул для решения этой сложной задачи в 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, Затем нажмите Shift + Ctrl + 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 мы можем применить функцию ВПР для возврата первого совпадающего значения из ячеек таблицы, но иногда нам нужно извлечь все совпадающие значения, а затем разделить их определенным разделителем, таким как запятая, тире и т. Д., В один ячейку, как показано на следующем снимке экрана. Как мы могли получить и вернуть несколько значений поиска в одной ячейке, разделенной запятыми в Excel?
  • Vlookup и возврат нескольких совпадающих значений одновременно в листе Google
  • Обычная функция Vlookup в листе Google может помочь вам найти и вернуть первое совпадающее значение на основе заданных данных. Но иногда вам может потребоваться vlookup и вернуть все совпадающие значения, как показано на следующем снимке экрана. Есть ли у вас какие-нибудь хорошие и простые способы решить эту задачу в листе Google?
  • Vlookup и возврат нескольких значений из раскрывающегося списка
  • В Excel, как вы могли vlookup и возвращать несколько соответствующих значений из раскрывающегося списка, что означает, что когда вы выбираете один элемент из раскрывающегося списка, все его относительные значения отображаются одновременно, как показано на следующем снимке экрана. В этой статье я расскажу о решении шаг за шагом.
  • Vlookup и возврат нескольких значений по вертикали в Excel
  • Обычно вы можете использовать функцию Vlookup для получения первого соответствующего значения, но иногда вы хотите вернуть все совпадающие записи на основе определенного критерия. В этой статье я расскажу о том, как использовать vlookup и возвращать все совпадающие значения по вертикали, горизонтали или в одну ячейку.
  • Vlookup и возврат данных соответствия между двумя значениями в Excel
  • В Excel мы можем применить обычную функцию Vlookup, чтобы получить соответствующее значение на основе заданных данных. Но иногда мы хотим выполнить vlookup и вернуть значение соответствия между двумя значениями, как показано на следующем снимке экрана, как вы могли бы справиться с этой задачей в Excel?

 



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

 

Сортировать комментарии по
Комментарии (20)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
Я попробовал эту же формулу; скопировано 100%. Единственное, что я изменил, это сопоставление и возврат данных. Когда я использую эту формулу, Excel говорит: «Вы ввели слишком много аргументов для этой функции». = ИНДЕКС («Отчет об объеме за 2020 г.»! Отчет'!$A$3:$A$100)*COUNTIF($A$1,'Отчет об объемах за 2020 год'!$D$3:$D$100),СТРОКА('Отчет об объемах за 3 год'!$A$2020:$G$3)- МИН(СТРОКА('Отчет об объеме за 100 год'!$A$2020:$G$3))+100,"2020"),СТРОКА(A3),СТОЛБЦ(A100))
Этот комментарий был сведен к минимуму модератором на сайте
Привет, не могли бы вы привести свои данные и ошибку формулы в виде скриншота здесь?
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте, как я могу использовать его для горизонтального состояния.
Этот комментарий был сведен к минимуму модератором на сайте
Что такое «0» после +1 в формуле? Этого нет в примере.
Этот комментарий был сведен к минимуму модератором на сайте
Привет, я пробовал ту же формулу. я получаю результат, но когда я даю CSE, он не дает никаких множественных ответов
Этот комментарий был сведен к минимуму модератором на сайте

Этот комментарий был сведен к минимуму модератором на сайте
Что касается возврата нескольких совпадающих значений на основе одного или нескольких критериев с формулами массива: почему, если у меня есть данные где-либо еще, кроме начала в A1, они не работают, даже если я обновляю все ссылки на ячейки в формуле?
Этот комментарий был сведен к минимуму модератором на сайте
В первом примере какое изменение формулы потребуется, чтобы вернуть всех, кому меньше 28 лет?
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте,

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

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

Спасибо за помощь заранее.
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Ник,

Рад помочь. Если вы хотите получить имена людей как из Америки, так и из Франции, я советую вам использовать нашу формулу дважды, чтобы получить результат. Пожалуйста, посмотрите на скриншот. В F2 и G2 указаны значения «США» и «Франция». Примените формулу =ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$11, МАЛЕНЬКИЙ(ЕСЛИ($F$2=$D$2:$D$11, СТРОКА($D$2:$D$11)-СТРОКА($D$2)+1) ), ROW(1:1))),"" ), чтобы получить результаты для Америки. И применить формулу =ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$11, МАЛЕНЬКИЙ(ЕСЛИ($G$2=$D$2:$D$11, СТРОКА($D$2:$D$11)-СТРОКА($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; ДД1009; 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))

Не забудьте нажать Shift + Ctrl + Enter ключи вместе.
Пожалуйста, попробуйте, надеюсь, это поможет вам!
Этот комментарий был сведен к минимуму модератором на сайте
Привет, спасибо за формулу. Это работало для «фиксированных» значений/текста в качестве критериев. Однако одним из критериев, который я пытаюсь использовать, является условие (значения <>0), но описанная формула не работает. Ребята, вы знаете, что мне нужно изменить, чтобы адаптировать формулу, чтобы я мог иметь условие в качестве одного из критериев, пожалуйста?

Лучший,

Джон
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Маркус
Чтобы решить вашу проблему, просмотрите эту статью:
https://www.extendoffice.com/documents/excel/6393-excel-vlookup-function.html#b3-2
Есть несколько подробных пояснений к этой задаче. Вам просто нужно изменить критерий на свой собственный.
Спасибо!
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места