Как распределить сумму на N чисел по графику степенной функции в екселе
Есть определенная сумма, например 1000. Есть таблица формата:
Нужно сумму распределить так, чтобы значение, относительно параметра "номер" возрастало. А сам график из этих данных, где ось X - это номер,а Y - значение - был в виде графика степенной функции.
Я перерыл кучу сайтов, но не смог найти даже намека на формулу и логику того, как это можно сделать.
Знаю метод как пропорционально распределить сумму, но по той формуле - график получается линейный. Там мы берем номер, умножаем его на сумму и делим на сумму всех номеров.Пример для нахождения первого значения по данной формуле (1*1000/55 (55 - это сумма числе от 1 до 10))
С помощью этой формулы можно добиться того, что после проделанных операций, если взять сумму второго столбца, то мы получим 1000. Но мне нужно получить не линейное распределение, а степенное так, чтобы сумма значений была равна первоначально заданной сумме
Ответы (1 шт):
Задача имеет отношение больше к математике, чем к электронным таблицам, но решать её будем именно в таблице Google (так поставлен вопрос).
Итак, аргументы степенной функции (числа 1..10) стоят в колонке A. Ячейке A1 содержит показатель степени нашей функции, для примера он равен 3. К исходным данным относится также требуемая сумма (1000) в ячейке C1. Далее идут вычисления.
В колонке B - результат возведения аргументов в степень с итоговой суммой в ячейке B1. В колонке C - результат "нормировки", то есть пропорциональное изменение всех слагаемых из колонки B так, чтобы их сумма составила требуемую 1000. Формула нормировки раскрыта на рисунке. Наконец, если в конечном итоге нам требуется целочисленное распределение, то необходимо округлить полученные дроби по обычным правилам, используя функцию FLOOR. Результат округления записан в колонку D, а сумма целых - в ячейку D1. У нас она оказалась 999, поскольку применено округление. Итоговый график степенной функции построен на основе значений из колонок A (аргументы) и D.
В данном случае задача решена пошагово, что удобно для понимания алгоритма. Одной функцией мне обойтись не удалось, хотя можно было написать свою пользовательскую функцию для тех же целей.