Случайное распределение с заданной суммой. I часть

Случайное распределение с заданной суммой ексельСлучайное распределение с заданной суммой. Часть I.

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

Не буду уточнять, для чего это необходимо, вариантов может быть масса.

И в моей практике бывали случаи, когда сумму необходимо распределить так, чтобы сымитировать нормальное распределение (кто знаком со статистикой, меня поймут).

Итак — условия задачи:

  1. Есть некая сумма;
  2. Есть календарь, где каждый день разбит на часы (см.рис.)
  3. Эту сумму необходимо распределить по дням и часам с определенными ограничениями:
    1. В ночное время значения должны составлять 70% от дневных;
    2. Вариации должны быть в интервале 5-10% от средних значений;
    3. В праздничные дни значения также должны составлять 70% от дневных значений в обычные дни.

Для решения я использовал две ключевые функции: СЛЧИС и НОРМОБР. Первая функция генерирует случайное число в диапазоне от 0 до 1. Вторая — формирует значения в соответствии с нормальным распределением (если кто не знаком с этим термином, прошу в википедию, а в двух словах поясню, что множество случайных замеров какого-либо явления подчиняются как раз закону нормального распределения).

Создание вводной страницы

Прежде, чем приступать к таблице, необходимо продумать вводные значения — исходные данные, которые мы будем изменять, чтобы получить желаемый результат:

  1. Итоговая сумма — это очевидно. Нам необходимо «раскидать» значения по дням и часам месяца именно так, чтобы в сумме получилось эта сумма. Ячейке с суммой дадим имя «Сумм» (это облегчит использование формул в дальнейшем);
  2. Расчетный год — таблицу создаем «на века», поэтому позаботимся над тем, чтобы можно было устанавливать год;
  3. Расчетный месяц — чтобы знать, на сколько дней необходимо «раскидывать» сумму, а также иметь информацию о выходных и праздничных днях. Для выбора месяца из списка, создадим страницу «Списки» и создадим там список месяцев (см.рис.). Подробнее о работе со списками читайте здесь;
  4. Время начала и окончания ночи — по трудовому законодательству ночь начинается в 22.00, а заканчивается в 6.00. Однако некоторые организации началом и окончанием ночи считают, например, время работы ночных смен. Словом, необходимо оставить возможность менять эти часы. Первое значение ночного интервала назовем «Ночь1«, второе — «Ночь2«;
  5. Примерная доля, отличающая значения в праздничные дни и в ночные часы (у нас — 70% в том и в другом случае). Ячейку со значением доли днем назовем «ДоляДень«, ночью — «ДоляНочь«, в праздники — «ДоляПраздник«;
  6. Процент вариации, т.е. в каких пределах примерно должны отличаться значения друг от друга. В нашем случае — 10%. Назовем эту ячейку «Вариация».

Закладываем все это на странице «Ввод» (см.рисунок выше).

Настройка формы основной таблицы

Как должна выглядеть таблица, мы уже видели. Настроим эту страницу и назовем ее оригинально — «Таблица«. Количество часов в дне у нас не меняется, поэтому просто проставим часы в вертикальной колонке. А вот даты нужно настроить. Как это сделать, написано здесь.

Формула первого числа месяца будет преобразовывать введенные на странице «Ввод» текстовые значения в дату: =ДАТАЗНАЧ(1&Ввод!B2&Ввод!B1)

Следующие числа будут образованы простым прибавлением единицы к предыдущей дате. А формула, соответствующая 29-му числу будет следующей: =ЕСЛИ(МЕСЯЦ(N34)=МЕСЯЦ(N34+1);N34+1;»»).

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

Настройка условий распределения

Первым делом, настроим условие, при котором должна определяться доля в зависимости от того, является ли день праздничным или является ли час ночным. Форму таблицы используем точно такую же, что и на странице «Таблица«. Это избавит нас от головной боли при протягивании формул, ссылающихся на аналогичные таблицы. Просто выделяем таблицу, и вставляем ее на новую страницу. Эту таблицу я назвал «Доли«.

Распределение долей

Запрограммировать условие, при котором в ночные часы должно быть одно значение, а в дневные — другое, не проблема. Используем функцию ЕСЛИ. В случае, когда необходимо заложить два условия, используем функцию ИЛИ: =ЕСЛИ(ИЛИ($A4<Ночь1;$A4>=Ночь2);ДоляНочь; ДоляДень)

Перевод функции на русский примерно такой:

  • Если одно из двух условий верно:
    • значение меньше значения в ячейке «Ночь1» или…
    • значение больше или равно значения в ячейке «Ночь2«
  • то используем значение в ячейке «ДоляНочь«, в противном случае…
  • используем значение в ячейке «ДоляДень«.

Однако нам необходимо использовать еще одно условие — доля в праздничные дни. Здесь решение не такое тривиальное. Используем функцию ПОИСКПОЗ, которая проверяет, соответствует ли выбранное значение какому-либо значению из списка.

Кстати, сформируем список праздничных дней на странице «Списки» (см.рис.). Как видите, для праздников создана специальная, так называемая, умная таблица. Чтобы была возможность гибко менять ее содержание (мало ли, какие праздники нам спустят сверху в будущем). Диапазону с датами (не всей «умной» таблице, а только диапазону с праздничными датами) дано наименование «Праздник«.

Формула будет такой:

=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B$3;Праздник;0));ДоляДень;ДоляПраздник)

Перевод на русский:

  • Если верно следующее условие:
    • Если проверка наличия выбранного значения в списке «Праздник» выдает ошибку(то есть нет такого значения в списке), то…
  • Выбираем значение из ячейки «ДоляДень«, в противном случае…
  • Выбираем значение из ячейки «ДоляПраздник«

Теперь, напрягая остатки интеллекта (у меня шел дым из ушей), объединяем эти две части формулы в одну:

=ЕСЛИ(ИЛИ($A4<Ночь1;$A4>=Ночь2);ДоляНочь;ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B$3;Праздник;0));ДоляДень;ДоляПраздник))

Копируем формулу на все ячейки таблицы (необходимые колонки и строки закреплены с помощью символа $, обеспечивая смещение только требуемых ссылок).

На этом я прерываюсь. Продолжим в следующей статье.

Случайное распределение с заданной суммой. II часть

Случайное распределение с заданной суммой ексельСлучайное распределение с заданной суммой. Часть II.

В прошлой статье мы определили условия распределения, и распределили доли.

Здесь я опишу завершение работы и в конце текста выложу файл со всеми расчетами.

Пора переходить к основным значениям. Для начала — обеспечим линейное распределение, без случайных вариаций.

Распределение средних по долям

Теперь нам необходимо распределить значения в соответствии с этими долями. Здесь все чрезвычайно просто:

  • просуммируем все доли и назовем ячейку с этой суммой «СумДоли«;
  • создадим еще одну таблицу для промежуточных значений (я ее назвал «Распределение средних по долям);
  • в ячейке новой таблицы, соответствующей первому числу месяца запишем формулу: =B4/СумДоли*Сумм
    В4 — это значение, соответствующее первому числу месяца из первой таблицы, «Сумм» — значение в ячейке с суммой, которую необходимо «раскидать»;
  • скопируем формулу в прочие ячейки таблицы.

Случайное распределение

Мы определили своеобразный каркас нашей модели. Осталась самая малость — обеспечить случайное распределение в рамках тех ограничений, которые были заданы. Мы обеспечили соблюдение почти всех правил, кроме одного — значения должны варьироваться в пределах установленного диапазона вариации (мы определили 10%). Для этого создадим еще одну таблицу («Распределение корректив«), которая будет устанавливать корректирующие значения в пределах диапазона, установленного нами ранее (процент вариации, если помните).

