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

Вход

Регистрация

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

 

= Мир MS Excel/Найти ПЕРВОЕ наименьшее число по нескольким критериям - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Найти ПЕРВОЕ наименьшее число по нескольким критериям (Формулы/Formulas)
Найти ПЕРВОЕ наименьшее число по нескольким критериям
qwerty4869 Дата: Вторник, 12.01.2021, 10:59 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Добрый день!
Как можно найти число из списка чисел число, которое будет ПЕРВЫМ наименьшим числом. При этом, необходимо, чтобы оно соответствовало нескольким критериям: 1. было меньше определенного значения (в ячейке B2) 2. выборка соответствовала именно той дате, которая соответствует числу в ячейке B2. Другими словами, нужно найти ПЕРВОЕ наименьшее число из списка за 1 определенную дату меньше определенного значения.

Пробовал экспериментировать с функцией НАИМЕНЬШИЙ, но ничего не получилось, где-то рядом, но не то.
К сообщению приложен файл: 007.xlsx (9.3 Kb)
 
Ответить
СообщениеДобрый день!
Как можно найти число из списка чисел число, которое будет ПЕРВЫМ наименьшим числом. При этом, необходимо, чтобы оно соответствовало нескольким критериям: 1. было меньше определенного значения (в ячейке B2) 2. выборка соответствовала именно той дате, которая соответствует числу в ячейке B2. Другими словами, нужно найти ПЕРВОЕ наименьшее число из списка за 1 определенную дату меньше определенного значения.

Пробовал экспериментировать с функцией НАИМЕНЬШИЙ, но ничего не получилось, где-то рядом, но не то.
Автор - qwerty4869
Дата добавления - 12.01.2021 в 10:59
Serge_007 Дата: Вторник, 12.01.2021, 11:18 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2750 ±
Замечаний: ±

Excel 2016
Здравствуйте

Формула массива:
Код
=МИН(ЕСЛИ((H$2:H$18=A2*(I$2:I$18<B2));I$2:I$18))
К сообщению приложен файл: 20210112_qwerty.xls (28.0 Kb)

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеЗдравствуйте

Формула массива:
Код
=МИН(ЕСЛИ((H$2:H$18=A2*(I$2:I$18<B2));I$2:I$18))
Автор - Serge_007
Дата добавления - 12.01.2021 в 11:18
qwerty4869 Дата: Вторник, 12.01.2021, 11:28 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Цитата Serge_007, 12.01.2021 в 11:18, в сообщении № 2 ()
=МИН(ЕСЛИ((H$2:H$18=A2*(I$2:I$18<B2));I$2:I$18))

Наверное, я немного неверно сформулировал задачу. Имеется в виду, что должно находиться первое число меньшее определенного. В примере, это первое число в столбце I меньшее 32, т.е. должно получиться 27.
 
Ответить
Сообщение
Цитата Serge_007, 12.01.2021 в 11:18, в сообщении № 2 ()
=МИН(ЕСЛИ((H$2:H$18=A2*(I$2:I$18<B2));I$2:I$18))

Наверное, я немного неверно сформулировал задачу. Имеется в виду, что должно находиться первое число меньшее определенного. В примере, это первое число в столбце I меньшее 32, т.е. должно получиться 27.
Автор - qwerty4869
Дата добавления - 12.01.2021 в 11:28
Serge_007 Дата: Вторник, 12.01.2021, 11:46 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2750 ±
Замечаний: ±

Excel 2016
Код
=ИНДЕКС(I$2:I$18;МИН(ЕСЛИ((H$2:H$18=A2*(I$2:I$18<B2));СТРОКА($1:$17))))
К сообщению приложен файл: 8910901.xls (28.0 Kb)

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Код
=ИНДЕКС(I$2:I$18;МИН(ЕСЛИ((H$2:H$18=A2*(I$2:I$18<B2));СТРОКА($1:$17))))
Автор - Serge_007
Дата добавления - 12.01.2021 в 11:46
Egyptian Дата: Вторник, 12.01.2021, 12:47 | Сообщение № 5
Группа: Проверенные
Ранг: Ветеран
Сообщений: 534
Репутация: 196 ±
Замечаний: 0% ±

Excel 2013/2016
Еще вариант, немассивный. Не будет работать в экселе ниже 2010.
Код
=INDEX($I$2:$I$18,AGGREGATE(15,6,ROW($A$1:$A$20)/(($H$2:$H$18=A2)/($I$2:$I$18<B2)),1))
 
Ответить
СообщениеЕще вариант, немассивный. Не будет работать в экселе ниже 2010.
Код
=INDEX($I$2:$I$18,AGGREGATE(15,6,ROW($A$1:$A$20)/(($H$2:$H$18=A2)/($I$2:$I$18<B2)),1))
Автор - Egyptian
Дата добавления - 12.01.2021 в 12:47
qwerty4869 Дата: Вторник, 12.01.2021, 13:45 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

А реально ли добавить еще одно условие выборки? То есть, чтобы искал только значения не только по дате, но и, например, по времени, начиная с 10:00 до 16:00?
К сообщению приложен файл: 008.xlsx (9.6 Kb)
 
Ответить
СообщениеА реально ли добавить еще одно условие выборки? То есть, чтобы искал только значения не только по дате, но и, например, по времени, начиная с 10:00 до 16:00?
Автор - qwerty4869
Дата добавления - 12.01.2021 в 13:45
Serge_007 Дата: Вторник, 12.01.2021, 13:49 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2750 ±
Замечаний: ±

Excel 2016
Цитата qwerty4869, 12.01.2021 в 13:45, в сообщении № 6 ()
реально ли добавить еще одно условие выборки?
Реально. Количество условий ограничено разве-что максимумом знаков в формуле

В ЕСЛИ() добавляйте к [vba]
Код
H$2:H$18=A2*(I$2:I$18<B2)*...условие3*...условие4*...условие5
[/vba] и т.д.

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата qwerty4869, 12.01.2021 в 13:45, в сообщении № 6 ()
реально ли добавить еще одно условие выборки?
Реально. Количество условий ограничено разве-что максимумом знаков в формуле

В ЕСЛИ() добавляйте к [vba]
Код
H$2:H$18=A2*(I$2:I$18<B2)*...условие3*...условие4*...условие5
[/vba] и т.д.
Автор - Serge_007
Дата добавления - 12.01.2021 в 13:49
Serge_007 Дата: Вторник, 12.01.2021, 14:08 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2750 ±
Замечаний: ±

Excel 2016
Код
=ЕСЛИ(СУММ(-(H$2:H$18=A2*(I$2:I$18<B2)))=0;"Нет данных по критериям отбора";ИНДЕКС(I$2:I$18;МИН(ЕСЛИ((H$2:H$18=A2*(I$2:I$18<B2));СТРОКА($1:$17)))))

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Код
=ЕСЛИ(СУММ(-(H$2:H$18=A2*(I$2:I$18<B2)))=0;"Нет данных по критериям отбора";ИНДЕКС(I$2:I$18;МИН(ЕСЛИ((H$2:H$18=A2*(I$2:I$18<B2));СТРОКА($1:$17)))))
Автор - Serge_007
Дата добавления - 12.01.2021 в 14:08
Egyptian Дата: Вторник, 12.01.2021, 14:33 | Сообщение № 9
Группа: Проверенные
Ранг: Ветеран
Сообщений: 534
Репутация: 196 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата qwerty4869, 12.01.2021 в 13:45, в сообщении № 6 ()
чтобы искал только значения не только по дате, но и, например, по времени

Вот, взгляните.
Временной диапазон задаете в ячейках Е1 - F1
К сообщению приложен файл: 2737881.xlsx (9.6 Kb)


Сообщение отредактировал Egyptian - Вторник, 12.01.2021, 14:34
 
Ответить
Сообщение
Цитата qwerty4869, 12.01.2021 в 13:45, в сообщении № 6 ()
чтобы искал только значения не только по дате, но и, например, по времени

Вот, взгляните.
Временной диапазон задаете в ячейках Е1 - F1
Автор - Egyptian
Дата добавления - 12.01.2021 в 14:33
Egyptian Дата: Вторник, 12.01.2021, 17:14 | Сообщение № 10
Группа: Проверенные
Ранг: Ветеран
Сообщений: 534
Репутация: 196 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата qwerty4869, 12.01.2021 в 17:07, в сообщении № 12 ()
А есть ли способ узнать номер строки полученного значения?

Да, легко. Оставляете лишь часть формулы без индекса. Именно она и сообщает индексу номер строки.
Код
=AGGREGATE(15,6,ROW($A$1:$A$20)/(($H$2:$H$18=A2)/($J$2:$J$18<B2)/($I$2:$I$18>=$E$1)/($I$2:$I$18<=$F$1)),1)
 
