Округление чисел в excel в огромную и наименьшую сторону

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

Формат ячейки

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

  1. Заполняете рабочий лист необходимыми данными.
  1. Щелкаете по подходящей ячейке правой кнопкой мыши и ищете строчку Формат ячеек….

Щелкаете по нужной ячейке

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

В появившемся окне

Резвее эту операцию можно провести, используя особые клавиши в блоке Число во вкладке Основная на Панели инструментов.

Число во вкладке

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

Функция ОКРУГЛ

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

К примеру, нужно округлить число до 2 символов опосля запятой. Тогда в строке функции будет последующая запись: ОКРУГЛ(C4;2), где С4 – аргумент, а 2 – количество разрядов.

Необходимо округлить число до 2

Принципиально! Excel при округлении управляется правилами арифметики. Если цифра оканчивается от 1 до 4, то округление идет в наименьшую сторону, если от 5 до 9 – в огромную сторону.

Зависимо от значения второго блока функции можно настроить разные режимы округления:

  1. Чтоб округлить до целого числа, нужно поставить 0.

Можно настроить различные режимы округления

  1. До 10-ов, соответственно 1, до сотен — 2.
  1. Для округления в наименьшую сторону до целого нужно вписать -1 (минус 1).

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

Доп функции

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

К примеру, функция ОТБР, которая отбрасывает числа от установленного разряда, другими словами происходит не округление, а отсечение.

Функция ОКРВВЕРХ дозволяет округлять значение ячейки до целого числа, при всем этом, если нужно округлить в огромную сторону кратно 100, нужно указать точность 100, а если до 50, то, соответственно, 50.

На заметку! Синтаксис ОКРВВЕРХ различается от ОКРУГЛ тем, что 2-ой блок не число разрядов, а точность.

Формула ОКРВНИЗ, подобна предшествующей, лишь округляется вниз до целого. Зависимо от избранной точности, можно округлить и до 5, но это работает лишь для спектра значений от 5,00 до 9,99.

Формула ОКРВНИЗ, аналогична предыдущей

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

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

Функции округления в Гугл таблицах

Функции округления в Google таблицах

Точность в данных — залог глубочайшей и полной аналитики вашего бизнеса, но зрительное отображение в отчетах чисел с дробной частью совсем неловкое для восприятия. В гугл (excel) таблицах нередко употребляют округление чисел для придания отчетам комфортного вида. А еще, есть масса случаев, когда округление числа в огромную либо наименьшую сторону помогает сформировывать закупки продуктов, строить аналитические отчеты бюджета и прочее.

Интегрированный инструмент отображения десятичных чисел в google таблицах

Фактически все юзеры совершают ошибку, применяя в качестве округления при помощи форматирования чисел через обычные инструменты из репозитория таблиц.

Пример округления чисел встроенными инструментами формата числа в гугл таблице

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

Интересно почитать:  Функция если в excel если ячейка содержит

Функция ROUND (ОКРУГЛ) в Гугл таблице с положительными аспектами

Функция ROUND (ОКРУГЛ) фиксирует числовое значение обозначенной ячейки (либо просто числа в формуле) и округляет ее согласно обозначенному числу десятичных символов в согласовании со обычными математическими правилами.

Обычные правила округления последующие:

  • Если цифра справа от числа, которую необходимо округлить, меньше 5 — она остается постоянной.
  • Если цифра справа от числа, которую необходимо округлить, больше либо равна 5 — то она возрастает на 1. Иными словами, число «округляется в огромную сторону» до наиблежайшей числа.

Синтаксис:

  • Value (Значение) — число, которое необходимо округлить. Это быть может иная функция, вложенная в формулу округления, прямое числовое значение в формуле либо ссылка на ячейку с данными, содержащими числовое значение.
  • [Places (Число_знаков)] — количество десятичных символов либо цифр, до которых необходимо округлить значение. Этот параметр не является неотклонимым. Если он не указан — то его значение равно 0.

Пример работы функции ROUND (ОКРУГЛ) в гугл таблице с положительными значениями

Функция ROUND (ОКРУГЛ) в Гугл таблице с отрицательными аспектами

Функцию ROUND (ОКРУГЛ) также можно употреблять с отрицательными значениями параметра [place (число_знаков)] . В таковых вариантах значение округляется слева от десятичного разделителя:

  • Если параметр разряда равен «-1» , функция округляет значение до ближайших 10-ов.
  • Если параметр разряда равен «-2» , функция округляет значение до ближайших сотен.
  • Если параметр разряда равен «-3» , функция округляет значение до ближайших тыщ, и т.д.

Разглядим примеры наглядно, чтоб осознать, как функция ROUND (ОКРУГЛ) работает с отрицательными значениями десятичного параметра [places (число_знаков)] :

Пример работы функции ROUND (ОКРУГЛ) с отрицательными критериями в гугл таблице

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

Функция ROUNDUP (ОКРУГЛВВЕРХ) с положительным параметром

