Как посчитать сумму четных чисел в Excel

Обновлено: 01.07.2024

Простите за каламбур, но это может показаться странным запросом, чтобы узнать, как суммировать нечетные или четные числа только в Excel, но это все еще заданный вопрос.

Поскольку ко мне обратились за помощью в этом вопросе, я сам недавно начал использовать эту формулу, когда мне нужно суммировать нечетные числа, а затем отдельно суммировать четные числа в Excel (я не буду вдаваться в скучные подробности), но поверьте мне. когда я говорю, что это оказалось полезным.

Суммировать только нечетные или четные числа можно с помощью СУММ, ЕСЛИ, ОСТАТ и формулы массива. Если вас это отталкивает, не волнуйтесь, после объяснения я надеюсь, что это станет более понятным и менее сложным.< /p>

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

Начнем со списка чисел:

Этот список чисел будет основой того, что мы собираемся сделать.

Функция MOD "Возвращает остаток после деления числа на делитель".

Это наша отправная точка, мы знаем, что нечетное число при делении на 2 дает остаток 1 (7/2 = 3 с остатком 1).

Четное число, разделенное на 2, не дает остатка (6/2 = 3 без остатка).

Так мы будем отличать нечетные числа от четных.

Формула MOD состоит из двух параметров: числа и делителя.

Число будет числом в столбце A, а делитель будет равен 2.

Формула в ячейке B2: =MOD(A2,2) .

Как видите, у всех нечетных чисел остаток равен 1, а у всех четных чисел остаток равен 0.

В столбце C я ввел формулу ЕСЛИ.

Если значение в столбце B (остаток) равно 1, укажите значение в столбце A или оставьте 0.

Затем в столбце C перечислены все нечетные числа, а вместо четных чисел стоит 0.

Выражение IF имеет вид =IF(B2=1,A2,0) .

В ячейке C21 есть простая формула СУММ, суммирующая значения всех нечетных чисел.

Суммирование четных и нечетных чисел в Excel

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

Давайте разберем формулу и способ ее ввода.

Вы можете пока игнорировать фигурные скобки <>, и я вернусь к ним после содержания между ними.

=СУММ ( – очевидно, что это будет формула СУММ, и она будет СУММИТЬ все, что я определил в скобках.
IF(MOD($A$2:$A$22,2)=1, — логический оператор в нашей функции ЕСЛИ проверяет, остается ли остаток от 1 при делении чисел между A2 и A22 на 2. Обратите внимание на $ знаки в диапазоне ячеек. Обычно этот тип функции ЕСЛИ не работает, но это будет, потому что позже мы собираемся сделать его формулой массива.
$A$2:$A$22)) — значением, если оно истинно, в нашей функции ЕСЛИ будут значения в столбце A. Если остаток от нашей функции ОСТАТ равен 1, то мы собираемся суммировать эти значения здесь . В этой формуле нет значения параметра if false, мы работаем только с теми значениями, которые истинны.
<> — Формула как есть не будет работать. Нам нужно сделать это формулой массива, чтобы сделать это, когда вы вводите остальную часть формулы и все еще редактируете формулу, одновременно нажимая Ctrl, Shift и Enter/Return. Excel превращает это в формулу массива и добавляет фигурные скобки <> в начало и конец формулы.

Чтобы суммировать четные числа в Excel, измените логическое значение оператора IF, чтобы проверить, равна ли функция ОСТАТ нулю.

В этой формуле для VLookUp с несколькими значениями немного больше информации о массивах.

Эту идею можно использовать для суммирования или выполнения других функций в зависимости от определенных критериев.

Поэкспериментируйте с ним сами и посмотрите, что вы можете придумать или где это может оказаться полезным.

Вот формулы, использующие функцию СУММПРОИЗВ, которые суммируют диапазон ячеек, находящихся только в четных или нечетных строках.

Суммирование только строк с четными номерами.
Формула в выбранной ячейке C21, скопированная в ячейку F21, имеет вид
=СУММПРОИЗВ(C4:C18,MOD(СТРОКА(C4:C18)+1,2))

Суммирование только строк с нечетными номерами.
Формула в выбранной ячейке C22, скопированная в ячейку F22, имеет вид
=СУММПРОИЗВ(C5:C19,MOD(СТРОКА(C5:C19)+0,2))

11 комментариев к «Учебники Тома по Excel: суммирование ячеек только в четных или нечетных строках»

Том — перенос в Powerquery — затем депивотирование, затем доллары — затем перенос в модель данных с помощью некоторого dax (IMV) дает гораздо более гибкий результат, чем использование простого суммарного произведения

Вы бы применили все это к этому делу? Не для меня. У всех этих аналитических инструментов есть свое место, но в этом примере было бы слишком сложно просто получить простую сумму.

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

поясните, пожалуйста, как сделать промежуточный итог для sumproduct .

Я буду рад помочь вам, если смогу, но ваш комментарий может повлечь за собой множество возможностей. Предложите пример с подробным описанием того, с чем вы работаете (используемые диапазоны, примеры данных), что вы хотите сделать и ожидаемых результатов.

Спасибо за ответ. Ниже приведены мои требования

Наименование Элемент План / Фактическое обязательство
Текстильное покрытие, план 350
Текстильное покрытие, фактическое 100
Текстильное покрытие, план 350
Текстильное покрытие, фактическое 100
Технический лист, план 350 < br />Технический лист Фактический 100
Технический план стержня 500
Технический стержень Фактический 100
Коммерческий угол План 600
Коммерческий угол Фактический 100
Коммерческий план трубы 700
Commercial Pipe Actual 100
Commercial Lever Plan 350
Commercial Lever Fact 100
Total Plan
Total Actual

ЕСЛИ я применяю фильтр по имени или элементу, я хочу получить общий план и общее фактическое значение. только для данных фильтра

Если ваши данные поступают из A2:C15, то эти заголовки в A16:A23 и соответствующие формулы в B16:B23 мне подошли.

Только план =СУММПРОИЗВ((ПРАВО(B2:B15,4)=”План”)*C2:C15)
Только факт =СУММПРОИЗВ((ПРАВО(B2:B15,6)=”Факт”) *C2:C15)
План текстиля =СУММПРОИЗВ(((A2:A15)="Текстиль")*(ПРАВО(B2:B15,4)="План")*C2:C15)
Инженерный План =СУММПРОИЗВ(((A2:A15)="Инженерный")*(ПРАВО(B2:B15,4)="План")*C2:C15)
Коммерческий план =СУММПРОИЗВ(((A2:A15) ="Коммерческий")*(ПРАВО(B2:B15,4)="План")*C2:C15)
Текстиль Факт =СУММПРОИЗВ(((A2:A15)="Текстиль")*(ПРАВО(B2 :B15,6)="Факт")*C2:C15)
Технический факт =СУММПРОИЗВ(((A2:A15)="Технический")*(ПРАВО(B2:B15,6)="Факт") *C2:C15)
Коммерческий факт =СУММПРОИЗВ(((A2:A15)=”Коммерческий”)*(ПРАВО(B2:B15,6)="Факт")*C2:C15)

Это очень полезно для меня. Объясните, пожалуйста, как работает эта формула? Я имею в виду математическое объяснение этой функции.

Вот как работает формула:

Во-первых, интересующий диапазон — C4:C18, и проблема заключается в суммировании только строк с четными номерами или строк с нечетными номерами для C5:C19. Просто взглянув на формулу четных строк, формула СУММПРОИЗВ сравнивает два массива, которые являются диапазонами. Один диапазон — это полный диапазон C4:C18. Другой диапазон — это оценка, для которой ячейки в диапазоне удовлетворяют требованию нахождения в четных строках. Функция MOD использует число 2 в качестве делителя для каждой второй строки. Если бы вы выбрали часть формулы MOD(ROW(C4:C18)+1,2) в строке формул и нажали клавишу F9, вы бы увидели это: . Теперь все, что делает функция СУММПРОИЗВ, — это просмотр только ИСТИННЫХ ячеек (которые обозначаются единицами) и суммирование того, что находится в этих ячейках, поскольку они попадают в диапазон первого массива C4: C18. Для строк с нечетными номерами это в основном тот же процесс, но с использованием числового аргумента 0 вместо 1 для просмотра нечетных строк.

Чтобы суммировать ячейки, содержащие только четные числа, нам нужно использовать комбинацию функций СУММПРОИЗВ и ОСТАТ

Пример: сумма ячеек, содержащих четные числа

СПОСОБ 1. Суммировать ячейки, содержащие четные числа

В формуле используется функция Excel MOD для возврата остатка от 0 для четных чисел и 1 для нечетных чисел. Затем функция СУММПРОИЗВ Excel выполняет это вычисление для каждой ячейки в указанном диапазоне (B5:B9) и суммирует значения в том же диапазоне (B5:B9), который включает два четных значения (10 и 12).

СПОСОБ 1. Суммировать ячейки, содержащие четные числа, с помощью VBA

'применить формулу к сумме ячеек, содержащих четные числа
ws.Range("D5").Formula = "=СУММПРОИЗВ(--(MOD(B5:B9,2)=0),B5: Б9)"

ОБЪЕКТЫ
Рабочие листы. Объект «Рабочие листы» представляет все рабочие листы в рабочей книге, за исключением листов диаграмм.
Диапазон. Объект Range представляет собой одну ячейку или диапазон ячеек на листе.

ТРЕБОВАНИЯ
Название рабочего листа: Имейте рабочий лист под названием «Аналитик».
Диапазон значений. Убедитесь, что значения, которые вы хотите проверить, находятся в диапазоне ("B5:B9").
Диапазон суммирования. Убедитесь, что значения, которые вы хотите суммировать, находятся в диапазоне ("B5:B9").

