Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Равномерное разделение суммы в периоде/заполнение массива - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин  
Мир 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

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

Спасибо
К сообщению приложен файл: 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)


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

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

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

Спасибо

Автор - ao-prokopov
Дата добавления - 23.10.2021 в 18:05
gling Дата: Суббота, 23.10.2021, 18:51 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 2296
Репутация: 595 ±
Замечаний: 0% ±

2010
Здравствуйте. Формулами не всё срослось, требуется доработка. Если поставить единички в выделенные ячейки, то всё срастётся. Ячейки можно выбрать и в других строках, на усмотрение.
Поиск решения показал вариант, устроит он вас или нет, смотрите в файле. А может вообще я не то делал.
К сообщению приложен файл: 7733532.xlsx(23.3 Kb)


ЯД-41001506838083

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

Автор - gling
Дата добавления - 23.10.2021 в 18:51
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
 
Ответить
Сообщениеgling,
Спасибо,
есть над чем подумать, конечно по формуле интересное решение.

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

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

Автор - ao-prokopov
Дата добавления - 23.10.2021 в 19:41
Светлый Дата: Суббота, 23.10.2021, 20:00 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1738
Репутация: 473 ±
Замечаний: 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
 
Ответить
СообщениеУпрощённая формула без равномерности:
Код
=МИН(ОКРВВЕРХ(($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. Файл перезалил.

Автор - Светлый
Дата добавления - 23.10.2021 в 20:00
ao-prokopov Дата: Воскресенье, 24.10.2021, 10:52 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

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

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

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


Сообщение отредактировал ao-prokopov - Воскресенье, 24.10.2021, 11:55
 
Ответить
СообщениеСветлый,
Спасибо огромное, то что нужно, особенно последний вариант

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

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

Автор - ao-prokopov
Дата добавления - 24.10.2021 в 10:52
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
 
Ответить
СообщениеСветлый,

А если в строках столбца 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:55
Светлый Дата: Воскресенье, 24.10.2021, 14:10 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1738
Репутация: 473 ±
Замечаний: 0% ±

Excel 2013, 2016
как бы такое подправить
Код
=ЕСЛИ($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
 
Ответить
Сообщение
как бы такое подправить
Код
=ЕСЛИ($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 в 14:10
ao-prokopov Дата: Воскресенье, 24.10.2021, 14:43 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

2019
Светлый,

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


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

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

Автор - ao-prokopov
Дата добавления - 24.10.2021 в 14:43
Светлый Дата: Воскресенье, 24.10.2021, 22:06 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 1738
Репутация: 473 ±
Замечаний: 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)


Программировать проще, чем писать стихи.
 
Ответить
СообщениеНемного потестировал. Выяснилось, что бывают варианты, при которых сумма столбцов не очень равномерна. Лечится умножением на 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)

Автор - Светлый
Дата добавления - 24.10.2021 в 22:06
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

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


Сообщение отредактировал ao-prokopov - Воскресенье, 24.10.2021, 22:44
 
Ответить
СообщениеСветлый,

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

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

Конечно, если развить тему и стремиться к идеалу, то можно пробовать, что бы распределение шло, например,
из последнего файла последовательность сумм по столбцам после заполнения: 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

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

Автор - ao-prokopov
Дата добавления - 24.10.2021 в 22:43
Светлый Дата: Воскресенье, 24.10.2021, 23:40 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 1738
Репутация: 473 ±
Замечаний: 0% ±

Excel 2013, 2016
Но это уже из области фантастики
Наоборот, это упрощает задачу. Я наворачивал формулу, чтобы равномернее распределить данные. Вот:
Код
=ЕСЛИ(ОСТАТ($B4;$N$2);(C$2%%+СУММ(C$3:C3)<=НАИМЕНЬШИЙ($C$2:$N$2%%+ТРАНСП(МУМНОЖ(ТРАНСП($C$3:$N3);СТРОКА($3:3)^0));ОСТАТ($B4;$N$2))))+ОТБР($B4/$N$2)
*Всё равно всегда 12 месяцев считаться будет, заменил на 12. И МУМНОЖ() оптимизировал:
Код
=ЕСЛИ(ОСТАТ($B4;12);(C$2%+СУММ(C$3:C3)<=НАИМЕНЬШИЙ($C$2:$N$2%+МУМНОЖ(ТРАНСП(СТРОКА($3:3)^0);$C$3:$N3);ОСТАТ($B4;12))))+ОТБР($B4/12)


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

Сообщение отредактировал Светлый - Понедельник, 25.10.2021, 08:45
 
Ответить
Сообщение
Но это уже из области фантастики
Наоборот, это упрощает задачу. Я наворачивал формулу, чтобы равномернее распределить данные. Вот:
Код
=ЕСЛИ(ОСТАТ($B4;$N$2);(C$2%%+СУММ(C$3:C3)<=НАИМЕНЬШИЙ($C$2:$N$2%%+ТРАНСП(МУМНОЖ(ТРАНСП($C$3:$N3);СТРОКА($3:3)^0));ОСТАТ($B4;$N$2))))+ОТБР($B4/$N$2)
*Всё равно всегда 12 месяцев считаться будет, заменил на 12. И МУМНОЖ() оптимизировал:
Код
=ЕСЛИ(ОСТАТ($B4;12);(C$2%+СУММ(C$3:C3)<=НАИМЕНЬШИЙ($C$2:$N$2%+МУМНОЖ(ТРАНСП(СТРОКА($3:3)^0);$C$3:$N3);ОСТАТ($B4;12))))+ОТБР($B4/12)

Автор - Светлый
Дата добавления - 24.10.2021 в 23:40
ao-prokopov Дата: Понедельник, 25.10.2021, 20:26 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

2019
Светлый,

По-моему все варианты рассмотрены))

Спасибо.
Высший пилотаж)
 
Ответить
СообщениеСветлый,

По-моему все варианты рассмотрены))

Спасибо.
Высший пилотаж)

Автор - ao-prokopov
Дата добавления - 25.10.2021 в 20:26
Светлый Дата: Понедельник, 15.11.2021, 23:17 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 1738
Репутация: 473 ±
Замечаний: 0% ±

Excel 2013, 2016
По-моему все варианты рассмотрены
После "небольшого" обдумывания и Мозгового штурма родилось ещё несколько вариантов. Вот лучший:
Код
=(ОСТАТ(СУММ($B$3:$B4)-C$2;12)<ОСТАТ($B4;12))+ОТБР($B4/12)
*А вот ещё лучший:
Код
=ОТБР((ОСТАТ(СУММ($B$3:$B3)-C$2;12)+$B4)/12)
К сообщению приложен файл: test2_2003-5.xls(109.5 Kb)


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

Сообщение отредактировал Светлый - Вторник, 16.11.2021, 08:01
 
Ответить
Сообщение
По-моему все варианты рассмотрены
После "небольшого" обдумывания и Мозгового штурма родилось ещё несколько вариантов. Вот лучший:
Код
=(ОСТАТ(СУММ($B$3:$B4)-C$2;12)<ОСТАТ($B4;12))+ОТБР($B4/12)
*А вот ещё лучший:
Код
=ОТБР((ОСТАТ(СУММ($B$3:$B3)-C$2;12)+$B4)/12)

Автор - Светлый
Дата добавления - 15.11.2021 в 23:17
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Равномерное разделение суммы в периоде/заполнение массива (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования Jobsora jooble
© 2010-2021 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!