Завантажте зразок робочої книги
Цей підручник з Excel демонструє, як користуватися Функція Excel MOD обчислити залишок після ділення.
Огляд функції MOD
Функція MOD повертає залишок або модуль числа після виконання поділу. Однак функція MOD не призначена виключно для того, щоб допомогти нам у вирішенні проблем поділу. Він стає ще більш потужним, коли ми хочемо шукати кожен N -й елемент у списку, або коли нам потрібно створити повторюваний шаблон.
Щоб використовувати функцію аркуша MOD Excel, виберіть клітинку та введіть:
Синтаксис функцій MOD та входи:
| 1 | = MOD (число, дільник) |
номер - Число.
дільник - Число для поділу.
Що таке функція MOD?
Функція MOD повертає залишок або модуль числа після виконання поділу. Однак функція MOD не призначена виключно для того, щоб допомогти нам у вирішенні проблем поділу. Він стає ще більш потужним, коли ми хочемо шукати кожен N -й елемент у списку, або коли нам потрібно створити повторюваний шаблон.
MOD Основна математика
Коли ви намагаєтесь поділити 13 на 4, ви можете сказати, що відповідь - залишок 3. "1" у цьому випадку відомий як модуль (звідси назва функції MOD). Тоді у формулі ми могли б записати
| 1 | = MOD (13, 4) |
І вихід буде 1.
Перегляд цієї таблиці дає ще деякі ілюстрації того, як працюватиме вхід/вихід MOD.
| 1 | = MOD (A2,3) |
Зауважте, що коли на вході було 3, залишку не було, а отже, результат формули дорівнював 0. Також у нашій таблиці ми використовували функцію ROW для генерації наших значень. Більшість потужності MOD буде від використання функції ROW (або COLUMN), як ми побачимо в наступних прикладах.
MOD Сума кожного другого рядка
Розглянемо цю таблицю:
Для ілюстрації другий стовпець має формулу
| 1 | = MOD (A2, 2) |
Щоб додати всі парні рядки, ви можете написати формулу SUMIF і запропонувати критеріям знайти значення 0 у стовпці B. Або, щоб додати всі непарні рядки, критеріям буде шукати 1 значення.
Однак нам зовсім не потрібно створювати стовпець -помічник. Ви можете об’єднати потужність MOD у SUMPRODUCT, щоб зробити все це за один крок. Наша формула для цього була б такою
| 1 | = ПІДПРИЄМСТВО (A2: A5, -(MOD (B2: B2, 2) = 0) |
Оскільки це в SUMPRODUCT, функція MOD зможе обробляти введення масиву. Ми вже бачили результат у стовпці -помічнику, але масив з нашого MOD у цій формулі буде {0, 1, 0, 1}. Після перевірки значень, які дорівнюють 0, застосовуючи подвійний одинар, масив буде {1, 0, 1, 0}. Потім SUMPRODUCT робить магію або множить масиви, щоб створити {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, -(MOD (РЯД (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 за стовпцями
До цього часу ми використовували приклади, які йдуть вертикально та використовують ROW, але ви також можете переходити по горизонталі за допомогою функції COLUMN. Розглянемо такий макет:
Ми хочемо підсумувати всі пункти. Наша формула цього може бути
| 1 | = ПІДПРИЄМСТВО (B2: E2*(MOD (КОЛОНКА (B2: E2), 2) = 0) |
У цьому випадку ми налаштовуємо збирати кожні 2nd стовпець у нашому діапазоні, тому SUMPRODUCT зберігатиме тільки ненульові значення для стовпців B & D. Для довідки тут таблиця, що показує номери стовпців та їх відповідне значення після взяття MOD 2.
Виділіть кожен N -й рядок
Інше поширене місце використання функції MOD - це коли ви хочете, щоб виділений рядок відображався в кожному N -му рядку. Загальною формою для цього буде
| 1 | = MOD (ROW () ± Зсув, N) = 0 |
Де N - це кількість рядків між кожним виділеним рядком (тобто для виділення кожних 3rd ряд, N = 3) і Зсув - це необов’язково число, яке нам потрібно додати або відняти, щоб наш перший виділений рядок вирівнявся за N (тобто виділити кожні 3rd рядок, але почніть з рядка 5, нам потрібно буде відняти 2, оскільки 5 -2 = 3). Зауважте, що за допомогою функції ROW, опускаючи будь -які аргументи, вона поверне номер рядка з комірки, у якій знаходиться формула.
Давайте скористаємось нашою таблицею від раніше:
Щоб застосувати виділення до всіх рядків Усього, ми створимо нове правило умовного форматування з формулою
| 1 | = MOD (ROW ()-1, 3) = 0 |
Коли умовне форматування застосовує цю формулу, відображатиметься рядок 2
| 1234 | = MOD (2-1, 3) = 0= MOD (1, 3) = 0= 1 = 0= Неправда |
Ряд 3 отримає подібний результат, але потім побачить рядок 4
| 1234 | = MOD (4-1, 3) = 0= MOD (3, 3) = 0= 0 = 0= Правда |
Таким чином, наше правило працює коректно, як показано тут:
Виділіть цілі чи парні числа
Замість виділення певних рядків ви також можете перевірити фактичні значення всередині комірок. Це може бути корисним, якщо ви хочете знайти числа, кратні N. Наприклад, щоб знайти кратні 3, ваша формула умовного формату буде
| 1 | = MOD (A2, 3) = 0 |
До цього моменту ми мали справу з цілими числами. Ви можете мати введення десяткового числа (наприклад, 1.234), а потім поділити на 1, щоб отримати лише десяткову частину (наприклад, 0.234). Ця формула виглядає так
| 1 | = MOD (A2, 1) |
Знаючи, що для виділення лише цілих чисел формула умовного формату буде такою
| 1 | = MOD (A2, 1) = 0 |
Об'єднайте кожні N клітин
Раніше ми використовували MOD, щоб повідомляти комп’ютеру, коли потрібно брати за значення для кожного N -го елемента. Ви також можете використати його для запуску більшої формули. Розглянемо такий макет:
Ми хочемо об’єднати імена разом, але лише кожні 3rd рядок, починаючи з рядка 2. Для цього використовується формула
| 1 | = IF (MOD (ROW ()+1, 3) = 0, ЗВ'ЯЗАТИ (A2, "", A3, "", A4), "") |
Наша функція MOD є тим, що діє як критерій загальної функції IF. У цьому прикладі нам потрібно було додати 1 до нашого ряду, тому що ми починаємо з 2 -го рядка (2 + 1 = 3). Коли вихідний сигнал MOD дорівнює 0, формула виконує конкатенацію. В іншому випадку він просто повертається порожнім.
Підрахувати парні/непарні значення
Якщо вам коли -небудь доводилося підраховувати, скільки парних чи непарних значень знаходиться в діапазоні, ви будете знати, що COUNTIF не має можливості цього робити. Хоча ми можемо це зробити за допомогою MOD та SUMPRODUCT. Розглянемо цю таблицю:
Формула, яку ми будемо використовувати для пошуку непарних значень, буде такою
| 1 | = ПІДПРИЄМСТВО (1*(MOD (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 |
Потім SUMPRODUCT додає значення в нашому масиві, даючи бажану відповідь: 5.
Повторюваний візерунок
Усі попередні приклади перевіряли значення MOD на виході. Ви також можете використовувати MOD для створення повторюваного шаблону чисел, що в свою чергу може бути дуже корисним.
По -перше, скажімо, у нас був список пунктів, які ми хочемо повторити.
Ви можете спробувати вручну скопіювати та вставити скільки завгодно разів, але це стане нудним. Натомість ми хочемо використовувати функцію INDEX для отримання наших значень. Для того, щоб INDEX працював, нам потрібен аргумент ряду - це послідовність чисел, що відповідає {1, 2, 3, 1, 2, 3, 1 тощо}. Ми можемо досягти цього за допомогою MOD.
По -перше, ми почнемо лише з функції ROW. Якщо почати з
| 1 | = РЯД (A1) |
А потім скопіюйте це вниз, ви отримаєте основну послідовність чисел {1, 2, 3, 4, 5, 6,…}. Якби ми застосували функцію MOD з дільником 3,
| 1 | = MOD (РЯД (A1), 3) |
ми отримаємо {1, 2, 0, 1, 2, 0,…}. Ми бачимо, що у нас є повторювана модель "0, 1, 2", але в першій серії відсутня початкова 0. Щоб виправити це, зробіть резервну копію кроку і відніміть 1 з номера рядка. Це змінить нашу початкову послідовність на {0, 1, 2, 3, 4, 5,…}
| 1 | = MOD (РЯД (A1) -1, 3) |
І після того, як він вийде з MOD, ми маємо {0, 1, 2, 0, 1, 2,…}. Це наближається до того, що нам потрібно. Останній крок - додати 1 до масиву.
| 1 | = MOD (РЯД (A1) -1, 3) +1 |
Що тепер створює послідовність чисел {1, 2, 3, 1, 2, 3,…}. Це наша бажана послідовність! Підключивши його до функції INDEX, ми отримаємо формулу
| 1 | = ІНДЕКС (Мій список, MOD (РЯД (A1) -1, 3) +1) |
Тепер результат буде виглядати так:
Приклади MOD у VBA
Ви також можете використовувати функцію LINEST у VBA.
У рамках VBA MOD - це оператор (так само, як оператори плюс, мінус, множення та ділення). Отже, виконання наступних операторів VBA
| 123456 | Діапазон ("C2") = Діапазон ("A2") Діапазон модифікацій ("B2")Діапазон ("C3") = Діапазон ("A3") Діапазон модифікацій ("B3")Діапазон ("C4") = Діапазон ("A4") Діапазон модифікацій ("B4")Діапазон ("C5") = Діапазон ("A5") Діапазон модифікацій ("B5")Діапазон ("C6") = Діапазон ("A6") Діапазон модифікацій ("B6")Діапазон ("C7") = Діапазон ("A7") Діапазон модифікацій ("B7") |
дасть такі результати
Для аргументів функції (відомі_y та ін.) Ви можете або ввести їх безпосередньо у функцію, або визначити змінні, які потрібно використовувати замість них.
Повернення до списку всіх функцій Excel
Функція MOD Google Таблиць
Функція MOD працює точно так само в Google Таблицях, як і в Excel:
