No Image

Как правильно округлять в экселе

СОДЕРЖАНИЕ
28 просмотров
16 декабря 2019

При выполнении деления или работе с дробными числами, 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.

    Функцию можно вводить, как в ячейку, так и в строку функций, предварительно выделив ту ячейку, в которой она будет находиться. Перед каждой функцией следует ставить знак «=».

    Существует и несколько другой способ введения функций округления. Его особенно удобно использовать, когда есть таблица со значениями, которые нужно преобразовать в округленные числа в отдельном столбике.

      Переходим во вкладку «Формулы» и кликаем по кнопке «Математические». В открывшемся списке выбираем подходящую функцию, например, «ОКРУГЛ».

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

    Читайте также:  Как заблокировать сообщения в контакте от человека

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

    Опять открывается окно аргументов функции. В поле «Число разрядов» записываем разрядность, до которой нам нужно сокращать дроби и применяем изменения.

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

    Теперь все значения в столбце будут округлены.

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

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    К таблицам у всех нас разное отношение, кто-то их очень любит и “рисует” по каждому поводу, кто-то терпеть не может. Но всем нам иногда приходится с ними работать, поэтому полезно знать основные возможности табличного редактора и дополнительные фишки, например, закрепление строк, использование сортировки и фильтров. Такие инструменты могут здорово облегчить жизнь.

    Сегодня же мы говорим о том, как округлить число в Excel, какие есть способы, для чего это нужно, и как правильно применять функции.

    Способы

    Под округлением каждый из нас может понимать что-то свое, поэтому давайте рассмотрим, какие варианты есть в Excel:

    1. Простое отбрасывание дробной части. Например, у нас есть число 12,3456, мы же хотим видеть только десятые и сотые доли, большая точность нас не интересует. Отбрасыванием дробной части мы получим результат 12,34. Такой способ часто используется для красоты, чтобы не перегружать таблицу и себя лишними знаками. В таком случае для решения задачи можно использовать формат ячеек.
    2. Математическое округление. В данном случае часть знаков после запятой отбрасывается, но при этом учитывается следующая цифра после значимого для нас разряда. Мы смотрим, она больше 5 или меньше. Например, у нас есть число 12,57. Мы хотим округлить его до десятых. Тогда по правилам математики мы получаем 12,6. То есть мы увеличили десятые доли на 1, потому что сотые больше 5. Если бы мы округляли 12,52, то получили бы 12,5.
    3. Округление до ближайшего большего или меньшего числа, так называемое округление “вверх” и округление “вниз” соответственно. Например, у нас есть исходные данные: 12,75, 12,31, 11,89, которые мы хотим преобразовать в целые величины. Если мы используем округление “вниз”, то получим 12, 12, 11. Если округляем в большую сторону, то получим 13, 13, 12.
    4. Округление до десятков, сотен, тысяч и так далее. Этот вариант бывает полезен, когда нам нужны приблизительные вычисления, без детализации.
    5. Округление до ближайшего четного и нечетного значения.
    6. Округление до ближайшего кратного какой-то величине числа. Например, у нас есть 124 конфеты, а в 1 коробку вмещается 13 конфет. И мы хотим понять, сколько конфет у нас разместится в коробках по 13 штук. Тогда мы будем округлять 124 до меньшего целого числа кратного 13 и получим результат 117.

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

    Формат ячеек

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

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

    Во вкладке “Число” выбираем подходящий формат.

    “Общий” используется по умолчанию и отображает данные в ячейках так, как они есть, изменить внешний вид тут не получится.

    “Числовой” позволяет отображать столько знаков после запятой, сколько нужно, или вовсе убрать дробную часть.

    “Денежный” и “Финансовый” похожи на “Числовой”, но позволяют также указывать валюту.

    В “Процентном” тоже можно регулировать количество знаков после запятой. Если мы переключаемся на этот формат, то исходные данные в ячейках умножаются на 100, и добавляется значок %.

    Я для своих вычислений чаще всего пользуюсь числовым форматом. Он универсальный и позволяет быстро привести ячейки к единому виду.

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

    Читайте также:  Как заказать деньги мтс крым

    Внимание: при изменении формата данных фактические значения в ячейках не меняются, они остаются прежними, просто отображаются по-другому.

    Как работать с функциями в Excel

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

    Функции ускоряют и упрощают вычисления. Например, нам надо посчитать сумму в столбце, в котором 100 значений. Мы можем делать это самым прямым способом “1+20+30+43. ” и так 99 раз. Или написать встроенную команду “СУММ”, указать диапазон ячеек, и программа за секунду сделает расчеты.

    Первый способ

    Чтобы вручную написать формулу в ячейке с использованием функции, обязательно нужно:

    1. Поставить знак =.
    2. Правильно указать название.
    3. Открыть круглую скобку.
    4. Указать через точку с запятой аргументы, то есть данные, которые будут использоваться.
    5. Закрыть скобку.

    Что может выступать аргументом математических формул:

    Пример: функция “=СУММ(F21:F30)” посчитает сумму чисел, написанных в столбце F с позиции F21 до F30.

    Эти правила нужно запомнить, иначе программа не сможет сделать вычисления, а будет выдавать ошибку такого вида.

    Второй способ

    Если неудобно писать команды вручную, то можно воспользоваться библиотекой, которая находится во вкладке “Формулы”.

    Нас в данный момент интересуют математические операции. Нажимаем на соответствующий раздел и выбираем нужную процедуру.

    Откроется диалоговое окно, в котором легко понять, какие аргументы нужно указать. Заполните поля и нажмите “ОК”.

    Отбрасывание дробной части

    Этот способ подойдет, если нам не важно, произойдет ли округление по математическим законам, и в какую сторону изменится значение. Когда мы просто хотим упростить вычисления, убрав лишние знаки, то можем отбросить их. Для этого есть функция “ОТБР”, которая содержит 2 параметра:

    • число, над которым нужно произвести действие;
    • количество знаков после запятой, которое следует оставить.

    Например, у нас есть цена товара 4 716 рублей 77 копеек и еще уйма знаков после запятой. Логично отбросить все лишнее и получить только рубли и копейки.

    Если же мы хотим получить только рубли без копеек, то вторым аргументом пишем не 2, а 0.

    Математическое округление

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

    Например, у нас есть значение 34,83021, и мы хотим округлить его с точностью до сотых. Тогда формула будет выглядеть так: “=ОКРУГЛ(34,83021;2)”.

    Если в качестве второго аргумента указать 0, то получим целочисленное значение, то есть округлим первоначальные данные до единиц. А если количество разрядов будет отрицательным, то преобразовываться будет уже целое число.

    Например, мы хотим округлить до сотен 1245,329. Тогда делаем такую запись в ячейке нашей таблицы: “=ОКРУГЛ(1245,329;-2)” и получаем результат 1 200.

    В меньшую и большую сторону

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

    До дробных частей

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

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

    Давайте посмотрим на примере работу этих двух команд.

    До целых

    Есть еще один способ округлить в меньшую или большую сторону. Для этого нам понадобятся функции “ОКРВНИЗ” и “ОКРВВЕРХ”. Они применяются для получения целочисленного результата.

    Второй аргумент показывает, до какого разряда мы округляем:

    • 10 – до десятков;
    • 100 – до сотен;
    • 1 000 – до тысяч и т. д.

    Показываю на примере.

    Округление “вниз” до единиц можно заменить функцией “ЦЕЛОЕ”, у которой есть единственный аргумент – число. Результат будет таким же, как при использовании “ОКРВНИЗ”.

    Округлить в большую сторону до ближайшего четного или нечетного целого значения помогают действия “ЧЁТН” и “НЕЧЁТ”. Пример использования одного из них вы видите на скриншоте.

    До целых с учетом кратности

    Помните пример с конфетами, который был в начале статьи? Сейчас мы научимся округлять до ближайшего кратного какой-то величине значения. Для этого служат 2 функции: “ОКРВНИЗ.МАТ” и “ОКРВВЕРХ.МАТ”. Первая уменьшает исходную величину, а вторая соответственно увеличивает.

    Например, у нас есть число 124, и мы хотим округлить его до ближайшего большего кратного 5 значения. Тогда пишем в ячейке: “=ОКРВВЕРХ.МАТ(124;5)” и в результате получаем 125.

    Читайте также:  Как проверить диагностику компьютера

    В Google Таблицах

    Если вы любите работать в режиме онлайн, то можете задать формулу для округления данных в Google Таблицах.

    Чтобы изменить формат ячеек, используйте опцию на основной панели инструментов.

    Для уменьшения или увеличения разрядности также есть соответствующий инструмент.

    Чтобы открыть перечень встроенных формул, нажмите на маленький черный треугольник рядом со значком суммы.

    Чтобы округлить данные в ячейках, наводим курсор на математические функции и выбираем один из вариантов:

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

    Заключение

    Мы рассмотрели все основные способы округления чисел в Microsoft Excel и Google Таблицах. Конечно, есть много частных случаев, которые требуют нетривиального решения. Если вы столкнулись с ситуацией, которая не описана в этой статье, напишите комментарий, и я постараюсь помочь и подсказать, как правильно составить формулу.

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

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

    Изменение количества знаков после запятой без изменения значения

    На листе

    Выделите ячейки, формат которых требуется изменить.

    Чтобы после запятой отображалось больше или меньше знаков, на вкладке Главная в группе Число нажмите кнопку Увеличить разрядность или Уменьшить разрядность .

    Во встроенном числовом формате

    На вкладке Главная в группе Число щелкните стрелку рядом со списком числовых форматов и выберите пункт Другие числовые форматы.

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

    В поле Число десятичных знаков введите требуемое число знаков после запятой.

    Округление числа вверх

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

    Округление числа вниз

    Округление числа до ближайшего значения

    Округление числа до ближайшего дробного значения

    Округление числа до указанного количества значимых разрядов

    Значимые разряды — это разряды, которые влияют на точность числа.

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

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

    Округляемые отрицательные числа прежде всего преобразуются в абсолютные значения (значения без знака "минус"). После округления знак "минус" применяется повторно. Хотя это может показаться нелогичным, именно так выполняется округление. Например, при использовании функции ОКРУГЛВНИЗ для округления числа -889 до двух значимых разрядов результатом является число -880. Сначала -889 преобразуется в абсолютное значение (889). Затем это значение округляется до двух значимых разрядов (880). После этого повторно применяется знак "минус", что дает в результате -880.

    При применении к положительному числу функции ОКРУГЛВНИЗ оно всегда округляется вниз, а при применении функции ОКРУГЛВВЕРХ — вверх.

    Функция ОКРУГЛ округляет дробные числа следующим образом: если дробная часть больше или равна 0,5, число округляется вверх. Если дробная часть меньше 0,5, число округляется вниз.

    Функция ОКРУГЛ округляет целые числа вверх или вниз аналогичным образом, при этом вместо делителя 0,5 используется 5.

    В общем при округлении числа без дробной части (целого числа) необходимо вычесть длину числа из нужного количества значимых разрядов. Например, чтобы округлить 2345678 вниз до 3 значимых разрядов, используется функция ОКРУГЛВНИЗ с параметром -4: = ОКРУГЛВНИЗ(2345678,-4). При этом число округляется до значения 2340000, где часть "234" представляет собой значимые разряды.

    Округление числа до заданного кратного

    Иногда может потребоваться округлить значение до кратного заданному числу. Например, допустим, что компания поставляет товары в ящиках по 18 единиц. С помощью функции ОКРУГЛТ можно определить, сколько ящиков потребуется для поставки 204 единиц товара. В данном случае ответом является 12, так как число 204 при делении на 18 дает значение 11,333, которое необходимо округлить вверх. В 12-м ящике будет только 6 единиц товара.

    Может также потребоваться округлить отрицательное значение до кратного отрицательному или дробное — до кратного дробному. Для этого также можно применять функцию ОКРУГЛТ.

    Комментировать
    28 просмотров
    Комментариев нет, будьте первым кто его оставит

    Это интересно
    No Image Компьютеры
    0 комментариев
    No Image Компьютеры
    0 комментариев
    No Image Компьютеры
    0 комментариев
    Adblock detector