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

Вход

Регистрация

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

 

= Мир MS Excel/Выбрать ближайшее значение с учетом нескольких условий - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выбрать ближайшее значение с учетом нескольких условий (Формулы/Formulas)
Выбрать ближайшее значение с учетом нескольких условий
Kaktus8 Дата: Пятница, 10.05.2019, 23:04 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 79
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Уважаемые знатоки,
помогите, пожалуйста, по заданным значениям отобрать числа из диапазонов.
1. Лист 1. Взять заданное значение, найти его в диапазоне, по найденным найти парные из другого диапазона, а среди них найти ближайшее по величине к числу из другой ячейки.
2. Лист 2. То же самое, но в случае, если найденных чисел больше одного, выбрать одно из них в зависимости от значения в третьей ячейки.
К сообщению приложен файл: 11_1.xlsx (16.5 Kb)
 
Ответить
СообщениеУважаемые знатоки,
помогите, пожалуйста, по заданным значениям отобрать числа из диапазонов.
1. Лист 1. Взять заданное значение, найти его в диапазоне, по найденным найти парные из другого диапазона, а среди них найти ближайшее по величине к числу из другой ячейки.
2. Лист 2. То же самое, но в случае, если найденных чисел больше одного, выбрать одно из них в зависимости от значения в третьей ячейки.

Автор - Kaktus8
Дата добавления - 10.05.2019 в 23:04
bmv98rus Дата: Суббота, 11.05.2019, 08:05 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
1. Название темы слишком общее.
2. 6. Если ближайших по величине больше одного, то в NP сохраняется ближайшее к среднему значению из найденных парных значений. А если будет 2 значения 1 и 3 ?
Вроде одна тема - один вопрос, для 1 и 2 решение есть, но нужно корректировать тему(ы).


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Суббота, 11.05.2019, 08:19
 
Ответить
Сообщение1. Название темы слишком общее.
2. 6. Если ближайших по величине больше одного, то в NP сохраняется ближайшее к среднему значению из найденных парных значений. А если будет 2 значения 1 и 3 ?
Вроде одна тема - один вопрос, для 1 и 2 решение есть, но нужно корректировать тему(ы).

Автор - bmv98rus
Дата добавления - 11.05.2019 в 08:05
Светлый Дата: Суббота, 11.05.2019, 12:50 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Есть неоптимизированная формула 665 символов. Для всех трёх случаев. Если одинаковое количество равноудалённых от среднего из ближайших, то выбирается минимальное. Ищем 2, есть 1 и 3, выбираем 1.
Если модераторы не против, а мне кажется, что название удовлетворяет правилам, решение можно опубликовать.


Программировать проще, чем писать стихи.
 
Ответить
СообщениеЕсть неоптимизированная формула 665 символов. Для всех трёх случаев. Если одинаковое количество равноудалённых от среднего из ближайших, то выбирается минимальное. Ищем 2, есть 1 и 3, выбираем 1.
Если модераторы не против, а мне кажется, что название удовлетворяет правилам, решение можно опубликовать.

Автор - Светлый
Дата добавления - 11.05.2019 в 12:50
bmv98rus Дата: Суббота, 11.05.2019, 13:02 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
ЭЭЭЭ Светлый, 240. для листа 1 и 178 для 2 в ENG варианте. Делал в лоб.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеЭЭЭЭ Светлый, 240. для листа 1 и 178 для 2 в ENG варианте. Делал в лоб.

Автор - bmv98rus
Дата добавления - 11.05.2019 в 13:02
Pelena Дата: Суббота, 11.05.2019, 13:58 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 19162
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Цитата Светлый, 11.05.2019 в 12:50, в сообщении № 3 ()
Если модераторы не против

Не против.
Немного исправила название темы, хотя в суть задачи не очень вникала. Если есть более удачный вариант, предлагайте, исправлю


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Цитата Светлый, 11.05.2019 в 12:50, в сообщении № 3 ()
Если модераторы не против

Не против.
Немного исправила название темы, хотя в суть задачи не очень вникала. Если есть более удачный вариант, предлагайте, исправлю

Автор - Pelena
Дата добавления - 11.05.2019 в 13:58
bmv98rus Дата: Суббота, 11.05.2019, 15:17 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Код
=INDEX(XD5:YB5;MATCH(MIN(IF(YR5:ZP5=NN5;ABS(AVERAGE(IF(MIN(IF(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)))=IF(YR5:ZP5=NN5;ABS(XD5:YB5-NO5));XD5:YB5))-XD5:YB5)));IF(YR5:ZP5=NN5;ABS(AVERAGE(IF(MIN(IF(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)))=IF(YR5:ZP5=NN5;ABS(XD5:YB5-NO5));XD5:YB5))-XD5:YB5));))

