Равномерное разделение суммы в периоде/заполнение массива

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Равномерное разделение суммы в периоде/заполнение массива (Формулы/Formulas)

ao-prokopov

Дата: Суббота, 23.10.2021, 18:05 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 7

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

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

2019

Здравствуйте!
Уважаемые форумчане, прошу помочь в решении задачи, может быть у же кто то сталкивался и пробовал реализовать следующее:

Для чего применимо: на разных объектах стоят некоторые единицы устройств/оборудования и со временем устаревают, вывести и заменить их единовременно невозможно из-за специфики работы, поэтому самое старое оборудование берется из каждого объекта и равномерно меняется в периоде по нескольким объектам без единовременной нагрузки на бюджет
как то так

1. Есть данные (просто цифры) в ячейках столбца А, т.е. А4-А19. Сумма данных столбца А из примера=93 и есть 12 периодов
2. Нужно заполнить ячейки массива таким образом, что бы каждая строка массива в сумме соответствовала числу из соответствующей ячейки, т.е. при значении 14 в ячейке, данные должны распределиться в периодах таким образом, что бы в сумме давать 14 (2+2+1+1+1+1+1+1+1+1+1+1)
В свою очередь сумма значений ячеек в столбцах периода должна быть общая сумма /12 периодов.
3. Алгоритм в поиске

В приложении test2.xls , на листе 1 и 2 пробовал реализовать подобное, но что то с равномерностью не получается:

в ячейках первого периода придумал такую формулу

Код

=ОКРУГЛ((($B$3/11)*((100*B4)/B$3))/100;0)

в ячейках 2-12 периода такую

Код

=ОКРУГЛ((($B$3/11)*((100*($B4-СУММ($C4:C4)))/C$3))/100;0)

Пока не знаю как, но работает...только подогнать пришлось(период в формуле 11), т.к. в 0 не выходил к концу 12 периода и суммы столбцов ушли начиная с 3 периода.

Ранее рассматривал еще вариант зависящий от процентного отношения, но что то тоже пошло не так, т.к. при большом количестве строк процент сильно падает и округляет все к 0, и в итоге к 7 периоду единицы пропадают, или при очень большом списке всегда значение единиц при расчете по процентам меньше 0,5 и округляется к 0

Все что могу придумать и описать

Спасибо

Код

=ОКРУГЛ((($B$3/11)*((100*B4)/B$3))/100;0)

в ячейках 2-12 периода такую

Код

=ОКРУГЛ((($B$3/11)*((100*($B4-СУММ($C4:C4)))/C$3))/100;0)

К сообщению приложен файл: test2_2003.xls (83.0 Kb) · test2.xlsx (20.1 Kb)

Ответить

СообщениеЗдравствуйте!
Уважаемые форумчане, прошу помочь в решении задачи, может быть у же кто то сталкивался и пробовал реализовать следующее:

Для чего применимо: на разных объектах стоят некоторые единицы устройств/оборудования и со временем устаревают, вывести и заменить их единовременно невозможно из-за специфики работы, поэтому самое старое оборудование берется из каждого объекта и равномерно меняется в периоде по нескольким объектам без единовременной нагрузки на бюджет
как то так

1. Есть данные (просто цифры) в ячейках столбца А, т.е. А4-А19. Сумма данных столбца А из примера=93 и есть 12 периодов
2. Нужно заполнить ячейки массива таким образом, что бы каждая строка массива в сумме соответствовала числу из соответствующей ячейки, т.е. при значении 14 в ячейке, данные должны распределиться в периодах таким образом, что бы в сумме давать 14 (2+2+1+1+1+1+1+1+1+1+1+1)
В свою очередь сумма значений ячеек в столбцах периода должна быть общая сумма /12 периодов.
3. Алгоритм в поиске

В приложении test2.xls , на листе 1 и 2 пробовал реализовать подобное, но что то с равномерностью не получается:

в ячейках первого периода придумал такую формулу

Код

=ОКРУГЛ((($B$3/11)*((100*B4)/B$3))/100;0)

в ячейках 2-12 периода такую

Код

=ОКРУГЛ((($B$3/11)*((100*($B4-СУММ($C4:C4)))/C$3))/100;0)

Автор - ao-prokopov
Дата добавления - 23.10.2021 в 18:05

gling

Дата: Суббота, 23.10.2021, 18:51 | Сообщение № 2

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2519

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

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

2010

Здравствуйте. Формулами не всё срослось, требуется доработка. Если поставить единички в выделенные ячейки, то всё срастётся. Ячейки можно выбрать и в других строках, на усмотрение.
Поиск решения показал вариант, устроит он вас или нет, смотрите в файле. А может вообще я не то делал.

К сообщению приложен файл: 7733532.xlsx (23.3 Kb)