РЕГУЛИРУЕМЫЕ ПАРАМЕТРЫ
Выходной диапазон: выберите выходной диапазон, изменив ссылку на ячейку ("D5") в коде VBA на любую ячейку на листе, которая не противоречит формуле.
Диапазон значений: выберите значения, которые вы хотите проверить, изменив диапазон ("B5:B9"), диапазон в функции ОСТАТ, на любой диапазон на листе, который не противоречит формуле.
Диапазон суммы: выберите значения, которые вы хотите суммировать, изменив диапазон ("B5:B9"), вторую часть формулы, на любой диапазон на листе, который не противоречит формуле.< /p>

Пояснение к формуле, используемой для суммирования ячеек, содержащих четные числа

ОБЪЯСНЕНИЕ
Для суммирования ячеек, содержащих четные числа, нам нужно использовать комбинацию функций Excel СУММПРОИЗВ и ОСТАТ. В этом примере мы суммируем из того же диапазона, который мы тестируем на четные числа. Вы можете выбрать другой диапазон суммы, однако диапазон суммы должен соответствовать диапазону значений.

АРГУМЕНТЫ
values_range: числовой диапазон значений, которые вы хотите проверить на наличие четных чисел.
sum_range: числовой диапазон значений, которые вы хотите просуммировать, соответствующий диапазону значений.

Иногда в наборе данных нам приходится считать нечетные и четные числа в Excel, чтобы понять записи. В этой статье мы опишем способы подсчета нечетных и четных чисел, используя комбинации таких функций, как СУММ и МОД, СУММПРОИЗВ и МОД и макрокод VBA.

В сценарии у нас есть набор данных регистрационных номеров учащегося, и мы хотим подсчитать нечетные и четные числа в наборе данных.

Набор данных для загрузки

3 простых способа подсчета четных и нечетных чисел в Excel

Метод 1. Использование функций массива SUM и MOD

Функция MOD извлекает остатки, когда одно число делится на другое число. Например, MOD(нечетный/четный,2) извлекает 1 или 0 как остаток от нечетных или четных чисел. Поэтому мы используем функцию ОСТАТ, чтобы определить любое число как четное или нечетное.

Метод 1.1: подсчет нечетного числа

Шаг 1. Введите формулу =SUM(1*(MOD($C$8:$C$21,2)=1)) в любой нужной ячейке.

Шаг 2. Так как это формула массива, нажмите CTRL+SHIFT+ENTER.

В наборе данных 8 нечетных чисел, поэтому формула предоставила именно столько в качестве результата.

Метод 1.2: для подсчета четных чисел

Шаг 1. Вставьте формулу =SUM(1*(MOD($C$8:$C$21,2)=0)) в любую нужную ячейку.

Шаг 2. Нажмите CTRL+SHIFT+ENTER.

В результате формула дала 6, поскольку мы видим, что в наборе данных 6 четных чисел.

Метод 2: Использование функции СУММПРОИЗВ И МОДА

Метод 2.1: подсчет нечетного числа

Шаг 1. Введите формулу =СУММПРОИЗВ((MOD($C$8:$C$21,2)<>0)+0) в любой нужной ячейке.

Шаг 2. Нажмите клавишу ВВОД.

Метод 2.2: для подсчета четных чисел

Шаг 1. Вставьте формулу =СУММПРОИЗВ((MOD($C$8:$C$21,2)=0)+0) в любую нужную ячейку.

Шаг 2. Нажмите клавишу ВВОД

Способ 3. Использование кода макроса VBA

Метод 3.1: подсчет нечетного числа

Шаг 1. Нажмите одновременно клавиши ALT + F11. Откроется Microsoft Visual Basic.

Шаг 2. На панели инструментов нажмите «Вставить и выбрать модуль».

Шаг 3. Вставьте приведенный ниже код макроса VBA в модуль

Шаг 4. Нажмите F5, чтобы запустить код.

Количество нечетных чисел отображается в ячейке C2, когда мы вводим код макроса VBA.

Метод 3.2: подсчет четных чисел

Шаг 1. Повторите шаги 1 и 2, описанные выше.

Шаг 2. Вставьте ниже код макроса VBA

Шаг 3. Нажмите F5, чтобы запустить код.

Количество четных чисел отображается в ячейке C3, когда мы вводим код макроса VBA.

Заключение

Вы также можете отображать отдельно любое число в соседней ячейке, независимо от того, является ли оно четным или нечетным в печатном тексте. В этой статье мы описываем методы, которые могут просто подсчитывать число в зависимости от того, являются ли они четными или нечетными. Мы используем функцию MOD в сочетании с SUM и SUMPRODUCT и макрокодом VBA для подсчета нечетных и четных чисел. Надеюсь, вы найдете методы эффективными и простыми в использовании. Комментируйте, если вам нужны дополнительные разъяснения и есть что добавить.

Читайте также: