Как найти несколько значений в Excel: шаг за шагом

При работе с большими наборами данных в Microsoft Excel может быть сложно найти несколько значений, соответствующих нескольким условиям. Некоторые встроенные функции, например ВПР, изначально были предназначены для работы с одиночными значениями.

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

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

Пойдем!

Оглавление

• Как использовать функцию ВПР с несколькими значениями
  • Что такое формулы массива?
  • Пример ВПР
  • Последние версии Excel
  • Старые версии
  • Точное совпадение и приблизительное совпадение
  • Подробнее о ВПР
• Как использовать ИНДЕКС и ПОИСКПОЗ для поиска нескольких значений
  • Как использовать ИНДЕКС и ПОИСКПОЗ вместе в Excel 365
  • Работа со старыми версиями Excel
• Как использовать функцию ЕСЛИ для поиска нескольких значений
  • Текущие версии Excel
  • Старые версии Excel
• Как использовать СУММПРОИЗВ для нескольких критериев
• 3 распространенные ошибки с функциями поиска
  • 1. #Н/Д Ошибки
  • 2. #ССЫЛКА! Ошибки
  • 3. Круговые ошибки
• Как использовать ЕСЛИОШИБКА с функциями поиска
• 7 продвинутых методов поиска
  • 1. Поиск относительного положения
  • 2. МАЛЕНЬКАЯ функция
  • 3. Функция РЯД
  • 4. Функция ФИЛЬТР
  • 5. Вспомогательные столбцы
  • 6. Динамические массивы
  • 7. Power-запрос
• Последние мысли

Как использовать функцию ВПР с несколькими значениями

Функция ВПР часто используется для поиска отдельных значений в диапазоне данных. Однако вы также можете найти несколько совпадений в Excel с помощью этой формулы поиска.

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

Что такое формулы массива?

Формула массива — это формула, которая может выполнять вычисления с массивами данных. Она называется формулой массива, поскольку может возвращать массив результатов, а не одно значение.

Существует несколько простых шагов при создании формулы массива:

1. Выберите диапазон ячеек для поиска.

2. Введите формулу в строку формул.

3. Нажмите Ctrl + Shift + Enter, чтобы завершить его.

Синтаксис формулы массива аналогичен синтаксису обычной формулы, но включает фигурные скобки {} вокруг формулы. Фигурные скобки указывают, что формула является формулой массива и возвращает массив значений.

Примеры в этой статье покажут вам, как правильно использовать формулы массива.

Пример ВПР

В нашем примере есть пять элементов в столбце A рабочего листа:

• Яблоко

• Банан

• Хлеб

• Морковь

• вишня

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

Последние версии Excel

Синтаксис ВПР зависит от используемой версии Excel.

Самые последние версии ( Excel 365 или Excel 2021 ) поддерживают динамические массивы. Эта функция позволяет формулам возвращать несколько результатов, которые «растекаются» по соседним ячейкам.

Это синтаксис (знак равенства начинает формулу):

=ВПР(искомое_значение, массив_таблиц, индекс_столбца, [диапазон_просмотра])

• lookup_value: значение, которое вы хотите найти.

• table_array: вся таблица данных, которую вы хотите найти.

• col_index_num: номер столбца таблицы в table_array, который содержит данные, которые вы хотите вернуть.

• range_lookup: необязательно. Указывает, хотите ли вы точное совпадение или приблизительное совпадение.

В нашем конкретном примере используется следующая формула:

=ВПР({"яблоко","банан","вишня"},A1:A5,1,ЛОЖЬ)

Когда формула вводится в ячейку B1, результаты переносятся в ячейки C1 и D1. На этом рисунке показан пример в действии:

Старые версии

Если вы используете более старую версию Excel, которая не поддерживает динамические массивы (например, Excel 2019 или более раннюю версию), вам потребуется использовать немного другой подход к формуле массива.

Следуй этим шагам:

1. Щелкните ячейку, в которой вы хотите отобразить результаты для первого элемента (например, столбец B).

2. Введите следующую формулу, еще не нажимая Enter: =ВПР({"яблоко", "банан", "вишня"}, A1:A5, 1, FALSE)

3. Нажмите Ctrl + Shift + Enter, чтобы превратить это в формулу массива.

4. Скопируйте ячейку B1 и вставьте ее в ячейку ниже (или используйте маркеры заполнения).

Когда вы используете Ctrl + Shift + Enter, Excel добавляет формулу в фигурные скобки. Это указывает на то, что это формула массива.

Точное совпадение и приблизительное совпадение

По умолчанию функция ВПР использует приблизительное совпадение. Это означает, что он вернет самое близкое совпадение, которое сможет найти, даже если значения ячеек не являются точным совпадением.

Если вы хотите выполнить точное совпадение, вы можете установить для аргумента range_lookup значение FALSE.

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

Подробнее о ВПР

Если вы хотите узнать больше об этой универсальной функции, ознакомьтесь со следующими статьями:

• как сравнить два столбца в Excel с ВПР

• как найти расхождения с ВПР

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

Как использовать ИНДЕКС и ПОИСКПОЗ для поиска нескольких значений

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

Функция ИНДЕКС в Excel возвращает значение или ссылку на ячейку в указанном диапазоне.

=ИНДЕКС(массив,номер_строки,[номер_столбца])

• массив: диапазон ячеек для поиска значения.

• row_num: номер строки в массиве, из которого возвращается значение.

• column_num: (необязательно) номер столбца в массиве, из которого возвращается значение. Если он опущен, функция возвращает всю строку.

Функция ПОИСКПОЗ в Excel возвращает позицию значения в указанном диапазоне.

=ПОИСКПОЗ(искомое_значение, искомый_массив, [тип_совпадения])

• lookup_value: значение для поиска в lookup_array.

• lookup_array: диапазон ячеек для поиска искомого_значения.

• match_type: (необязательно) Тип сопоставления, которое необходимо выполнить. Если он опущен, функция выполняет точное совпадение.

Как использовать ИНДЕКС и ПОИСКПОЗ вместе в Excel 365

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

Работая с более ранними примерными данными, это формула в Excel 365:

=ИНДЕКС(A1:A5, ПОИСКПОЗ({"яблоко","банан","вишня"}, A1:A5, 0))

Приведенный выше пример разбивается следующим образом:

• ИНДЕКС: возвращает значение ячейки в указанном диапазоне на основе заданного номера строки и столбца. В этом случае он вернет значение из диапазона A1:A5.

• A1:A5: это определенный диапазон таблицы, в которой вы ищете значение и из которого будет возвращен результат.

• ПОИСКПОЗ: это ищет указанный элемент в диапазоне ячеек и возвращает относительное положение этого элемента в диапазоне.

• {"яблоко", "банан", "вишня"}: это константа массива, содержащая значения, которые вы хотите найти.

• A1:A5: это диапазон, в котором ПОИСКПОЗ будет искать значения из константы массива.

• 0: это тип соответствия для функции ПОИСКПОЗ. В данном случае это 0, что означает, что вы ищете точное совпадение, а не близкое.

 На этом рисунке показана формула в действии:

Работа со старыми версиями Excel

Если вы используете старый файл Excel, который не поддерживает динамические массивы (например, Excel 2019 или более ранней версии), вам потребуется использовать другой подход.

Поскольку более старые версии не поддерживают «распределение» формул по соседним ячейкам, вам потребуется разбить использование на три отдельные формулы.

Следуй этим шагам:

1. Нажмите на ячейку, где вы хотите получить результат для первого элемента (например, ячейка B1)

2. Введите приведенную ниже формулу:

3. =ИНДЕКС(A1:A5, ПОИСКПОЗ("яблоко", A1:A5, 0))

4. Нажмите Enter, чтобы выполнить формулу.

5. Введите эту формулу в ячейку B2: =ИНДЕКС(A1:A5, ПОИСКПОЗ("банан", A1:A5, 0))

6. Введите эту формулу в ячейку B3: =ИНДЕКС(A1:A5, ПОИСКПОЗ("вишня", A1:A5, 0))

На этом рисунке показана ссылка на ячейку B3:

Функции ИНДЕКС и ПОИСКПОЗ — не единственные, которые позволяют находить несколько значений. В следующем разделе мы рассмотрим, как вы можете использовать функцию ЕСЛИ в качестве альтернативы.

Как использовать функцию ЕСЛИ для поиска нескольких значений

Другой способ поиска нескольких значений ячеек на основе определенных критериев — использовать функцию ЕСЛИ с другими функциями.

Функция ЕСЛИ позволяет тестировать несколько условий и возвращать разные результаты в зависимости от результата этих тестов.

Например, предположим, что у вас есть таблица данных о продажах со столбцами «Продукт» и «Продажи». Вы хотите найти и суммировать сумму продаж для двух из трех продуктов.

Текущие версии Excel

Чтобы найти сумму столбца «Продажи», где продуктом является «Яблоко» или «Банан», с помощью функции ЕСЛИ вы можете использовать формулу массива с функциями ЕСЛИ, СУММ и ИЛИ.

Предполагая, что ваши данные начинаются с ячейки A1, используйте следующую формулу:

=СУММ(ЕСЛИ((A2:A4="Яблоко")+(A2:A4="Банан"), B2:B4, 0))

Раздел (A2:A4="Яблоко")+(A2:A4="Банан") создает массив со значением 1, если ячейка в диапазоне A2:A4 содержит "Яблоко" или "Банан", и 0 в противном случае. .

Оператор IF проверяет каждый элемент аргумента массива. Если значение равно 1 (т. е. продуктом является «Яблоко» или «Банан»), оно принимает соответствующее значение в столбце «Продажи» (диапазон B2:B4); в противном случае принимается 0.

Функция СУММ суммирует значения функции ЕСЛИ, эффективно суммируя значения продаж как для «Яблока», так и для «Банан».

На этом рисунке показана формула в действии в диапазоне поиска:

Старые версии Excel

В Excel 2019 или более ранней версии вам необходимо использовать формулу массива. Следуй этим шагам:

1. Введите формулу, но не нажимайте Enter.

2. Нажмите Ctrl + Shift + Enter, чтобы сделать его формулой массива.

Excel добавит фигурные скобки {} вокруг формулы, указывая, что это формула массива.

Далее мы рассмотрим, как можно использовать СУММПРОИЗВ для поиска нескольких значений на основе ваших критериев. Пойдем!

Как использовать СУММПРОИЗВ для нескольких критериев

Функция СУММПРОИЗВ также позволяет искать несколько значений на основе нескольких критериев.

Поскольку для этого не требуется использование формулы массива, синтаксис один и тот же независимо от версии Excel.

Используя те же данные, что и в предыдущем примере, формула выглядит так:

=СУММПРОИЗВ((A2:A4="Яблоко")+(A2:A4="Банан"), B2:B4)

Раздел (A2:A4="Яблоко")+(A2:A4="Банан") создает массив со значением 1, если ячейка в диапазоне A2:A4 содержит "Яблоко" или "Банан", и 0 в противном случае. .

Функция СУММПРОИЗВ умножает элементы массива на соответствующие элементы в столбце «Продажи» (диапазон B2:B4). Затем он суммирует полученные значения, эффективно суммируя значения продаж как для «Яблока», так и для «Банан».

Приведенная ниже формула показывает это в действии:

Функции Excel прекрасны, когда они работают должным образом, но иногда вы можете столкнуться с ошибками. В следующем разделе мы рассмотрим некоторые распространенные ошибки и способы их устранения.

3 распространенные ошибки с функциями поиска

Функции поиска иногда могут возвращать ошибки, которые могут вызывать разочарование и отнимать много времени для устранения неполадок. Три самые распространенные ошибки, с которыми вы столкнетесь:

1. #Н/Д Ошибки

2. #ССЫЛКА! Ошибки

3. Круговые ошибки

1. #Н/Д Ошибки

Ошибка #Н/Д возникает, когда искомое значение не может быть найдено в массиве поиска.

Существует несколько причин, по которым может возникнуть эта ошибка, в том числе:

• искомое значение написано с ошибкой или неверно.

• поисковый массив не отсортирован по возрастанию.

• искомое значение отсутствует в наборе данных.

Если искомое значение отсутствует в наборе данных, это полезная информация. Однако неопытные пользователи Excel могут подумать, что #N/A означает, что с формулой что-то пошло не так. В следующем разделе показано, как сделать это более удобным для пользователя.

2. #ССЫЛКА! Ошибки

#REF! ошибка возникает, когда массив поиска или возвращаемый массив удаляется или перемещается.

Эту ошибку можно исправить, обновив ссылки на ячейки в функции поиска.

3. Круговые ошибки

Когда вы комбинируете функции в сложных формулах, Excel может сообщить вам, что у вас есть циклическая ссылка.

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

Как использовать ЕСЛИОШИБКА с функциями поиска

Функция ЕСЛИОШИБКА — полезный инструмент для обработки ошибок в функциях поиска. Он позволяет указать значение или формулу, которые будут возвращаться, если функция поиска возвращает ошибку.

Синтаксис функции ЕСЛИОШИБКА следующий:

=ЕСЛИОШИБКА(значение, значение_если_ошибка)

· значение: значение или формула, которую вы хотите оценить.

· Value_if_error: значение или формула, возвращаемая, если первый аргумент возвращает ошибку.

Например, предположим, что у вас есть функция ВПР, которая просматривает несколько значений в таблице. На рисунке ниже одно из значений не существует в искомом диапазоне данных.

Как видите, отображается ошибка #N/A, которая может сбить с толку неопытных пользователей Excel.

Вместо этого вы можете использовать ЕСЛИОШИБКА, чтобы отобразить пустую ячейку или сообщение «Не найдено» со следующим синтаксисом:

=ЕСЛИОШИБКА(ВПР(искомое_значение, массив_таблиц, индекс_столбца, ЛОЖЬ), «Не найдено»)

В этом примере, если функция ВПР возвращает ошибку, функция ЕСЛИОШИБКА вместо этого вернет сообщение «Не найдено».

На этом рисунке показана формула в действии. Столбец B имеет отсутствующее значение, а столбец C и столбец D нашли совпадения.

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

7 продвинутых методов поиска

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

Существует семь продвинутых методов поиска, которые можно использовать для упрощения и повышения эффективности процесса.

• Поиск относительного положения

• МАЛЕНЬКАЯ функция

• Функция РЯД

• ФИЛЬТР Функция

• Вспомогательные столбцы

• Динамические массивы

• Power Query

1. Поиск относительного положения

Один из самых простых способов поиска нескольких значений в Excel — использование поиска относительного положения. Это включает в себя указание смещения строки и столбца от данной ячейки, чтобы найти желаемое значение.

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

=СМЕЩЕНИЕ(ячейка, 2, 3)

2. МАЛЕНЬКАЯ функция

Другой полезный метод поиска нескольких значений — использование функции МАЛЕНЬКИЙ. Эта функция возвращает n-е наименьшее значение в диапазоне ячеек.

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

Например, следующая формула ищет второе наименьшее значение в диапазоне ячеек, соответствующих определенному условию:

= ИНДЕКС (данные, ПОИСКПОЗ (МАЛЕНЬКИЙ (ЕСЛИ (критерий, диапазон), 2), диапазон, 0))

3. Функция РЯД

Функцию ROW также можно использовать для поиска нескольких значений в Excel. Эта функция возвращает номер строки данной ячейки, который можно использовать для ссылки на ячейки в таблице данных.

Например, следующая формула ищет значение в таблице на основе уникального идентификатора:

=ИНДЕКС(данные, ПОИСКПОЗ(уникальный_идентификатор, данные[уникальный_идентификатор], 0), номер_индекса_столбца)

4. Функция ФИЛЬТР

Функция ФИЛЬТР недоступна в старых версиях Excel.

В Excel 365 вы можете использовать его для фильтрации диапазона ячеек на основе определенных критериев и возврата только тех значений, которые соответствуют этим критериям. Это синтаксис и три аргумента:

=ФИЛЬТР(массив, включить, [if_empty])

• массив: конкретные данные, которые вы хотите отфильтровать.

• include: Аргумент include — это критерии или условия, которые вы хотите применить к массиву.

• [if_empty] (необязательно): возвращаемое значение, если ни одна строка или столбец не соответствует критериям, указанным в аргументе включения.

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

=СУММ(ФИЛЬТР(B2:B4, (A2:A4="Яблоко")+(A2:A4="Банан")))

На этом рисунке показано, сколько строк совпадает, и сумма различных значений:

5. Вспомогательные столбцы

Вы можете использовать вспомогательный столбец для объединения нескольких полей в рамках такой функции, как ВПР.

Предположим, вы работаете с именами и фамилиями на отдельном листе. Вы можете объединить их во вспомогательном столбце, на который ссылается окончательная формула.

6. Динамические массивы

Как вы узнали из предыдущих примеров, пользователи Microsoft 365 могут использовать преимущества динамических массивов для поиска нескольких значений в Excel.

Динамические массивы позволяют возвращать несколько значений из одной формулы, упрощая поиск больших объемов данных.

7. Power-запрос

Power Query — это мощный инструмент в Excel, который можно использовать для возврата значений на основе нескольких критериев.

Например, это видео находит беспорядочные данные в электронной таблице и очищает их.

Последние мысли

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

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

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