Функции, связанные с округлением

Функция ОКРУГЛ

Осуществляет стандартное округление, а именно округляет число до ближайшего разряда с указанной точностью.

Синтаксис: = ОКРУГЛ (число; число_разрядов), где

• Число – обязательный аргумент. Число либо ссылка на ячейку, его содержащую;
• Число_разрядов – обязательный аргумент. Указывает, какое количество знаков после запятой необходимо оставить:
• 0 – округление до целого числа;
• 1 – округление до десятых долей;
• 2 – округление до сотых долей;
• И т.д.

Аргумент может также принимать отрицательные числа:

• -1 – округление до десятков;
• -2 – округление до сотен;
• И т.д.

Пример использования:

= ОКРУГЛ (5, 45; 0) – формула возвращает значение 5.
= ОКРУГЛ (5, 45; 1) – формула возвращает значение 5, 5.
= ОКРУГЛ (5, 45; 3) – не меняет число, т.к. указанное число разрядов превышает его точность.
= ОКРУГЛ (5, 45; -1) – формула возвращает значение 10.

Функция ОТБР

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

Синтаксис: = ОТБР (число; [число_разрядов]), где

• Число – обязательный аргумент. Число либо ссылка на ячейку с числом;
• Число_разрядов – необязательный аргумент. Указывает, какое количество знаков после запятой необходимо оставить:
• 0 – точность до целого числа;
• 1 – точность до десятых долей;
• 2 – точность до сотых долей;
• И т.д.

Пример использования:

= ОТБР (5, 45; 0) – формула возвращает значение 5.
= ОТБР (5, 85; 0) – так же возвращает значение 5.
= ОТБР (5, 45; 1) – возвращает значение 5, 4.
= ОТБР (5, 45; 3) – формула не меняет число, т.к. указанное количество разрядов превышает его точность.

Функция ОКРУГЛВВЕРХ

Производит округление до ближайшего большего по модулю числа с указанной точностью.

Синтаксис: = ОКРУГЛВВЕРХ (число; число_разрядов), где

• Число – обязательный аргумент. Число либо ссылка на ячейку, содержащую число;
• Число_разрядов – обязательный аргумент. Указывает, какое количество знаков после запятой необходимо оставить:
• 0 – округление до целого числа;
• 1 – округление до десятых долей;
• 2 – округление до сотых долей;
• И т.д.

Аргумент может также принимать отрицательные числа:

• -1 – округление до десятков;
• -2 – округление до сотен;
• И т.д.

Пример использования:

= ОКРУГЛВВЕРХ (5, 001; 0) – формула возвращает значение 6.
= ОКРУГЛВВЕРХ (-5, 001; 0) – формула возвращает значение -6, т.к. -6 по модулю больше, чем -5, 001 по модулю.
= ОКРУГЛВВЕРХ (5, 45; 1) – возвращает значение 5, 5.
= ОКРУГЛВВЕРХ (5, 45; 3) – функция не изменяет число, т.к. требуемая разрядность превышает его точность.
= ОКРУГЛВВЕРХ (5, 45; -1) – формула возвращает значение 10.

Функция ОКРУГЛВНИЗ

Аналогична предыдущей функции, за исключением того, то округляет число в меньшую сторону по модулю с указанной точностью.

Пример использования:

= ОКРУГЛВНИЗ (5, 99; 0) – формула возвращает значение 5.
= ОКРУГЛВНИЗ (-5, 99; 0) – формула возвращает значение -5, т.к. -5 по модулю меньше, чем -5, 99 по модулю.
= ОКРУГЛВНИЗ (5, 45; 1) – функция возвращает значение 5, 4.
= ОКРУГЛВНИЗ (5, 45; 3) – не меняет число, т.к. указанная разрядность превышает его точность.
= ОКРУГЛВНИЗ (5, 45; -1) – формула возвращает значение 0.

Функция ОКРУГЛТ

Округляет число до ближайшего кратного числу, заданного вторым аргументом.

Синтаксис: = ОКРУГЛТ (число; точность), где

• Число – обязательный аргумент. Число либо ссылка на ячейку, содержащую число;
• Точность – обязательный аргумент. Число, для которого необходимо найти кратное ближайшее к первому аргументу. В случае задания нулевого значения, функция всегда будет возвращать 0.

Знаки двух аргументов должны совпадать, иначе функция вернем ошибку.

Пример использования:

= ОКРУГЛТ (5, 45; 0) – формула возвращает значение 0.
= ОКРУГЛТ (5, 45; 1, 45) – возвращает значение 5, 8, т.к. 5, 8/1, 45=4 и это ближе, чем 7, 25/1, 45=5.
= ОКРУГЛТ (5, 45; 3) – формула возвращает значение 6, т.к. 6/3=2, ближе, чем 3/3=1.

Функция ОКРВВЕРХ.МАТ

Появилась в Microsoft Excel 2013. Она округляет число до ближайшего большего кратного числу, заданного вторым аргументом.

Синтаксис: = ОКРВВЕРХ.МАТ (число; [точность]; [режим]), где

• Число – обязательный аргумент. Число либо ссылка на ячейку, содержащую числовое значение;
• Точность – необязательный аргумент. Число, для которого необходимо найти большее кратное, наиболее приближенное к заданному числу. В случае задания данному аргументу нулевого значения, функция всегда будет возвращать 0.
• Режим – необязательный аргумент. Принимает число. Если режим не задан либо равно нулю, то округление будет производиться до большего кратного не по модулю. Если же аргумент отличается от 0, то при округлении отрицательных чисел, большим будет считаться кратное наиболее отдаленное от нуля, т.е. по модулю.

Пример использования:

= ОКРВВЕРХ.МАТ (5, 45; 0) – формула возвращает значение 0.
= ОКРВВЕРХ.МАТ (5, 45; 4) – формула возвращает значение 8, несмотря на то, что кратное 4 ближе к 5, 45.
= ОКРВВЕРХ.МАТ (-5, 45; 4) – формула возвращает значение -4, т.к. режим не задан, то округление производиться не по модулю.
= ОКРВВЕРХ.МАТ (-5, 45; 4; 1) – формула возвращает значение -8, т.к. аргумент режим отличается от нуля, то округление производиться по модулю.

Функция ОКРВНИЗ.МАТ

Появилась в Microsoft Excel 2013. Она округляет число до ближайшего меньшего кратного числу, заданного вторым аргументом.

Синтаксис: = ОКРВНИЗ.МАТ (число; [точность]; [режим]), где

• Число – обязательный аргумент. Число либо ссылка на ячейку, содержащую число;
• Точность – необязательный аргумент. Число, для которого необходимо найти меньшее кратное, наиболее приближенное к первому аргументу. В случае задания нулевого значения, функция всегда будет возвращать 0.
• Режим – необязательный аргумент. Принимает число. Если данное число отсутствует либо равно нулю, то округление будет производиться до меньшего кратного не по модулю. Если же аргумент отличается от 0, то при округлении отрицательных чисел, меньшим будет считаться кратное наиболее приближенное к нулю, т.е. по модулю.

Обращаем внимание на то, что третьи аргументы функций ОКРВВЕРХ.МАТ и ОКРВНИЗ.МАТ, не смотря на то, что очень похожи, все же отличаются, т.к. имеют противоположный эффект. Для избавления от путаницы можно прибегать к следующей ассоциации:

• Если режим для функции ОКРВВЕРХ.МАТ равен 0, то направление округления к нулю, т.к. аргумент действует только на отрицательные числа;
• Если режим для функции ОКРВНИЗ.МАТ равен 0, то направление округления от нуля.

Пример использования:

= ОКРВНИЗ.МАТ (5, 45; 0) – формула возвращает значение 0.
= ОКРВНИЗ.МАТ (5, 45; 3) – формула возвращает значение 3, несмотря на то, что кратное 6 ближе к 5, 45.
= ОКРВНИЗ.МАТ (-5, 45; 3) – возвращает значение -6, т.к. режим не задан, то округление производиться не по модулю.
= ОКРВНИЗ.МАТ (-5, 45; 4; 1) – функция возвращает значение -4, т.к. аргумент режим принимает не равен 0, то округление производиться по модулю.

Функция ЦЕЛОЕ

Округляет число до целого в меньшую сторону.

Синтаксис: = ЦЕЛОЕ (число), где число – обязательный аргумент, принимающий числовое значение либо ссылку на ячейку с числовым значением.

Пример использования:

= ЦЕЛОЕ (5, 85) – формула вернет значение 5.
= ЦЕЛОЕ (-5, 85) – вернет значение -6.

Функция ЧЁТН

Округляет число до ближайшего большего по модулю четного числа.

Синтаксис: = ЧЁТН (число), где число – обязательный аргумент. Принимает числовое значение либо ссылку на ячейку, содержащую число.

Пример использования:

= ЧЁТН (6, 85) – вернет значение 8.
= ЧЁТН (-6, 85) – вернет значение -8.

Функция НЕЧЁТ

Аналогична функции ЧЁТН за исключением того, что числа округляются до нечетных.

Пример использования:

= НЕЧЁТ (5, 85) – вернет значение 7.
= НЕЧЁТ (-5, 85) – вернет значение -7.

Суммирование и условное суммирование

Функция СУММ

Суммирует свои аргументы. Максимальное число аргументов 255.

Если функция ссылается на ячейку, диапазон ячеек или массив, содержащий текстовые либо логические значения, то такие значения игнорируются. Если какой-либо аргумент принимает константу (значение введенное руками), содержащую текстовое значение, то такой аргумент возвращает ошибку, в результате чего вся формула вернет ошибку.

Если же в качестве аргумента функции принимается константа с логическим значением, то ЛОЖЬ приравнивается к нулю, а ИСТИНА к единице.

Синтаксис: = СУММ (число1; [число2]; …), где

• Число1 – обязательный аргумент, являющийся числом либо ссылкой на ячейку или диапазон ячеек, содержащих число;
• Число2 и последующие аргументы – необязательные аргументы, аналогичные первому.

Пример использования:

• В данном примере значение ячейки A5 игнорируется.

• = СУММ (1; 2; 3; 4; " текст" ) – данный вариант вернет ошибку #ЗНАЧ!, т.к. последний аргумент явно принимает текстовое значение.
• = СУММ (ИСТИНА; ЛОЖЬ) – формула вернет значение 1.

Функция СУММПРОИЗВ

Производит суммирование произведений массивов либо диапазонов.

Если аргументы принимают диапазоны либо массивы, содержащие текстовые или логические значения, то такие значения игнорируются.

Если в аргументе явно задать логическое либо текстовое значение или ссылку на одну ячейку, содержащую такое значение, то вся формула вернет ошибку.

Синтаксис: = СУММПРОИЗВ (массив1; [массив2]; …), где

• Массив1 – обязательный аргумент, являющийся числом либо ссылкой на ячейку, диапазон ячеек или массив, содержащих числовое значение;
• Массив2 и последующие аргументы – необязательные аргументы, аналогичные первому.

Все аргументы функции должны иметь одинаковую размерность, т.е. если один аргумент ссылается на диапазон с 5 ячейками, то и остальные аргументы должны иметь по 5 элементов. Также должны использоваться диапазоны и массивы одних типов, т.е. горизонтальные и вертикальные массивы и диапазоны либо двумерные и одномерные массивы не могут использоваться одновременно в данной функции, иначе она возвратит ошибку. Чтобы лучше понять этот абзац, ознакомьтесь со статьей «Массивы Excel».

Пример использования:

• В данном примере один диапазон содержит текст, но функция игнорирует данное значение и возвращает сумму произведений остальных элементов.

• В данном случае формула возвращает ошибку, потому что, не смотря на одинаковое количество элементов в двух диапазонах, они имеют разные типы, т.е. A1: A5 – вертикальный диапазон, а B1: F1 – горизонтальный диапазон.

Функция СУММЕСЛИ

Возможно, одна из самых полезных функций, по мнению office-menu. Она производит суммирование элементов, которые соответствуют заданным условиям.

Синтаксис: = СУММЕСЛИ (диапазон_условия; критерий; [диапазон_суммирования]), где

• диапазон_условия – обязательный атрибут. Ссылка на ячейку или диапазон ячеек, которые необходимо проверить на совпадение с условием;
• критерий – обязательный атрибут. Содержит в себе конкретное значение либо условие для проверки. Условия типа больше, меньше, равно либо их комбинации всегда заключаются в кавычки.
• диапазон_суммирования – необязательный атрибут. Ссылка на ячейку либо диапазон ячеек, которые необходимо просуммировать в случае, если элемент диапазона условия подходит под критерий. Если аргумент не указан, то по умолчанию он принимает значение первого аргумента. Также, если диапазон указан не правильно, т.е. для вертикального диапазона условия, указан горизонтальный диапазон суммирования, то последний заменяется на вертикальный, не меняя своего первого элемента, т.е. претерпевает транспонирование.

Пример использования:

• В данном примере производится суммирование чисел, которые больше 2. Так как диапазон суммирования не указан, то по умолчанию принимает диапазон условия.

• В следующем примере используются разные типы диапазонов, поэтому 3 аргумент меняет ссылку с A1: B1 на A1: A2, и функция возвращает значение 2.

• При совместном использовании текстовых и числовых значений в диапазоне условия, проверяться будут либо те, либо другие. Рассмотрите последние два примера.

В первом случае, необходимо произвести суммирование по B1: B5, если элемент из A1: A5 больше нуля. Возвращаемое значение 4, так как текстовый элемент A3 игнорируется.

Теперь изменим условие и найдем сумму, если элементы для условия больше или равняются «а». По условиям сортировки все числа являются меньшими любым буквам, поэтому результат должен быть 5. Но так как в условии задано сравнение с текстовой строкой, то все числовые значения отбрасываются. Чтобы они учитывались, их необходимо перевести в текстовый формат. Также можно использовать массивы, для лучшего контроля перевода чисел в текст – {=СУММ(ЕСЛИ(ТЕКСТ(A1: A5; 0)< =" а"; B1: B5; 0))}.

Функция СУММЕСЛИМН

Выполняет те же действия, что и СУММЕСЛИ, но может проверять различные условия по нескольким диапазонам.

Синтаксис: = СУММЕСЛИМН (диапазон_суммирования; диапазон_условия1; критерий1; [диапазон_условия2]; [критерий2]; …), где аргументы в точности совпадают с аргументами функции СУММЕСЛИ, за исключением того, что диапазон суммирования и первая пара диапазон условия - критерий являются обязательными аргументами. Все последующие пары (от диапазон_условия2; критерий2 до диапазон_условия127; критерий127) необязательны.

Также в данной функции не происходит никаких подмен диапазонов, поэтому все заданные в функции диапазоны должны быть равны по размерности и иметь один тип, т.е. только горизонтальные либо только вертикальные.

Пример использования:

Необходимо узнать сумму ячеек, удовлетворяющих условиям:

1. По A1: A5 больше 2;
2. По B1: B5 меньше или равно “г”.

Таким образом, по первому критерию подходят 3 ячейки, по второму 4, но ячеек, которые подходят под оба условия две – C3 и C4. Поэтому формула вернет значение 2.

⇐ Предыдущая6789101112131415Следующая ⇒

Поделиться: