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% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно

 

Сортировать комментарии по
Комментарии (25)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
Я попробовал эту же формулу; скопировано 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
Есть несколько подробных пояснений к этой задаче. Вам просто нужно изменить критерий на свой собственный.
Спасибо!
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте,

Во-первых, спасибо, что поделились!

Не могли бы вы предоставить решение для случая ниже:

У меня есть 3 столбца (A: Содержит справочную информацию, B: Содержит информацию для поиска, C: Результат поиска)

URL изображения указан ниже

https://ibb.co/VHCd09K

Столбец A------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Столбец 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

Когда я добавляю '=ИНДЕКС($B$2:$B$11, МАЛЕНЬКИЙ(ЕСЛИ(СЧЁТЕСЛИ($F$2, $C$2:$C$11)*СЧЁТЕСЛИ($G$2, $D$2:$D$11), ROW ($A$2:$D$11)-MIN(СТРОКА($A$2:$D$11))+1), СТРОКА(A1)), СТОЛБЦ(A1))' на моем листе Excel не позволяет запятая между разными частями формулы.
Поэтому мне нужно изменить их на ';'.
Но когда я пытаюсь это сделать, он всегда говорит: «#ИМЯ?»

Так может ли кто-нибудь помочь мне с этим?

С уважением,

Bas
[img]https://drive.google.com/file/d/1iIPQKuj_PNhqWyWlwJ4IQTqGNEd6B9Hw/view?usp=share_link[/img]
Этот комментарий был сведен к минимуму модератором на сайте
Привет, если есть повторяющиеся значения (например, два Адама), как мне убедиться, что он возвращает только 1 Адам, а не 2?
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Бобби,
Чтобы извлечь только уникальные совпадающие значения, вы должны применить следующую формулу:
После вставки формулы нажмите Shift + Ctrl + Enter ключи вместе, чтобы получить правильный результат.
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$5, ПОИСКПОЗ(0, СЧЁТЕСЛИ(H1:$H$1, $B$2:$B$5)+ЕСЛИ($D$2:$D$5<>$G$2, 1) , 0)+ЕСЛИ($C$2:$C$5<>$F$2, 1, 0), 0)), "")

Пожалуйста, попробуйте, надеюсь, это поможет вам!
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места

Подписывайтесь на Нас

Copyright © 2009 - www.extendoffice.ком. | Все права защищены. Питаться от ExtendOffice, | Карта сайта
Microsoft и логотип Office являются товарными знаками или зарегистрированными товарными знаками Microsoft Corporation в США и / или других странах.
Защищено Sectigo SSL