ЯД-41001506838083

Сообщение отредактировал gling - Воскресенье, 24.10.2021, 15:10

Ответить

ao-prokopov

Дата: Суббота, 23.10.2021, 19:41 | Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 7

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

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

2019

gling,
Спасибо,
есть над чем подумать, конечно по формуле интересное решение.

Мне уже начинает казаться, что может стоит реализовать подобное в VBA? что бы циклы считать, но все таки склоняюсь к формулам и может доп. поля сделать между периодами для промежуточных расчетов.
Хочется реализовать так (идеал в картинке, приложение).
Т.е. сначала расчёт идет по количеству и равномерное распределение по 1 столбцу в сумме не более 93/12(сначала округление к большему- 8, далее к меньшему - 7(10-11-12 периоды) с приоритетом с большими значениями и далее по столбцам при достижении по каждой строке равных значений ( при равных остатках)происходит распределение или рандомно или сверху вниз

Пока думаю...

К сообщению приложен файл: 0723080.jpg (53.2 Kb)

Сообщение отредактировал ao-prokopov - Суббота, 23.10.2021, 19:42

Ответить

Светлый

Дата: Суббота, 23.10.2021, 20:00 | Сообщение № 4

Группа: Друзья

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

Сообщений: 1829

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

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

Excel 2013, 2016

Упрощённая формула без равномерности:

Код

=МИН(ОКРВВЕРХ(($B4*2-СУММ($B4:B4))/($N$2-B$2);1);ОКРВВЕРХ($B$3/$N$2;1)+C$3-СУММ(C$3:C3))

*А это формула равномерного по строке распределения. Массивная для всей строки:

Код

=ТРАНСП(МУМНОЖ(Ч(ОСТАТ($C$2:$N$2;$B4/$N$2)+$C$2:$N$2%%+ТРАНСП(МУМНОЖ(ТРАНСП($C$3:$N3);Ч($B$3:$B3<>$B$3)))<=ТРАНСП(ОСТАТ($C$2:$N$2;$B4/$N$2)+$C$2:$N$2%%+ТРАНСП(МУМНОЖ(ТРАНСП($C$3:$N3);Ч($B$3:$B3<>$B$3)))));ТРАНСП($C$2:$N$2)^0)<=ОСТАТ($B4;$N$2))+ОТБР($B4/$N$2)

**чуть-чуть упростил:

Код

=ТРАНСП(МУМНОЖ(Ч(ОСТАТ($C$2:$N$2;$B4/$N$2)+$C$2:$N$2%%+ТРАНСП(МУМНОЖ(ТРАНСП($C$3:$N3);$B$3:$B3^0))<=ТРАНСП(ОСТАТ($C$2:$N$2;$B4/$N$2)+$C$2:$N$2%%+ТРАНСП(МУМНОЖ(ТРАНСП($C$3:$N3);$B$3:$B3^0))));ТРАНСП($C$2:$N$2)^0)<=ОСТАТ($B4;$N$2))+ОТБР($B4/$N$2)

***Массивная формула для ячейки C4 и протянуть на весь диапазон:

Код

=(ОСТАТ(C$2;$B4/$N$2)+C$2%%+СУММ(C$3:C3)<=ЕСЛИ(ОСТАТ($B4;$N$2);НАИМЕНЬШИЙ(ОСТАТ($C$2:$N$2;$B4/$N$2)+$C$2:$N$2%%+ТРАНСП(МУМНОЖ(ТРАНСП($C$3:$N3);$B$3:$B3^0));ОСТАТ($B4;$N$2));))+ОТБР($B4/$N$2)

****Формулу исправил. Не работала для кратных 12. Файл перезалил.

К сообщению приложен файл: test2_2003-1.xls (90.5 Kb) · test2_2003-3.xls (119.5 Kb)

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Суббота, 23.10.2021, 23:37

Ответить

ao-prokopov

Дата: Воскресенье, 24.10.2021, 10:52 | Сообщение № 5

Группа: Пользователи

Ранг: Прохожий

Сообщений: 7

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

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

2019

Светлый,
Спасибо огромное, то что нужно, особенно последний вариант

осталось только разобраться)

Светлый, gling, ++

Сообщение отредактировал ao-prokopov - Воскресенье, 24.10.2021, 11:55

Ответить

ao-prokopov

Дата: Воскресенье, 24.10.2021, 11:55 | Сообщение № 6

Группа: Пользователи

Ранг: Прохожий

Сообщений: 7

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

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

2019

Светлый,

А если в строках столбца B (заданных) значение будет 0, выходила ошибка деления на 0, я ее с помощью "если" подправляю:

Код

=ЕСЛИ($B9=0;0;(ОСТАТ(C$2;$B9/$N$2)+C$2%%+СУММ(C$3:C8)<=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ОСТАТ($C$2:$N$2;$B9/$N$2)+$C$2:$N$2%%+ТРАНСП(МУМНОЖ(ТРАНСП($C$3:$N8);$B$3:$B8^0));ОСТАТ($B9;$N$2));))+ОТБР($B9/$N$2))

Но при таком варианте, все, что ниже в массиве становится 0, как бы такое подправить, т.е. даже при условии 0 в любой заданной ячейке шел расчет? спасибо

Сообщение отредактировал ao-prokopov - Воскресенье, 24.10.2021, 11:57

Ответить

Светлый

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

Группа: Друзья

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

Сообщений: 1829

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

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

Excel 2013, 2016

Цитата ao-prokopov, 24.10.2021 в 11:55, в сообщении № 6 (

)

как бы такое подправить

Код

=ЕСЛИ($B4;(ОСТАТ(C$2;$B4/$N$2)+C$2%%+СУММ(C$3:C3)<=ЕСЛИ(ОСТАТ($B4;$N$2);НАИМЕНЬШИЙ(ОСТАТ($C$2:$N$2;$B4/$N$2)+$C$2:$N$2%%+ТРАНСП(МУМНОЖ(ТРАНСП($C$3:$N3);СТРОКА($3:3)^0));ОСТАТ($B4;$N$2));))+ОТБР($B4/$N$2);)

*Даже ещё короче:

Код

=ЕСЛИ(ОСТАТ($B4;$N$2);(ОСТАТ(C$2;$B4/$N$2)+C$2%%+СУММ(C$3:C3)<=НАИМЕНЬШИЙ(ОСТАТ($C$2:$N$2;$B4/$N$2)+$C$2:$N$2%%+ТРАНСП(МУМНОЖ(ТРАНСП($C$3:$N3);СТРОКА($3:3)^0));ОСТАТ($B4;$N$2))))+ОТБР($B4/$N$2)

**Пришлось немного исправить. Суммы по столбцам принимали три значения, если последняя строка - большое число. Теперь лучше:

Код

=ЕСЛИ(ОСТАТ($B4;$N$2);(ОСТАТ(C$2;$B4/$N$2)+C$2%%+3*СУММ(C$3:C3)<=НАИМЕНЬШИЙ(ОСТАТ($C$2:$N$2;$B4/$N$2)+$C$2:$N$2%%+3*ТРАНСП(МУМНОЖ(ТРАНСП($C$3:$N3);СТРОКА($3:3)^0));ОСТАТ($B4;$N$2))))+ОТБР($B4/$N$2)

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Воскресенье, 24.10.2021, 15:39

Ответить

ao-prokopov

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

Группа: Пользователи

Ранг: Прохожий

Сообщений: 7

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

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

2019

Светлый,

Спасибо, все получается

Сообщение отредактировал ao-prokopov - Воскресенье, 24.10.2021, 20:08

Ответить

Светлый

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

Группа: Друзья

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

Сообщений: 1829

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

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

Excel 2013, 2016

Немного потестировал. Выяснилось, что бывают варианты, при которых сумма столбцов не очень равномерна. Лечится умножением на 9 вместо 3:

Код

=ЕСЛИ(ОСТАТ($B4;$N$2);(ОСТАТ(C$2;$B4/$N$2)+C$2%%+9*СУММ(C$3:C3)<=НАИМЕНЬШИЙ(ОСТАТ($C$2:$N$2;$B4/$N$2)+$C$2:$N$2%%+9*ТРАНСП(МУМНОЖ(ТРАНСП($C$3:$N3);СТРОКА($3:3)^0));ОСТАТ($B4;$N$2))))+ОТБР($B4/$N$2)

К сообщению приложен файл: test2_2003-4.xls (126.5 Kb)

Программировать проще, чем писать стихи.

Ответить

ao-prokopov

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

Группа: Пользователи

Ранг: Прохожий

Сообщений: 7

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

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

2019

Светлый,

В данной ситуации, если не сказать круто или здорово- не сказать ничего))) Если было бы возможно, я бы каждый пост плюсовал, а так не дает)
Круто и здорово!

Конечно, нет пределу совершенства и можно еще попробовать подправить, но, наверное, этого достаточно.
Мне кажется Вам интересны сложные задачи (для меня такое задание было сложным)

Конечно, если развить тему и стремиться к идеалу, то можно пробовать, что бы распределение шло, например,
из последнего файла последовательность сумм по столбцам после заполнения: 75 75 75 74 75 74 74 75 75 74 74 74,
а если сделать сначала большие, т.е. 75, а в конце меньшие: 75 75 75 75 75 75 74 74 74 74 74 74

Но это уже из области фантастики