Главная → Как сделать в Excel → Распределить сумму пропорционально

Распределить сумму пропорционально в Excel

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

Коротко

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

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

В файле распределяемая сумма вводится в жёлтую ячейку C4. Позиции — «Товар А», «Товар Б», «Товар В», «Товар Г» — идут в столбце B, их база распределения (стоимость позиции) — в столбце C, строки 8:27. Столбец D считает долю, столбец E — распределённую сумму. Строка 28 с итогами: C28 — сумма баз, E28 — сумма распределения, а проверочная ячейка рядом всегда показывает ноль.

То же самое формулой в Excel

=ЕСЛИ($B8="";"";$C8/$C$28) — доля позиции: её база, делённая на сумму всех баз в C28

=ЕСЛИ($B8="";"";ЕСЛИ(СТРОКА()=МАКС(ЕСЛИ($B$8:$B$27<>"";СТРОКА($B$8:$B$27)));$C$4-СУММ($E$8:$E7);ОКРУГЛ($C$4*$D8;2)))

— доля × сумма с округлением до копеек; последняя заполненная строка берёт остаток, а не свою долю

=E28-C4 — проверка: итог распределения минус исходная сумма всегда должен быть ноль

Распределение суммы: вводите сумму и базы — доли считаются сами

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

Забрать в Telegram Забрать в MAX

Файл raspredelit-summu.xlsx · формулы работают в Excel 2016 и новее и в Google Таблицах · без макросов · формулы проходят автопроверку перед публикацией

Лист файла «Распределить сумму пропорционально в Excel»: Позиция, База распределения, Доля, Распределено — Так выглядит файл. Картинка собрана из него же при сборке сайта: числа в ней те, что посчитает Excel.

Пошагово

Введите распределяемую сумму. В отдельную ячейку, скажем C4, впишите то, что разносите: сумму доставки, размер скидки, накладные расходы за период.
Выпишите позиции и их базу. В столбец B — названия позиций, в столбец C напротив — базу распределения: стоимость, вес, количество. По чему делить — по тому и база.
Посчитайте сумму баз. Под столбцом баз поставьте =СУММ(C8:C27). В примере это C28 — на неё будут ссылаться доли.
Доля. В столбце D: =ЕСЛИ($B8="";"";$C8/$C$28). База закреплена знаком $, чтобы при протягивании вниз ссылка на итог не сползла. Пустые строки ЕСЛИ оставит пустыми.
Распределение с остатком. В столбце E — длинная формула из блока выше. Обычные строки берут ОКРУГЛ(сумма × доля; 2), а последняя заполненная строка — остаток: сумма минус всё, что разнесено выше. Так копейки досводятся.
Проверьте на ноль. Рядом с итогом поставьте =E28-C4. Ноль — распределение сошлось с исходной суммой. Любое другое число — ошибка в базах или в диапазонах.

Где обычно ломается

Главная тонкость приёма — почему последняя строка считается иначе, чем остальные.

Округление до копеек не сходится с суммой. Разнесите 1000 ₽ на три равные позиции — по формуле выйдет 333,33 ₽ трижды, а это 999,99 ₽. Одна копейка потерялась на округлении. На сотне позиций так набегает и рубль. Поэтому последнюю строку нельзя округлять наравне с прочими.
Последняя строка — это остаток, а не доля. Формула вычисляет $C$4-СУММ($E$8:$E7): берёт распределяемую сумму и вычитает всё, что уже разнесено в строках выше. Что осталось — то и приходится на последнюю позицию. Копейки округления оседают в ней, и итог сходится точно.
Проверочная строка всегда должна быть ноль. E28-C4 — это сумма распределения минус исходная сумма. Пока приём работает, там ноль. Увидели не ноль — сбит диапазон баз или итоговая строка налезла на данные; ищите там, а не подгоняйте цифры вручную.
Все базы нулевые или пустые — делить не на что. Если сумма всех баз равна нулю, доля $C8/$C$28 даёт ошибку деления на ноль. ЕСЛИ($B8="";"";…) отсекает пустые строки, но нулевые базы проверьте сами: без базы пропорцию не построить.
Как определяется «последняя строка». МАКС(ЕСЛИ($B$8:$B$27<>"";СТРОКА($B$8:$B$27))) находит номер самой нижней заполненной строки. Поэтому неважно, сколько позиций вы заполнили — остаток всегда садится на последнюю из них, а пустые строки ниже не мешают.

Частые вопросы

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

Пропорционально — значит в той же пропорции, в какой относятся базы. Сложите все базы, поделите базу каждой позиции на эту сумму — получите долю. Умножьте долю на распределяемую сумму. Чтобы итог сошёлся копейка в копейку, последней позиции отдайте остаток: распределяемую сумму минус всё, что разнесено на позиции выше.

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

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

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

Так же, как по стоимости: меняется только то, что стоит в столбце базы. Количество штук даёт разбивку по числу единиц, вес нетто годится для фрахта, площадь — для аренды, а если делят по своей шкале, в базу ставят коэффициенты. Формулы доли, распределения и остатка при этом не меняются.

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

Равномерно — это частный случай: все базы равны, поэтому равны и доли. И на нём же виднее всего главная ловушка приёма. Разнесите 1000 ₽ на три позиции — по формуле выйдет 333,33 ₽ трижды, а это 999,99 ₽: копейка потерялась на округлении. Поэтому последняя заполненная строка получает не свою округлённую долю, а остаток — сумму минус всё разнесённое выше, — и проверочная ячейка показывает ровно ноль. Все формулы приёма — ЕСЛИ, СУММ, ОКРУГЛ, СТРОКА, МАКС — работают и в Excel 2016, и в Google Таблицах; специального ввода массива длинная формула распределения не требует.

Связанные страницы