Лабораторная работа №7. Форматирование численных данных в Excel
Цель работы: ввод числовых значений, форматирование чисел, создание пользовательских числовых форматов, форматирование положительных, отрицательных, нулевых и текстовых значений, включение цвета в форматы, условное форматирование в MS Excel.
Методические указания
В Microsoft Excel можно изменить формат числа, включая дату и время, без изменения формата других чисел. Применяемый числовой формат не влияет на значение в ячейке (отображается в строке формул), которое Excel будет использовать при вычислениях.
Общий числовой формат используется по умолчанию. В большинстве случаев отображается то, что вводится в ячейку, имеющую общий формат. Однако если ширины ячейки недостаточно для отображения всего числа, общий числовой формат округляет число или использует научное представление для больших чисел.
Excel содержит множество встроенных числовых форматов. Для просмотра полного списка форматов, выберите команду Ячейки в меню Формат и перейдите на вкладку Число. Форматы объединены в категории: денежный, дата, время, дробный, текстовый или экспоненциальный. Категория Дополнительный формат включает в себя почтовые индексы и телефонные номера. Параметры каждой категории можно посмотреть справа от листа Категории.
При необходимости, можно создать пользовательский числовой формат. Используйте категорию Все форматы на вкладке Число (меню Формат, команда Ячейки) для создания пользовательского числового формата. Пользовательский числовой формат использует коды, описывающие отображение числа, даты, времени или текста.
Форматирование текста и отдельных символов. Чтобы выделить текст, можно отформатировать весь текст в ячейке или только отдельные символы. Выделите символы, которые нужно отформатировать, и нажмите одну из кнопок 
на панели инструментов Форматирование.
Вращение текста и границ. Данные в столбце занимают, как правило, гораздо меньше места, чем нужно для подписи столбца. Чтобы не создавать неоправданно широкие столбцы или использовать сокращенные обозначения в подписях столбцов, можно повернуть текст и наложить границы, которые будут повернуты на тот же угол, что и текст. Чтобы повернуть текст:
· Выделите ячейки, внутри которых необходимо перевернуть текст.
· В меню Формат выберите команду Ячейки, а затем перейдите к вкладке Выравнивание.
· В поле Ориентация выберите градус поворота либо переместите индикатор на необходимый угол. Для отображения текста вертикально сверху вниз в поле Ориентация выберите поле с вертикальным Текстом.
Чтобы наложить границы:
· Выделите ячейки, в которые необходимо добавить рамку.
· Чтобы использовать последний выбранный цвет, нажмите кнопку Границы на панели инструментов Форматирование.
Для использования другого типа рамки нажмите стрелку рядом с кнопкой Границы и выберите требуемый тип рамки.
Дополнительные параметры границы. Чтобы использовать дополнительные типы рамок, в меню Формат выберите команду Ячейки, а затем перейдите к вкладке Граница. Выберите требуемый тип линии, а затем укажите место применения выбранной рамки.
Границы и повернутый текст. Для применения рамок к выделенным ячейкам, содержащим повернутый текст, используйте кнопки Внешние и Внутренние на вкладке Границы. Рамка будет использована на границах ячеек, повернутых на тот же угол, что и текст.
Типы линий границы. Для изменения типа линии уже существующей рамки выделите ячейки, на которых рамка отображена. На вкладке Граница в поле Тип линии выберите необходимый тип, а затем в диаграмме, расположенной ниже поля Отдельные, укажите границу, которую необходимо изменить.
Добавление границ, цветов и узоров. Для разделения различных типов информации на листе можно обвести границы ячеек, затенить фон ячейки или затенить ячейки цветным узором. Затенение фона ячеек цветом:
1. Выделите ячейки, которые необходимо затенить.
2. Чтобы использовать последний выбранный цвет, выберите мышью элемент Цвет заливки на панели инструментов Форматирование.
3. Для использования другого цвета нажмите стрелку рядом с кнопкой Цвет заливки, а затем выберите необходимый цвет.
Затенение ячеек узором:
1. Выделите ячейки, которые необходимо затенить.
2. В меню Формат выберите команду Ячейки, а затем перейдите к вкладке Вид.
3. Для использования цвета фона с узором выберите необходимый цвет в поле Заливка ячеек.
4. Нажмите стрелку рядом с полем Узор, а затем выберите необходимый узор и цвет.
5. Если цвет узора не выбран, то он будет черным.
Числовые форматы позволяют изменять внешний вид чисел, включая даты и время, не изменяя внутреннее представление чисел.
Порядок выполнения работы
1. Включите компьютер. Загрузите Excel.
2. Узнайте у преподавателя номер своего варианта. Создайте на рабочем листе пользовательскую таблицу, изображенную на рис. 26.
Рисунок 26 Пользовательская таблица к заданию
3. Введите данные Xi по месяцам, предусматривая ввод прибылей (положительные числа с дробной частью) и убытков (отрицательные числа с дробной частью). Кроме того, должны быть введены нулевые значения.
4. Подсчитать сумму по складам за весь период.
5. Скопировать итоговые значения прибылей из столбца Итого в столбец Заключение.
6. Создать пользовательский формат в соответствии с заданием из табл. 6 и отформатировать введенные числовые данные. Кодировка заданий показана в таблице, где s – код режима отображения знака числа, j – код наличия разделителя, n – количество разрядов в дробной части числа, v – код обозначения денежной единицы, c – код цвета. Расшифровка кодов показана в табл. 8.
Таблица 6 Варианты заданий для создания пользовательских форматов
| № | Секции формата | |||
| X>0 <s>,<j>,<n>,<v>,<c> | X<0 <s>,<j>,<n>,<v>,<c> | X=0 <n>,<v>,<c> | Т <c> | |
| 1, 1, 0, 1, 6 | 2, 2, 3, 5, 1 | 3, 6, 6 | ||
| 1, 2, 1, 1, 5 | 2, 1, 2, 2, 2 | 2, 5, 6 | ||
| 2, 1, 2, 2, 4 | 1, 1, 1, 3, 3 | 1, 5, 4 | ||
| 2, 2, 3, 6, 3 | 1, 2, 0, 4, 5 | 3, 4, 2 | ||
| 1, 1, 0, 4, 2 | 2, 1, 3, 6, 6 | 2, 3, 3 | ||
| 1, 2, 1, 5, 1 | 2, 2, 2, 2, 3 | 1, 1, 1 | ||
| 2, 2, 2, 2, 1 | 1, 2, 1, 3, 4 | 2, 2, 4 | ||
| 2, 2, 3, 6, 2 | 1, 1, 0, 5, 3 | 2, 3, 1 | ||
| 1, 1, 0, 5, 3 | 2, 2, 0, 3, 1 | 1, 5, 6 | ||
| 1, 2, 1, 4, 4 | 2, 1, 1, 3, 1 | 3, 4, 5 | ||
| 2, 1, 2, 2, 5 | 1, 1, 2, 2, 2 | 2, 2, 5 | ||
| 2, 2, 3, 6, 6 | 1, 2, 3, 6, 5 | 1, 4, 5 | ||
| 1, 1, 0, 1, 6 | 2, 1, 2, 2, 4 | 3, 3, 3 | ||
| 1, 2, 1, 1, 5 | 2, 1, 3, 5, 1 | 2, 5, 5 | ||
| 1, 1, 2, 2, 4 | 1, 2, 3, 5, 2 | 3, 1, 2 | ||
| 1, 2, 3, 5, 3 | 1, 2, 2, 2, 6 | 2, 6, 6 | ||
| 1, 1, 0, 3, 2 | 2, 1, 0, 1, 5 | 1, 1, 6 | ||
| 1, 2, 1, 4, 1 | 2, 1, 3, 5, 4 | 2, 1, 3 | ||
| 1, 1, 2, 2, 6 | 1, 1, 1, 4, 3 | 3, 4, 1 | ||
| 1, 2, 2, 2, 1 | 1, 2, 0, 5, 4 | 2, 3, 4 |
7. Отформатировать результаты в столбце Итого с использованием условного пользовательского формата. Варианты заданий приведены в табл. 7. Расшифровка кодов показана в табл. 8.
Таблица 7 Условные пользовательские форматы
| № | Условие 1 | Задание <s>,<j>,<n>,<v>,<c> | Условие 2 | Задание <s>,<j>,<n>,<v>,<c> |
| Xi>1000 | 2, 1, 3, 6, 6 | Xi<=30 | 2, 1, 2, 2, 4 | |
| Xi>500 | 2, 2, 2, 2, 3 | Xi<0 | 2, 2, 3, 6, 3 | |
| Xi<>300 | 1, 2, 1, 3, 4 | Xi>1000 | 1, 1, 0, 4, 2 | |
| Xi=700 | 1, 1, 0, 5, 3 | Xi>1000 | 1, 2, 1, 5, 1 | |
| Xi>=900 | 2, 2, 0, 3, 1 | Xi<200 | 2, 2, 2, 2, 1 | |
| Xi>=400 | 2, 1, 1, 3, 1 | Xi>800 | 2, 2, 3, 6, 2 | |
| Xi<>100 | 1, 1, 2, 2, 2 | Xi>1000 | 1, 1, 0, 5, 3 | |
| Xi=2000 | 1, 2, 3, 6, 5 | Xi<>500 | 1, 2, 1, 4, 4 | |
| Xi>450 | 2, 1, 2, 2, 4 | Xi<330 | 2, 1, 2, 2, 5 | |
| Xi>500 | 2, 1, 3, 5, 1 | Xi=600 | 2, 2, 3, 6, 6 | |
| Xi=3000 | 1, 2, 3, 5, 2 | Xi<200 | 1, 1, 0, 1, 6 | |
| Xi=400 | 1, 2, 2, 2, 6 | Xi<100 | 1, 2, 1, 1, 5 | |
| Xi<3400 | 2, 1, 0, 1, 5 | Xi>150 | 1, 1, 2, 2, 4 | |
| Xi>6000 | 2, 1, 3, 5, 4 | Xi>300 | 1, 2, 3, 5, 3 | |
| Xi=300 | 1, 1, 1, 4, 3 | Xi>1000 | 1, 1, 0, 3, 2 | |
| Xi<100 | 1, 2, 0, 5, 4 | Xi>500 | 1, 2, 1, 4, 1 | |
| Xi>1000 | 1, 1, 0, 1, 6 | Xi<>0 | 1, 1, 2, 2, 6 | |
| Xi>900 | 1, 2, 1, 1, 5 | Xi>7000 | 1, 2, 2, 2, 1 | |
| Xi>1000 | 2, 1, 2, 2, 4 | Xi=100 | 2, 2, 3, 5, 1 | |
| Xi<=500 | 2, 2, 3, 6, 3 | Xi=180 | 2, 1, 2, 2, 2 |
8. Создать условный пользовательский формат для столбца Заключение, работающий следующим образом: если число в ячейке >1000, то отображается слово Прибыль, если число в ячейке <=100, то отображается слово Убыток.
9. Сохраните результаты работы, так как пользовательские таблицы будут необходимы при выполнении следующей лабораторной работы.
Таблица 8 Расшифровка кодов заданий для создания пользовательских форматов и условных пользовательских форматов
| s | Знак числа | j | Разделитель триад | v | Символ денежной единицы | c | Цвет |
| Есть | Нет | р. | Красный | ||||
| руб. коп. | Синий | ||||||
| Руб. | Зеленый | ||||||
| Нет | Есть | Рубли | Желтый | ||||
| Тыс. руб. | Фиолетовый | ||||||
| Млн. руб. | Черный |
Порядок оформления отчета
Подготовьте отчет о выполненной лабораторной работе. Отчет о лабораторной работе должен содержать: титульный лист (с действующим вариантом титульного листа можно ознакомиться на http://standarts.guap.ru), цель лабораторной работы, полученные в ходе выполнения работы документы. На компьютере представляются файлы с результатами работы, записанные в папку с номером вашей группы/ваша фамилия/№ лабораторной работы. Сформулируйте выводы, которые можно сделать по результатам выполненной работы.
Контрольные вопросы
1. В чем разница между хранимым и отображаемым значениями?
2. Каковы особенности общего формата?
3. В чем различие в работе финансового и денежного форматов?
4. Область применения дополнительных форматов и их особенности.
5. Как создать пользовательский числовой формат?
6. Что понимают под ориентацией текста?
7. Каким способом можно поменять тип линии границы?
8. Какие встроенные числовые форматы вы знаете?
9. Какой числовой формат используется по умолчанию?