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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[expert_bq id=»1570″]Теперь давайте предположим, что мы хотим вычесть конкретное число из всех ячеек столбца, но оно не просто будет указано в формуле, а будет также записано в определенной ячейке. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Таблицы, как мы знаем, содержат данные как по горизонтали (столбцы), так и по вертикали (строки). И довольно часто требуется найти разность между числовыми данными, содержащимися в разных столбцах (двух и более). Причем, желательно автоматизировать данный процесс, чтобы не тратить на выполнение этой задачи много времени.

Как рассчитать процентное изменение в Excel (формула% увеличения / уменьшения)

• Теперь, выделим все данные таблицы (за исключением вспомогательного столбца). В нашем случае это ячейки диапазона A2:D15 ;
• Затем, на вкладке «Главная» на Панели инструментов нажмем на пункт «Условное форматирование» -> «Создать правило»:

ОТБР – позволит отбросить дробную часть, оставив целое число. По законам математики 23,5168 должно было округлиться до 24, а у нас число 23 – дробная часть просто отброшена. Запись: =ОТБР(число;число разрядов).