Вывод итогов столбцов по строкам в MS EXCEL

Пусть имеется 5 столбцов с данными. В каждом столбце по 10 чисел, необходимо найти сумму чисел в каждом столбце. Обычно итоговое значение выводится внизу столбца или над его заголовком, поэтому все итоговые значения располагаются в одной строке. Но когда столбцов слишком много, то удобнее итоговые значения столбцов выводить в один отдельный столбец, а не располагать их под данными. Напишем формулу, которая помогает сделать это преобразование.

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

Но мы не будем просто транспонировать строку итогов, а вычислим итоги и расположим их в столбце. Это можно сделать с помощью формулы 

=СУММ(СМЕЩ($B$9;;СТРОКА()-СТРОКА($B$23);$B$6))

В ячейке В6 содержится количество значений, содержащихся в отдельном столбце, а ячейка В9 это - левый верхний угол диапазона с числами (верхняя ячейка первого столбца). 

Формулу нужно ввести в ячейку, которая будет содержать самое верхнее значение итогов, т.е. итог первого столбца. При протягивании формулы вниз значение третьего аргумента функции СМЕЩ(), ответственного за смещение по столбцам относительно В9, будет увеличиваться с каждой строкой. Поэтому в каждой новой строке мы будем иметь итог следующего столбца справа (см. файл примера).

Более изощренная формула для расчета итогов выглядит так:

=СУММ(ДВССЫЛ(АДРЕС(СТРОКА($B$9);СТРОКА(B9)-СТРОКА($B$9)+СТОЛБЕЦ($B$9))):ДВССЫЛ(АДРЕС(СТРОКА($B$9)+$B$6-1;СТРОКА(B9)-СТРОКА($B$9)+СТОЛБЕЦ($B$9))))

В этой формуле граничные ячейки для суммирования определяются напрямую с помощью функции АДРЕС().

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (1 оценка)

Комментарии

MCH

Вариант, формула массива, вводится сразу в несколько ячеек:
=МУМНОЖ(ТРАНСП(B9:F18);СТРОКА(B9:F18)^0)