Сумма нарастающим итогом в Excel считается формулой =СУММ($B$2:B2), протянутой вниз: начало диапазона закреплено знаками $, а конец «растёт» вместе со строкой. Доля строки в общей сумме (например, доля товара в общей выручке) — =B2/СУММ($B$2:$B$5), а доля нарастающим итогом — =СУММ($B$2:B2)/СУММ($B$2:$B$5) в процентном формате. Без формул и промежуточных столбцов долю нарастающим итогом считает процедура !SEMTools.
При анализе данных важно не только суммировать значения, но и понимать, какую долю занимает каждая строка в общем объёме — в нарастающем виде. Это помогает оценить вклад элементов в общую сумму и используется, например, в анализе продаж, ABC-анализе или при построении диаграммы Парето.
Пример доли нарастающим итогом: выручка по товарам
Выручка по четырём товарам — в столбце B. Нарастающий итог показывает, сколько принесли товары с первого по текущий, доля — вклад каждого товара в общую выручку 1000, а доля нарастающим итогом — какой процент выручки дают товары с первого по текущий:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Товар | Выручка | Нарастающий итог | Доля | Доля нарастающим итогом |
| 2 | A | 400 | 400=СУММ($B$2:B2) | 40%=B2/СУММ($B$2:$B$5) | 40%=C2/СУММ($B$2:$B$5) |
| 3 | B | 300 | 700=СУММ($B$2:B3) | 30%=B3/СУММ($B$2:$B$5) | 70%=C3/СУММ($B$2:$B$5) |
| 4 | C | 200 | 900=СУММ($B$2:B4) | 20%=B4/СУММ($B$2:$B$5) | 90%=C4/СУММ($B$2:$B$5) |
| 5 | D | 100 | 1000=СУММ($B$2:B5) | 10%=B5/СУММ($B$2:$B$5) | 100%=C5/СУММ($B$2:$B$5) |
Товары A и B вместе дают 70% выручки. Ячейкам с долями задан процентный формат (Ctrl+Shift+5), иначе вместо 40% будет 0,4.
В английском Excel: =SUM($B$2:B2)/SUM($B$2:$B$5)
Нарастающий итог в «умной» таблице
Если данные оформлены как таблица (Ctrl+T), формула с $B$2:B2 может «сломаться» при добавлении строк. Надёжнее =СУММ(ИНДЕКС([Выручка];1):[@Выручка]) — диапазон от первой строки столбца до текущей. В Excel 365 весь столбец нарастающего итога сразу даёт =SCAN(0;B2:B5;LAMBDA(a;x;a+x)).
Как посчитать долю нарастающим итогом формулами
Доля нарастающим итогом с !SEMTools
Если вы используете надстройку !SEMTools для Excel, вы можете получить долю накопленным итогом сразу, без промежуточного вычисления нарастающей суммы:
- Выделите диапазон значений;
- Перейдите на вкладку !SEMTools;
- Зайдите в меню "Извлечь" - "Посчитать"
- Нажмите команду «Доля нарастающим итогом».
Если включена галочка "выводить в соседний столбец", столбец с долей накопительным итогом для каждой строки значений будет выведен справа от исходного, если нет - данные будут заменены на месте. Второй вариант тоже может быть удобным, если вы привыкли при работе с надстройкой копировать исходный столбец и обрабатывать его копию.
Надстройка возвращает только значения, без формул.
Где используется доля нарастающим итогом
- Анализ продаж — какие товары дают 80% выручки;
- Оценка трафика — какие страницы приносят основную долю визитов;
- Финансовые отчёты — распределение по клиентам, регионам, категориям;
- ABC-анализ — выделение ключевых элементов по принципу Парето.
Советы: сортировка и диаграмма Парето
- Перед расчётом обычно стоит сортировать значения по убыванию — иначе результат может быть некорректным;
- Для графиков удобно использовать комбинированную диаграмму: столбцы + линия;
- Если вы часто делаете подобный анализ — автоматизация задачи может сэкономить вам немало времени.
Почему нарастающий итог считается неверно
- Во всех строках одно и то же число. Не закреплено начало диапазона: должно быть $B$2:B2, а не B2:B2 — иначе при протягивании оба конца сдвигаются.
- Доля последней строки не 100%. Делитель не закреплён ($B$2:$B$5) или в общую сумму не попали все строки. Проверьте диапазон в СУММ.
- Вместо процентов дроби 0,4 и 0,7. Ячейкам не задан процентный формат.
- Доля нарастающим итогом «скачет». Для ABC-анализа и диаграммы Парето данные нужно отсортировать по убыванию — иначе крупные позиции окажутся в конце.
- В сумму попала строка «Итого». Исключите итоговую строку из диапазона, иначе общая сумма удвоится.
Нарастающий итог в Excel — частые вопросы
Как посчитать нарастающий итог в Excel?
Формулой =СУММ($B$2:B2) в первой строке, протянутой вниз до конца данных.
Как найти долю в общей выручке?
Разделите выручку строки на общую сумму: =B2/СУММ($B$2:$B$5), и задайте процентный формат.
Как посчитать долю нарастающим итогом?
Формулой =СУММ($B$2:B2)/СУММ($B$2:$B$5) или процедурой !SEMTools «Доля нарастающим итогом» — без промежуточного столбца.
Как сделать нарастающий итог в сводной таблице?
В параметрах поля значений выберите «Дополнительные вычисления → С нарастающим итогом в поле» (или «% от суммы с нарастающим итогом в поле»).
Посчитать долю нарастающим итогом для любого диапазона — с выводом в соседний столбец или прямо на месте — можно в пару кликов с !SEMTools, без промежуточных сумм и закреплённых ссылок.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как посчитать нарастающий итог в Excel
| Задача | Формула |
|---|---|
| Сумма нарастающим итогом | =СУММ($B$2:B2) |
| Доля в общей сумме | =B2/СУММ($B$2:$B$5) |
| Доля нарастающим итогом | =СУММ($B$2:B2)/СУММ($B$2:$B$5) |
| Весь столбец сразу (Excel 365) | =SCAN(0;B2:B5;LAMBDA(a;x;a+x)) |
| Без формул | Процедура !SEMTools |
Надеюсь, на этой странице вы нашли то, что искали. Вас наверняка заинтересуют и похожие операции: ABC-анализ в Excel и статистические метрики.