Как посчитать сумму четных чисел в excel
Ранее я описал, как с помощью пользовательской функции найти сумму значений в ячейках, выделенных цветом. К сожалению, эта функция не работает, если ячейки раскрашены с помощью условного форматирования. Я обещал «доработать» функцию. Но за два года, прошедшие с публикации той заметки, я не смог ни самостоятельно, ни с помощью информации из Интернета написать удобоваримый код… (Дополнение от 29 марта 2017 г. Спустя еще пять лет, код мне всё же удалось написать; см. заключительную часть заметки). И вот недавно я наткнулся на идею, содержащуюся в книге Д.Холи, Р. Холи «Excel 2007. Трюки», которая позволяет обойтись вовсе без кода.
Пусть есть список чисел от 1 до 100, размещенных в диапазоне А1:А100 (рис. 1; см. также лист «СУММЕСЛИ» Excel-файла) [1]. На диапазон наложено условное форматирование, помечающее ячейки, содержащие числа больше 10 и меньше или равно 20.
Рис. 1. Диапазон чисел; условным форматированием выделены ячейки, содержащие значения от 10 до 20
Теперь необходимо сложить значения в ячейках, отвечающих только что установленному критерию. Неважно, какое именно форматирование применяется к этим ячейкам, однако необходимо знать критерий, согласно которому ячейки выделяются.
Чтобы сложить диапазон ячеек, отвечающих одному критерию, можно использовать функцию СУММЕСЛИ (рис. 2).
Рис. 2. Суммирование ячеек, отвечающих одному условию
Если у вас несколько условий, можно использовать функцию СУММЕСЛИМН (рис. 3).
Рис. 3. Суммирование ячеек, отвечающих нескольким условиям
Для подсчета числа ячеек, отвечающих одному критерию, можно использовать функцию СЧЁТЕСЛИ.
Для подсчета числа ячеек, отвечающих нескольким критериям, можно использовать функцию СЧЁТЕСЛИМН.
В Excel предусмотрена еще одна функция, которая позволяет указать несколько условий. Эта функция входит в набор функций баз данных Excel и называется БДСУММ. Чтобы проверить ее, используйте тот же набор чисел в диапазоне А2:А100 (рис. 4; см. также лист «БДСУММ» Excel-файла).
Рис. 4. Использование функций баз данных
Выделите ячейки C1:D2 и присвойте этому диапазону имя Критерий, введя его в поле имени слева от строки формул. Теперь выделите ячейку С1 и введите =$А$1, то есть ссылку на первую ячейку на листе, содержащую имя базы данных. Введите =$А$1 в ячейку D1 и вы получите две копии заголовка столбца А. Эти копии будут использоваться как заголовки для условий БДСУММ (C1:D2), который вы назвали Критерий. В ячейке С2 введите >10. В ячейке D2 введите <=20. В ячейке, где должен быть результат, введите следующую формулу:
Для подсчета числа ячеек, отвечающих нескольким критериям, можно использовать функцию БСЧЁТ.
Эта заметка продолжает цикл материалов по использованию формул массива. Если ранее вы не сталкивались с формулами массива, рекомендую начать с Введение в формулы массива и Некоторые примеры использования формул массива. Основное достоинство формул массивов состоит в том, что они позволяют выполнять очень широкий круг вычислений, который другими способами выполнить нельзя. К сожалению, формулы массивов – это наиболее сложное и непонятное средство Excel.
В настоящей заметке использованы материалы книги Джона Уокенбаха MS Excel 2007. Библия пользователя. – М.: Издательский дом «Вильямс», 2008. – 816 с.
Сумма цифр целого числа
Приведенная ниже формула массива возвращает сумму цифр, составляющих положительное целое число, которое содержится в ячейке А1. Например, если в этой ячейке хранится число 409, то формула вернет число 13 (сумму цифр 4, 0 и 9):
Чтобы понять, как работает эта формула, рассмотрим последовательно ее работу.
1. ДЛСТР(A1) возвращает количество цифр числа, находящегося в ячейке А1. В нашем примере, 3.
2. Функция <=СТРОКА(ДВССЫЛ( " 1: " &ДЛСТР(A1)))>возвращает массив целых чисел, который начинается с единицы и заканчивается числом, равным количеству цифр, которые составляют значение, хранящееся в ячейке А1. В нашем примере СТРОКА создаст следующий виртуальный массив:
3. Функция ПСТР возвращает часть текстовой строки. Функция ПСТР имеет три аргумента: (1) текст; в нашем примере содержимое ячейки А1, 409;
(2) начальная позиция; в нашем примере – массив , то есть в первом заходе будет использовано значение 1, во втором – 2, в третьем – 3;
(3) количество извлекаемых символов; в нашем примере, 1.
В нашем примере функция ПСТР выглядит так: <=ПСТР(409;;1)>. Функция создаст массив из трех элементов: .
4. К сожалению, функция ПСТР возвращает массив строк, которые содержат текст. Чтобы преобразовать строку, которая выглядит как число, в соответствующее число, следует умножить ее на единицу (что и было сделано). Можно также воспользоваться функцией ЗНАЧЕН, которая преобразует текстовую строку, содержащую число, в соответствующее значение:
5. И, наконец, функция СУММ суммирует элементы массива : <=СУММ()>. Эта формула возвращает значение 13.
Вы также можете «встать» в ячейку В1 и пройти по меню Формулы → Зависимости формул → Вычислить формулу. В открывшемся окне, нажимая последовательно на «Вычислить», вы будете видеть, как работает эта формула шаг за шагом:
Заметьте, что формула не работает с отрицательными числами, поскольку знак «минус» не является числом. В следующей формуле эта проблема решается с помощью функции ABS, которая возвращает модуль аргумента:
Высчитать сумму четных либо нечетных чисел в Excel можно несколькими способами:
Стандартные функции
Первый способ возможен при использовании стандартных функций приложения. Для этого необходимо создать два дополнительных столбца с формулами:
- Четные числа – вставляем формулу «= ЕСЛИ ( ОСТАТ(число;2) =0;число;0)», которая вернет число, в случае если оно делится на 2 без остатка.
- Нечетные числа – вставляем формулу «= ЕСЛИ ( ОСТАТ(число;2) =1;число;0)», которая вернет число, в случае если оно не делится на 2 без остатка.
Затем необходимо определит сумму по двум столбцам с помощью функции «=СУММ()».
Плюсы данного способа в том, что он будет понятен даже тем пользователям, которые профессионально не владею приложением.
Минусы способа – приходится добавлять лишние столбцы, что не всегда удобно.
Пользовательская функция
Второй способ, является более удобным, чем первый, т.к. в нем применяется пользовательская функция, написанная на VBA – sum_num(). Функция возвращает сумму чисел в виде целого числа. Суммируются либо четные числа, либо нечетные, в зависимости от значения ее второго аргумента.
Синтаксис функции: sum_num(rng;odd):
- Аргумент rng – принимает диапазон ячеек, по которым необходимо произвести суммирование.
- Аргумент odd – принимает логическое значение ИСТИНА для четных чисел или ЛОЖЬ для нечетных.
Важно: Четными и нечетными числа могут являться только целые числа, поэтому числа, которые не соответствуют определению целого числа, игнорируются. Также, если значением ячейки является срока, то данная строка не участвует в расчете.
Плюсы: нет нужны добавлять новые столбцы; лучший контроль над данными.
Минусы заключаются в необходимости перевода файла в формат .xlsm для версий Excel, начиная с версии 2007. Также функция будет работать только в той книге, в которой она присутствует.
Использование массива
Последний способ является самым удобным, т.к. не требует создания дополнительных столбцов и программирования.
Его решение схоже с первым вариантом - они используют одни и те же формулы, но данный способ, благодаря использованию массивов, производит подсчет в одной ячейке:
- Для четных чисел - вставляем формулу «=СУММ( ЕСЛИ ( ОСТАТ(диапазон_ячеек;2) =0;диапазон_ячеек;0))». После ввода данных в строку формул нажимаем одновременно клавиши Ctrl + Shift + Enter, чем сообщаем приложению, что данные необходимо обрабатывать как массив, и оно заключит их в фигурные скобки;
- Для нечетных чисел - повторяем действия, но изменяем формулу «=СУММ(ЕСЛИ( ОСТАТ(диапазон_ячеек;2) =1;диапазон_ячеек;0))».
Плюсом способа является то, что все рассчитывается в одной ячейке, без дополнительных столбцов и формул.
Минусом является лишь то, что неопытные пользователи могут не понять Ваших записей.
На рисунке видно,что все способы возвращают один и тот же результат, какой лучше, необходимо выбирать под конкретную задачу.
Если на вашем листе есть диапазон чисел, который содержит как нечетные, так и четные числа, но теперь вам нужно подсчитать или суммировать только нечетные или четные числа. Как можно быстро выполнить эту задачу на листе Excel?
Подсчет / среднее / сумма нечетных или четных строк с помощью Kutools for Excel
Считайте четные или нечетные числа с помощью формул в Excel
Чтобы подсчитать только четные или нечетные числа в диапазоне, вам могут помочь следующие формулы, выполните следующие действия:
1. Чтобы считать только нечетные числа, введите эту формулу = СУММПРОИЗВ ((MOD (A1: C8,2) <> 0) +0) в пустой ячейке, см. снимок экрана:
2, Затем нажмите Enter key, и вы получите количество нечетных значений в выбранном диапазоне.
Ноты:
1. Если вы хотите получить только количество четных значений, примените эту формулу = СУММПРОИЗВ ((МОД (A1: C8,2) = 0) +0) .
2. В приведенных выше формулах A1: C8 - это диапазон, в котором вы хотите посчитать нечетные или четные числа, вы можете изменить его по своему усмотрению.
Суммируйте нечетные или четные числа с формулами в Excel
Если вам нужно сложить все нечетные или четные числа только в диапазоне, вы можете использовать следующие формулы: обычные формулы или формулы массива.
1. Нормальные формулы для суммирования нечетных / четных чисел в диапазоне:
Пожалуйста, введите эту формулу =SUMPRODUCT(--(MOD(A1:C8,2)=1),A1:C8) в пустую ячейку, см. снимок экрана:
Затем нажмите Enter key, все нечетные значения добавлены в диапазон.
Заметки： 1. Чтобы суммировать только четные числа, вы можете использовать следующую формулу: =SUMPRODUCT(--(MOD(A1:C8,2)=0),A1:C8) .
2. В приведенных выше формулах A1: C8 - это диапазон, в котором вы хотите сложить нечетные / четные числа.
2. Формулы массива для суммирования четных и нечетных чисел в диапазоне
В пустой ячейке введите эту формулу =SUM(IF(MOD($A$1:$C$8,2)=1,$A$1:$C$8)) , Затем нажмите Ctrl + Shift + Enter ключи вместе. И вы получите сумму нечетных чисел.
Для суммирования четных чисел примените эту формулу массива =SUM(IF(MOD($A$1:$C$8,2)=0,$A$1:$C$8)) , и не забудьте нажать Ctrl + Shift + Enter ключи.
Суммирование нечетных или четных строк с формулами в Excel
Иногда вам нужно суммировать диапазон ячеек, которые располагаются только в строках с четными или нечетными номерами. Вы также можете использовать формулы для ее решения.
1. Предположим, у вас есть следующие данные, и теперь для суммирования нечетных строк в столбце B введите эту формулу =SUMPRODUCT(B2:B10,MOD(ROW(B2:B10)+0,2)) в пустую ячейку. Смотрите скриншот:
2, Затем введите Enter key, и все значения нечетных строк были добавлены.
Ноты:
1. Чтобы суммировать строки с четными номерами, используйте эту формулу =SUMPRODUCT(B2:B10,MOD(ROW(B2:B10)+1,2)) .
2. В приведенных выше формулах B2: B10 - это диапазон, который вы хотите использовать, вы можете изменить его по своему усмотрению.
Подсчет / среднее / сумма нечетных или четных строк с помощью Kutools for Excel
Если у вас есть Kutools for Excel После установки вы можете быстро выбрать нечетные или четные строки или столбцы, а затем просмотреть вычисления в строке состояния.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите весь диапазон строк и нажмите Kutools > Выберите > Выберите интервальные строки и столбцы. Смотрите скриншот:
2. Затем в появившемся диалоговом окне отметьте Ряды or Колонны как вам нужно, и введите 1 в оба текстового поля, снимите флажок Выбрать целые строки. Смотрите скриншот:
3. Нажмите Ok, теперь все нечетные строки выбраны, и вы перейдете в строку состояния, вы сможете просмотреть среднее значение, подсчет и суммирование результатов.
Наконечник: Если вы хотите подсчитать, усреднить или суммировать четные строки, просто выберите все строки, кроме первой, затем примените Выберите интервальные строки и столбцы.
Читайте также: