Меню

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

Excel для бухгалтера: исправление ошибки округления

Бухгалтеры (и не только) знают одну «нехорошую» особенность Excel – «неумение» правильно суммировать. 🙂 Иногда это приводит к казусам в бухгалтерских документах, сформированных в Excel (рис. 1)

Рис. 1. Фрагмент счет-фактуры с «неверным» суммированием

Скачать заметку в формате Word, примеры в формате Excel

Видно, что общий итог по налогу (значение в ячейке G7) и стоимости товаров (Н7) отличаются на копейку от суммы по строкам (G4:G6 и Н4:Н6, соответственно). Это ошибка является следствием округления. Дело в том, что значения только отображаются в формате с двумя десятичными знаками. Фактические значения в этих ячейках содержат больше десятичных знаков (рис. 2). Excel суммирует не отображаемые значения, а фактические.

Рис. 2. Тот же счет-фактура с большим числом знаков после запятой

Чтобы значение в ячейке G7 равнялось сумме отображаемых значений в ячейках G4:G6, можно применить формулу массива, проводящую округление значений до двух десятичных знаков перед суммированием: <=СУММ(ОКРУГЛ(G4:G6;2))>(рис. 3). [1]

Рис. 3. «Правильное» суммирование с использованием формулы массива

Чуть подробнее, как работает эта формула. Excel формирует виртуальный массив (в памяти компьютера), состоящий из трех элементов: ОКРУГЛ(G4;2), ОКРУГЛ(G5;2), ОКРУГЛ(G6;2), то есть значений в ячейках G4:G6, округленных до двух десятичных знаков, а затем суммирует эти три элемента. Вуаля! 🙂

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

[1] Идея подсмотрена в книге Джона Уокенбаха «MS Excel 2007. Библия пользователя». Если вы не использовали ранее формулы массива, рекомендую начать с заметки Excel. Введение в формулы массива .

46 комментариев для “Excel для бухгалтера: исправление ошибки округления”

Для полноты картины можно упомянуть еще об одном варианте, в параметрах Excel указать «точность как на экране» (Файл-Параметры-Дополнительно-При пересчете этой книги: задать точность как на экране)

Вот не рекомендуют программисты такой способ

Источник

Округление чисел в Microsoft Excel

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

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

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

    Читайте также:  А вот как настроить valve hammer editor

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

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

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

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

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

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

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

    Источник

    

    Десятичные знаки в Excel. Автоматический ввод

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

    Как настроить десятичные знаки в Excel

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

    Для версии Excel 2007

    • Кликните по кнопке «Office» в левом верхнем углу.

    • В открывшемся окне перейдите в самый низ и нажмите на кнопку ПараметрыExcel.
    • В новом окне в левой половине перейдите на вкладку Дополнительно.
    • В правой половине окна установите галочку напротив записи Автоматическая вставка десятичной запятой.
    • Чуть ниже, в маленьком окошке напротив записи Число знаков после запятой, установите количество знаков после запятой.

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

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

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

    Для версии Excel 2003

    • Войдите в меню Сервис и выберите команду Параметры.
    • В открывшемся окне перейдите на вкладку Правка.
    • В области Параметры выберите опцию Фиксированный десятичный формат при вводе.
    • Остальные настройки не отличаются от описанных выше.

    Вот таким образом можно настроить десятичные знаки в excel .

    Видео о десятичных знаках в excel:

    Источник

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

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

    Читайте также:  Как настроить home connect

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

    На листе

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Округляемые отрицательные числа прежде всего преобразуются в абсолютные значения (значения без знака «минус»). После округления знак «минус» применяется повторно. Хотя это может показаться нелогичным, именно так выполняется округление. Например, при использовании функции ОКРУГЛВНИЗ для округления числа -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 единиц товара.

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

    Источник