Подсчёт или суммирование только целых чисел в Excel

В этой статье представлены две формулы на основе функций SUMPRODUCT и MOD, которые позволяют подсчитывать и суммировать только целые числа в заданном диапазоне ячеек Excel.

Как подсчитать только целые числа в Excel
Как суммировать только целые числа в Excel

Как подсчитать только целые числа в Excel

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

Универсальная формула

=SUMPRODUCT(--(MOD(range,1)=0))

Аргументы

Как использовать эту формулу?

1. Выберите пустую ячейку, чтобы разместить в ней результат.

2. Введите в неё приведённую ниже формулу и нажмите клавишу Enter, чтобы получить результат.

=SUMPRODUCT(--(MOD(E3:E17,1)=0))

Как работает эта формула?

=SUMPRODUCT(--(MOD(E3:E17,1)=0))

• 1) MOD(E3:E17,1): Здесь функция MOD проверяет, какие числа в диапазоне E3:E17 являются целыми. Она возвращает остатки от деления каждого числа из этого диапазона на 1: {0,35;0;0;0;0;0,35;0;0,98;0;0;0,80;0;0;0,75}.
• 2) {0,35;0;0;0;0;0;0,35;0;0,98;0;0;0,80;0;0;0,75}=0:Сравните каждое число в массиве с нулём и получите массив логических значений ИСТИНА/ЛОЖЬ следующего вида: {ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ}. В этом массиве значения ИСТИНА соответствуют целым числам, а ЛОЖЬ — десятичным.
• 3) --{ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ}: Эти два знака минус преобразуют значения «ИСТИНА» в 1, а «ЛОЖЬ» — в 0. В результате получится новый массив: {0;1;1;1;1;1;0;1;0;1;1;0;1;1;0}.
• 4) SUMPRODUCT{0;1;1;1;1;1; 0;1;0;1;1;0;1;1;0}: Функция SUMPRODUCT складывает все числа в массиве и возвращает итоговый результат — 10.

Как суммировать только целые числа в Excel

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

Возьмём за основу таблицу продаж товаров, приведённую выше.

Универсальная формула

=SUMPRODUCT(--(MOD(range,1)=0)*range)

Аргументы

Как использовать эту формулу?

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

=SUMPRODUCT(--(MOD(E3:E17,1)=0)*E3:E17)

Как работает эта формула?

=SUMPRODUCT(--(MOD(E3:E17,1)=0)*E3:E17)

• 1) MOD(E3:E17,1): Здесь используйте функцию MOD для проверки целых чисел в диапазоне E3:E17. Она возвращает остатки от деления каждого числа в диапазоне E3:E17 на 1: {0,35;0;0;0;0;0,35;0;0,98;0;0;0,80;0;0;0,75}.
• 2) {0,35;0;0;0;0;0;0,35;0;0,98;0;0;0,80;0;0;0,75}=0:Сравните каждое число в массиве с 0 и получите массив значений ИСТИНА/ЛОЖЬ следующего вида: {ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ}. В этом массиве значения ИСТИНА соответствуют целым числам, а значения ЛОЖЬ — десятичным числам.
• 3) --{ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ}: Эти два знака минус преобразуют «ИСТИНА» в 1, а «ЛОЖЬ» — в 0. В результате вы получите новый массив: {0;1;1;1;1;1; 0;1;0;1;1;0;1;1;0}.
• 4) SUMPRODUCT({0;1;1;1;1;1;0;1;0;1;1;0;1;1;0}*E3:E17): Каждое число в массиве умножается на соответствующее значение из диапазона E3:E17, в результате чего получается массив: SUMPRODUCT(0;2028;900;3944;2757;1231;0;2313;0;3152;1361;0;1980;2579;0). Затем функция SUMPRODUCT суммирует все элементы этого массива и возвращает итоговый результат — 22245.

Связанные функции

Связанные формулы