Поиск нескольких значений в Excel
Обновлено: 24.11.2024
В этом руководстве мы будем использовать формулы Excel для поиска нескольких значений и объединения результатов в одну ячейку или строку. Мы также упорядочим возвращаемые значения двумя разными способами.
Формулы, используемые в этом руководстве, доступны только в Excel 365 и Excel Online. Они используют преимущества механизма массивов современного Excel. В этом видео показано, как искать несколько значений во всех версиях Excel.
Посмотрите видео ниже или читайте дальше. В этом руководстве рассматриваются три примера формулы поиска для возврата нескольких значений.
Много информации, так что давайте перейдем к делу.
Загрузите книгу, чтобы следовать руководству.
Поиск и возврат нескольких значений в виде списка
В этом примере у нас есть таблица с названием «продукты», которая содержит название продукта и категорию, к которой он относится. В ячейке G2 есть раскрывающийся список, который можно использовать для выбора одной из трех категорий продуктов — напитков, продуктов питания или тортов и выпечки.
Мы хотим вернуть все продукты из указанной категории в диапазон в разделе «Товары», начиная с ячейки G5.
В ячейке G5 используется следующая формула.
Функция ФИЛЬТР в Excel представляет собой формулу поиска, которая возвращает несколько значений, поэтому эта задача создана специально для нее.
Первый аргумент — это возвращаемые значения. В данном примере это столбец «Продукт». Второй аргумент — это критерий, который означает, что категория равна категории, выбранной в ячейке G2.
Эта формула легко возвращает несколько значений. Формула вводится только в ячейку G2, а результаты распределяются по соседним ячейкам.
Объединить значения в одну ячейку
Давайте сделаем еще один шаг и объединим результаты в одну ячейку. Мы также упорядочим названия продуктов в алфавитном порядке.
Продолжая работу с таблицей «продукты», теперь у нас есть следующий диапазон, и мы хотели бы вернуть результаты всех продуктов для каждой из категорий. Эти результаты будут объединены в одну ячейку и разделены запятой.
Используется следующая формула.
В этой формуле используется та же формула FILTER, что и раньше.
Функция SORT используется для упорядочения возвращаемых значений. По умолчанию функция SORT упорядочивает значения в порядке возрастания, поэтому дополнительные аргументы не нужны.
Затем добавляется функция TEXTJOIN для объединения результатов в одну ячейку. Это потрясающая функция, которая объединяет массив или диапазон и разделяет их указанным разделителем. В этом примере использовались запятая и пробел. Второй аргумент опущен. При этом будут игнорироваться пустые ячейки, что не является фактором в данном сценарии.
Эта формула не переливается, поэтому ее нужно заполнить до ячеек I4 и I5.
Поиск нескольких значений и сортировка по другому столбцу
В этом примере у нас есть таблица с именем tblOptions. В таблице у нас есть несколько человек, и они выбирают место, которое они хотят посетить. Они могут выбрать первый, второй и третий вариант в порядке предпочтения.
В столбце F у нас есть отсортированный и отдельный список имен, взятых из таблицы по следующей формуле.
Мы хотим найти три выбранных местоположения для каждого человека и вернуть их в диапазон, начинающийся в ячейке G2. Возвращаемые значения должны быть в порядке 1, 2, 3.
Начнем с функции ФИЛЬТР и воспользуемся ею так же, как и в предыдущих примерах. Эта формула возвращает три местонахождения имени, то есть Адама.
Итак, функция FILTER возвращает местоположения, но нам нужно транспонировать результаты, чтобы они шли по строке, а не вниз по столбцу.
Нам также необходимо отсортировать результаты по столбцу «Вариант». Для Адама Харлоу был его первым выбором, Лондон был вторым, а Кембридж - третьим.
Следующая формула позволяет достичь этих целей.
Функция SORTBY добавлена в FILTER для сортировки результатов по столбцу вне столбца, используемого FILTER. Это связано с тем, что FILTER использует столбец "Местоположение", а нам нужно выполнить сортировку по столбцу "Параметры".
Второй ФИЛЬТР используется внутри массива для сортировки по аргументу SORTBY. Они показаны в разных строках формулы, чтобы их было легче различать. Этот второй ФИЛЬТР предоставляет три варианта для указанного лица.
Затем добавляется функция ТРАНСП, возвращающая результаты по строке.
Итак, в этом руководстве показаны три примера использования формулы для поиска нескольких значений в Excel. Затем мы рассмотрели, как объединить результаты в одну ячейку или по строке, а также упорядочить результаты.
Самый простой способ найти несколько значений в Excel — использовать функцию поиска.
Сначала выберите ячейки, в которых вы хотите выполнить поиск.
Затем перейдите в раздел Главная >> Редактирование >> Найти и выбрать >> Найти. Вы также можете использовать сочетание клавиш Ctrl + F для быстрого доступа.
Нажмите кнопку «Найти все», чтобы выполнить поиск во всей выбранной области. Он покажет список ячеек, соответствующих критерию.
Не делая ничего другого, нажмите сочетание клавиш Ctrl + A, чтобы выбрать их все.
Нажмите "Закрыть".
Теперь вместо всех выбранных ранее значений выделены только три ячейки — те, что содержат словосочетание «Джон».
Если вы нажмете Параметры >>, прежде чем нажимать Найти все, вы можете найти дополнительные настройки, например, вы можете заставить Excel искать текст, соответствующий регистру.
Фильтр для Excel 365
Функция ВПР очень полезна, если вы хотите найти значение на основе значения поиска. Это работает только для уникальных значений. Если дубликаты, функция вернет только первый из них.
Поэтому, если таблица содержит несколько значений поиска, эта функция не будет работать.
Если вам нужна функция ВПР с несколькими значениями, вы можете использовать функцию ФИЛЬТР. Он очень прост в использовании.
Эта функция доступна только для подписчиков Excel 365, поэтому, если вы используете другую версию Excel, она вам не подойдет.
Давайте посмотрим, как это выглядит на этом примере:
Теперь мы собираемся отобразить все заказы Джона Доу.
Для начала воспользуемся функцией ВПР. Вставьте эту формулу в ячейку B10.
Формула возвращает первый заказ Джона Доу в списке.
Но два других заказа должны быть включены.
Теперь воспользуемся функцией ФИЛЬТР.
Вставьте новую формулу в ячейку B10:
Вот результат:
Его очень легко и интересно использовать, но он недоступен в предыдущих версиях Excel. Давайте узнаем, как мы можем создать формулу, которая работает аналогично для тех, кто не использует Excel 365.
Функция ИНДЕКС
Мы собираемся использовать функцию ИНДЕКС, чтобы получить результат, аналогичный результату функции ФИЛЬТР. Введите эту формулу в ячейку B2:
Эта формула возвращает первое вхождение заказа Джона Доу: A281.
Если вы автоматически заполните оставшиеся ячейки, вы получите оставшиеся заказы.
Давайте разобьем эту формулу на более мелкие части:
Если ячейка в диапазоне A1:A6 равна A10 (Джон Доу), то возвращается номер строки, в противном случае возвращается FALSE.
2, 4 и 5 — это строки, в которых присутствует имя «Джон Доу».
В следующей части формулы используется функция НАИМЕНЬШИЙ, которая возвращает n-е наименьшее значение.
СТРОКА(1:1) возвращает первую строку. Если вы используете автозаполнение, оно вернет номер строки 1, 2, 3 и т. д.
Формула вернет числа: 2, 4 и 5.
Функция ИНДЕКС возвращает значение в заданной позиции.
Массив представляет собой диапазон A1:A6. Номера строк — 2, 4, 5. Номер столбца — 2.
Если вы введете эти формулы, вы получите тот же результат:
Избавление от ошибок
Чтобы исправить это, вы должны использовать эту формулу, которая решает эту проблему:
Вы можете узнать больше о функции index и о том, как вы можете использовать ее для возврата нескольких результатов, прочитав статью на эту тему.
Несколько значений в одной ячейке
Если вы предпочитаете, чтобы все заказы были разделены запятой в одной ячейке, вы можете сделать это, создав формулу, содержащую функции FILTER и TEXTJOIN.
Чтобы проверить, содержит ли ячейка одну из множества строк, можно использовать формулу, основанную на функциях ПОИСК, ЕСЛИЧИСЛО и СУММПРОИЗВ. Формула в C5, скопированная вниз, выглядит так:
где вещами является именованный диапазон E5:E7.
Цель этого примера — проверить каждую ячейку в B5:B14, чтобы увидеть, содержит ли она какие-либо строки из именованного диапазона вещей (E5:E7). Эти строки могут появиться в любом месте в ячейке, так что это буквальная проблема «содержит». Формула в C5, скопированная вниз, выглядит так:
Эта формула основана на другой формуле, которая проверяет ячейку на наличие одной подстроки. Если ячейка содержит подстроку, формула возвращает ИСТИНА. Если нет, формула возвращает ЛОЖЬ:
В этом примере цель состоит в том, чтобы проверить более одной строки, поэтому мы даем функции ПОИСК список строк в именованном диапазоне вещей. Поскольку в вещах есть 3 строки ("красный", "зеленый" и "синий"), ПОИСК возвращает 3 результата в виде такого массива:
Этот массив возвращается непосредственно в функцию ISNUMBER, которая преобразует элементы массива либо в TRUE, либо в FALSE:
По логике вещей, если в массиве есть хотя бы одно значение TRUE, мы знаем, что ячейка содержит хотя бы одну из искомых строк. Самый простой способ проверить TRUE — сложить все значения вместе. Мы можем сделать это с помощью функции СУММПРОИЗВ, но сначала нам нужно привести значения ИСТИНА / ЛОЖЬ к 1 и 0 с двойным отрицанием (--) следующим образом:
Это дает новый массив, содержащий только 1 и 0:
который доставляется непосредственно в SUMPRODUCT:
Обрабатывая только один массив, СУММПРОИЗВ суммирует элементы массива и возвращает результат. Любой ненулевой результат означает, что у нас есть "попадание", поэтому мы добавляем >0, чтобы получить окончательный результат ИСТИНА или ЛОЖЬ:
Обратите внимание, что любая комбинация совпадений вернет число больше нуля, и формула вернет значение TRUE.
С жестко заданным списком
Нет необходимости использовать диапазон для списка искомых строк. Вы также можете использовать константу массива. Например, чтобы проверить "красный", "синий" или "зеленый", можно использовать следующую формулу:
Функция СУММ
Исторически СУММПРОИЗВ часто появляется в формулах массивов, потому что он может обрабатывать массивы изначально, без управления + Shift + Enter. Это делает формулу «более удобной» для большинства пользователей. В Excel 365, который изначально обрабатывает массивы, функцию СУММ можно использовать вместо СУММПРОИЗВ без управления + сдвиг + ввод:
Предотвращение ложных совпадений
Одна из проблем с этим подходом заключается в том, что вы можете получить ложные совпадения из подстрок, которые появляются внутри более длинных слов. Например, если вы попытаетесь сопоставить «dr», вы также можете найти «Andrea», «drink», «dry» и т. д., поскольку внутри этих слов появляется «dr». Это происходит потому, что ПОИСК автоматически выполняет поиск "содержит".
Для быстрого решения вы можете добавить пробелы вокруг поисковых слов (например, "dr" или "dr"), чтобы избежать попадания "dr" в другое слово. Но это не удастся, если "dr" стоит первым или последним в ячейке или стоит со знаками препинания.
Если вам нужно более точное решение, один из вариантов — сначала нормализовать текст во вспомогательном столбце, позаботившись также о добавлении начального и конечного пробелов. Затем вы применяете формулу на этой странице к полученному тексту.
Одной из наиболее распространенных задач в Excel является фильтрация данных. Вероятно, в каждом аспекте вашего использования Excel вы используете фильтр, фильтрация может выполняться как с отдельными элементами, так и с несколькими элементами.Сегодня мы собираемся показать вам, как искать несколько элементов в фильтре.
Для начала давайте познакомимся с сегодняшним примером набора данных.
У нас есть простая таблица с несколькими случайными людьми из разных стран, их любимыми видами спорта и любимыми игроками. Используя этот набор данных, мы будем фильтровать по разным элементам.
Обратите внимание, что это базовый набор данных, в реальной жизни вы можете столкнуться со многими сложными и большими наборами данных.
Практическая рабочая тетрадь
Вы можете загрузить учебную тетрадь по ссылке ниже.
Поиск нескольких элементов в фильтре Excel
1. Использование основного фильтра
Опция базового фильтра может быть полезна для поиска нескольких элементов. Давайте изучим этот инструмент.
Я. Непосредственное использование фильтра
Мы можем использовать базовый параметр фильтра напрямую для поиска нескольких элементов. Вы найдете этот параметр «Фильтр» в разделе «Сортировка и фильтр» на вкладке «Данные».
Сначала выберите диапазон данных, для которого вы хотите использовать фильтр, а затем нажмите "Фильтр".
Вы найдете значок фильтра в нижнем углу заголовков столбцов.
Теперь нам нужно нажать на любой из значков фильтра, для которого мы хотим отфильтровать наши данные. Например, мы собираемся со столбцом Страна.
Будут видны все названия стран. Поскольку наша задача состоит в том, чтобы использовать несколько элементов для фильтрации, мы выберем из них несколько стран.
Прежде всего выберите страну. Здесь мы выбрали Австралия.
Выбран один элемент, теперь нам нужно выбрать еще несколько элементов (поскольку мы собираемся искать несколько элементов).
Здесь мы выбрали Канаду и США. Вы можете предпочесть свой. Теперь нажмите ОК.
Нам были представлены данные только по этим трем странам.
Мы отфильтровали наш набор данных по нескольким элементам (странам). Мы можем выполнять поиск не только в одном столбце, но и в нескольких столбцах.
В нашем примере теперь мы будем фильтровать столбцы Любимые виды спорта. Вам нужно щелкнуть значок фильтра в этом столбце.
Теперь выберите любой из вариантов, здесь мы выбираем Футбол и Теннис.
Здесь мы найдем отфильтрованные данные.
II. Использование вспомогательного столбца для фильтрации
В предыдущем разделе мы напрямую использовали параметр «Фильтр». Теперь мы увидим, как фильтровать с помощью вспомогательного столбца.
Здесь мы должны сначала перечислить элементы, которые мы хотим найти в нашем наборе данных.
Мы перечислили три страны отдельно от нашего набора данных. И добавил вспомогательный столбец.
Мы заполним этот Вспомогательный (вспомогательный) столбец, используя формулу, сформированную функцией СЧЁТЕСЛИ. COUNTIF подсчитывает ячейки с критериями. Чтобы узнать больше об этой функции, посетите эту статью СЧЁТЕСЛИ.
Формула будет
H4:H6 – это ссылка на диапазон округов, в которых выполняется поиск, а C4 – первая ячейка из столбца Страна.
Мы нашли номер экземпляра страны (США) в списке стран поиска.
Используйте автозаполнение Excel, чтобы заполнить вспомогательный (вспомогательный) столбец. Там, где страны совпадали, мы нашли 1, иначе 0.
Теперь используйте параметр "Фильтр" в столбце Helper (Helping) и выберите 1 из него.
Здесь мы нашли данные из нужных нам стран.
Похожие чтения:
- Как применить несколько фильтров в Excel [методы + VBA]
- Фильтрация нескольких критериев в Excel (4 подходящих способа)
- Как независимо отфильтровать несколько столбцов в Excel
2. Использование расширенного фильтра для поиска нескольких элементов
Мы можем использовать расширенный фильтр для поиска нескольких элементов. Давайте изучим этот метод.
Я. Несколько значений для одного столбца
Мы можем искать несколько значений в одном столбце. Здесь мы перечислили несколько видов спорта.
Убедитесь, что столбец поиска имеет то же имя, что и исходный столбец. Теперь нажмите «Расширенный фильтр» в разделе «Сортировка и фильтр» на вкладке «Данные».
Перед вами откроется диалоговое окно
Расширенный фильтр.
Затем вам нужно выбрать Диапазон списка и Диапазон критериев.
Здесь мы выбрали наш набор данных в диапазоне списка и столбец поиска в диапазоне критериев. Теперь нажмите ОК.
Мы искали следующие виды спорта: Футбол и Крикет. И в нашем наборе данных мы нашли только эти виды спорта.
II. Несколько значений для нескольких столбцов
Мы можем использовать несколько столбцов при поиске значений. В нашем примере мы будем фильтровать по параметрам Страна и Любимые виды спорта.
Здесь мы взяли США и Индию для поиска в столбце Страна и футбол и крикет для столбца Любимые виды спорта.
Теперь задействуйте расширенный фильтр и установите диапазоны для соответствующих полей.
Здесь мы выбрали наш набор данных в диапазоне списка и столбцы поиска в диапазоне критериев. Теперь нажмите ОК.
Мы нашли набор данных о значениях из наших элементов поиска.
Обратите внимание, что если мы использовали несколько столбцов, процесс фильтрации будет рассматривать их как отдельные строки.
Заключение
Это все для сеанса. Мы перечислили несколько способов поиска нескольких элементов в фильтре Excel. Надеюсь, вы найдете это полезным. Не стесняйтесь комментировать, если что-то кажется трудным для понимания. Сообщите нам, какой из методов вы собираетесь использовать. Сообщите нам о подходах, которые мы могли здесь пропустить.
Читайте также: