Массовый поиск в Excel

Обновлено: 05.07.2024

СУММПРОИЗВ — одна из самых мощных функций работы с таблицами в Excel. Здесь, например, вы можете использовать его в одной формуле для поиска текста в одной ячейке по многим элементам.

СУММПРОИЗВ — одна из самых мощных функций работы с таблицами Excel. Здесь, например, вы можете использовать его в одной формуле для поиска текста в одной ячейке по многим элементам.

В разделе Как добавить расширенные возможности фильтрации в таблицы Excel я объяснил, как использовать длинную формулу в таблице, чтобы упростить сложную фильтрацию.

Формула основана на функции рабочего листа Excel ПОИСК, которая дает нам возможность искать одну строку в другой строке. Поиск не чувствителен к регистру и может использовать подстановочные знаки.

К сожалению, функция ПОИСК предназначена для поиска только одной строки за раз. Это ограничение было проблемой для меня, потому что мне часто приходилось включать более двух критериев при фильтрации данных в одном столбце.

Чтобы понять, что я имею в виду, взгляните на содержимое четырех ячеек из столбца aTags в моей таблице Excel:

|США|NSA|ежемесячно|bls|ставка|безработица|msa|mt|missoula|
|США|NSA|ежемесячно|bls|уровень|безработица|округ|mt|галлатин округ, mt|
|США|ежемесячно|sa|bls|ставка|безработица|штат|mt|
|США|NSA|еженедельно|занятость|штат|eta|mt|покрывается|

Что делать, если я хочу просмотреть данные по безработице в Монтане, игнорируя данные по округам, столичным статистическим районам («msa») и данные с поправкой на сезонные колебания («sa»)? Для этого мне нужно применить пять фильтров.

Как добавить расширенные возможности фильтрации в таблицы Excel объяснил, что эффективный способ сделать это — настроить один столбец фильтра в моей таблице, столбец с формулами, которые возвращают ИСТИНА при соблюдении всех условий; в противном случае они возвращают FALSE. Однако в этом посте формула требовала использования нескольких функций ПОИСК для каждой искомой ячейки.

А сейчас я представлю формулу, в которой нужно использовать только одну функцию ПОИСК для каждой искомой ячейки... независимо от того, сколько фильтров вы хотите применить к каждой ячейке.

Прерывание: зачем добавлять теги в таблицы Excel

Перечисленные выше теги описывают экономические данные Федерального резервного банка Сент-Луиса. Но даже если вы НИЧЕГО не заботитесь об экономических данных, вам может иногда понадобиться столбец «Теги» для работы с вашими данными в таблицах Excel. Вот почему:

Большая часть ваших данных, вероятно, создается ИТ-отделом или коммерческими программами. Поэтому вы, вероятно, не имеете никакого контроля над кодами и описаниями — метаданными — которые содержат ваши таблицы Excel. Но когда вы добавите столбец «Теги» в свою таблицу, вы сможете получить информацию, которая имеет смысл для ВАС.

Например, ваша таблица, вероятно, содержит столбец с кодом, который однозначно идентифицирует каждую строку, идентификатор серии, номер учетной записи GL, SKU, номер продукта и т. д.

Поэтому вы можете вести отдельную таблицу со столбцом кодов и собственным столбцом тегов. Затем, когда вы открываете новую версию данных в виде таблицы Excel, вы можете добавить столбец с формулами, которые используют ВПР или ИНДЕКС-ПОИСКПОЗ, чтобы добавить свой пользовательский столбец тегов к стандартным данным.

Чтобы пометить каждую строку данных, могут потребоваться некоторые усилия. Но вам нужно будет пометить каждую строку только один раз (если вы не измените тег, что вы можете сделать). С этого момента вы сможете использовать свои пользовательские теги, чтобы смотреть на данные таблицы со СВОЕЙ точки зрения.

Введение в формулу многокритериального поиска

Эта формула использует одну функцию ПОИСК для поиска в тексте любой ячейки любого количества элементов в списке. Он возвращает сводку своих выводов в виде одного значения. Затем проверка этого значения приводит к тому, что формула возвращает значение ИСТИНА или ЛОЖЬ, чтобы указать, соответствует ли ячейка всем критериям. Вот формула в четыре строки:

Для каждого теста, который функция ПОИСК выполняет и проходит, функция СУММПРОИЗВ добавляет сопоставимое число к его общему количеству. Таким образом, если поиск находит в тексте только «мт» и «безработица», СУММПРОИЗВ добавляет 1 плюс 8. Если это то условие, которое вам нужно, формула вернет ИСТИНА при проверке значения 9, как показано здесь

С другой стороны, если вам также нужны данные об округе, вы должны включить его значение, 4, в общую сумму. То есть вы бы проверили 13, а не 9.

Как работает формула многокритериального поиска

Ключом к этой формуле является функция СУММПРОИЗВ, которая обрабатывает свои аргументы как массивы... даже если формула не вводится массивом. Функция устанавливает в памяти временный столбец, который выполняет проверку ПОИСК для каждого элемента в списке.

Нам все равно, где находится искомый текст в списке, мы просто хотим знать, есть ли он там. Поэтому мы окружаем функцию ПОИСК функциями НЕ(ЕОШИБКА(…)) . Если элемент найден, ошибки нет. Таким образом, ISERR возвращает FALSE, а функция NOT переключает результат на TRUE. Таким образом, TRUE указывает, что искомый текст найден.

С другой стороны, если искомый текст не найден, ПОИСК возвращает значение ошибки. Таким образом, ISERR возвращает значение TRUE, которое функция НЕ переключает на FALSE. Таким образом, FALSE указывает, что искомый текст НЕ найден.

Наконец, функция СУММПРОИЗВ умножает эти результаты ИСТИНА или ЛОЖЬ на соответствующие числа в списке. Поскольку ИСТИНА равна 1, а ЛОЖЬ равна нулю, СУММПРОИЗВ суммирует числа найденных элементов. Числа выбираются таким образом, чтобы каждая сумма представляла уникальную комбинацию значений. Таким образом, мы можем проверить одно число, чтобы указать любую комбинацию успешных и неудачных поисков, которую мы хотим.

Расширение формулы многокритериального поиска

Вот снова формула:

Его можно изменять и расширять различными способами. Например…

…Если вас интересует какая-либо информация об округе Монтана, кроме безработицы, вы должны проверить значение 5. (Это означает, что поиск «mt» (1) и «округ» (4) должен быть успешным и что все другие поиски завершаются неудачей.)

…Если вас интересует информация о безработице в каком-либо городе за пределами Монтаны, вы должны проверить значение 10. (Поиск «msa» (2) и «безработица» (8) завершается успешно, а все остальные поиски завершаются неудачно. )

…Если вы решите, что вам временно все равно, существует тег «округ» или нет, вы можете заменить значение 4 в списке на ноль. Или, если вы хотите временно выбрать какое-либо состояние, вы можете заменить значение 1 в списке на ноль.

…Если вы хотите использовать в формуле строку элементов текста поиска в диапазоне F3:J3, а не строку констант массива, вы можете изменить формулу на:

…Если вы хотите использовать столбец элементов текста поиска в диапазоне D3:D7, а не ряд элементов, вы можете изменить формулу на:

(Обратите внимание на точки с запятой между числами в константе массива в конце этой формулы. Точки с запятой означают столбец данных, а не строку.)

…Если вы хотите использовать этот метод поиска для ячейки, которой нет в таблице, замените «[@Tags]» ссылкой на ячейку.

…Если вы хотите протестировать более пяти элементов, просто добавьте их в свой список и добавьте последовательные степени двойки к вашему списку чисел. Например, если вы хотите протестировать восемь элементов, ваш список номеров будет .

Наконец, если вы хотите найти список элементов в двух разных ячейках, вы должны использовать два теста СУММПРОИЗВ, подобные приведенному выше, причем оба они содержатся в одной функции И, например:

Конечно, если вы хотите выполнить поиск в четырех ячейках, вы должны включить четыре теста СУММПРОИЗВ в свою функцию И.

Одной из наиболее распространенных задач в Excel является фильтрация данных. Вероятно, в каждом аспекте вашего использования Excel вы используете фильтр, фильтрация может выполняться как с отдельными элементами, так и с несколькими элементами. Сегодня мы собираемся показать вам, как искать несколько элементов в фильтре.

Для начала давайте познакомимся с сегодняшним примером набора данных.

У нас есть простая таблица с несколькими случайными людьми из разных стран, их любимыми видами спорта и любимыми игроками. Используя этот набор данных, мы будем фильтровать по разным элементам.

Обратите внимание, что это базовый набор данных, в реальной жизни вы можете столкнуться со многими сложными и большими наборами данных.

Практическая рабочая тетрадь

Вы можете загрузить учебную тетрадь по ссылке ниже.

Поиск нескольких элементов в фильтре Excel

1. Использование основного фильтра

Опция базового фильтра может быть полезна для поиска нескольких элементов. Давайте изучим этот инструмент.

Я. Непосредственное использование фильтра

Мы можем использовать базовый параметр фильтра напрямую для поиска нескольких элементов. Вы найдете этот параметр «Фильтр» в разделе «Сортировка и фильтр» на вкладке «Данные».

Сначала выберите диапазон данных, для которого вы хотите использовать фильтр, а затем нажмите "Фильтр".

Вы найдете значок фильтра в нижнем углу заголовков столбцов.

Теперь нам нужно нажать на любой из значков фильтра, для которого мы хотим отфильтровать наши данные. Например, мы собираемся со столбцом Страна.

Будут видны все названия стран. Поскольку наша задача состоит в том, чтобы использовать несколько элементов для фильтрации, мы выберем из них несколько стран.

Прежде всего выберите страну. Здесь мы выбрали Австралия.

Выбран один элемент, теперь нам нужно выбрать еще несколько элементов (поскольку мы собираемся искать несколько элементов).

Здесь мы выбрали Канаду и США. Вы можете предпочесть свой. Теперь нажмите ОК.

Нам были представлены данные только по этим трем странам.

Мы отфильтровали наш набор данных по нескольким элементам (странам). Мы можем выполнять поиск не только в одном столбце, но и в нескольких столбцах.

В нашем примере теперь мы будем фильтровать столбцы Любимые виды спорта. Вам нужно щелкнуть значок фильтра в этом столбце.

Теперь выберите любой из вариантов, здесь мы выбираем Футбол и Теннис.

Здесь мы найдем отфильтрованные данные.

II. Использование вспомогательного столбца для фильтрации

В предыдущем разделе мы напрямую использовали параметр «Фильтр». Теперь мы увидим, как фильтровать с помощью вспомогательного столбца.

Здесь мы должны сначала перечислить элементы, которые мы хотим найти в нашем наборе данных.

Мы перечислили три страны отдельно от нашего набора данных. И добавил вспомогательный столбец.

Мы заполним этот Вспомогательный (вспомогательный) столбец, используя формулу, сформированную функцией СЧЁТЕСЛИ. COUNTIF подсчитывает ячейки с критериями. Чтобы узнать больше об этой функции, посетите эту статью СЧЁТЕСЛИ.

Формула будет

H4:H6 – это ссылка на диапазон округов, в которых выполняется поиск, а C4 – первая ячейка из столбца Страна.

Мы нашли номер экземпляра страны (США) в списке стран поиска.

Используйте автозаполнение Excel, чтобы заполнить вспомогательный (вспомогательный) столбец. Там, где страны совпадали, мы нашли 1, иначе 0.

Теперь используйте параметр "Фильтр" в столбце Helper (Helping) и выберите 1 из него.

Здесь мы нашли данные из нужных нам стран.

Похожие чтения:

Как применить несколько фильтров в Excel [методы + VBA]
Фильтрация нескольких критериев в Excel (4 подходящих способа)
Как независимо отфильтровать несколько столбцов в Excel

2. Использование расширенного фильтра для поиска нескольких элементов

Мы можем использовать расширенный фильтр для поиска нескольких элементов. Давайте изучим этот метод.

Я. Несколько значений для одного столбца

Мы можем искать несколько значений в одном столбце. Здесь мы перечислили несколько видов спорта.

Убедитесь, что столбец поиска имеет то же имя, что и исходный столбец. Теперь нажмите «Расширенный фильтр» в разделе «Сортировка и фильтр» на вкладке «Данные».

Перед вами откроется диалоговое окно

Расширенный фильтр.

Затем вам нужно выбрать Диапазон списка и Диапазон критериев.

Здесь мы выбрали наш набор данных в диапазоне списка и столбец поиска в диапазоне критериев. Теперь нажмите ОК.

Мы искали следующие виды спорта: Футбол и Крикет. И в нашем наборе данных мы нашли только эти виды спорта.

II. Несколько значений для нескольких столбцов

Мы можем использовать несколько столбцов при поиске значений. В нашем примере мы будем фильтровать по параметрам Страна и Любимые виды спорта.

Здесь мы взяли США и Индию для поиска в столбце Страна и футбол и крикет для столбца Любимые виды спорта.

Теперь задействуйте расширенный фильтр и установите диапазоны для соответствующих полей.

Здесь мы выбрали наш набор данных в диапазоне списка и столбцы поиска в диапазоне критериев. Теперь нажмите ОК.

Мы нашли набор данных о значениях из наших элементов поиска.

Обратите внимание, что если мы использовали несколько столбцов, процесс фильтрации будет рассматривать их как отдельные строки.

Заключение

Это все для сеанса. Мы перечислили несколько способов поиска нескольких элементов в фильтре Excel. Надеюсь, вы найдете это полезным. Не стесняйтесь комментировать, если что-то кажется трудным для понимания. Сообщите нам, какой из методов вы собираетесь использовать. Сообщите нам о подходах, которые мы могли здесь пропустить.

< /p>

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

В Excel функция «Найти и заменить» используется широко и удобно. Однако функция «Найти и заменить» может искать или находить значение только за один раз. Иногда, если вы хотите найти два значения одновременно, как вы можете это сделать? Теперь я познакомлю вас с быстрым способом поиска нескольких значений одновременно в Excel.

Повторное использование всего: добавляйте наиболее часто используемые или сложные формулы, диаграммы и другие объекты в избранное и быстро используйте их повторно в будущем.
Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты объединения: несколько книг и листов в одну; Объединение нескольких ячеек/строк/столбцов без потери данных; Объединить повторяющиеся строки и суммировать.
Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Одна рабочая книга для нескольких файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
Вставить, пропуская скрытые/отфильтрованные строки; Подсчет и сумма по цвету фона; Массовая рассылка персонализированных электронных писем нескольким получателям.
Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировка по неделям, дням, частоте и т. д.; Фильтр по полужирному шрифту, формулам, комментариям.
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Поиск нескольких значений одновременно с помощью Kutools for Excel

Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Сэкономьте 50 % своего времени и сократите количество кликов мышью каждый день!

Если вы установили Kutools for Excel — полезную и удобную надстройку Excel, вы можете использовать ее функцию «Выбрать определенные ячейки», чтобы быстро найти несколько значений одновременно.

<р>1. Выберите диапазон, в котором вы хотите найти значения, и нажмите Kutools > Выбрать инструменты > Выбрать определенные ячейки. Смотрите скриншот:

<р>2. В диалоговом окне «Выбрать определенные ячейки» выполните следующие действия:

Отметить параметр «Ячейка» в разделе «Тип выбора»;

Выберите «Равно» в двух раскрывающихся списках и введите значения, которые вы хотите найти, в каждое текстовое поле в разделе «Особый тип»;

Отметьте или вариант.

<р>3. Нажмите «ОК» или «Применить», чтобы применить функцию, и появится диалоговое окно, в котором будет указано количество выбранных ячеек. Смотрите скриншот:

<р>4. Щелкните OK > Ok, чтобы закрыть диалоговые окна. Теперь вы можете видеть, что значения, которые вы хотите сразу найти, выбраны.

Примечания:

<р>1. Если вы хотите искать значения на всем листе, вы можете щелкнуть здесь, чтобы выбрать весь лист:

<р>2. Утилита «Выбрать определенные ячейки» может искать или находить только два разных значения одновременно.

В этом руководстве мы будем использовать формулы 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. Затем мы рассмотрели, как объединить результаты в одну ячейку или по строке, а также упорядочить результаты.

Читайте также: