Пример, о котором идет речь, мы рассмотрим позже в силу его сложности, а начнем с более простого случая – найдем сумму значений для всех четных / нечетных строк. Исходные данные представлены на рис. 1 (см. также лист «Пример1» Excel-файла).

Рис. 1. Данные о приходе / уходе [1]

Скачать заметку Excel. Суммирование значений в четных (или кратных) строках (столбцах) в формате Word2007

Скачать Пример суммирования четных (или кратных) строк (столбцов) в формате Excel2007

1-й способ. Более простой для понимания (на мой взгляд J), но требующий дополнительной колонки для промежуточных вычислений. Введем в столбец Е формулу =ЕЧЁТН(СТРОКА()), возвращающую значение ИСТИНА, если строка четная, и ЛОЖЬ – если нечетная (рис. 2).

Рис. 2. Определение четности номера строки

После чего применим две формулы условного суммирования (рис. 3)

Рис. 3. Использование функции условного суммирования

Формула СУММЕСЛИ суммирует только те значения из диапазона D3:D46 (Диапазон_суммирования), для которых значение в столбце Е равно ЛОЖЬ. Аналогично для вычисления суммарного расхода в ячейке Н4 используется формула =СУММЕСЛИ(E3:E46;ИСТИНА;D3:D46).

2-й способ. Использование формулы массива. Если вы не знакомы с формулами массива, настоятельно рекомендую пройти по ссылке и почитать подробнее. На мой взгляд, работу формул массива кратко можно сформулировать так: формулы массива сначала выполняют действия, прописанные в них, над элементами массива, а потом суммируют эти действия для всех элементов массива (что-то наподобие формулы СУММПРОИЗВ – сумма произведений). Для того чтобы ввести в ячейку формулу массива, надо набрать формулу, а затем одновременно нажать CTRL+SHIFT+ВВОД. Это приведет к тому, что вокруг формулы (см. строку формул на рис. 4) появятся фигурные скобки: { и }. Не пытайтесь ввести скобки с клавиатуры; формула массива не возникнет! J После редактирования формулы массива (или если вы случайно встали курсором в ячейку с такой формулой), повторно нажмите CTRL+SHIFT+ВВОД.

Рис. 4. Использование формулы массива для суммирования значений в нечетных строках.

Рассмотрим, как действует формула массива в нашем случае (рис. 4):

1. СТРОКА(D3) – определяет номер строки для ячейки D3;
2. ЕНЕЧЁТ(СТРОКА(D3)) – определяет, является ли номер строки, содержащей ячейку D3, нечетным; если нечетный, возвращает значение ИСТИНА (или единица), если четный – ЛОЖЬ (или ноль);
3. ЕНЕЧЁТ(СТРОКА(D3))*D3 – умножает значение, хранящееся в ячейке D3 (536) на значение, полученное на предыдущем шаге;
4. {=СУММ(ЕНЕЧЁТ(СТРОКА(D3:D46))*(D3:D46))} – суммирует в диапазоне D3:D46, все значения, полученные на предыдущем шаге, то есть, ЕНЕЧЁТ(СТРОКА(D3))*D3 + ЕНЕЧЁТ(СТРОКА(D4))*D4 + … + ЕНЕЧЁТ(СТРОКА(D46))*D46

Для вычисления суммарного расхода в ячейке Е49 используется формула {=СУММ(ЕЧЁТН(СТРОКА(D3:D46))*(D3:D46))}, которая находит сумму значений в диапазоне D3:D46 только для четных строк.

* * *

Вернемся к более общей задаче – суммирование строк (столбцов) кратных n (рис. 5; см. также лист «Пример2» Excel-файла). Задача – найти сумму запасов, то есть сумму по строке 23 для всех столбцов с заголовком «Запасы».

Рис. 5. Суммирование значений в каждом третьем столбце

Рассмотрим, как работает формула массива во втором примере (рис. 5):

1. СТОЛБЕЦ(B23) – определяет номер столбца для ячейки В23;
2. ОСТАТ(СТОЛБЕЦ(B23);3) – определяет, остаток от деления номера столбца на 3;
3. ЕСЛИ(ОСТАТ(СТОЛБЕЦ(B23);3)=2;1;0) – если остаток равен двум, функция ЕСЛИ возвращает значение 1; если не равен – 0;
4. ЕСЛИ(ОСТАТ(СТОЛБЕЦ(B23);3)=2;1;0)*B23 – умножает значение, хранящееся в ячейке В23 (1) на значение, полученное на предыдущем шаге;
5. {=СУММ(ЕСЛИ(ОСТАТ(СТОЛБЕЦ(B23:BX23);3)=2;1;0)*B23:BX23)} – суммирует в диапазоне В23:ВХ23, все значения, полученные на предыдущем шаге, то есть, ЕСЛИ(ОСТАТ(СТОЛБЕЦ(B23);3)=2;1;0)*B23 + ЕСЛИ(ОСТАТ(СТОЛБЕЦ(С23);3)=2;1;0)*С23 + ЕСЛИ(ОСТАТ(СТОЛБЕЦ(D23);3)=2;1;0)*D23 + … + ЕСЛИ(ОСТАТ(СТОЛБЕЦ(BХ23);3)=2;1;0)*BХ23.

Вуаля!