Форматирование листов и данных
Лабораторная работа №2
АВТОЗАПОЛНЕНИЕ ЯЧЕЕК И
ФОРМАТИРОВАНИЕ ЛИСТОВ В MS EXCEL
Цель работы: изучение функций автозаполнения; форматирование ячеек; использование стилей; форматы данных в ячейках.
ТЕОРЕТИЧЕСКИЕ ПОЛОЖЕНИЯ
Автозаполнение
Excel позволяет автоматически заполнять ячейки листа последовательными логически связанными значениями или одним и тем же значением в нескольких ячейках таблицы (даже если ячейки не смежные или располагаются на разных листах).
1.1.1 Ввод чисел, текста, даты или времени суток.Для выполнения этих действий щелкните ячейку, в которую необходимо ввести данные, наберите данные, нажмите клавишу Enter или клавишу табуляции и выполните одно из следующих действий.
- Ввод чисел и текста в списке.
1) Введите данные в ячейку в первом столбце и нажмите клавишу табуляции для перемещения в соседнюю ячейку.
2) После ввода данных в первую строку нажмите клавишу Enter, чтобы перейти на начало следующей.
3) Если ячейка, расположенная в начале следующей строки, не становится активной, выберите команду меню Сервис Þ Параметры, а затем – вкладку Правка (рис. 1.1). В группе Параметры установите флажок Переход к другой ячейке после ввода, а затем из списка в направлении выберите значение Вниз.
- Ввод даты. При вводе даты используйте точку или дефис в качестве разделителя, например 09.05.2002 или Янв-2002. Чтобы ввести текущую дату, нажмите клавиши Ctrl+; (точка с запятой).
- Вод времени суток. Для отображения времени суток в 12-часовом формате введите букву a или p, отделенную пробелом от значения времени, например 9:00 p. В противном случае время будет интерпретировано на основе 24-часового формата. Чтобы ввести текущее время, нажмите клавиши Ctrl+Shift+: (двоеточие).
1.1.2 Ввод чисел с фиксированным количеством десятичных разрядов или конечных нулей. Для этого:
1) Выберите команду меню Сервис Þ Параметры, а затем откройте вкладку Правка (см. рис. 1.1).
2) Установите флажок Фиксированный десятичный формат при вводе.
3) В рамке разрядов введите положительное или отрицательное число, которое задает автоматическое умножение вводимого значения на десять в соответствующей степени.
Рисунок 1.1 – Вкладка Правка диалога Параметры
Например, если ввести 3 в рамке разрядов, а затем – 2834 в ячейке, то значение будет равно 2,834. Если ввести -3 в рамке разрядов, а затем – 283, то значение будет равно 283000.
Изменение параметра Фиксированный десятичный формат при вводе не оказывает влияния на введенные до этого данные. Чтобы для данного числа параметр Фиксированный десятичный формат при вводе не использовался, при наборе числа введите десятичную точку.
1.1.3 Ввод одного и того же значения в несколько ячеек одновременно. Для этого:
1) Выделите ячейки, в которые необходимо ввести данные. Они не обязательно должны быть смежными.
2) Введите данные и нажмите клавиши Ctrl+Enter.
1.1.4 Ввод и изменение одного и того же значения на нескольких листах. Если выбрана группа рабочих листов, то изменения в одном из них применяются ко всем выделенным листам. Данные могут быть изменены. Для выполнения этих действий:
1) Выберите листы, на которые необходимо ввести данные. Чтобы выделить:
- отдельный лист щелкните ярлычок листа. Если ярлычок нужного листа не виден, найдите этот ярлычок с помощью кнопок прокрутки листов, а затем щелкните его;
- два или более смежных листа щелкните ярлычок первого листа, а затем, удерживая нажатой клавишу Shift, щелкните ярлычок последнего листа;
- два или более несмежных листа щелкните ярлычок первого листа, а затем, удерживая нажатой клавишу Ctrl, последовательно щелкните ярлычки остальных листов;
- все листы книги щелкните правой кнопкой мыши ярлычок какого-либо листа, а затем выберите команду Выделить все листы в контекстном меню.
2) Укажите ячейку или выделите диапазон ячеек, в которые необходимо ввести данные.
3) Введите или измените данные в первой выделенной ячейке.
4) Нажмите клавишу Enter или клавишу табуляции.
1.1.5 Автоматическое заполнение повторяющихся записей в столбце. Если несколько первых знаков, вводимых в ячейку, совпадают со знаками записи, ранее введенной в этом столбце, то недостающая часть набора будет произведена автоматически. В Microsoft Excel автоматический ввод производится только для тех записей, которые содержат текст или текст в сочетании с числами. Записи, полностью состоящие из чисел, дат или времени, необходимо вводить самостоятельно.
Для подтверждения предлагаемого варианта, нажмите клавишу Enter. Законченная запись по формату полностью совпадает с существующей записью, включая знаки верхнего и нижнего регистра.
Для замены автоматически введенных знаков продолжите ввод самостоятельно.
Для удаления автоматически введенных знаков нажмите клавишу Backspace.
Чтобы выбрать значение из списка записей, которые уже имеются в столбце, нажмите правую кнопку мыши, а затем выберите в контекстном меню команду Выбрать из списка.
1.1.6 Заполнение серии ячеек числами, датами либо другими элементами. Для этого:
1) Выделите первую из заполняемых ячеек.
2) Введите начальное значение для ряда значений.
3) Введите значение в соседнюю ячейку, чтобы определить образец заполнения:
- если требуется получить ряд 2, 3, 4, 5…, введите 2 и 3 в первые две ячейки. Если требуется получить ряд 2, 4, 6, 8…, введите 2 и 4. Если требуется получить ряд 2, 2, 2, 2…, вторую ячейку можно оставить пустой;
- чтобы задать тип ряда значений, перетащите маркер заполнения правой кнопкой мыши, а затем выберите соответствующую команду в контекстном меню. Например, если начальное значение – дата «янв-2002», то для получения ряда «фев-2002», «мар-2002» и т.д. выберите команду Заполнить по месяцам, а для получения ряда «янв-2003», «янв-2004» и т. д. выберите команду Заполнить по годам;
- для того чтобы управлять созданием ряда вручную или использовать для заполнения ряда значений клавиатуру, используйте команду контекстного меню – Прогрессия.
4) Выделите ячейку или ячейки, содержащие начальные значения.
5) Перетащите маркер заполнения через заполняемые ячейки:
- для заполнения в возрастающем порядке перетащите маркер вниз или вправо;
- для заполнения в убывающем порядке перетащите маркер вверх или влево.
1.1.7 Ввод одинаковых данных в разные рабочие листы.Если данные были введены на одном листе, то их можно быстро скопировать в соответствующие ячейки других листов. Для этого:
1) Выберите лист, содержащий введенные данные, и листы, на которые их необходимо скопировать.
2) Выделите ячейки, содержащие данные, которые необходимо скопировать.
3) Выберите команду меню Правка Þ Заполнить Þ По листам.
Форматирование листов и данных
1.2.1 Оформление ячеек. Грамотное оформление ячеек помогает быстрее отыскивать числа, которые требуют изменения, и ячейки с результатами вычислений. Чтобы раскрасить таблицу листа можно воспользоваться инструментом автоформата. Для этого:
1) выделите прямоугольную область, содержащую таблицу;
2) выберите команду меню Формат Þ Автоформат;
3) в открывшемся диалоге (рис. 1.2) щелкните на подходящем образце;
Рисунок 1.2 –Диалоговое окно Автоформат
4) щелкните на кнопке Параметры, открыв этой командой область флажков для установки дополнительных параметров;
5) сбросьте флажок ширину и высоту, чтобы инструмент автоформата не изменял размеры ячеек;
6) щелкните на кнопке ОК.
Программа Excel сама оформит таблицу выбранным стилем.
Чтобы изменить формат ячеек вручную необходимо выделить нужные ячейки и выбрать команду меню Формат Þ Ячейки или команду контекстного меню выделенных ячеек Формат ячеек. В результате появится диалоговое окно Формат ячеек (рис. 1.3).
 |  |
| а | б |
 |  |
| в | г |
Рисунок 1.3 – Диалог Формат ячеек:
а – вкладка Граница;
б – вкладка Вид;
в – вкладка Выравнивание;
г – вкладка Шрифт.
На вкладке Граница (см. рис. 1.3а) в списке Тип линии можно выбрать линию подходящей толщины. С помощью кнопок в левой части окна можно задать границы ячеек (внешние, внутренние или отдельные). На вкладке Вид (см. рис. 1.3б) можно задавать цвет заливки выбранных ячеек или заполнять их узором, воспользовавшись раскрывающимся списком Узор.
Данные в столбце занимают, как правило, гораздо меньше места, чем нужно для подписи столбца. Чтобы не создавать неоправданно широкие столбцы или использовать сокращенные обозначения в подписях столбцов, можно повернуть текст и наложить границы (см. рис. 1.3а), которые будут повернуты на тот же угол, что и текст. Чтобы повернуть текст необходимо задать ориентацию в одноименном разделе вкладки Выравнивание (см. рис. 1.3в).
Для построения таблиц сложной структуры можно воспользоваться объединением ячеек. Для этого на вкладке Выравнивание (см. рис. 1.3в) необходимо поставить соответствующий флажок в разделе Отображение.
1.2.2 Форматирование текста и отдельных знаков.Excel позволяет форматировать не только цвет и рамку ячеек, но также шрифт и расположение их содержимого. Для задания форматирования содержимого ячейки или группы ячеек можно воспользоваться также диалогом Формат ячеек.
На вкладке Выравнивание (см. рис. 1.3в) в раскрывающихся списках По горизонтали и По вертикали модно задать соответствующий тип выравнивания содержимого в ячейке. С помощью счетчика отступ можно задать отступ от левого края ячейки. На вкладке Шрифт (см. рис. 1.3г) можно задать все необходимые параметры шрифта (тип, начертание, размер и т.д.)
Для форматирования содержимого выделенных ячеек также можно воспользоваться соответствующими кнопками на панели инструментов Форматирование (форматирование выполняется так же, как и в MS Word).
1.2.3 Использование стилей. Применение стилей позволяет быстро назначать ячейкам определенное заранее заданное оформление. Изменение стиля приводит к моментальному обновлению форматирования всех ячеек, которым назначен этот стиль. За основу вновь создаваемого стиля всегда выбирается формат активной ячейки. Для создания стиля:
1) выполните команду меню Формат Þ Стиль;
2) введите название нового стиля в поле раскрывающегося списка Имя стиля открывшегося окна диалога (рис. 1.4). В окне диалога появятся его исходные параметры (то есть режим форматирования выделенных ячеек);
3) щелкните на кнопке Изменить;
4) в открывшемся окне диалога Формат ячеек на вкладке Шрифт (см. рис. 1.3г) выберите необходимые параметры шрифта и щелкните на кнопке ОК.
5) в окне диалога Стиль сбросьте все флажки, кроме шрифт, чтобы новый стиль влиял только на шрифт ячейки и не изменял другие параметры оформления;
6) щелкните на кнопке ОК.
Можно использовать стандартные стили Excel:
- Обычный – стандартный стиль ячеек;
- Процентный – стиль ячеек, содержащих процентные величины;
- Финансовый – денежные значения (рубли и копейки);
- Финансовый [0] – денежные значения (только рубли);
- Денежный – денежные значения (рубли, копейки и знак денежной единицы);
- Денежный [0] – денежные значения (только рубли и знак денежной единицы).
Рисунок 1.4 – Диалоговое окно настройки стиля
Форматы данных в ячейках
Электронные таблицы обрабатывают числовые данные, которые размещаются в ячейках листа Excel. Столбцы и строки таблицы могут иметь текстовые названия. На листе Excel могут также располагаться заголовки, подписи и дополнительные ячейки данных с пояснительным текстом.
1.3.1 Формат чисел. Так как программа Excel предназначена для обработки чисел, важную роль играет правильная настройка их формата. Для человека число 10 – это просто единица и ноль. С точки зрения Excel эти две цифры могут нести совершенно разную информацию в зависимости от того, обозначают ли они количество работников компании, денежную величину, процентную часть целого или фрагмент заголовка «10 ведущих фирм». Во всех четырех ситуациях это число должно отображаться и обрабатываться по-разному. Excel поддерживает следующие форматы данных:
- Общий – текст и числовые значения произвольного типа;
- Числовой – наиболее общий способ представления чисел;
- Денежный – денежные величины;
- Финансовый – денежные величины с выравниванием по разделителю целой и дробной частей;
- Дата – дата или дата и время;
- Время – время или дата и время;
- Процентный – значение ячейки, умноженное на 100 с символом «%» в конце;
- Дробный – рациональные дроби с числителем и знаменателем;
- Экспоненциальный – десятичные дробные числа;
- Текстовый – текстовые данные отображаются точно так же, как вводятся и обрабатываются строки, вне зависимости от их содержимого;
- Дополнительный – форматы для работы с базами данных и списками адресов;
- Заказной – формат, настраиваемый пользователем.
Вводимые в ячейку числа интерпретируются как константы. В Microsoft Excel число может состоять только из следующих символов:
0 1 2 3 4 5 6 7 8 9 + - ( ) , / $ % . E e
Стоящие перед числом знаки плюс игнорируются, а запятая интерпретируется как разделитель десятичных разрядов. Все другие сочетания клавиш, состоящие из цифр и нецифровых символов, рассматриваются как текст. Символы, которые рассматриваются как числа, зависят от установленных параметров в компоненте Язык и стандарты панели управления.
Перед рациональной дробью следует вводить 0 (нуль), чтобы избежать ее интерпретации как формата даты; например, вводите 0 1/2.
Перед отрицательным числом необходимо вводить знак минус или заключать его в круглые скобки.
Введенные числа выравниваются в ячейке по правому краю. Чтобы изменить выравнивание, выделите ячейки, выберите команду Ячейки в меню Формат, а затем на вкладке Выравнивание установите необходимые параметры (см. рис. 1.3в).
Формат числа, назначаемый ячейке, определяет способ просмотра числа на листе. Если число вводится в ячейку, имеющую общий числовой формат, то может быть использован другой формат. Например, если вводится 14,73р., то будет использован денежный формат. Чтобы изменить числовой формат, выделите ячейки, которые содержат числа, выберите команду Ячейки в меню Формат, а затем на вкладке Число выберите необходимый формат.
В ячейках, имеющих общий числовой формат, отображаются целые числа (789), десятичные дроби (7,89) или числа, представленные в экспоненциальной форме (7,89E+08), если число длиннее ширины ячейки. Используя общий формат можно просматривать до 11 разрядов, включая десятичные запятые и такие символы, как E и +. Чтобы использовать числа с большим количеством разрядов, используйте встроенный экспоненциальный числовой (экспоненциальное представление числа) или пользовательский формат.
Независимо от количества отображаемых разрядов числа хранятся с точностью до 15 разрядов. Если число имеет больше 15 значащих знаков, то разряды после 15-го преобразуются в нули (0).
Число хранится в числовом формате, даже если была использована команда Ячейки, чтобы назначить текстовый формат ячейкам, содержащим числа. Для интерпретации чисел, например, инвентаризационных номеров, как текста, необходимо назначить текстовый формат незаполненным ячейкам. Только после этого следует вводить числа. Если числа уже введены, то назначьте ячейкам текстовый формат и, выделяя каждую ячейку, нажимайте клавишу F2, а затем – клавишу Enter, чтобы ввести заново данные.
1.3.2 Формат текста. В Microsoft Excel текстом является любая последовательность, состоящая из цифр, пробелов и нецифровых символов, например, приведенные ниже записи интерпретируются как текст:
10AA109, 127AXY, 12-976, 208 4675.
Введенный текст выравнивается в ячейке по левому краю. Чтобы изменить выравнивание, выберите команду Ячейки (вкладка Выравнивание) в меню Формат, а затем измените необходимые параметры.
Чтобы просмотреть весь текст, занимающий в ячейке несколько строк, установите флажок Переносить по словам на вкладке Выравнивание диалога Формат ячеек (см. рис. 1.3в). Чтобы начать в ячейке новую строку, нажмите клавиши Alt+Enter.
ХОД ВЫПОЛНЕНИЯ РАБОТЫ
2.1 Создать новую книгу и сохранить под именем «ваша фамилия»_excel2.
2.2 Создать таблицу (см. индивидуальное задание).
2.3 В соответствии с логикой выбрать формат и заполнить ячейки таблицы.
2.4 Выполнить автоформатирование таблицы (см. индивидуальное задание).
2.5 Добавить несколько строк и столбцов внутри таблицы.
2.6 Удалить добавленные строки и столбцы.
2.7 Изменить направление текста в заголовках столбцов (см. индивидуальное задание).
2.8 Добавить первый столбец и пронумеровать строки таблицы, используя автозаполнение.
2.9 Выполнить форматирование первого столбца (см. индивидуальное задание).
2.10 Сохранить все изменения в книге.
ИНДИВИДУАЛЬНЫЕ ЗАДАНИЯ
Номер варианта соответствует 2-й цифре по журналу академической группы (цифра 0 – вариант№10).
Вариант 1. Сравнительная таблица розничных цен на продовольственные товары по городам Украины.
| Киев | Одесса | Харьков | Ужгород | Сумы | Средняя цена | |
| Говядина | ||||||
| Свинина | ||||||
| Птица | ||||||
| Рыба |
Вариант 2. Данные о продаже автомобилей в 200__г.
| Марка | 1 квартал | % | 2 квартал | % | 3 квартал | % | 4 квартал | % |
| БМВ | ||||||||
| Форд | ||||||||
| Ауди | ||||||||
| Рено | ||||||||
| Тойота | ||||||||
| Всего |
Вариант 3. Температура воздуха в городах мира с 1 по 7 января 200__г.
| Дата | Москва | Рим | Париж | Лондон | Мадрид |
| 01 янв 2000 | |||||
| 02.01.2000 | |||||
| 03.1.2000 | |||||
| 04.01.2000 | |||||
| 5 янв | |||||
| 6.01 | |||||
| 07.01.2000 13:30 | |||||
| Средн. температура |
Вариант 4. Выручка от продажи книжной продукции в 200_ г.
| Название книги | 1 кв. | 2 кв. | 3кв. | 4 кв. | Процент за год |
| 1. | |||||
| 2. | |||||
| 3. | |||||
| 4. | |||||
| 5. | |||||
| 6. | |||||
| Всего за год |
Вариант 5. Итоговые экспертные оценки (от 0 до 10 баллов) кандидатов на должность генерального директора фирмы «Аква».
| Номер кандидата | Эксперты | Сумма баллов | Процент | |||
| №1 | ||||||
| №2 | ||||||
| №3 | ||||||
| №4 | ||||||
| №5 | ||||||
| Всего |
Вариант 6. Прайс-лист фирмы «Трианон» на 22 декабря 200_ г.
| Наименование товара | Розничная цена, руб. | От 10 шт. | От 100 шт. | Свыше 1000 шт. | Дилеры |
| 1. | |||||
| 2. | |||||
| 3. | |||||
| 4. | |||||
| 5. | |||||
| 6. |
Вариант 7. Перечень доходов и расходов за 1-е полугодие.
| Статья \ Месяц | Янв. | Февр. | Март | Апр. | Май | Июнь |
| Доходы | ||||||
| Оклад | ||||||
| Премия | ||||||
| Совместительство | ||||||
| Всего доходов | ||||||
| Расходы | ||||||
| Жилье | ||||||
| Кредит | ||||||
| Питание | ||||||
| Налоги | ||||||
| Другие расходы | ||||||
| Всего расходов | ||||||
| Остаток | ||||||
| Остаток за полугодие |
Вариант 8. Результаты зимней сессии 200_ г. студентов группы КИТ-__.
| Ф.И.О. | Математика | Программирование | Иностранный язык | Физика | Сумма | Средний балл |
| 1. | ||||||
| 2. | ||||||
| 3. | ||||||
| 4. |
Вариант 9. Анализ цен на товары в конкурирующих фирмах.
| Наименование товара | Название фирмы | ||
| ВЛАДОС | ОК | Контраст | |
| 1. | |||
| 2. | |||
| 3. | |||
| 4. | |||
| 5. | |||
| Сумма |
Вариант 10. Изменение курса валют за период 1 по 5 марта 200_г.
| Дата | Доллар | Марка | Доллар/Марка |
| 1 мар 2000 | |||
| 2 мар 2000 | |||
| 3 мар 2000 | |||
| 4 мар 2000 | |||
| 5 мар 2000 | |||
| Средний за период | |||
| Процент роста |
Автоформатирование таблицы. Формат выбирается по формуле:
№формата = №варианта mod 6.
где №формата =
0. Простой; 1. Классический;
2. Финансовый; 3. Цветной;
4. Список; 5. Объемный.
| № варианта | Параметры ячеек 1-го столбца | |||||||
| шрифт | начертание | размер | подчеркивание | цвет | видоизменение | выравнивание | ||
| по горизонтали | по вертикали | |||||||
| 1. | Times New Roman | курсив | одинарное, по значению | цвет | зачеркнутый | по левому краю | по верхнему краю | |
| 2. | Impact | полужирный | двойное, по значению | цвет | надстрочный | по центру | по нижнему краю | |
| 3. | Courier New | полужирный курсив | одинарное, по ячейке | цвет | подстрочный | с заполнением | по центру | |
| 4. | Verdana | курсив | двойное, по ячейке | цвет | зачеркнутый | по ширине | по высоте | |
| 5. | Times New Roman | полужирный | одинарное, по значению | цвет | надстрочный | по центру выделения | распределенный | |
| 6. | Impact | полужирный курсив | двойное, по значению | цвет | подстрочный | распределенный | по верхнему краю | |
| 7. | Courier New | курсив | одинарное, по ячейке | цвет | зачеркнутый | по левому краю | по нижнему краю | |
| 8. | Verdana | полужирный | двойное, по ячейке | цвет | надстрочный | по центру | по центру | |
| 9. | Times New Roman | полужирный курсив | одинарное, по ячейке | цвет | подстрочный | распределенный | по высоте | |
| 10. | Courier New | курсив | двойное, по ячейке | цвет | зачеркнутый | с заполнением | распределенный |
КОНТРОЛЬНЫЕ ВОПРОСЫ
1) Что такое автозаполнение?
2) Какие способы автозаполнения вы знаете?
3) Что такое автоформат?
4) Как изменить таблицу с помощью автоформата?
5) Как изменить формат ячеек вручную?
6) Как отформатировать содержимое ячеек?
7) Как создать стиль?
8) Как форматировать ячейки с помощью стилей?
9) Какие стандартные стили вы знаете?
10) Какие форматы чисел поддерживает Excel?
11) Для чего нужны форматы чисел?
12) Что в Microsoft Excel является текстом?