Сумма всего столбца
=СУММ( A:А )
Если вы хотите, просуммировать весь столбец без подачи верхней или нижней границы, вы можете использовать функцию СУММ с конкретным синтаксисом диапазона для всего столбца.
Excel поддерживает «полный столбец» и «полная строка» ссылки, как это:
=СУММ( A:А ) // сумма всего столбца A
=СУММ( 3 : 3 ) // сумма всех строк 3
Вы можете увидеть, как это работает самостоятельно, набрав «A: A», «3: 3» и т.д., в поле Имя (слева от строки формул) и ударяя возвращения — Excel будет выбирать весь столбец или строку.
Полные ссылки столбцов и строк являются простым способом ссылки на данные, которые могут изменяться в размерах, но вы должны быть уверены, что вы случайно не включаете дополнительные данные. Например, если вы используете = СУММ (A: A), чтобы просуммировать весь столбец А и столбец А также включает в себя дату где-то (в любом месте), эта дата будет включена в сумму.
Сумма столбцов на основе смежных критериев
=СУММПРОИЗВ ( — ( диапазон1 = критерии ); диапазон2 )
Суммируя столбцы на основе критериев, указанных в соседних столбцах, можно использовать формулу, основанную на функции СУММПРОИЗВ.
=СУММПРОИЗВ(—($B5:$H5=J$4); $C5:$I5)
СУММПРОИЗВ умножает, затем суммирует произведения двух массивов: массив1 и массив2. Массив1 настроен выступать в качестве «фильтра», чтобы пропустить только те значения, которые удовлетворяют критериям.
Массив1 использует диапазон, который начинается в первом столбце, который содержит значения, которые должны пройти критерии. Эти «критерии ценности» находятсят в колонке слева, и в непосредственной близости к ним «значения данных».
Критерии применяются в качестве простого теста, который создает массив истинных и ложных значений:
— ( $ B5: $ H5 = J$ 4 )
Этот кусочек формулы «тестирует» каждое значение в первом массиве с помощью поставляемых критериев, а затем использует двойное отрицание (-), превращающее получившиеся Истинные и ложные значения в единицы и нули. Результат выглядит следующим образом:
{1;0;0;0;1;0;1}
Обратите внимание, что единицы соответствуют колонкам 1,5, и 7, которые соответствуют критериям «А».
Для массив2 внутри СУММПРОИЗВ, мы используем диапазон, «сдвинутый» на один столбец вправо. Этот диапазон начинается с первого столбца содержащего значения суммы и заканчивается последней колонке, которая содержит значения суммы.
Так, в примере формулы в J5, после того, как массивы были заселены, мы имеем:
= СУММПРОИЗВ ( { 1 ; 0 ; 0 ; 0 ; 1 ; 0 ; 1 } ; { 1 ; «Б» ; 1 ; «А» ; 1 ; «А» ; 1 } )
Так как СУММПРОИЗВ запрограммирован специально, чтобы игнорировать ошибки, возникающие в результате умножения значения текста, окончательный массив выглядит следующим образом:
{1;0;0;0;1;0;1}
Единственные «выжившие» значения умножения являются теми, которые соответствуют 1 внутри массив1. Вы можете думать о логике в массив1 «фильтрации» значений в массив2.
Сумма каждого N-го столбца
=СУММПРОИЗВ ( — ( ОСТАТ(СТОЛБЕЦ(ранг) — СТОЛБЕЦ(первый.ранг) + 1 ; n) = 0 ); ранг)
Суммируя каждый N-й столбец, вы можете использовать формулу, основанную на функциях СУММПРОИЗВ, МОД и СТОЛБЕЦ.
=СУММПРОИЗВ( — (ОСТАТ( СТОЛБЕЦ( B5: J5 ) — СТОЛБЕЦ( B5 ) + 1 ; К5 ) = 0 ); B5: J5 )
По сути, использование СУММПРОИЗВ суммирует значения в строке , которые были «отфильтрованы» , используя логику , основанную на ОСТАТ. Ключ заключается в следующем:
ОСТАТ (СТОЛБЕЦ( B5: J5 ) – СТОЛБЕЦ ( B5 ) + 1 ; К5 ) = 0
Этот фрагмент формулы использует функцию СТОЛБЕЦ, чтобы получить набор чисел «относительных» столбцов для диапазона, который выглядит следующим образом :
{1;2;3;4;5;6;7;8;9}
Это идет в ОСТАТ, так:
ОСТАТ( { 1 ; 2 ; 3 ; 4 ; 5 ; 6 ; 7 ; 8 ; 9 } ; К5 ) = 0
где K5 это значение N в каждой строке. Функция ОСТАТ рассчитывает остаток для каждого номера столбца, деленное на N. Так, например, при N = 3, ОСТАТ будет рассчитывать что-то вроде этого:
{1;2;0;1;2;0;1;2;0}
Обратите внимание, что нули появляются в столбце 3, 6, 9 и т.д. Формула использует = 0, чтобы превратить значение ИСТИНА, если остаток равен нулю, и ЛОЖЬ, если нет, мы используем двойное отрицание (-), принуждающее ИСТИНА/ЛОЖЬ в единицы и нули. Это составляет массив вроде этого:
{0;0;1;0;0;1;0;0;1}
Где 1-цы в настоящее время указывают на «степени n значения». Это идет в СУММПРОИЗВ как массив1, наряду с B5: J5, как массив2. СУММПРОИЗВ затем делает свое дело, сначала умножая, затем суммируя произведения массивов.
Только «выжившие» ценности умножения являются теми, где массив1 содержит 1. Таким образом, вы можете думать о логике массив1 «фильтрации» значения в массив2.
Суммировать любой другой столбец
Если вы хотите просуммировать любой другой столбец, просто адаптируйте эту формулу по мере необходимости, имея в виду, что формула автоматически присваивает 1 к первому столбцу в диапазоне. Суммируя четные столбцы, используйте:
=СУММПРОИЗВ ( — (ОСТАТ ( СТОЛБЕЦ ( A1: Z1 ) — СТОЛБЕЦ ( A1 ) + 1 ; 2 ) = 0 ); A1: Z1 )
Суммируя нечетные столбцы, используйте:
= СУММПРОИЗВ ( — ( ОСТАТ( СТОЛБЕЦ ( A1: Z1 ) — СТОЛБЕЦ( A1 ) + 1 ; 2 ) = 1 ); A1: Z1 )
Сумма последних n столбцов
=СУММ ( ИНДЕКС( данные ; 0 ; СТОЛБЕЦ( данные ) — (n — 1 )) : ИНДЕКС( данные ; 0 ; СТОЛБЕЦ( данные )))
Подсчитывая последние n столбцы в таблице данных (т.е. последние 3 столбца, последние 4 столбца и т.д.), вы можете использовать формулу, основанную на функции ИНДЕКС.
В показанном примере формула в К5:
=СУММ(ИНДЕКС(C5:H8; 0; СТОЛБЕЦ(C5:H8)+3-(K4-1)):ИНДЕКС(C5:H8; 0; СТОЛБЕЦ(C5:H8)+3))
где «данные» является именованный диапазон С5: H8.
Ключ к пониманию этой формулы является тем, что функция ИНДЕКС может быть использована для возврата ссылки на целую строку и целый столбец.
Чтобы создать ссылку на «последние n столбцы» в таблице, мы делим ссылку на две части, соединенных оператором диапазона. Для того, чтобы получить ссылку на левый столбец, мы используем:
ИНДЕКС( данные ; 0 ; СТОЛБЕЦ( данные ) — ( К4 — 1 ))
Поскольку данные содержит 6 столбцов и К4 содержит 3, это упрощает:
ИНДЕКС( данные ; 0 ; 4 ) // все столбцы 4
Для того, чтобы получить ссылку на правый столбец в диапазоне, мы используем:
ИНДЕКС( данные ; 0 ; СТОЛБЕЦ( данные ))
Который рассчитывает ссылку на столбец 6 названного диапазона «данные», так как функция СТОЛБЕЦ рассчитывает 6:
ИНДЕКС( данные ; 0 ; 6 ) // все столбцы 6
Вместе эти две функции ИНДЕКС рассчитывают ссылку на столбцы с 4 по 6 в данных (т.е. F5: Н8), которые можно свести в массив значений внутри функции СУММ:
СУММ( { 15 ; 14 ; 10 ; 9 ; 12 ; 12 ; 7 ; 9 ; 9 ; 12 ; 13 ; 13 } )
Функция СУММ затем вычисляет сумму, 135.