Возврат нескольких совпадающих значений по нескольким критериям в Excel (полное руководство)
Пользователи Excel часто сталкиваются с задачей извлечения нескольких значений, соответствующих сразу нескольким критериям, и отображения всех совпадающих результатов — в столбце, строке или объединённых в одной ячейке. В этом руководстве описаны методы для всех версий Excel, а также новая функция ФИЛЬТР, доступная в Excel 365 и Excel 2021.
Возврат нескольких совпадающих значений по нескольким критериям в одной ячейке
В Excel извлечение нескольких совпадающих значений по нескольким критериям в одну ячейку — распространённая задача. Ниже представлены два эффективных метода.
Метод 1: использование функции TEXTJOIN (Excel 365 / 2021,2019)
Чтобы собрать все совпадающие значения в одной ячейке через разделители, воспользуйтесь функцией TEXTJOIN.
Введите или скопируйте следующую формулу в пустую ячейку, затем нажмите клавишу Enter (в Excel 2021 и Excel 365) или комбинацию Ctrl + Shift + Enter в Excel 2019, чтобы получить результат:
=TEXTJOIN(", ", TRUE, IF(($A$2:$A$18=E2)*($B$2:$B$18=F2), $C$2:$C$18, ""))
- ($A$2:$A$21=E2)*($B$2:$B$21=F2) проверяет, удовлетворяет ли каждая строка сразу двум условиям: «Продавец равен значению в ячейке E2» и «Месяц равен значению в ячейке F2». Если оба условия выполняются, результат — 1; в противном случае — 0. Знак * означает, что оба условия должны быть истинными одновременно.
- IF(…, $C$2:$C$21, «») возвращает название товара, если строка соответствует заданным условиям; в противном случае — пустую ячейку.
- TEXTJOIN(", ", ИСТИНА, …) объединяет все непустые названия товаров в одну ячейку, разделяя их запятой и пробелом «, ».
Метод 2: использование Kutools для Excel
Kutools для Excel предлагает мощное и простое решение для быстрого извлечения и объединения нескольких совпадений в одной ячейке на основе нескольких критериев — без сложных формул.
После установки Kutools для Excel выполните следующие действия:
- Выберите диапазон данных, для которого необходимо получить все соответствующие значения на основе заданных критериев.
- Затем выберите «Kutools» > «Объединить и разделить» > «Расширенное объединение строк» (см. снимок экрана):
- В диалоговом окне Расширенное объединение строк настройте следующие параметры:
- Выберите заголовки столбцов, содержащие критерии сопоставления (например, «Продавец» и «Месяц»). Для каждого выбранного столбца нажмите «Первичный ключ», чтобы задать их в качестве условий поиска.
- Щелкните заголовок столбца, содержащего объединённые результаты (например, «Товар»). В разделе «Объединение» выберите подходящий разделитель — например, запятую, пробел или собственный разделитель.
- Наконец, нажмите кнопку «ОК».
Результат: Kutools мгновенно объединит все совпадающие значения в одну ячейку для каждой уникальной комбинации критериев.
Возврат нескольких совпадающих значений по нескольким критериям в столбце
Когда нужно извлечь и отобразить несколько совпадающих записей из набора данных по нескольким условиям, представив результаты в виде вертикального столбца, Excel предлагает несколько мощных решений.
Метод 1: использование формулы массива (для всех версий)
Вы можете использовать следующую формулу массива для вертикального возврата результатов в столбец:
1. Скопируйте или введите следующую формулу в любую пустую ячейку:
=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), ROW(1:1))), "")2. Нажмите Ctrl + Shift + Enter, чтобы получить первое совпадающее значение, затем выделите ячейку с этой формулой и протяните маркер заполнения вниз до появления пустой ячейки — так вы получите все совпадающие значения, как показано на снимке экрана ниже:
- $A$2:$A$18=$E$2: Проверяет, соответствует ли продавец значению в ячейке E2.
- $B$2:$B$18=$F$2: проверяет, соответствует ли месяц значению в ячейке F2.
- * — это логический оператор И (оба условия должны быть истинными).
- ROW($C$2:$C$18)-ROW($C$2)+1: генерирует относительный номер строки для каждого товара.
- SMALL(…, ROW(1:1)): извлекает n-ю наименьшую строку, соответствующую условию (при протягивании формулы вниз).
- INDEX(…): Возвращает элемент из соответствующей строки.
- IFERROR(…, «»): Возвращает пустую ячейку, когда совпадения заканчиваются.
Метод 2: использование функции ФИЛЬТР (Excel 365 / 2021)
Если вы используете Excel 365 или Excel 2021, функция ФИЛЬТР — идеальное решение для получения нескольких результатов по нескольким критериям: она проста в использовании, интуитивно понятна и динамически выводит результаты без необходимости создавать сложные формулы массива.
Скопируйте или введите приведённую ниже формулу в пустую ячейку и нажмите клавишу Enter — все соответствующие записи будут автоматически подобраны по нескольким критериям.
=FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match")
- ФИЛЬТР(…) возвращает все значения из диапазона C2:C18, где оба условия выполняются.
- (A2:A18=E2)*(B2:B18=F2): логический массив, проверяющий совпадение продавца и месяца.
- «Нет совпадений»: необязательное сообщение, отображаемое, если значения не найдены.
Возврат нескольких совпадающих значений по нескольким критериям в строке
Пользователям Excel нередко нужно извлекать несколько значений из набора данных, соответствующих нескольким условиям, и отображать их горизонтально — в одной строке. Это особенно полезно при создании динамических отчётов, панелей мониторинга или сводных таблиц, где вертикальное пространство ограничено. В этом разделе мы рассмотрим два мощных метода.
Метод 1: использование формульного массива (для всех версий)
Традиционные формульные массивы позволяют извлекать несколько совпадающих значений с помощью функций ИНДЕКС, НАИМЕНЬШИЙ, ЕСЛИ и СТОЛБЕЦ. Вместо вертикального извлечения (по столбцам) мы адаптируем формулу так, чтобы результаты возвращались в строке.
1. Скопируйте или введите приведённую ниже формулу в любую пустую ячейку:
=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), COLUMN(A1))), "")2. Нажмите сочетание клавиш Ctrl + Shift + Enter, чтобы получить первое совпадающее значение, затем выделите ячейку с этой формулой и перетащите её вправо по столбцам, чтобы получить все результаты.
- $A$2:$A$18=$E$2: проверяет, соответствует ли продавец указанному значению.
- $B$2:$B$18=$F$2: Проверяет, соответствует ли месяц указанному значению.
- *: Логическое И — оба условия должны выполняться.
- ROW($C$2:$C$18)-ROW($C$2)+1: Создаёт относительные номера строк, определяя их количество.
- COLUMN(A1): определяет, какое совпадение вернуть, в зависимости от того, на сколько ячеек вправо протянута формула.
- IFERROR(…): предотвращает появление ошибок после того, как совпадения закончатся.
Метод 2: использование функции ФИЛЬТР (Excel 365 / 2021)
Скопируйте или введите приведённую ниже формулу в пустую ячейку и нажмите клавишу Enter — все совпадающие значения будут извлечены и размещены в строке. См. скриншот:
=TRANSPOSE(FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match"))
- ФИЛЬТР(…): возвращает совпадающие значения из столбца C, соответствующие двум условиям.
- (A2:A18=E2)*(B2:B18=F2): Оба условия должны выполняться одновременно.
- ТРАНСП(…): Преобразует вертикальный массив, возвращаемый функцией ФИЛЬТР, в горизонтальный.
🔚 Заключение
Извлечь несколько совпадающих значений по нескольким критериям в Excel можно разными способами — в зависимости от того, как вы хотите представить результаты: в столбце, строке или одной ячейке.
- Пользователям Excel 365 и Excel 2021 функция ФИЛЬТР предлагает современное, динамичное и элегантное решение, которое минимизирует сложность.
- Для пользователей более старых версий формулы массива по-прежнему остаются мощным инструментом — пусть и требующим чуть больше настройки и внимания.
- Кроме того, если вы хотите объединить результаты в одной ячейке или предпочитаете решение без программирования, функция TEXTJOIN или сторонние инструменты, такие как Kutools для Excel, значительно упростят этот процесс.
Выберите метод, который лучше всего подходит для вашей версии Excel и предпочитаемого формата отображения — и вы сможете эффективно и точно выполнять поиск по нескольким критериям. Если вас интересуют дополнительные советы и приёмы работы в Excel, на нашем сайте представлены тысячи обучающих материалов, которые помогут вам освоить Excel.
Другие связанные статьи:
- Возврат нескольких Диапазон значений поиска в одной ячейке с разделителями-запятыми
- В Excel функция ВПР позволяет получить первое совпадающее значение из таблицы, но иногда нужно извлечь все совпадающие значения и объединить их в одной ячейке с помощью определённого разделителя — например, запятой или тире, как показано на следующем снимке экрана. Как получить и вернуть несколько значений из диапазона поиска в одной ячейке, разделив их запятыми, в Excel?
- ВПР и одновременный возврат нескольких совпадающих значений на листе Google
- Стандартная функция ВПР в Google Таблицах находит и возвращает только первое совпадающее значение на основе заданных данных. Однако иногда необходимо выполнить поиск с помощью ВПР и получить все совпадающие значения, как показано на следующем снимке экрана. Знаете ли вы простые и удобные способы решения этой задачи в Google Таблицах?
- ВПР и возврат нескольких значений из раскрывающегося списка
- Как в Excel выполнить поиск с помощью ВПР и вернуть сразу несколько соответствующих значений через раскрывающийся список — чтобы при выборе одного элемента автоматически отображались все связанные с ним данные, как показано на следующем снимке экрана? В этой статье я подробно опишу пошаговое решение.
- ВПР и вертикальный возврат нескольких значений в Excel
- Обычно вы можете использовать функцию ВПР для получения первого соответствующего значения, но иногда требуется возвращать все совпадающие записи на основе определенного критерия. В этой статье я расскажу, как выполнять поиск по ВПР и возвращать все совпадающие значения вертикально, горизонтально или в одной ячейке.
- ВПР и возврат соответствующих данных между двумя значениями в Excel
- В Excel можно использовать стандартную функцию ВПР для получения соответствующего значения на основе заданных данных. Но иногда требуется выполнять поиск по ВПР и возвращать совпадающее значение между двумя значениями, как показано на следующем снимке экрана. Как решить эту задачу в Excel?
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек