101 функция excel

Настройка округления как на экране

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

Включать точность как на экране, нужно в ситуациях следующего плана. Например, у вас стоит задача сложить два числа 4,41 и 4,34, но обязательным условиям является то, чтобы на листе отображался только один десятичный знак после запятой. После того, как мы произвели соответствующее форматирование ячеек, на листе стали отображаться значения 4,4 и 4,3, но при их сложении программа выводит в качестве результата в ячейку не число 4,7, а значение 4,8.

Это как раз связано с тем, что реально для расчета Эксель продолжает брать числа 4,41 и 4,34. После проведения вычисления получается результат 4,75. Но, так как мы задали в форматировании отображение чисел только с одним десятичным знаком, то производится округление и в ячейку выводится число 4,8. Поэтому создается видимость того, что программа допустила ошибку (хотя это и не так). Но на распечатанном листе такое выражение 4,4+4,3=8,8 будет ошибкой. Поэтому в данном случае вполне рациональным выходом будет включить настройку точности как на экране. Тогда Эксель будет производить расчет не учитывая те числа, которые программа держит в памяти, а согласно отображаемым в ячейке значениям.

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

Включение настройки точности как на экране в современных версиях Excel

Теперь давайте выясним, как включить точность как на экране. Сначала рассмотрим, как это сделать на примере программы Microsoft Excel 2010 и ее более поздних версий. У них этот компонент включается одинаково. А потом узнаем, как запустить точность как на экране в Excel 2007 и в Excel 2003.

1. Перемещаемся во вкладку «Файл».

Запускается дополнительное окно параметров. Перемещаемся в нем в раздел «Дополнительно», наименование которого значится в перечне в левой части окна.

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

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

После этого в программе Excel 2010 и выше будет включен режим «точность как на экране».

Для отключения данного режима нужно снять галочку в окне параметров около настройки «Задать точность как на экране», потом щелкнуть по кнопке «OK» внизу окна.

Включение настройки точности как на экране в Excel 2007 и Excel 2003

Теперь давайте вкратце рассмотрим, как включается режим точности как на экране в Excel 2007 и в Excel 2003. Данные версии хотя и считаются уже устаревшими, но, тем не менее, их используют относительно немало пользователей.

Прежде всего, рассмотрим, как включить режим в Excel 2007.

1. Жмем на символ Microsoft Office в левом верхнем углу окна. В появившемся списке выбираем пункт «Параметры Excel».
2. В открывшемся окне выбираем пункт «Дополнительно». В правой части окна в группе настроек «При пересчете этой книги» устанавливаем галочку около параметра «Задать точность как на экране».

Режим точности как на экране будет включен.

В версии Excel 2003 процедура включения нужного нам режима отличается ещё больше.

1. В горизонтальном меню кликаем по пункту «Сервис». В открывшемся списке выбираем позицию «Параметры».
2. Запускается окно параметров. В нем переходим во вкладку «Вычисления». Далее устанавливаем галочку около пункта «Точность как на экране» и жмем на кнопку «OK» внизу окна.

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

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

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

Особенности округления чисел Excel

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

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

Округление с помощью кнопок на ленте

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

При нажатии кнопки «Увеличить битовую глубину» количество введенных десятичных знаков увеличивается на единицу.

Следовательно, кнопка «Уменьшить разрядность» уменьшается на одно число цифр после десятичной точки.

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

Вы также можете установить округление, используя настройки формата ячейки. Для этого выделите диапазон ячеек на листе, щелкните правой кнопкой мыши и в появившемся меню выберите «Форматировать ячейки».

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

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

Если в предыдущих случаях заданные параметры влияли только на внешнее отображение данных, а в расчетах использовались более точные индикаторы (до 15 знаков), то теперь мы расскажем, как изменить точность расчетов.

Перейдите на вкладку «Файл», оттуда — в раздел «Параметры».

Откроется окно параметров Excel. В этом окне перейдите в подраздел «Дополнительно». Найдите блок настроек «При пересчете книги». Настройки в этом блоке применяются не к листу, а ко всей книге, то есть ко всему файлу. Установите флажок рядом с «Установить точность, как на экране» и нажмите «ОК».

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

Отображение длинных формул

Пусть в ячейке имеется длинная формула: =+ЕСЛИ($A2=0;0; ЕСЛИ ($B2 =4264;$B2 =4895;$B2 32760;($B2*0,128-1623,49)*$C2/365)))))

В Строке формул такую громоздкую формулу можно отобразить полностью (см. выше), но ее трудно воспринимать. Гораздо удобнее работать с формулой в таком виде: =+ЕСЛИ($A2=0;0; ЕСЛИ($B2 =4264;$B2 =4895;$B2 32760;($B2*0,128-1623,49)*$C2/365)))))

Привести формулу в Строке формул к такому виду можно вставив перед функциями ЕСЛИ() символ Разрыв строки (символ Разрыв строки не влияет на вычисления). Символ Разрыв строки можно вставить, нажимая комбинацию клавиш ALT+ENTER .

Как округлить число в Excel?

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

Форматирование, количество знаков после запятой

Любое введенное в ячейку числовое значение отображается в формате «Общий» (Главное меню или формат ячеек). В случае форматирования числа, оно отображает определённое количество знаков после запятой, которые можно настроить ( ctrl+1 , формат ячейки). Т.е. вы можете задать любое количество знаков после запятой при помощи форматирования (при этом само число в ячейке не изменится — изменится отображение).

Функции округления ОКРУГЛ(), ОКРУГЛВВЕРХ(), ОКРУГЛВНИЗ()

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

Интересная функция = ОКРУГЛТ (128;6), чтобы округлить число «127» к кратному «6» в строке формул необходимо прописать: = ОКРУГЛТ (128;6), в итоговой ячейке мы получим число «126».

Округление денежных значений

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

Для этого если ячейка В1 содержит числовой показатель 10,561 р. (этот формат можно задать нажав иконку с деньгами на второй картинке), чтобы привести значение к нужному (2 знака после запятой) достаточно в строке формул прописать: = ОКРУГЛ (В1;2), получим результат 10,56 р.

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

1. Округление в большую сторону, т.е. вверх: =ОКРВВЕРХ(В1;0,01), ячейка В1 получит значение 10,57 р., с округлением до следующей копейки (0,01) в большую сторону 2. Округление в меньшую сторону, вниз: =ОКРВНИЗ(В1;0,01), ячейка получит значение 10,56 р., с округлением до следующей копейки в меньшую сторону 3. А если к примеру округлить показатель до 10 копеек используйте формулу: =ОКРВВЕРХ(В2;0,10)

Преобразование в целое число

Для того, чтобы получить целое число в Excel , используют формулы =ЦЕЛОЕ() и = ОТБР() . На первый взгляд они могут показаться похожими, но это не так, особенно это хорошо видно на отрицательных числах. При использовании формулы с функцией ОТБР , удаляется только дробная часть числа.

Например, имеем число- 16,3543,формула: = ОТБР (-16,3543) преобразовывает значение в число -16, а формула: =ЦЕЛОЕ(-16,3543) выдаёт показатель -17, потому что, целое следующее число, идущее за «-16,3543» — это именно «-17».

Иногда функцию ОТБР используют, для усечения десятичных знаков формула:= ОТБР (16,3555555;2) даёт показатель «16,35».

Как округлить число в Excel вверх или вниз

Бывает так, что крупные цифровые значения необходимо округлить до определённого количества некоторых значимых цифр вверх или вниз. Для этого используем формулы с функцией ОКРВВЕРХ и ОКРВНИЗ. Например имеем число 164 358 находящееся в ячейки В1, формула: = ОКРУГЛВВЕРХ (В2;3-ДЛСТР(B1)), преобразует его в показатель «165000», Тройка в данной формуле это как раз значение, которое отвечает за количество знаков в преобразовании. Если мы заменим например её на «2» и напишем формулу = ОКРУГЛВНИЗ (В2;2-ДЛСТР(A1)), получим значение «160000».

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

Банковское округление

Очень часто в учетных программах, таких как 1С используется банковское округление, что говорит Википедия: Банковское округление (англ. banker’s rounding ) или бухгалтерское округление — округление здесь происходит к ближайшему четному (если число заканчивается на 5), то есть 2,5 → 2, 3,5 → 4. Для этого можно воспользоваться следующими функциями:

Округление до четного/нечетного

Функция =ЧЕТН() округляет до ближайшего четного целого. При этом положительные числа округляются в сторону увеличения, а отрицательные – к уменьшению

Функция =НЕЧЁТ() округляет число до ближайшего нечетного целого. Положительные числа округляются в сторону увеличения, а отрицательные – к уменьшению

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

На листе

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

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

   или Уменьшить разрядность

   .

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

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

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

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

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

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

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

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

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

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

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

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

Функции ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ

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

ОКРУГЛВВЕРХ

С помощью данной функции можно округлить числовое значение от 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.

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

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

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

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

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

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

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

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

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

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

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