Подбор слагаемых дляй определенной суммы.

Дата: Воскресенье, 12.10.2014, 14:06 | Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 107

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Здравствуйте. Помогите, пожалуйста, со следующей задачей.В диапазоне (А2:А10) - номера деталей. В диапазоне (В2:В10)- соответственно площадь каждой детали. Необходимо подсчитать макросом количество деталей по каждому виду (диапазон С2:С10), чтоб получилась заданная общая сумма площадей деталей в ячейки D2.Одну деталь можно брать несколько раз.

К сообщению приложен файл: 7792661.xls (22.5 Kb)

Дата: Воскресенье, 12.10.2014, 14:12 | Сообщение № 2

Группа: Авторы

Ранг: Ветеран

Сообщений: 903

Репутация: 290 ±

Замечаний: 0% ±

Excel 2013

Amator, здравствуйте.

Я Вас правильно понял, Вы хотите чтобы Excel подбирал количество деталей так, чтобы их общая площадь стала равна площади в ячейке D2?

Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279

Дата: Воскресенье, 12.10.2014, 14:14 | Сообщение № 3

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 107

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Rioran, да ,именно так.

Дата: Воскресенье, 12.10.2014, 14:39 | Сообщение № 4

Группа: Авторы

Ранг: Ветеран

Сообщений: 903

Репутация: 290 ±

Замечаний: 0% ±

Excel 2013

Amator, предлагаю решать эту задачу с помощью уже встроенного в Excel механизма "Поиск решения". Я вижу у Вас 2007-й Excel и Вы можете это запустить. Где найти - посмотрите в интернете запросом "excel поиск решения 2007".

Во вложении пример, как я решал ту же задачу с помощью этого механизма.

В ячейке D3 пишем формулу для подсчёта результирующей площади по всем деталям:

Код

=СУММПРОИЗВ(B2:B10;C2:C10)

Запускаем "Поиск решения" и далее по списку:

1) Оптимизировать целевую фукнцию $D$3
2) До максимум
3) Изменяя ячейки переменных $C$2:$C$10
4) В соответствии с ограничением: $C$2:$C$10 >= 0 плюс $C$2:$C$10 целое
5) И ещё: $D$3 <= $D$2 (добавляются через кнопку "добавить")
6) Поиск решения нелинейных задач методом ОПГ
7) Нажать "Найти решение"

К сообщению приложен файл: Rio_Solver.xls (32.5 Kb)

Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279

Дата: Воскресенье, 12.10.2014, 15:00 | Сообщение № 5

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 107

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Rioran, спасибо, разобрался. А можна как-то макросом?

Дата: Воскресенье, 12.10.2014, 15:19 | Сообщение № 6

Группа: Авторы

Ранг: Ветеран

Сообщений: 903

Репутация: 290 ±

Замечаний: 0% ±

Excel 2013

Amator, можно. По сути "Поиск Решения" это тоже макрос.

Вы можете описать логику или алгоритм, по которому макрос будет подбирать количество деталей?

Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279

Дата: Воскресенье, 12.10.2014, 16:34 | Сообщение № 7

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 107

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Rioran, можно по принципу "Поиск решения".

MCH

Дата: Воскресенье, 12.10.2014, 21:42 | Сообщение № 8

Группа: Админы

Ранг: Старожил

Сообщений: 2003

Репутация: 751 ±

Замечаний: ±

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

Есть вопрос, если решений несколько, какое выбирать?

Несколько примеров решений (на самом деле их существенно больше):
=0,64*6+0,58*2
=0,64*4+0,55+0,21*9
=0,64*2+0,55*6+0,21*2
=0,58+0,55+0,48*3+0,24*4+0,21*7
=0,58*6+0,55*2+0,21*2
=0,58*5+0,21*10
=0,58*2+0,55*3+0,24*3+0,21*7
=0,55+0,44*5+0,24*5+0,21*5
=0,55+0,38*5+0,33*7+0,24
=0,55*3+0,48*3+0,44+0,33+0,24*3+0,21*2
=0,48*7+0,44+0,24*5
=0,44+0,38*6+0,33*4+0,24*4
=0,44+0,38*12
=0,44*7+0,24*8
=0,44*5+0,38*2+0,33*4+0,24*3
=0,38+0,33*14

MCH

Сообщение отредактировал MCH - Воскресенье, 12.10.2014, 21:52

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

Есть вопрос, если решений несколько, какое выбирать?

Несколько примеров решений (на самом деле их существенно больше):
=0,64*6+0,58*2
=0,64*4+0,55+0,21*9
=0,64*2+0,55*6+0,21*2
=0,58+0,55+0,48*3+0,24*4+0,21*7
=0,58*6+0,55*2+0,21*2
=0,58*5+0,21*10
=0,58*2+0,55*3+0,24*3+0,21*7
=0,55+0,44*5+0,24*5+0,21*5
=0,55+0,38*5+0,33*7+0,24
=0,55*3+0,48*3+0,44+0,33+0,24*3+0,21*2
=0,48*7+0,44+0,24*5
=0,44+0,38*6+0,33*4+0,24*4
=0,44+0,38*12
=0,44*7+0,24*8
=0,44*5+0,38*2+0,33*4+0,24*3
=0,38+0,33*14

