Как целые числа в Excel
Числовой формат является наиболее общим способом представления чисел и в связи с этим наиболее распространенным. В числовом формате можно применять разделитель групп разрядов, выбирать способ отображения отрицательных чисел и задавать количество знаков после запятой.
Виды чисел
На практике приходится работать с числами двух видов, с точными и приближенными.
Точные
Хранение данных и пересчет формул, которые поддерживаются приложением Excel составляют 15 разрядов. Числовые значения хранятся с точностью 15 разрядов, а отображаться на экране могут по-разному, в зависимости от выбранного формата. Для расчетов используются хранимые, а не отображаемые значения. При работе с различными числовыми значениями может понадобиться разная степень точности в вычислениях.
Приближенные
Существует множество задач, решение которых не требует большого количества знаков после запятой в числовых значениях. Например, при работе с денежными единицами достаточно двух знаков после запятой, а при работе со среднесписочной численностью людей знаки после запятой вообще не нужны. Таким образом, при расчетах в Excel возникает необходимость в округлении определенных результатов вычислений.
Что такое округление?
Округление — это математическая операция, которая позволяет уменьшить количество знаков после запятой в числе, заменив это число его приближенным значением с определенной точностью.
Существуют различные способы округления, такие как округление к большему, округление к меньшему, округление к большему по модулю, округление к меньшему по модулю, случайное округление, чередующееся округление, ненулевое округление, банковское округление.
Наиболее распространенный способ — математическое округление, когда число округляется в меньшую сторону, если в числе «отбрасываемая» цифра меньше пяти и округляется в большую сторону, если в числе «отбрасываемая» цифра больше либо равна пяти.
Остановимся на способах округления и отображения числовых значений, используемых в Excel.
Изменение количества отображаемых знаков после запятой без изменения числового значения
Выполнение такого рода действия осуществляется при помощи настроек числового формата, путем уменьшения количества знаков после запятой, при этом само числовое значение не округляется, округляется только его отображение на экране монитора. Внести изменения в настройки числового формата, можно при помощи кнопок «Увеличить разрядность» и «Уменьшить разрядность» на ленте Excel 2007 во вкладке «Главная», в группе «Число».
В других версиях Excel эти кнопки выглядят аналогично. Изменения в настройки числового формата, а именно увеличение либо уменьшение количества отображаемых знаков после запятой, можно внести в настройках формата ячеек. Не буду повторять о том, как вызвать окно «Формат ячеек» разными способами, так как ранее этот вопрос уже рассматривался. Использование вышеописанного метода округления может приводить к некоторым нежелательным последствиям, например вот к такому:
Как видно из примера, некорректная настройка числового формата привела к тому, что 2+3=6, что неверно.
Задать точность как на экране
Следует отметить еще одну возможность Excel, заложенную в параметрах Excel, в разделе «Дополнительно», в группе «При пересчете этой книги» — это пункт «Задать точность как на экране».
*Применение опции «Задать точность как на экране» ведет к изменению числовых значений и точности вычислений.
Округление числовых значений с помощью функций
Для выполнения округления числовых значений можно использовать одну из нескольких функций округления, при этом округляется непосредственно само числовое значение, а не его экранное отображение.
Функция ОКРУГЛ(число;число_разрядов) округляет число до указанного количества десятичных разрядов по классическим правилам математического округления;
Функция ОКРУГЛВВЕРХ(число;число_разрядов) округляет число с избытком до ближайшего большего по модулю;
Функция ОКРУГЛВНИЗ(число;число_разрядов) округляет число с недостатком до ближайшего меньшего по модулю;
Функция ОКРУГЛТ(число;точность) округляет число до кратного заданному числу, в формуле ОКРУГЛТ параметр «точность» — это кратное, до которого нужно округлить результат.
Функция ОКРВВЕРХ(число;точность) округляет с избытком число до ближайшего целого либо до ближайшего кратного указанному значению
Функция ОКРВНИЗ(число;точность) округляет с недостатком число до ближайшего целого либо до ближайшего кратного указанному значению.
*В некоторых версиях Excel функция ОКРУГЛТ отсутствует, для ее появления необходимо установить стандартную надстройку Excel — «Пакет анализа». Если функция ОКРУГЛТ есть в математических функциях, но возвращает ошибку #ИМЯ?, попробуйте установить стандартную надстройку Excel — «Пакет анализа».
Кроме вышеперечисленных функций, в стандартных средствах Excel есть еще такие функции, связанные с округлением, как
Функция ЧЕТН(число) округляет число до ближайшего четного целого. При этом положительные числа округляются в сторону увеличения, а отрицательные — в сторону уменьшения;
Функция НЕЧЁТ(число) округляет число до ближайшего нечетного целого. Положительные числа округляются в сторону увеличения, а отрицательные — в сторону уменьшения;
Функция ОТБР(число) округляет число до целого, отбрасывая дробную часть.
Формат ячейки может предопределять отображаемый результат функции, например, если число округлено функцией ОКРУГЛ до трех десятичных разрядов, а в формате ячейки указан числовой формат с двумя знаками после запятой, то при отображении результата на экране определяющее действие будет оказывать формат ячейки.
Для тех, кто использует стандартные функции Excel при написании макросов в VBA, может быть полезен сайт http://ru.excelfunctions.eu/, где есть сопоставление между русскими и английскими именами функций с их русским описанием.
Примеры использования функций округления
=ОКРУГЛ(1,475;2) Округляет число 1,475 до двух десятичных разрядов, в результате получается 1,48;
=ОКРУГЛВВЕРХ(3,14159;3) Округляет с избытком число 3,14159 до трех десятичных разрядов, результат 3,142;
=ОКРУГЛВНИЗ(3,14159) Округляет с недостатком число 3,14159 до трех десятичных разрядов, результат 3,141;
=ОКРУГЛТ(10;3) Округляет число 10 до ближайшего числа, кратного 3, то есть до 9;
=ОКРВВЕРХ(2,5;1) Округляет число 2,5 до ближайшего большего числа, кратного 1, то есть до 3;
=ОКРВНИЗ(2,5;1) Округляет число 2,5 до ближайшего меньшего числа, кратного 1, то есть до 2.
Для того, чтобы избежать возможных неточностей и ошибок, следует тщательно подбирать форматы и способы округления при формировании таблиц в Excel. Если же таблицы уже сформированы не совсем так, как вам бы этого хотелось, то табличные данные можно программно обработать средствами VBA, прибегнув к помощи макросов.
Быстрое округление числовых значений в Excel без использования формул
Надстройка, в которой объединены все основные способы округления, поможет заменить числа в ячейках их округленными значениями, минуя этап ввода формул. Пользователь может на свое усмотрение выбрать диапазон и способ округления, все остальное за него сделает программа.
Возможности надстройки позволяют:
1. Одним кликом мыши вызывать диалоговое окно прямо из панели инструментов Excel;
2. быстро заменять числовые значения ячеек округленными значениями, в заданном диапазоне не прибегая к вводу формул и функций в ячейки;
3. быстро выбирать на одной из трех вкладок диалогового окна нужный способ округления и задавать его точность (доступно 9 способов);
4. легко изменять область для поиска и замены чисел их округленными значениями.
видео по работе с надстройкой
надстройка (макрос) для замены чисел на их округленные значения
Ниже приведен результат округления диапазона ячеек с числовыми значениями до целых чисел с использованием опции «ОКРУГЛ» и количеством знаков после запятой равным нулю.
