Вопрос 16: Типы данных в ячейках. Форматы числовых данных. Точность числовых значений.

Типы данных в ячейках

Ячейки электронной таблицы, так же как и ячейки обычной таблицы, могут содержать обычный текст, числа, даты. Кроме этого, значения ячеек электронной таблицы могут быть зависимы от значений других ячеек. В частности, числа в ячейках могут вычисляться по значениям из других ячеек. Исходя из этого, каждая заполненная ячейка таблицы содержит один из следующих типов данных: текстовое значение; числовое значение; формулу. Распознавание типа данных при заполнении ячеек новой таблицы выполняется автоматически (см. ниже).

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

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

Форматы числовых данных.

При вводе в ячейку чисел или формул, возвращающих числа, Excel автоматически применяет к ячейке числовой формат Общий (General). Этот формат отображает введенные числовые значения практически в том же виде, в котором пользователь их ввел в рабочий лист. Существуют лишь некоторые исключения. ? Программа отбрасывает нули в десятичной части числа, например, при вводе 4,500 в ячейке будет находиться значение 4,5. ? Программа отбрасывает незначащие нули, например, при вводе 04567 в ячейке будет находиться значение 4567. ? Программа помещает нуль перед десятичной точкой в любой десятичной дроби, например, при вводе ,123 в ячейке будет находиться значение 0,123. * Если вводимое число содержит много знаков после запятой, программа отбрасывает часть десятичных знаков, которые не умещаются в ячейке.

Кроме того, очень большие значения преобразуются в экспотенциальный формат. Например, при вводе 7890123,45 в ячейке будет находиться значение 7890123, а при вводе 789012345,67 появится значение 7,89Е+08. Помните, что числовой формат Общий всегда можно переопределить и задать числовой формат, который наилучшим образом подходит для вводимых данных.

Например, чтобы задать денежный формат с двумя знаками после десятичной точки, введите в ячейку 2 500,00 р. При вводе данных в ячейку можно сразу задать иной, отличный от общего, числовой формат. Чтобы задать другой формат ячейке, содержащей формулу, необходимо выделить ячейку и использовать соответствующую кнопку на панели инструментов Форматирование либо вкладку Число (Number) диалогового окна Формат ячеек (Format cells). При назначении числового формата диапазону ячеек (в том числе и с помощью кнопок панели инструментов Форматирование) вы предписываете Excel применять к ячейке определенные коды формата. На рис. 2.9 приведено диалоговое окно Формат ячеек с открытой вкладкой Число. По умолчанию выбран формат Общий, в области Образец (Sample) находится содержимое активной ячейки, а ниже располагается текст, поясняющий, для чего используется этот формат. Если в списке Числовые форматы (Category) выбрать другой формат (например, числовой, денежный или процентный), в правой части диалогового окна появятся дополнительные списки и поля

Вы можете задать число десятичных знаков, обозначение, способ отображения отрицательных значений и т. д. При выборе формата Дата (Date), Время (Time), Дробный (Fraction) или Дополнительный (Custom) в правой части диалогового окна появляется список Тип (Туре), содержащий предопределенные типы данного формата, которые можно использовать для вывода данных в ячейке таблицы. Excel всегда пытается распознать формат вводимых данных. Например, если ввести в ячейку 3:00, а затем выбрать команду Формат=>Ячейки и перейти на вкладку Число, в списке Числовой формат будет выбран элемент Все форматы, а в списке Тип — ч:мм. Названия большинства категорий в списке Числовые форматы дают полное представление о типе формата. Однако стоит лишь перейти к категории Все форматы (Custom), и ситуация изменяется — в списке Тип сначала располагается слово Общий, а затем следуют непонятные наборы символов (рис. 2.11). Чем дальше вы пролистываете список Тип, тем более длинными становятся коды, тем больше в них встречается символов и разделителей. Оказывается, разобраться с ними довольно легко. Кроме того, они достаточно полезны, особенно если понять, что они означают и как их применять для создания собственных форматов.

Шаблоны числовых форматов Excel используют символы 0, ?, # и знаки препинания (запятая, знак доллара и процента). * Символ 0 применяется для обозначения десятичных разрядов (если такие имеются). Код 0,00 означает, что используется два знака после десятичной точки.А шаблон, состоящий из одного символа 0 указывает на то, что данный формат не имеет десятичных знаков (т. е. округляет значение до целого числа). ? Символ ? используется аналогично 0, но выравнивает значения по десятичному разделителю. Например, если выбран формат 0,??, значения 10,5 и 24,71 будут выровнены по десятичной точке, a Excel добавит пробел после цифры 5. Если при работе с данными значениями использовать формат 0,00, оба значения будут выровнены в ячейках по правому краю. ? Символ # используется в комбинации с разделителем тысяч, миллионов и т. д. ? Символ $ добавляется перед шаблоном числового формата, если необходимо, чтобы он предварял значение, использующее данный формат. * Символ % размещается в конце шаблона числового формата, если необходимо, чтобы Excel преобразовал число в проценты, умножив на 100 и поместив после числа знак %.

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

Рассмотрим, например, следующий числовой формат. # ##0_); (# ##0) Как вы видите, задан формат положительных (до точки с запятой) и отрицательных (после точки с запятой) числовых значений. Поскольку присутствует всего две группы кода, для нулевых значений используется первый шаблон, а к тексту не применяется специальное форматирование.

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

Точность числовых значений

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

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

Наиболее распространенный способ округления – математическое округление, когда число округляется в меньшую сторону, если в числе «отбрасываемая» цифра меньше пяти и округляется в большую сторону, если в числе «отбрасываемая» цифра больше либо равна пяти. Кроме математического округления существуют и другие способы, такие как округление к большему, округление к меньшему, округление к большему по модулю, округление к меньшему по модулю, случайное округление, чередующееся округление, ненулевое округление и банковское округление.

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

Изменение количества отображаемых знаков после запятой без изменения числового значения

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

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

Как видно из примера, некорректная настройка числового формата привела к тому, что 2+3=6, что неверно.

Округление числовых значений с помощью функций

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

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

=ОКРУГЛ(1,475;2) Округляет число 1,475 до двух десятичных разрядов, в результате получается 1,48;

Функция ОКРУГЛВВЕРХ(число;число_разрядов) округляет число с избытком до ближайшего большего по модулю

=ОКРУГЛВВЕРХ(3,14159;3) Округляет с избытком число 3,14159 до трех десятичных разрядов, результат 3,142;

Функция ОКРУГЛВНИЗ(число;число_разрядов) округляет число с недостатком до ближайшего меньшего по модулю;

=ОКРУГЛВНИЗ(3,14159) Округляет с недостатком число 3,14159 до трех десятичных разрядов, результат 3,141;

Функция ОКРУГЛТ(число;точность) округляет число до кратного заданному числу, в формуле ОКРУГЛТ параметр «точность» - это кратное, до которого нужно округлить результат;

=ОКРУГЛТ(10;3) Округляет число 10 до ближайшего числа, кратного 3, то есть до 9.

В некоторых версиях Excel функция ОКРУГЛТ отсутствует, для ее появления необходимо установить стандартную надстройку Excel – «Пакет анализа». Если функция ОКРУГЛТ есть в математических функциях, но возвращает ошибку #ИМЯ?, попробуйте установить стандартную надстройку Excel – «Пакет анализа».

Функция ОКРВВЕРХ(число;точность) округляет с избытком число до ближайшего целого либо до ближайшего кратного указанному значению

=ОКРВВЕРХ(2,5;1) Округляет число 2,5 до ближайшего большего числа, кратного 1, то есть до 3;

Функция ОКРВНИЗ(число;точность) округляет с недостатком число до ближайшего целого либо до ближайшего кратного указанному значению.

=ОКРВНИЗ(2,5;1) Округляет число 2,5 до ближайшего меньшего числа, кратного 1, то есть до 2.

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

Кроме вышеперечисленных функций, в стандартных средствах Excel есть еще такие функции, связанные с округлением, как

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

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

Для тех, кто использует стандартные функции Excel при написании макросов в VBA, может быть полезен сайтhttp://ru.excelfunctions.eu/, где есть сопоставление между русскими и английскими именами функций с их русским описанием.

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

Применение этой настройки ведет к изменению числовых значений и точности вычислений.

Округление числовых значений Excel с помощью макроса

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