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

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

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

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

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

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

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

2,3(3)

И тут сразу же встает вопрос – а как компьютер может работать с подобными бесконечными числами? На самом деле, если говорить строго, то НИКАК. Все компьютеры прибегают к хитростям и округляют последний разряд. Давайте посмотрим, как это происходит в том же самом Excel.

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

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

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

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

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

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

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

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

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

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

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

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

Для второго способа, давайте изменим цифры в нашей формуле и попробуем разделить семь (7) на девять (9). Смотрите, в отличие от прошлого примера, когда в разрядах было число три (3) и округление было до нуля (0), то тут округление идет до единицы (1) следующего разряда – а это восемь (8).

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

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

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

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

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

То есть проводя какие-то подсчёты, вы не теряете в точности. Но, если у вас очень большие таблицы, а процессор не такой мощный (как хотелось бы), то вы можете столкнуться с проблемой. В таком случае расчеты могут быть по часу или два. Если вам не важна точность расчетов, то можно уменьшить количество разрядов, хранимых в памяти ячейки. В таком случае процесс подсчета будет быстрее, а данные будут браться с округлением, которое вы выставите. Давайте посмотрим, как это можно сделать.

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

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

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

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

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

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

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

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

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

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

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

Для людей, которые работают с финансами, есть еще одна интересная формула:

ОКРУГЛТ

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

У этой же формулы есть подобные вариации:

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

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

• ОТБР – отбрасывает всю дробную часть и превращает в целое число.
• ЧЕТН – округление до четного.
• НЕЧЕТН – округление до нечетного.

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

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

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

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

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

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

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