ОСТАТ и ABS в Excel (эксель): остаток и модуль числа

=ОСТАТ(A2;B2) возвращает остаток от деления A2 на B2: =ОСТАТ(17;5) равно 2, потому что 5 помещается в 17 три раза и 2 остаётся. =ABS(A2) возвращает число без знака, поэтому -42 становится 42. ОСТАТ по-английски называется MOD, а ABS в русском Excel называется так же. В таблицах ниже формулы записаны по-английски, но их можно вводить и по-русски, с точкой с запятой.

ФормулыСбросить

C2fx

=MOD(A2,B2)

	A	B	C	D
1	Number	Divisor	MOD	Times it fits
2	17	5	2	3
3	20	5	0	4
4	7	2	1	3
5	100	7	2	14
6	-3	2	1	-1
7	3	-2	-1	-1

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ОСТАТ(A2;B2)

Столбец D показывает вторую половину деления: ЧАСТНОЕ (QUOTIENT) возвращает, сколько целых раз помещается делитель, поэтому 17 это 3 раза по 5 плюс 2. Замените B2 на 0, и ОСТАТ вернёт #DIV/0! (в русском Excel #ДЕЛ/0!; таблицы на этой странице показывают ошибки под английскими именами), как и при делении на ноль.

Синтаксис ОСТАТ

=MOD(number, divisor)

ОСТАТ работает и с дробями: =ОСТАТ(7,5;2) равно 1,5, а =ОСТАТ(A2;1) возвращает только дробную часть числа, например 0,75 из 3,75. Для дат со временем =ОСТАТ(A2;1) оставляет время и отбрасывает дату.

ОСТАТ с отрицательными числами

Строки 6 и 7 таблицы выше показывают правило Excel: у результата знак делителя. =ОСТАТ(-3;2) равно 1, а не -1, а =ОСТАТ(3;-2) равно -1. Excel вычисляет ОСТАТ как number - divisor * ЦЕЛОЕ(number / divisor), а ЦЕЛОЕ всегда округляет вниз.

Здесь таблица и код расходятся. В JavaScript, Java, C и C# -3 % 2 равно -1; в Python -3 % 2 равно 1, как в Excel. Google Таблицы следуют Excel. Если нужен знак числа, используйте =A2-B2*ОТБР(A2/B2).

Чётное или нечётное и каждая вторая строка

Число чётное, когда ОСТАТ(number;2) равно 0. Это даёт подпись «Even» или «Odd», а вместе со СТРОКА правило, которое закрашивает каждую вторую строку. В Excel есть и ЕЧЁТН и ЕНЕЧЁТ, которые сразу возвращают ИСТИНА или ЛОЖЬ.

ФормулыСбросить

C2fx

=IF(MOD(B2,2)=0,"Even","Odd")

	A	B	C
1	Order	Qty	Even or odd
2	1001	4	Even
3	1002	7	Odd
4	1003	10	Even
5	1004	3	Odd
6	1005	8	Even
7	1006	5	Odd

Выделить A2:C7, если

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ЕСЛИ(ОСТАТ(B2;2)=0;"Even";"Odd")

Правило условного форматирования =ОСТАТ(СТРОКА($A2);2)=0 подсвечивает строки 2, 4 и 6, то есть с чётными номерами. В Excel это Главная > Условное форматирование > Создать правило > «Использовать формулу для определения форматируемых ячеек», и там работает и более короткое =ОСТАТ(СТРОКА();2)=0. Замените 2 на 3, чтобы закрасить каждую третью строку.

Каждая n-я строка: сумма каждого третьего значения

ОСТАТ(СТРОКА()-СТРОКА(first);n)=0 равно ИСТИНА для первой строки диапазона и затем для каждой n-й строки после неё. Внутри СУММПРОИЗВ это выбирает из столбца каждое третье значение, например последний месяц каждого квартала.

ФормулыСбросить

E2fx

=SUMPRODUCT((MOD(ROW(B2:B7)-ROW(B2),3)=2)*B2:B7)

	A	B	C	D	E
1	Month	Sales			Every 3rd
2	Jan	120			310
3	Feb	135
4	Mar	150
5	Apr	110
6	May	125
7	Jun	160

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СУММПРОИЗВ((ОСТАТ(СТРОКА(B2:B7)-СТРОКА(B2);3)=2)*B2:B7)

Смещения строк от 0 до 5, а =2 оставляет смещения 2 и 5: март и июнь, 150 плюс 160. Замените =2 на =0, чтобы взять январь и апрель.

Минуты в часы и минуты

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

ФормулыСбросить

B2fx

=INT(A2/60)

	A	B	C	D
1	Minutes	Hours	Minutes left	Label
2	135	2	15	2 h 15 min
3	59	0	59	0 h 59 min
4	240	4	0	4 h 0 min
5	1000	16	40	16 h 40 min

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ЦЕЛОЕ(A2/60)

1000 минут это 16 часов и 40 минут. Для настоящих значений времени (8:30, 17:45) и смен через полночь стандартная формула =ОСТАТ(end-start;1); см. расчёты со временем.

ABS: разница между двумя числами

=ABS(number) убирает знак минус. Главное её применение это величина разницы, когда неважно, какое значение больше: насколько каждый прогноз отличался от факта или укладывается ли измерение в допуск.

ФормулыСбросить

D2fx

=ABS(B2-C2)

	A	B	C	D	E
1	Week	Forecast	Actual	Off by	Within 10?
2	W1	120	112	8	TRUE
3	W2	95	109	14	FALSE
4	W3	140	138	2	TRUE
5	W4	80	93	13	FALSE
6	W5	110	104	6	TRUE

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ABS(B2-C2)

B2-C2 равно 8 для W1 и -14 для W2; ABS превращает оба в расстояние. Чтобы сложить расстояния, =СУММПРОИЗВ(ABS(B2:B6-C2:C6)) работает в любой версии Excel; здесь получается 43, итог столбца D. Если разделить его на количество, получится средняя абсолютная ошибка.

Попробуйте: ОСТАТ и ABS

ФормулыСбросить

C2fx

	A	B	C
1	Eggs	Per carton	Left over
2	350	12

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.

Ваша очередь: В C2 посчитайте, сколько яиц останется после того, как яйца из A2 разложат по полным лоткам размера из B2.

ПроверитьПоказать ответ

ФормулыСбросить

D2fx

	A	B	C	D
1	City	Morning	Evening	Change
2	Oslo	14	6

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.

Ваша очередь: В D2 покажите, на сколько градусов изменилась температура между B2 и C2, положительным числом, какое бы показание ни было выше.

ПроверитьПоказать ответ

ОСТАТ, ЧАСТНОЕ или ЦЕЛОЕ

Для положительных чисел ЧАСТНОЕ и ЦЕЛОЕ(A2/B2) совпадают, и B2*ЦЕЛОЕ(A2/B2)+ОСТАТ(A2;B2) всегда восстанавливает число. С отрицательным числом сходится только пара с ЦЕЛОЕ: -4 раза по 5 плюс 3 даёт -17. Смешивание ЧАСТНОЕ с ОСТАТ на отрицательных числах это обычная причина ошибки на единицу в расписании или разбиении.