Ошибки Excel при округлении и введении данных в ячейки
Excel старается облегчить наш труд разными подсказками и авто-подстановками. Если все это выполняется вместе с нашими ожиданиями мы в восторге от этой программы. Но если авто-подстановка является ошибочной мы просто выходим из себя.
Например, ошибки при округлении дробных чисел и т.п. Давайте разберемся почему часто возникают ошибки при скруглениях чисел? Ведь данная операция играет очень важную роль в процессе формирования цен и расчетов. Поэтому лучше как можно раньше разобраться в данной особенности программы.
Ошибки при округлении дробных чисел
Как правильно округлить и суммировать числа в Excel?
Для наглядного примера выявления подобного рода ошибок ведите дробные числа 1,3 и 1,4 так как показано на рисунке, а под ними формулу для суммирования и вычисления результата.
Дальше нужно выделить все числа и формулу, чтобы уменьшить разрядность для округления.
Как видите, в результате получаем абсурд: 1+1=3. Никакие форматы здесь не помогут. Решить данный вопрос поможет только функция «ОКРУГЛ». Запишите формулу с функцией так: =ОКРУГЛ(A1;0)+ОКРУГЛ(A2;0)
Как правильно округлить и суммировать числа в столбце таблицы Excel? Если этих чисел будет целый столбец, то для быстрого получения точных расчетов следует использовать массив функций. Для этого мы введем такую формулу: =СУММ(ОКРУГЛ(A1:A7;0)). После ввода массива функций следует нажимать не «Enter», а комбинацию клавиш Ctrl+Shifi+Enter. В результате Excel сам подставит фигурные скобки «{}» – это значит, что функция выполняется в массиве. Результат вычисления массива функций на картинке:
Можно пойти еще более рискованным путем, но его применять крайне не рекомендуется! Можно заставить Excel изменять содержимое ячейки в зависимости от ее формата. Для этого следует зайти «Файл»-«Параметры»-«Дополнительно» и в разделе «При пересчете этой книги:» указать «задать точность как на экране». Появиться предупреждение: «Данные будут изменены — точность будет понижена!»
Внимание!!! Работая в таком режиме, изменив один раз количество разрядов, уже нельзя вернуть исходные данные обратно. Это путь только в одну сторону. Поэтому лучше его не использовать. А массив функций прекрасно и безопасно справляется с данной задачей.
Здесь мы забегаем вперед, но иногда без этого сложно объяснить решение важных задач.
Пишем в Excel слово ИСТИНА или ЛОЖЬ как текст
Заставить программу воспринимать слова логических типов данных как текст можно двумя способами:- Вначале вписать форматирующий символ.
- Изменить формат ячейки на текстовый.
В ячейке А1 реализуем первый способ, а в А2 – второй.
Задание 1. В ячейку А1 введите слово с форматирующим символом так: «’истина». Обязательно следует поставить в начале слова символ апострофа «’», который можно ввести с английской раскладки клавиатуры (в русской раскладке символа апострофа нет). Тогда Excel скроет первый символ и будет воспринимать слова «истина» как текст, а не логический тип данных.
Задание 2. Перейдите на ячейку А2 и вызовите диалоговое окно «Формат ячеек». Например, с помощью комбинации клавиш CTRL+1 или контекстным меню правой кнопкой мышки. На вкладке «Число» в списке числовых форматов выберите «текстовый» и нажмите ОК. После чего введите в ячейку А2 слово «истина».
Задание 3. Для сравнения напишите тоже слово в ячейке А3 без апострофа и изменений форматов.
Как видите, апостроф виден только в строке формул.
Примечание. В ячейках А1 и А2 символы текста не были сменены на большие, а значит не была выполнена авто-подстановка, потому что слово воспринято программой как текст.
Отображение формул
Заполните данными ячейки, так как показано ниже на рисунке:
Обычно комбинация символов набранных в B3 должна быть воспринята как формула и автоматически выполнен расчет. Но что если нам нужно записать текст именно таким способом и мы не желаем вычислять результат? Решить данную задачу можно аналогичным способом из примера описанного выше.
все уроки
Как округлить число в Excel в большую и меньшую сторону
17. 03.2021
Автор: CHIP
1 звезда2 звезды3 звезды4 звезды5 звезд
Как работает округление чисел в Эксель: принципы и настройки
4
5
1
2
Чтобы упростить расчеты, можно самостоятельно настроить формат чисел в Excel: для этого существует целых 3 основных способа и множество разных вариантов получения округленных чисел.Выполняя деление или вычисления с дробями, числа в программе Эксель автоматически округляются. Слишком высокая точность на самом деле редко бывает необходима для получения нужного результата. Кроме того, расчеты с большим количеством знаков после запятой отличаются высокой сложностью и увеличивают нагрузку на ПК. Хотя можно столкнуться с операциями, когда точность имеет серьезное значение — или, наоборот, вообще не нужна.
Принципы округления в Excel
Числа, которыми оперирует при расчетах и выполнении других действий табличный редактор Excel, могут быть приближенными или точными. Число хранящихся в памяти разрядов достигает 15, а в программе отображаются знаки с указанной пользователем точностью. Хотя все формулы по умолчанию рассчитываются с учетом настоящих, а не округленных значений.
Принципы округления зависят от выбранного способа. Но в большинстве случаев применяется стандартный: числа от 5 до 9 округляются в большую сторону, а от 1 до 4 — в меньшую. Так, например, при округлении в Эксель до целого 1,4 превратится в 1, а 1,5 — уже в 2. Тогда как специальные функции позволяют выбирать и другие варианты округления, на которые влияют и разрядность, и знак, и даже то, являются числа целыми или дробными.
Способ 1. Иконки на ленте
Проще и быстрее всего округлять в Excel, пользуясь специально предназначенными для этого иконками. Они находятся в разделе чисел и выглядят как три ноля с точками и стрелкой, направленной в соответствии со стороной, в которую нужно изменить разрядность.
Влево — число разрядов будет увеличено, и, например, 3,777 превратится в 3,7770. Стрелка вправо при нажатии уберет 1 разряд — и вместо 3,777 в ячейке останется округленное значение 3,78.
Стоит отметить, что при расчетах для программы не будет иметь значения, сколько разрядов отображается в ячейке. Эксель посчитает все с точностью до 15-й цифры. И при умножении округленного числа 2,777, например, на 2, результатом станет не 7,56, а 7,5554.
Способ 2. Изменение формата
При округлении в Эксель есть возможность настройки разрядности при изменении формата ячеек. Причем, доступна она и для таблицы в целом, и для выбранного диапазона (для любого количества строк или столбцов, прямоугольной или даже произвольной по форме области).
Для этого необходимо выделение ячеек, в которых округляется отображение чисел, и перейти к пункту формата в меню, выпадающем при нажатии правой кнопки мышки.
Когда откроется окно с настройкой формата, необходимо перейти к вкладке чисел и выбрать «числовой», если он не был установлен по умолчанию. Если указаны другие параметры, например, «финансовый», «дата» или «общий», округлить значение в ячейках не получится.
Выбрав правильный формат, следует указать в правой части окна, сколько десятичных знаков будет показываться. Так, выбрав 0, можно получить из 3,777 целое число 4, а указав 2 — 3,78. Правда, при вычислениях, опять же, будет применяться настоящее значение, а не то, которое увидит на экране пользователь.
Чтобы изменить ситуацию и обеспечить использование в формулах тех чисел, которые отображаются на экране, пользователю придется настроить точность выполнения расчетов. Для этого придется выполнить такие действия:
- Перейти к первому пункту верхнего меню — «Файл».
- Выбрать расположенный в самом низу раздел параметров.
- В появившемся на экране окне перейти к подразделу «Дополнительно» и найти здесь блок с названием «При пересчете этой книги».
- Поставить галочку напротив второго пункта, выбрав «указанную точность», после чего на экране появится сообщение, подтверждающее операцию.
- Нажать «ОК».
После выполнения этих действий процесс округления в Excel изменится. И учитываться будет те величины, которые выводятся на экран, а не данные из памяти программы. Поэтому, настраивая отображение, можно рассчитывать, что в формуле округления в Эксель будут учтены результаты округления. А, значит, точность расчетов станет такой, какой она нужна пользователю.
Способ 3. Специальные функции
Кроме двух способов как округлить число в Excel, подходящих только для одного числа, есть методики, позволяющие пользоваться округленными величинами в расчетах. Для этого в список возможностей программы включены такие функции округления в Excel:
- ОКРУГЛ – для получения округленного числа с указанным количеством знаков после запятой. Так, при вводе ОКРУГЛ(3,777;2), где «2» это число разрядов, получится формула округления в Excel до двух знаков, а результатом станет 3,78.
- ОКРУГЛТ – округление числа с заданной точностью. Указав ОКРУГЛТ(3,774;4) можно получить 4, а ОКРУГЛТ(3,774;5) — уже 5, то есть ближайшее число с нужной кратностью.
- ОКРУГЛВВЕРХ – функция, которая округляет в ближайшую большую сторону вверх по модулю. Указав в формуле (3,774;2), пользователь получит 3,78, а (-3,774;2) — значение -3,78.
- ОКРУГЛВНИЗ – округление до ближайшего меньшего по модулю числа. В этом случае 3,774 при выборе 2 десятичных знаков округляется до 3,77.
- ОКРВВЕРХ – округление с нужной точностью, но уже не по математическим правилам, а к верхнему значению. Поэтому ОКРВВЕРХ(3,774;8) окажется восьмеркой.
- ОКРВНИЗ – аналог предыдущей функции, но числа округляются до меньшего кратного.
- ОКРВВЕРХ.МАТ обеспечивает в Эксель округление в большую сторону. Но с изменением синтаксиса формул, где сначала идет аргумент, потом — кратность, а в конце — направление округления отрицательного числа.
ОКРВВЕРХ.МАТ(-7,24) дает в результате -7, ОКРВВЕРХ.МАТ(-7,24;3) обеспечит -6, а ОКРВВЕРХ.МАТ(-7,24;3;1) — как на скриншоте, -9.
- ОКРВНИЗ.МАТ — аналог предыдущей функции, но с округлением в меньшую сторону.
Для получения формулы округления в Excel до целого числа применяются еще три функции. ОТБР просто округляет данные до целого числа. ЧЕТН округляет в сторону ближайшего четного числа. А НЕЧЕТН дает возможность округлить данные до нечетных чисел.
Эксплуатация
8 бесплатных альтернатив Microsoft Office: офис без затрат
Несколько способов ввода формул
Вводить функцию округления можно и обычным способом, вводя текст после знака «=». Но есть и другая возможность, которая отлично подходит для таблицы со значениями, которые нужно округлять в отдельном столбце.
Для этого нужно перейти на вкладку «Формулы» и на вкладке библиотеки функций нажать по иконке «Математические». Здесь следует выбрать подходящий для округления вариант — например, ОКРУГЛВНИЗ или ОКРУГЛВВЕРХ.
На экране появится небольшое окно для ввода аргументов. В поле числа следует ввести или округляемую величину, или указать, в какой ячейке оно находится. После этого можно поставить то количество знаков после запятой, до которого округляется число. Ставить значение разрядности следует в поле разрядов.
Число в ячейке округлилось. Но для того, чтобы сделать то же самое с целым столбцом или прямоугольной областью, следует скопировать формулу. Для этого необходимо навести курсор на нижний правый угол ячейки, нажать и протянуть в нужном направлении. После этого округленными окажутся все значения.
Другие инструкции по работе в Excel:
-
Как работает функция ЕСЛИ в Excel: примеры использования
-
5 способов замены запятых на точки в Excel
-
Как в Эксель пронумеровать строки: 3 способа
Автор
CHIP
Была ли статья интересна?
Поделиться ссылкой
Нажимая на кнопку «Подписаться»,
Вы даете согласие на обработку персональных данных
Рекомендуем
Обзор смартфона HONOR Magic Vs: все складывается, как надо
Аккумуляторная пила: плюсы и минусы, рейтинг лучших
Магия цифр: как скручивают пробег автомобиля и как узнать реальные цифры
Четыре с половиной проблемы электромобилей: анализируем личный опыт в Норвегии
Какая видеокарта нужна для игр? Топ-3 варианта в бюджетном сегменте
Топ-10 лучших вертикальных пылесосов: рейтинг 2023
Система ABS в автомобиле: сокращает ли она тормозной путь?
Не включается телефон: почему это происходит и что делать
Лучшие игры про Супер Марио: топ представителей серии, в которые можно сыграть сегодня
Реклама на CHIP Контакты
Как установить точность округления в Excel
В этом руководстве объясняется, как можно установить точность округления числовых значений в Excel.
Установка точности округления вашего листа позволяет удалить любые ненужные десятичные значения. Это будет полезно для предотвращения ошибок точности.
Оглавление
- Реальный пример настройки точности округления в Excel
- Как настроить точность округления в Excel
- Часто задаваемые вопросы (FAQ)
При работе с десятичными значениями в Excel пользователи могут добавлять форматирование к ячейкам только для отображения определенного количества значений. Однако пользователи могут захотеть установить эти значения с точностью, указанной текущим форматированием.
Давайте рассмотрим быстрый пример, где мы можем применить точность округления для десятичных значений. =
У вас есть лист, который содержит различные математические константы. Ячейка, содержащая π, может иметь десятичное значение 3.1415926535 , но отображается в электронной таблице как 3.142 . Несмотря на то, что отображаются только три десятичных знака, фактическое значение все еще остается.
Excel включает возможность установки точности значений, отображаемых на листе. Выполнение этого действия навсегда потеряет точность значений и гарантирует, что фактическое значение ячейки равно отображаемому значению.
Этот вариант использования — лишь одна из причин, по которой мы можем захотеть установить точность округления в Excel. Любой рабочий лист, содержащий очень точные десятичные числа, может привести к ошибкам округления с плавающей запятой. Пользователи могут столкнуться с потерей точности при использовании очень больших или очень маленьких чисел.
Теперь, когда мы знаем, когда использовать этот параметр точности, давайте посмотрим, как мы можем использовать его в реальной электронной таблице.
Реальный пример настройки точности округления в Excel
Давайте рассмотрим реальный пример электронной таблицы, в которой точность округления задается для предотвращения ошибки с плавающей запятой.
В приведенном ниже примере мы установили точность ячеек в соответствии с точностью, указанной форматированием ячейки. Перед применением настройки в ячейках столбца B отображается до двух знаков после запятой. Однако сами значения могут иметь точность выше двух знаков после запятой.
Этот параметр влияет на активную книгу и все рабочие листы под ней. Этот параметр нельзя отменить, поэтому пользователю рекомендуется сохранить свою книгу перед использованием этого параметра.
Вы можете сделать свою собственную копию электронной таблицы, показанной выше, используя ссылку, прикрепленную ниже.
Сделайте копию примера электронной таблицы
Если вы готовы попробовать наш метод настройки точности в Excel, прочитайте следующий раздел, чтобы узнать, как это сделать.
Как настроить точность округления в Excel
В этом разделе описаны все шаги, необходимые для настройки точности округления в электронной таблице Excel. Вы узнаете, как мы можем использовать расширенный параметр Excel для установки точности, отображаемой на листе.
Чтобы установить точность округления в Excel, выполните следующие действия:
- Во-первых, убедитесь, что все ячейки электронной таблицы отображаются с заданной точностью. В приведенном ниже примере ячейка B2 имеет десятичную дробь с точностью до четырех знаков после запятой. Ячейка, однако, имеет форматирование, которое округляет значение до двух знаков после запятой.
- Когда все ячейки будут отформатированы с требуемым уровнем точности, перейдите на вкладку File .
- На вкладке File щелкните Options , чтобы открыть новое диалоговое окно.
- Перейдите в меню Advanced и найдите раздел с надписью «При расчете этой книги». Установите флажок «Установить точность, как показано».
- Excel предупредит вас о том, что данные вашей электронной таблицы навсегда потеряют точность. Если вас это устраивает, нажмите OK .
- Теперь на вашем листе должны быть десятичные значения, отражающие точность, указанную в форматировании ячейки.
- Каждый раз, когда пользователь настраивает форматирование ячейки для отображения более низкой точности, базовое значение ячейки также будет меняться. В приведенном ниже примере мы нажали на опцию уменьшения десятичных знаков, чтобы полностью удалить десятичные знаки. Поскольку мы изменили настройки электронной таблицы, значения ячеек стали целыми.
Часто задаваемые вопросы (FAQ)
- Может ли установка отображаемой точности привести к неточностям??
Настройка уровня точности для значений приводит к суммированию. Ваша электронная таблица может давать неточные данные в зависимости от того, как электронная таблица использует эти значения. - Что вызывает ошибки с плавающей запятой?
Числа, которые отображаются точно в десятичном формате, могут нуждаться в аппроксимации при преобразовании в двоичные числа с плавающей запятой. Каждый раз, когда число преобразуется в двоичное, ожидается некоторая потеря точности.
После вычисления между двумя или более десятичными значениями Excel округляет значение до некоторой степени. Например, вычисление уравнения =1*(0,5-0,4-0,1) возвращает значение -2,78E-17, а не 0, .
- Может ли установка отображаемой точности привести к неточностям??
Это пошаговое руководство должно быть всем, что вам нужно, чтобы самостоятельно установить точность округления в Excel. Мы показали вам, как легко настроить рабочий лист, который корректирует фактическое значение ячеек по мере его отображения пользователю.
Установка десятичной точности — это лишь один пример расширенного параметра, который вы можете найти в Excel. Имея так много других доступных функций Excel, вы обязательно найдете ту, которая подойдет именно вам.
Хотите узнать больше о возможностях Excel? Подпишитесь на нашу рассылку, чтобы получать от нас последние руководства и учебные пособия по Excel.
Получайте от нас электронные письма об Excel.
Наша цель в этом году — создать множество содержательных, коротких руководств для таких пользователей Excel, как вы. Если вам понравился этот, вам понравится то, над чем мы работаем! Читатели получают ✨ ранний доступ ✨ к новому контенту.
Дейон Менор
Привет! Я разработчик программного обеспечения со страстью к анализу данных. Google Таблицы помогли мне расширить возможности моих команд для принятия решений на основе данных. Всегда есть что-то новое, что можно узнать, так что давайте посмотрим, как вы можете сделать жизнь намного проще с помощью электронных таблиц!
Постоянное включение Set Precision As Displayed (Microsoft Excel)
Автор: Allen Wyatt (последнее обновление: 30 мая 2020 г.)
Этот совет относится к Excel 2007, 2010, 2013, 2016, 2019 и Excel в Microsoft 365
Тэми предпочитает, чтобы для всех ее книг была включена функция «Установить отображаемую точность». Она ведет расчет заработной платы и работает с долларами для всех расчетов. Когда этот параметр не действует, и она вручную использует калькулятор, чтобы перепроверить математику на листе, она часто отличается на несколько центов. Единственное решение, если настройка включена; тогда все совпадает. Делать это с каждой отдельной книгой, которую создает Тэми, довольно проблематично, поэтому она надеется, что есть способ включить этот параметр на постоянной основе.
Невозможно включить его постоянно в самом Excel, но вы можете сделать это с помощью макросов. Например, вы можете добавить в книгу следующий макрос:
. Частная подпрограмма Workbook_Open() ThisWorkbook.PrecisionAsDisplayed = True Конец сабвуфера
Макрос следует поместить в модуль Эта рабочая книга, чтобы он выполнялся каждый раз при открытии рабочей книги. При желании вы также можете добавить это в новую пустую книгу, а затем сохранить эту книгу как шаблон с именем Book.xltm в папке XLStart. Это указывает для Excel шаблон по умолчанию, который вы хотите использовать при создании новой книги. Таким образом, все новые рабочие книги будут включать этот простой однострочный макрос для установки точности.
При этом многие люди настоятельно не рекомендуют даже включать параметр «Установить точность как отображаемую». Почему? Потому что это постоянно влияет на данные в вашей книге. Например, попробуйте это небольшое упражнение:
- Установите отображение ячейки на листе с 5 знаками после запятой. (Допустим, вы делаете это, используя ячейку B3.)
- Убедитесь, что параметр «Установить отображаемую точность» включен.
- Введите значение 1,23456 в ячейку B3.
- Теперь измените отображение ячейки B3 на 2 знака после запятой. Теперь он должен показывать 1,23.
- Снова измените отображение ячейки B3 обратно на 5 знаков после запятой. Теперь он должен показывать 1.23000.
Эти последние цифры (456) теперь утеряны навсегда; они не могут быть восстановлены. Возможно, будет лучше настроить ваши формулы, включив в них функцию ОКРУГЛ, чтобы округление до 2 знаков после запятой выполнялось только в тех случаях, когда вам это нужно, а не везде.
Если вы все же решите использовать Set Precision As Displayed, возможно, вам лучше настроить простой способ изменения параметра для каждой книги отдельно. Это можно сделать с помощью одного макроса, добавленного в вашу личную книгу:
Sub TogglePrecision() Dim sTemp As String sTemp = "Отображаемая точность была " С ActiveWorkbook Если .PrecisionAsDisplayed Тогда .PrecisionAsDisplayed = Ложь sTemp = sTemp & "ОТКЛЮЧЕНО" Еще .PrecisionAsDisplayed = Истина sTemp = sTemp & "ВКЛЮЧЕНО" Конец, если Конец с MsgBox sTemp Конец сабвуфера
Теперь вы можете добавить макрос на панель быстрого доступа и щелкнуть его, чтобы включить или выключить настройку. Макрос просто переключает настройку с того, на что она установлена в данный момент, а затем отображает сообщение, указывающее на недавно измененное состояние настройки.
Примечание:
Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице ExcelTips сайтов), я подготовил специальную страницу, содержащую полезную информацию. . Нажмите здесь, чтобы открыть эту специальную страницу в новой вкладке браузера.
ExcelTips — ваш источник экономичного обучения работе с Microsoft Excel. Этот совет (13765) относится к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Microsoft 365.
Биография автора
Allen Wyatt
На его счету более 50 научно-популярных книг и множество журнальных статей. Аллен Вятт является всемирно признанным автором. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Allen…
Excel Smarts для начинающих! Это популярное руководство, выполненное в удобном и надежном стиле для чайников , показывает новичкам, как приступить к работе с Excel, а также помогает более опытным пользователям освоиться с новейшими функциями. Выезд Excel 2013 Для чайников уже сегодня!
Подписаться
БЕСПЛАТНАЯ СЛУЖБА: Получайте такие советы каждую неделю в ExcelTips, бесплатном информационном бюллетене по продуктивности.