Поиск 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
Хитрость метода заключается в понимании двух особенностей функции ПОИСКПОЗ, т.е.
- Если совпадений не найдено, функция ПОИСКПОЗ вернет позицию последнего значения в массиве.
- Функция ПОИСКПОЗ не возвращает позицию ошибки или пустого значения
Поскольку ПОИСКПОЗ(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)
Хитрость здесь заключается в понимании функции ПРОСМОТР, которая имеет синтаксис ПРОСМОТР(значение, искомый_диапазон, [результативный_диапазон])
- Если ПРОСМОТР не может найти значение в диапазоне_просмотра, он возвращает позицию наибольшего/последнего значения в массиве, которое меньше или равно lookup_range.
- Затем функция ПРОСМОТР использует эту позицию для возврата значения из той же позиции в 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
РЕКОМЕНДУЕМЫЕ ССЫЛКИ:
- get-digital-help.com/2014/02/07/find-last-matching-value-in-an-unsorted-list/
- найти-последнее-вхождение-элемента-в-списке-в-Excel/
- http://blog.contextures.com/archives/2014/02/04/find-last-item-in-group-with-index-match/#comment-282746
Первоначально опубликовано на crispexcel.com.