Распределение суммы значений на основе исторчиских данных

Question

Необходимо распределить случайное количество гостей на временные промежутки на основе исторических данных. Можно ли сделать так, чтобы excel сам посчитал какой процент от общей суммы гостей приходиться на временной период и распределил новое случайное число в соответствии с старыми данными в столбце Понедельник-тест. Пример:

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

Answer 1

Всё решается одной формулой, а поскольку итого - не того, пусть "1000/396" будет просто коэффициентом пропорциональности:

[C2]=ROUND(B2:B16*C17/B17;0)

Если пересчитать итог,

то можно рассчитать распределение посетителей по интервалам для заданного общего количества:

[C2]=LET(arr;B2:B16;tgt;B18;rs;ROWS(arr);rwv;arr*tgt/SUM(arr);rdv;ROUND(rwv;0);
  dt;SUM(rdv)-tgt;darr;rdv-rwv;cso;SORTBY(SEQUENCE(rs);darr);rdvs;SORTBY(rdv;darr);
  SORTBY(MAKEARRAY(rs;1;LAMBDA(r;c;INDEX(rdvs;r)-IFS(r>rs-dt;1;r<=-dt;-1;TRUE;0)));cso)
)

На иллюстрации, помимо данных автора вопроса, приведён ещё один пример в колонках "F:I". Колонки "Без коррекции" (соответствует rdv из формулы) и выделение жёлтым иллюстрируют работу алгоритма коррекции ошибок округления, который изменяет на 1 количество посетителей в интервалах с наибольшими ошибками округления для устранения разницы с заданным количеством.

Answer 2

Поиграемся :)

Формула для корректировки (в столбце F), без оптимизации:

{=ЕСЛИ(E2-СУММ(ОКРУГЛ(B6:B20*F2;))=0;
       ОКРУГЛ(B6:B20*F2;);
       ОКРУГЛ(B6:B20*F2;)+
           ЕСЛИ(B6:B20*F2-ОКРУГЛ(B6:B20*F2;)>=
                 НАИБОЛЬШИЙ(B6:B20*F2-ОКРУГЛ(B6:B20*F2;);ABS(E2-СУММ(ОКРУГЛ(B6:B20*F2;))));
               ЗНАК(E2-СУММ(ОКРУГЛ(B6:B20*F2;)))))}

Обнаруженный недостаток: при наличии в исходном диапазоне одинаковых значений НАИБОЛЬШИЙ определит, что корректировать надо больше, чем нужно. Устранить - менять вычисление количества корректировок...

Это все о том, что да, можно поиздеваться, но для данной практической задачи - излишество.

БЛОГ НА HUSL

Распределение суммы значений на основе исторчиских данных

Ответы (2 шт):