У меня есть 78 кубиков различной массы, (таблица из двух столбцов с номером кубика и его массой). Мне нужно распределить кубики в 13 кластеров, так что бы разница между массами кластеров была наиболее минимальна (то есть получить 13 таблиц, в каждой из которых есть номер и масса кубика).
У меня есть 78 кубиков различной массы, (таблица из двух столбцов с номером кубика и его массой). Мне нужно распределить кубики в 13 кластеров, так что бы разница между массами кластеров была наиболее минимальна (то есть получить 13 таблиц, в каждой из которых есть номер и масса кубика).w2den777
w2den777, если это Готовое решение, то приложите файл с решением. Если это вопрос, то перенесу тему в раздел ВОПРОСЫ ПО EXCEL, но думаю, что файл всё равно потребуется.
w2den777, если это Готовое решение, то приложите файл с решением. Если это вопрос, то перенесу тему в раздел ВОПРОСЫ ПО EXCEL, но думаю, что файл всё равно потребуется.Pelena
"Черт возьми, Холмс! Но как??!!" Ю-money 41001765434816
Как вариант не совсем оптимальной, но близкой к оптимальной линейной группировки - сортировка по убыванию и расположение списка змейкой. Если необходимо ещё более оптимально сгруппировать, можно попытаться вручную менять элементы в группах, сумма которых сильно отличается от среднего значения в разные стороны.
Как вариант не совсем оптимальной, но близкой к оптимальной линейной группировки - сортировка по убыванию и расположение списка змейкой. Если необходимо ещё более оптимально сгруппировать, можно попытаться вручную менять элементы в группах, сумма которых сильно отличается от среднего значения в разные стороны.Светлый
Если формульный подход даёт недостаточно оптимальное решение, то нужно использовать макросы (в зависимости от исходных данных могут быть разные алгоритмы) вариант решения по случайным данным формулами (предложенными Светлым) и найденное более точное решение макросами
Если формульный подход даёт недостаточно оптимальное решение, то нужно использовать макросы (в зависимости от исходных данных могут быть разные алгоритмы) вариант решения по случайным данным формулами (предложенными Светлым) и найденное более точное решение макросамиMCH
Принцип работы жадного алгоритма реализованного в 10 сообщении: Сортируем массив с числами от большего к меньшему Заполняем вначале первый элемент во всех строках последовательно наибольшими значениями, затем второй элемент каждой строки, при этом наибольшее значение помещаем туда, где накоплена наименьшая сумма, аналогично для 3го - 6го элемента. Данный подход полностью повторяет размещение первого и второго элемента, также, как в варианте распределения змейкой, далее элементы распределяются по другому. Не скажу, что данный вариант лучше, чем распределение змейкой (на случайных данных)
Хотелось бы все таки увидеть реальные Ваши данные, т.к. макросом можно распределить числа существенно лучше.
Принцип работы жадного алгоритма реализованного в 10 сообщении: Сортируем массив с числами от большего к меньшему Заполняем вначале первый элемент во всех строках последовательно наибольшими значениями, затем второй элемент каждой строки, при этом наибольшее значение помещаем туда, где накоплена наименьшая сумма, аналогично для 3го - 6го элемента. Данный подход полностью повторяет размещение первого и второго элемента, также, как в варианте распределения змейкой, далее элементы распределяются по другому. Не скажу, что данный вариант лучше, чем распределение змейкой (на случайных данных)
Хотелось бы все таки увидеть реальные Ваши данные, т.к. макросом можно распределить числа существенно лучше.MCH
макросом можно распределить числа существенно лучше
Надеюсь получилось ... [vba]
Код
Option Explicit
Dim z, iRow, iCol, arr(), i, Номер
Sub ГазонЗасеян_РавноМерно() ' http://www.excelworld.ru/forum/2-36648-1 'распределить 78 кубикjd в 13 кластеров, так что бы разница между _ массами кластеров была наиболее минимальна With ActiveSheet Range(.Cells(2, 4), .Cells(14, 15)).ClearContents z = .Cells(2, 1).CurrentRegion.Sort(.Cells(2, 2), xlAscending, Header:=xlYes) arr = .[a1].CurrentRegion.Value For iRow = 2 To 14 For iCol = 4 To 14 Step 2 If iRow Mod 2 = 0 Then .Cells(iRow, iCol + 1) = МаксВес Else .Cells(iRow, iCol + 1) = МинВес End If .Cells(iRow, iCol) = Номер Next Next End With End Sub
Private Function МаксВес() МаксВес = 0 'пусть нет отрицательных чисел _ и вес отсортирован по возрастанию For i = UBound(arr) To LBound(arr) + 1 Step -1 If arr(i, 2) <> "" Then МаксВес = arr(i, 2) arr(i, 2) = "" Номер = arr(i, 1) Exit For End If Next End Function
Private Function МинВес() МинВес = 0 'пусть нет отрицательных чисел _ и вес отсортирован по возрастанию For i = LBound(arr) + 1 To UBound(arr) - 1 If arr(i, 2) <> "" Then МинВес = arr(i, 2) arr(i, 2) = "" Номер = arr(i, 1) Exit For End If Next End Function
макросом можно распределить числа существенно лучше
Надеюсь получилось ... [vba]
Код
Option Explicit
Dim z, iRow, iCol, arr(), i, Номер
Sub ГазонЗасеян_РавноМерно() ' http://www.excelworld.ru/forum/2-36648-1 'распределить 78 кубикjd в 13 кластеров, так что бы разница между _ массами кластеров была наиболее минимальна With ActiveSheet Range(.Cells(2, 4), .Cells(14, 15)).ClearContents z = .Cells(2, 1).CurrentRegion.Sort(.Cells(2, 2), xlAscending, Header:=xlYes) arr = .[a1].CurrentRegion.Value For iRow = 2 To 14 For iCol = 4 To 14 Step 2 If iRow Mod 2 = 0 Then .Cells(iRow, iCol + 1) = МаксВес Else .Cells(iRow, iCol + 1) = МинВес End If .Cells(iRow, iCol) = Номер Next Next End With End Sub
Private Function МаксВес() МаксВес = 0 'пусть нет отрицательных чисел _ и вес отсортирован по возрастанию For i = UBound(arr) To LBound(arr) + 1 Step -1 If arr(i, 2) <> "" Then МаксВес = arr(i, 2) arr(i, 2) = "" Номер = arr(i, 1) Exit For End If Next End Function
Private Function МинВес() МинВес = 0 'пусть нет отрицательных чисел _ и вес отсортирован по возрастанию For i = LBound(arr) + 1 To UBound(arr) - 1 If arr(i, 2) <> "" Then МинВес = arr(i, 2) arr(i, 2) = "" Номер = arr(i, 1) Exit For End If Next End Function
Задача не давала мне покоя и я её все-таки решил формулами. Правда без итераций не обошлось и формулы не получились полностью протягиваемыми. Сильно оптимизировать не стал.
Задача не давала мне покоя и я её все-таки решил формулами. Правда без итераций не обошлось и формулы не получились полностью протягиваемыми. Сильно оптимизировать не стал.Светлый
Приветствую! Еще раз благодарю Вас за помощь! Вставил свои данные, но gruppy4.xls не работает. Пока что минимальная дисперсия которую удалось получить составляет около 180 из второго алгоритма в gruppy3.xls. Прикладываю свои данные. up
Понравился макрос InExSu он шикарно работает и главное оперирует номерами кубиков, это облегчает сборку. Разница между самой большой и самой маленькой матрицей очень маленькая, считаю данный просто прекрасным!
Практический смысл задачи не менее интересен чем ее решение, задача собрать максимально эффективную батарею для электровелосипеда. Дело в том, что ячейки нужно параллелеить так, что бы разница между 13 сборками из 6 запараллеленых элементов была минимальна, так как контроллер отключает всю батарею когда одна из 13 сборок просаживается ниже 3 вольт. "Взвешивание" батареек, то есть замер их емкости долгий процесс и был закончен только сегодня.
Приветствую! Еще раз благодарю Вас за помощь! Вставил свои данные, но gruppy4.xls не работает. Пока что минимальная дисперсия которую удалось получить составляет около 180 из второго алгоритма в gruppy3.xls. Прикладываю свои данные. up
Понравился макрос InExSu он шикарно работает и главное оперирует номерами кубиков, это облегчает сборку. Разница между самой большой и самой маленькой матрицей очень маленькая, считаю данный просто прекрасным!
Практический смысл задачи не менее интересен чем ее решение, задача собрать максимально эффективную батарею для электровелосипеда. Дело в том, что ячейки нужно параллелеить так, что бы разница между 13 сборками из 6 запараллеленых элементов была минимальна, так как контроллер отключает всю батарею когда одна из 13 сборок просаживается ниже 3 вольт. "Взвешивание" батареек, то есть замер их емкости долгий процесс и был закончен только сегодня.w2den777
С реальными данными вообще махом посчитала. Дисперсия=0,25640576 в gruppy4.xls Проблема в том, что есть одинаковые значения. Надо к каждому значению прибавить номер строки, делённый на 1000000
Код
=B2+A2%%%
В этом файле формулы немного упростил. Если результата сразу не выдаст, можно продолжить расчёт (F9) или сделать сброс и снова расчёт в жёлтой ячейке. Для удобства дописал номера элементов.
С реальными данными вообще махом посчитала. Дисперсия=0,25640576 в gruppy4.xls Проблема в том, что есть одинаковые значения. Надо к каждому значению прибавить номер строки, делённый на 1000000
Код
=B2+A2%%%
В этом файле формулы немного упростил. Если результата сразу не выдаст, можно продолжить расчёт (F9) или сделать сброс и снова расчёт в жёлтой ячейке. Для удобства дописал номера элементов.Светлый