Excel. Суммирование значений в четных (или кратных) строках (столбцах) - umotnas.ru o_O
Главная
Поиск по ключевым словам:
страница 1
Похожие работы
Название работы Кол-во страниц Размер
Лабораторная Работа №1 Реализация «Линейных вычислительных процессов»... 1 24.99kb.
Практическая работа №4 Вычисление площади поверхности конуса в табличном... 1 32.83kb.
«Для чтения использовать зеркало » 1 270.71kb.
Задача оптимального программирования. Получение оптимальных решений... 1 18.73kb.
Электронные таблицы ms excel Подготовка к работе 1 261.13kb.
Лабораторная работа №3 логические функции в excel цель работы: Изучить... 1 235.85kb.
Задач линейного, целочисленного и нелинейного программирования 1 70.47kb.
Практическая работа n 2 Microsoft Excel 2010. Вычисление по формулам 1 51kb.
Книга между книг, Ты свернутый запечатленный свиток; в его строках... 1 108.12kb.
Использование стандартных функций ms excel для решения систем линейных... 1 55.95kb.
Презентация Power Point небольшой сайт публикация Publisher таблицы... 1 38.54kb.
Решение практических задач с компонентами ms office. Word 1 21.02kb.
Викторина для любознательных: «Занимательная биология» 1 9.92kb.

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

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

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

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

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

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

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

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

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

2-й способ. Использование формулы массива. Если вы не знакомы с формулами массива, настоятельно рекомендую пройти по ссылке и почитать подробнее. На мой взгляд, работу формул массива кратко можно сформулировать так: формулы массива сначала выполняют действия, прописанные в них, над элементами массива, а потом суммируют эти действия для всех элементов массива (что-то наподобие формулы СУММПРОИЗВ – сумма произведений). Для того чтобы ввести в ячейку формулу массива, надо набрать формулу, а затем одновременно нажать CTRL+SHIFT+ВВОД. Это приведет к тому, что вокруг формулы (см. строку формул на рис. 4) появятся фигурные скобки: { и }. Не пытайтесь ввести скобки с клавиатуры; формула массива не возникнет!  После редактирования формулы массива (или если вы случайно встали курсором в ячейку с такой формулой), повторно нажмите 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 + … + ЕСЛИ(ОСТАТ(СТОЛБЕЦ(23);3)=2;1;0)*23.

Вуаля! 

1 Данные оформлены весьма симпатично для их просмотра, но очень неудобно для дальнейшей обработки. По возможности оформляйте исходные данные в форме, приемлемой для последующей их обработки сводными таблицами. На эту тему рекомендую замечательную книгу: Билл Джелен, Майкл Александер «Сводные таблицы в Microsoft Excel 2010». Если бы данные были оформлены соответствующим образом, никаких ухищрений не понадобилось бы. Данные можно было бы обработать стандартными сводными таблицами.