Как Заполнить Диапазон Ячеек в Excel Нечетными Числами • Функция случмежду

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а. Функция СУММПРОИЗВ для суммирования значений в четных строках

Рис. 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 + … + ЕСЛИ(ОСТАТ(СТОЛБЕЦ(23);3)=2;1;0)*23.

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

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

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

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

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

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Конечно, способ очень простой, но рекомендуется для сравнительно коротких списков реестры на несколько десятков значений промаркировать данным способом будет несколько проблематично. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Есть еще один оператор, предназначенный для получения числа ячеек. Но ее отличие от прошлого оператора тем, что способна осуществлять расчет исключительно тех ячеек, в которых есть числа. Как же использовать эту функцию?
Как Заполнить Диапазон Ячеек в Excel Нечетными Числами • Функция случмежду

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

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

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

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

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

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Выберите в меню команду Правка Заполнить Прогрессия а затем необходимые опции Расположение по столбцам, тип арифметическая , а также введите значение шага, в данном случае 1 и нажуть ОК. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
при выполнении задания 2с-3 необходимо ввести шаг арифметической прогрессии равный 2, а при выполнении задания 2с-4шаг равный 3.

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

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

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

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

Adblock
detector