Поиск по двум условиям в excel: Двумерный поиск в таблице (ВПР 2D)

Содержание

ВПР (VLOOKUP) по нескольким условиям | by Eugeny Namokonov

2 min read

·

May 5, 2018

Друзья, нас очень часто спрашивают — а как все-таки сделать ВПР по нескольким условиям. Давайте разбираться.

Таблица с примером.

Объединяем условия и диапазоны для поиска

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

Наша таблица — ниже, из нее нужно отобрать ячейку по двум условиям, “а” и “б”.

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

нам нужно найти “аб” в диапазоне [“аб”, “ав”]

Добавляем все в ВПР. Формула массива (arrayformula) нужна, чтобы “на лету” объединить столбцы.

Функция уже работает но в ней всего один столбец данных, добавляем к диапазону тот столбец, который будем выводить, C2:C3.

Конструкцией ниже мы создали массив из двух столбцов (объединенного A2:A3 и B2:B3 и C2:C3), обратная косая черта (“\”) — отделяет столбцы, фигурные скобки {} — создают массив.

{A2:A3&B2:B3\C2:C3}

Итак, формула:

=ArrayFormula(VLOOKUP(E2&F2;{A2:A3&B2:B3\C2:C3};2;0))

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

Тоже самое можно сделать с помощью функции FILTER:

=FILTER(C2:C3;A2:A3=E2;B2:B3=F2)

Если значений большего одного

FILTER, в отличии от ВПР выводит все значения, которые отвечают нашему условию, чтобы оставить одно (например верхнее), добавляем INDEX:

=INDEX(FILTER(C2:C4;A2:A4=E2;B2:B4=F2);1)

Мы много писали и про ВПР и про FILTER, все статьи — в оглавлении нашего канала.

Оглавление канала — все статьи

Канал “Google Таблицы” в Телеграме

Задать свой вопрос

Google Spreadsheets

Google Sheets

Function

Written by Eugeny Namokonov

198 Followers

More from Eugeny Namokonov

Eugeny Namokonov

Bot happens.

Telegram bot + Google Sheets (on webhooks)

Друзья, привет!

4 min read·Aug 20, 2018

Eugeny Namokonov

Отбираем данные (много способов) и оставляем только уникальные строки

Друзья, сегодня ответим на вопрос из нашего чата:

2 min read·Jun 4, 2018

Eugeny Namokonov

Telegram бот, который записывает всё, что видит в Google Таблицу

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

4 min read·Jun 9, 2020

Eugeny Namokonov

Простая CRM-система на Google Формах для постановки и контроля выполнения заданий

Друзья, привет! Наш читатель, Александр Прозоров (@aleprozorov) поделился CRM-системой, которую он внедрил у себя на производстве.

4 min read·Jul 19, 2018

See all from Eugeny Namokonov

Recommended from Medium

Unbecoming

10 Seconds That Ended My 20 Year Marriage

It’s August in Northern Virginia, hot and humid.

I still haven’t showered from my morning trail run. I’m wearing my stay-at-home mom…

·4 min read·Feb 16, 2022

The PyCoach

in

Artificial Corner

You’re Using ChatGPT Wrong! Here’s How to Be Ahead of 99% of ChatGPT Users

Master ChatGPT by learning prompt engineering.

·7 min read·Mar 17

Lists

Staff Picks

300 stories·62 saves

Stories to Help You Level-Up at Work

19 stories·17 saves

Self-Improvement 101

20 stories·39 saves

Productivity 101

20 stories·39 saves

Aleid ter Weel

in

Better Advice

10 Things To Do In The Evening Instead Of Watching Netflix

Device-free habits to increase your productivity and happiness.

·5 min read·Feb 15, 2022

Kristen Walters

in

Adventures In AI

5 Ways I’m Using AI to Make Money in 2023

These doubled my income last year

·9 min read·Feb 16

Linda Caroll

in

The Partnered Pen

I Asked ChatGPT How To Earn $1000 Online.

It Was Hilarious.

Peering in the hive mind can be really helpful, but it can also be so stupid it’s funny

·6 min read·Mar 24

Somnath Singh

in

JavaScript in Plain English

Coding Won’t Exist In 5 Years. This Is Why

Those who won’t adapt would cease to exist.

·8 min read·Jan 20

See more recommendations

Status

Writers

Careers

Privacy

Text to speech

Быстрый поиск в двумерном массиве

В данной статье показаны 2 способа быстрого поиска значений в двумерных массивах.

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

оба способа получают на выходе отфильтрованный двумерный массив.

Способы формирования отфильтрованных массивов — разные:

первый способ использует функцию ArrAutofilterEx

второй способ — функцию ArraySearchResults

Основные отличия и особенности этих 2 способов поиска:

  • ArrAutofilterEx позволяет задавать несколько критериев поиска (фильтрации)
  • ArrAutofilterEx ищет вхождение искомого текста в значения заданных столбцов (неточное совпадение)
  • ArrAutofilterEx при каждом вызове заново в цикле перебирает все элементы массива,
    соответственно, при поиске 10 значений время работы кода увеличивается в 10 раз
  • ArraySearchResults позволяет использовать фильтрацию массива только по одному столбцу
  • ArraySearchResults ищет совпадение искомого текста со значением столбца (точное совпадение)
  • ArraySearchResults производит поиск в заранее сформированной текстовой строке
    Таким образом, перебираются все ячейки массива в цикле только один раз, и поиск 100 значений в массиве займёт ненамного больше времени, чем поиск 1 значения.

Примеры поиска в огромных массивах:

Поиск с использованием ArrAutofilterEx 

Sub ПримерМедленногоПоискаВМассиве()
    t = Timer
    ИскомоеЗначение$ = 560
    СтолбецДляПоиска& = 3
 
    ' загружаем массив с листа
    arr = [a1:d30000].Value
 
    ' укорачиваем массив Arr, оставляя лишь те строки,
    ' где в заданном столбце есть искомое значение
    On Error Resume Next: Err.Clear
    resArr = ArrAutofilterEx(arr, СтолбецДляПоиска& & "=" & ИскомоеЗначение$)
 
    ' проверяем возвращеное функцией значение на наличие результатов поиска
    If Err Then Debug.Print "Такие строки в массиве не найдены": Exit Sub
 
    ' выводим из отфильтрованных строк значения первого столбца
    For i = LBound(resArr) To UBound(resArr)
        Debug.Print "Результат - строка " & i & " из " & UBound(resArr) & ": ", resArr(i, 1)
    Next i
    Debug.Print "Время: " & Timer - t & " сек."
End Sub

Поиск с использованием ArraySearchResults

Sub ПримерБыстрогоПоискаВМассиве()
    t = Timer
    ИскомоеЗначение$ = 560
    СтолбецДляПоиска& = 3
 
    ' загружаем массив с листа
    arr = [a1:d30000]. Value
 
    ' формируем строку поиска
    ss$ = SearchString(arr, СтолбецДляПоиска&)
 
    ' укорачиваем массив Arr, оставляя лишь те строки,
    ' где в заданном столбце есть искомое значение
    resArr = ArraySearchResults(arr, ss$, ИскомоеЗначение$)
 
    ' проверяем возвращеное функцией значение на наличие результатов поиска
    If Not IsArray(resArr) Then Debug.Print "Такие строки в массиве не найдены": Exit Sub
 
    ' выводим из отфильтрованных строк значения первого столбца
    For i = LBound(resArr) To UBound(resArr)
        Debug.Print "Результат - строка " & i & " из " & UBound(resArr) & ": ", resArr(i, 1)
    Next i
    Debug.Print "Время: " & Timer - t & " сек."
End Sub

Код функции ArraySearchResults:

Function ArraySearchResults(ByRef arr, ByRef searchStr As String, ByVal txt As String, _
                            Optional ByVal Sep As String = "%$%") As Variant
    ' функция получает в качестве параметров массив Arr,
    ' и заранее сформированную строку SearchString из значений ячеек нужного столбца массива
    ' По этой строке SearchString функция ищет строки массива, в которые встречается значение txt,
    ' и возвращает усечённый массив, содержащий только подходящие строки
    ' Поиск ведётся по ТОЧНОМУ совпадению значений
    ro& = 0: spl = Split(searchStr, Sep & txt & Sep)
    If UBound(spl) = 0 Then Exit Function    ' нет в массиве нужных строк
    ' перебираем результаты поиска, вычисляя номера строк в исходном массиве
    For i = LBound(spl) To UBound(spl) - 1
        txt = spl(i): ro& = ro& + 1 + (Len(spl(i)) - Len(Replace(spl(i), Sep, ""))) / Len(Sep) \ 2
        spl(i) = ro&
    Next i
    ' подготавливаем массив для результатов:
    ' по ширине - как исходный, по высоте - содержащий столько строк, сколько найдено совпадений
    ReDim resArr(1 To UBound(spl), LBound(arr, 2) To UBound(arr, 2))
    ' заполняем новый массив
    For i = LBound(spl) To UBound(spl) - 1
        For j = LBound(arr, 2) To UBound(arr, 2)
            resArr(i + 1, j) = arr(spl(i), j)
        Next j
    Next i
    ArraySearchResults = resArr
End Function
 
Function SearchString(ByRef arr, ByVal ArrayColumn As Long, _
                      Optional ByVal Sep As String = "%$%") As String
    ' Объединяет все значения из столбца ArrayColumn массива Arr в одну текстовую строку,
    ' в качестве разделителя элементов используя строку Sep
    ' Для ускорения конкатенации длинных строк используются
    ' промежуточные переменные buffer$ и buffer2$
    buffer$ = "": buffer2$ = "": Sep2$ = Sep$ & Sep$: Const BufferLen& = 6000
    On Error Resume Next: Err. Clear: SearchString = Sep2$
    If ArrayColumn > UBound(arr, 2) Or ArrayColumn < LBound(arr, 2) Then Exit Function
    For i = LBound(arr) To UBound(arr)
        buffer$ = buffer$ & Trim$(arr(i, ArrayColumn)) & Sep2$
        If Len(buffer$) > BufferLen& Then
            buffer2$ = buffer2$ & buffer$: buffer$ = ""
            If Len(buffer2$) > BufferLen& * 20 Then _
               SearchString = SearchString & buffer2$: buffer2$ = ""
        End If
    Next i
    SearchString = SearchString & buffer2$ & buffer$
End Function

При поиске только одного значения время работы обоих макросов поиска не сильно отличается — но обычно функция ArraySearchResults оказывается немного быстрее.

Как выполнять поиск по нескольким критериям в Excel (2 простых способа)

Получите БЕСПЛАТНЫЕ продвинутые упражнения Excel с решениями!

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

Скачать практическую рабочую тетрадь

2 подходящих способа поиска с несколькими критериями в Excel

Метод 1: Поиск нескольких критериев типа AND

1.1 Объединение функций ИНДЕКС и ПОИСКПОЗ в строках и столбцах

1.2 Использование функции XLOOKUP

1.3 Применение функции ФИЛЬТР

Метод 2: Поиск нескольких критериев типа OR

2.1 Объединение функций ИНДЕКС и ПОИСКПОЗ в диапазоне дат

2.2 Применение функции XLOOKUP

2.3 Использование функции ФИЛЬТР

Заключение

Дальнейшие чтения

Скачать практическую рабочую тетрадь

Загрузите эту практическую рабочую тетрадь, чтобы тренироваться, пока вы читаете эту статью.

2 подходящих способа поиска с несколькими критериями в Excel

Посмотрите на набор данных ниже. У нас есть идентификаторов сотрудников, имена сотрудников, даты вступления, и зарплаты компании под названием Jupyter Group . Мы будем искать значения с несколькими критериями, используя функции ИНДЕКС, ПОИСКПОЗ, XLOOKUP, и ФИЛЬТР . Вот обзор набора данных для нашей сегодняшней задачи.

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

Метод 1: Поиск нескольких критериев типа AND

Прежде всего, попробуем найти несколько критериев типа И . Здесь И введите несколько критериев, это означает, что одно значение должно удовлетворять всем критериям, которые будут выбраны. Давайте попробуем найти сотрудника с ID больше 400 и зарплата больше $40000 . Вы можете выполнить задание 3 разными способами.

1.1 Объединение функций ИНДЕКС и ПОИСКПОЗ в строках и столбцах

Прежде чем перейти к главному, вы можете взглянуть на функции Excel ИНДЕКС и ПОИСКПОЗ . Найдем сотрудника с ID больше 400 и зарплатой больше 40000$ по формуле INDEX-MATCH . Следуйте инструкциям ниже:

Шаги:

  • Прежде всего, выберите ячейку G7 и запишите следующую формулу.

=ИНДЕКС(C5:C16,ПОИСКПОЗ(1,(B5:B16>400)*(E5:E16>40000),0),1)

  • После этого нажмите Введите на клавиатуре. В итоге мы нашли сотрудника с ID больше 400 и зарплата выше 40000 долларов , Ричард Самуэльсон .

Разбивка формулы

  • B5:B16>400 перебирает все ID в столбце B и возвращает массив TRUE 90 038 и ЛОЖЬ , ИСТИНА при ID больше 400 , иначе FALSE .
  • Е5:Е16>40000 просматривает все окладов в столбце E и возвращает массив из ИСТИНА и ЛОЖЬ , ИСТИНА , когда зарплата больше 40 000 долларов США , иначе 9003 7 ЛОЖЬ.
  • (B5:B16>400)*(E5:E16>40000) умножает два массива TRUE и FALSE и возвращает 1 , когда ID больше 9 0037 400 и зарплата выше 40 000 долларов . В противном случае возвращает 0 .
  • ПОИСКПОЗ(1,(B5:B16>400)*(E5:E16>40000),0) проходит через массив (B5:B16>400)*(E5:E16>40000) и возвращает серийный номер номер первого 1 он встречает.
  • В этом случае возвращается 5, так как первая 1 находится в серийном номере 5.
  • Наконец, ИНДЕКС(C5:C16,ПОИСКПОЗ(1,(B5:B16>400)*(E5:E16>40000),0),1) возвращает имя сотрудника из диапазона C5:C16 , с номером строки, равным выходным данным функции MATCH , и номером столбца, равным 1 .

Примечания

Это формула массива . Поэтому не забудьте нажать Ctrl + Shift + Enter  , если только вы не находитесь в Office 365 .

  • Требуется сотрудник с ID больше 400 и зарплатой больше 40 000 долларов . Теперь, если вы это понимаете, можете ли вы сказать мне формулу, чтобы узнать сотрудника, который присоединился к 9?0037 до 31 декабря 2009 г. , но по-прежнему получает зарплату меньше 25 000 долларов США .
  • После этого введите приведенную ниже формулу в ячейку G7 .

=ИНДЕКС(C5:C16,СООТВЕТСТВИЕ(1,(D5:D16<ДАТА(2009,12,31))*(E5:E16<25000),0),1)

  • Следовательно, нажмите Введите . Далее вы получите Анджела Хопкинс в качестве возврата формулы.

Подробнее: 7 типов поиска, которые можно использовать в Excel

1.2 Использование функции XLOOKUP

Мы также можем выполнить предыдущую задачу, используя XLOOKUP функцию Excel. Но помните, XLOOKUP доступен только в Office 365 . Прежде чем перейти к главному, вы можете взглянуть на функцию Excel XLOOKUP . Теперь находим сотрудника с ID больше 400 и зарплатой больше $40 000 с использованием функции XLOOKUP . Давайте следовать инструкциям ниже, чтобы учиться!

Шаги:

  • Во-первых, введите приведенную ниже формулу в ячейку G7 .

=XLOOKUP(1,(B5:B16>400)*(E5:E16>40000),C5:C16)

  • В итоге у нас получился тот же сотрудник, что и раньше, Ричард Самуэльсон . Это имя сотрудника с ID больше 400 и зарплата больше 40 000 долларов .

Разбивка формулы

  • (B5:B16>400)*(E5:E16>40000) возвращает массив 1 и 0 9003 8, 1 , когда ID больше, чем 400 и зарплата больше 40 000 долларов . 0 иначе.
  • XLOOKUP(1,(B5:B16>400)*(E5:E16>40000),C5:C16) сначала ищет 1 в массиве (B5:B16>400)*(E5:E16>40000). Когда он находит его, он возвращает значение из соседней ячейки в диапазоне C5:C16 .

Подробнее: Как использовать функцию ПРОСМОТР в Excel (4 подходящих примера)

1.3 Применение функции ФИЛЬТР

Формула INDEX-MATCH и XLOOKUP имеют одно ограничение. Если заданным критериям соответствует несколько значений, они возвращают только первое значение. Например, в предыдущем примере, если вы посмотрите внимательно, вы обнаружите, что есть два сотрудника с ID выше 400 и зарплатой выше 40 000 долларов . Это Ричард Самуэльсон и Усман Малик. Но формулы ИНДЕКС-ПОИСКПОЗ и XLOOKUP возвращают только первого сотрудника, Ричарда Самуэльсона . Чтобы получить все значения, удовлетворяющие заданным критериям, вы можете использовать функцию Excel ФИЛЬТР . Но помните, что функция ФИЛЬТР также доступна только в Офис 365 .

Шаги:

  • Чтобы найти сотрудников с ID больше 400 и зарплатой больше 40 000 долларов , формула ФИЛЬТР будет:

=ФИЛЬТР(C5:C16,(B5:B16>400)*(E5:E16>40000))

  • После этого, на этот раз у нас есть все сотрудники, которые соответствуют всем критериям, Ричард Самуэльсон и Усман Малик .

Разбивка формулы

  • (B5:B16>400)*(E5:E16>40000) возвращает массив 1 и 0 9003 8, 1 , когда ID больше 400 и заработная плата превышает 40 000 долларов. 0 в противном случае (см. раздел INDEX-MATCH ).
  • ФИЛЬТР(C5:C16,(B5:B16>400)*(E5:E16>40000)) перебирает все значения в массиве (B5:B16>400)*(E5:E16>40000), и когда он находит 1 , он возвращает соседнее значение из диапазона C5:C16 .
  • Таким образом, мы получаем всех сотрудников с ID больше 400 и зарплатой больше 40 000 долларов .
  • Теперь, если вы это понимаете, можете ли вы сказать мне формулу, чтобы узнать сотрудников, которые присоединились между 1 января 2014 года, и 31 декабря 2016 года , но получили зарплату не менее 30 000 долларов ? Да. Ты прав. Формула будет:

=ФИЛЬТР(C5:C16,(D5:D16>=ДАТА(2014,1,1))*(D5:D16<=ДАТА(2016,12,31))*(E5:E16>= 30000))

Подробнее: Как найти несколько значений в Excel (10 способов)

Метод 2: Поиск нескольких критериев типа OR

Теперь мы попытаемся найти некоторые значения, которые удовлетворяют нескольким критериям из 9Тип 0037 ИЛИ . Здесь критерии типа ИЛИ означают, что одно значение должно удовлетворять по крайней мере одному критерию среди всех критериев, которые должны быть выбраны. Давайте попробуем найти сотрудника, который присоединился до 1 января 2010 года или получает зарплату выше 30 000 долларов США .

2.1 Объединение функций ИНДЕКС и ПОИСКПОЗ в диапазоне дат

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

Шаги:

  • Формула INDEX-MATCH будет такой, как показано в поле формулы ниже.

=ИНДЕКС(C5:C16,СООТВЕТСТВИЕ(ИСТИНА,((D5:D16<ДАТА(2010,1,1))+(E5:E16>30000))>0,0),1)

  • Смотрите, у нас есть Джек Симпсон , первый сотрудник с датой приема на работу до 1 января 2010 года или с зарплатой больше 30 000 долларов . Но сотрудников гораздо больше. Использование ИНДЕКС-ПОИСКПОЗ, получаем только первый.
  • Мы соберем всех сотрудников позже, используя функцию ФИЛЬТР позже. Это искомый сотрудник, который соответствует хотя бы одному критерию.

Разбивка формулы

  • D5:D16 возвращает массив TRUE и FALSE . TRUE , если дата присоединения в столбце D меньше 1 января 2010 г. ЛОЖЬ иначе.
  • E5:E16>30000 также возвращает массив TRUE и FALSE . TRUE , если зарплата превышает 30 000 долларов. ЛОЖЬ иначе.
  • (D5:D1630000) добавляет два массива и возвращает другой массив 0, 1, или 2 . 0 , когда не выполняется ни один критерий, 1 , когда выполняется только один критерий и 2 , когда оба критерия удовлетворены.
  • ((D5:D1630000))>0 перебирает все значения массива (D5:D1630000) и возвращает ИСТИНА , если значение больше 0 ( 1 и 2 ), и ЛОЖЬ в противном случае ( 0 9003 8).
  • ПОИСКПОЗ(ИСТИНА,((D5:D16<ДАТА(2010,1,1))+(E5:E16>30000))>0,0) перебирает все значения в массиве ((D5:D1630000))>0 и возвращает первый серийный номер, где он получает TRUE .
  • В этом случае возвращается 3 , поскольку первое TRUE находится в серийном номере 3 .
  • Наконец, ИНДЕКС(C5:C16,ПОИСКПОЗ(ИСТИНА,((D5:D1630000))>0,0),1) возвращает сотрудника имя из диапазона C5:C16 с серийным номером, возвращаемым функцией MATCH  .

Теперь, если вы это понимаете, можете ли вы сказать мне формулу, чтобы узнать сотрудника с ID меньше, чем 300, или датой приема на работу меньше, чем января 1, 2012, или зарплатой больше 30 000 долларов США ?

Да. Ты прав. Формула будет:

=ИНДЕКС(C5:C16,СООТВЕТСТВИЕ(ИСТИНА,((B5:B16<200)+(D5:D16<ДАТА(2012,1,1))+(E5:E16>30000))>0, 0),1)

Подробнее: Как искать текст в Excel (7 подходящих методов)

2.

2 Применение функции XLOOKUP

Вы можете выполнить ту же задачу, используя функцию XLOOKUP в Excel. XLOOKUP доступен только в Office 365 .

Шаги:

  • Формула для поиска сотрудника с датой приема на работу до 1 января 2010 г., или зарплатой выше $30 000 будет следующей:

=XLOOKUP(ИСТИНА,((D5:D16<ДАТА(2010,1,1))+(E5:E16>30000))>0,C5:C16)

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

Разбивка формулы

  • ((D5:D1630000))>0 возвращает ИСТИНА , если выполняется хотя бы один из двух критериев, иначе ЛОЖЬ . См. раздел выше.
  • XLOOKUP(TRUE,((D5:D1630000))>0,C5:C16) затем возвращает имя сотрудника из столбца C5:C16 , где он получает первый TRUE .

Подробнее: Как найти значение на другом листе в Excel (3 простых метода)

2.3 Использование функции ФИЛЬТР

Наконец, мы выполним ту же задачу, используя функцию ФИЛЬТР в Excel. Функция FILTER доступна только в Office 365 . На этот раз мы получим всех сотрудников, присоединившихся до 1 января 2010 г., или получивших зарплату выше 30 000 долларов США .