Подправить пришлось, и с 240 до 271 подросла.
и
Код
=INDEX(XD5:YB5;MATCH(MAX(IFERROR(COUNTIFS(YR5:ZP5;NN5;XD5:YB5;IF(YR5:ZP5=NN5;XD5:YB5))^($NP$1*2-1);));IFERROR(COUNTIFS(YR5:ZP5;NN5;XD5:YB5;IF(YR5:ZP5=NN5;XD5:YB5))^($NP$1*2-1););))


Исправления далее.


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Суббота, 11.05.2019, 18:56
 
Ответить
Сообщение
Код
=INDEX(XD5:YB5;MATCH(MIN(IF(YR5:ZP5=NN5;ABS(AVERAGE(IF(MIN(IF(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)))=IF(YR5:ZP5=NN5;ABS(XD5:YB5-NO5));XD5:YB5))-XD5:YB5)));IF(YR5:ZP5=NN5;ABS(AVERAGE(IF(MIN(IF(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)))=IF(YR5:ZP5=NN5;ABS(XD5:YB5-NO5));XD5:YB5))-XD5:YB5));))

Подправить пришлось, и с 240 до 271 подросла.
и
Код
=INDEX(XD5:YB5;MATCH(MAX(IFERROR(COUNTIFS(YR5:ZP5;NN5;XD5:YB5;IF(YR5:ZP5=NN5;XD5:YB5))^($NP$1*2-1);));IFERROR(COUNTIFS(YR5:ZP5;NN5;XD5:YB5;IF(YR5:ZP5=NN5;XD5:YB5))^($NP$1*2-1););))


Исправления далее.

Автор - bmv98rus
Дата добавления - 11.05.2019 в 15:17
Светлый Дата: Суббота, 11.05.2019, 16:51 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Моя кошмарная массивная формула для Лист1:
Код
=ВЫБОР(ЗНАК(МАКС(ЧАСТОТА(ЕСЛИ((YR5:ZP5=NN5)*ABS(XD5:YB5-NO5)*(ABS(XD5:YB5-NO5)=МИН(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5))));ABS(XD5:YB5-NO5));СТРОКА($1:$99)-1))-1)+2;NO5;МИН(ЕСЛИ(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5))=МИН(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)));XD5:YB5));МИН(ЕСЛИ(ABS(СРЗНАЧ(ЕСЛИ(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5))=МИН(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)));XD5:YB5))-ЕСЛИ(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5))=МИН(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)));XD5:YB5))=МИН(ABS(СРЗНАЧ(ЕСЛИ(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5))=МИН(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)));XD5:YB5))-ЕСЛИ(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5))=МИН(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)));XD5:YB5)));XD5:YB5)))
* Оказывается третья часть ВЫБОР работает во всех ситуациях:
Код
=МИН(ЕСЛИ(ABS(СРЗНАЧ(ЕСЛИ(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5))=МИН(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)));XD5:YB5))-ЕСЛИ(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5))=МИН(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)));XD5:YB5))=МИН(ABS(СРЗНАЧ(ЕСЛИ(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5))=МИН(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)));XD5:YB5))-ЕСЛИ(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5))=МИН(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)));XD5:YB5)));XD5:YB5))


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Суббота, 11.05.2019, 17:10
 
Ответить
СообщениеМоя кошмарная массивная формула для Лист1:
Код
=ВЫБОР(ЗНАК(МАКС(ЧАСТОТА(ЕСЛИ((YR5:ZP5=NN5)*ABS(XD5:YB5-NO5)*(ABS(XD5:YB5-NO5)=МИН(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5))));ABS(XD5:YB5-NO5));СТРОКА($1:$99)-1))-1)+2;NO5;МИН(ЕСЛИ(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5))=МИН(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)));XD5:YB5));МИН(ЕСЛИ(ABS(СРЗНАЧ(ЕСЛИ(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5))=МИН(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)));XD5:YB5))-ЕСЛИ(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5))=МИН(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)));XD5:YB5))=МИН(ABS(СРЗНАЧ(ЕСЛИ(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5))=МИН(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)));XD5:YB5))-ЕСЛИ(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5))=МИН(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)));XD5:YB5)));XD5:YB5)))
* Оказывается третья часть ВЫБОР работает во всех ситуациях:
Код
=МИН(ЕСЛИ(ABS(СРЗНАЧ(ЕСЛИ(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5))=МИН(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)));XD5:YB5))-ЕСЛИ(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5))=МИН(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)));XD5:YB5))=МИН(ABS(СРЗНАЧ(ЕСЛИ(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5))=МИН(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)));XD5:YB5))-ЕСЛИ(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5))=МИН(ЕСЛИ(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)));XD5:YB5)));XD5:YB5))

Автор - Светлый
Дата добавления - 11.05.2019 в 16:51
Kaktus8 Дата: Суббота, 11.05.2019, 18:03 | Сообщение № 8
Группа: Пользователи
Ранг: Участник
Сообщений: 79
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
bmv98rus, Светлый,
Ребята, спасибо за помощь!

bmv98rus,
на Лист 2 (там где атрибут), - равное значение пропускается (игнорируется), почему-то.
Т.е. формула на него не реагирует.

Если ближайших по величине больше одного, то в NP сохраняется ближайшее к среднему значению из найденных парных значений. А если будет 2 значения 1 и 3 ?

Поскольку у меня уже начинает не хватать процессорных ресурсов для вычислений, то для упрощения формул(ы) пусть ищется не ближайшее к среднему, а из найденных выбирается меньшее по величине значение.

Светлый,
всё работает без ошибок, но на Лист 2 не реагирует на атрибут (NP1).
В принципе, если bmv98rus переправит свои формулы (на Лист 1, - чтобы выбиралось не ближайшее к среднему, а минимальное из найденных, а на Лист 2, - чтобы формула реагировала и на равное значение), то вопрос можно будет закрыть.

Есть ещё одна просьба. У меня очень большой рабочий файл и формулы, которые Вы написали в предыдущей теме, работают прекрасно, но очень сильно загружают процессор при их массовом добавлении.
Не могли бы Вы или кто-то ещё, как-то их упростить? Был бы рабочий файл поменьше, - не было бы проблем, но сейчас стало затыкаться на несколько минут при каждом действии.


Сообщение отредактировал Kaktus8 - Суббота, 11.05.2019, 18:48
 
Ответить
Сообщениеbmv98rus, Светлый,
Ребята, спасибо за помощь!

bmv98rus,
на Лист 2 (там где атрибут), - равное значение пропускается (игнорируется), почему-то.
Т.е. формула на него не реагирует.

Если ближайших по величине больше одного, то в NP сохраняется ближайшее к среднему значению из найденных парных значений. А если будет 2 значения 1 и 3 ?

Поскольку у меня уже начинает не хватать процессорных ресурсов для вычислений, то для упрощения формул(ы) пусть ищется не ближайшее к среднему, а из найденных выбирается меньшее по величине значение.

Светлый,
всё работает без ошибок, но на Лист 2 не реагирует на атрибут (NP1).
В принципе, если bmv98rus переправит свои формулы (на Лист 1, - чтобы выбиралось не ближайшее к среднему, а минимальное из найденных, а на Лист 2, - чтобы формула реагировала и на равное значение), то вопрос можно будет закрыть.

Есть ещё одна просьба. У меня очень большой рабочий файл и формулы, которые Вы написали в предыдущей теме, работают прекрасно, но очень сильно загружают процессор при их массовом добавлении.
Не могли бы Вы или кто-то ещё, как-то их упростить? Был бы рабочий файл поменьше, - не было бы проблем, но сейчас стало затыкаться на несколько минут при каждом действии.

Автор - Kaktus8
Дата добавления - 11.05.2019 в 18:03
bmv98rus Дата: Суббота, 11.05.2019, 18:55 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
значение пропускается (игнорируется), почему-то.
да забыл я про него :-)
Код

=INDEX(XD5:YB5;IFERROR(MATCH(NO5;IF(YR5:ZP5=NN5;XD5:YB5););MATCH(MAX(IFERROR(COUNTIFS(YR5:ZP5;NN5;XD5:YB5;IF(YR5:ZP5=NN5;XD5:YB5))^($NP$1*2-1);));IFERROR(COUNTIFS(YR5:ZP5;NN5;XD5:YB5;IF(YR5:ZP5=NN5;XD5:YB5))^($NP$1*2-1););)))

По поводу быстродействия - думаю доп. столбцы, облегчат вычисления, не нарушая принципа расчета.
К сообщению приложен файл: 6604422.xlsx (17.0 Kb) · Copy_of_926_1.xlsx (17.8 Kb)


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Суббота, 11.05.2019, 19:25
 
