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

Вход

Регистрация

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

 

= Мир MS Excel/Выбор ячеек, вариация значений которых попадает в диапазон - Мир MS Excel

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

Excel 2016
Добрый день, уважаемые магистры Экселя!
Прошу Вашей помощи.
Передо мной стоит задача следующая: выбрать из столбца ячейки, чтобы вариация и разница между соответствующими им значениям попадала в диапазон. При этом в случае недостаточного количества объектов (например 3,4, но не более 5), при которых в столбцах вылазит ошибка (Н/Д), вариация и разница считались по этим 3 или 4 объектам (чтобы не учитывать ячейки с ошибками).
Конкретно на файле:
На вкладке "Лист1" имеется столбец В2:В11.
Во вкладке "Лист2" в строке D2:H2 нужно выбрать и вывести значения столбца В2:В11, с условием что вариация цены, соответствующей этим ячейкам, рассчитываемая в ячейке D14, была не больше вариации, указанной в ячейке Q8. Аналогично с разницей между объектами (чтобы разница цен, указанная в ячейке D16, была не больше разницы, указанной в ячейке Q9).
К сообщению приложен файл: 5880941.xls (23.0 Kb)


Сообщение отредактировал Dubdubom - Среда, 31.07.2019, 13:52
 
Ответить
СообщениеДобрый день, уважаемые магистры Экселя!
Прошу Вашей помощи.
Передо мной стоит задача следующая: выбрать из столбца ячейки, чтобы вариация и разница между соответствующими им значениям попадала в диапазон. При этом в случае недостаточного количества объектов (например 3,4, но не более 5), при которых в столбцах вылазит ошибка (Н/Д), вариация и разница считались по этим 3 или 4 объектам (чтобы не учитывать ячейки с ошибками).
Конкретно на файле:
На вкладке "Лист1" имеется столбец В2:В11.
Во вкладке "Лист2" в строке D2:H2 нужно выбрать и вывести значения столбца В2:В11, с условием что вариация цены, соответствующей этим ячейкам, рассчитываемая в ячейке D14, была не больше вариации, указанной в ячейке Q8. Аналогично с разницей между объектами (чтобы разница цен, указанная в ячейке D16, была не больше разницы, указанной в ячейке Q9).
Автор - Dubdubom
Дата добавления - 31.07.2019 в 13:51
Pelena Дата: Среда, 31.07.2019, 20:53 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19174
Репутация: 4413 ±
Замечаний: ±

Excel 365 & Mac Excel
Здравствуйте.
Не совсем то, что Вы хотели. Выводятся ближайшие варианты для каждого объекта и соответствующие значения для сравнения
К сообщению приложен файл: 8338257.xls (50.5 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Не совсем то, что Вы хотели. Выводятся ближайшие варианты для каждого объекта и соответствующие значения для сравнения
Автор - Pelena
Дата добавления - 31.07.2019 в 20:53
Dubdubom Дата: Четверг, 01.08.2019, 08:31 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Немного не то, но уже что то)) попытаюсь приспособить эти вычисления к своим расчетам. Большое спасибо!
 
Ответить
СообщениеНемного не то, но уже что то)) попытаюсь приспособить эти вычисления к своим расчетам. Большое спасибо!
Автор - Dubdubom
Дата добавления - 01.08.2019 в 08:31
Светлый Дата: Четверг, 01.08.2019, 11:43 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Вот ещё вариант.
Первое значение выбирается из списка случайным образом, последующие - наиболее близкие к среднему из предыдущих и неповторяющихся.
Левые зелёные ограничены коротким списком, правые полным списком.
Промежуточные вариация и разница для двух, трёх, четырёх подкрашиваются, если удовлетворяют ограничениям.
*Нижние зелёные сделал наиболее близкие к первому значению.
Файл перезалил.
К сообщению приложен файл: 5880941-1.xls (36.0 Kb)

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

Сообщение отредактировал Светлый - Четверг, 01.08.2019, 12:13
 
Ответить
СообщениеВот ещё вариант.
Первое значение выбирается из списка случайным образом, последующие - наиболее близкие к среднему из предыдущих и неповторяющихся.
Левые зелёные ограничены коротким списком, правые полным списком.
Промежуточные вариация и разница для двух, трёх, четырёх подкрашиваются, если удовлетворяют ограничениям.
*Нижние зелёные сделал наиболее близкие к первому значению.
Файл перезалил.
Автор - Светлый
Дата добавления - 01.08.2019 в 11:43
Dubdubom Дата: Четверг, 01.08.2019, 14:35 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Добрый день! Уже ближе. Постараюсь его переделать вод свои запросы. Спасибо!
 
Ответить
СообщениеДобрый день! Уже ближе. Постараюсь его переделать вод свои запросы. Спасибо!
Автор - Dubdubom
Дата добавления - 01.08.2019 в 14:35
Светлый Дата: Пятница, 02.08.2019, 06:44 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Первое значение случайное, каждое последующее добавляется с минимальной вариацией с предыдущими. Без повторений.
К сообщению приложен файл: 5880941-2.xls (31.5 Kb)

Программировать проще, чем писать стихи.
 
Ответить
СообщениеПервое значение случайное, каждое последующее добавляется с минимальной вариацией с предыдущими. Без повторений.
Автор - Светлый
Дата добавления - 02.08.2019 в 06:44
Светлый Дата: Понедельник, 05.08.2019, 13:12 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Задачка спать не давала.
Считает варианты для трёх, четырёх и пяти элементов с минимальными вариациями.
Для пяти массивная формула:
Код
=СМЕЩ(Лист1!B2;ОСТАТ(ОТБР(ПОИСКПОЗ(МИН(ЕСЛИ(МУМНОЖ(Ч(ОСТАТ(ОТБР(СТРОКА(ДВССЫЛ("1:"&J1^5))/J1^{0;0;0;0;1;1;1;2;2;3});J1)=ОСТАТ(ОТБР(СТРОКА(ДВССЫЛ("1:"&J1^5))/J1^{1;2;3;4;2;3;4;3;4;4});J1));{1:1:1:1:1:1:1:1:1:1})=0;МУМНОЖ((Ч(СМЕЩ(Лист1!C2;ОСТАТ(ОТБР(СТРОКА(ДВССЫЛ("1:"&J1^5))/J1^(СТОЛБЕЦ(A:E)-1));J1);))-МУМНОЖ(Ч(СМЕЩ(Лист1!C2;ОСТАТ(ОТБР(СТРОКА(ДВССЫЛ("1:"&J1^5))/J1^(СТОЛБЕЦ(A:E)-1));J1);));{1:1:1:1:1})/5)^2;{1:1:1:1:1})^0,5/МУМНОЖ(Ч(СМЕЩ(Лист1!C2;ОСТАТ(ОТБР(СТРОКА(ДВССЫЛ("1:"&J1^5))/J1^(СТОЛБЕЦ(A:E)-1));J1);));{1:1:1:1:1})));ЕСЛИ(МУМНОЖ(Ч(ОСТАТ(ОТБР(СТРОКА(ДВССЫЛ("1:"&J1^5))/J1^{0;0;0;0;1;1;1;2;2;3});J1)=ОСТАТ(ОТБР(СТРОКА(ДВССЫЛ("1:"&J1^5))/J1^{1;2;3;4;2;3;4;3;4;4});J1));{1:1:1:1:1:1:1:1:1:1})=0;МУМНОЖ((Ч(СМЕЩ(Лист1!C2;ОСТАТ(ОТБР(СТРОКА(ДВССЫЛ("1:"&J1^5))/J1^(СТОЛБЕЦ(A:E)-1));J1);))-МУМНОЖ(Ч(СМЕЩ(Лист1!C2;ОСТАТ(ОТБР(СТРОКА(ДВССЫЛ("1:"&J1^5))/J1^(СТОЛБЕЦ(A:E)-1));J1);));{1:1:1:1:1})/5)^2;{1:1:1:1:1})^0,5/МУМНОЖ(Ч(СМЕЩ(Лист1!C2;ОСТАТ(ОТБР(СТРОКА(ДВССЫЛ("1:"&J1^5))/J1^(СТОЛБЕЦ(A:E)-1));J1);));{1:1:1:1:1}));)/J1^(СТОЛБЕЦ(A:E)-1));J1);)
К сообщению приложен файл: 5880941-3.xlsx (14.9 Kb)

Программировать проще, чем писать стихи.
 
Ответить
СообщениеЗадачка спать не давала.
Считает варианты для трёх, четырёх и пяти элементов с минимальными вариациями.
Для пяти массивная формула:
Код
=СМЕЩ(Лист1!B2;ОСТАТ(ОТБР(ПОИСКПОЗ(МИН(ЕСЛИ(МУМНОЖ(Ч(ОСТАТ(ОТБР(СТРОКА(ДВССЫЛ("1:"&J1^5))/J1^{0;0;0;0;1;1;1;2;2;3});J1)=ОСТАТ(ОТБР(СТРОКА(ДВССЫЛ("1:"&J1^5))/J1^{1;2;3;4;2;3;4;3;4;4});J1));{1:1:1:1:1:1:1:1:1:1})=0;МУМНОЖ((Ч(СМЕЩ(Лист1!C2;ОСТАТ(ОТБР(СТРОКА(ДВССЫЛ("1:"&J1^5))/J1^(СТОЛБЕЦ(A:E)-1));J1);))-МУМНОЖ(Ч(СМЕЩ(Лист1!C2;ОСТАТ(ОТБР(СТРОКА(ДВССЫЛ("1:"&J1^5))/J1^(СТОЛБЕЦ(A:E)-1));J1);));{1:1:1:1:1})/5)^2;{1:1:1:1:1})^0,5/МУМНОЖ(Ч(СМЕЩ(Лист1!C2;ОСТАТ(ОТБР(СТРОКА(ДВССЫЛ("1:"&J1^5))/J1^(СТОЛБЕЦ(A:E)-1));J1);));{1:1:1:1:1})));ЕСЛИ(МУМНОЖ(Ч(ОСТАТ(ОТБР(СТРОКА(ДВССЫЛ("1:"&J1^5))/J1^{0;0;0;0;1;1;1;2;2;3});J1)=ОСТАТ(ОТБР(СТРОКА(ДВССЫЛ("1:"&J1^5))/J1^{1;2;3;4;2;3;4;3;4;4});J1));{1:1:1:1:1:1:1:1:1:1})=0;МУМНОЖ((Ч(СМЕЩ(Лист1!C2;ОСТАТ(ОТБР(СТРОКА(ДВССЫЛ("1:"&J1^5))/J1^(СТОЛБЕЦ(A:E)-1));J1);))-МУМНОЖ(Ч(СМЕЩ(Лист1!C2;ОСТАТ(ОТБР(СТРОКА(ДВССЫЛ("1:"&J1^5))/J1^(СТОЛБЕЦ(A:E)-1));J1);));{1:1:1:1:1})/5)^2;{1:1:1:1:1})^0,5/МУМНОЖ(Ч(СМЕЩ(Лист1!C2;ОСТАТ(ОТБР(СТРОКА(ДВССЫЛ("1:"&J1^5))/J1^(СТОЛБЕЦ(A:E)-1));J1);));{1:1:1:1:1}));)/J1^(СТОЛБЕЦ(A:E)-1));J1);)
Автор - Светлый
Дата добавления - 05.08.2019 в 13:12
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выбор ячеек, вариация значений которых попадает в диапазон (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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