Лабораторная работа по теме "Расширенные возможности процессора электронных таблиц Microsoft Office Excel"
методическая разработка по информатике и икт по теме

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

Расширенные возможности процессора электронных таблиц Microsoft Office Excel

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

Методика выполнения работы

Работа с макросами

Макрос — это программа, автоматизирующая выполнение различных часто повторяющихся операций. Понятие макроса и управление его записью и применением аналогичны рассмотренным в текстовом процессоре Word.

Основной задачей пользователя является аккуратное выполнение требуемой последовательности операций в реальной таблице при включенном макрорекордере. Макрорекордер преобразует выполняемые действия в последовательность команд языка VBA.

Работая с книгой Excel, пользователь может записать несколько макросов. Все они сохраняются в VBA-модуле. Запуск макроса выполняется одним из трех способов:

1. с помощью "горячих" клавиш;
2. посредством выбора имени макроса в меню;
3. щелчком мыши по графическому объекту, связанному с макросом.

Начало записи макроса 

1. Обратитесь к меню Сервис/Запись макроса.../Начать запись... .
2. В окне "Запись макроса" укажите имя макроса.
3. Если запуск макроса планируется через меню или "горячие" клавиши, то нажмите кнопку "Параметры" и задайте имя пункта меню или (и) сочетание клавиш.
4. Нажмите "Ok". Признак начала записи — появление небольшого окна с кнопкой "Остановить запись".

Запись макроса 

Начинать запись макроса следует после предварительного планирования действий. Рекомендуется потренироваться в выполнении записываемых операций без запуска макрорекордера. Если при записи выполнено неверное действие, следует отменить его, нажав кнопку "Отменить" на основной панели инструментов.

Завершение записи 

1. Нажмите кнопку "Остановить запись".
2. Если запуск макроса планируется с помощью графического объекта, то выберите требуемый объект на панели "Формы" и разместите его на рабочем листе. Для вызова указанной панели следует обратиться к меню Вид/Панели инструментов... .После размещения объекта типа "Кнопка" откроется окно "Назначить макрос объекту", в котором требуется указать имя макроса и нажать "Ok". Для других объектов необходимо щелкнуть по нему правой кнопкой мыши, после чего из выпадающего меню следует выбрать пункт "Назначить макросу" и указать связь с макросом.
3. Опробуйте действие макроса, предварительно восстановив исходное состояние таблицы.

Пример записи макроса

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

1. Выделить диапазон клеток, в котором задается формат. Важно сделать это до запуска макрорекордера, а не после, поскольку макрос должен быть применим для любого выделенного участка. В противном случае он будет форматировать только один диапазон.
2. Запустить макрорекордер. 
3. Обратиться к меню Формат/Ячейки... и на вкладке "Число" выбрать числовой формат и "Число десятичных знаков" — 1.
4. Щелкнуть мышью на любой ячейке, чтобы снять выделение диапазона.
5. Нажать кнопку "Остановить запись".
6. Вызвать панель "Элементы управления", щелкнуть по объекту "Кнопка" и, установив указатель мыши на свободное место листа, "растянуть" объект. Сменить надпись на кнопке можно двойным щелчком мыши по стандартной надписи, но если объект выделен. Выделяется объект правой кнопкой мыши и нажатием клавиши Esc для закрытия всплывающего меню форматирования объекта.
7. В окне "Назначить макрос объекту" указать имя макроса, связываемого с кнопкой и нажать "Ok".
8. Щелкнуть мышью вне объекта "Кнопка", чтобы подготовить макрос к работе.
9. Восстановить исходные данные и опробовать работу макроса для различных выделяемых диапазонов.

На рисунке показан диапазон C1:E2, преобразованный в требуемый формат макросом, запускаемым кнопкой с надписью "Десятичный формат".

Построение трендовых моделей при помощи диаграмм

Многие экспериментальные данные можно интерпретировать как временные ряды - последовательность измерений, полученных в определенные моменты времени ti, где i - порядковый номер измерения на оси времени. Такие ряды характеризуются некоторой тенденцией развития процесса во времени и называются трендовыми. Используя трендовые модели, можно выдавать прогнозы на краткосрочный и среднесрочный периоды. Excel имеет средства для создания трендовых моделей встроенные в построитель диаграмм.

Одной из форм трендовых моделей при постоянном шаге по времени является линейная:

В качестве примера используем данные об авиаперевозках в США с 1949 по 1960 годы. Пусть требуется предсказать объем авиаперевозок на 1961 год. Знание этого объема позволяет планировать развитие авиационной промышленности и инфраструктуры, связанной с авиаперевозками. Исходные данные приведены в таблице.

Порядок расчетов следующий.

1. Выделить диапазон B2:B13 и построить по этим данным диаграмму типа "График", щелкнув по значку "Мастер диаграмм" на панели инструментов.
2. Выделить диаграмму и выполнить Диаграмма/Добавить линию тренда.

3. В окне "Линия тренда" открыть вкладку "Параметры" и установить флажки "Показывать уравнение на диаграмме" и "Поместить на диаграмму величину достоверности аппроксимации".

1. На вкладке "Тип" выбрать тип диаграммы – линейная и нажать Ok. Результаты показаны на рисунке.

1. Вычислить по формуле y = 383,09x + 873,52. Следует учесть, что аргументом трендовой модели является порядковый номер, т.е. в нашем примере x=13. В результате получим прогноз на 1961 год: 5853,69 тысяч пассажиров.

Следует заметить, что мы, скорее всего, получили заниженный прогноз. Это видно из диаграммы и обусловлено выбором линейной модели прогноза. Возможно, что более точный прогноз был бы получен с помощью степенной или экспоненциальной линий тренда. Оценить качество прогноза можно только в конце 1961 года. В целом прогноз следует делать весьма осторожно – возможны большие ошибки. Именно поэтому чаще всего используются краткосрочные и среднесрочные прогнозы.

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

Задание к работе

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

Порядок выполнения работы

1. Работа с несколькими листами:

1. Создать лист с именем «Исходная» с таблицей. Таблицу выбрать согласно варианту в пункте «Варианты заданий к работе». Номер своего варианта узнать у преподавателя.
2. В произвольной пустой ячейке этого листа введите число равное номеру вашего варианта.
3. Создать лист «Сводная» с таблицей, изображенной в пункте «Сводная таблица» «Вариантов заданий к работе».
4. Добавить к сводной таблице столбец с именем «Кол-во1». Значения в нем должны быть заполнен числами равными исходным значениям столбца «Кол-во», сложенным с ячейкой, содержащей номер варианта на листе «Исходная»;

2. Работа со сводными таблицами:

1. На основе этой таблицы создайте отчёт сводной таблицы, используя меню «Данные – Сводная таблица…».
2. Следовать указаниям «Мастера сводных таблиц», указав источник данных и место размещение результата;
3. разместить поля по областям следующим образом:

Страница – «Квартал»

Строка – «Инв. №»

Столбец – «Канал»

Данные – сумма по полю «Кол-во1»

4. Используя переключатели в отчёте сводной таблицы, найти:

1. Общее количество заказов по почте в 1 квартале;
2. Общее количество проданных изданий № 30782;
3. Количество изданий № 26059, проданных по международным каналам;
4. Общее количество изданий, проданных во втором квартале.

5. Отобразить все значения по всем полям сводной таблицы;
6. Построить отчёт сводной диаграммы для всех изданий, проданных по международным каналам;
7. Отобразить все значения по всем полям сводной таблицы;
8. Построить отчёт сводной диаграммы для издания № 41210;
9. Отобразить все значения по всем полям сводной таблицы;
10. Приведите отчёт сводной таблицы к структурированному формату, используя автоформат «Отчёт4».

3. Создание и выполнение макросов:

