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

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

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

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

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

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

Дополнение от 20.09.15. Еще проще с задачей справится формула =СУММЕСЛИ(C:C; » Приход » ;D:D), которая суммирует только те значения из столбца D, для которых значение в столбце С равно Приход.

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

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

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

Для вычисления суммарного расхода в ячейке Е49 используется формула , которая находит сумму значений в диапазоне D3:D46 только для четных строк.

3-й способ. Подсмотрен у Билла Джелена в его книге Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel (рис. 4а). Для подсчета значений в нечетных строках используйте формулу: =СУММПРОИЗВ(ОСТАТ(СТРОКА(A2:A99);2);(C2:C99)). Для полсчета значений в четных строках воспользуйтесь формулой: =СУММПРОИЗВ(ОСТАТ(СТРОКА(A2:A99);2)+1;(C2:C99))

1. СТРОКА(А2) – определяет номер строки для ячейки А2;
2. ОСТАТ(СТРОКА(A2;2) – определяет остаток от деления значения, полученного на предыдущем шаге, на двойку; понятно, что для четных строк ОСТАТОК() будет равен нулю, а для нечетных – единице;
3. ОСТАТ(СТРОКА(A2;2)+1 – прибавляет единицу к предыдущему результату; теперь для четных строк значение 1, а для нечетных – 0;
4. Далее значение 1 или 0 умножается на значение в ячейке С2; т.е., значения в четных строках столбца С равны сами себе, а в нечетных – нулю;
5. СУММПРОИЗВ() суммирует значения в столбце С только в четных строках.

Рис. 4а. Функция СУММПРОИЗВ для суммирования значений в четных строках

Для суммирования значений в четных строках также подойдут формулы (см. Excel-файл, лист «Рис. 4а»):

=СУММПРОИЗВ(ОСТАТ(СТРОКА(2:99);2)+1;(D2:D99)); не обязательно указывать буквы для вычисления номера строки;

=СУММПРОИЗВ( — — (ОСТАТ(СТРОКА(C2:C99);2)=0);(E2:E99)); если строка четная, то ОСТАТ()=0, т.е. этот фрагмент формулы вернет значение ИСТИНА, а операция минус минус, вернет значение 1;

=СУММПРОИЗВ(ОСТАТ(СТРОКА(A1:A98);2);(F2:F99)); вы просто сместили фрагмент формулы, определяющий 1/0, на строку выше.

Вернемся к более общей задаче – суммирование строк (столбцов) кратных 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. – суммирует в диапазоне В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.

2-й способ. Также подсмотрен у Билла Джелена. Для суммирования значений в каждой третьей строке воспользуйтесь формулой: =СУММПРОИЗВ( — — (ОСТАТ(СТРОКА(A2:A148);3)=2);(C2:C148)).

Рис. 5а. Функция СУММПРОИЗВ для суммирования значений в каждой третьей строке

1. СТРОКА(А2) – определяет номер строки для ячейки А2;
2. ОСТАТ(СТРОКА(A2);3) – определяет остаток от деления значения, полученного на предыдущем шаге, на тройку;
3. ОСТАТ(СТРОКА(A2);3)=2 – возвращает ИСТИНА, если остаток от деления равен 2 и ЛОЖЬ, если равен 0 или 1;
4. Операция минус минус конвертит логические значения ИСТИНА/ЛОЖЬ в числовые 1/0;
5. СУММПРОИЗВ() суммирует значения в столбце С только для тех строки, которые вернули значение 1 на предыдущем шаге.

Есть еще один оператор, предназначенный для получения числа ячеек. Но ее отличие от прошлого оператора тем, что способна осуществлять расчет исключительно тех ячеек, в которых есть числа. Как же использовать эту функцию?

Как посчитать кол во ячеек в excel

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

Как в экселе пронумеровать строки автоматически

Вместо рутинного и грозящего ошибками метода введения порядковых номеров вручную, рекомендую к применению автоматический — проще, быстрее, надёжнее.

Конечно, способ очень простой, но рекомендуется для сравнительно коротких списков — реестры на несколько десятков значений промаркировать данным способом будет несколько проблематично.

при выполнении задания 2с-3 необходимо ввести шаг арифметической прогрессии равный 2, а при выполнении задания 2с-4 — шаг равный 3.

Задания для самостоятельной работы. Лабораторная работа № 8 — Мегаобучалка

• Проставляем стартовое значение в начале списка, например, единицу.
• Переходим в верхнем меню в пункт «Редактирование» на вкладке «Главная». В нём выбираем команду «Заполнить» и выбираем тип заполнения «Прогрессия»
• Устанавливаем значение по столбцам или по строкам
• Устанавливаем шаг счёта (в большинстве случаев единица)
• Устанавливаем предельное значение, равное количеству записей в списке.

Для добавления новой строки в ячейку используется комбинация клавиш ALT + ENTER. Для расположения текста в ячейке в несколько строк также можно применить вкладку выравниваниекоманды Формат/Ячейки и установить флажок Переносить по словам.