Автор - MCH
Дата добавления - 12.10.2014 в 21:42

Дата: Воскресенье, 12.10.2014, 22:03 | Сообщение № 9

Группа: Авторы

Ранг: Ветеран

Сообщений: 903

Репутация: 290 ±

Замечаний: 0% ±

Excel 2013

Цитата MCH, 12.10.2014 в 21:42, в сообщении № 8

Посмотрел файл Романа, число 5 там не получается

Всё верно, там НЕ целочисленное решение.

Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279

alex1248

Дата: Воскресенье, 12.10.2014, 22:07 | Сообщение № 10

Группа: Проверенные

Ранг: Обитатель

Сообщений: 384

Репутация: 71 ±

Замечаний: 0% ±

Excel 2007, 2010

А если поставить условие (в варианте Романа), чтобы в указанной ячейке было ровно 5, то находится подходящий точный вариант.
Видимо, есть в поиске решения проблема, что при выборе макс/мин значений выдается не оптимальный вариант.

skype alex12481632
Qiwi +79276708519

Сообщение отредактировал alex1248 - Воскресенье, 12.10.2014, 22:17

Дата: Воскресенье, 12.10.2014, 22:24 | Сообщение № 11

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 107

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

MCH, здравствуйте. Вместо 5 может быть и 5,6 и другое. Выбирать любое решение с минимальной погрешностью.

Дата: Понедельник, 13.10.2014, 15:16 | Сообщение № 12

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 107

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Здравствуйте, уважаемые. Помогите ,пожалуйста, с макросом.

MCH

Дата: Воскресенье, 19.10.2014, 09:46 | Сообщение № 13

Группа: Админы

Ранг: Старожил

Сообщений: 2003

Репутация: 751 ±

Замечаний: ±

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

Решение "в лоб" без какой либо оптимизации и универсальности (просто так написать значительно легче и быстрее):
[vba]

Код

Sub www()
     Dim s&, i1&, i2&, i3&, i4&, i5&, i6&, i7&, i8&, i9&, m&, k&, a, txt$
     a = Array(21, 24, 33, 38, 44, 48, 55, 58, 64)
     s = 500
     For i1 = 0 To s \ a(0)
     For i2 = 0 To (s - i1 * a(0)) \ a(1)
     For i3 = 0 To (s - i1 * a(0) - i2 * a(1)) \ a(2)
     For i4 = 0 To (s - i1 * a(0) - i2 * a(1) - i3 * a(2)) \ a(3)
     For i5 = 0 To (s - i1 * a(0) - i2 * a(1) - i3 * a(2) - i4 * a(3)) \ a(4)
     For i6 = 0 To (s - i1 * a(0) - i2 * a(1) - i3 * a(2) - i4 * a(3) - i5 * a(4)) \ a(5)
     For i7 = 0 To (s - i1 * a(0) - i2 * a(1) - i3 * a(2) - i4 * a(3) - i5 * a(4) - i6 * a(5)) \ a(6)
     For i8 = 0 To (s - i1 * a(0) - i2 * a(1) - i3 * a(2) - i4 * a(3) - i5 * a(4) - i6 * a(5) - i7 * a(6)) \ a(7)
         m = s - i1 * a(0) - i2 * a(1) - i3 * a(2) - i4 * a(3) - i5 * a(4) - i6 * a(5) - i7 * a(6) - i8 * a(7)
         If m Mod a(8) = 0 Then
             i9 = m \ a(8)
             k = k + 1
             txt = IIf(i1, "+" & a(0) / 100 & IIf(i1 > 1, "*" & i1, ""), "") _
                 & IIf(i2, "+" & a(1) / 100 & IIf(i2 > 1, "*" & i2, ""), "") _
                 & IIf(i3, "+" & a(2) / 100 & IIf(i3 > 1, "*" & i3, ""), "") _
                 & IIf(i4, "+" & a(3) / 100 & IIf(i4 > 1, "*" & i4, ""), "") _
                 & IIf(i5, "+" & a(4) / 100 & IIf(i5 > 1, "*" & i5, ""), "") _
                 & IIf(i6, "+" & a(5) / 100 & IIf(i6 > 1, "*" & i6, ""), "") _
                 & IIf(i7, "+" & a(6) / 100 & IIf(i7 > 1, "*" & i7, ""), "") _
                 & IIf(i8, "+" & a(7) / 100 & IIf(i8 > 1, "*" & i8, ""), "") _
                 & IIf(i9, "+" & a(8) / 100 & IIf(i9 > 1, "*" & i9, ""), "")
             Cells(k, 1) = "'=" & Mid(txt, 2)
         End If
     Next i8, i7, i6, i5, i4, i3, i2, i1
     'Debug.Print k
End Sub

[/vba]

если нужно считать не 5, а 5,6 то вместо строки s = 500 напишите s = 560
Если слагаемых будет не 9 а больше или меньше и каждый раз из количество будет меняться, то лучше задачу решать другим способом

Код