1. Перейти на лист «Исходная» и выделить на нем произвольный диапазон числовых ячеек.
2. Произвести запись макроса, выполняющего установку полужирного курсивного шрифта в любом выделенном диапазоне ячеек;
3. Добавить на лист «Исходная» элемент управления «Кнопка» через меню панель инструментов «Формы» в меню «Вид»;
4. Назначить макрос элементу управления «Кнопка» с надписью «Полужирный+Курсив»

4. Построение трендовых моделей:

1. Создать новый лист с именем «Трендовая модель»;
2. Создать на листе таблицу, содержащую две колонки: «Код» и «Кол-во1». Колонка Код заполняется, начиная с числа равного номеру вашего студенческого, шаг заполнения +1. Колонка «Кол-во1» – содержит ссылки на ячейки в одноименной колонке на листе «Сводная».
3. Построить трендовую модель по данным получившейся таблицы;
4. По уравнению получившейся трендовой модели в отдельной ячейке вычислить значение столбца «Кол-во1» для кода, превышающего последнее значение столбца Код на 10.

5. Доложить преподавателю об окончании выполнения работы;

Требование к защите работы

1. Отчет о выполнении лабораторной работы;
2. Конспект порядка выполнения лабораторной работы.
3. Знать ответы на ВСЕ контрольные вопросы.

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

1. Название и цель лабораторной работы;
2. Отчет сводной диаграммы, отображающий общее количество проданных изданий № 30782 выполненных в П.2 «Порядка выполнения работы».
3. График построения трендовой модели с результатами прогнозирования по П.4. выполнения работы»;
4. Выводы.

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

1. Что такое трендовая модель?
2. Для чего используются трендовые модели?
3. Как можно создать трендовую модель в Excel?
4. Что показывает коэффициент достоверности аппроксимации R2?
5. Какие прогнозы: краткосрочные, среднесрочные или прогноз на длительный срок используются чаще? Почему?
6. Как оценить степень соответствия трендовой модели исходным данным?
7. Что такое сводная таблица?
8. Что такое макрос?
9. Каково назначение макрорекордера?
10. Где сохраняются макросы?
11. Сколько макросов можно создать для одной таблицы?
12. Сколькими способами можно выполнить запуск макроса? Перечислите их.
13. Как запустить макрос на выполнение?
14. Каковы особенности записи макроса?
15. Какие действия следует выполнить при завершении записи макроса?

Рекомендуемая литература

1. Зайден М. Excel 2000 / Зайден М.; Науч. ред.: А. Плещ, С. Молявко. - М.: Лаборатория Базовых Знаний, 2000 - 336 с.: ил. + табл.. - (Компьютер)

(Шифр: 32.97        З 17)

2.         Вейсскопф Д. Excel 2000. Базовый курс (русифицированная версия) / Вейсскопф Д.; Пер. с англ. под ред. В.В. Легейды, Б.Г. Жадаева. - Киев - М.- СПб.: ВЕК+; ЭНТРОП; Корона-Принт, 2000 - 400 с.: ил. + табл.. - Слов. терминов Excel: с. 377-384.-Предметный указ.: с. 385-393

(Шифр: 32.97        В 26)

3. Саймон Д. Анализ данных в Excel = Excel Data Fnalysis: Наглядный курс создания отчетов, диаграмм и свободных таблиц / Саймон Д.; Пер. с англ. И.В. Константинова; Под ред. И.В. Василенко. - М.- СПб.- Киев: Диалектика, 2004 - 517 с.: ил. + CD-ROM. - (Читая меньше, узнавай больше!). - Прил.: с. 471-511.-Предметный указ.: с. 512-516

(Шифр: 32.97        С 14)

4.         Лавренов С.М. Excel: Сборник примеров и задач / Лавренов С.М.; Рец.: О.А. Козлов, Р.А. Подбельская. - М.: Финансы и статистика, 2003 - 336 с.: ил.. - (Диалог с компьютером). - Библиогр.: с. 304-306.-Прил.: с. 307-332

(Шифр: 32.973я7        Л 13)