Для случайного распределения по нормальному принципу используем две функции:

  • НОРМОБР преобразует число в единичное наблюдение кривой нормального распределения;
  • СЛЧИС обеспечит случайное число.

Для функции НОРМОБР необходимо три переменных:

  1. случайное число в интервале от 0 до 1, которое мы обеспечиваем функцией СЛЧИС;
  2. среднее значение. Поскольку среднее значение у нас зависит от указанных ранее чисел, нам необходимо оперировать долями, которые мы устанавливали в ячейке «Процент вариации» и числами, которые мы рассчитали в таблице распределения средних значений. Формула здесь будет такая — =B63-B63*(1-Вариация/2). Суть ее в том, что мы определяем среднее значение отклонения в большую или меньшую сторону, которое должно равняться половине значения вариации, которую мы установили в ячейке «Процент вариации» (уж не знаю, понятно ли я объяснил суть формулы);
  3. стандартное отклонение. Не буду вдаваться в подробности статистических функций, скажу лишь, что это разброс большинства значений в нашей выборке от среднего значения. Чем меньшее число мы установим, тем больше значений в выборке будет стремиться к среднему. Чтобы приблизить кривую распределения к нормальной, я предлагаю установить стандартное отклонение как пятую часть вариации. Формула такова: =B63-B63*(1-Вариация/5)

Есть одна проблемка — мы не сможем настроить сложение или вычитание по случайному принципу. Поэтому нам необходимо обеспечить вариацию от нуля в сторону положительных или отрицательных чисел. А затем уже можно прибавить к среднему значению положительное или отрицательное число. Поэтому из преобразованного с помощью функции НОРМОБР числа мы вычтем то среднее значение, которое было в этой формуле использовали: =B63-B63*(1-Вариация/2). Таким образом вся формула, соответствующая первому числу месяца, будет выглядеть так:
=НОРМОБР(СЛЧИС();B63-B63*(1-Вариация/2); B63-B63*(1-Вариация/5))-(B63-B63*(1-Вариация/2))

Скопируем ее на все ячейки.

Настройка основной таблицы

Пора переходить к нашей основной таблице. Мы вычислили все промежуточные значения, и осталось из средней, вычисленной в таблице «Распределение средних по долям» вычесть отклонение, вычисленное в таблице «Распределение корректив«. Формула, соответствующая первому числу месяца, будет такой: =Коррективы!B63+Коррективы!B120

Казалось бы — и все. Однако остается проблема. Если мы просуммируем все, вычисленные таким образом, значения, они не дадут идеального совпадения с той суммой, которая нами установлена изначально. Можно было бы установить какую-нибудь одну корректирующую ячейку, значение в которой сформировалось бы путем вычитания из общей суммы, суммы всех ячеек, кроме последней. Но, в этому случае, отклонение может стать слишком большим. Поэтому я сделал 12 корректирующих ячеек, в диапазоне N43:N54.

Коррекция для обеспечения конечной суммы

Для обеспечения конечной суммы, нам необходимо совместить два несогласующихся условий — значения должны казаться случайными и давать в сумме заданное число. Предлагаю такой подход:

  1. определяем шкалу, которая будет обеспечивать доли разброса. Эта шкала должна в сумме давать 12, чтобы сумма соответствовала двенадцати ячейкам. Ее я настроил рядом с таблицей распределения корректив, в диапазоне R156:R167;
  2. производим расчет значений путем перемножения среднего значения на долю (диапазон S156:S167);
  3. добавим шкалу, которая будет определять порядок вывода значений в таблице (диапазон Т156:Т167);
  4. в основной таблице, в ячейках диапазона N43:N54 используем функцию ВЫБОР, которая вытягивает значение из таблицы на основании номера, указанного нами в шкале, созданной на предыдущем шаге. Формула первого значения в этом диапазоне будет такой: =ВЫБОР(Коррективы!T156;Коррективы!$S$156;Коррективы!$S$157;Коррективы!$S$158;Коррективы!$S$159;Коррективы!$S$160;Коррективы!$S$161;Коррективы!$S$162;Коррективы!$S$163;Коррективы!$S$164;Коррективы!$S$165;Коррективы!$S$166;Коррективы!$S$167)