Ответить
Сообщение
Цитата qwerty4869, 12.01.2021 в 17:07, в сообщении № 12 ()
А есть ли способ узнать номер строки полученного значения?

Да, легко. Оставляете лишь часть формулы без индекса. Именно она и сообщает индексу номер строки.
Код
=AGGREGATE(15,6,ROW($A$1:$A$20)/(($H$2:$H$18=A2)/($J$2:$J$18<B2)/($I$2:$I$18>=$E$1)/($I$2:$I$18<=$F$1)),1)
Автор - Egyptian
Дата добавления - 12.01.2021 в 17:14
qwerty4869 Дата: Вторник, 12.01.2021, 17:49 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Цитата qwerty4869, 12.01.2021 в 17:22, в сообщении № 14 ()
В примере первое значение определилось корректно, а вот потом неверно указывались значения

Это я про номер строки
 
Ответить
Сообщение
Цитата qwerty4869, 12.01.2021 в 17:22, в сообщении № 14 ()
В примере первое значение определилось корректно, а вот потом неверно указывались значения

Это я про номер строки
Автор - qwerty4869
Дата добавления - 12.01.2021 в 17:49
jakim Дата: Вторник, 12.01.2021, 18:15 | Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 1218
Репутация: 316 ±
Замечаний: 0% ±

Excel 2010
Две формулы. Второго числа нет в указанном диапазоне.

Код
=IFERROR(AGGREGATE(14;6;J$2:J$18/(A2=H$2:H$18)/(B2>J$2:J$18)/($I$2:$I$18>$E$1)/($I$2:$I$18<$F$1);1);"")

Код
=MAX(INDEX(J$2:J$18*(A2=H$2:H$18)*(B2>J$2:J$18)*($I$2:$I$18>$E$1)*($I$2:$I$18<$F$1);0))
К сообщению приложен файл: 2737881-1.xlsx (9.7 Kb)


Сообщение отредактировал jakim - Вторник, 12.01.2021, 18:19
 
Ответить
Сообщение
Две формулы. Второго числа нет в указанном диапазоне.

Код
=IFERROR(AGGREGATE(14;6;J$2:J$18/(A2=H$2:H$18)/(B2>J$2:J$18)/($I$2:$I$18>$E$1)/($I$2:$I$18<$F$1);1);"")

Код
=MAX(INDEX(J$2:J$18*(A2=H$2:H$18)*(B2>J$2:J$18)*($I$2:$I$18>$E$1)*($I$2:$I$18<$F$1);0))
Автор - jakim
Дата добавления - 12.01.2021 в 18:15
qwerty4869 Дата: Вторник, 12.01.2021, 18:27 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Цитата jakim, 12.01.2021 в 18:15, в сообщении № 16 ()
Две формулы. Второго числа нет в указанном диапазоне.

Спасибо! Теперь понять бы как определить номер строк, полученных значений
 
Ответить
Сообщение
Цитата jakim, 12.01.2021 в 18:15, в сообщении № 16 ()
Две формулы. Второго числа нет в указанном диапазоне.

Спасибо! Теперь понять бы как определить номер строк, полученных значений
Автор - qwerty4869
Дата добавления - 12.01.2021 в 18:27
jakim Дата: Вторник, 12.01.2021, 18:37 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 1218
Репутация: 316 ±
Замечаний: 0% ±

Excel 2010
Формула

Код
=IFERROR(MATCH(C2;J$2:J$18;0);"")
К сообщению приложен файл: 2230056.xlsx (9.9 Kb)
 
Ответить
Сообщение
Формула

Код
=IFERROR(MATCH(C2;J$2:J$18;0);"")
Автор - jakim
Дата добавления - 12.01.2021 в 18:37
qwerty4869 Дата: Вторник, 12.01.2021, 18:58 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Цитата jakim, 12.01.2021 в 18:37, в сообщении № 18 ()
=ЕСЛИОШИБКА(ПОИСКПОЗ(C2;J$2:J$18;0);"")

Не срабатывает... Речь о таблице, где значений много больше. В этом конкретном случае нашел строку, близкую к значению, но не именно ту, в которой находится указанное значение.
 
Ответить
Сообщение
Цитата jakim, 12.01.2021 в 18:37, в сообщении № 18 ()
=ЕСЛИОШИБКА(ПОИСКПОЗ(C2;J$2:J$18;0);"")

