Функция Excel MOD, чтобы получить остаток и многое другое

В учебнике показано, как использовать функцию ОСТАТ для выполнения операции по модулю в Excel. Вы также узнаете, как нахождение остатка может помочь в решении более сложных задач, таких как суммирование каждой N-й ячейки, выделение нечетных или четных чисел и многое другое.

Функция Excel MOD предназначена для получения остатка от деления. Просто, не так ли? Но эта функция способна на гораздо большее, чем ее первоначальная цель, и через мгновение вы узнаете несколько нетривиальных способов использования MOD в Excel, которые могут оказаться полезными в вашей работе.

Функция Excel MOD — синтаксис и основные способы использования

Функция ОСТАТ в Excel используется для нахождения остатка после деления одного числа (делимого) на другое (делитель). В математике эта операция называется по модулю операция или модульотсюда и название МОД.

Функция MOD имеет следующий синтаксис:

MOD(число, делитель)

Где:

Число (обязательно) – число, которое нужно разделить.
Делитель (обязательно) – число, на которое нужно разделить.

Например, MOD(10,3) возвращает 1, потому что 10, разделенное на 3, дает 3 в частном, а остаток равен 1 (10=3*3+1). Формула MOD(10,5) возвращает ноль, потому что 10 делится на 5 без остатка.

3 вещи, которые вы должны знать о MOD в Excel

Результат функции ОСТАТ имеет тот же знак, что и делитель.
Если делитель равен 0, MOD возвращает #DIV/0! ошибка, потому что нельзя делить на ноль.
Если число или делитель являются текстовым значением, формула Excel Mod возвращает ошибку #ЗНАЧ! ошибка.

Формулы MOD для расчета ячеек

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

Формула модификации для суммирования каждой N-й строки или столбца

Удивительно, но Microsoft Excel не имеет встроенной функции для суммирования каждой 2-й, 3-й и т. д. строки на листе. Однако это можно легко сделать, используя одну из следующих формул.

Суммируйте каждую вторую строку

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

Суммируйте четные строки:

СУММПРОИЗВ((MOD(СТРОКА(диапазон),2)=0)*(диапазон))

Сумма нечетных строк:

СУММПРОИЗВ((MOD(СТРОКА(диапазон),2)=1)*(диапазон))

Предполагая, что B2:B7 являются ячейками для суммирования, формулы выглядят следующим образом:

Суммируйте четные строки:
=СУММПРОИЗВ((MOD(СТРОКА($B$2:$B$7),2)=0)*($B$2:$B$7))

Сумма нечетных строк:
=СУММПРОИЗВ((MOD(СТРОКА($B$2:$B$7),2)=1)*($B$2:$B$7))

На скриншоте ниже показан результат:

В этих формулах комбинация ОСТАТ СТРОКА определяет, какие строки следует суммировать, а СУММПРОИЗВ суммирует значения. Вот как:

ROW предоставляет массив номеров строк в количество аргумент функции ОСТАТ. MOD делит номер каждой строки на 2, и вы проверяете результат:

Чтобы суммировать четные строки, результат должен быть 0, потому что четные числа делятся на 2 без остатка.
Чтобы суммировать нечетные строки, результат должен быть 1, потому что нечетные числа, разделенные на 2, дают в остатке 1.

Вместо СУММПРОИЗВ вы можете использовать функцию ЕСЛИ для оценки условия и СУММ для сложения чисел (это формула массива, которая вводится нажатием Ctrl + Shift + Enter):

=СУММ(ЕСЛИ(ОСТАТ(СТРОКА($B$2:$B$7),2)=0,$B$2:$B$7,0))

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

Сумма каждой N-й строки

Чтобы сложить каждую 3-ю, 4-ю, 5-ю и т. д. строку, используйте эту общую формулу:

СУММПРОИЗВ((MOD(СТРОКА(диапазон)-СТРОКА(первая_ячейка)+1,н)=0)*(диапазон))

Например, чтобы суммировать каждую 3-ю ячейку в диапазоне C2:C10, формула выглядит следующим образом:

=СУММПРОИЗВ((MOD(СТРОКА($C$2:$C$10)-СТРОКА($C$2)+1,3)=0)*($C$2:$C$10))

По сравнению с предыдущим примером, эта формула имеет немного более сложную «логику фильтрации»:

MOD(СТРОКА($C$2:$C$10)-СТРОКА($C$2)+1,3)=0

Сначала вы вычитаете первую строку из всех остальных строк в диапазоне, чтобы получить массив относительных номеров строк {0;1;2;3;4;5;6;7;8}, к которому вы добавляете 1, чтобы начать счет. из одного: {1;2;3;4;5;6;7;8;9}

Приведенный выше массив входит в количество аргумент функции ОСТАТ, которая возвращает остаток после деления каждого числа на 3: {1;2;0;1;2;0;1;2;0}

Обратите внимание, что нули появляются для каждой 3-й строки, а MOD()=0 сообщает Excel, что нужно возвращать TRUE только для этих строк, FALSE для всех остальных строк. Если ИСТИНА равняется 1, а ЛОЖЬ равна 0, у нас остается массив {0,0,1,0,0,1,0,0,1}.

Наконец, СУММПРОИЗВ умножает приведенный выше массив и C2:C10, а затем складывает произведения. Поскольку умножение на ноль всегда дает 0, умножению подвергаются только строки, содержащие 1 в первом массиве.

Сумма каждого N-го столбца

Чтобы просуммировать числа в каждом 2-м, 3-м и т. д. столбцах, просто замените СТРОКУ на СТОЛБЦ в приведенной выше формуле, например:

СУММПРОИЗВ((MOD(СТОЛБЦ(диапазон)-СТОЛБЕЦ(первая_ячейка)+1,н)=0)*(диапазон))

Например, чтобы сложить числа в каждом четвертом столбце в диапазоне B2:I2, используйте следующую формулу:

=СУММПРОИЗВ((MOD(СТОЛБЦ($B2:$I2)-СТОЛБЦ($B2)+1,4)=0)*($B2:$I2))

Формула модификации для объединения каждых N ячеек

В ситуациях, когда детали, относящиеся к одному и тому же элементу, разбиты на несколько ячеек, объединение содержимого каждого N ячеек в одну ячейку может стать проблемой.

В этом примере у нас есть детали заказа в трех разных ячейках в столбце B, поэтому мы хотим объединить каждый блок из трех ячеек, начиная с B2. Это можно сделать с помощью следующей формулы:

= ЕСЛИ (ОСТАТ (СТРОКА () – 1, 3) = 0, СЦЕПИТЬ (B2, “-“, B3, “-“, B4), “”)

Важная заметка! Чтобы формула работала правильно, ее необходимо ввести в 3-ю строку с данными (в данном примере C4), а затем скопировать вниз по столбцу.

Логика формулы очень проста:

Во-первых, вы проверяете, делится ли данная строка на 3 без остатка: MOD(ROW()-1, 3)=0. Чтобы получить относительное положение текущей строки, мы вычитаем 1 из фактического номера строки (поскольку наши данные начинаются со строки 2, а не с строки 1). Если остаток от деления равен 0, функция СЦЕПИТЬ объединяет значения из 3 ячеек и разделяет их разделителем по вашему выбору (“-” в этом примере). Использование относительных ссылок на ячейки обеспечивает объединение трех разных ячеек каждый раз.

Кончик. Вместо исправления -1 вы можете получить относительное положение каждой строки, как мы сделали в приведенном выше примере, т.е. , “”)

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

Модифицируйте формулы для подсчета ячеек, содержащих нечетные или четные числа.

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

Подсчет ячеек с нечетными номерами:

СУММПРОИЗВ((МОД(диапазон,2)=1)*1)

Подсчитайте ячейки с четными числами:

СУММПРОИЗВ((МОД(диапазон,2)=0)*1)

С числами в ячейках от A2 до A8 формулы принимают следующий вид:

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

=СУММПРОИЗВ((ОСТАТ(A2:A8,2)=1)*1)

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

=СУММПРОИЗВ((ОСТАТ(A2:A8,2)=0)*1)

Как использовать MOD в Excel для выделения ячеек

Помимо вычисления ячеек на основе определенного остатка, функцию ОСТАТ в Excel можно использовать для выделения этих ячеек. Для этого вам нужно будет создать правило условного форматирования на основе формулы. Подробные шаги по настройке правила можно найти здесь, а формулы приведены ниже.

Выделите нечетные и четные числа

Чтобы выделить ячейки, содержащие нечетные или четные числа, используйте следующую формулу Excel Mod, которая делит число на 2, а затем проверяет наличие остатка 1 и 0 соответственно:

Выделите ячейки с нечетными номерами:

= ОСТАТ (A2,2) = 1

Выделите ячейки с четными номерами:

= ОСТАТ (A2,2) = 0

Где A2 — самая левая ячейка с данными.

Выделите целые числа и десятичные дроби

Чтобы идентифицировать целые числа, просто следуйте этой логической цепочке: любое число, деленное на 1, равно самому себе, то есть целое число, деленное на 1, всегда является одним и тем же целым числом, то есть остаток равен нулю, что означает, что мы можем использовать эту простую формулу MOD:

= ОСТАТ (A2,1) = 0

Если остаток больше нуля, вы имеете дело с дробью (включая десятичные):

=ОСТАТ(A2,1)>0

Поскольку в правилах условного форматирования на основе формул ссылки на ячейки относятся к верхней левой ячейке выбранного диапазона, в этом примере мы ссылаемся на A2:

Выделите несколько чисел

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

Например, чтобы выделить ячейки, содержащие 10 и все его кратные, используйте эту формулу:

=МОД(A3,10)=0

Где A3 — верхняя левая ячейка в диапазоне, для которого вы создаете правило (A3: C9 на снимке экрана ниже).

Или вы можете ввести интересующее число в любую пустую ячейку, скажем, C1, и указать ссылку на эту ячейку в своей формуле (не забудьте заблокировать ячейку абсолютной ссылкой):

= ОСТАТ (A3, $ C $ 1) = 0

Excel MOD с проверкой данных

Другим разумным использованием функции Excel MOD может быть предотвращение ввода определенных данных в выбранные ячейки, таких как нечетные числа, четные числа или десятичные дроби. Это можно сделать с помощью тех же формул MOD, которые мы использовали для выделения ячеек, но на этот раз мы будем определять правило проверки данных.

Чтобы создать правило проверки данных на основе формулы, щелкните Данные > Проверка данных. На Настройки вкладка, выберите Обычай в Разрешать поле и введите формулу в поле Формула коробка. Подробные инструкции см. в разделе Как создать пользовательское правило проверки в Excel.

Например, чтобы разрешить только целые числа в ячейках A2:A8, выберите эти ячейки и настройте правило с помощью этой формулы:

= ОСТАТ (A2,1) = 0

Аналогичным образом вы можете ограничить ввод данных четными или нечетными числами:

Разрешить только нечетные числа:

= ОСТАТ (A2,2) = 1

Разрешить только четные числа: