Построение графиков, поверхностей и диаграмм в Excel

Цель работы: научиться строить и редактировать графики и диаграммы по таблице исходных данных.

Задание 1. Составьте таблицу расчета доходов фирмы в абсолютном и процентном отношении и диаграмму роста доходов на основе данных о доходах фирмы.

 

Месяцы	Уровень доходов фирмы в 1998 г., млн. руб.	Уровень доходов фирмы в 1999 г., млн. руб.	Рост уровня доходов фирмы в 1999 г., %
Январь
Февраль
Март
Апрель
Май
Июнь
Июль
Август
Сентябрь
Октябрь
Ноябрь
Декабрь
Всего:

Методические указания

· Составить таблицу расчета доходов фирмы: определить тип, размер и стиль шрифтов для заголовков строк и столбцов: Times New Roman Cyr, размер 12, стиль полужирный; для остального текста - Times New Roman Cyr, размер 10, стиль обычный.

· Вычислить рост уровня доходов фирмы в процентном отношении в каждом месяце 1999 г. по отношению к январю 1999 г. (3-й столбец таблицы): =(Ci-C$3)/C$3, где Сi – адрес ячейки i-го месяца графы Рост уровня доходов; С$3 – абсолютный адрес ячейки Уровень доходов фирмы за январь 1999 г.

· Вычислить суммарный уровень доходов фирмы за 1999 и 1998 гг., результаты поместить в последней строке второго и третьего столбца соответственно.

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

· Построить диаграмму зависимости уровня доходов фирмы за 1999 и 1998 гг. по месяцам в виде гистограммы.

· Построить диаграмму зависимости уровня доходов фирмы в процентном отношении в виде линейного графика.

· Построить совмещенную диаграмму (тип нестандартная/график|гистограмма 2) по данным полученной таблицы (второй, третий и четвертый столбцы).

· Рассмотреть другие типы диаграмм, освоить редактирование элементов диаграмм.

Задание 2. Составьте круговую диаграмму с отображением среднего балла по предметам на основании таблицы "Итоги экзаменационной сессии"

№ п/п	Ф. И.О.	Математика	Экономическая теория	Информатика
1.	Макаров С.П.
2.	...	...
3.
…
Средний балл

Задание 3. Постройте график функции y=sin x. Значение аргумента х выбрать в пределах от –6 до 6 с шагом 0,5.

 

Методические указания

Построим таблицу следующего вида:

 

X	-6,0	-5,5	-5,0	...
Y	0,28	0,71	0,96	…

 

· Для чего заполним значениями строку Х путем протягивания. В строку Y вставим формулу =Sin(B1) и протянем до конца таблицы.

· Затем выделим построенный диапазон и на панели стандартная нажмем кнопку Мастер диаграмм. Выберем тип диаграммы – график.

Форма отчета: документ в формате.xls.

 

Лабораторная работа № 4

Построение и обработка списков (баз данных)

Цель работы: научиться работать с базами данных. Применять к ним формы, сортировку, фильтр, автофильтр.

Задание 1. Создать телефонный справочник. Его пример приведен ниже.

 

Телефон	Фамилия И.О.	Адрес
212-50-74	Котин У.Г.	пр.Рокоссовского 3-73
250-33-44	Андреев А.А.	пр.Пушкина 23-33
222-30-49	Борисов Д.А.	ул.Плеханова 5-113
226-30-69	Борисевич Г.Н.	ул.Плеханова 12-13
232-43-50	Андреев Б.С.	ул.Сердича 13-89
233-63-48	Антонов А.Н.	пр.Партизанский 7-45
257-50-29	Кукин Б.И.	ул.Серова 17-89
243-73-84	Яшин Р.А.	ул.Жилуновича 30-16

 

Методические указания

· Создать название, заголовки таблицы и границы.

· Заполнить 5 записей обычным способом.

· Ввести 3 записи в режиме формы (меню Данные/Форма).

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

Задание 2. При помощи команды Данные/Форма/Критерии просмотрите записи списка, удовлетворяющие следующим условиям:

а) владельцев телефонов, фамилии которых начинаются на букву А;

б) владельцев телефонов, проживающих на проспектах;

в) владельцев телефонов, номера телефонов которых > заданного номера.

 

Задание 3. Выполните сортировку справочника:

а) по возрастанию номеров телефонов;

б) по алфавитному порядку фамилий.

Задание 4. Выделите записи из справочника при помощи автофильтра (меню Данные/ Фильтр/ Автофильтр):

а) выделите записи, у которых номер телефона больше 250-50-50 и меньше 270-50-50;

б) затем среди выделенных записей выделите записи, в которых фамилии начинаются с буквы П;

в) отобразите все записи списка;

г) отобразите записи, в которых улица или проспект начинается с буквы “П”;

д) отобразите записи, у которых номер квартиры заканчивается числом 13;

е) выделить записи, у которых номер телефона содержит во второй группе цифры 50 или 30, например- 260- 50 -40,

ж) затем среди выделенных записей выделите записи, в которых фамилия начинается с букв “Ан”.

 

Задание 5. Создайте список (табличную базу данных) реализации товаров следующего вида.

 

 

Фирма	Продукция	Месяц	Стоимость
Колос	Хлеб	Январь
Колос	Батон	Январь
Колос	Батон	Февраль
Атлант М	ВАЗ-21009	Январь
Атлант М	ВАЗ-2111	Январь
Атлант М	ВАЗ-21009	Март
Горизонт	Телевизор	Февраль
Горизонт	Телевизор	Март
Горизонт	Телевизор	Апрель

 

Задание 6. При помощи команды Данные/Итоги подведите промежуточные итоги в стоимостном выражении:

а) по фирмам;

б) по месяцам среди всех фирм;

в) по продукции среди всех фирм.

 

Задание 7. Постройте диаграмму (одну), показывающую изменение стоимости реализации товаров по месяцам для каждой фирмы.

 

Форма отчета: документ в формате.xls.

 

 

Лабораторная работа № 5

Консолидация рабочих таблиц

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

Под термином консолидация подразумевается ряд стандартных операций с несколькими рабочими таблицами и рабочими книгами. В некоторых случаях консолидация может включать в себя создание связанных формул. Основной фактор, влияющий на консолидацию данных – это способ размещения информации в рабочих таблицах. Если размещение информации во всех таблицах одинаково, то говорят о консолидации по позиции. В том случае, когда размещение информации не идентично, но достаточно похоже, то можно объединить данные по заголовкам строк и/или столбцов. Такая консолидация называется консолидацией по категориям. Если же рабочие таблицы имеют мало общего друг с другом, то необходимо отредактировать листы, чтобы они стали единообразными.

 

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

 

Филиал № 1
Название товара	Январь	Февраль	Март
А-995
В-123
А-143
В-123
С-070
Д-060
Е-130
Ф-270
Т-234
М-235

Филиал № 2
Название товара	Январь	Февраль	Март
Т-234
В-123
Р-234
А-143
В-123
С-070
Д-060
Е-060
Ф-270
У-111
К-254

 

Филиал № 3
Название товара	Январь	Февраль	Март
А-995
В-123
А-143
Р-234
В-123
С-070
Д-060
Е-130
Ф-270
К-254

 

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

Методические указания

Для выполнения данного задания необходимо:

· Создать рабочие таблицы на различных листах рабочей книги(например на листах с первого по третий). Часть записей скопировать из данного документа. Добавить не менее пяти записей в каждую рабочую таблицу так, чтобы в таблицах были записи с одинаковым названием товара.

· Создать новую рабочую книгу (выберите новый рабочий лист), где должны размещаться результаты консолидации. Выполнить команду Данные / Консолидация.

· Задание параметров для диалогового окна «Консолидация»:

- в поле «Функция» указать функцию Сумма, которая показывает тип объединения данных;

- в поле «Ссылка» ввести ссылку на диапазон первой рабочей таблицы, которая должна быть консолидирована. Если нужная книга закрыта, щелкните по кнопке «Обзор», чтобы найти нужный файл на диске. Ссылка может задавать диапазон больший, по числу строк, чем нужно консолидировать, но в случае добавления новых строк, параметры консолидации не нужно будет изменять. Когда в поле «Ссылка» будет введена нужная ссылка, щелкните по кнопке «Добавить», чтобы добавить ее к списку диапазонов;

- ввести ссылку на диапазон второй рабочей таблицы и добавить ее к списку диапазонов. Выполнить указанное действие для остальных диапазонов консолидации;

- так как способы размещения информации в рабочих таблицах различны, установить опции Подписи верхней строки и Значения левого столбца. В результате Excel будет подбирать данные по заголовкам;

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

· Построить требуемую диаграмму.

 

Форма отчета: документ в формате.xls.

 

 

Лабораторная работа № 6

Сводные таблицы

Цель работы: научиться создавать сводные таблицы, производить в них группировку элементов и создавать диаграммы.

Сводные таблицы предназначены для обобщения (объединения, переработки) информации, хранящейся в базе данных. Они также позволяют отображать табличные данные в виде двухмерной или трехмерной таблицы. Кроме того, с их помощью можно вывести промежуточные итоги с любым уровнем детализации.

Сводная таблица может быть создана на основании данных, находящихся:

- в списке или базе данных Microsoft Excel;

- во внешнем источнике данных;

- в нескольких диапазонах консолидации;

- в другой сводной таблице.

Каждая сводная таблица состоит из 4 областей: страница, строка, столбец, данные (рис. 2).

 

Рис. 2.

 

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

- Cтрока. Уникальные значения полей, помещенных в эту область, используются в качестве заголовков строк в сводной таблице. Если в эту область помещено одно поле, то количество строк в сводной таблице (без учета итогов) равно числу уникальных значений этого поля.

- Столбец. Уникальные значения полей, помещенных в эту область, используются в качестве заголовков столбцов в сводной таблице. Если в эту область помещено одно поле, то количество столбцов в сводной таблице (без учета итогов) равно числу уникальных значений этого поля.

- Данные. Значения полей, помещенных в эту область, используются для заполнения ячеек сводной таблицы итоговыми данными (суммирование, подсчет количества, вычисление среднего значения и т.д.).

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

Задание 1. На основании следующей таблицы

 

Менеджер	МЕСЯЦ	Продукты	Доход	Расход	Прибыль	Регион
Иванов	Янв.	мясо	100,00	50,00		Страны СНГ
Иванов	Фев.	мясо	100,00	50,00		Россия
Иванов	Фев.	мясо	100,00	50,00		Россия
Иванов	Апр.	мясо	100,00	50,00		Россия
Иванов	Апр.	мясо	100,00	50,00		Россия
Петров	Янв.	мясо	100,00	50,00		Страны СНГ
Петров	Фев.	мясо	100,00	50,00		Страны СНГ
Петров	Фев.	мясо	100,00	50,00		Страны СНГ
Петров	Апр.	мясо	100,00	50,00		Страны СНГ
Петров	Апр.	мясо	100,00	50,00		Страны СНГ
Сидоров	Май	рыба	100,00	50,00		Страны СНГ
Сидоров	Янв.	рыба	100,00	50,00		Россия
Иванов	Фев.	рыба	100,00	50,00		Россия
Иванов	Март	молоко	200,00	20,00		Россия
Петров	Март	молоко	300,00	30,00		Страны СНГ
Сидоров	Март	молоко	150,00	100,00		Страны СНГ

 

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

Методические указания

· Рассчитайте значение поля «Прибыль», записав соответствующую формулу.

· Сделайте текущей любую ячейку построенного списка.

· Выполните команды Данные и Сводная таблица.

· Установите флажок - В списке или базе данных Microsoft Excel.

· Укажите диапазон, содержащий построенный список. Если список был построен правильно, нужный диапазон будет выбран автоматически.

· Перетащите кнопки «Продукция» и «Менеджер» в область «Строка». При этом важен порядок перетаскивания – поле «Менеджер» будет вложенным по отношению к полю «Продукция». Затем в область «Столбец» перетащите кнопку «Месяц» и в область страниц – кнопку «Регион». В область данных перетащите кнопку «Прибыль».

· Укажите место размещения сводной таблицы.

Построенная сводная таблица будет иметь следующий вид (рис. 3).

Рис. 3.

 

Форма отчета: документ в формате.xls.

 

 

ЗАКЛЮЧЕНИЕ

 

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

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

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

 

Литература:

1. Excel 7.0 для Windows 95. M. Додж, К. Кината, К. Стинсон. Microsoft Press, 1997.

2. “ Microsoft PowerPoint одним взглядом “, Старшинин Александр, СПб, 1996.

 

Учебно-практическое издание

 

____________________________

 

Драка Оксана Евгеньевна

 

Методические указания к лабораторным работам по курсу

«Программные среды электронного офиса»

 

Редактор Ж.В. Паршина

Подписано в печать 26.10.2004. Формат 60х84 ¹/₁₆.

Бумага тип №2. Плоская печать. Печ. п. 1,39.

Уч.-изд. П. 1,16. Тираж 100.

_____________________________

 

Южно-Российский государственный технический университет

346428, Новочеркасск, ул. Просвещения, 132.

Типография ВИ(ф) ЮРГТУ, ул. Ленина, 73/94.