Не срабатывает... Речь о таблице, где значений много больше. В этом конкретном случае нашел строку, близкую к значению, но не именно ту, в которой находится указанное значение.
Автор - qwerty4869
Дата добавления - 12.01.2021 в 18:58
Egyptian Дата: Вторник, 12.01.2021, 19:25 | Сообщение № 16
Группа: Проверенные
Ранг: Ветеран
Сообщений: 534
Репутация: 196 ±
Замечаний: 0% ±

Excel 2013/2016
qwerty4869, Тогда составьте пример по структуре максимально похожий на рабочий, пометьте строки номера которых надо найти, и выложите.
 
Ответить
Сообщениеqwerty4869, Тогда составьте пример по структуре максимально похожий на рабочий, пометьте строки номера которых надо найти, и выложите.
Автор - Egyptian
Дата добавления - 12.01.2021 в 19:25
qwerty4869 Дата: Вторник, 12.01.2021, 19:47 | Сообщение № 17
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Цитата jakim, 12.01.2021 в 18:37, в сообщении № 18 ()
=ЕСЛИОШИБКА(ПОИСКПОЗ(C2;J$2:J$18;0);"")

Не срабатывает... Речь о таблице, где значений много больше. В этом конкретном случае нашел строку, близкую к значению, но не именно ту, в которой находится указанное значение.
Цитата Egyptian, 12.01.2021 в 19:25, в сообщении № 20 ()
Тогда составьте пример по структуре максимально похожий на рабочий, пометьте строки номера которых надо найти, и выложите.


Смысл тот же самый. Разве что по датам, не 2 дня, а 2-3 года и значения иногда могут повторяться. Нужно, чтобы был именно номер строки найденного значения. Я сам пробовал ПОИСКПОЗ, но эта формула выдает неточные значения, причем иногда ошибается весьма значительно.
Эта формула - =АГРЕГАТ(15;6;СТРОКА($A$1:$A$20)/(($H$2:$H$18=A2)/($J$2:$J$18<B2)/($I$2:$I$18>=$E$1)/($I$2:$I$18<=$F$1));1) уже лучше, но есть небольшие расхождения.
 
Ответить
Сообщение
Цитата jakim, 12.01.2021 в 18:37, в сообщении № 18 ()
=ЕСЛИОШИБКА(ПОИСКПОЗ(C2;J$2:J$18;0);"")

Не срабатывает... Речь о таблице, где значений много больше. В этом конкретном случае нашел строку, близкую к значению, но не именно ту, в которой находится указанное значение.
Цитата Egyptian, 12.01.2021 в 19:25, в сообщении № 20 ()
Тогда составьте пример по структуре максимально похожий на рабочий, пометьте строки номера которых надо найти, и выложите.


Смысл тот же самый. Разве что по датам, не 2 дня, а 2-3 года и значения иногда могут повторяться. Нужно, чтобы был именно номер строки найденного значения. Я сам пробовал ПОИСКПОЗ, но эта формула выдает неточные значения, причем иногда ошибается весьма значительно.
Эта формула - =АГРЕГАТ(15;6;СТРОКА($A$1:$A$20)/(($H$2:$H$18=A2)/($J$2:$J$18<B2)/($I$2:$I$18>=$E$1)/($I$2:$I$18<=$F$1));1) уже лучше, но есть небольшие расхождения.
Автор - qwerty4869
Дата добавления - 12.01.2021 в 19:47
Egyptian Дата: Вторник, 12.01.2021, 19:50 | Сообщение № 18
Группа: Проверенные
Ранг: Ветеран
Сообщений: 534
Репутация: 196 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата qwerty4869, 12.01.2021 в 19:47, в сообщении № 21 ()
есть небольшие расхождения.

Так пометьте их и укажите нужные, так легче будет сориентироваться.
 
Ответить
Сообщение
Цитата qwerty4869, 12.01.2021 в 19:47, в сообщении № 21 ()
есть небольшие расхождения.

Так пометьте их и укажите нужные, так легче будет сориентироваться.
Автор - Egyptian
Дата добавления - 12.01.2021 в 19:50
Serge_007 Дата: Вторник, 12.01.2021, 20:05 | Сообщение № 19
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2750 ±
Замечаний: ±

Excel 2016
Я изменю на время ограничение на вес файла

Сколько он весит?

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеЯ изменю на время ограничение на вес файла

Сколько он весит?
Автор - Serge_007
Дата добавления - 12.01.2021 в 20:05
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Найти ПЕРВОЕ наименьшее число по нескольким критериям (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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