Суммы столбцов и по столбцам

Сумма всего столбца

=СУММ( 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 )

Сумма каждого N-го столбца

По сути, использование СУММПРОИЗВ суммирует значения в строке , которые были «отфильтрованы» , используя логику , основанную на ОСТАТ. Ключ заключается в следующем:

ОСТАТ (СТОЛБЕЦ( 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 столбца и т.д.), вы можете использовать формулу, основанную на функции ИНДЕКС.

Сумма последних n столбцов

В показанном примере формула в К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.

Оцените статью
Добавить комментарий