Функции ЦЕЛОЕ и ОТБР в Excel
Этот пример иллюстрирует работу двух функций для отбрасывания десятичной части числа в Excel: INT (ЦЕЛОЕ) и TRUNC (ОТБР).
ЦЕЛОЕ
Функция INT (ЦЕЛОЕ) округляет число до ближайшего целого числа.
Функция INT (ЦЕЛОЕ) отбрасывает десятичную часть только положительного числа. Отрицательное она округляет до меньшего целого.
Функция TRUNC (ОТБР) всегда отбрасывает знаки справа от запятой.
Функция TRUNC (ОТБР) может оставить требуемое количество знаков после запятой, отбросив остальные.
Основы Excel: 4 способа округлить числа. Детальные инструкции со скриншотами
Методы, о которых должен знать каждый, кто работает с Excel. Разбираем, когда можно округлить число одной кнопкой, а когда лучше запустить функцию.
Иллюстрация: Meery Mary для Skillbox Media
Округление числа — это замена его точного значения на приближённое. Число сокращают до нужного разряда — до десятков, сотых, тысячных, — а остальное отбрасывают. Чаще всего округление используют, когда точность не важна, а в округлённом виде число воспринимать проще.
В Excel числа округляют двумя основными способами: вносят изменения в формат ячейки или используют функции округления. В статье разберём особенности каждого способа и расскажем, когда их лучше применять. Расскажем, какие функции округления есть в Excel, чем они различаются и как ими пользоваться.
-
— когда нужно округлить число только визуально — когда нужно округлить число по правилам математики — когда нужно самостоятельно выбрать, в какую сторону округлить число — когда нужно округлить число с заданной точностью
Округление через формат ячейки
Когда использовать. Подойдёт в случаях, когда нужно изменить только формат отображения числа. Например, при подготовке презентации или перед распечаткой документа. Если после округления ссылаться на эту ячейку в формулах, Excel будет использовать исходное значение и округлит число 44,7 до 45 только на экране, а при дальнейших действиях, например, 45 + 5 рассчитает как 49,7, а не как 50.
Число будет округлено по правилам математики: если оно заканчивается на цифру от 1 до 4, то округляется в меньшую сторону, если на цифру от 5 до 9 — то в большую.
Как сделать. Для примера округлим два числа. Выделим их в общий диапазон и на вкладке «Главная» нажмём на стрелку справа от окна форматов.
В появившемся окне выбираем пункт «Другие числовые форматы».
Появляется окно форматирования ячеек. По умолчанию в Excel стоит общий формат значений — числа в таком формате округлять нельзя.
Чтобы округлить значения, выбираем числовой формат, а в блоке «Число десятичных знаков» задаём 0 — для этого можно нажать на стрелку вниз справа от блока либо ввести значение с клавиатуры. Нажимаем «ОК».
В результате получаем целые значения. При этом число 17560,42 округляется в меньшую сторону, до 17560. Число 5149,8 — в большую, до 5150.
Такого же эффекта можно достичь более коротким путём. Снова выделим первоначальные значения общим диапазоном; на вкладке «Главная» нажмём кнопку «Уменьшить разрядность». Нажать на неё нужно столько раз, сколько цифр стоит после запятой, — в нашем случае дважды.
В результате формат ячеек самостоятельно меняется на числовой, а значения округляются по тому же принципу, что и в первом случае.
Функция ОКРУГЛ
Когда использовать. Подойдёт, когда округлённое значение нужно использовать в дальнейших расчётах. Как и при применении предыдущего способа, Excel округлит число по правилам математики: от 1 до 4 округляется в меньшую сторону, от 5 до 9 — в большую.
Как сделать. Когда вы используете этот метод, округлённые значения появятся в новых ячейках. Поэтому создадим для них отдельный столбец и выделим первую ячейку в нём.
Дальше откроем окно для построения функции. Есть два способа сделать это. Первый — перейти во вкладку «Формулы» и нажать на «Вставить функцию».
Второй способ — нажать на «fx» в строке ссылок на любой вкладке таблицы.
Справа появляется окно «Построитель формул». В нём через поисковик находим функцию ОКРУГЛ и нажимаем «Вставить функцию».
Появляется окно для заполнения аргументов функции:
- «Число» — ячейка, значение которой нужно округлить.
- «Число_разрядов» — порядковый номер разряда, до которого нужно округлить значение. Или количество знаков после запятой, которое нужно оставить после округления.
Заполняем аргументы функции. Нажимаем на окно аргумента «Число» и из таблицы выбираем значение для него. В нашем случае это ячейка B3.
Выбранное значение переносится в построитель формул и одновременно появляется в формуле строки ссылок: fx=ОКРУГЛ(B3).
Нажимаем на окно «Число_разрядов». В нашем случае нужно округлить число до целого, поэтому поставим здесь 0. Это значит, что после запятой не будет ни одного значения.
В итоговом виде функция получается такой: fx=ОКРУГЛ(B3;0). Нажимаем кнопку «Готово».
В выбранной ячейке получаем целое значение первого числа. Теперь протягиваем результат вниз на следующую ячейку — функция автоматически выполняется и для следующего числа.
Excel округлил число 17560,42 в меньшую сторону, а 5149,8 — в большую.
Чтобы округлить число до тысяч, в окне аргумента «Число_разрядов» нужно задать значение -3. В этом случае функция округлит три значения до запятой. В случае с нашим примером первое число округлится до 18000, второе — до 5000.
Чтобы убрать лишние нули после запятой, воспользуемся кнопками изменения разрядности из первого метода. Выделим одновременно оба значения, перейдём во вкладку «Главная» и нажмём кнопку «Уменьшить разрядность».
Функции ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ
Когда использовать. Когда пользователь хочет сам решить, в какую сторону округлить число — в большую или в меньшую.
Перечисленные выше методы округляли числа по правилам математики: от 1 до 4 — в меньшую сторону, от 5 до 9 — в большую. ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ работают независимо от этого правила.
Как сделать. Помещаем курсор в ячейку, где мы хотим увидеть результат. Дальше откроем окно для построения функции. Для этого воспользуемся одним из двух способов:
- Перейти во вкладку «Формулы» и нажать на «Вставить функцию».
- Нажать на «fx» в строке ссылок в любой вкладке таблицы.
Появляется построитель формул. Аргументы в нём — «Число» и «Число_разрядов» — работают по тому же принципу, что и для функции ОКРУГЛ. Заполним их:
- «Число» — ячейка B3.
- «Число_разрядов» — 0.
В строке ссылок функция примет такой вид: fx=ОКРУГЛВВЕРХ(B3;0). Нажмём кнопку «Готово».
Получаем целое значение первого числа. Функция округлила его в большую сторону, до 17561, хотя по правилам математики округление должно было сработать в меньшую сторону. Протягиваем результат вниз на второе число — функция округляет и его.
Функция ОКРУГЛВНИЗ работает по обратному принципу: округляет в меньшую сторону все числа независимо от их значений.
Вызовем окно построителя формул, найдём функцию ОКРУГЛВНИЗ и заполним аргументы функции, как делали выше.
В строке ссылок функция примет вид: fx=ОКРУГЛВНИЗ(B3;0). Нажмём кнопку «Готово».
Получаем целое значение первого числа и протягиваем его вниз на второе — функция округлила его в меньшую сторону, до 5149, хотя по правилам округление должно было сработать вверх.
Функция ОКРУГЛТ
Когда использовать. Когда пользователь хочет сам решить, с какой точностью округлить число. При этом Excel округлит его не до ближнего значения, а до значения, которое будет кратно выбранному.
Например, если при округлении числа 49,5 пользователь выберет точность 2, функция округлит число до 50. Если точность будет 7, число округлится до 49.
Как сделать. Выберем пустую ячейку и откроем окно для построения функции так же, как и при использовании предыдущих методов. Найдём функцию ОКРУГЛТ и нажмём «Вставить функцию».
Появляется окно для заполнения аргументов функции:
- «Число» — ячейка, значение которой нужно округлить.
- «Точность» — точность, с которой нужно округлить число. Или значение, которому должно быть кратно первоначальное число после округления.
Заполняем аргументы функции. В окне аргумента «Число» выбираем значение, которое нужно округлить. Как и во всех предыдущих методах, это ячейка B3.
В окне «Точность» зададим значение 5. Это значит, что выбранное число после округления должно будет делиться на 5 без остатка.
В итоговом виде функция принимает вид: fx=ОКРУГЛТ(B3;5). Нажимаем кнопку «Готово».
Получаем целое значение первого числа и протягиваем его вниз до второго. Функция округлила числа до 17560 и 5150 — они оба полностью делятся на 5.
Принцип округления в математике эта функция не учитывает, так же как и функции ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ. Это значит, что функция не следует правилу, по которому цифры от 1 до 4 округляются в меньшую сторону, а от 5 до 9 — в большую.
Какие методы округления есть в «Google Таблицах»? В них можно использовать все методы, описанные выше. При этом все они работают по аналогии с Excel. Одно отличие — в «Google Таблицах» нет построителя формул, поэтому нужно прописывать их вручную. Готовые формулы будут выглядеть так:
- fx=ОКРУГЛ(17560,42;0);
- fx=ОКРУГЛВВЕРХ(17560,42;0);
- fx=ОКРУГЛВНИЗ(17560,42;0);
- fx=ОКРУГЛТ(17560,42;7).
Другие материалы Skillbox Media по Excel
- Инструкция: как в Excel объединить ячейки и данные в них
- Руководство: как сделать ВПР в Excel и перенести данные из одной таблицы в другую
- Инструкция: как закреплять строки и столбцы в Excel
- Руководство по созданию выпадающих списков в Excel — как упростить заполнение таблицы повторяющимися данными
- Статья с разбором диаграммы Ганта — что должен знать каждый управленец
Кратное число — число, которое делится на заданное целое значение без остатка. Например, число 10 кратно 5, но не кратно 3.
Вычислить целую часть числа — Excel и Google Таблицы
Чтобы вычислить целую часть числа, используйте функцию TRUNC для «усечения» десятичной дроби.
Функция TRUNC просто отсекает десятичную дробь, а не округляет.
Функции INT и ROUND
Вам может быть интересно узнать о функциях INT или ROUND.
Функция INT будет работать точно так же, как TRUNC для положительных чисел, но для отрицательных чисел INT округляет числа от нуля в меньшую сторону.
Функция ROUND округляет число, которое может дать разные ответы, чем другие функции.
Вычислить целую часть числа в Google Таблицах
Все приведенные выше примеры работают в Google Таблицах точно так же, как и в Excel.
Округление чисел в Microsoft Excel
При выполнении деления или работе с дробными числами, Excel производит округление. Это связано, прежде всего, с тем, что абсолютно точные дробные числа редко когда бывают нужны, но оперировать громоздким выражением с несколькими знаками после запятой не очень удобно. Кроме того, существуют числа, которые в принципе точно не округляются. В то же время, недостаточно точное округление может привести к грубым ошибкам в ситуациях, где требуется именно точность. К счастью, в программе имеется возможность пользователям самостоятельно устанавливать, как будут округляться числа.
Особенности округления чисел Excel
Все числа, с которыми работает Microsoft Excel, делятся на точные и приближенные. В памяти хранятся числа до 15 разряда, а отображаются до того разряда, который укажет сам пользователь. Все расчеты выполняются согласно хранимых в памяти, а не отображаемых на мониторе данных.
С помощью операции округления Эксель отбрасывает некоторое количество знаков после запятой. В нем применяется общепринятый способ округления, когда число меньше 5 округляется в меньшую сторону, а больше или равно 5 – в большую сторону.
Округление с помощью кнопок на ленте
Самый простой способ изменить округление — это выделить ячейку или группу ячеек и, находясь на вкладке «Главная», нажать на ленте на кнопку «Увеличить разрядность» или «Уменьшить разрядность». Обе кнопки располагаются в блоке инструментов «Число». Будет округляться только отображаемое число, но для вычислений при необходимости будут задействованы до 15 разрядов чисел.
При нажатии на кнопку «Увеличить разрядность» количество внесенных знаков после запятой увеличивается на один.
Кнопка «Уменьшить разрядность», соответственно, уменьшает на одну количество цифр после запятой.
Округление через формат ячеек
Есть возможность также выставить округление с помощью настроек формата ячеек. Для этого нужно выделить диапазон ячеек на листе, кликнуть правой кнопкой мыши и в появившемся меню выбрать пункт «Формат ячеек».
В открывшемся окне настроек формата ячеек следует перейти на вкладку «Число». Если формат данных указан не числовой, необходимо выставить именно его, иначе вы не сможете регулировать округление. В центральной части окна около надписи «Число десятичных знаков» просто укажите цифрой то количество знаков, которое желаете видеть при округлении. После этого примените изменения.
Установка точности расчетов
Если в предыдущих случаях устанавливаемые параметры влияли только на внешнее отображения данных, а при расчетах использовались более точные показатели (до 15 знака), то сейчас мы расскажем, как изменить саму точность расчетов.
- Перейдите на вкладку «Файл», оттуда — в раздел «Параметры».
- Откроется окно параметров Excel. В этом окне зайдите в подраздел «Дополнительно». Отыщите блок настроек под названием «При пересчете этой книги». Настройки в этом блоке применяются не к одному листу, а к книге в целом, то есть ко всему файлу. Поставьте галочку напротив параметра «Задать точность как на экране» и нажмите «OK».
- Теперь при расчете данных будет учитываться отображаемая величина числа на экране, а не та, которая хранится в памяти Excel. Настройку же отображаемого числа можно провести любым из двух способов, о которых мы говорили выше.
Применение функций
Если же вы хотите изменить величину округления при расчете относительно одной или нескольких ячеек, но не хотите понижать точность расчетов в целом для документа, в этом случае лучше всего воспользоваться возможностями, которые предоставляет функция «ОКРУГЛ» и различные ее вариации, а также некоторые другие функции.
Среди основных функций, которые регулируют округление, следует выделить такие:
| Функция | Описание |
|---|---|
| ОКРУГЛ | Округляет до указанного числа десятичных знаков согласно общепринятым правилам округления |
| ОКРУГЛВВЕРХ | Округляет до ближайшего числа вверх по модулю |
| ОКРУГЛВНИЗ | Округляет до ближайшего числа вниз по модулю |
| ОКРУГЛТ | Округляет число с заданной точностью |
| ОКРВВЕРХ | Округляет число с заданной точностью вверх по модулю |
| ОКРВНИЗ | Округляет число вниз по модулю с заданной точностью |
| ОТБР | Округляет данные до целого числа |
| ЧЕТН | Округляет данные до ближайшего четного числа |
| НЕЧЕТН | Округляет данные до ближайшего нечетного числа |
Для функций «ОКРУГЛ», «ОКРУГЛВВЕРХ» и «ОКРУГЛВНИЗ» используется следующий формат ввода: Наименование функции (число;число_разрядов) . То есть если вы, к примеру, хотите округлить число 2,56896 до трех разрядов, то применяете функцию «ОКРУГЛ(2,56896;3)». В итоге получается число 2,569.
Для функций «ОКРУГЛТ», «ОКРВВЕРХ» и «ОКРВНИЗ» применяется такая формула округления: Наименование функции(число;точность) . Так, чтобы округлить цифру 11 до ближайшего числа, кратного 2, вводим функцию «ОКРУГЛТ(11;2)». На выходе получается результат 12.
Функции «ОТБР», «ЧЕТН» и «НЕЧЕТ» используют следующий формат: Наименование функции(число) . Для того чтобы округлить цифру 17 до ближайшего четного, применяем функцию «ЧЕТН(17)». Получаем результат 18.
Функцию можно вводить, как в ячейку, так и в строку функций, предварительно выделив ту ячейку, в которой она будет находиться. Перед каждой функцией следует ставить знак «=».
Существует и несколько другой способ введения функций округления. Его особенно удобно использовать, когда есть таблица со значениями, которые нужно преобразовать в округленные числа в отдельном столбике.
- Переходим во вкладку «Формулы» и кликаем по кнопке «Математические». В открывшемся списке выбираем подходящую функцию, например, «ОКРУГЛ».
- После этого открывается окно аргументов функции. В поле «Число» можно ввести число вручную, но если мы хотим автоматически округлить данные всей таблицы, тогда кликаем по кнопке справа от окна введения данных.
- Окно аргументов функции сворачивается. Теперь щелкнуте по самой верхней ячейке столбца, данные которого мы собираемся округлить. После того, как значение занесено в окно, жмем по кнопке справа от этого значения.
- Опять открывается окно аргументов функции. В поле «Число разрядов» записываем разрядность, до которой нам нужно сокращать дроби и применяем изменения.
- Число округлилось. Чтобы таким же образом округлить и все другие данные нужного столбца, наводим курсор на нижний правый угол ячейки с округленным значением, жмем на левую кнопку мыши, и протягиваем ее вниз до конца таблицы.
- Теперь все значения в столбце будут округлены.
Как видим, существуют два основных способа округлить видимое отображение числа: с помощью кнопки на ленте и путем изменения параметров формата ячеек. Кроме того, можно изменить и округление реально рассчитываемых данных. Это также можно сделать по-разному: изменением настроек книги в целом или применением специальных функций. Выбор конкретного метода зависит от того, собираетесь ли вы применять подобный вид округления для всех данных в файле или только для определенного диапазона ячеек.