Имеется множество чисел в столбце (вероятность нескольких столбцов). Числа 1 до 100 (вероятность повторения чисел), то есть несколько "1" или несколько "12" и т.д. . Нужно провести суммирование чисел до (примерно 110), с условием погрешности в меньшую сторону, лишь бы достигнуть результата "меньшее количество групп". При суммировании, повторение чисел возможно только в его количественном повторении относительно списка. Если при формировании групп, образуется остаток в виде нескольких чисел, то они отмечаются как остаток.
Заранее спасибо!
Имеется множество чисел в столбце (вероятность нескольких столбцов). Числа 1 до 100 (вероятность повторения чисел), то есть несколько "1" или несколько "12" и т.д. . Нужно провести суммирование чисел до (примерно 110), с условием погрешности в меньшую сторону, лишь бы достигнуть результата "меньшее количество групп". При суммировании, повторение чисел возможно только в его количественном повторении относительно списка. Если при формировании групп, образуется остаток в виде нескольких чисел, то они отмечаются как остаток.
Немного переделал и по дополнительному столбцу подобрал группы. Ограничение: В группе не более четырёх слагаемых. Для пяти не хватает ресурсов Excel. В конце списка подбор вручную остатков.
Немного переделал и по дополнительному столбцу подобрал группы. Ограничение: В группе не более четырёх слагаемых. Для пяти не хватает ресурсов Excel. В конце списка подбор вручную остатков.Светлый
For i = LBound(tempArr) To UBound(tempArr) 'сортируем полученный массив от меньшего к большему x = tempArr(i) For j = i To UBound(tempArr) If tempArr(j) < x Then x = tempArr(j): y = tempArr(i) tempArr(i) = x: tempArr(j) = y End If Next j Next i MeArr = tempArr
i = 1 ReDim tempArr(1 To 20, 1 To i) Do 'цикл для поиска групп j = 1 tempVal = Val(MeArr(UBound(MeArr))): MeArr(UBound(MeArr)) = "--" 'затираю черточками, что бы потом исключить из массива использованное число If tempVal > 120 Then GoTo Propuskaem 'если входное число больше максимально-допустимого, то переходим к следующему
tempArr(j, i) = tempVal For x = LBound(MeArr) To UBound(MeArr) - 1 If (tempVal + Val(MeArr(x))) = iMax Then j = j + 1 tempArr(j, i) = Val(MeArr(x)): MeArr(x) = "--" tempVal = tempVal + tempArr(j, i) Exit For
ElseIf (tempVal + Val(MeArr(x))) > iMax Then If tempArr(j, i) = 0 Or x = 1 Then Exit For j = j + 1 tempArr(j, i) = Val(MeArr(x - 1)): MeArr(x - 1) = "--" tempVal = tempVal + tempArr(j, i) x = LBound(MeArr) - 1
ElseIf x = UBound(MeArr) - 1 Then 'если дошли до конца массива, а первые два условия не выполнились, то For y = x To LBound(MeArr) Step -1 'перебираем массив в обратном порядке и собираем числа с конца массива. If Val(MeArr(y)) > 0 Then Exit For Next y j = j + 1 tempArr(j, i) = Val(MeArr(y)): MeArr(y) = "--" tempVal = tempVal + tempArr(j, i) x = LBound(MeArr) - 1
ElseIf Val(MeArr(1)) = 0 And (tempVal + Val(MeArr(2))) > iMax Then ' Exit For End If Next x If tempVal < iMin Then Exit Do 'если в результат попало число меньше минимального, то пора выходить из цикла i = i + 1 ReDim Preserve tempArr(1 To 20, 1 To i) 'увеличиваем размер массива для сбора результатов
Propuskaem: ' следующие две строки переопределяют размер масива исключая использованные числа StrTransfer = Replace(Replace(Join(MeArr, "|"), "|--", ""), "--|", "") MeArr = Application.Transpose(Application.Transpose(Split(StrTransfer, "|"))) Loop 'выводим полученный результат x = i - 1 ReDim ResultArr(1 To x) For i = 1 To x For j = 1 To 20 If tempArr(j, i) > 0 Then ResultArr(i) = IIf(j = 1, "", ResultArr(i) & "+") & tempArr(j, i) Else Exit For Next j Next i
For i = LBound(tempArr) To UBound(tempArr) 'сортируем полученный массив от меньшего к большему x = tempArr(i) For j = i To UBound(tempArr) If tempArr(j) < x Then x = tempArr(j): y = tempArr(i) tempArr(i) = x: tempArr(j) = y End If Next j Next i MeArr = tempArr
i = 1 ReDim tempArr(1 To 20, 1 To i) Do 'цикл для поиска групп j = 1 tempVal = Val(MeArr(UBound(MeArr))): MeArr(UBound(MeArr)) = "--" 'затираю черточками, что бы потом исключить из массива использованное число If tempVal > 120 Then GoTo Propuskaem 'если входное число больше максимально-допустимого, то переходим к следующему
tempArr(j, i) = tempVal For x = LBound(MeArr) To UBound(MeArr) - 1 If (tempVal + Val(MeArr(x))) = iMax Then j = j + 1 tempArr(j, i) = Val(MeArr(x)): MeArr(x) = "--" tempVal = tempVal + tempArr(j, i) Exit For
ElseIf (tempVal + Val(MeArr(x))) > iMax Then If tempArr(j, i) = 0 Or x = 1 Then Exit For j = j + 1 tempArr(j, i) = Val(MeArr(x - 1)): MeArr(x - 1) = "--" tempVal = tempVal + tempArr(j, i) x = LBound(MeArr) - 1
ElseIf x = UBound(MeArr) - 1 Then 'если дошли до конца массива, а первые два условия не выполнились, то For y = x To LBound(MeArr) Step -1 'перебираем массив в обратном порядке и собираем числа с конца массива. If Val(MeArr(y)) > 0 Then Exit For Next y j = j + 1 tempArr(j, i) = Val(MeArr(y)): MeArr(y) = "--" tempVal = tempVal + tempArr(j, i) x = LBound(MeArr) - 1
ElseIf Val(MeArr(1)) = 0 And (tempVal + Val(MeArr(2))) > iMax Then ' Exit For End If Next x If tempVal < iMin Then Exit Do 'если в результат попало число меньше минимального, то пора выходить из цикла i = i + 1 ReDim Preserve tempArr(1 To 20, 1 To i) 'увеличиваем размер массива для сбора результатов
Propuskaem: ' следующие две строки переопределяют размер масива исключая использованные числа StrTransfer = Replace(Replace(Join(MeArr, "|"), "|--", ""), "--|", "") MeArr = Application.Transpose(Application.Transpose(Split(StrTransfer, "|"))) Loop 'выводим полученный результат x = i - 1 ReDim ResultArr(1 To x) For i = 1 To x For j = 1 To 20 If tempArr(j, i) > 0 Then ResultArr(i) = IIf(j = 1, "", ResultArr(i) & "+") & tempArr(j, i) Else Exit For Next j Next i
Нашёл и исправил ошибки в своих формулах, исключил сложение с самим собой и выбросил из сложения уже использованные. Немного причесал и вычислил остатки. Формула тяжёлая, считает долго. Больше четырёх слагаемых уже вручную, но это остаток. Доб. Сделал, чтобы считал быстрее. Файл перевложил.
Нашёл и исправил ошибки в своих формулах, исключил сложение с самим собой и выбросил из сложения уже использованные. Немного причесал и вычислил остатки. Формула тяжёлая, считает долго. Больше четырёх слагаемых уже вручную, но это остаток. Доб. Сделал, чтобы считал быстрее. Файл перевложил.Светлый
Светлый, практически то что надо!!! Я в первом файле дал вводные данные упрощенные!! Сейчас выкладываю файл с данными столбца А, которые использую, и его результат поражает!!! Считает не долго!!! Есть некоторые непонятности при подсчетах: 1) В столбце С группа 8, происходит ожидаемый подсчет, так же как и в группах 15,16,17. Но в группах с 9 по 14 происходит фиксирование максимума без сложения с остатками. PS: может можно это исправить!? Заодно хвостов меньше будет!
Вопросы: 1) Возможно ли, при суммировании 4 отрезков с получением суммарности не в равном (G3),а в диапазоне от F3 до G3!? Может это поможет при суммировании с остатками. PS: Или данный диапазон использовать в какой ни будь не сложной формуле (отдельно для хвостов) подсчета остатков (с большим чем 4 суммируемых). 2) Возможно ли, увеличить количество отрезков в столбце А до 200 и более!? Как я понял формулы привязаны к определенному количеству занятых ячеек!
Светлый, практически то что надо!!! Я в первом файле дал вводные данные упрощенные!! Сейчас выкладываю файл с данными столбца А, которые использую, и его результат поражает!!! Считает не долго!!! Есть некоторые непонятности при подсчетах: 1) В столбце С группа 8, происходит ожидаемый подсчет, так же как и в группах 15,16,17. Но в группах с 9 по 14 происходит фиксирование максимума без сложения с остатками. PS: может можно это исправить!? Заодно хвостов меньше будет!
Вопросы: 1) Возможно ли, при суммировании 4 отрезков с получением суммарности не в равном (G3),а в диапазоне от F3 до G3!? Может это поможет при суммировании с остатками. PS: Или данный диапазон использовать в какой ни будь не сложной формуле (отдельно для хвостов) подсчета остатков (с большим чем 4 суммируемых). 2) Возможно ли, увеличить количество отрезков в столбце А до 200 и более!? Как я понял формулы привязаны к определенному количеству занятых ячеек!Кашкар
У меня там ручной подсчёт остатков. Надо выделить вторую группу, которая в рамочке, и протянуть до самого низа. Формулы скопируются так же группами. Там, где просто максимумы, под ними можно вручную вписывать числа и подгонять сумму. Задействовать для этого две или больше групп. Одна-три группы вручную подгоняется очень просто. В группе справа внизу сумма оставшихся чисел. Если она меньше требуемой, значит это остаток. Для следующего расчёта - формулы можно опять скопировать сверху. 1) В принципе можно. 2) У меня формула на 100. Можно попробовать до 1000 расширить, сколько времени считать будет? Попытался расширить до 200, закончились ресурсы Excel. Формула только в одной ячейке скушала два с половиной гигабайта оперативки. Время будет, ещё поиграюсь.
У меня там ручной подсчёт остатков. Надо выделить вторую группу, которая в рамочке, и протянуть до самого низа. Формулы скопируются так же группами. Там, где просто максимумы, под ними можно вручную вписывать числа и подгонять сумму. Задействовать для этого две или больше групп. Одна-три группы вручную подгоняется очень просто. В группе справа внизу сумма оставшихся чисел. Если она меньше требуемой, значит это остаток. Для следующего расчёта - формулы можно опять скопировать сверху. 1) В принципе можно. 2) У меня формула на 100. Можно попробовать до 1000 расширить, сколько времени считать будет? Попытался расширить до 200, закончились ресурсы Excel. Формула только в одной ячейке скушала два с половиной гигабайта оперативки. Время будет, ещё поиграюсь.Светлый
я думал, что никто не заметил... Я ж потратил на его написание не мало времени. Задачка ж нетривиальная... формулы - это прекрасно, но в большинстве своем, они проиграют алгоритму на VBA. Макрос всеодно пошевелее формул будет, и это при том, что я его не стремился сделать суппер скоростным для обработки сотен тысяч записей.
нашел ошибку в коде! вместо:[vba]
Код
If tempVal > 120 Then GoTo Propuskaem
[/vba] исправьте на: [vba]
Код
If tempVal > iMax Then GoTo Propuskaem
[/vba]
P.S. а еще в новых данных изменился минимальный шаг, что тоже влияет на результат...
я думал, что никто не заметил... Я ж потратил на его написание не мало времени. Задачка ж нетривиальная... формулы - это прекрасно, но в большинстве своем, они проиграют алгоритму на VBA. Макрос всеодно пошевелее формул будет, и это при том, что я его не стремился сделать суппер скоростным для обработки сотен тысяч записей.
нашел ошибку в коде! вместо:[vba]
Код
If tempVal > 120 Then GoTo Propuskaem
[/vba] исправьте на: [vba]
Код
If tempVal > iMax Then GoTo Propuskaem
[/vba]
P.S. а еще в новых данных изменился минимальный шаг, что тоже влияет на результат...boa
Сообщение отредактировал boa - Суббота, 16.06.2018, 00:45