Загрузите образец книги
Это руководство по Excel демонстрирует, как использовать Функция Excel MOD чтобы вычислить остаток после деления.
Обзор функций MOD
Функция MOD возвращает остаток или модуль числа после выполнения деления. Однако функция MOD предназначена не только для того, чтобы помочь нам с проблемами разделения. Он становится еще более мощным, когда мы хотим искать каждый N-й элемент в списке или когда нам нужно сгенерировать повторяющийся шаблон.
Чтобы использовать функцию таблицы MOD Excel, выберите ячейку и введите:
Синтаксис и входные данные функции MOD:
| 1 | = МОД (число; делитель) |
количество - Число.
делитель - Число, на которое нужно разделить.
Что такое функция MOD?
Функция MOD возвращает остаток или модуль числа после выполнения деления. Однако функция MOD предназначена не только для того, чтобы помочь нам с проблемами разделения. Он становится еще более мощным, когда мы хотим искать каждый N-й элемент в списке или когда нам нужно сгенерировать повторяющийся шаблон.
MOD Базовая математика
Когда вы пытаетесь разделить 13 на 4, вы можете сказать, что ответ - 3 остатка 1. «1» в этом случае называется модулем (отсюда и название функции MOD). Тогда в формуле мы могли бы написать
| 1 | = МОД (13; 4) |
И на выходе будет 1.
Эта таблица дает еще несколько иллюстраций того, как будет работать ввод / вывод MOD.
| 1 | = МОД (A2,3) |
Обратите внимание, что когда на входе было 3, не было остатка, и, следовательно, на выходе формулы было 0. Кроме того, в нашей таблице мы использовали функцию СТРОКА для генерации наших значений. Большая часть возможностей MOD будет заключаться в использовании функции ROW (или COLUMN), как мы увидим в следующих примерах.
MOD Суммируйте каждую вторую строку
Рассмотрим эту таблицу:
Для наглядности второй столбец имеет формулу
| 1 | = МОД (A2; 2) |
Чтобы добавить все четные строки, вы можете написать формулу СУММЕСЛИ, и критерии будут искать 0 значений в столбце B. Или, чтобы добавить все нечетные строки, задайте критерий поиска 1 значения.
Однако нам вообще не нужно создавать вспомогательный столбец. Вы можете объединить возможности MOD в SUMPRODUCT, чтобы сделать все за один шаг. Наша формула для этого была бы
| 1 | = СУММПРОИЗВ (A2: A5; - (MOD (B2: B2; 2) = 0) |
Поскольку он находится в СУММПРОИЗВЕ, функция МОД сможет обрабатывать ввод нашего массива. Мы уже видели результат в вспомогательном столбце, но массив из нашего MOD в этой формуле будет {0, 1, 0, 1}. После проверки значений, равных 0, с применением двойного унарного массива, массив будет {1, 0, 1, 0}. Затем СУММПРОИЗВ выполняет свое волшебство или умножает массивы для получения {2, 0, 4, 0}, а затем суммирует, чтобы получить желаемый результат, равный 6.
MOD Суммировать каждую N-ю строку
Поскольку формула MOD (x, N) будет выводить 0 для каждого N-го значения, мы можем использовать это, чтобы помочь формулам выбирать, какие значения использовать в других функциях. Посмотри на эту таблицу.
Наша цель - получить значения из каждой строки с пометкой «Итого». Обратите внимание, что Итого появляется каждые 3rd строки, но начиная со строки 4. Наша функция MOD, таким образом, будет использовать 3 как 2nd аргумент, и нам нужно вычесть 1 из первого аргумента (поскольку 4 -1 = 3). Таким образом, нужные нам строки (4, 7, 10) будут кратны 3 (3, 6, 9). Наша формула для суммирования желаемых значений будет
| 1 | = СУММПРОИЗВ (C2: C10; - (МОД (СТРОКА (A2: A10) +2; 3) = 0)) |
Созданный массив преобразуется следующим образом:
| 12345 | {2, 3, 4, 5, 6, 7, 8, 9, 10}{1, 2, 3, 4, 5, 6, 7, 8, 9}{1, 2, 0, 1, 2, 0, 1, 2, 0}{Ложь, Ложь, Истина, Ложь, Ложь, Истина, Ложь, Ложь, Истина}{0, 0, 1, 0, 0, 1, 0, 0, 1} |
В нашем массиве критериев SUMPRODUCT теперь настроено, как нам нужно получать каждые 3rd значение, и мы получим желаемый результат в размере 90 долларов.
Сумма MOD по столбцам
До сих пор мы использовали примеры, которые идут вертикально и используют СТРОКУ, но вы также можете двигаться горизонтально с помощью функции СТОЛБЕЦ. Рассмотрим этот макет:
Мы хотим подвести итоги по всем пунктам. Наша формула для этого могла бы быть
| 1 | = СУММПРОИЗВ (B2: E2 * (MOD (COLUMN (B2: E2); 2) = 0) |
В этом случае мы настроены на получение каждые 2nd столбец в пределах нашего диапазона, поэтому СУММПРОИЗВ сохранит только ненулевые значения для столбцов B и D. Для справки, вот таблица, показывающая номера столбцов и их соответствующие значения после выбора MOD 2.
Выделите каждую N-ю строку
Еще одно распространенное место использования функции MOD - это когда вы хотите, чтобы выделенная строка отображалась в каждой N-й строке. Общая форма для этого будет
| 1 | = MOD (СТРОКА () ± Смещение, N) = 0 |
Где N это количество строк между каждой выделенной строкой (т.е., чтобы выделить каждые 3rd ряд, N = 3), и Компенсировать - это необязательно число, которое нам нужно добавить или вычесть, чтобы наша первая выделенная строка выровнялась с N (то есть, чтобы выделить каждые 3rd ряду, но начинаем со строки 5, нам нужно вычесть 2, так как 5-2 = 3). Обратите внимание, что при использовании функции СТРОКА, опуская любые аргументы, она вернет номер строки из ячейки, в которой находится формула.
Давайте воспользуемся нашей таблицей ранее:
Чтобы выделить все строки итогов, мы создадим новое правило условного форматирования с формулой
| 1 | = МОД (СТРОКА () - 1, 3) = 0 |
Когда условное форматирование применяет эту формулу, в строке 2 будет отображаться
| 1234 | = МОД (2-1; 3) = 0= МОД (1, 3) = 0= 1 = 0= Ложь |
Строка 3 получит аналогичный результат, но затем строка 4 увидит
| 1234 | = МОД (4-1; 3) = 0= МОД (3, 3) = 0= 0 = 0= Верно |
Таким образом, наше правило работает правильно, как показано здесь:
Выделите целые или четные числа
Вместо того, чтобы выделять определенные строки, вы также можете проверить фактические значения в ячейках. Это может быть полезно, когда вы хотите найти числа, кратные N. Например, чтобы найти числа, кратные 3, ваша формула условного формата будет выглядеть так:
| 1 | = МОД (A2; 3) = 0 |
До этого момента мы имели дело с целыми числами. Однако вы можете ввести десятичную дробь (например, 1,234), а затем разделить ее на 1, чтобы получить только десятичную часть (например, 0,234). Эта формула выглядит как
| 1 | = МОД (A2; 1) |
Зная это, чтобы выделить только целые числа, формула условного формата будет
| 1 | = МОД (A2; 1) = 0 |
Объедините каждые N ячеек
Ранее мы использовали MOD, чтобы сообщать компьютеру, когда нужно брать значение за каждый N-й элемент. Вы также можете использовать его для запуска более крупной формулы. Рассмотрим этот макет:
Мы хотим объединить имена вместе, но только через каждые 3rd строка, начинающаяся со строки 2. Для этого используется формула
| 1 | = ЕСЛИ (МОД (СТРОКА () + 1, 3) = 0, СЦЕПИТЬ (A2, "", A3, "", A4), "") |
Наша функция MOD - это то, что действует как критерий для общей функции IF. В этом примере нам нужно добавить 1 к нашей СТРОКЕ, потому что мы начинаем со строки 2 (2 + 1 = 3). Когда выход MOD равен 0, формула выполняет конкатенацию. В противном случае он просто возвращается пустым.
Подсчет четных / нечетных значений
Если вам когда-либо приходилось подсчитывать, сколько четных или нечетных значений находится в диапазоне, вы знаете, что COUNTIF не может этого сделать. Однако мы можем сделать это с помощью MOD и SUMPRODUCT. Давайте посмотрим на эту таблицу:
Формула, которую мы будем использовать для нахождения нечетных значений, будет
| 1 | = СУММПРОИЗВ (1 * (МОД (A2: A7; 2) = 1)) |
Вместо того, чтобы загружать некоторые номера строк, наш MOD будет загружать фактические значения ячеек в массив. Общее преобразование будет происходить следующим образом:
| 1234 | {5, 5, 3, 3, 2, 1}{1, 1, 1, 1, 0, 1} <- Взял мод 2{True, True, True, True, False, True} <- Проверено, было ли значение 0{1, 1, 1, 1, 0, 1} <- умножить на 1 для преобразования из True / False в 1/0 |
Затем СУММПРОИЗВ складывает значения в нашем массиве, давая желаемый ответ: 5.
Повторяющийся узор
Все предыдущие примеры проверяли вывод MOD на предмет значения. Вы также можете использовать MOD для создания повторяющегося шаблона чисел, что, в свою очередь, может быть очень полезно.
Во-первых, предположим, что у нас есть список элементов, которые мы хотим повторить.
Вы можете попробовать скопировать и вставить вручную сколько угодно раз, но это будет утомительно. Вместо этого мы захотим использовать функцию ИНДЕКС для получения наших значений. Чтобы ИНДЕКС работал, нам нужно, чтобы аргумент строки был последовательностью чисел, которая идет {1, 2, 3, 1, 2, 3, 1 и т. Д.}. Мы можем сделать это с помощью MOD.
Во-первых, мы начнем с функции СТРОКА. Если вы начнете с
| 1 | = СТРОКА (A1) |
А затем скопируйте это вниз, вы получите базовую числовую последовательность {1, 2, 3, 4, 5, 6,…}. Если мы применили функцию MOD с 3 в качестве делителя,
| 1 | = МОД (СТРОКА (A1); 3) |
мы получим {1, 2, 0, 1, 2, 0,…}. Мы видим, что у нас есть повторяющийся шаблон «0, 1, 2», но в первой серии отсутствует начальный 0. Чтобы исправить это, сделайте резервную копию шага и вычтите 1 из номера строки. Это изменит нашу начальную последовательность на {0, 1, 2, 3, 4, 5,…}
| 1 | = МОД (СТРОКА (A1) -1; 3) |
А после выхода из MOD у нас есть {0, 1, 2, 0, 1, 2,…}. Это приближается к тому, что нам нужно. Последний шаг - добавить 1 в массив.
| 1 | = МОД (СТРОКА (A1) -1; 3) +1 |
Что теперь дает числовую последовательность {1, 2, 3, 1, 2, 3,…}. Это наша желаемая последовательность! Подключив его к функции ИНДЕКС, мы получим нашу формулу
| 1 | = ИНДЕКС (MyList; МОД (СТРОКА (A1) -1; 3) +1) |
Теперь результат будет выглядеть так:
Примеры модов в VBA
Вы также можете использовать функцию ЛИНЕЙН в VBA.
В VBA MOD - это оператор (точно так же, как операторы плюс, минус, умножение и деление). Итак, выполнение следующих операторов VBA
| 123456 | Диапазон ("C2") = Диапазон ("A2") Диапазон мод. ("B2")Диапазон ("C3") = Диапазон ("A3") Диапазон мод. ("B3")Диапазон ("C4") = Диапазон ("A4") Диапазон мод. ("B4")Диапазон ("C5") = Диапазон ("A5") Диапазон мод ("B5")Диапазон ("C6") = Диапазон ("A6") Диапазон мод. ("B6")Range ("C7") = Range ("A7") Mod Range ("B7") |
даст следующие результаты
Для аргументов функции (известные_y и т. Д.) Вы можете либо ввести их непосредственно в функцию, либо определить переменные, которые будут использоваться вместо них.
Вернуться к списку всех функций в Excel
Google Таблицы MOD Функция
Функция MOD работает в Google Таблицах точно так же, как и в Excel:
