Округление в Excel (есть формулы): полный разбор от Бородача

Всем доброго времени суток. Сегодня мы посмотрим, как можно округлить число в Excel. Мы поговорим про формулу округления до целого числа или определенного разряда. Посмотрим, как именно происходит превращение чисел, чем отличается обычное отображение числа от настоящего округления, почему иногда Excel показывает одно значение, а в расчетах использует другое. Надеюсь, статья для вас будет полезной.

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

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

Способ 1: С помощью кнопок

Прежде чем мы начнем волшебные числовые превращения, нужно понять – каким вообще образом хранятся неточные числа в компьютере. Это очень важная тема, которая почему-то нигде не всплывает. А важна она для тех людей, которые хотят получить точные результаты. Да, я помню, что мы хотим округлить какую-то цифру, но я вам настоятельно не рекомендую пропускать то, что я сейчас вам поведаю.

Давайте вспомним, как мы раньше делили в столбик в школе. Переживать не стоит, так вам ничего делать для этого не нужно. Помните: в математике были такие противные числа, которые нельзя было представить конечной десятичной дробью. Давайте попробуем разделить 7 на 3.

Да, обычный дробный ответ достаточно простой. Но компьютер не может бесконечно хранить все цифры подобного числа. А если попробовать выполнить деление в столбик, то мы упираемся в бесконечное число в периоде.

Обычно учительница математики на этом моменте говорила, что в таком случае можно не продолжать деление и записать подобный ответ. В более старших классах бесконечное число обрамляли в скобки, что и обозначало тот самый «период»:

2,3(3)

И тут сразу же встает вопрос – а как компьютер может работать с подобными бесконечными числами? На самом деле, если говорить строго, то никак. Компьютеры используют ограниченную точность, а Excel хранит числовые значения с ограничением по значащим цифрам. То есть программа не держит в памяти бесконечную цепочку троек, а работает с приближенным значением.

В Excel точность чисел ограничена значащими цифрами, а не просто количеством цифр после запятой. Это важная разница. Например, в числе 2,33333333333333 первая двойка тоже считается значащей цифрой, поэтому после запятой мы видим не бесконечность, а ограниченное количество троек. Для обычных домашних расчетов этого более чем хватает, но в инженерных, научных и финансовых таблицах эту особенность лучше держать в голове.

Попробуем выполнить деление этих же чисел в программе. В первую ячейку первой строки вписываем 7, а во вторую ячейку – 3. И попробуем посчитать через формулу. Напомню, что формула начинается со знака равно (=).

Теперь смотрим на результат. Я советую немного расширить саму ячейку, чтобы увидеть больше цифр, так как программа частенько обрезает невидимую часть. Как видите, по умолчанию Excel показывает не бесконечное число, а уже ограниченное значение. Это не ошибка, а нормальное поведение программы.

Давайте попробуем увеличить или наоборот уменьшить точность отображения, округлив число на экране еще сильнее. Для этого на вкладке «Главная» в разделе «Число» найдите две кнопки со стрелочками. Одна кнопка уменьшает количество знаков после запятой, а вторая увеличивает.

Важно: эти кнопки не всегда меняют само число внутри ячейки. Они в первую очередь меняют то, как число отображается. Например, внутри ячейки может храниться 2,33333333333333, а на экране вы увидите просто 2,33. Если потом использовать эту ячейку в другой формуле, Excel будет брать внутреннее значение, а не только то, что вы видите глазами. Поэтому кнопки удобны для красивого вида таблицы, но не всегда подходят для настоящего округления в расчетах.

Пример из жизни: у вас есть цена 99,995, а на экране вы сделали 100,00. Визуально все красиво, но в формуле может продолжать участвовать исходное значение. Если таких строк сотни, итоговая сумма может отличаться от той, которую вы ожидаете увидеть. Именно поэтому для бухгалтерских и финансовых расчетов лучше округлять формулой, а не только менять вид ячейки.

А вот теперь мы подошли к самой сути проблемы всех компьютеров на планете Земля. Попробуйте выделить ячейку с результатом и увеличить количество отображаемых знаков после запятой. В результате мы увидим ограниченное количество троек, а далее могут появиться нули.

Дело в том, что Excel не работает с бесконечной точностью. У программы есть ограничение по числовой точности, поэтому дальше идет не «настоящая бесконечность», а приближенное значение. Это нормально для электронных таблиц и большинства программ. В обычных задачах вроде расходов, цен, процентов, школьных таблиц и простых отчетов вы этого почти не заметите. Но если вы занимаетесь точными расчетами, моделированием, большими массивами данных или научными вычислениями, нужно понимать, что Excel – это удобный табличный инструмент, а не система бесконечной математической точности.

Если же до максимума попробовать округлить цифру до целого, то получим целое число – два (2). Вспоминаем, что округление обычно происходит по такому правилу:

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

Чтобы вам было понятнее, посмотрите на картинку ниже. Красным обозначены числа, которые мы хотим убрать и округлить до следующего разряда. Синие – это результат округления.

Про данное правило округления нужно знать обязательно, чтобы в голове держать точность числа, до которой можно опустить мелкие дробные данные. Но еще раз повторюсь: кнопки на вкладке «Главная» чаще всего нужны именно для внешнего вида. Если вам нужно, чтобы само число стало округленным и дальше именно оно участвовало в расчетах, переходите к способу с формулами ниже.

Если у вас в таблице проценты, скидки, надбавки или доли, округление часто идет рядом с процентным форматом. В таком случае советую отдельно посмотреть, как считается процент от числа в Excel. Там проще понять, почему 0,25 может отображаться как 25%, и как это связано с форматом ячеек.

На этом первый способ мы рассмотрели. Вы можете использовать эти кнопки не только для одной, но и для ряда выделенных ячеек.

Способ 2: Формат ячейки

Для второго способа давайте изменим цифры в нашей формуле и попробуем разделить семь (7) на девять (9). Смотрите, в отличие от прошлого примера, когда в разрядах было число три (3), здесь в дробной части идут семерки. Если мы оставим мало знаков после запятой, Excel покажет округленный вариант.

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

ПРИМЕЧАНИЕ! Аналогично можно выделить сразу несколько ячеек, чтобы задать им определенное правило.

На первой вкладке найдите строчку «Число десятичных знаков» и установите количество разрядов, то есть количество цифр после запятой. После этого жмем «ОК».

Этот способ очень похож на кнопки из первой главы, просто здесь больше ручных настроек. Вы можете выбрать числовой формат, денежный, финансовый, процентный, дробный и другие варианты. Например, для цен обычно ставят 2 знака после запятой, для процентов иногда оставляют 0, 1 или 2 знака, а для измерений можно оставить столько, сколько требуется в вашей задаче. Если хотите подробнее разобраться именно с внешним видом значений, почитайте отдельный урок про формат ячеек в Excel.

Формат ячейки не равен настоящему округлению. Он меняет отображение числа, но исходное значение может остаться более точным. Это удобно, когда нужно просто красиво показать данные. Но если вы хотите, чтобы дальнейшие формулы считали уже округленные значения, используйте функцию ОКРУГЛ или другие функции округления.

Способ 3: Настройка точности расчетов

Я должен вам признаться, дорогие мои читатели – я немного утаил одну очень важную вещь. Оба прошлых способа не совсем округляют числа. Они всего лишь «округляют» ту цифру, которая отображается в ячейке. Можно сказать, что они просто ее скрывают. На самом деле цифра, которая хранится в таблице и используется в расчетах, может иметь большую точность, чем вы видите на экране.

То есть проводя какие-то подсчеты, вы обычно не теряете точность из-за одного только формата. Но в Excel есть отдельная настройка, которая заставляет программу считать именно по тем значениям, которые отображаются на экране. Она называется «Задать указанную точность» или «Точность как на экране» в зависимости от перевода интерфейса.

Этот способ нужно использовать очень аккуратно. Настройка может навсегда изменить сохраненные значения в книге по отображаемому виду. Если у вас было 2,33333333333333, а показывалось 2,33, после применения такой точности исходная длинная дробь может быть потеряна. Вернуть старые скрытые цифры потом уже не получится, если у вас нет копии файла.

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

Но если вы точно понимаете последствия и вам действительно нужно считать по отображаемым значениям, делается это так:

1. В левом верхнем углу экрана переходим в «Файл».

2. Открываем «Параметры».

3. Слева выбираем основной раздел «Дополнительно». В правом блоке листаем до подраздела «При пересчете этой книги» и ставим галочку «Задать указанную точность». Жмем «ОК».

4. Всплывет окошко с предупреждением.

5. Теперь, если вы измените отображение цифр любым из предыдущих способов, в расчетах будет принимать участие именно отображаемое значение, а не прежнее скрытое число с большей точностью.

Перед применением этой настройки обязательно сохраните копию документа. Я бы делал так: «Файл» – «Сохранить как» – создаем отдельный файл с понятным названием вроде «Отчет_копия_до_точности». После этого можно экспериментировать. Если результат вас не устроит, вы сможете вернуться к исходной книге. Без копии можно испортить финансовый отчет или расчетную таблицу, особенно если в ней много формул, которые зависят друг от друга.

Способ 4: Функциональное округление и упрощение расчетов

Проблема прошлого способа в том, что настройка четко задает рамки для всего документа. Да, мы таким образом можем заставить Excel считать по отображаемым значениям. Но что если нам нужно задать четкое округление не для всего документа, а только для отдельных ячеек? В таком случае нам помогут специальные формулы округления.

Представим себе, что у нас есть сложное и длинное число. Нам нужно уменьшить разрядность для подсчета именно в этой ячейке. Для этого используем формулу:

=ОКРУГЛ(Число;Разряд округления)

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

Разряд округления показывает, сколько знаков оставить. Если указать 2, останется две цифры после запятой. Если указать 0, число округлится до целого. Если указать отрицательное значение, округление пойдет уже влево от запятой: до десятков, сотен, тысяч и так далее. Например, формула =ОКРУГЛ(A1;-1) округлит число до десятков, а =ОКРУГЛ(A1;-2) – до сотен.

Простой пример: в ячейке A1 стоит 1234,56. Формула =ОКРУГЛ(A1;2) оставит 1234,56, формула =ОКРУГЛ(A1;1) даст 1234,6, формула =ОКРУГЛ(A1;0) даст 1235, а формула =ОКРУГЛ(A1;-2) даст 1200. Так проще понять, что второй аргумент функции отвечает именно за разряд.

В данном случае превращение последнего разряда происходит по стандартным математическим правилам. Есть также две вариации этой формулы: «ОКРУГЛВВЕРХ» и «ОКРУГЛВНИЗ». Первая функция округляет число от нуля, а вторая – к нулю. На положительных числах это выглядит просто: вверх – число становится больше, вниз – меньше. На отрицательных числах логика может запутать, поэтому обязательно проверяйте результат на примерах.

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

ОКРУГЛТ

Работает она немного по-другому. Представим себе, что у нас есть целое число, которое мы хотим округлить так, чтобы оно делилось на 2, или 3, или быть может 5 или 7. Для этого после точки с запятой (;) просто указываем это кратное число. В примере ниже 15 превратилось в ближайшую цифру, кратную семи (7), а это 14.

Пример из обычной жизни. Магазин продает товар упаковками по 5 штук, а в расчете получилось 23 штуки. Если нужно округлить до ближайшего количества, кратного 5, можно получить 25 или 20 в зависимости от задачи. Если товар нельзя купить меньше нужного количества, лучше брать округление вверх. Если вы просто группируете данные для отчета, может подойти ближайшее кратное значение.

У этой же идеи есть похожие вариации:

• ОКРВВЕРХ – подбирает кратное число в большую сторону.
• ОКРВНИЗ – подбирает кратное число в меньшую сторону.

Есть еще несколько формул, которые могут вам помочь:

• ОТБР – отбрасывает дробную часть. Например, 7,9 превратит в 7.
• ЦЕЛОЕ – округляет число вниз до ближайшего целого. На отрицательных числах результат может отличаться от ОТБР.
• ЧЕТН – округляет до ближайшего четного целого от нуля.
• НЕЧЕТН – округляет до ближайшего нечетного целого от нуля.

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

Особенно аккуратно работайте с отрицательными числами. Например, ОТБР и ЦЕЛОЕ могут дать разные результаты для значения -2,7. ОТБР просто отбросит дробную часть и получится -2, а ЦЕЛОЕ округлит вниз и получится -3. Если у вас долги, убытки, отрицательные температуры или корректировки, обязательно проверьте формулу на нескольких примерах.

Если после округления у вас вдруг перестала считаться сумма или Excel воспринимает числа как текст, проблема может быть не в округлении. Такое часто бывает после копирования данных с сайтов, из PDF, 1С, банковских выгрузок или чужих файлов. В этом случае сначала проверьте формат ячеек и наличие лишних пробелов. На эту тему у нас есть отдельная инструкция, почему Excel не считает сумму выделенных ячеек.

Давайте разберем уже конкретный пример с таблицей и несколькими ячейками. Представим себе, что у нас есть данные о температуре в городе за конкретный период. Данные берутся со специальных точных приборов. Понятное дело, нам такая точность просто ни к чему. Поэтому создаем отдельную колонку с округленными данными по температуре.

1. Выделяем первую ячейку таблицы.
2. Мы можем, конечно, ввести формулу вручную, но давайте я покажу, где они находятся. В разделе «Формулы» находим «Математическую» книжечку и раскрываем ее. Ищем наш «ОКРУГЛ». На самом деле вы можете применить любую формулу, которая вам по душе – это не имеет значения.

3. Кликните левой кнопкой мыши по строке «Число» и выделите ту ячейку, с которой будет идти округление.

4. Ниже вписываем разрядность.

5. Формула применилась только к первой строке, теперь просто растягиваем квадратик вниз.

Ура, у нас получилось. Как видите, вам не нужно помнить все эти формулы наизусть, и вы всегда можете обратиться к ним через эту вкладку. А чтобы не вводить формулу вручную в каждой строке, используйте маркер заполнения. Если он у вас работает не так, как нужно, посмотрите отдельный урок про автозаполнение ячеек в Excel.

