Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Выборка последнего значения из массива по условиям - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выборка последнего значения из массива по условиям (Формулы/Formulas)
Выборка последнего значения из массива по условиям
vit-2 Дата: Понедельник, 08.01.2018, 12:21 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

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

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

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

Буду благодарен за помощь.
К сообщению приложен файл: -2-.xlsx(28.2 Kb)
 
Ответить
СообщениеЗдравствуйте, форумчане!

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

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

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

Автор - vit-2
Дата добавления - 08.01.2018 в 12:21
Karataev Дата: Понедельник, 08.01.2018, 13:47 | Сообщение № 2
Группа: Проверенные
Ранг: Старожил
Сообщений: 1256
Репутация: 485 ±
Замечаний: 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)))


Киви-кошелек: 9166309108
Яндекс-деньги: 410014131888288


Сообщение отредактировал Karataev - Понедельник, 08.01.2018, 15:58
 
Ответить
СообщениеФормула массива для "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 в 13:47
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, Спасибо за ответ.

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

Автор - vit-2
Дата добавления - 08.01.2018 в 15:44
Karataev Дата: Понедельник, 08.01.2018, 15:56 | Сообщение № 4
Группа: Проверенные
Ранг: Старожил
Сообщений: 1256
Репутация: 485 ±
Замечаний: 0% ±

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


Киви-кошелек: 9166309108
Яндекс-деньги: 410014131888288
 
Ответить
СообщениеДа, неправильно я составил формулу. Исправил формулу в посте 2.

Автор - Karataev
Дата добавления - 08.01.2018 в 15:56
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, спасибо. Номер строки 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)

Автор - vit-2
Дата добавления - 08.01.2018 в 16:33
Karataev Дата: Понедельник, 08.01.2018, 16:46 | Сообщение № 6
Группа: Проверенные
Ранг: Старожил
Сообщений: 1256
Репутация: 485 ±
Замечаний: 0% ±

Excel
Здесь укажите фрагмент с первой строки, т.к. функция СТРОКА возвращает номер строки не во фрагменте, а начиная с первой строки листа:
Код
=ИНДЕКС($A$1:$AB$397

Или второй вариант. Корректируйте номера строк здесь:
Код
СТРОКА($A$3:$A$397)-2

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

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


Киви-кошелек: 9166309108
Яндекс-деньги: 410014131888288


Сообщение отредактировал Karataev - Понедельник, 08.01.2018, 16:47
 
Ответить
СообщениеЗдесь укажите фрагмент с первой строки, т.к. функция СТРОКА возвращает номер строки не во фрагменте, а начиная с первой строки листа:
Код
=ИНДЕКС($A$1:$AB$397

Или второй вариант. Корректируйте номера строк здесь:
Код
СТРОКА($A$3:$A$397)-2

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

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

Автор - Karataev
Дата добавления - 08.01.2018 в 16:46
vikttur Дата: Понедельник, 08.01.2018, 16:53 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 2711
Репутация: 477 ±
Замечаний: 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)

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


Сообщение отредактировал vikttur - Понедельник, 08.01.2018, 16:56
 
Ответить
СообщениеЗамена в формуле из сообщения №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)

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

Автор - vikttur
Дата добавления - 08.01.2018 в 16:53
vit-2 Дата: Понедельник, 08.01.2018, 16:53 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Караваев, спасибо за оперативньіе ответьі, вроде все понятно, потом вме проверю, а пока иду в гости, все таки Рождество на дворе. С праздником!
 
Ответить
СообщениеКараваев, спасибо за оперативньіе ответьі, вроде все понятно, потом вме проверю, а пока иду в гости, все таки Рождество на дворе. С праздником!

Автор - vit-2
Дата добавления - 08.01.2018 в 16:53
vit-2 Дата: Понедельник, 08.01.2018, 22:43 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Всем спасибо за помощь. Вопрос решен.

Тема закрыта.
 
Ответить
СообщениеВсем спасибо за помощь. Вопрос решен.

Тема закрыта.

Автор - vit-2
Дата добавления - 08.01.2018 в 22:43
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выборка последнего значения из массива по условиям (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс цитирования
© 2010-2018 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!