Поиск N-го или последнего значения в отсортированном или неотсортированном списке может вызвать затруднения, если вы не понимаете, какие функции использовать.

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

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

Чтобы показать различные методы, я создал список клиентов и их заказов на покупку (P.O). Вы можете скачать его, чтобы следовать примерам.

НАЙТИ N-е ЗНАЧЕНИЕ

Например, как получить второй заказ на покупку, полученный от клиента Карл Людвиг?

1.Использование ФОРМУЛЫ ИНДЕКС, МАЛЕНЬКИЙ И ЕСЛИ ►►►МАССИВ

{=INDEX(E2:E20,SMALL(IF(Customers="Carl Ludwig",ROW(Order_Quantity)-ROW(D1)),2))}= 114143

Как это работает

=ИНДЕКС(E2:E20► ► ►Используется для получения номеров заказов на поставку после получения номера строки функцией SMALL.

{105686;121018;113075;116058;116418;110033;112798;113809;113756;108836;109427;116014;114633;115342;114047;120918;114143;116293;106822}

=SMALL( ► ► ► Предоставляет номер строки функции ИНДЕКС

=IF(► ► ► Предоставляет функции SMALL массив номеров строк

Customers = "Carl Ludwig"► ► определяет критерии, используемые при получении номера строки

{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}

СТРОКА(Количество_Заказа)-СТРОКА(D1) ► ► Возвращает стандартизированные номера строк. Примечание: для стандартизации номеров строк необходимо вычесть номер строки заголовка

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}

Таким образом, после полной оценки функция ЕСЛИ возвращает массив номеров строк, отвечающих критериям:

{FALSE;FALSE;FALSE;FALSE;FALSE;6;FALSE;FALSE;FALSE;FALSE;11;FALSE;FALSE;FALSE;FALSE;FALSE;17;FALSE;FALSE}

Затем функция МАЛЕНЬКИЙ выбирает n-й из этого массива. А так как наше n =2, то номер строки =11

Это номер строки, которую ИНДЕКС использует для возврата номера заказа на поставку.

Следовательно, =ИНДЕКС(E2:E20, 11) = 114143

2. Использование функции АГРЕГАТ

=AGGREGATE(15,6,(E2:E20/(Customers="Carl Ludwig" )),2) =114143

Как это работает

Хитрость в использовании функции АГРЕГАТ заключается в том, чтобы определить массив для функции МАЛЕНЬКИЙ

Поэтому выберите 15 ► МАЛЕНЬКАЯ функция и вариант 6 ►Игнорировать значения ошибок.

E2:E20возвращает все номера заказов

{105686;121018;113075;116058;116418;110033;112798;113809;113756;108836;109427;116014;114633;115342;114047;120918;114143;116293;106822}

В то время как Customers="Carl Ludwig"возвращает логический массив, TRUE, если критерии выполняются, в противном случае FALSE

NB: логический массив преобразуется в числовой эквивалент, где ИСТИНА = 1 и ЛОЖЬ = 0 во время деления.

Таким образом, (E2:E20/(Customers= «Carl Ludwig») приводит к номеру заказа на покупку, где делитель равен 1, и к ошибке, где делитель равен 0.

{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;112798;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;114143;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;116418;#DIV/0!;#DIV/0!}

Поскольку мы выбрали вариант 6 ► игнорировать нулевые значения, окончательный массив будет {112798;114143;116418}

Функция АГРЕГАТ выбирает второе наименьшее значение

3. Использование ПРОСМОТР И СЧЁТЕСЛИ ►► ФОРМУЛА БЕЗ МАССИВА

=LOOKUP(2,1+COUNTIFS(Customers,"Carl Ludwig",A2:A20,"<"&A2:A20),F2:F20)

Эта последняя формула была предложена Питером Бафоломью на форуме Linkedin.

Как это работает

См. объяснение Питера на Linkedin

ПОИСК ПОСЛЕДНЕГО Вхождения В СПИСОК

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

1. Использование ИНДЕКС И МАКС ►► ФОРМУЛА МАССИВА

{=INDEX(E2:E20,MAX((Customers=G3)*ROW(E2:E20)-ROW(E1)))} =116418

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

Функция MAX предоставляется ТОЛЬКО массивом номеров строк, которые соответствуют критериям (заказчик = «Карл Людвиг»)

=MAX({FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}*{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19})

Что упрощает до

=MAX({0;0;0;0;0;6;0;0;0;0;11;0;0;0;0;0;17;0;0})= 17

2.Использование ИНДЕКС И СУММПРОИЗВ ► ►ФОРМУЛА БЕЗ МАССИВА

=INDEX(E2:E20,SUMPRODUCT(MAX((Customers=G3)*ROW(E2:E20)-ROW(E1)))) =116418

Если вам не нравятся формулы массива, вы можете обернуть функцию МАКС внутри СУММПРОИЗВ, которая будет обрабатывать массив за вас.

3. Использование ИНДЕКС И ПОИСКПОЗ ►► ФОРМУЛА МАССИВА

{=INDEX(E2:E20,MATCH(2,1/(Customers=G3)))} = 116418

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

  1. Если совпадений не найдено, функция ПОИСКПОЗ вернет позицию последнего значения в массиве.
  2. Функция ПОИСКПОЗ не возвращает позицию ошибки или пустого значения

Поскольку ПОИСКПОЗ(2,1/(Клиенты=G3)) оценивается как

MATCH(2,{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!})

И значение 2 не может быть найдено в массиве, ПОИСКПОЗ возвращает позицию последнего значения (1), т.е. 17

Следовательно, =ИНДЕКС(E2:E20, 17) = 116418

4. Использование ПРОСМОТРА ► ►ФОРМУЛА БЕЗ МАССИВА

=LOOKUP(2,1/(Customers=G9),E2:E20)

Хитрость здесь заключается в понимании функции ПРОСМОТР, которая имеет синтаксис ПРОСМОТР(значение, искомый_диапазон, [результативный_диапазон])

  1. Если ПРОСМОТР не может найти значение в диапазоне_просмотра, он возвращает позицию наибольшего/последнего значения в массиве, которое меньше или равно lookup_range.
  2. Затем функция ПРОСМОТР использует эту позицию для возврата значения из той же позиции в result_range.

Поскольку ПРОСМОТР(2,1/(Клиенты=G9),E2:E20) оценивается ниже

=LOOKUP(2,{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!},E2:E20)

И значение 2 не может быть найдено в диапазоне_просмотра, тогда функция ПРОСМОТР возвращает позицию наибольшего или последнего значения в lookup_range (17)и возвращает значение в той же позиции в result_range (E2:E20).

Download Worksheet

РЕКОМЕНДУЕМЫЕ ССЫЛКИ:

Первоначально опубликовано на crispexcel.com.