Создание и редактирование диаграмм и графиков

Создание и редактирование таблиц

Цель работы: научиться создавать и редактировать таблицы

Содержание работы:

1 Настройка новой книги

2 Ввод данных и формул

3 Форматирование ячеек

Настройка новой книги

После запуска Excel на экране появляется новая рабочая книга с тремя листами (по умолчанию). Некоторые её параметры необходимо настроить, для чего нажать мышью по вкладке Файл (кнопке Office для MS Excel 2007), затем по клавише Параметры Excel – внизу справа - откроется окно Параметры Excel (рисунок 1.1), в котором следует выполнить следующие операции:

1. В левой части окна выделить пункт Формулы, в правой части окна

Рисунок 1.1 – Окно Параметры Excel

▪ Снять флажок в строке Стиль ссылок R1C1, чтобы строки и столбцы именовались в формате А1

Пункт Основные:

▪ Число листов: – 3;

▪ Имя пользователя: – ввести Фамилию, № группы

▪ Шрифт: – Arial, размер 10

Пункт Сохранение:

▪ Установить флажок Автосохранение каждые - 10 минут

Пункт Дополнительно – установить флажки:

▪ Показывать строку формул

▪ Показывать разбиение на страницы

Пункт Надстройки:

Пакет анализа - нажать Перейти, в окне Надстройки поставить флажок на Пакет анализа, нажать ОК

Поиск решения - нажать Перейти, в окне Надстройки поставить флажок на Поиск решения, нажать ОК

Остальные параметры не нуждаются в подстройке, поскольку удовлетворяют общепринятым требованиям.

Окно приложения MS Excel (рисунок 1.2) содержит:

▪ Заголовок (слева – панель быстрого доступа, по центру – имя открытого файла, справа – кнопки Свернуть, Развернуть, Закрыть),

▪ Ленту вкладок Файл (кнопка Office), Главная, Вставка, Разметка страницы и др. Каждая вкладка состоит из групп, напр., вкладка Главная содержит группы Буфер обмена, Шрифт, Выравнивание, …, Редактирование. Группы содержат кнопки и раскрывающиеся списки с различными инструментами.

▪ Список имён ячеек (А1) со Строкой формул (f_x),

▪ Рабочую зону, состоящую из ячеек таблицы, полос прокрутки, именами листов книги Excel с кнопками перехода между листами,

▪ Строки состояния с кнопками режимов предъявления документа (чтение, разметка, веб) и масштабной линейкой.

При установке курсора на элемент окна выводится название этого элемента.

Ячейки и таблицы в Excel расположены на пересечении столбцов и строк, которые обозначаются А, В, С и 1, 2, 3 и т.д. соответственно (если не установлен стиль ссылок R1C1) . Таким образом, адрес ячейки формируются из названия столбца и номера строки, например, А1, В12. Всего рабочий лист может содержать 247 столбцов и 65536 строки.

Рисунок 1.2 – Окно приложения MS Excel

Таблицы MS Excel очень большие, при площади одной ячейки в 169 мм² для экрана с диагональю 14' один рабочий лист занимает площадь в 2735,7 м², что составляет около 60% от площади футбольного поля.

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

Для снятия копии с листа нужно активизировать его, затем при нажатой клавише Ctrl ухватить вкладку копируемого листа (например, Лист1) левой клавишей мыши, не отпуская её, перетащить влево или вправо и отпустить сначала клавишу мыши, затем Ctrl. К стрелке курсора при этом добавляется изображение листа со знаком «+». Копии автоматически присваивается имя, например, Лист 1(2) для первой копии, Лист 1(3) для второй и т.д. Изменить имя листа можно, щелкнув дважды по имени листа левой мышью и ввести новое имя. Поменять листы местами можно перетаскиванием их за вкладки при нажатой клавише Shift.

Эти же операции можно выполнить из контекстного меню, щелкнув по вкладке листа правой кнопкой мыши Оно содержит команды Вставить, Удалить, Переименовать, Переместить\Скопировать и др.

Активная ячейка - это ячейка, в которой установлен курсор. Она выделяется темной рамкой при щелчке по ней мышью. Переход между ячейками производится с помощью мыши или клавиш управления курсором.

Интервал (блок) ячеек задается адресами левой верхней и правой нижней ячеек, разделенных двоеточием, например, А1:С4. Для выделения блока ячеек можно использовать мышь или клавиши управления курсором при нажатой клавише S hift. Для выделения столбца или строки следует щелкнуть мышью по заголовку нужного столбца или строки. Одним из приемов выделения несмежных областей, например, А1:А10 и С1:С10, является использование мыши при нажатой клавише C trl.

Выделенные элементы таблицы или рабочего листа перемещаются и копируются с помощью мыши, либо через буфер обмена: команды Вырезать, Копировать, Вставить.

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

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

Кроме того, высота строк и ширина столбцов таблицы регулируется с помощью команд вкладки Главная, группа Ячейки, Формат \ Размер ячейки – рисунок 1.3.

Рисунок 1.3 – Регулирование размеров ячейки

Для изменения ширины столбца с помощью мыши надо выделить ячейку или весь столбец, выбрать команду Главная\Формат\Размер ячейки\ Ширина столбца… и ввести нужную ширину в миллиметрах в окне Ширина столбца. Команда Автоподбор ширины увеличивает ширину ячейки по длине вводимых символов, команды Скрыть или Отобразить убирают или вставляют отмеченный столбец. Аналогично изменяется высота строки, её автоподбор, удаление или вставка строки при использовании команд меню Формат\Высота строки.

Для подготовки рабочего листа к печати (рисунок 1.4) используется команда Печать из менювкладки Файл ( кнопки Office) или горячие клавиши Ctrl + P.

Рисунок 1.4 – Печать и просмотр документа перед печатью

Вкладка Разметка страницы позволяет задать Поля, Ориентацию, Размер бумаги, Темы оформления листов и др.

Вкладка Вставка позволяет строить диаграммы и графики, вставлять рисунки, фигуры, клипы, художественные надписи, колонтитулы и т.п. Например, команда вкладки Вставка\Текст\Колонтитулы вставляет в листколонтитул (текст на верхнем/нижнем поле и\или картинкой, выводимыми на каждой странице) с указанием номера страницы.

При оформлении отчёта в виде бланка фирмы можно с помощью команды меню Вставка\Рисунок вставить в лист подходящую картинку – знак фирмы, ввести название, адрес и телефоны фирмы.

Ввод данных и формул

Ввод данных. Ввести данные можно непосредственно в активную ячейку, либо набрав их в строке формул и нажав клавишу E nter. Если текст не помещается в активной ячейке, он автоматически распространяется на соседнюю ячейку. Если не помещается чиcло - ячейка заполняется символами "#".

Ввод формул. Помимо различных типов данных ячейки таблицы Excel могут содержать формулы. Любая формула в Excelначинается со знака «=»!

В формулах допустимо использование следующих операторов: вычитание (-), деление(/), умножение (*), возведение в степень (^), <, >, <>(не равно), => (больше или равно), <= (меньше или равно) и, так называемых операторов связи - диапазон (:), объединение (;) и объединение текстов (&). Для ввода формулы необходимо:

1) выделить ячейку и нажать клавишу «=» (равно);

2) набрать формулу и нажать Enter. Например: =2*6-9 или =А1*5-ВЗ/2+С4^2.

Первая формула содержит только константы и знаки арифметических

операций. Вторая формула использует ссылки на ячейки А1, ВЗ и С4. Содержимое этих ячеек подставляется в формулу и после нажатия клавиши Enter вычисляется результат. Таким образом, в ячейке показывается результат вычисления формулы, а саму формулу можно увидеть и/или отредактировать в строке формул, предварительно выделив нужную ячейку. Если щёлкнуть по ячейке дважды, то формулу можно изменить и в самой ячейке. Следует заметить, что ссылки на ячейки во второй формуле можно набрать не только с клавиатуры, а также с помощью мыши. Для этого после нажатия клавиши «=» щелкните мышью в ячейке А1 (в строке формул автоматически появится А1), затем наберите *5, щелкните мышью в ячейке ВЗ и т.д.

Формулы могут содержать ссылки не только на ячейки, но и блоки ячеек, например =А1:А4+С1:С4, что равносильно формуле =А1+А2+АЗ+ А4+С1+С2+СЗ+С4. Кроме того, в формулах можно использовать функции.

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

Рассмотрим наиболее распространенную функцию суммирования, с помощью которой приведенные выше формулы можно записать так:

=СУММ(А1:А4;С1:С4) или

=СУММ(А1;А2;АЗ;А4;С 1;С2;СЗ;С4);

Кнопка Сумма (значок Σ) на вкладке Главная\ Редактирование облегчает использование функции СУММ. Пусть требуется вычислить сумму чисел в четырёх ячейках для каждого из столбцов А, В, С. Выделите ячейку А5, нажмите кнопку Сумма в менювкладки Главная, затем E nter. Сумма чисел первого столбца =СУММ(А1:А4) появится в ячейке А5. Аналогичную операцию можно повторить для столбцов В и С.

Однако удобнее применить следующий прием: выделите ячейку А5 с записанной функцией =СУММ(А1:А4), установите указатель мыши на маркер в нижнем правом углу ячейки (указатель примет форму жирного плюса) и, удерживая левую кнопку мыши, растяните рамку на соседние ячейки В5 и С5 вправо. После того, как левая кнопка мыши будет отпущена, в ячейках В5 и С5 появятся результаты суммирования соответствующих столбцов. Обратите внимание, что в ячейке В5 будет находиться формула = СУММ (В1: В4), а в ячейке С5 – формула СУММ (С1:С4), т.е. адреса ячеек автоматически изменились.

Такой вид адресации ячеек называется относительной адресацией. Для того, чтобы при копировании адреса ячеек, входящих в формулы не менялись, необходимо зафиксировать строку и\или столбец символом $, т.е. использовать абсолютную адресацию. Формула суммирования ячеек в столбце А с абсолютной адресацией будет выглядеть так:

=СУММ($А$1:$А$4) и при копировании в ячейки В5 и С5 эта формула не изменится. Если убрать знак $ перед буквой столбца (А) или цифрой строки, то соответствующий элемент будет адресоваться относительно, т.е. изменяться при копировании.

Предположим, что в ячейке А6 необходимо вычислить, какой процент

составляет число в ячейке А1 от общей суммы в ячейке А5. Это можно сделать двумя способами:

1. В ячейку А6 введите формулу= А1/А5*100

2. В ячейку А6 введите формулу = А1/А5 и присвойте ячейке А6 формат Процентный командой меню Главная\ раздел Число\Процентный формат (Ctrl + Shift + %) или откройте список Общий и выберите Процентный.

При этом результат вычислений будет умножен на 100 и рядом с ним появится знак %.

Форматирование ячеек

Для удобства представления данных в Eexcel применяются различные форматы ячеек (числовой, денежный, финансовый, процентный, дата, время и т.д.). Формат влияет только на внешнее представление данных в ячейке – таблица 1.

Таблица 1. Примеры форматов:

Содержимое ячейки	Формат	Результат
1234,567	Числовой	1 234,567
1234,567	Денежный	1 234,567р.
1234,567	Процентный	123456,7%
1234,567	Экспоненциальный	1,235Е+03
123456	Время	12:34:56

В экспоненциальном формате символ Е означает основание системы счисления, в данном случае – число 10.

Присвоить формат ячейке или блоку ячеек, предварительно выделив их, можно с помощью команды Ячейки из вкладки Главная\Число \ список Общий (рисунок 4) или нажав правую кнопку мыши и выбрав команду Формат ячеек..., или из Главная \ Ячейки \ Формат\ Формат ячеек....

В понятие формат ячейки входят также её шрифт (тип, размер, цвет используемого шрифта), выравнивание (расположение текста в ячейке по вертикали и горизонтали), границы (толщина и цвет линий обрамления), вид (узор и цвет заполнения), защита (установка ограниченного доступа и пароля), которые задаются на вкладках Число, Выравнивание, Шрифт, Граница, Заливка в диалоговом окне Формат ячеек, через список Стили ячеек в разделе Стили (рисунок 1.5) или с помощью кнопок на панели инструментов.

Рисунок 1.5 – Группы Число и Стили вкладки Главная

После того, как таблица создана, ее необходимо отформатировать.

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

В Excel может быть открыто несколько файлов рабочих книг одновременно, которые накладываются друг на друга. Переход из одного окна в другое осуществляется с помощью меню вкладки Вид \ Окно\Перейти в другое окно – рисунок 1.6.

Рисунок 1.6 – Группа Окно вкладки Вид

Контрольные вопросы

1 Как выделить ячейку, блок ячеек, несвязанные блоки, строку, столбец таблицы?

2 Как сделать копию с листа и дать ей другое имя?

3 Как скопировать данные таблицы с одного рабочего листа на другой?

4 Что входит в понятие «формат ячейки»? Как присвоить формат ячейке? Приведите примеры различных форматов.

5 Как изменить ширину и высоту ячейки?

6 Каковы правила ввода и редактирования данных в Excel?

7 Каковы правила ввода и редактирования формул в Excel?

8 Что такое относительная и абсолютная адресация?

9 Как отформатировать таблицу Excel?

10 Как подготовить отчет для печати?

11 Как внедрить логотип на рабочем листе Excel?

12 Что такое колонтитул и как он вводится в лист?

Задание

1 На Листе 1 разработайте таблицу согласно варианту задания (используйте условные данные). Введите необходимые формулы различными способами ввода формул.

2 Создайте две копии разработанной таблицы, скопировав ее по частям (ячейками, столбцами, строками, интервалами ячеек).

Все остальные операции производите над копиями.

3 Отформатируйте первую копию таблицы вручную, используя:

- форматы данных(числовой, денежной, процентный и др.);

- обрамление, узор и заполнение ячеек;

- различные форматы шрифтов;

- выравнивание абзацев:

- изменение высоты строк и ширины столбцов.

В результате каждая ячейка таблицы должна быть отформатирована

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

4 Отформатируйте вторую копию таблицы, используя команду Форматировать как таблицу из вкладки Главная\Стили стилем Средний13.

5 На Листе 2 подготовьте к печати одностраничный отчет по лабораторной работе.

С этой целью:

- сделайте копию с Листа 1и переименуйте егов Отчёт 1,

- удалите таблицу-оригинал и - таблицу (копия –2), отформатированную стилем Средний13

- оформите рабочий лист с оставшейся таблицей, отформатированной вручную в виде фирменного бланка, внедрив в левом верхнем углу листа логотип с помощью команды меню вкладки Вставка\Рисунок и указав название, адрес и телефон фирмы,

- создайте нижний колонтитул, включающий номер страницы в центре, а справа и слева - фамилии авторов отчета.

В итоге на на Листе 1 должны быть:

- таблица-оригинал (основная таблица)

- таблица (копия –1), отформатированная вручную,

- таблица (копия –2), отформатированная стилем Средний13.

На листе Отчёт 1:

- бланк отчёта, подготовленный для печати с таблицей (копия 2) в центре, адресом и телефоном фирмы, её логотипом, колонтитулами и др...

Варианты заданий

Вариант 1 Сравнительная таблица розничных цен на продовольственные товары по городам Северного Кавказа (руб. за 1 кг)

Товар	Краснодар	Ростов	Ставрополь	Майкоп	Нальчик	Средняя цена
Говядина
Свинина
Птица
Рыба

В ячейки столбца «Средняя цена» ввести любым способом формулы для вычисления среднего значения содержимого ячеек соответствующей строки.

Вариант 2 Данные о продаже автомобилей в 20__г.

Марка	1 квартал	%	2 квартал	%	3 квартал	%	4 квартал	%
БМВ
Форд
Ауди
Рено
Всего

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

проданных в данном квартале машин из ячеек строки «Всего».

Вариант 3 Температура воздуха в городах мира с 1 по7 января 20__г.

Дата	Москва	Рим	Париж	Лондон	Мадрид
01 янв 2000
02.01.2000
03.1.2000
04.01.2000
5 янв
6.01
07.01.2000 13:30
Средн. темп -ра
Общая средняя. по всем городам

В ячейке строки «Средняя температура» ввести формулу для вычисления среднего значения температуры в столбце, в строку «Общая средняя по всем городам» – формулу для вычисления среднего от содержимого ячеек строки «Средняя температура».

Вариант 4 Перечень доходов и расходов за 1-е полугодие.

Статья \ Месяц	Янв.	Февр.	Март	Апр.	Май	Июнь
Доходы
Оклад
Премия
Совместительство
Всего доходов
Расходы
Жилье
Кредит
Питание
Налоги
Другие расходы
Всего расходов
Остаток

Остаток за полугодие

В ячейки строки «Всего доходов» и «Всего расходов» ввести формулы, вычисляющие сумму значений ячеек соответствующего столбца, в ячейках строки «Остаток» должны быть формулы, вычисляющие разность содержимого ячеек строк «Всего доходов» и «Всего расходов», в строке

«Остаток за полугодие» – сумма содержимого ячеек строки «Остаток».

Вариант 5. Выручка от продажи книжной продукции в 20__ г.

Название книги	1 кв.	2 кв.	3 кв.	4 кв.	Процент за год
1.
2.
3.
4.
5.
6.
Всего за год

В ячейках столбца «Процент за год» и строки «Всего за год» должны быть записаны соответствующие формулы

Вариант 6 Итоговые экспертные оценки (от 0 до 10 баллов) кандидатов на должность генерального директора фирмы «Аква».

Номер кандидата	Эксперты				Сумма баллов	Процент
Номер кандидата	1	2	3	4	Сумма баллов	Процент
№1
№2
№3
№4
№5
Всего:

В столбце «Процент» вычисляется процентное отношение ячеек столбца «Сумма баллов» от значения ячейки строки «Всего».

Вариант 7 Прайс-лист фирмы «Трианон» на 22 декабря 20__ г.

Наименование товара	Рознич. цена, руб.	От 10 шт.	От 100 шт.	Свыше 1000 шт.	Диллеры
1.
2.
3.
4.
5.
6.

Формулы в ячейках столбцов «От 10 шт.... Диллеры» должны учитывать, что цена уменьшается на 1 процент по сравнению с предыдущей

колонкой.

Вариант 8 Результаты зимней сессии студентов группы __-Эк- 52

Фамилия И.О.	Математика	Информатика	Иност.. язык	Маркетинг	Сумма	Средн. балл
1.
2.
3.
4.
5.
6.

В столбцах «Сумма» и «Средний балл» должны быть введены соответствующие формулы.

Вариант 9 Анализ цен на товары в конкурирующих фирмах.

Наименование товара	Название фирмы
Наименование товара	ВЛАДОС	ОК	Контраст
1.
2.
3.
4.
5.
Сумма, руб.

Вариант 10 Изменение курса валют за период 1 по 5 марта 20__г.

Дата	Доллар	Рубль	Доллар/Рубль
1 мар 2000
2 мар 2000
3 мар 2000
4 мар 2000
5 мар 2000
Средний за период
Процент роста

В столбце «Доллар/ Рубль» должна быть формула для вычисления отношения курсов доллара и марки в соответствующей строке. В строку «Средний за период» вводятся формулы для вычисления среднего

значения в столбце, в строку «Процент роста» - формулы для определения процентного прироста курса на5 марта относительно курса на 1 марта 20__г.

Вариант 11 Сравнительная таблица розничных цен

Товар	Краснодар	Ростов	Ставрополь	Майкоп	Нальчик	Средняя цена
Цемент
Алебастр
Толь
Битум

Вариант 12 Данные о продаже автомобилей в 20__г.

Марка	1 кварт	%	2 кварт	%	3 кварт	%	4 кварт	%
Волга
Москвич
Жигули
Газель
Бычок
Всего

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

Вариант 13 Температура воздуха в городах мира с 1 по 5 января 200__г.

Дата	Вена	Рим	Париж	Лондон	Осло
01.01. 20__
02.01.20__
03.1.20__
04.01.20__
05.01.20__
Средн. темп -ра
Общая средняя. по всем городам

В ячейкм строки «Средняя температура» ввести формулу для вычисления среднего значения температуры в столбце, в строку «Общая средняя по всем городам» – формулу для вычисления среднего от содержимого

ячеек строки «Средняя температура».

Вариант 14 Перечень доходов и расходов за 1-е полугодие.

Статья \ Месяц	Янв.	Февр.	Март	Апр.	Май	Июнь
Доходы
Оклад
Премия
Прибыль
Всего доходов
Расходы
Жилье
Кредит
Питание
Налоги
Всего расходов
Остаток
Остаток за полугодие

Вариант 15. Выручка от продажи книжной продукции в 20__ г.

Название книги	1 кв.	2 кв.	3 кв.	4 кв.	% за год
1.
2.
3.
4.
5.
6.
Всего за год

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

Вариант 16 Итоговые экспертные оценки (от 0 до 10 баллов) кандидатов на должность генерального директора фирмы «Аква».

Номер кандидата	Эксперты				Сумма баллов	Процент
Номер кандидата	1	2	3	4	Сумма баллов	Процент
№1
№2
№3
№4
№5
Всего:

Вариант 17 Прайс-лист фирмы «Трианон» на 22 декабря 20__ г.

Наименование товара	Рознич. цена, руб.	От 10 шт.	От 100 шт.	Свыше 1000 шт.	Диллеры
1. Шторы
2. Занавес
3. Карнизы
4.Ролл-ставни
5.
6.

Формулы в ячейках столбцов «От 10 шт.... Диллеры» должны учитывать, что цена уменьшается на 1 процент по сравнению с предыдущей колонкой.

Вариант 18 Результаты зимней сессии студентов группы 0_-Эк- 52

Фамилия И.О.	Математика	Информатика	Иност.. язык	Маркетинг	Сумма	Средн. балл
1.
2.
3.
4.
5.
6.

В столбцах «Сумма» и «Средний балл» должны быть введены соответствующие формулы.

Вариант 19 Анализ цен на товары в конкурирующих фирмах.

Наименование товара	Название фирмы
Наименование товара	Санрайз	ОК	Иманго
1.
2.
3.
4.
5.
Сумма, руб.

Вариант 20 Изменение курса валют за период 1 по 5 марта 20__г.

Дата	Доллар	Евро	Доллар/Евро
1 мар 20__
2 мар 20__
3 мар 20__
4 мар 20__
5 мар 2000
Средний за период
Процент роста

В столбце «Доллар/Евро» должна быть формула для вычисления отношения курсов доллара и марки в соответствующей строке. В строку «Средний за период» вводятся формулы для вычисления среднего значения в столбце, в строку «Процент роста» - формулы для определения процентного прироста курса на 5 марта относительно курса на 1 марта 20__г.

Содержание отчёта

1Название, цель, содержание работы

2 Задание своего варианта

3 Письменные ответы на контрольные вопросы

4 Выводы по работе

На своём носителе (флэшке) должны быть сохранены таблицы и фирменный бланк.

Практическое занятие №2

Создание и редактирование диаграмм и графиков

Цель работы: научиться создавать и редактировать графики и

диаграммы средствами редактора Word

Содержание работы:

1 Построение двумерных и трехмерных диаграмм

2 Редактирование диаграмм

3 Построение других типов диаграмм