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

Вход

Регистрация

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

 

= Мир MS Excel/Подбор слагаемых - Мир MS Excel

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

Excel 2016
Всем добрый день!
Прошу опытных экселистов о помощи в решении задачи.
Поиск по форуму дал результаты подбора слагаемых с помощью SOLVERа и VBA, что, к сожалению, в моём случае не совсем подходит.
Необходимо произвести подбор слагаемых из массива данных под заданную сумму (готовый результат) и, в случае, если слагаемое из массива используется для нахождения заданной суммы - вернуть в смежный массив какой-либо индикатор использования данного слагаемого.
Решение с помощью надстройки (Поиск решения) не подходит, так как значения в массиве I6:I9 расчётные и меняются каждый час, соответственно Поиском решения придётся пользоваться каждый час вручную, а хотелось максимально автоматизировать. Решение с помощью VBA, предполагаю, тоже не подойдёт, так как, во-первых, мой уровень познания Excel пока не дотягивает до понимания VBA :) , во-вторых, во вложении представлен пример с массивом из 4 значений, в полной форме всего массивов 36, количество значений разнится от 2 до 11 - это какой макрос там необходимо написать? :)
К сообщению приложен файл: 4819804.xls(69.0 Kb)
 
Ответить
СообщениеВсем добрый день!
Прошу опытных экселистов о помощи в решении задачи.
Поиск по форуму дал результаты подбора слагаемых с помощью SOLVERа и VBA, что, к сожалению, в моём случае не совсем подходит.
Необходимо произвести подбор слагаемых из массива данных под заданную сумму (готовый результат) и, в случае, если слагаемое из массива используется для нахождения заданной суммы - вернуть в смежный массив какой-либо индикатор использования данного слагаемого.
Решение с помощью надстройки (Поиск решения) не подходит, так как значения в массиве I6:I9 расчётные и меняются каждый час, соответственно Поиском решения придётся пользоваться каждый час вручную, а хотелось максимально автоматизировать. Решение с помощью VBA, предполагаю, тоже не подойдёт, так как, во-первых, мой уровень познания Excel пока не дотягивает до понимания VBA :) , во-вторых, во вложении представлен пример с массивом из 4 значений, в полной форме всего массивов 36, количество значений разнится от 2 до 11 - это какой макрос там необходимо написать? :)

Автор - АйбеливАйкенфлаев
Дата добавления - 20.11.2019 в 14:25
MCH Дата: Среда, 20.11.2019, 16:27 | Сообщение № 2
Группа: Админы
Ранг: Старожил
Сообщений: 1940
Репутация: 722 ±
Замечаний: ±

проще всего задачу решить простым перебором
К сообщению приложен файл: _4819804.xls(42.5 Kb)
 
Ответить
Сообщениепроще всего задачу решить простым перебором

Автор - MCH
Дата добавления - 20.11.2019 в 16:27
MCH Дата: Среда, 20.11.2019, 16:51 | Сообщение № 3
Группа: Админы
Ранг: Старожил
Сообщений: 1940
Репутация: 722 ±
Замечаний: ±

Цитата АйбеливАйкенфлаев, 20.11.2019 в 14:25, в сообщении № 1 ()
во вложении представлен пример с массивом из 4 значений, в полной форме всего массивов 36, количество значений разнится от 2 до 11

Приложите пример с реальными данными, задачу можно решить на макросах, в предыдущем посте сделано только под 4 переменных
 
Ответить
Сообщение
Цитата АйбеливАйкенфлаев, 20.11.2019 в 14:25, в сообщении № 1 ()
во вложении представлен пример с массивом из 4 значений, в полной форме всего массивов 36, количество значений разнится от 2 до 11

Приложите пример с реальными данными, задачу можно решить на макросах, в предыдущем посте сделано только под 4 переменных

Автор - MCH
Дата добавления - 20.11.2019 в 16:51
АйбеливАйкенфлаев Дата: Среда, 20.11.2019, 20:28 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Приложите пример с реальными данными, задачу можно решить на макросах, в предыдущем посте сделано только под 4 переменных


Данные во вложении.

Предложенное Вами решение уже можно применить, за что огромное Вам спасибо! Однако может быть всё-таки как-то можно реализовать данный перебор формулой (пускай и трёхэтажной)?

И дополнительно - в Вашем решении варианты подбираются абсолютно верные (с точки зрения математики). Однако есть пара нюансов, прошу прощения, что сразу не акцентировал на них внимание - есть дополнительный параметр "Среднее количество насосов в работе за месяц", количество подобранных слагаемых не должно быть больше значения данного параметра. Кроме того не требуется точного совпадения от результата подбираемых слагаемых и заданной суммы, допустима погрешность в 3-5% (в моём изначальном примере заданная сумма равняется 22054 (ячейка J20), слагаемые подобраны на сумму 22070 (СУММ(L20:L23))).
К сообщению приложен файл: 2874333.xls(89.5 Kb)
 
Ответить
Сообщение
Приложите пример с реальными данными, задачу можно решить на макросах, в предыдущем посте сделано только под 4 переменных


Данные во вложении.

Предложенное Вами решение уже можно применить, за что огромное Вам спасибо! Однако может быть всё-таки как-то можно реализовать данный перебор формулой (пускай и трёхэтажной)?

И дополнительно - в Вашем решении варианты подбираются абсолютно верные (с точки зрения математики). Однако есть пара нюансов, прошу прощения, что сразу не акцентировал на них внимание - есть дополнительный параметр "Среднее количество насосов в работе за месяц", количество подобранных слагаемых не должно быть больше значения данного параметра. Кроме того не требуется точного совпадения от результата подбираемых слагаемых и заданной суммы, допустима погрешность в 3-5% (в моём изначальном примере заданная сумма равняется 22054 (ячейка J20), слагаемые подобраны на сумму 22070 (СУММ(L20:L23))).

Автор - АйбеливАйкенфлаев
Дата добавления - 20.11.2019 в 20:28
АйбеливАйкенфлаев Дата: Пятница, 22.11.2019, 10:49 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Апну тему - вопрос всё ещё актуален.
 
Ответить
СообщениеАпну тему - вопрос всё ещё актуален.

Автор - АйбеливАйкенфлаев
Дата добавления - 22.11.2019 в 10:49
MCH Дата: Суббота, 23.11.2019, 19:06 | Сообщение № 6
Группа: Админы
Ранг: Старожил
Сообщений: 1940
Репутация: 722 ±
Замечаний: ±

Вариант решения с ограничением кол-ва различных слагаемых
К сообщению приложен файл: PodborSum.xlsm(58.1 Kb)
 
Ответить
СообщениеВариант решения с ограничением кол-ва различных слагаемых

Автор - MCH
Дата добавления - 23.11.2019 в 19:06
АйбеливАйкенфлаев Дата: Среда, 27.11.2019, 21:00 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Вариант решения с ограничением кол-ва различных слагаемых


Если в первом макросе хоть часть со скрипом смог понять, то со вторым совсем труба :)
МСН, подскажите пожалуйста, как можно адаптировать первый макрос под <=11 значений?
Хватило только на это, при чём не уверен что это правильно:
[vba]
Код
Dim i As Long, i1 As Long, i2 As Long, i3 As Long, i4 As Long, i5 As Long, i6 As Long, i7 As Long, i8 As Long, i9 As Long, i10 As Long, i11 As Long
Dim outArr(1 To 11, 1 To 1) As Long, n(1 To 11) As Double, x, s0 As Double, sm As Double
For Each x In rng
i = i + 1
If i > 11 Then Exit For
[/vba]
:D
И второй момент - массив из 4 значений (других) должен рассчитываться, однако что-то не так...
К сообщению приложен файл: 7412144.xlsm(62.3 Kb)


Сообщение отредактировал АйбеливАйкенфлаев - Четверг, 28.11.2019, 19:50
 
Ответить
Сообщение
Вариант решения с ограничением кол-ва различных слагаемых


Если в первом макросе хоть часть со скрипом смог понять, то со вторым совсем труба :)
МСН, подскажите пожалуйста, как можно адаптировать первый макрос под <=11 значений?
Хватило только на это, при чём не уверен что это правильно:
[vba]
Код
Dim i As Long, i1 As Long, i2 As Long, i3 As Long, i4 As Long, i5 As Long, i6 As Long, i7 As Long, i8 As Long, i9 As Long, i10 As Long, i11 As Long
Dim outArr(1 To 11, 1 To 1) As Long, n(1 To 11) As Double, x, s0 As Double, sm As Double
For Each x In rng
i = i + 1
If i > 11 Then Exit For
[/vba]
:D
И второй момент - массив из 4 значений (других) должен рассчитываться, однако что-то не так...

Автор - АйбеливАйкенфлаев
Дата добавления - 27.11.2019 в 21:00
Pelena Дата: Среда, 27.11.2019, 21:05 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 14862
Репутация: 3248 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
АйбеливАйкенфлаев, оформите код тегами с помощью кнопки # в режиме правки поста


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеАйбеливАйкенфлаев, оформите код тегами с помощью кнопки # в режиме правки поста

Автор - Pelena
Дата добавления - 27.11.2019 в 21:05
АйбеливАйкенфлаев Дата: Четверг, 28.11.2019, 19:54 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Pelena, оформил.
 
Ответить
СообщениеPelena, оформил.

Автор - АйбеливАйкенфлаев
Дата добавления - 28.11.2019 в 19:54
MCH Дата: Пятница, 29.11.2019, 08:12 | Сообщение № 10
Группа: Админы
Ранг: Старожил
Сообщений: 1940
Репутация: 722 ±
Замечаний: ±

Цитата АйбеливАйкенфлаев, 27.11.2019 в 21:00, в сообщении № 7 ()
подскажите пожалуйста, как можно адаптировать первый макрос под <=11 значений?

Если делать полный перебор (11 вложенных циклов), то расчет может занять очень много времени, к тому же в этом алгоритме нет ограничения на количество различных слагаемых.

Чем не устраивает результат из 6го поста?
 
Ответить
Сообщение
Цитата АйбеливАйкенфлаев, 27.11.2019 в 21:00, в сообщении № 7 ()
подскажите пожалуйста, как можно адаптировать первый макрос под <=11 значений?

Если делать полный перебор (11 вложенных циклов), то расчет может занять очень много времени, к тому же в этом алгоритме нет ограничения на количество различных слагаемых.

Чем не устраивает результат из 6го поста?

Автор - MCH
Дата добавления - 29.11.2019 в 08:12
АйбеливАйкенфлаев Дата: Суббота, 30.11.2019, 15:51 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Чем не устраивает результат из 6го поста?


К сожалению результат необходим иной.
Задача про насосы, но давайте её переделаем, например, на продажи:
Дано:
1) Несколько магазинов (всего 36)
1) От 2х до 11 кассовых аппаратов в каждом магазине
2) От 2х до 11 соответствующих средних чеков, на которые данные кассовые аппараты пробивают товары (например от 100 до 900 рублей/час)
3) Временной период, ограниченный 24 часами
4) План продаж в сутки для каждого магазина (например 40000 рублей/сут)
Необходимо найти:
1) Какие кассовые аппараты в каждом магазине должны работать для достижения плана продаж? (например, 1я, 4я, 6я и 10я)
2) Сколько часов в сутки данные кассовые аппараты должны работать? (например, 5 часов, 8 часов, 16 и 21 часа соответственно)

В Вашем решении из 6го поста получается, что в магазине №1 установлено 5 кассовых аппаратов, 1й должен отработать 47 часов/сутки, 2й - 1 час/сутки, 3й - 24 часа/сутки, а 4я и 5я кассы простаивают.

Предложенное Вами решение сообщении №2 практически идеально подходит для решения данной задачи, однако перебор почему-то работает только для 1го магазина (первых 4х установленных кассовых аппаратов), 2й магазин - 3 аппарата - ошибка, 3й магазин - 5 аппаратов - рассчитывается, но некорректно, и так далее.

Касательно перебора в 11 циклов - Вы правы, в 11 циклах нет необходимости, возможно ли ограничить до 8 циклов?
Т.е. из 11 кассовых аппаратов 3 постоянно в работе по 24 часа независимо от плана, необходимо подобрать работу остальных 8 с учётом уже работающих трёх касс - возможно ли это?
 
Ответить
Сообщение
Чем не устраивает результат из 6го поста?


К сожалению результат необходим иной.
Задача про насосы, но давайте её переделаем, например, на продажи:
Дано:
1) Несколько магазинов (всего 36)
1) От 2х до 11 кассовых аппаратов в каждом магазине
2) От 2х до 11 соответствующих средних чеков, на которые данные кассовые аппараты пробивают товары (например от 100 до 900 рублей/час)
3) Временной период, ограниченный 24 часами
4) План продаж в сутки для каждого магазина (например 40000 рублей/сут)
Необходимо найти:
1) Какие кассовые аппараты в каждом магазине должны работать для достижения плана продаж? (например, 1я, 4я, 6я и 10я)
2) Сколько часов в сутки данные кассовые аппараты должны работать? (например, 5 часов, 8 часов, 16 и 21 часа соответственно)

В Вашем решении из 6го поста получается, что в магазине №1 установлено 5 кассовых аппаратов, 1й должен отработать 47 часов/сутки, 2й - 1 час/сутки, 3й - 24 часа/сутки, а 4я и 5я кассы простаивают.

Предложенное Вами решение сообщении №2 практически идеально подходит для решения данной задачи, однако перебор почему-то работает только для 1го магазина (первых 4х установленных кассовых аппаратов), 2й магазин - 3 аппарата - ошибка, 3й магазин - 5 аппаратов - рассчитывается, но некорректно, и так далее.

Касательно перебора в 11 циклов - Вы правы, в 11 циклах нет необходимости, возможно ли ограничить до 8 циклов?
Т.е. из 11 кассовых аппаратов 3 постоянно в работе по 24 часа независимо от плана, необходимо подобрать работу остальных 8 с учётом уже работающих трёх касс - возможно ли это?

Автор - АйбеливАйкенфлаев
Дата добавления - 30.11.2019 в 15:51
MCH Дата: Суббота, 30.11.2019, 18:29 | Сообщение № 12
Группа: Админы
Ранг: Старожил
Сообщений: 1940
Репутация: 722 ±
Замечаний: ±

Цитата АйбеливАйкенфлаев, 30.11.2019 в 15:51, в сообщении № 11 ()
Временной период, ограниченный 24 часами

Внес возможность ограничивать количество
это единственный недостаток?
К сообщению приложен файл: PodborSum-1-.xlsm(60.6 Kb)
 
Ответить
Сообщение
Цитата АйбеливАйкенфлаев, 30.11.2019 в 15:51, в сообщении № 11 ()
Временной период, ограниченный 24 часами

Внес возможность ограничивать количество
это единственный недостаток?

Автор - MCH
Дата добавления - 30.11.2019 в 18:29
АйбеливАйкенфлаев Дата: Воскресенье, 01.12.2019, 18:28 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Внес возможность ограничивать количество
это единственный недостаток?


Перенёс макрос в свою форму - всё работает замечательно! МСН, спасибо огромное! ^_^ Бьюсь над этой задачкой уже довольно давно, а Вы её решили, можно сказать, "одной левой" - мотивирует конечно :)


Сообщение отредактировал АйбеливАйкенфлаев - Воскресенье, 01.12.2019, 18:28
 
Ответить
Сообщение
Внес возможность ограничивать количество
это единственный недостаток?


Перенёс макрос в свою форму - всё работает замечательно! МСН, спасибо огромное! ^_^ Бьюсь над этой задачкой уже довольно давно, а Вы её решили, можно сказать, "одной левой" - мотивирует конечно :)

Автор - АйбеливАйкенфлаев
Дата добавления - 01.12.2019 в 18:28
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подбор слагаемых (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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