Как распределить сумму пропорционально в экселе
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Как посчитать процентное распределение в Excel по формуле
Процентное распределение отображает нам как определенное значение (например, показатель суммарного дохода) разделяется на отдельные составляющие, которые образуют его целостность.
Формула процентного распределения в Excel
Как видно ниже на рисунке ниже формула вычисления процентного распределения в Excel очень проста:
Каждую часть необходимо разделить на сумму всех частей. В данном случаи ячейка B7 содержит значение суммарного дохода всех отделов регионов. Чтобы вычислить процентное распределение суммарного дохода по всем регионам, достаточно лишь поделить значение отдельного показателя по каждому региону на суммарный доход.
Как видно формула не очень сложна. Она использует просто относительные ссылки на доходы регионов, чтобы поделить их на абсолютную ссылку на суммарный доход. Обратите внимание на абсолютную ссылку. Указанные символы доллара позволяют заблокировать ссылку на одну, конкретную ячейку. Благодаря этому адрес абсолютной ссылки не изменяется при копировании формул в другие ячейки.
Процентное распределение по динамической формуле Excel
Отдельное вычисление для хранения суммарного дохода в отдельной ячейке как константу – не обязательно. Если мы добавим в формулу функцию =СУММ(), тогда мы можем динамически выполнять вычисление процентного распределения. Ниже на рисунке показано решение для создания динамической формулы процентного распределения отдельных значений.
Примечание: Для тех, кто не в курсе – функция СУММ суммирует все значения, которые заданы в ее аргументах.
Снова обратите внимание на то, что все адреса ссылок, которые заданы в аргументах функции СУММ должны быть абсолютными (в данном случаи). Благодаря зафиксированный абсолютными ссылками диапазон ячеек в аргументе функции СУММ, не изменяться в процессе копирования формулы в другие ячейки.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
Как распределить сумму на N чисел по графику степенной функции в екселе
Есть определенная сумма, например 1000. Есть таблица формата: Нужно сумму распределить так, чтобы значение, относительно параметра «номер» возрастало. А сам график из этих данных, где ось X — это номер,а Y — значение — был в виде графика степенной функции. Я перерыл кучу сайтов, но не смог найти даже намека на формулу и логику того, как это можно сделать. Знаю метод как пропорционально распределить сумму, но по той формуле — график получается линейный. Там мы берем номер, умножаем его на сумму и делим на сумму всех номеров.Пример для нахождения первого значения по данной формуле (1*1000/55 (55 — это сумма числе от 1 до 10)) С помощью этой формулы можно добиться того, что после проделанных операций, если взять сумму второго столбца, то мы получим 1000. Но мне нужно получить не линейное распределение, а степенное так, чтобы сумма значений была равна первоначально заданной сумме
Отслеживать
задан 27 ноя 2022 в 14:39
CyberNoble CyberNoble
63 5 5 бронзовых знаков
берете квадрат от номеров, складываете, делите 1000 на полученную сумму, умножаете значения квадратов на этот коэффициент. Готовая функция врядли существует
27 ноя 2022 в 14:42
или воспользуйтесь тем, что сумма 1+2**2 + 3**2. = n(n+1)(2n+1)/6
27 ноя 2022 в 14:52
1 ответ 1
Сортировка: Сброс на вариант по умолчанию
Задача имеет отношение больше к математике, чем к электронным таблицам, но решать её будем именно в таблице Google (так поставлен вопрос).
Итак, аргументы степенной функции (числа 1..10) стоят в колонке A. Ячейке A1 содержит показатель степени нашей функции, для примера он равен 3. К исходным данным относится также требуемая сумма (1000) в ячейке C1. Далее идут вычисления.
В колонке B — результат возведения аргументов в степень с итоговой суммой в ячейке B1. В колонке C — результат «нормировки», то есть пропорциональное изменение всех слагаемых из колонки B так, чтобы их сумма составила требуемую 1000. Формула нормировки раскрыта на рисунке. Наконец, если в конечном итоге нам требуется целочисленное распределение, то необходимо округлить полученные дроби по обычным правилам, используя функцию FLOOR. Результат округления записан в колонку D, а сумма целых — в ячейку D1. У нас она оказалась 999, поскольку применено округление. Итоговый график степенной функции построен на основе значений из колонок A (аргументы) и D.
В данном случае задача решена пошагово, что удобно для понимания алгоритма. Одной функцией мне обойтись не удалось, хотя можно было написать свою пользовательскую функцию для тех же целей.
Как распределить сумму пропорционально в экселе
(3)Формулой это типа:
Ч1 = 150
Ч2 = 100
Ч3 = 50
Сумма = Ч2 / (Ч1 + Ч3)
Ч1 = Ч1 + Ч1 * Сумма
Ч3 = Ч3 + Ч3 * Сумма
Все понятно, всем спасибо.
(8) Чтобы не путаться в понятиях:
Коэффициент = Ч2 / (Ч1 + Ч3)
Ч1 = Ч1 + Ч1 * Коэффициент
Ч3 = Ч3 + Ч3 * Коэффициент
(8) А зачем так:
Ч1 = Ч1 + Ч1 * Коэффициент ?
Функция РассчитатьДельту(СуммаВходящая,ВсегоСуммаВходящая,СуммаРаспределения)
ПроцентОтОбщей=СуммаВходящая /(ВсегоСуммаВходящая/100);
Дельта=Окр(СуммаРаспределения/100*ПроцентОтОбщей,2,РежимОкругления.Окр15как20);
Возврат Дельта;
КонецФункции
+ потом если последняя сумма надо не функцию вызывать а тупо прибавлять оставшийся хвост. а то копейки остаются
мдэ. а что, в Советское время тоже образование хромало?
(12) хвост в виде лишней копейки, если коэффициент вида 0,3333333333333, лучше не к последней сумме прибавлять, а к максимальной. Аккуратнее получается. 🙂
(0) «программист»-гуманитарий?
(0) это шутка ?
(14)Можно и так 🙂
есть 2 (два) осажденных города.
в одном 150 защитников. в другом 50 защитников.
им на подмогу идет обоз с мукой.
везут 100 кг.
Задача: сколько муки нужно завезти в каждый город через подземный ход чтобы всем защитникам досталось поровну?
Решение:
Складываем всех защитников города, делим всю муку на кол-во всех защитников, находим сколько муки приходится на 1 защитника по норме.
Умножаем норму на кол-во защитников в каждом городе.
разделяем всю муку на 2 части ПРОПОРЦИОНАЛЬНО полученной норме для каждого города.
(14) В социальном государстве лучше прибавлять к минимальной сумме.
а теперь в запросе и так, чтобы копейки не терялись :)))
лично у меня — УГ получилось 🙂
(20)
declare t table (id int, b decimal)
declare @sum as decimal
insert t values (1, 100), (2, 50)
select
cast(@sum / (SUM(b) OVER()) * b as decimal(15,2)) AS t
from
t
Усложним задачу:
Есть главный документ.
Сумма документа = 150
Нал = 50
БезНал = 100
Он делится на 2 документа.
Первый Документ
Сумма = 80
Нал = ?
БезНал = ?
Второй Документ
Сумма = 70
Нал = ?
БезНал = ?
Вот как тут рассчитать оплату из главного документа ?
(22) распредели сумму в 100 пропорционально между 3 одинаковимы значениями, чтобы копейки не потерялись
*одинаковыми
типа
100 + 100 + 100 + 100 => 133,34 + 133,33 + 133,33
(14) В исходной задаче две суммы. В случае, если у одной суммы будет 0,3333333333333 — значит у другой 0,6666666666666. В общем, при округлении все будет хорошо. Вот если было три суммы (и более). Возникла бы ситуёвина, когда одному 0,3333333333333, другому 0,3333333333333 и третьему столько же. В результате копеечки могут и не бить.
(27) решение таки должно быть универсальным
(0) размазывать нужно как в (6) но с одной оговоркой
отсортировать суммы по убыванию и последней сумме добавить остаток
Суть этого гемора такова:
Определенный товар продается на ИП, остальной на ООО (одна касса работает с двумя фискальниками), до того пока не подключили банк все было нормально, вчера подключили.
т.е.
Один Чек ККМ делится на два в зависимости от товара.
Для Каждого Строка Из ТЗ Цикл
Результат = НужныйРезультат * Строка.Основание / База + Отклонение;
Отклонение = (Результат — Окр(Результат, 2));
Строка.Результат = Окр(Результат, 2);
КонецЦикла;
Это 1с розница, программно деление чека выглядит так:
Если Константы.ДваФР.Получить() Тогда
Запрос = Новый Запрос(»
|ВЫБРАТЬ * ПОМЕСТИТЬ ТабТов ИЗ &ТабТов КАК ТТ;
|ВЫБРАТЬ *
|ИЗ
| ТабТов КАК Товары
|ГДЕ
| Товары.Номенклатура.ТоварОрганизации = &Организация»);
Запрос.УстановитьПараметр(«ТабТов», Товары.Выгрузить());
ВремКассаККМ = КассаККМ;
Оплаты = Оплата.Выгрузить();
Организации = Справочники.Организации.Выбрать();
Пока Организации.Следующий() Цикл
Если Организации.Ссылка = Магазин.ОсновнойСклад.Организация Тогда;
Организация = Справочники.Организации.ПустаяСсылка();
Иначе
Организация = Организации.Ссылка;
КонецЕсли;
Запрос.УстановитьПараметр(«Организация», Организация);
Результат = Запрос.Выполнить();
Если Не Результат.Пустой() Тогда
ЭтотОбъект.Товары.Загрузить(Результат.Выгрузить());
ФР = ПолучитьСерверТО().ПолучитьИдентификаторПоИдКассы(Организация);
Если ЗначениеЗаполнено(Организация) И Не ПустаяСтрока(ФР) Тогда
КассаККМ = ПолучитьСерверТО().ПолучитьКассуККМ(ФР);
Иначе
КассаККМ = ВремКассаККМ;
КонецЕсли;
ИтогСуммы = Товары.Итог(«Сумма»);
ИтогОплат = Оплаты.Итог(«Сумма»);
Если ИтогСуммы <> ИтогОплат Тогда
ЭтотОбъект.Оплата.Загрузить(Оплаты);
Для Каждого ФормаОплат Из Оплата Цикл
КонецЦикла;
КонецЕсли;
ЗавершитьЗакрытиеЧека2(Печать, РучнойРежим, ВыбратьДокументПечати, ФР);
КонецЕсли;
КонецЦикла;
Иначе
ЗавершитьЗакрытиеЧека2(Печать, РучнойРежим, ВыбратьДокументПечати);
КонецЕсли;
(32) запросом же
(34)Если использовать временные таблицы, то можно распределить результат, а остаток оставить на максимальной/минимальной строке
Получилось как то так:
.
Для Каждого ФормаОплат Из Оплата Цикл
Если ПервыйПроход Тогда
СуммаОплаты = ИтогСуммы * Окр(ФормаОплат.Сумма / ИтогОплат, 2);
СписокОплат.Добавить(ФормаОплат.Сумма — СуммаОплаты);
ФормаОплат.Сумма = СуммаОплаты;
Иначе
ФормаОплат.Сумма = СписокОплат.Получить(ФормаОплат.НомерСтроки — 1).Значение;
КонецЕсли;
КонецЦикла;
ПервыйПроход = 0;
.
(36) В УТ или УПП глянь документ доп. расходы. Там есть алгоритм распределения суммы по кол-ву или объему