Нужно разделить премию на три равные части, но с условием, что одна часть идет в отдел продаж, другая — в отдел маркетинга, а третья поровну между всеми сотрудниками этих отделов? В Excel это решается одной формулой на основе функции ВПР (или XLOOKUP) и простой арифметики. Сейчас покажу на примере с квартальным бонусом в 1 200 000 рублей.
Исходные данные:
- Общий бонусный фонд (B2):
1 200 000 ₽ - Отделы (A5:A7): Продажи, Маркетинг, Все сотрудники
- Доля от общей суммы (C5:C7): 1/3, 1/3, 1/3 (т.е. по ~33.33% каждому направлению).
Формула для расчета доли каждого отдела
В ячейку D5, напротив «Продажи», вводим формулу и растягиваем ее вниз на три строки:
=ВПР(A5; $A$5:$C$7; 3; ЛОЖЬ) * $B$2
Как это работает:
ВПР(A5; ...)ищет название отдела из ячейкиA5(«Продажи») в первом столбце указанного диапазона ($A$5:$C$7).3— это номер столбца в диапазоне, откуда нужно вернуть значение. Нам нужен столбец «Доля» (C).ЛОЖЬозначает точный поиск.- Формула находит для «Продаж» долю
1/3и умножает ее на общую сумму из$B$2(1 200 000 * 1/3 = 400 000).
Абсолютные ссылки ($) фиксируют общую сумму и таблицу с долями, чтобы они не «съезжали» при копировании формулы. Для отделов маркетинга и всех сотрудников расчет будет идентичным: каждый получит свою треть от общего фонда.
А если доли распределения неравные?
Чаще всего части не равны. Например, отделу продаж — 50%, маркетингу — 30%, всем сотрудникам — 20%. В этом случае просто измените значения долей в столбце C с дробей на проценты: 0.5, 0.3, 0.2. Формула продолжит работать без изменений и посчитает корректные суммы: 600 000 ₽, 360 000 ₽ и 240 000 ₽ соответственно.
Для более сложных расчетов, где нужно делить суммы между десятками статей или проектов, используйте функцию СУММПРОИЗВ. Она незаменима, когда условия несколько. Если же вам нужно просто быстро разделить ФИО на три столбца в Excel и Google Таблицах, у нас есть отдельная инструкция.
Автоматизация с помощью XLOOKUP (Excel 2026/365)
В современных версиях Excel (2026, Microsoft 365) логичнее использовать XLOOKUP. Формула в D5 будет выглядеть так:
=XLOOKUP(A5; $A$5:$A$7; $C$5:$C$7; 0) * $B$2
Она делает то же самое, но более наглядна: ищет A5 в диапазоне $A$5:$A$7 и возвращает соответствующее значение из $C$5:$C$7.
Как избежать ошибок при разделении
- Проверьте итог долей. Убедитесь, что сумма всех долей в столбце C равна 1 (или 100%). Используйте простую формулу
=СУММ(C5:C7). Если сумма не равна 1, общий бонусный фонд будет распределен неверно. - Форматируйте как деньги. Выделите ячейки с результатами (
D5:D7), нажмитеCtrl+1, выберите формат «Денежный» или «Финансовый». Это предотвратит отображение 400000 как 400000.00, а покажет 400 000,00 ₽. - Защитите ячейки с формулами. Чтобы кто-то случайно не удалил формулу, выделите эти ячейки, снова
Ctrl+1, перейдите на вкладку «Защита» и поставьте галочку «Защищаемая ячейка». Затем защитите лист через меню «Рецензирование».
Ручной расчет и проверка таких распределений отнимают время и чреваты арифметическими ошибками. Гораздо надежнее использовать готовый, защищенный шаблон, где все формулы уже прописаны, а ячейки с логикой заблокированы от редактирования. Вы просто вводите общую сумму и доли — результат, включая проверочный итог, появляется мгновенно.
Скачайте наш шаблон «Калькулятор распределения премий и бюджетов» — в нем уже реализована эта логика с проверкой, защитой ячеек и настраиваемыми статьями затрат. Вам останется только подставить свои цифры.
Чек-лист для безошибочного распределения
- Общая сумма введена в одной ячейке (например, B2).
- Таблица с отделами и долями создана (столбцы A и C).
- Сумма всех долей (C5:C7) равна 1 (проверка формулой
=СУММ(C5:C7)). - В ячейке D5 введена формула
=ВПР(A5;$A$5:$C$7;3;ЛОЖЬ)*$B$2или=XLOOKUP(A5;$A$5:$A$7;$C$5:$C$7;0)*$B$2. - Формула растянута на все строки отделов.
- Ячейки с результатами отформатированы как денежные.
- Итоговая сумма распределения (
=СУММ(D5:D7)) равна исходной сумме в B2.
