Изтеглете примерната работна книга
Този урок за Excel демонстрира как да използвате Excel MOD функция за изчисляване на остатъка след разделяне.
MOD Общ преглед на функцията
Функцията MOD връща остатъка или модула от число след извършване на разделяне. Функцията MOD обаче не е само за да ни помогне с нашите проблеми с разделянето. Той става още по -мощен, когато искаме да търсим всеки N -ти елемент в списък или когато трябва да генерираме повтарящ се модел.
За да използвате функцията за работен лист MOD Excel, изберете клетка и въведете:
MOD Функция Синтаксис и входове:
| 1 | = MOD (число, делител) |
номер - Номер.
делител - Число за разделяне.
Какво представлява MOD функцията?
Функцията MOD връща остатъка или модула от число след извършване на разделяне. Функцията MOD обаче не е само за да ни помогне с нашите проблеми с разделянето. Той става още по -мощен, когато искаме да търсим всеки N -ти елемент в списък или когато трябва да генерираме повтарящ се модел.
MOD Основна математика
Когато се опитате да разделите 13 на 4, можете да кажете, че отговорът е 3 остатък 1. „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 (ROW (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}{False, False, True, False, False, True, False, False, True}{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 () ± Offset, N) = 0 |
Където н е броят на редовете между всеки маркиран ред (т.е., за да маркирате всеки 3rd ред, N = 3) и Изместване е по избор числото, което трябва да добавим или извадим, за да накараме първия ни подчертан ред да се подравни с N (т.е. да подчертае всеки 3rd ред, но започнете от ред 5, ще трябва да извадим 2, тъй като 5 -2 = 3). Обърнете внимание, че с функцията ROW, като пропусне аргументи, тя ще върне номера на реда от клетката, в която е формулата.
Нека използваме нашата таблица от преди:
За да приложим подчертаване към всички редове Total, ще създадем ново правило за условно форматиране с формула на
| 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, CONCATENATE (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 | = ROW (A1) |
И след това копирайте това надолу, получавате основната последователност от числа от {1, 2, 3, 4, 5, 6,…}. Ако приложим MOD функцията с 3 като делител,
| 1 | = MOD (ROW (A1), 3) |
ще получим {1, 2, 0, 1, 2, 0,…}. Виждаме, че имаме повтарящ се модел от „0, 1, 2“, но в първата серия липсва първоначалната 0. За да поправите това, направете резервно копие на една стъпка и извадете 1 от номера на реда. Това ще промени началната ни последователност на {0, 1, 2, 3, 4, 5,…}
| 1 | = MOD (ROW (A1) -1, 3) |
И след като излезе от MOD, имаме {0, 1, 2, 0, 1, 2,…}. Това се доближава до това, от което се нуждаем. Последната стъпка е да добавите 1 към масива.
| 1 | = MOD (ROW (A1) -1, 3) +1 |
Което сега произвежда числова последователност от {1, 2, 3, 1, 2, 3,…}. Това е желаната от нас последователност! Включвайки го във функция INDEX, получаваме формулата на
| 1 | = INDEX (MyList, MOD (ROW (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 Sheets като в Excel:
