Всем добрый день! Прошу опытных экселистов о помощи в решении задачи. Поиск по форуму дал результаты подбора слагаемых с помощью SOLVERа и VBA, что, к сожалению, в моём случае не совсем подходит. Необходимо произвести подбор слагаемых из массива данных под заданную сумму (готовый результат) и, в случае, если слагаемое из массива используется для нахождения заданной суммы - вернуть в смежный массив какой-либо индикатор использования данного слагаемого. Решение с помощью надстройки (Поиск решения) не подходит, так как значения в массиве I6:I9 расчётные и меняются каждый час, соответственно Поиском решения придётся пользоваться каждый час вручную, а хотелось максимально автоматизировать. Решение с помощью VBA, предполагаю, тоже не подойдёт, так как, во-первых, мой уровень познания Excel пока не дотягивает до понимания VBA , во-вторых, во вложении представлен пример с массивом из 4 значений, в полной форме всего массивов 36, количество значений разнится от 2 до 11 - это какой макрос там необходимо написать?
Всем добрый день! Прошу опытных экселистов о помощи в решении задачи. Поиск по форуму дал результаты подбора слагаемых с помощью SOLVERа и VBA, что, к сожалению, в моём случае не совсем подходит. Необходимо произвести подбор слагаемых из массива данных под заданную сумму (готовый результат) и, в случае, если слагаемое из массива используется для нахождения заданной суммы - вернуть в смежный массив какой-либо индикатор использования данного слагаемого. Решение с помощью надстройки (Поиск решения) не подходит, так как значения в массиве I6:I9 расчётные и меняются каждый час, соответственно Поиском решения придётся пользоваться каждый час вручную, а хотелось максимально автоматизировать. Решение с помощью VBA, предполагаю, тоже не подойдёт, так как, во-первых, мой уровень познания Excel пока не дотягивает до понимания VBA , во-вторых, во вложении представлен пример с массивом из 4 значений, в полной форме всего массивов 36, количество значений разнится от 2 до 11 - это какой макрос там необходимо написать? АйбеливАйкенфлаев
Приложите пример с реальными данными, задачу можно решить на макросах, в предыдущем посте сделано только под 4 переменных
Данные во вложении.
Предложенное Вами решение уже можно применить, за что огромное Вам спасибо! Однако может быть всё-таки как-то можно реализовать данный перебор формулой (пускай и трёхэтажной)?
И дополнительно - в Вашем решении варианты подбираются абсолютно верные (с точки зрения математики). Однако есть пара нюансов, прошу прощения, что сразу не акцентировал на них внимание - есть дополнительный параметр "Среднее количество насосов в работе за месяц", количество подобранных слагаемых не должно быть больше значения данного параметра. Кроме того не требуется точного совпадения от результата подбираемых слагаемых и заданной суммы, допустима погрешность в 3-5% (в моём изначальном примере заданная сумма равняется 22054 (ячейка J20), слагаемые подобраны на сумму 22070 (СУММ(L20:L23))).
Приложите пример с реальными данными, задачу можно решить на макросах, в предыдущем посте сделано только под 4 переменных
Данные во вложении.
Предложенное Вами решение уже можно применить, за что огромное Вам спасибо! Однако может быть всё-таки как-то можно реализовать данный перебор формулой (пускай и трёхэтажной)?
И дополнительно - в Вашем решении варианты подбираются абсолютно верные (с точки зрения математики). Однако есть пара нюансов, прошу прощения, что сразу не акцентировал на них внимание - есть дополнительный параметр "Среднее количество насосов в работе за месяц", количество подобранных слагаемых не должно быть больше значения данного параметра. Кроме того не требуется точного совпадения от результата подбираемых слагаемых и заданной суммы, допустима погрешность в 3-5% (в моём изначальном примере заданная сумма равняется 22054 (ячейка J20), слагаемые подобраны на сумму 22070 (СУММ(L20:L23))).АйбеливАйкенфлаев
Вариант решения с ограничением кол-ва различных слагаемых
Если в первом макросе хоть часть со скрипом смог понять, то со вторым совсем труба МСН, подскажите пожалуйста, как можно адаптировать первый макрос под <=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] И второй момент - массив из 4 значений (других) должен рассчитываться, однако что-то не так...
Вариант решения с ограничением кол-ва различных слагаемых
Если в первом макросе хоть часть со скрипом смог понять, то со вторым совсем труба МСН, подскажите пожалуйста, как можно адаптировать первый макрос под <=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] И второй момент - массив из 4 значений (других) должен рассчитываться, однако что-то не так...АйбеливАйкенфлаев
подскажите пожалуйста, как можно адаптировать первый макрос под <=11 значений?
Если делать полный перебор (11 вложенных циклов), то расчет может занять очень много времени, к тому же в этом алгоритме нет ограничения на количество различных слагаемых.
подскажите пожалуйста, как можно адаптировать первый макрос под <=11 значений?
Если делать полный перебор (11 вложенных циклов), то расчет может занять очень много времени, к тому же в этом алгоритме нет ограничения на количество различных слагаемых.
К сожалению результат необходим иной. Задача про насосы, но давайте её переделаем, например, на продажи: Дано: 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 с учётом уже работающих трёх касс - возможно ли это?
К сожалению результат необходим иной. Задача про насосы, но давайте её переделаем, например, на продажи: Дано: 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 с учётом уже работающих трёх касс - возможно ли это?АйбеливАйкенфлаев
Внес возможность ограничивать количество это единственный недостаток?
Перенёс макрос в свою форму - всё работает замечательно! МСН, спасибо огромное! Бьюсь над этой задачкой уже довольно давно, а Вы её решили, можно сказать, "одной левой" - мотивирует конечно
Внес возможность ограничивать количество это единственный недостаток?
Перенёс макрос в свою форму - всё работает замечательно! МСН, спасибо огромное! Бьюсь над этой задачкой уже довольно давно, а Вы её решили, можно сказать, "одной левой" - мотивирует конечно АйбеливАйкенфлаев
Сообщение отредактировал АйбеливАйкенфлаев - Воскресенье, 01.12.2019, 18:28
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
, во-вторых, во вложении представлен пример с массивом из 4 значений, в полной форме всего массивов 36, количество значений разнится от 2 до 11 - это какой макрос там необходимо написать? 
Бьюсь над этой задачкой уже довольно давно, а Вы её решили, можно сказать, "одной левой" - мотивирует конечно 
