Лабораторная работа №1
Форматирование данных в MS Excel
Цель работы: сформировать умения создавать и использовать пользовательские форматы, применять условные форматы для представления данных в электронной таблице.
Пояснения к работе
Форматирование информации, содержащейся в таблицах, не является необходимой процедурой, однако в значительной степени облегчает восприятие и интерпретацию данных.
В Excel возможны два варианта форматирования – форматирование числовых значений и форматирование шрифтов (стилистическое форматирование). Подробнее остановимся на первом.
Большой выбор числовых форматов предоставляет диалоговое окно Формат ячеек, вызвать которое можно одним из перечисленных ниже способов:
ü с помощью команды Формат, Ячейки;
ü посредством команды Формат ячеек контекстного меню ячейки или диапазона;
ü воспользовавшись комбинацией клавиш <Ctrl+1>.
Рисунок 1 – Диалоговое окно Формат ячеек
Кроме стандартных форматов, Excel предоставляет возможность использования форматов, которые может конструировать сам пользователь.
Для конструирования пользовательских форматов можно использовать ряд специальных символов, назначение которых приводится в таблице 1.
Таблица 1. Символы, используемые в масках пользовательских форматов
Символ | Назначение |
Основной # 0 Символ | Использование формата по умолчанию в неформатированных ячейках Обозначает цифру в данном месте формата Обозначает 0 при отсутствии цифры в данном месте формата Назначение |
? _ (символ подчеркивания) Пробел % Е или е + - / \ “ (кавычки) * (звездочка) @ [ ] (квадратные скобки) | Обозначает 0 или пробел (если 0 является незначащим) при отсутствии цифры в данном месте формата Делает пропуск, ширина которого равна ширине символа, находящегося справа от него Внутри шаблона указывает на необходимость разделить число на разряды, а в конце шаблона масштабирует число, округляя его до тысяч Переводит находящееся в ячейке число в проценты Указывает место латинской буквы Е — заменителя числа 10 при записи числа в экспоненциальной форме Обозначает знак "плюс" (+) в данном месте формата Обозначает знак "минус" (-) в данном месте формата Обозначает знак "наклонная черта" (/) в данном месте формата Символ, стоящий справа от обратного слэша (\), будет отображен в ячейке точно в том виде, как в маске формата (независимо от того, является ли он специальным символом или нет) Используется для обозначения начала и конца помещаемого в формат произвольного текста Оставшаяся справа часть ячейки будет заполнена символом, стоящим справа от звездочки (*) Указывает место, где будет отображен вводимый пользователем текст Если внутри скобок находится название цвета, то содержимое ячейки будет иметь этот цвет; если внутри скобок находится условие, то оно интерпретируется как условный формат. |
Из этих символов и символов-разделителей, таких как десятичный разделитель – запятая (,), разделитель групп разрядов — пробел ( ) и др., можно формировать маски пользовательских форматов.
При создании пользовательских форматов для дат и времени полезно знать обозначения, распознаваемые Excel для указанных форматов (например, "М" означает Месяц, а "м" — минута). Эти обозначения и их расшифровка в Excel приводятся в таблице 2.
Таблица 2. Обозначения дат и времени в Excel и их расшифровка
Символ | Расшифровка |
Д ДД ДДД ДДДД М ММ МММ ММММ ГГ ГГГГ | Число в диапазоне от 1 до 31 (без ведущего нуля) Число в диапазоне от 1 до 31 (для чисел от 1 до 9 слева добавляется нуль) Дни недели в сокращенном отображении (пн—вс) Дни недели в полном отображении (понедельник-воскресенье) Номер месяца (без ведущего нуля) Номер месяца с ведущим нулем Сокращенное название месяца (янв—дек) Полное название месяца (январь—декабрь) Две последние цифры года от 00 до 99 Четырехзначный год (от 1900 до 9999) |
При создании пользовательского формата следует помнить, что он может состоять из четырех секций, разделенных знаком "точка с запятой" (;).
Первая секция используется для вывода, если отображается числовое значение больше нуля, вторая — для отображения числового значения меньше нуля, третья — для отображения нулевого значения, четвертая — для формата сопроводительного текста.
Например, для отображения сумм до миллиона рублей с копейками, вывода предупреждения (красным цветом) для отрицательных сумм и вывода пробелов для нулевых сумм можно использовать формат вида:
## ##0, 00"руб. "; "Отрицательные суммы недопустимы";" "
В некоторых случаях удобно использовать разные формы представления данных в одной и той же ячейке в зависимости от значения содержащихся в ячейке данных.
Один из способов задания разных форматов в одной ячейке рассмотрен выше.
Еще одним способом решения указанной проблемы является использование условных форматов. Условные форматы предоставляют несколько большие возможности по количеству накладываемых условий (до трех условий включительно в одном формате) и позволяют в зависимости от условий менять не только цвет, но также шрифт и обрамление. В то же время возможности использования дополнительной текстовой "диагностики" значительно меньше, чем при использовании пользовательских форматов.
Рисунок 2 – Диалоговое окно Условное форматирование
Задания
После выполнения лабораторной работы Вы должны
· знать символы, используемые в масках пользовательских форматов, секции пользовательского формата;
· знать способы задания разных форматов в одной ячейке;
· уметь задавать пользовательские форматы;
· уметь создавать условные форматы.
Ход выполнения лабораторной работы
Задача 1. Отформатировать ячейки таблицы так, чтобы:
ü в ячейке А2 номера сотовых телефонов выводились в виде 8 903 ;
ü в ячейке А5 введенное слово повторялось несколько раз;
ü в ячейке В2 вместо введенного числа выводился текст «холодно», если число меньше 15, и «тепло», если число больше либо равно 15;
ü в ячейке В5 при вводе числового значения выводилось «Цена ‘значение’ рублей»
ü в ячейки С2 вместо введенной даты выводился день недели;
ü в ячейке D2 вводимые положительные значения выделялись синим цветом, отрицательные – красным, нулевые – зеленым, а текст – желтым.
Для задания (маски) пользовательского формата нужно:
1. Выполнить команду Формат, Ячейки.
2. На вкладке Число (Number) диалогового окна Формат ячеек в списке Числовые форматы выбрать все форматы.
3. Ввести в поле со списком Тип маску пользовательского формата или выбрать ее из списка имеющихся пользовательских форматов.
4. Нажать кнопку ОК.
Для удаления введенного ранее пользовательского формата нужно выделить этот формат в списке Тип и нажать кнопку Удалить.
Задача 2. Отформатировать диапазон ячеек Е5:Е10 так, чтобы отрицательные числа и числа больше миллиона отображались с использованием одного фона, числа от 10 до 100 тысяч с использованием другого фона, а числа от 100 тысяч до миллиона с использованием штриховки.
Для создания условного формата в выделенной ячейке или диапазоне нужно выполнить команду Формат, Условное форматирование и задать параметры в открывшемся диалоговом окне.
В раскрывшемся списке нужно выбрать, как будет производиться условное форматирование – по значению или по формуле, и количество накладываемых условий.
Содержание отчета
1. Запишите в тетрадь ход выполнения лабораторной работы.
2. Ответьте письменно на контрольные вопросы.
Контрольные вопросы
1. Каково назначение пользовательских форматов?
2. Опишите символы, используемые в масках пользовательских форматов.
3. Опишите расшифровку обозначений дат и времени в Excel.
4. Опишите назначение секций пользовательского формата.
5. Для чего служат условные форматы?