Ответить
Сообщение
значение пропускается (игнорируется), почему-то.
да забыл я про него :-)
Код

=INDEX(XD5:YB5;IFERROR(MATCH(NO5;IF(YR5:ZP5=NN5;XD5:YB5););MATCH(MAX(IFERROR(COUNTIFS(YR5:ZP5;NN5;XD5:YB5;IF(YR5:ZP5=NN5;XD5:YB5))^($NP$1*2-1);));IFERROR(COUNTIFS(YR5:ZP5;NN5;XD5:YB5;IF(YR5:ZP5=NN5;XD5:YB5))^($NP$1*2-1););)))

По поводу быстродействия - думаю доп. столбцы, облегчат вычисления, не нарушая принципа расчета.

Автор - bmv98rus
Дата добавления - 11.05.2019 в 18:55
Kaktus8 Дата: Суббота, 11.05.2019, 20:17 | Сообщение № 10
Группа: Пользователи
Ранг: Участник
Сообщений: 79
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
А можно для Лист1,
сделать, чтобы минимальное выбиралось?

На Лист 2, опять ошибка: атрибут при изменении выбирает второе ближайшее.
Например, для строки 8:для атрибута=0 выбирается 4 вместо 3, а при атрибуте =1, выбирается 3, вместо 1.

Файл с выделением цветом прилагаю.
К сообщению приложен файл: 6604422-XX.xlsx (18.4 Kb)
 
Ответить
СообщениеА можно для Лист1,
сделать, чтобы минимальное выбиралось?

На Лист 2, опять ошибка: атрибут при изменении выбирает второе ближайшее.
Например, для строки 8:для атрибута=0 выбирается 4 вместо 3, а при атрибуте =1, выбирается 3, вместо 1.

Файл с выделением цветом прилагаю.

Автор - Kaktus8
Дата добавления - 11.05.2019 в 20:17
bmv98rus Дата: Суббота, 11.05.2019, 21:10 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
опять ошибка: атрибут при изменении выбирает второе ближайшее.
точнее первое реже/чаще встречающеяся
а вот так
Код
=MIN(IFERROR(INDEX(XD5:YB5;N(INDEX(IFERROR(MATCH(NO5+MIN(IF(YR5:ZP5=NN5;ABS(NO5-XD5:YB5)))*{-1;1};IF(YR5:ZP5=NN5;XD5:YB5););99);)));99))


Код
=NO5+MIN(IF(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)))*(MATCH(MAX(IFERROR(COUNTIFS(XD5:YB5;NO5+MIN(IF(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)))*{-1;1};YR5:ZP5;NN5)^($NP$1*2-1);));IFERROR(COUNTIFS(XD5:YB5;NO5+MIN(IF(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)))*{-1;1};YR5:ZP5;NN5)^($NP$1*2-1););)*2-3)
К сообщению приложен файл: Copy_of_926_2.xlsx (18.3 Kb)


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Суббота, 11.05.2019, 21:25
 
Ответить
Сообщение
опять ошибка: атрибут при изменении выбирает второе ближайшее.
точнее первое реже/чаще встречающеяся
а вот так
Код
=MIN(IFERROR(INDEX(XD5:YB5;N(INDEX(IFERROR(MATCH(NO5+MIN(IF(YR5:ZP5=NN5;ABS(NO5-XD5:YB5)))*{-1;1};IF(YR5:ZP5=NN5;XD5:YB5););99);)));99))


Код
=NO5+MIN(IF(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)))*(MATCH(MAX(IFERROR(COUNTIFS(XD5:YB5;NO5+MIN(IF(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)))*{-1;1};YR5:ZP5;NN5)^($NP$1*2-1);));IFERROR(COUNTIFS(XD5:YB5;NO5+MIN(IF(YR5:ZP5=NN5;ABS(XD5:YB5-NO5)))*{-1;1};YR5:ZP5;NN5)^($NP$1*2-1););)*2-3)

Автор - bmv98rus
Дата добавления - 11.05.2019 в 21:10
Kaktus8 Дата: Суббота, 11.05.2019, 22:53 | Сообщение № 12
Группа: Пользователи
Ранг: Участник
Сообщений: 79
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Вроде всё в порядке!
Большущее спасибо!
yes
 
Ответить
СообщениеВроде всё в порядке!
Большущее спасибо!
yes

Автор - Kaktus8
Дата добавления - 11.05.2019 в 22:53
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выбрать ближайшее значение с учетом нескольких условий (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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