Получается, что мы имитируем случайное распределение, вытягивая распределенные значения в якобы случайном порядке. Если найдете более изящное решение, буду вам очень признателен — пишите в комментариях к этой статье.

А у меня — все. Во вложении файл со всеми расчетами.

Логические функции и сложные алгоритмы

Логические функции и сложные алгоритмы ExcelЛогические функции и сложные алгоритмы в Excel.

Логические функции Excel, пожалуй, одни из моих самых любимых.

Ведь с помощью них можно закладывать практически любые алгоритмы расчетов!

Фактически, мы занимаемся программированием, комбинируя различные функции друг с другом.

И это не более сложно, чем писать формулы сложения или умножения, о которых я писал в статье «Excel для начинающих II«.

Мы рассмотрим функции ЕСЛИ, И, ИЛИ.

Начнем с ключевой логической функции ЕСЛИ. Она, фактически, задает алгоритмы, которые многие учились строить на информатике в школе.

Структура ее такова:

Выглядеть это будет так: =ЕСЛИ(A1>10;»Много»;»Мало»).

Внесем эту формулу в ячейку А3. А в ячейке А1 запишем число 5. Должно появиться слово «Мало». Исправим 5 на 50. Появится слово «Много». Все работает!

Очень часто, одного условия бывает недостаточно.

В этом случае мы можем вкладывать функцию ЕСЛИ саму в себя, таким образом, увеличивая количество условий. Допустим, нам необходимо определить сумму надбавки за разряд работнику (см.рисунок).

Логика очевидна — если разряд 4-й, то платим 4000 руб, если 3-й3000 руб, и т.д. Вот как это выглядит в формуле (для знающих оговорюсь — мне известно, что оптимальнее, в данном случае, использовать ВПР :-)): =ЕСЛИ(C8=A2;B2;ЕСЛИ(C8=A3;B3;ЕСЛИ(C8=A4;B4;ЕСЛИ(C8=A5;B5;0)))). Лично я, для себя, когда начинаю писать нечто подобное, чтобы не сбиться, начинаю про себя проговаривать алгоритм (читаем формулу слева на право):

Таким образом у нас получилось четыре условия ЕСЛИ, вложенных друг в друга.

Эти две функции, как правило, используются как вспомогательные для функции ЕСЛИ. С помощью них нельзя вызвать какое-либо значение, так как они просто проверяют — истинны или ложны условия, которые в них заложены.

Рассмотрим пример — нашему работнику положена премия, но только в том случае, если им была пройдена аттестация, и не было дисциплинарных взысканий.

То есть, только при соблюдении двух условий одновременно.

Напишем формулу: =ЕСЛИ(И(F8=»Да»;G8=»Нет»);5000;0). Пропишем текстом:

Это же условие можно прописать и с помощью функции ИЛИ. Просто мы пойдем от обратного:

Во втором случае нужно понимать, что если ячейки F8 и G8 не будут заполнены, то премия работнику будет выплачена.

Поэтому нельзя сказать, что обе формулы идентичны. Первая говорит о том, что только при выполнении обоих условий премия будет выплачена, а вторая — если будет отмечено хотя бы одно нарушение, то премия не будет выплачена.

Суммирование по условию

Суммирование по условию ExcelСуммирование по условию в Excel.

В прошлой статье я писал о функции суммирования — СУММ.

Она позволяет сложить ряд чисел в диапазоне.

Но иногда бывает необходимо просуммировать не все числа, а только те, которые отвечают какому-либо условию.

Условия могут быть совершенно разные. Например, в ряду чисел (см. рисунок) просуммировать только те, что больше  10.

Или, допустим, в таблице со списком затрат на покупки, просуммировать только продукты питания.

Для подобных целей используется функция СУММЕСЛИ. Она несколько сложнее простого суммирования. Если раньше мы заносили или выбирали наименование СУММ и в скобках указывали диапазон, то сейчас нам необходимо, помимо диапазона суммирования, указать условие суммирования.

Вот как это будет выглядеть: =СУММЕСЛИ(A1:A12;»>10″)

А1:А12 — это, как вы понимаете, диапазон суммирования. А вот то, что указано через точку с запятой в кавычках — условие суммирования, больше 10. Соответственно, во всем диапазоне будут суммироваться только числе больше 10.

Признаться, раньше, когда я еще только осваивал Excel, мне было проще использовать диалоговые окна. То есть, не вносить имя функции в строку формул. а выбрать необходимую в библиотеке. Поскольку суммирование (пусть даже и условное), это математическая функция, то выбираем в библиотеке функций (в закладке меню «Формулы») пункт «Математические».

Библиотека математических функций достаточно большая, и в выпавшем списке мы сразу имя СУММЕСЛИ не увидим. Для этого прокрутите каретку полосы прокрутки выпавшего списка (справа от него) вниз — подводим курсор мыши к каретке, зажимаем левую кнопку и ведем курсор мыши вниз. Поскольку все имена расположены по алфавиту, найти нужное труда не составит.

Кликаем на имени СУММЕСЛИ. Откроется диалоговое окно, в котором для каждого аргумента предусмотрено отдельное поле. Обратите внимание — названия двух полей в окне выделены жирным, а название третьего поля написано обычным шрифтом. Это означает, что два первых поля — «Диапазон» и «Критерий» — должны быть заполнены обязательно, а поле «Диапазон_суммирования» — не обязательное для заполнения.

Диапазон суммирования, в нашем случае, совпадает с диапазоном критерия суммирования. То есть, нам необходимо просуммировать те же значения, в отношении которых должно работать правило «>10«. А вот для второй задачи, где нужно сложить значения, соответствующие надписи «Еда», эти диапазоны отличаются. Тут мы и используем третий аргумент.

Открываем таблицу, и вызываем диалоговое окно условного суммирования. В первое поле («Диапазон«) заносим не диапазон суммирования, а диапазон, на основании которого будут отбираться складываемые ячейки. Это B2:B7.

Второе поле для критерия. Вносим сюда в кавычках слово «Еда«. А в третье поле, собственно, вносим через двоеточие адреса верхней и нижней ячеек складываемого массива. Сразу можно проверить, какие числа суммируются (справа от поля в фигурных скобках), и какой получается ответ — 437.

Нажимаем «ОК» и любуемся результатом.

Помимо суммирования, по условию можно подсчитывать количество ячеек, отвечающих какому-либо условию. За это отвечает функция СЧЁТЕСЛИ. Ее синтаксис абсолютно идентичен — указываем диапазон, критерий и, при необходимости, диапазон суммирования.

Третья идентичная функция, которая часть используется — СРЗНАЧЕСЛИ. Как вы понимаете, это расчет среднего значения в ряду чисел, отобранных по какому либо условию.

Ну, и последнее, о чем я хотел бы здесь рассказать, это функция СУММЕСЛИМН. Она может применяться в случаях, когда складывать необходимо числа, отобранные не по одному условию, а по нескольким. Ее синтаксис несколько отличается от предыдущих, но не принципиально:

Обратите внимание на каретку в правой части окна. Если ее прокрутить вниз, то будут появляться поля для новых условий, до 127. То есть, мы можем заложить 127 условий суммирования.

Ну, и конечно, расчет среднего значения и подсчет количества ячеек по нескольким условиям тоже возможны. Это, соответственно, функции СРЗНАЧЕСЛИМН и СЧЁТЕСЛИМН.