Шаги:

  • Формула будет такой же, как показано в поле формулы ниже.

=ФИЛЬТР(C5:C16,((D5:D16<ДАТА(2010,1,1))+(E5:E16>30000))>0)

  • Таким образом, он возвращает всех сотрудников, которые соответствуют хотя бы одному из заданных критериев.
  • Видите ли, на этот раз у нас есть все сотрудники, которые соответствуют заданным критериям, дата приема на работу до 1 января 2010 г., или зарплата выше 30 000 долларов США .

Разбивка формулы

  • ((D5:D1630000))>0 возвращает TRUE , когда хотя бы одно из значений два критерия удовлетворено, иначе ЛОЖЬ . См. раздел INDEX-MATCH .
  • FILTER(C5:C16,((D5:D1630000))>0) просматривает все ячейки в диапазоне C5:C16 но возвращает только те, когда встречает TRUE .

Подробнее: Как найти таблицу в Excel (8 методов)

Заключение

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

Дополнительная литература

  • Как использовать функцию АДРЕС в Excel (7 примеров)
  • Excel ПРОСМОТР и ВПР: с 3 примерами
  • Расширенные функции поиска в Excel (9 примеров)

Частичное совпадение с двумя условиями и возврат нескольких результатов

Автор: Оскар Кронквист Последнее обновление статьи: 26 октября 2021 г. как критерии.

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

Содержание

  1. Частичное совпадение с двумя условиями и возврат нескольких результатов поиска
    1. Как ввести формулу массива
    2. Объяснение формулы
  2. Частичное совпадение с двумя условиями и возврат нескольких результатов поиска — Excel 365
  3. Получить файл Excel

Вопрос:

Как выполнить поиск в списке, содержащем столбец Имя и столбец Фамилия ? Я хочу выполнять поиск в обоих столбцах одновременно с двумя разными критериями (по одному критерию для каждого столбца), а затем отображать несколько результатов поиска.

1. Частичное совпадение с двумя условиями и возврат нескольких результатов поиска

Я создал два поля поиска. Имя и фамилия в F2 и F3. Результаты поиска представлены в столбцах D и E. См. рисунок ниже.

Формула массива в ячейке E8:

=ИНДЕКС($B$3:$C$17, МАЛЕНЬКИЙ(ЕСЛИ(ЧИСЛО((ПОИСК($F$2, $C$3:$C$17))*(ПОИСК($F $3, $B$3:$B$17))), ПОИСКПОЗ(СТРОКА($B$3:$B$17), СТРОКА($B$3:$B$17)), ""), СТРОКИ($A$1:A1) ), COLUMNS($A$1:A1))

В начало

1.1 Как создать формулу массива

  1. Скопируйте (Ctrl + c) и вставьте (Ctrl + v) формулу массива в строку формул.
  2. Нажмите и удерживайте клавиши CTRL + SHIFT.
  3. Нажмите клавишу Enter один раз.
  4. Отпустить все клавиши.

Рекомендуемые статьи

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

Скопируйте ячейку D6 и вставьте ее в ячейки ниже и правее, насколько это необходимо.

В начало

1.2 Объяснение формулы массива в ячейке D6

Шаг 1. Поиск первого частичного совпадения вправо (без учета регистра)

ПОИСК( найти_текст , внутри_текста , [ начальный_номер ])

ПОИСК($F$2, $C$3:$C$17)

становится

90 002 ПОИСК("е",{"Роджер"; "Новак", "Энди", "Николай", "Энди", "Хуан Мартин", "Фернандо", "Фернандо", "Станислав", "Джеймс", "Дэвид", "Томми", "Марин", " Радек»; «Николя»})

и возвращает

{5; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; 3; 3; #ЦЕНИТЬ!; 5; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; 5; #ЦЕНИТЬ!}.

Шаг 2 - Второе частичное совпадение ";"Роддик";"Дель Потро";"Вердаско";"Гонсалес";"Вавринка";"Блейк";"Налбандян";"Робредо";"Чилич";"Степанек";"Альмагро"})

и возвращает

{#ЗНАЧ!; 3; #ЦЕНИТЬ!; 9; 2; 6; 8; 2; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; 2; #ЦЕНИТЬ!; #ЦЕНИТЬ!; 7}.

Шаг 3. Умножение массивов

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

(ПОИСК($F$2, $C$3:$C$17))*(ПОИСК($F$3, $B$3:$B$17))

становится

{5; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; 3; 3; #ЦЕНИТЬ!; 5; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; 5; #ЗНАЧ!}*{#ЗНАЧ!; 3; #ЦЕНИТЬ!; 9; 2; 6; 8; 2; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; 2; #ЦЕНИТЬ!; #ЦЕНИТЬ!; 7}

и возвращает

{#ЗНАЧ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; 24; 6; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!}.

Шаг 4. Проверьте, равно ли значение

a  number

Массив, рассчитанный на предыдущем шаге, имеет ошибочные значения, о которых мы должны позаботиться. Функция ISNUMBER возвращает значение TRUE, если значение в массиве является числом, и FALSE для всего остального, включая значения ошибок.

ISNUMBER((ПОИСК($F$2, $C$3:$C$17))*(ПОИСК($F$3, $B$3:$B$17)))

становится

ISNUMBER({#ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; 24; 6; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ. !})

и возвращает

{ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИННЫЙ; ИСТИННЫЙ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}.

Шаг 5. Замените логические значения соответствующими номерами строк

Функция ЕСЛИ возвращает одно значение, если логическая проверка ИСТИНА, и другое значение, если логическая проверка ложна.

ЕСЛИ(логическая_проверка, [значение_если_истина], [значение_если_ложь])

ЕСЛИ(ЧИСЛО((ПОИСК($F$2, $C$3:$C$17))*(ПОИСК($F$3, $B$3:$B $17))), ПОИСКПОЗ(СТРОКА($B$3:$B$17), СТРОКА($B$3:$B$17)), "")

становится

ЕСЛИ({ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}, MATCH(ROW($B$3:$B$17) ), ROW($B$3:$B$17)), "")

Функция ROW позволяет создавать числа, представляющие строки на основе диапазона ячеек.

СТРОКА($B$3:$B$17) возвращает {3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17}.

Функция ПОИСКПОЗ находит относительное положение заданной строки в массиве или диапазоне ячеек. Это создаст массив от 1 до n, где n — количество строк в диапазоне ячеек $B$3:$B$17.

ПОИСКПОЗ(СТРОКА($B$3:$B$17), СТРОКА($B$3:$B$17))

становится

ПОИСКПОЗ({3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17}, {3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17}). возвращает

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15}.

ЕСЛИ({ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}, MATCH(СТРОКА($B$3:$B$17), СТРОКА ($B$3:$B$17)), "")

становится

ЕСЛИ({ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15}, "")

и возвращает {""; ""; ""; ""; ""; ""; 7; 8; ""; ""; ""; ""; ""; ""; ""}.

Шаг 6. Извлечение k-го наименьшего номера строки

Функция МАЛЕНЬКИЙ возвращает k-е наименьшее значение из группы чисел.

МАЛЕНЬКИЙ( массив ,  k )

МАЛЕНЬКИЙ(ЕСЛИ(ЧИСЛО((ПОИСК($F$2, $C$3:$C$17))*(ПОИСК($F$3, $B$3:$B $17))), MATCH(СТРОКА($B$3:$B$17), СТРОКА($B$3:$B$17)), ""), СТРОКА($A$1:A1))

становится

МАЛЕНЬКИЙ( {""; ""; ""; ""; ""; ""; 7; 8; ""; ""; ""; ""; ""; ""; ""}, ROWS($A$1 :А1))

становится

МАЛЕНЬКИЙ({""; ""; ""; ""; ""; ""; 7; 8; ""; ""; ""; ""; ""; ""; "" }, 1)

и возвращает 7.

Шаг 7. Получите значение из B3:C17 на основе номера строки и столбца

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

ИНДЕКС($B$3:$C$17, МАЛЕНЬКИЙ(ЕСЛИ(ЧИСЛО((ПОИСК($F$2, $C$3:$C$17))*(ПОИСК($F$3, $B$3:$B$17) )), ПОИСКПОЗ(СТРОКА($B$3:$B$17), СТРОКА($B$3:$B$17)), ""), СТРОКИ($A$1:A1)), СТОЛБЦЫ($A$1:A1) )

становится

ИНДЕКС($B$3:$C$17, 7, СТРОКИ($A$1:A1)), СТОЛБЦЫ($A$1:A1))

Функция СТОЛБЦЫ возвращает новое значение в каждой ячейке, это гарантирует, что одно и то же значение не будет возвращено дважды.

ИНДЕКС($B$3:$C$17, 7, COLUMNS($A$1:A1))

становится

ИНДЕКС($B$3:$C$17, 7, 1)

возвращает ""Verdasco" в ячейка E8.

В начало

2. Частичное совпадение с двумя условиями и возврат нескольких результатов поиска — Excel 365

На изображении выше показана формула динамического массива, которая намного короче, чем формула в разделе 1 для предыдущих версий Excel.

Формула динамического массива Excel 365 в ячейке E8:

=ФИЛЬТР($B$3:$C$17, IЧИСЛО(ПОИСК($F$2, $C$3:$C$17)*ПОИСК($F$3, $B$3 :$B$17)))

Вернуться к началу

Объяснение формулы в ячейке E8

Шаг 1. Частичное совпадение с первым условием

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

ПОИСК( найти_текст , внутри_текста , [ начальный_номер ])

ПОИСК($F$2, $C$3:$C$17)

становится

90 002 ПОИСК("е",{" Рог е р"; "Новак"; "Энди"; "Николай"; "Энди"; "Хуан Мартин"; "F e рнандо"; "F e рнандо"; "Станислав"; "Джем e ". s"; "David"; "Tommy"; "Marin"; "Rad e k"; "Nicolas"})

и возвращает

{ 5 ; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; 3 ; 3 ; #ЦЕНИТЬ!; 5 ; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; 5 ; #ЦЕНИТЬ!}.

Шаг 2 — Частичное совпадение второго условия

ПОИСК($F$3, $B$3:$B$17)

становится

8 Кович" ;"Murray";"Davydenk o ";"R o ddick";"Del P o tro";"Verdasc o ";"G o nzalez";"Wawrinka ";"Блейк ";"Налбандян";"R o бредо";"Чилич";"Степанек";"Алмагр или "})

и возвращает

{#ЗНАЧ!; 3; #ЗНАЧ!; 9; 2; 6; 8; 2; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; 2; #ЗНАЧ! ; #VALUE!; 7}.

Шаг 3 — Логика И

Символ звездочки позволяет умножать массивы, создавая логику И, означающую, что оба значения в одной и той же позиции должны быть числом. Это будет соответствовать только строкам, в которых выполняются оба условия . !; #ЗНАЧ!; #ЗНАЧ!; 3; 3; #ЗНАЧ!; 5; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; 5; #ЗНАЧ!}*{#ЗНАЧ!; 3; #ЗНАЧ!; 9; 2; 6; 8; 2; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; 2; #ЦЕНИТЬ!; #ЦЕНИТЬ!; 7}

и возвращает

{#ЗНАЧ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; 24; 6; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!}.

Шаг 4. Проверка числа

Массив, рассчитанный на предыдущем шаге, содержит ошибочные значения, о которых мы должны позаботиться. Функция ISNUMBER возвращает значение TRUE, если значение в массиве является числом, и FALSE для всего остального, включая значения ошибок.

ISNUMBER((ПОИСК($F$2, $C$3:$C$17))*(ПОИСК($F$3, $B$3:$B$17)))

становится

IНОМЕР({#ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; 24; 6; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ !; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!})

и возвращает

{ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИННЫЙ; ИСТИННЫЙ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}.

Шаг 5. Извлечение записей

Функция ФИЛЬТР позволяет извлекать значения/строки на основе условия или критерия. Он находится в категории «Поиск и ссылки» и доступен только подписчикам Excel 365.

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

ФИЛЬТР($B$3:$C$17, ISNUMBER(ПОИСК($F$2, $C$3:$C$17)*ПОИСК($F$3, $B$3 :$B$17)))

становится

ФИЛЬТР($B$3:$C$17, {ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ; FALSE})

и возвращает

{"Verdasco", "Fernando"; «Гонсалес», «Фернандо»}.

В начало

3. Получить файл Excel

Получить файл Excel

поиск по нескольким критериям с несколькими результатами1.xls

В начало

Поиск и возврат категории нескольких значений

Частичное совпадение и возврат нескольких соседних значений
В этой статье демонстрируются формулы массива, которые выполняют поиск значений ячеек, содержащих строку поиска, и возвращают соответствующие значения […]

Поиск и отображение всех ячеек, содержащих несколько строк поиска
Джером спрашивает в этом сообщении блога Поиск нескольких текстовых строк в нескольких ячейках в Excel: Если список […]

Частичное совпадение для нескольких строк — логика И
В этой статье демонстрируются формулы, которые позволяют выполнять частичные совпадения на основе нескольких строк и возвращать эти строки, если все […]

Просмотреть все статьи в категории «поиск и возврат нескольких значений»

Функции в этой статье

INDEXSMALLIFISNUMBERSEARCHMATCHROWROWSCOLUMNSFILTER

Подробнее чем 1300 формул Excel

Категории Excel

Домашняя страница

Последние обновленные статьи.

Функции Excel

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

Формулы Excel

Более 1300 формул, организованных в подкатегории.

Таблицы Excel

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

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

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

Проверка данных

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

Раскрывающийся список

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

Именованные диапазоны

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

Excel Solver

Excel Solver — это бесплатная надстройка, которая использует целевые ячейки, ограничения, основанные на формулах на листе, для выполнения анализа «что, если» и других проблем принятия решений, таких как перестановки и комбинации.

Диаграммы

Функция Excel, позволяющая визуализировать данные в виде графика.

Условное форматирование

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

Сводные таблицы

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

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