Быстрый переход
Функция MOD в Microsoft Excel помогает определить, делится ли одно число на другое без остатка. Если деление нецелое, функция возвращает остаток. Например, при делении 3 на 2 функция MOD вернёт 1. В этой статье я покажу, как применять её в реальных задачах.
Чтобы повторять примеры вместе со мной, скачайте бесплатный рабочий файл Excel, который я использовал. Ссылка для загрузки находится в правом верхнем углу экрана после перехода по ней.
Синтаксис функции MOD
Прежде чем начать работать с функцией MOD, давайте разберём её структуру и принципы:
где n — число, которое вы хотите разделить, а d — делитель. Например, при делении 3 на 2, 3 — это число, а 2 — делитель.
Если делитель отрицательный, результат тоже будет отрицательным. Если делитель равен нулю, Excel покажет ошибку #DIV/0! — делить на ноль нельзя.
Важно отметить, что функция MOD редко используется сама по себе — её сила раскрывается в сочетании с другими инструментами Excel.
Пример 1: Минимизируем остатки с помощью MOD
Представьте, что у вас есть разные продукты для упаковки и распределения. В столбце A — ID продукта, в столбце B — количество каждого продукта, а во второй строке — варианты размеров упаковок. Задача — подобрать оптимальный размер упаковки для каждого продукта, исходя из их количества.
Для этого используйте функцию MOD, чтобы проверить, делится ли количество продукта из столбца B на размер упаковки из строки 2, и получить остаток при делении.
В ячейке C3 введите формулу:
Символ доллара ($) перед ссылкой на строку или столбец создаёт абсолютную или смешанную ссылку — она закрепляет ячейку, позволяя применять формулу к другим ячейкам без изменения этой части адреса.
После нажатия Enter (или Ctrl Enter, чтобы остаться в той же ячейке) вы увидите, что при упаковке продукта с ID 2805 в коробки по 10 штук, останется 4 штуки без упаковки.
Если протянуть формулу вправо с помощью «ручки заполнения» в ячейке C3, вы увидите, что упаковки размером 8 или 3 подходят для этого продукта лучше всего.
Теперь в ячейке G3 задайте формулу с INDEX и XMATCH, чтобы Excel автоматически выбрал лучший размер упаковки:
Здесь INDEX($C$2:$F$2,1 берёт данные из диапазона C2:F2, а XMATCH(SMALL(C3:F3,1),C3:F3)) находит минимальное значение в строке C3:F3 и возвращает номер столбца для INDEX.
Замените MATCH на XMATCH в Excel — вы не пожалеете
Обновите формулы и расширьте возможности своих таблиц!
Выделите ячейки с C3 по G3 и дважды кликните на «ручке заполнения», чтобы скопировать формулы для всех продуктов.
Теперь в столбце G отображается лучший размер упаковки для каждого продукта, а в столбцах C–F вы видите, сколько единиц останется после упаковки.
Если минимальное значение встречается несколько раз подряд, XMATCH обычно выбирает первый — и это отлично, ведь нам нужно использовать упаковку с наименьшим размером.
Главное преимущество такого подхода в том, что если изменится количество продукта в столбце B, оптимальный размер упаковки пересчитается автоматически благодаря формуле MOD в ячейках C3–F12. Например, при изменении количества продукта 2805 с 24 на 25, обновив значение в B3, оптимальный размер упаковки изменится на 5.
Пример 2: Как выделять ячейки с помощью MOD и условного форматирования
Функция MOD в Excel полезна не только для расчётов — её можно применять в условном форматировании, чтобы выделить нужные ячейки.
Допустим, нужно распределить активности для десяти групп, у каждой из которых разное количество участников. Задача — выделить группы, которые могут участвовать в конкретной активности без остатка участников.
Сначала в ячейке B1 создайте выпадающий список с активностями из столбца D. Для этого выделите B1, перейдите во вкладку «Данные» и выберите верхнюю часть кнопки «Проверка данных».
В появившемся окне выберите «Список» в поле «Разрешить». Затем укажите диапазон с активностями в поле «Источник» и нажмите ОК.
Как быстро добавить выпадающий список в Excel
Забудьте о многократном ручном вводе одних и тех же данных.
Теперь при выборе ячейки B1 появится список активностей. Далее можно перейти к условному форматированию диапазона ячеек A4:B13, чтобы выделить группы, подходящие под выбранную активность.
Выделите диапазон A4:B13, затем на вкладке «Главная» выберите «Условное форматирование» > «Создать правило».
Почему я всегда использую условное форматирование в Excel
Этот инструмент — мой незаменимый помощник.
В окне создания правила выберите «Использовать формулу для определения форматируемых ячеек» и введите формулу:
Обратите внимание, что знак доллара перед столбцом ($B4) фиксирует именно столбец, позволяя Excel применять правило ко всем ячейкам выбранного диапазона в этом столбце.
Таким образом выделятся группы, в которых общее количество участников делится на размер подгруппы для выбранной активности в B1 без остатка.
Далее нажмите «Формат», выберите стиль выделения — я предпочёл светло-зелёный фон.
Подтвердите нажатием ОК, и наглядно увидите: при выборе активности из списка в B1 соответствующие группы подсвечиваются. Например, для викторины выделены группы 1, 2, 3, 5 и 8 — у них число участников кратно размеру подгруппы для этой игры.
Если выбрать «Поход», подсветится только группа 5 — у неё число участников идеально подходит под требования подгруппы.
Пример 3: Разделяем дату и время с помощью MOD
Функция MOD позволяет выделить дробную часть числа. Например, формула:
в ячейке B2 разделит 10,2 на 1 и вернёт остаток 0,2 — дробную часть числа.
Целую часть можно получить с помощью функции INT, которая округляет число вниз. Например, формула:
в ячейке C2 выдаст только целую часть значения из A2.
Так же можно отделить дату и время.
В A2 записано значение с датой и временем, а в A3 — его числовое представление.
Excel хранит дату и время в виде чисел — если сменить формат ячейки на «Общий», это станет видно. В нашем случае число 45782 — это дата, а 0,4375 — время.
С помощью MOD и INT их легко разделить.
Сначала выберите ячейку B2 и назначьте формат «Время» на вкладке «Главная» в группе «Число».
12 полезных форматов чисел в Excel и как они влияют на данные
Правильный формат значительно облегчает работу с таблицами.
Потом выберите C2 и выберите формат «Краткая дата».
В ячейке B2 введите формулу:
Она выделит дробную часть числового значения даты-времени, которая отобразится в формате времени — часы и минуты.
В ячейке C2 введите формулу:
которая выделит целую часть (дату), а благодаря формату «Краткая дата» она отобразится привычным образом.
Знать, какую функцию выбрать для задачи — уже половина успеха в Excel. Например, для сложных вычислений пригодится AGGREGATE, а чтобы быстро создать список номеров — SEQUENCE. К счастью, Excel помогает найти нужную функцию через кнопку «fx» рядом со строкой формул или через окно «Вставка функции».
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Вы также можете найти наши материалы в:
- Telegram: https://t.me/gergenshin
- Youtube: https://www.youtube.com/@gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru