Как округлить число в excel до целого в большую сторону

Округлить до

MulTEx » 13 Январь 2020 Дмитрий 2507 просмотров

Данная функция является частью надстройки MulTEx

  • Описание, установка, удаление и обновление
  • Полный список команд и функций MulTEx
  • Часто задаваемые вопросы по MulTEx

Вызов команды: MulTEx -группа Ячейки/Диапазоны —Ячейки —Округлить до

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

Округлить значения выделенных ячеек до — указывается количество десятичных разрядов, до которого надо округлить число. Например, если в ячейке записано число 45 127,1384. и указано 2, то число будет округлено до двух знаков после запятой, при этом произойдет округление ровно так же, как это делает Excel: 45 127,14. Но есть и другие ситуации — когда округлить надо не просто знаки — а целые числа. К примеру, число 45 127,13 необходимо округлить до тысяч, чтобы получить 45 000. Тогда надо будет указать количество знаков после запятой с минусом: -3. Если число надо просто округлить до целого, то необходимо указать количество знаков после запятой — 0. Диапазон знаков работает от -15 до 15.

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

Преобразовывать числа, записанные как текст, в числа — по умолчанию команда игнорирует ячейки, в которых записан текст. При этом если в ячейке записано число, но записано как текст, то команда воспримет его как текст и проигнорирует. Заметка: Что такое число, записанное как текст? Это когда в ячейке отображается число, но по типу данных является текстом. Как правило такие числа помечаются зеленым треугольником в левом верхнем углу ячейки. Формат отображения для таких чисел невозможно изменить напрямую через Формат ячеек, многие функции не учитывают(например СУММ(SUM)).

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

Так же надо быть осторожным с датами и временем. Т.к. эти данные являются для Excel числовыми, то они так же будут округлены, что может быть нежелательным. Читать подробнее про то, как Excel воспринимает данные >>

Для чего это вообще может быть нужно? Часто при выгрузке документов из других систем и файлов(вроде 1С, файлов CSV, текстовых файлов и т.п.) в Excel числа выгружаются с «хвостами» — 5 и более знаков после запятой: 546 932,78923. Но в ячейке они могут отображаться с округлением — 546 932,79. Как правило это происходит из-за назначенного формата ячеек через Формат ячеек —Число —Числовой —Число десятичных знаков=2:


Это часто приводит к расхождениям расчетов формулами с расчетами на калькуляторе, т.к. на калькуляторе мы суммируем числа с двумя знаками после запятой, а Excel при суммировании формулами учитывает все 5 знаков, т.е. реальное значение числа. Часто в таких ситуациях применяют функции вроде ОКРУГЛ(ROUND), но далеко не всегда это удобно и практично, т.к. формула должна быть записана в другие ячейки. А это приводит к дополнительным неудобствам(создание доп.столбцов, дублирование листов, таблиц и т.п.). С помощью команды Округлить до это можно сделать прямо в этих же ячейках, при этом допускается выделить несколько несмежных ячеек или всю нужную область на листе, вызвать команду и указать нужное количество десятичных разрядов. Так же можно указать, чтобы преобразовывались исключительно ячейки с числами, а ячейки с формулами пропускались. Для этого надо установить флажок Не изменять ячейки с формулами.

Формат ячеек

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

  1. Выделям нужный диапазон, и нажав правой кнопкуой мыши выбираем пункт «Формат ячеек». 
  2. Далее, ищем пункт «Числовой» и меняем значение в блоке «Число десятичных знаков» вместо 2 (по умолчанию) на ноль. 

Теперь, если вы напишите какое-либо нецелое число, то программа автоматически его округлит в данном диапазоне. Например, даже если я напишу 1,5, то Excel автоматом вставит 2, хотя формально там все равно будет находится наше реальное значение.

И вот почему:

  • Во-первых, реальное значение будет написано в строке для формул вверху;
  • Во-вторых, если я суммирую по формуле два этих числа (2+2), то нам выдаст 3, а не 4. А все потому, что цифры округленные, и система продолжает считать их как 1,5, а не 2. Но на первый взгляд кажется, что Эксель сошел с ума, раз он думает, что 2+2 будет 3.

Так что. рекомендую иметь данный способ ввиду.

Дополнительные функции

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

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

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

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

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

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

Способ 1: Применение кнопок управления разрядностью

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

  1. Выделите число, которое хотите округлить до десятых, сделав по нему клик левой кнопкой мыши.

В блоке «Число» на верхней панели нажмите кнопку «Уменьшить разрядность». Она отмечена красным выделением на следующем изображении.

Вы сразу же заметите, что пропали сотые или тысячные доли.

Если нужно, сделайте еще один или несколько кликов по этой же кнопке, добиваясь необходимого округления.

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

Установка точности расчетов

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

Для начала посчитаем сумму значений во всех наших ячейках.

  1. Для этого добавим новое поле и перейдем в него. Затем кликнем на иконку «Fx» (вызов окна вставки функции).

  1. В появившемся окне выберите категорию «Математические».

  1. Найдите в списке функцию «СУММ». Для вставки нажмите на кнопку «OK».

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

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

  1. Как только вы отпустите палец, всё вернется обратно. При этом в поле «Число 1» будет указан нужный диапазон, а не то, что стояло по умолчанию. Для сохранения данных нажмите на кнопку «OK».

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

Можете проверить при помощи калькулятора. У вас будут расхождения.

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

Для того чтобы в формуле «СУММ» происходил расчет того, что отображается, нужно сделать следующие действия.

  1. Нажмите на пункт меню «Файл».

  1. Перейдите в раздел «Параметры».

  1. Кликните на пункт «Дополнительно». После этого прокрутите окно практически до самого низа.

  1. Найдите группу настроек «При пересчете этой книги». Поставьте галочку около пункта «Задать указанную точность» (если в будущем вам эта настройка окажется ненужной, достаточно будет убрать эту галочку).

  1. Кликните на кнопку «OK». Сразу после этого вы увидите предупреждение, в котором вам сообщат, что данные в таблице будут изменены в меньшую сторону. Необходимо согласиться с этим условием нажатием на «OK».

  1. Только после этого вы сможете сохранить внесенные изменения.

  1. Снова посмотрите результат подсчетов. Теперь он изменился и соответствует сумме отображаемых чисел.

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

Как округлить число до сотен тысяч в Excel?

Если во втором аргументе будет указано число 1, тогда Excel округлит исходное значение до одного знака десятичного разряда после запятой на основе второго числового значения после запятой. Например, если исходное значение 94,45, тогда функция ОКРУГЛ с единицей во втором аргументе возвращает дробное значение до десятых 94,5. Ячейка B2:

Во втором аргументе для функции ОКРУГЛ можно так же задавать отрицательные числовые значения. Благодаря такому способу Excel округляет число на основе знаков перед запятой, то есть по левую сторону на 1 знак. Например, следующая формула с отрицательным числом -1 во втором аргументе возвращает числовое значение 90 при том же исходном числе 94,45:

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

Способ 1: Функция «Увеличить разрядность»

Самый простой и быстрый способ отключения округления чисел в Excel — использование функции «Увеличить разрядность». Она работает по принципу увеличения отображения чисел после запятой до необходимого количества, а для использования понадобится выполнить пару действий.

  1. Определитесь, для каких ячеек требуется вносить изменения, и если их несколько, выделите все сразу.

В блоке «Число» разверните список числовых форматов и решите, какой хотите использовать.

Сразу же после этого нажмите по кнопке «Увеличить разрядность» столько раз, сколько чисел хотите добавить.

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

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

Работа с таблицами Excel

Округление результатов вычислений

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

Рис. 2.28. Панель функции «ОКРУГЛ»

Данная функция округляет число до указанного количества десятичных разрядов. Синтаксис ее следующий:

ОКРУГЛ(число; количество_цифр)

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

Например, для операции извлечения корня в нашем примере формула округления будет иметь такой вид:

=ОКРУГЛ(В10/\(1/С10);2)

Первый аргумент, В10/\(1/С10), показывает, для какой операции выполняется округление, а второй (цифра 2) определяет количество десятичных разрядов. В нашем случае вычисления осуществляются с точностью до второго знака после запятой (например, до копеек). Однако округление необходимо производить на определенном этапе расчетов.

ПРИМЕР: На таможню приходит товар стоимостью 3 цента за единицу в количестве 1000 штук. Курс обмена — 29,31 руб./$, а все таможенные платежи (без учета НДС) составляют 29,45%. База начисления для таможенных платежей будет равна цене в долларах, умноженной на курс доллара и количество единиц товара:
$0,03 * 29,31 * 1000 = 879,30 руб.

При умножении базы начисления на ставку таможенного сбора получается сумма, которой быть не может (так как не существует пока в мире десятых и сотых долей копеек при перечислении их через банк):
879,30 руб. * 29,45% = 258,9539 руб.

