Как пропорционально распределить сумму?

Статические и динамические формулы для расчета и вычисления процентного распределения от общей суммы в Excel. Как разбить итоговую сумму на доли по каждому показателю?

Итак, классика! 

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

Таким образом все сводится к такому вот методу:

 Сумма  Количество   Распределенная сумма
100  1  16,(6) * 1 = 16,67
200  2  16,(6) * 2 = 33,33
300  3  16,(6) * 3 =  50
 итого: 600   итого: 6  итого: 100, к = 100/6 = 16,(6) 

Здесь базой является количество, сумма базы = 6, распределяемая сумма = 100. Коэффициент = распределяемая сумма / сумма базы = 100 / 6 = 16,(6) (“Шесть в скобках” – это то, как нас учили записывать периодичские дроби. Если кого-то учили иначе – проьба иметь это ввиду). Далее в каждой строке я округляю результат до копеек.

В принципе мы получили то, что хотели – распределили нужную сумму пропорционально количеству. В данном случае у нас крайне удачно получилось с округлением – в первой строке мы округлили вверх и получили одну лишнюю копейку, во второй строке мы округлили вниз и потеряли копейку. И то, что нам так повезло – это воля парня, сказавшего парню из эпиграфа сказать древним грекам все те умные вещи, о которых он им сказал…

Давайте рассмотрим случай, когда тот парень был к нам не так благосклонен, а именно – давайте распределим 10 на 3:

Сумма  Количество   Распределенная сумма
100  1  3,(3) * 1 = 3,33
200  1  3,(3) * 1 = 3,33 
250  1  3,(3) * 1 = 3,33 – добавим разницу 0,01 = 3,34
 итого: 550   итого: 3  итого: 10? нет! 9,99 + 0,01 = 10, к = 10/3 = 3,(3) 

В итоге у нас не хватило одной копейки. Для того, чтобы решить эту проблему, необходимо учесть остаточек в конце. У нас распределенная сумма получилась равна 9,99, а сумма, которую нужно распределить – 10. Разницу, обычно, добавляют к последней строке. Т.е. в последней строке у нас будет 3,34, “чтобы не нарушать отчетности” (с).

Все хрошо, пока потерянная в ходе округления сумма мала и не играет большой роли. Но если мы попытаемся таким же образом распределить 10 на 30 строк, то внезапно окажется, что к последней строке нам нужно прибавить уже не 1 копейку, а 10. Можно, конечно, прибавить сумму остатка к последней строке:

 № п/п    Сумма    Количество   Распределенная сумма
1 100  1  0,(3) * 1 = 0,33
2 200  1  0,(3) * 1 = 0,33 
3 250  1  0,(3) * 1 = 0,33 – добавим разницу 0,01 = 3,34
… 
29 200 1  0,(3) * 1 = 0,33 
30 100 1  0,(3) * 1 = 0,33 
     итого: 30  итого: 10? нет! 9,90! 

В последней строке в итоге будет сумма 0,33 + 0,10 = 0,43. Если мы распределяем какие-нибудь ксвенные затраты на количество выпуска, то для каждой статьи затрат может набраться весьма большое отклонение, которое все целиком упадет на последнюю строчку. Таким образом продукт, выпущенный нами в последнюю очередь, вберет в свою себестоимость все те отклонения и станет “золотым” )))

Если мы будем дораспределять остаток, то, в принципе, мы также можем попасть на округление и дораспределять нам придется до тх пор, пока все копейки не израсходуются. Это, как мне кажется, несколько неудобно, непрозрачно да и затратно.

Пропорциональное деление

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

Чтобы разделить число на части пропорционально нескольким данным числам, надо разделить его на сумму этих чисел и частное умножить на каждое из них.

Формула процентного распределения в Excel

Как видно ниже на рисунке ниже формула вычисления процентного распределения в Excel очень проста:

Статическая формула.

Каждую часть необходимо разделить на сумму всех частей. В данном случаи ячейка B7 содержит значение суммарного дохода всех отделов регионов. Чтобы вычислить процентное распределение суммарного дохода по всем регионам, достаточно лишь поделить значение отдельного показателя по каждому региону на суммарный доход.

Как видно формула не очень сложна. Она использует просто относительные ссылки на доходы регионов, чтобы поделить их на абсолютную ссылку на суммарный доход. Обратите внимание на абсолютную ссылку. Указанные символы доллара позволяют заблокировать ссылку на одну, конкретную ячейку. Благодаря этому адрес абсолютной ссылки не изменяется при копировании формул в другие ячейки.

Другие статьи на тему патента

Как платить и делить страховые взносы, если приобретено два патента здесь.

Патент проще, чем УСН? Что платить и сдавать на двух режимах здесь.

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

Про совмещение УСН и патента по одному виду деятельности статья здесь.

Про совмещение по одному виду деятельности в одному субъекте РФ, но по разным точкам в розничной торговле читайте здесь.

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

  • S.png

Афиняне! Повсему вижу я, что Вы как-то по-особеному набожны, ибо проходя и осматривая Ваши святыни, я наткнулся и на жертвенник неведомому богу.

Где-то в библии в адрес древних греков.

В общем и целом написать данную статью подвигла меня очередная лекция на тему себестоимости. Кстати, крайне рекомендую курс для ИТ-менеджеров в открытом университете, который там сейчас находится в открытом доступе.

Новое решение!

Давным-давно, кажется в позапрошлую работу, меня попросили создать обработку, которая бы перекраивала контуры полей, перераспределяя на их новую площадь какие-то старые остатки на счетах учета затрат на дату распределения. Там как раз сумма распределялась между новыми площадями пропорционально новому метражу. Звучит пространно, но примите на веру (как древние греки), что это относится к обсуждаемой нами задаче распределения суммы по базе. И тогда я как раз “родил” (ага, прям как Авраам Исаака) алгоритм распределения, после которого нет остатка. Странно, но тогдашний мой руководитель так и не понял суть алгоритма, хотя после теста сказал, что все работает и оставил как есть. Западные программисты в таких случаях просто стараются не использовать подобные алгоритмы, так что честь и хвала программистам российским, которые используют и то, в чем не понимают )))

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

 № п/п    Сумма    Количество   Распределенная сумма   Плавающий коэффициент   По-новому распределенная сумма 
1 100  1  0,(3) * 1 = 0,33  10/30 = 0,(3)  0,33
2 200  1  0,(3) * 1 = 0,33  9,67/29 = 0,333448…   0,33 
3 250  1  0,(3) * 1 = 0,33  9,34/28 = 0,333571…   0,33
…     
29 200 1  0,(3) * 1 = 0,33   0,67/2 = 0,34   0,34
30 100 1  0,(3) * 1 = 0,33   0,33/1 = 0,33   0,33 
      итого: 30  итого: 9,90    итого: 10

Таким образом у нас больше нет остатка!

Пример 1. Распределение премии

Предположим, что Вы начальник производственного отдела и Вам предстоит по-честному распределить премию в сумме 100 000 руб. между сотрудниками отдела пропорционально их должностным окладам. Другими словами Вам требуется подобрать коэффициент пропорциональности для вычисления размера премии по окладу.

Первым делом создаём таблицу с исходными данными и формулами, с помощью которых должен быть получен результат. В нашем случае результат — это суммарная величина премии. Очень важно, чтобы целевая ячейка (С8) посредством формул была связана с искомой изменяемой ячейкой (Е2). В примере они связаны через промежуточные формулы, вычисляющие размер премии для каждого сотрудника (С2:С7).

image010.jpg

Теперь запускаем Поиск решения и в открывшемся диалоговом окне устанавливаем необходимые параметры. Внешний вид диалоговых окон в разных версиях несколько различается:

Начиная с Excel 2010

image012.jpg

image013.jpg

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

2. Варианты оптимизации: максимальное возможное значение, минимальное возможное значение или конкретное значение. Если требуется получить конкретное значение, то его следует указать в поле ввода

3. Изменяемых ячеек может быть несколько: отдельные ячейки или диапазоны. Собственно, именно в них Excel перебирает варианты с тем, чтобы получить в целевой ячейке заданное значение

4. Ограничения задаются с помощью кнопки Добавить. Задание ограничений, пожалуй, не менее важный и сложный этап, чем построение формул. Именно ограничения обеспечивают получение правильного результата. Ограничения можно задавать как для отдельных ячеек, так и для диапазонов. Помимо всем понятных знаков =, >=,

image016.jpg

5. Кнопка, включающая итеративные вычисления с заданными параметрами.

После нажатия кнопки Найти решение (Выполнить) Вы уже можете видеть в таблице полученный результат. При этом на экране появляется диалоговое окно Результаты поиска решения.

Начиная с Excel 2010

image018.jpg

image020.jpg

Если результат, который Вы видите в таблице Вас устраивает, то в диалоговом окне Результаты поиска решения нажимаете ОКи фиксируете результат в таблице. Если же результат Вас не устроил, то нажимаете Отменаи возвращаетесь к предыдущему состоянию таблицы.

Решение данной задачи выглядит так

image022.jpg

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

Разберём еще одну задачу оптимизации (получение максимальной прибыли)

Пример 2. Мебельное производство (максимизация прибыли)

Фирма производит две модели А и В сборных книжных полок.

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

Для каждого изделия модели А требуется 3 м² досок, а для изделия модели В — 4 м². Фирма может получить от своих поставщиков до 1700 м² досок в неделю.

Для каждого изделия модели А требуется 12 мин машинного времени, а для изделия модели В — 30 мин. в неделю можно использовать 160 ч машинного времени.

Сколько изделий каждой модели следует выпускать фирме в неделю для достижения максимальной прибыли, если каждое изделие модели А приносит 60 руб. прибыли, а каждое изделие модели В — 120 руб. прибыли?

Порядок действий нам уже известен.

Сначала создаем таблицы с исходными данными и формулами. Расположение ячеек на листе может быть абсолютно произвольным, таким как удобно автору. Например, как на рисунке

image024.jpg

Запускаем Поиск решения и в диалоговом окне устанавливаем необходимые параметры

image026.jpg

1. Целевая ячейка F7 содержит формулу для расчёта прибыли

2. Параметр оптимизации — максимум

3. Изменяемые ячейки F3:G3

4. Ограничения: найденные значения должны быть целыми, неотрицательными; общее количество машинного времени не должно превышать 160 ч (ссылка на ячейку D9); общее количество сырья не должно превышать 1700 м² (ссылка на ячейку D8). Здесь вместо ссылок на ячейки D8 и D9 можно было указать числа, но при использовании ссылок какие-либо изменения ограничений можно производить прямо в таблице

5. Нажимаем кнопку Найти решение (Выполнить) и после подтверждения получаем результат

image028.jpg

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

image030.jpg

И это несмотря на то, что было задано ограничение целое. В таких случаях можно попробовать настроить параметры Поиска решения. Для этого в окне Поиск решения нажимаем кнопку Параметрыи попадаем в одноимённое диалоговое окно

image032.jpg

Первый из выделенных параметров отвечает за точность вычислений. Уменьшая его, можно добиться более точного результата, в нашем случае — целых значений. Второй из выделенных параметров (доступен, начиная с версии Excel 2010) даёт ответ на вопрос: как вообще могли получиться дробные результаты при ограничении целое? Оказывается Поиск решения это ограничение просто проигнорировал в соответствии с установленным флажком.

Пример 3. Транспортная задача (минимизация затрат)

На заказ строительной компании песок перевозиться от трех поставщиков (карьеров) пяти потребителям (строительным площадкам). Стоимость на доставку включается в себестоимость объекта, поэтому строительная компания заинтересована обеспечить потребности своих стройплощадок в песке самым дешевым способом.

Дано: запасы песка на карьерах; потребности в песке стройплощадок; затраты на транспортировку между каждой парой «поставщик-потребитель».

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

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

image033.jpg

В серых ячейках формулы суммы по строкам и столбцам, а в целевой ячейке формула для подсчёта общих затрат на транспортировку.

Запускаем Поиск решения и устанавливаем необходимые параметры (см. рисунок)

image034.jpg

Нажимаем Найти решение (Выполнить) и получаем результат, изображенный ниже

image035.jpg

Иногда транспортные задачи усложняются с помощью дополнительных ограничений. Например, по каким-то причинам невозможно возить песок с карьера 2 на стройплощадку №3. Добавляем ещё одно ограничение $D$13=0. И после запуска Поиска решения получаем другой результат

image036.jpg

И последнее, на что следует обратить внимание, это выбор метода решения. Если задача достаточно сложная, то для достижения результата может потребоваться подобрать метод решения

Через практическое мессианство! Или перейдем на ты к практике.

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

Процедура РаспределитьСуммуПропорциональноБазе(Таблица, ИмяКолонкиБазы, ИмяКолонкиДляРаспределения, Сумма) СуммаБазы = Таблица.Итог(ИмяКолонкиБазы); Для каждого СтрокаТаблицы ИЗ Таблица Цикл К = Сумма / СуммаБазы; СуммаКРаспределению = Окр(СтрокаТаблицы[ИмяКолонкиБазы] * К, 2); СтрокаТаблицы[ИмяКолонкиДляРаспределения] = СтрокаТаблицы[ИмяКолонкиДляРаспределения] + СуммаКРаспределению; Сумма = Сумма – СуммаКРаспределению; СуммаБазы = СуммаБазы – СтрокаТаблицы[ИмяКолонкиБазы] КонецЦиклаКонецПроцедуры

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

В качестве постскриптума…

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

5 основ Excel (обучение): как написать формулу, как посчитать сумму, сложение с условием, счет строк и пр.

excel.gifЗдравствуйте!

Многие кто не пользуются Excel — даже не представляют, какие возможности дает эта программа! ☝

Подумать только: складывать в автоматическом режиме значения из одних формул в другие, искать нужные строки в тексте, создавать собственные условия и т.д. — в общем-то, по сути мини-язык программирования для решения «узких» задач (признаться честно, я сам долгое время Excel не рассматривал за программу, и почти его не использовал) .

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

То есть эта статья будет что-то мини гайда по обучению самому нужному для работы (точнее, чтобы начать пользоваться Excel и почувствовать всю мощь этого продукта!) .

Возможно, что прочти подобную статью лет 17-20 назад, я бы сам намного быстрее начал пользоваться Excel (и сэкономил бы кучу своего времени для решения «простых» задач. 👌

uskwin.png

Как посчитать процент от суммы

Тоже довольно распространенный вопрос, с которым часто сталкиваюсь. Вообще, насколько я себе представляю, возникает он чаще всего — из-за того, что люди путаются и не знают, что от чего ищут процент (да и вообще, плохо понимают тему процентов (хотя я и сам не большой математик, и все таки. ☝) ).

👉 В помощь!

Как посчитать проценты: от числа, от суммы чисел и др. [в уме, на калькуляторе и с помощью Excel] — заметка для начинающих

Самый простой способ, в котором просто невозможно запутаться — это использовать правило «квадрата», или пропорции.

Вся суть приведена на скрине ниже: если у вас есть общая сумма, допустим в моем примере это число 3060 — ячейка F8 (т.е. это 100% прибыль, и какую то ее часть сделал «Саша», нужно найти какую. ).

По пропорции формула будет выглядеть так: =F10*G8/F8 (т.е. крест на крест: сначала перемножаем два известных числа по диагонали, а затем делим на оставшееся третье число).

В принципе, используя это правило, запутаться в процентах практически невозможно 👌.

Primer-resheniya-zadach-s-protsentami.gif

Пример решения задач с процентами

PS

Собственно, на этом я завершаю данную статью. Не побоюсь сказать, что освоив все, что написано выше (а приведено здесь всего лишь «пяток» формул) — Вы дальше сможете самостоятельно обучаться Excel, листать справку, смотреть, экспериментировать, и анализировать. 👌

Скажу даже больше, все что я описал выше, покроет многие задачи, и позволит решать всё самое распространенное, над которым часто ломаешь голову (если не знаешь возможности Excel) , и даже не догадывается как быстро это можно сделать. ✔

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...