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

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выборка последнего значения из массива по условиям (Формулы/Formulas)

vit-2

Дата: Понедельник, 08.01.2018, 12:21 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 15

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Здравствуйте, форумчане!

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

Нужный номер из таблицы вносится в ячейку B43. В ячейке B46 работающая формула с выборкой из массива по нескольким условиям с помощью функции СЧЕТ. По такой же схеме пользуюсь функцией СРЗНАЧ. Но в данном случае необходимо выбрать последнее порядковое значение из массива, которое удовлетворяет тем же условиям.

Буду благодарен за помощь.

К сообщению приложен файл: -2-.xlsx (28.2 Kb)

Ответить

Karataev

Дата: Понедельник, 08.01.2018, 13:47 | Сообщение № 2

Группа: Проверенные

Ранг: Старожил

Сообщений: 1334

Репутация: 533 ±

Замечаний: 0% ±

Excel

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

Код

=МАКС(ЕСЛИ(($C$3:$C$397>$D$407)*($O$3:$O$397<>0)*($B$3:$B$397<$B$410)*($D$3:$D$397<100)*($E$3:$E$397=$E$410)*($J$3:$J$397=$J$410)*($AB$3:$AB$397>0,01);СТРОКА($A$3:$A$397)))

Сообщение отредактировал Karataev - Понедельник, 08.01.2018, 15:58

Ответить

vit-2

Дата: Понедельник, 08.01.2018, 15:44 | Сообщение № 3

Группа: Пользователи

Ранг: Новичок

Сообщений: 15

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Karataev, Спасибо за ответ.

Вот файл побольше, где видно что моя формула с функцией СЧЕТ в ячейке B423 работает. А вот то что мне нужно я еще не увидел.

К сообщению приложен файл: 7103082.xlsx (98.1 Kb)

Сообщение отредактировал vit-2 - Понедельник, 08.01.2018, 15:47

Ответить

Karataev

Дата: Понедельник, 08.01.2018, 15:56 | Сообщение № 4

Группа: Проверенные

Ранг: Старожил

Сообщений: 1334

Репутация: 533 ±

Замечаний: 0% ±

Excel

Да, неправильно я составил формулу. Исправил формулу в посте 2.

Ответить

vit-2

Дата: Понедельник, 08.01.2018, 16:33 | Сообщение № 5

Группа: Пользователи

Ранг: Новичок

Сообщений: 15

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Karataev, спасибо. Номер строки 198 находит точно. А что если надо найти предпоследнее значение, или 3-е снизу?

П.С. Почему то у меня не получается выборка значения с функцией ИНДЕКС из ячейки: ни если написать

Код

=ИНДЕКС($AB$6:$AB$397;МАКС(ЕСЛИ(($C$3:$C$397>$D$407)*($O$3:$O$397<>0)*($B$3:$B$397<$B$410)*($D$3:$D$397<100)*($E$3:$E$397=$E$410)*($J$3:$J$397=$J$410)*($AB$3:$AB$397>0,01);СТРОКА($A$3:$A$397))))

;

ни так

Код

=ИНДЕКС($A$6:$AB$397;МАКС(ЕСЛИ(($C$3:$C$397>$D$407)*($O$3:$O$397<>0)*($B$3:$B$397<$B$410)*($D$3:$D$397<100)*($E$3:$E$397=$E$410)*($J$3:$J$397=$J$410)*($AB$3:$AB$397>0,01);СТРОКА($A$3:$A$397)));28)

Ответить

Karataev

Дата: Понедельник, 08.01.2018, 16:46 | Сообщение № 6

Группа: Проверенные

Ранг: Старожил

Сообщений: 1334

Репутация: 533 ±

Замечаний: 0% ±

Excel

Здесь укажите фрагмент с первой строки, т.к. функция СТРОКА возвращает номер строки не во фрагменте, а начиная с первой строки листа:

Код

=ИНДЕКС($A$1:$AB$397

Или второй вариант. Корректируйте номера строк здесь:

Код

СТРОКА($A$3:$A$397)-2

Чтобы выбирать предпоследнее или другое, используйте вместо МАКС, НАИБОЛЬШИЙ. Первый наибольший - это последняя строка, второй - предпоследняя и т.д.

PS. Оформляйте формулы тегами. Когда пишите пост, то вверху есть панель инструментов, выберите там "Формула". Исправьте у себя в посте.

Сообщение отредактировал Karataev - Понедельник, 08.01.2018, 16:47

Ответить

vikttur

Дата: Понедельник, 08.01.2018, 16:53 | Сообщение № 7

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2941

Репутация: 526 ±

Замечаний: 0% ±

Замена в формуле из сообщения №2 МАКС на НАИБОЛЬШИЙ(массив;2) - не то?

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

Код

=НАИБОЛЬШИЙ(ЕСЛИ($C$3:$C$397>$D$407;ЕСЛИ($O$3:$O$397<>0;ЕСЛИ($B$3:$B$397<$B$410;ЕСЛИ($D$3:$D$397<100;ЕСЛИ($E$3:$E$397=$E$410;ЕСЛИ($J$3:$J$397=$J$410;ЕСЛИ($AB$3:$AB$397>0,01;СТРОКА($A$3:$A$397))))))));2)

Не разбирался в формуле. Возможно, не то. Показал, как обрезать лишние вычисления.