Если и далее производить расчеты без округления размера таможенного платежа, то можно получить стоимость партии товара, которая равна стоимости товара плюс таможенные платежи:
879,30 руб. + 258,9539 руб. = 1138,25385 руб.

Таким образом, цена единицы товара будет следующей:
1138,25385 руб. : 1000 шт. = 1,138254 руб.

Полученные неправильные результаты представлены в табл. 2.1.

Цена за единицу ($) Количество (штук) Курс (руб./$) Таможенный платеж (%) Таможенный платеж (руб.) Итого стоимость партии (руб.) Цена за единицу (руб.)
0,03 1000 29,31 29,45% 258,9539 1138,25385 1,138254

Таблица 2.1. Пример неправильного расчета таможенных платежей

Поэтому некоторые значения следует округлить с точностью до копеек. А сумма таможенного платежа должна вычисляться по формуле

=ОКРУГЛ(цена в долларах х на курс доллара х количество;2)

Цифра 2 означает, что выполняется округление с точностью до второго знака (то есть до копеек).

Таким же образом можно округлить и стоимость партии товара, в результате чего получится сумма, равная 1138,25 руб. Однако операция округления цены за единицу товара может привести к нежелательным последствиям. Это зависит от того, как происходит расчет цены. Если округлить цену товара до копеек, исходя из стоимости партии товара:

=ОКРУГЛ(1138,25/1000;2)

то результат будет равен 1,14 руб. Но получается парадокс: произведя обратный расчет, мы получим, что партия стоит 1,14 * 1000 =1140 руб. Откуда-то взялись лишние 2 рубля. Эти 2 рубля могут значительно усложнить ведение бухгалтерского учета, если в бухгалтерской программе не предусмотрена возможность задания разрядности денежной единицы при некоторых операциях. До какого же знака целесообразно задать точность в данном примере?

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

Цена ($) Количество Курс (руб./$) Таможенный платеж (%) Таможенный платеж (руб.) Итого стоимость партии (руб.) Цена за единицу (руб.)
0,03 1000 29,31 29,45% 258,95 1138,25 1,13825

Таблица 2.2. Пример правильного расчета таможенных платежей и стоимости товара

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

Рис. 2.29. Функции округления в Excel

Как задать числам требуемую точность при расчетах

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

Для изменения данного параметра откройте вкладку “Файл” в верхнем меню программы. В новом окне найдите строку “Параметры” в списке слева и нажмите на нее.

На экране должно появиться окно настройки параметров. Здесь переходим в раздел “Дополнительно”, далее находим внизу правой части окна подраздел “При пересчете этой книги”, в котором предлагается выбрать четыре параметра. Напротив второго из них (“Задать указанную точность”) устанавливаем галочку и нажимаем “OK”.

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

Важно: Обратите внимание, что настройка будет применена к файлу целиком, а не к отдельному листу, из которого были открыты настройки

Как правильно округлить число в Excel и Google Таблицах: все способы с подробной инструкцией

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

Сегодня же мы говорим о том, как округлить число в 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 есть своя функция округления. Давайте научимся ими пользоваться. Но сначала посмотрим, как можно изменить внешний вид данных в таблице.

Отмена округления

​ самом деле там​​JayBhagavan​ подскажете как подправить?​ округляя. Поэтому Вам​: Подскажите пожалуйста. При​Excel​Excel​.Как применить​ округляется до значения​

​ОКРУГЛВНИЗ​​ при округлении чисел​Экспоненциальный​​ 10, чтобы упростить​ изменить ширину столбца,​​ выделить столбец, не​ большое число. Если​ не более 15​ троек гораздо больше:)​: ostrik, ширину ячейки​Sanja​ нужен именно текст.​ вводе числа 5444414644444487​

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

​ приблизительные вычисления. Существует​​ высоту строки в​ смежные ячейки, всю​ числа округлены не​ значащих цифр. Вот​Максим Зеленский​ сделайте побольше. ​: Насколько “большие значения”​Если номера вводятся​ эксель округляет до​.​​ была «99»​Excel​ “234” представляет собой​ вниз, а при​

​ значимых разрядов. Вы​​ типа данных.​

​ несколько способов округления​​ Excel» тут.​ таблицу, смотрите в​ тем способом, (например,​ такой он -​: Я так понимаю,​​Влад​ ? Если от​

​ руками, можно предварительно​​ 5444414644444480 как сделать​Excel округляет по​.​, смотрите в статье​

​ значимые разряды.​​ применении функции​ можете поэкспериментировать с​

​В поле​​ чисел.​Третий способ​ статье «Как выделить​ форматом), то результат​ Excel.​ цена с НДС​: А можно Ваш​ 15 и выше​ задать ячейкам формат​ чтобы число осталось​ законам математики –​Нужно округлить числа​ «Округление в Excel».​

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

​А что у​​ = 10​ файл глянуть?​​ знаков, то см.​​ – текстовый.​ тем же, без​​ числа до 5​ так, чтобы последние​ Здесь рассмотрим не​ значение до кратного​​— вверх.​ подставить собственные числа​введите требуемое число​​ требуется изменить.​​Нужно удалить формулу​ таблицу, др.».​ быть не верный.​ тебя за число?​Округляйте цену без​ostrik​​ пост #4, если​Awindelf​ сообщений об ошибках?​ в меньшую сторону,​ две цифры в​ стандартное округление чисел.​​ заданному числу. Например,​Функция​

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

​ округления, если она​​Второй способ​ Подробнее о таких​

​ Поди, код какой-нибудь?​​ НДС (=ОКРУГЛ(10/1,2;2) =​: JayBhagavan, пробовали -​

​ меньше назначьте ячейкам​​: Спасибо за помощь. ​​ Спасибо.​ числа больше 5​

​ числе были «99».​​Первый вариант​ допустим, что компания​ОКРУГЛ​

​ получить число с​​Используйте функцию ОКРУГЛВВЕРХ. В​ больше или меньше​

​ есть в ячейках.​​.​ деталях округления, смотрите​ Тогда сделай формат​ 8,33​ это не то​

​ формат Числовой​​ankon85​EducatedFool​ в большую сторону.​В ячейке С7​.​ поставляет товары в​округляет дробные числа​ нужным количеством значимых​ некоторых случаях может​ знаков, на вкладке​ Для точного округления​Как отменить округление в​

​ в статье «Округление​​ у нужных ячеек​Сумма без НДС​JayBhagavan​​ostrik​: У меня похожая​: Зачем вам такие​​Здесь рассмотрим, как​ пишем такую формулу.​Округление в​​ ящиках по 18​ следующим образом: если​ разрядов.​ потребоваться использовать функции​​Главная​ чисел, в ячейки​Excel​

​ в Excel».​​ “текстовый” и вводи​

Округление в excel с помощью функции

Именно использование функции позволит решить практически все задачи с округлением числа. Использовать мы будем функцию «ОКРУГЛ».

Формула округления будет иметь следующий вид:

=ОКРУГЛ(число или ячейка с числом;число_разрядов).

Простое округление

Во второй части формулы нужно указать число разрядов. В качестве примера возьмем число 15,185 и укажем 1 разряд =ОКРУГЛ(153,185;1). В результате мы получим округление до 15,2.

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

Если в формуле указать число разрядов 0, то в результате получим округление до ближайшего целого числа. Пример: =ОКРУГЛ(153,185;1) в результате выдаст 153.

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

Изначально функция ОКРУГЛ работает на основании математического правила:

  • Если после запятой число от 0 до 4, то округление производиться в меньшую сторону;
  • Если же после запятой число от 5 до 9, то округление производиться в большую сторону;

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

  • =ОКРУГЛВВЕРХ(153,185;0) выдаст число 154;
  • =ОКРУГЛВНИЗ(153,185;0) даст результат 153.

Округление с заданной кратностью

Иногда требуется округлить число с определённой кратностью. Например, нужно округлить 3,785 так, что бы результат был кратен 0,5. Для этого используется функция ОКРУГЛТ. Пример: =ОКРУГЛТ(3,785;0,5) выдаст результат 4.

Установка точности расчетов

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

Для начала посчитаем сумму значений во всех наших ячейках.

  1. Для этого добавим новое поле и перейдем в него. Затем кликнем на иконку «Fx» (вызов окна вставки функции).
  1. В появившемся окне выберите категорию «Математические».
  1. Найдите в списке функцию «СУММ». Для вставки нажмите на кнопку «OK».
  1. Появится окно, в котором нужно вставить аргументы функции. Для этого достаточно вручную выделить все ячейки. В результате этого они автоматически подставятся в нужное поле.
  1. В процессе выделения вы увидите пунктирную рамку. При этом большое окошко исчезнет. Это сделано для удобства выбора диапазона ячеек, поскольку таблица может быть огромных размеров.
  1. Как только вы отпустите палец, всё вернется обратно. При этом в поле «Число 1» будет указан нужный диапазон, а не то, что стояло по умолчанию. Для сохранения данных нажмите на кнопку «OK».

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

Можете проверить при помощи калькулятора. У вас будут расхождения.

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

Для того чтобы в формуле «СУММ» происходил расчет того, что отображается, нужно сделать следующие действия.

  1. Нажмите на пункт меню «Файл».
  1. Перейдите в раздел «Параметры».
  1. Кликните на пункт «Дополнительно». После этого прокрутите окно практически до самого низа.
  1. Найдите группу настроек «При пересчете этой книги». Поставьте галочку около пункта «Задать указанную точность» (если в будущем вам эта настройка окажется ненужной, достаточно будет убрать эту галочку).
  1. Кликните на кнопку «OK». Сразу после этого вы увидите предупреждение, в котором вам сообщат, что данные в таблице будут изменены в меньшую сторону. Необходимо согласиться с этим условием нажатием на «OK».
  1. Только после этого вы сможете сохранить внесенные изменения.
  1. Снова посмотрите результат подсчетов. Теперь он изменился и соответствует сумме отображаемых чисел.

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

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

Функционал Microsoft Excel огромен, и одной из главных возможностей программы является работа с числовыми данными. Иногда во время арифметических операций или при работе с дробями программа эти числа округляет. С одной стороны, это практично, поскольку в подавляющем большинстве случаев высокая точность расчетов не нужна, и множество лишних знаков только занимает дополнительное место на экране. Кроме того, есть числа, дробная часть которых бесконечна, поэтому для отображения на экране приходится их немного сокращать. С другой – бывают расчеты, где точность сохранять просто необходимо, а округление приводит к неприятным последствиям.

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

ОКРУГЛТ

Довольно полезная функция ОКРУГЛТ. С ее помощью можно добиться округления не только до нужного разряда (слева или справа от запятой), но и вообще до нужной точности (кратности). К примеру, нужно рассчитать заказ изделий в штуках, но так, чтобы он был равен целому количеству ящиков. Если в ящике 6 шт, то заказываемое количество должно быть кратно шести. Или другой пример. Нужно, чтобы расчетная цена заканчивалась либо на 00, либо на 50, то есть имела кратность 50 рублей. Функция ОКРУГЛТ легко решает такие задачи.

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

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

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

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

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

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

Настроить отображение: только поверхностно округлить в Excel

Вы также можете отформатировать ячейки в Excel для отображения только определенного количества десятичных знаков. При щелчке правой кнопкой мыши в соответствующем месте появляется контекстное меню, в котором вы выбираете « Форматировать ячейки . ». На вкладке « Числа» и категории « Числа» у вас есть возможность указать желаемое количество десятичных знаков. Например, если вы введете 0, отображаются только целые числа. Отличие от функции ОКРУГЛИТЬ заключается в том, что округление путем форматирования влияет только на отображение. Вы увидите округленное число на своем рабочем листе, но правильное значение, включая десятичные разряды, сохраняется на фоне ячейки.

Разницу можно увидеть не позднее, когда вы продолжите вычисления с числами: если вы сохранили значения 2,4, 2,3 и 3,3 в отформатированных ячейках без десятичных разрядов, вы увидите только 2, 2 и 3. Теперь создайте В Excel 8 будет отображаться сумма этих значений, поскольку программа по-прежнему учитывает десятичные разряды. Если бы вы работали с функцией ОКРУГЛИТЬ, результат был бы 7.

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

ОКРВВЕРХ.МАТ и ОКРВНИЗ.МАТ

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

Это были основные формулы округления в Excel. Но есть еще парочку, которые используются реже.

Функция ЦЕЛОЕ в Excel отбрасывает дробную часть и таким образом округляет вниз до ближайшего целого. Будет полезна при расчете целого количество лет между датами (возраст человека).

Функции ЧЁТН и НЕЧЁТ округляют числа до ближайшего четного или нечетного значения соответственно. 

Видеоуроки ниже показываю основные приемы округления, а также некоторые трюки. 

3  Функции округления в Excel3 Функции округления в Excel3  Функции округления в Excel3 Функции округления в ExcelТрюк Excel 11. Округление чисел в Excel, как на экранеТрюк Excel 11. Округление чисел в Excel, как на экранеТрюк Excel 20. Округление до первых значащих цифрТрюк Excel 20. Округление до первых значащих цифр

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

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

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

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

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

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

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

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

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

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

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

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

  • число,
  • формула,
  • диапазон ячеек.

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

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

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

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

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

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

Оцените статью
Рейтинг автора
5
Материал подготовил
Илья Коршунов
Наш эксперт
Написано статей
134
Добавить комментарий