Форматы данных

Для числовых данных предусмотрены специальные форматы для отображения в ячейках ЭТ. Самые популярные числовые форматы вынесены в виде кнопок во вкладке «Главная» в группе команд «Число». Однако встречаются ситуации, когда этого набора форматов бывает недостаточно. В этом случае можно воспользоваться кнопкой во вкладке «Главная» в группе команд «Число» или вызвать контекстное меню к выделенной ячейке (ячейкам) правой кнопкой мыши и выбрать команду «Формат ячеек» или комбинация клавиш <Ctrl>+<1> (единица). Во всех случаях открывается Диалоговое окно «Формат ячеек» с вкладками. Активной вкладкой является вкладка «Число». Здесь имеется множество готовых форматов.

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

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

Например: Представить числа 1, 1,1, 0,111 в различных форматах.

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

Таблица. Форматы некоторых чисел и вид отображаемых чисел в ячейках MSExcel

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

Положительное; Отрицательное; Нулевое; Текстовое

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

Положительные значения выводилось со словом Избыток,
Отрицательные значения выводилось красным цветом со словом Недостача,
Нулевые – отображались словом Ничего синего цвета, Символьные – словом Ошибка.

Желаемый формат будет таким:

“Избыток“???,0р;[Красный]”Недостача”???,0р;”Ничего”;[Синий]”Ошибка” Вопросительные знаки введены, чтобы размер поля под собственно число был всегда одним и тем же, несмотря на отсутствие ведущих нулей. Таким образом, слова Избыток и Недостача всегда будут расположены в одном и том же месте. Результаты применения формата при вводе различных данных (чисел 5, -3, 0 и буква Х) представлены в таблице ниже.

Таблица. Результаты применения формата при вводе различных данных

Помимо примененных в примере цветов, в формате можно использовать следующие цвета: Черный, Синий, Голубой, Зеленый, Красный, Белый, Желтый. Можно вообще указывать только цвета, например:

[Черный];[Зеленый];[Голубой];[Красный]

Кроме цветов можно в квадратных скобках размещать и простые условия. Рассмотрим следующий пример.

Например. Положим в банке на разные суммы вкладов (до 10 тыс., до 50 тыс. и свыше 50 тыс.) начисляются разные проценты. Следует выделить эти значения разным (Черным, Синим и Красным) цветом.

[Черный][<=10];[Cиний][<=50];[Красный]

Форматы дат. Для форматирования дат используется символы Д, М и Г (день, месяц и год). Ниже перечислено действие элементов формата на отображение даты:

Д – отображает число даты без ведущего нуля (1-31),

ДД – число даты с ведущем нулем (01-31),

М – цифрами месяца без ведущего нуля (1-12),

ММ – цифрами месяца с ведущем нулем нулем(01-12),

МММ – трехбуквенное название месяца (Янв-Дек),

ММММ – полное название месяца (Январь-Декабрь),

ГГ – две младшие цифры года (98, 99, 00, 01…),

ГГГГ – все четыре цифры года (1998, 1999, 2000,2001…),

ДДД – двухбуквенное название дня недели (Пн-Вс),

ДДДД – полное название дня недели (Понедельник-Воскресение).

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

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

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

Условный формат изменяет внешний вид диапазона ячеек на основе условия (или критерия). Если условие истинно, то диапазон ячеек форматируется; если условие ложно, то диапазон ячеек не форматируется.

Условное форматирование позволяет выполнить следующее форматирование:

Правила выделения ячеек используют для быстрого поиска конкретных ячеек в выделенном диапазоне, можно отформатировать эти ячейки с помощью оператора сравнения.
Правила отбора первых и последних ячеек – для выделения максимального или минимального значения в диапазоне ячеек на основе указанного порогового значения
Гистограммы помогают рассмотреть значение в ячейке относительно других ячеек. Длина гистограммы соответствует значению в ячейке. Чем она длиннее – тем выше значение. Гистограммы оптимальны при определении основных показателей особенно в больших объемах данных
Цветовые шкалы – это визуальные инструкции, отображающие распределение и разброс данных. Уровень яркости цвета соответствует высоким, средним или низким значениям
Набор значков используется для классификации данных по тремпяти категориям, разделенным пороговым значением
Формулы используются для более сложного форматирования тогда, когда нужно оценить данные в ячейках за пределами выделенного диапазона (диапазона форматируемых ячеек).

Наиболее сложным в Условном форматировании является использование формул. Поэтому рассмотрим применение формул при форматировании подробнее на следующем примере.

Например. Дан список сотрудников некоторой фирмы. Выделить фамилии сотрудников, работающих в 4-ом отделе, желтым цветом, а работающих в 3-ем отделе – синим.

Для решения этого примера воспользуемся условным форматированием. Выберем во вкладке Главная в разделе Стили пункт Условное форматирование, далее Создать правило и Использовать формулу для определения форматируемых ячеек. После этого сформируем условия. Результат условного форматирования и сами правила представлены на рисунке ниже.

Использование формул в условном форматировании

Проверка данных при вводе различных значений в ячейки

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

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

Выделить ячейки, на которые будут накладываться ограничения.
На Ленте во вкладке Данные в разделе Работа с данными щелкнуть по кнопке Проверка данных. Откроется диалоговое окно «Проверка вводимых значений», включающая три вкладки:
1. Параметры – позволяет задавать допустимые данные для ввода. Для этого в раскрывающемся списке «Тип данных» выбирается тип данных, который будет использоваться при вводе значений, и, в зависимости от типа, задается условие
2. Сообщение для ввода позволяет сформировать подсказку, которая будет появляться всегда, когда будет выделена любая из ячеек.

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