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

Обновлено: 21.11.2024

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

Как финансовый аналитик Финансовый аналитик Описание работы Приведенное ниже описание работы финансового аналитика дает типичный пример всех навыков, образования и опыта, необходимых для найма на работу аналитика в банке, учреждении или корпорации. Выполнять финансовое прогнозирование, отчетность и отслеживать операционные показатели, анализировать финансовые данные, создавать финансовые модели, функция полезна, когда нам нужно обработать каждое n-е значение. Например, если наши данные состоят из нескольких столбцов чисел, мы можем использовать MOD для суммирования каждого n-го столбца.

Формула

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

Эта функция использует следующие аргументы:

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

Как использовать функцию ОСТАТ в Excel?

Чтобы понять, как используется функция MOD, рассмотрим несколько примеров:

Пример 1

Предположим, что нам даны следующие данные:

В приведенных выше данных мы использовали следующую формулу:

Результаты, которые мы получаем в Excel, показаны ниже:

Пример 2

Предположим, что нам даны следующие данные:

Если мы хотим получить максимальное значение каждого n-го столбца, мы можем использовать формулу массива, основанную на функциях MAX, MOD и COLUMN.

Используемая формула:

Поскольку это формула массива, нам нужно ввести ее, используя Ctrl + Shift + Enter. В приведенной выше формуле мы использовали функции MOD и COLUMN, чтобы отфильтровать значения не из n-го столбца, а затем выполнить MAX для результата.

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

Допустим, нам нужно найти функцию MIN, формула будет .

Кроме того, если мы хотим получить СУММПРОИЗВ четных строк, мы можем использовать следующую формулу:

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

Для нечетных строк формула будет такой:

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

Несколько вещей, которые нужно помнить

Дополнительные ресурсы

Спасибо, что прочитали руководство CFI по важным функциям Excel! Потратив время на изучение и освоение этих функций Excel, вы значительно ускорите свой финансовый анализ. Чтобы узнать больше, ознакомьтесь с этими дополнительными ресурсами CFI:

Функции Excel для финансов Excel для финансов В этом руководстве по Excel для финансов представлены 10 основных формул и функций, которые необходимо знать, чтобы стать отличным финансовым аналитиком в Excel.
Усовершенствованные формулы Excel, которые необходимо знать Усовершенствованные формулы Excel, которые необходимо знать Эти расширенные формулы Excel крайне важны для понимания и выведут ваши навыки финансового анализа на новый уровень. Загрузите нашу бесплатную электронную книгу Excel!
Сочетания клавиш Excel для ПК и Mac Ярлыки Excel для ПК Mac Сочетания клавиш Excel — список наиболее важных и распространенных сочетаний клавиш MS Excel для пользователей ПК и Mac, специалистов в области финансов и бухгалтерского учета. Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, работу с данными, редактирование формул и ячеек и другие сочетания клавиш.

Бесплатное руководство по Excel

Чтобы овладеть искусством работы с Excel, ознакомьтесь с БЕСПЛАТНЫМ ускоренным курсом CFI по Excel. Основы Excel — формулы для финансов Вы ищете ускоренный курс по Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. , который научит вас, как стать опытным пользователем Excel. Изучите самые важные формулы, функции и сочетания клавиш, чтобы уверенно проводить финансовый анализ.

Запустите бесплатный курс CFI по Excel прямо сейчас Основы Excel — формулы для финансов Вы ищете ускоренный курс Excel? Пройдите бесплатное обучение Excel, чтобы начать карьеру в сфере корпоративных финансов и инвестиционно-банковских услуг, от Института корпоративных финансов.

Итак, MOD(13,3) возвращает 1, так как при делении 13 на 3 в частном получается 4, а в остатке остается 1.

Давайте рассмотрим несколько примеров, чтобы узнать, как получить остаток в Excel с помощью формулы MOD.

Посмотрите видео об формуле остатка в Excel на YouTube и поставьте лайк!

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

ШАГ 1: Нам нужно ввести функцию MOD:

ШАГ 2: Аргументы MOD:

число1

На какое первое число мы собираемся делить?

Ссылка на ячейку, содержащую первое число:

=МОД (C9,

число2

Что такое делитель?

Ссылка на ячейку, содержащую второе число:

=МОД (C9, D9)

ШАГ 3. Проделайте то же самое с остальными ячейками, перетащив формулу MOD до упора вниз с помощью левой кнопки мыши.

Теперь вы можете получить остатки всех операций деления!

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

Используйте функцию MOD для выделения ячеек

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

Давайте посмотрим на таблицу данных ниже:

Давайте следуем приведенному ниже пошаговому руководству, чтобы выделить каждую четвертую строку этой таблицы данных:

ШАГ 1. Выберите всю таблицу данных.

ШАГ 2. Перейдите в раздел Главная > Условное форматирование > Новое правило.

ШАГ 2. В диалоговом окне "Новое форматирование" выберите "Использовать формулу для определения форматируемых ячеек из списка".

ШАГ 4. Введите формулу

=МОД(СТРОКА(),4)=0

в текстовом поле Форматировать значения, где эта формула верна, а затем нажмите кнопку «Формат», чтобы применить пользовательское форматирование к этим строкам.

С помощью этой формулы можно форматировать каждую четвертую строку данных. Вы можете изменить число на 2,3,5… в соответствии с вашими потребностями.

ШАГ 5. В диалоговом окне «Формат ячеек» перейдите на вкладку «Заливка» и выберите подходящий цвет. Нажмите "ОК".

ШАГ 6. Здесь вы можете увидеть предварительный просмотр того, как будут выглядеть ваши отформатированные данные. Если вас это устраивает, нажмите OK.

Ваша условно отформатированная таблица данных с использованием формулы MOD готова!

Чтобы выделить n-й столбец, вы можете просто заменить формулу ROW на формулу COLUMN.

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

Как вы видели, вы можете использовать функцию MOD, чтобы выделить n-ю строку или столбец ваших данных. Вы также можете использовать его для суммирования каждой n-й строки/столбца.

Использовать функцию MOD для вычисления ячеек

В приведенных ниже данных представлены данные о продажах для разных продуктов и разных регионов.

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

Формула для суммы каждой четной строки (Продажи в Северном регионе):

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

Формула для суммы каждой нечетной строки (Продажи в Южном регионе):

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

Функция СТРОКА возвращает номер строки ячейки.
Функция MOD при делении четного числа оставляет остаток равным 0, а при делении нечетного числа оставляет остаток равным 1.
Двойные тире (- -) вне функции MOD преобразуют FALSE и TRUE в 0 и 1.
Наконец, функция СУММПРОИЗВ умножает сумму произведения диапазона, содержащего 0 и 1, и диапазона, содержащего сумму продаж.

Формула для расчета общего объема продаж в Северном регионе, т. е. сумма всех четных строк (строка 2, строка 4, строка 6 и строка 8), будет следующей:

Формула для расчета общего объема продаж в Южном регионе, т. е. сумма всех нечетных строк (строка 3, строка 5, строка 7 и строка 9), будет следующей:

Заключение

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

Чтобы узнать больше о различных функциях, доступных в Excel, нажмите здесь!

Функция ОСТАТ в 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. В результате приведенная ниже формула возвращает четное значение.

<р>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-й строки, чтобы узнать, как создать эту формулу массива.

Функция ОСТАТ возвращает остаток или модуль числа после выполнения деления. Однако функция 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

Попрактикуйтесь в функциях и формулах Excel с помощью наших 100 % бесплатных тренировочных листов!

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