Как посчитать остаток в Excel

Обновлено: 03.07.2024

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Суммировать четные строки:

Суммировать нечетные строки:

Предполагая, что ячейки 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 передает массив номеров строк в аргумент number функции MOD. MOD делит номер каждой строки на 2, и вы проверяете результат:

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

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

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

Суммировать каждую N-ю строку

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

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

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

Сначала вы вычитаете первую строку из всех остальных строк диапазона, чтобы получить массив относительных номеров строк, к которому добавляете 1, чтобы начать счет с единицы:

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

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

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

Суммировать каждый N-й столбец

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

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

Изменить формулу для объединения каждых N ячеек

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Где A2 – самая левая верхняя ячейка выбранного диапазона.

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

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

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

Синтаксис и ввод функции MOD:

число — число.

divisor — число, на которое нужно разделить.

Что такое функция MOD?

Базовая математика MOD

Когда вы пытаетесь разделить 13 на 4, вы можете сказать, что ответ равен 3, остаток 1. «1» в этом случае называется модулем (отсюда и название функции MOD). Тогда в формуле мы могли бы написать

И выход будет 1.

Эта таблица дает еще несколько иллюстраций того, как будет работать ввод/вывод MOD.

Обратите внимание, что когда на входе было 3, остатка не было, и поэтому на выходе формулы был 0. Кроме того, в нашей таблице мы использовали функцию ROW для генерации наших значений. Большая часть возможностей MOD будет заключаться в использовании функции ROW (или COLUMN), как мы увидим в следующих примерах.

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

Рассмотрите эту таблицу:

Для наглядности во втором столбце есть формула

Чтобы добавить все четные строки, вы можете написать формулу СУММЕСЛИ и указать критерии поиска значений 0 в столбце B. Или, чтобы добавить все нечетные строки, критерии должны искать значения 1.

Однако нам вообще не нужно создавать вспомогательный столбец. Вы можете объединить мощь MOD в SUMPRODUCT, чтобы сделать все это за один шаг. Наша формула для этого будет

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

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

Поскольку формула MOD(x, N) будет выводить 0 для каждого N-го значения, мы можем использовать это, чтобы помочь формулам выбирать, какие значения использовать в других функциях. Посмотрите на эту таблицу.

Наша цель — получить значения из каждой строки с пометкой «Всего». Обратите внимание, что сумма появляется в каждой 3-й строке, но начиная с 4-й. Таким образом, наша функция ОСТАТ будет использовать 3 в качестве 2-го аргумента, и нам нужно вычесть 1 из первого аргумента (поскольку 4 -1 = 3). Таким образом, нужные нам строки (4, 7, 10) будут кратны 3 (3, 6, 9). Наша формула для суммирования желаемых значений будет

Созданный массив преобразуется следующим образом:

Наш массив критериев СУММПРОИЗВ теперь настроен так, как нам нужно получить каждое 3-е значение, и мы получим желаемый результат в размере 90 долларов США.

Сумма MOD по столбцам

До сих пор мы использовали примеры, которые идут по вертикали и используют ROW, но вы также можете двигаться по горизонтали с помощью функции COLUMN. Рассмотрим этот макет:

Мы хотим суммировать все пункты. Наша формула для этого может быть

В этом случае мы настроены на захват каждого второго столбца в нашем диапазоне, поэтому СУММПРОИЗВ будет сохранять ненулевые значения только для столбцов B и D. Для справки вот таблица, показывающая номера столбцов и их соответствующие значения после принимая МОД 2.

Выделить каждую N-ю строку

Еще один распространенный случай использования функции MOD – это когда вы хотите, чтобы выделенная строка появлялась в каждой N-й строке. Общая форма для этого будет

Где N — количество строк между каждой выделенной строкой (т. е. для выделения каждой 3-й строки N = 3), а Смещение — необязательное число, которое мы нужно добавить или вычесть, чтобы наша первая выделенная строка совпадала с N (т. е. чтобы выделить каждую 3-ю строку, но начать с строки 5, нам нужно вычесть 2, поскольку 5 -2 = 3). Обратите внимание, что функция СТРОКА, опуская какие-либо аргументы, возвращает номер строки из ячейки, в которой находится формула.

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

Когда условное форматирование применяет эту формулу, строка 2 будет отображаться

Строка 3 получит аналогичный вывод, но затем строка 4 увидит

Выделяйте целые или четные числа

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

До сих пор мы имели дело с целыми числами. Однако вы можете ввести десятичное число (например, 1,234), а затем разделить на 1, чтобы получить только десятичную часть (например, 0,234). Эта формула выглядит как

Зная это, чтобы выделить только целые числа, формула условного формата будет

Объединить каждые N ячеек

Ранее мы использовали MOD, чтобы сообщать компьютеру, когда нужно получить значение для каждого N-го элемента. Вы также можете использовать его для запуска более крупной формулы. Рассмотрим этот макет:

Мы хотим соединить имена вместе, но только в каждой 3-й строке, начиная со строки 2. Для этого используется формула

Наша функция MOD — это то, что действует как критерий для общей функции ЕСЛИ. В этом примере нам нужно было добавить 1 к нашей ROW, потому что мы начинаем со строки 2 (2 + 1 = 3). Когда вывод MOD равен 0, формула выполняет конкатенацию. В противном случае возвращается пустое значение.

Подсчет четных/нечетных значений

Если вам когда-либо приходилось подсчитывать, сколько четных или нечетных значений содержится в диапазоне, вы знаете, что функция СЧЁТЕСЛИ не позволяет этого делать. Однако мы можем сделать это с помощью MOD и SUMPRODUCT. Давайте посмотрим на эту таблицу:

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

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

Затем СУММПРОИЗВ суммирует значения в нашем массиве, давая желаемый ответ: 5.

Повторяющийся шаблон

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

Во-первых, предположим, что у нас есть список элементов, которые мы хотим повторить.

Вы можете попытаться вручную скопировать и вставить столько раз, сколько вам нужно, но это будет утомительно. Вместо этого мы будем использовать функцию ИНДЕКС для получения наших значений. Чтобы ИНДЕКС работал, нам нужно, чтобы аргумент строки был последовательностью чисел, которая идет . Мы можем сделать это с помощью MOD.

Во-первых, мы начнем только с функции ROW. Если начать с

А затем скопируйте это вниз, вы получите базовую числовую последовательность . Если бы мы применили функцию ОСТАТ с 3 в качестве делителя,

мы получим . Мы видим, что у нас есть повторяющийся шаблон «0, 1, 2», но в первой серии отсутствует начальный 0. Чтобы это исправить, вернитесь на шаг назад и вычтите 1 из номера строки. Это изменит нашу начальную последовательность на

И после выхода мода у нас есть . Это приближается к тому, что нам нужно. Последний шаг — добавить 1 к массиву.

Что теперь создает числовую последовательность . Это наша желаемая последовательность! Подключив его к функции ИНДЕКС, мы получим нашу формулу

Теперь вывод будет выглядеть следующим образом:

Примеры MOD в VBA

Вы также можете использовать функцию ЛИНЕЙН в VBA.

В VBA MOD является оператором (точно так же, как плюс, минус, операторы умножения и деления). Итак, выполнение следующих операторов VBA

получит следующие результаты

Аргументы функции (известные_y и т. д.) можно либо ввести непосредственно в функцию, либо определить используемые вместо них переменные.

Функция модификации Google Таблиц

Функция MOD работает точно так же в Google Таблицах, как и в Excel:

Практический лист Excel

Роберт Брук / Science Photo Library / Getty Images

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

Информация в этой статье относится к Excel для Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 и Excel для Mac.

Синтаксис и аргументы функции MOD

Синтаксис функции относится к макету функции и включает имя функции, квадратные скобки и аргументы.

Синтаксис функции ОСТАТ:

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

Если в качестве аргумента делителя введен ноль.
Если для аргумента Делитель введена ссылка на пустую ячейку.

Использовать функцию MOD Excel

Введите данные в ячейки. Чтобы следовать этому руководству, введите 5 в ячейку D1 и введите 2 в ячейку D2.

Выберите ячейку E1. Здесь будут отображаться результаты.

Выберите вкладку "Формулы".

Выберите Math & Trig, чтобы открыть раскрывающийся список.

Выберите MOD, чтобы открыть диалоговое окно «Аргументы функции».

В диалоговом окне поместите курсор в текстовое поле "Число".

Выберите ячейку D1 на листе.

В диалоговом окне поместите курсор в текстовое поле Divisor.

Выберите ячейку D2 на листе.

Выберите OK в диалоговом окне.

Ответ 1 появляется в ячейке E1 (5, разделенное на 2, дает остаток 1).

Выберите ячейку E1, чтобы увидеть полную функцию =MOD( D1,D2) в строке формул над рабочим листом.

Поскольку функция ОСТАТ возвращает только остаток, целая часть операции деления (2) не отображается. Чтобы отобразить целое число как часть ответа, используйте функцию ЧАСТНОЕ.

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

<р>1. Приведенная ниже функция MOD возвращает 9.

Объяснение: 59 разделить на 10 равно 5 с остатком 9.

<р>2. Приведенная ниже функция MOD возвращает 1.

Объяснение: 7 разделить на 2 равно 3 с остатком 1.

<р>3. Приведенная ниже функция MOD возвращает 0.

Объяснение: 36 разделить на 6 равно 6 с остатком 0.

Используйте ЕСЛИ и ОСТАТ, чтобы определить, является ли число четным или нечетным. Четные числа, разделенные на 2, всегда дают остаток 0. Нечетные числа, деленные на 2, всегда дают остаток 1.

<р>4. Например, 28 разделить на 2 равно 14 с остатком 0.В результате приведенная ниже формула возвращает значение Even.

<р>5. Например, 29 разделить на 2 равно 14 с остатком 1. В результате приведенная ниже формула возвращает значение Odd.

Давайте рассмотрим еще один классный пример.

<р>6. Приведенная ниже формула возвращает 0 для каждой третьей строки.

Объяснение: функция СТРОКА возвращает номер строки ячейки. Для первой строки MOD(1,3) = 1, потому что 1, деленное на 3, равно 0 с остатком 1. Для третьей строки MOD(3,3) = 0, потому что 3, деленное на 3, равно 1 с остатком 0. .

<р>7. Используйте эту формулу для суммирования каждой третьей строки в Excel.

Примечание. Чтобы закончить формулу массива, нажмите CTRL + SHIFT + ENTER. Excel добавляет фигурные скобки <>. Используете Excel 365 или Excel 2021? Закончите, просто нажав Enter. Вы не увидите фигурных скобок. Посетите нашу страницу о суммировании каждой n-й строки, чтобы узнать, как создать эту формулу массива.

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