Что выбрать: формат или формулу?

Давайте коротко закрепим самое важное, потому что именно здесь чаще всего и возникает путаница. Если вам нужно просто красиво показать число в таблице, используйте кнопки на вкладке «Главная» или «Формат ячеек». Например, чтобы цена выглядела как 150,00, а не 150,000000. Это оформление, и оно хорошо подходит для печати, просмотра и обычной аккуратной таблицы.

Если вам нужно, чтобы в дальнейших расчетах участвовало уже округленное значение, используйте формулы. Например, у вас есть цена с длинной дробной частью, и от нее дальше считается итог, скидка, налог или процент. В таком случае лучше создать отдельный столбец с формулой ОКРУГЛ и уже его использовать дальше. Так вы будете точно понимать, какое число участвует в итогах.

Мое простое правило: для внешнего вида – формат ячеек, для расчетов – формула. Если сомневаетесь, кликните по ячейке и посмотрите строку формул. Если в строке формул число длиннее, чем в ячейке, значит на экране оно просто отображается короче. Если в строке формул стоит функция ОКРУГЛ, значит число действительно считается через округление.

Короткая шпаргалка по функциям округления

• ОКРУГЛ – обычное математическое округление до нужного количества знаков.
• ОКРУГЛВВЕРХ – округление от нуля.
• ОКРУГЛВНИЗ – округление к нулю.
• ОКРУГЛТ – округление до ближайшего кратного значения.
• ОКРВВЕРХ – округление вверх до кратного значения.
• ОКРВНИЗ – округление вниз до кратного значения.
• ОТБР – отбрасывание дробной части без обычного округления.
• ЦЕЛОЕ – округление вниз до целого.
• ЧЕТН – округление до четного целого.
• НЕЧЕТН – округление до нечетного целого.

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

FAQ – частые вопросы

Почему я округлил число кнопкой, а итог все равно другой?
Потому что кнопка часто меняет только отображение числа. Внутри ячейки может храниться более точное значение, и именно оно участвует в итоговой формуле. Чтобы итог считал по округленным числам, используйте функцию ОКРУГЛ или включайте точность как на экране, но второй вариант опасен потерей исходных данных. Для важных таблиц лучше использовать формулу.

 

Как округлить число до целого?
Используйте формулу =ОКРУГЛ(A1;0), где A1 – ячейка с исходным числом. Ноль во втором аргументе означает, что знаков после запятой не останется. Например, 7,4 станет 7, а 7,5 станет 8. Если нужно всегда вниз или всегда вверх, используйте ОКРУГЛВНИЗ или ОКРУГЛВВЕРХ.

 

Как округлить число до десятков или сотен?
Используйте отрицательный разряд. Например, =ОКРУГЛ(A1;-1) округляет до десятков, а =ОКРУГЛ(A1;-2) – до сотен. Если в A1 стоит 126, то округление до десятков даст 130, а до сотен – 100. Это удобно для крупных отчетов, где не нужны точные копейки или единицы.

 

Почему Excel показывает странные хвосты после запятой?
Так бывает из-за ограниченной точности и особенностей хранения дробных чисел в компьютере. Например, некоторые десятичные дроби не могут быть идеально представлены во внутреннем двоичном виде. В обычных задачах это лечится нормальным форматированием или функцией ОКРУГЛ. Если вы видите хвост вроде 0,30000000000004, просто округлите результат до нужного количества знаков.

 

Можно ли округлить сразу целый столбец?
Да. В первой ячейке рядом с исходными данными введите формулу, например =ОКРУГЛ(A1;2). После этого протяните формулу вниз за маленький квадратик в правом нижнем углу ячейки. Excel применит формулу ко всем строкам. Затем при необходимости можно скопировать результат и вставить его как значения.

Что лучше для денег: формат ячейки или ОКРУГЛ?
Для красивого отображения денег используйте денежный или финансовый формат. Для расчетов лучше дополнительно использовать ОКРУГЛ, если по вашей логике сумма должна считаться именно с двумя знаками после запятой. Например, если вы считаете цену, скидку, НДС или итог по строкам, сначала продумайте, где именно должно происходить округление. Иначе итог по строкам и общий итог могут немного отличаться.

 

Нужно ли включать «Задать указанную точность»?
В большинстве случаев – нет. Эта настройка может навсегда изменить сохраненные значения в книге, ориентируясь на то, что отображается в ячейках. Для обычного округления лучше использовать функции. Включайте эту настройку только тогда, когда точно понимаете последствия и уже сделали копию файла.

На этом все. Если у вас еще остались какие-то вопросы, то вы смело можете задавать их в комментариях. Всем пока.