Функция ROUNDUP (ОКРУГЛВВЕРХ) работает по тому же принципу, как и функция ROUND (ОКРУГЛ), кроме того, что она постоянно округляет значение в огромную сторону.

Синтаксис:

Пример работы функции ROUNDUP (ОКРУГЛВВЕРХ) в гугл таблицах

Из приведенного выше примера ясно, что функция ROUNDUP (ОКРУГЛВВЕРХ) постоянно округляет числовое значение в огромную сторону до данного количества десятичных символов.

Функция ROUNDUP (ОКРУГЛВВЕРХ) с отрицательным параметром

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

Пример работы функции ROUNDUP (ОКРУГЛВВЕРХ) с отрицательным параметром в гугл таблицах

Функция ROUNDDOWN (ОКРУГЛВНИЗ) с положительным параметром

Функция ROUNDDOWN (ОКРУГЛВНИЗ) работает так же, как функция ROUNDUP (ОКРУГЛВВЕРХ), лишь уже напротив, постоянно округляет в наименьшую сторону, вопреки обычным правилам округления числа.

Синтаксис:

Функция ROUNDDOWN (ОКРУГЛВНИЗ) с положительным параметром

Функция ROUNDDOWN (ОКРУГЛВНИЗ) с отрицательным параметром

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

Функция ROUNDDOWN (ОКРУГЛВНИЗ) с отрицательным параметром

Функция MROUND (ОКРУГЛТ) в гугл таблице

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

Функция MROUND (ОКРУГЛТ) работает так же, как функция ROUND (ОКРУГЛ), кроме того, что дозволяет округлять числовое значение до наиблежайшего целого числа, кратного другому числу.

Синтаксис:

  • Value (значение) — значение числа, ссылка на ячейку, ссылка на другую числовую функцию, которое необходимо округлить.
  • Factor (точность) — значение числа, до кратности которого необходимо округлить value (значение) .

В отличие от функций ROUND, ROUNDDOWN и ROUNDUP, нет способности употреблять отрицательные значения во 2-м параметре функции MROUND (ОКРУГЛТ), если 1-ый параметр также не является отрицательным числом.

Функция MROUND (ОКРУГЛТ) в google таблице

В данной статье мы разглядели фактически все нередко встречающиеся функции округления: ROUND (ОКРУГЛ), ROUNDUP (ОКРУГЛВВЕРХ), ROUNDDOWN (ОКРУГЛВНИЗ) и MROUND (ОКРУГЛТ). В приведенных примерах разобрались с механизмами работы этих функций.

Они употребляются для решения задач, до этого всего: финансистами и людьми, занимающимися планированием закупок продуктов и т.д., аналитиками и рекламщиками.

Поделитесь в комментах, где для вас приходилось встречаться с такового рода задачками округления чисел и с какими неуввязками вы столкнулись, при решении этих задач?

Excel Rounding Functions

How to choose functions in Excel that round numbers, up or down. Or format a number to look rounded, but keep the original value in the cell.

Интересно почитать:  Индекс функция эксель

Rounding in Excel

To see a quick overview of 7 ways to round in Excel, you can watch this short slide show. There are details and step by step instructions, below the slide show.

Also, get the 7 Ways to Round sample workbook, so you can follow along with the slide show. The zipped file is in xlsx format, and does not contain any macros.

Round With Formatting

With formatting, a number can appear rounded on the worksheet, but the cell value is not affected. In the screen shot below, the total appears to be incorrect, because 2+3 does not equal 4.

round with formatting total incorrect

However, if you click on any of the cells, you can see its actual value in the formula bar. For example, the coffee price, in cell B3, is $1.54.

round with actual value in formula bar

The numbers in cells B3:B5 were formatted as currency, using the Number format drop down on the Ribbon’s Home tab.

round with formatting currency

Then, the Decrease Decimal button was clicked twice, to set the number of decimals to zero.

round with formatting decrease decimals

If the Currency number format is reapplied, it shows the default formatting of 2 decimals, so the total amount looks correct — 1.54 + 2.54 =4.08

round with formatting 2 decimals

When the numbers were rounded with formatting, each amount displayed as the closest dollar amount:

  • the coffee price rounded up to $2
  • the sandwich rounded up to $3
  • the total rounded down to $4.

Automatic Rounding of Long Numbers

If you leave the default number format — General — and enter long numbers on a worksheet, Excel might round some of those numbers automatically.

To see how Excel can automatically round numbers with the General format, watch this short video, and the details are below the video

Maximum 11 Characters

In my tests, a maximum of 11 characters (including a decimal place) are displayed in a cell, even if the column is wide enough to show more numbers.

    If a number is longer than 11 characters, the decimals are rounded, to show a maximum of 11 characters.

long numbers rounded - decimals

long numbers rounded - Scientific

To prevent this automatic rounding/ormatting, apply one of the other number formats, such as Number, and set the number of decimals that you want to display.

Column Too Narrow

If you reduce the column width, with General format applied, the numbers will be automatically rounded, in an attempt to fit.

  • Decimals are rounded, to show only the number that will fit the column
  • The number will be rounded to an integer, if there is no room for decimals
  • The number will change to Scientific format, if the rounded integer will not fit
  • The cell will display number signs, if it can’t display in Scientific format

long numbers rounded - Scientific

Round With Custom Number Format

In a summary report, with very large numbers, precision to the decimal places is probably not required. It’s difficult to get a quick overvier, if the numbers are too detailed, as shown below.

round with formatting large numbers

To round large numbers, and make them easier to read, you can use Custom Number Formats, to round to the left of the decimal point.

For example, show 123456.78 in one of the following formats.

round with formatting custom formatting

Apply a Custom Format

To apply a custom format to cells:

On the Ribbon’s Home tab, click the dialog launcher, at the bottom right of the Number group.

round with formatting launch number format

On the Number tab, in the list of Categories, click Custom.

Then, select one of the existing custom formats, or clear the box, to create your own.

round with formatting custom format

For example, enter the custom format — #, — to show only the rounded thousands in a large number.

Then, click the OK button, to close the dialog box, and apply the format.

round with formatting custom format thousands

Here are a few more custom number formats that you can use:

round with formatting custom format examples

You can also use a custom number format to line up numbers with varying decimal places. The question mark (?) is a placeholder that adds extra spacing, so the numbers line up at the decimal point.

round with formatting custom format examples

There are more examples of Custom formats on the Microsoft website: Create a Custom Number Format

Интересно почитать:  Excel функция поиск

ROUND Function

The ROUND function can be used to round a number to a specific number of places from the decimal point. For example:

  • Round a sales amount to the nearest cent
  • Round an age to the nearest decade

In the screen shot below, the sales tax (HST) is rounded to the nearest cent, by using 2 as the number of decimal places to round.

ROUND right

In the screen shot below, the age is rounded to the nearest decade, by using -1 as the number of decimal places to round. Because the number is negative, it rounds to the left of the decimal place.

ROUND right

ROUNDDOWN Function

The Excel ROUNDDOWN rounds a number down, to a specific number of places from the decimal point. For example:

  • Round a year down to the nearest decade

In the screen shot below, the publication year is rounded down to the nearest decade, by using -1 as the number of digits in the ROUNDDOWN function. Because the number is negative, it rounds to the left of the decimal place.

=ROUNDDOWN(B4,-1)

ROUND right

ROUNDUP Function

The Excel ROUNDUP function rounds a number up, to a specific number of places from the decimal point. For example:

  • Round a price up to the nearest dime

In the screen shot below, the price is rounded up to the nearest dime, by using 1 as the number of digits in the ROUNDUP function.

ROUND right

MROUND Function

The MROUND function rounds a number to the closest specified multiple. For example:

  • Round a sales amount to the nearest nickel

In the screen shot below, the total sales amount is rounded to the nearest nickel, by using 5 as the multiple in the MROUND function. Instead of hard coding the multiple into the formula, it is entered in cell B9, where it can be changed easily.

ROUND right

FLOOR Function

The Excel FLOOR function rounds numbers down, toward zero, based on the multiple of significance that you specify.

FLOOR(number,significance)

  • Round a year down to the nearest decade

In the screen shot below, the year is rounded down to the nearest decade, by using 10 as the significance in the FLOOR function.

ROUND right

NOTE: In the FLOOR function, if the number is positive, and the significance is negative, the result is the #NUM! error. The FLOOR function works well in the music example above, because the song’s year is always a positive number.

FLOOR.PRECISE or FLOOR.MATH Function

For Excel 2010, Microsoft recommends that you use the FLOOR.PRECISE function, instead of FLOOR.

For Excel 2013, use FLOOR.MATH, instead of FLOOR.

Both FLOOR.PRECISE and FLOOR.MATH return a number that is rounded down to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded down.

FLOOR.PRECISE(number,significance)

FLOOR.MATH(number,significance)

The three functions are compared in the following table. The results from FLOOR.PRECISE and FLOOR.MATH are the same, but FLOOR is different — it rounds toward zero, and returns an error if the number is positive and the significance is negative.

ROUND right

CEILING Function

The Excel CEILING function rounds numbers up, toward zero, based on the multiple of significance that you specify. For example:

  • Round a price up to the nearest quarter

In the screen shot below, the price is rounded up to the nearest quarter, by using 0.25 as the multiple in the CEILING function.

=CEILING(B4,0.25)

ROUND right

Sum Rounded Numbers

If a column has decimal numbers, you can use the ROUND function, to round each number individually, BEFORE calculating the total. This might give a different result than rounding the numbers AFTER summing them. .

In this example, there are decimal numbers in cells A1:A5

1.5
2.9
3.8
9.2
3.5

In cell A6, the formula rounds the total to 0 decimal places, AFTER summing them. It returns a result of 21.0

=ROUND(SUM(A1:A5), 0)

The formula in cell A7 rounds each number BEFORE summing them. It returns a result of 22.0

Ссылка на основную публикацию
Adblock
detector