Microsoft Office Excel Цель работы: научиться работать с таблицами на нескольких листах, делать по ним сводные отчет

Вид материала

Отчет

Содержание Завершение записи Пример записи макроса Построение трендовых моделей при помощи диаграмм Коэффициент достоверности аппроксимации Задание к работе Порядок выполнения работы Планирование сбыта Итоги торгов на ммвб Изменение курса Рейтинг стран - поставщиков упаковки Итоги сессии (4 курс, спец. "математика") Выдача зарплаты за октябрь 1998 г. Всего к выдаче Среднемесячная температура в г. Белгород Амплитудно-частотная характеристика разделительного фильтра Прибыль от автомобильных перевозок за сентябрь 1998 г. Распределение индивидуальной нагрузки на 1997-98 уч.год. Результаты многоборья Сумма очков Роза ветров для черноземья ... Полное содержание

Подобный материал:

• Microsoft Office Excel 2007, 13.85kb.
• Microsoft Office Word и Excel методическая разработка, 657.01kb.
• Microsoft Office Excel 2007 для формирования отчет, 35.92kb.
• Основы работы с электронными таблицами в Microsoft Excel, 40.42kb.
• Пособие для начинающих Воробьев В. В. Microsoft Excel, 439.68kb.
• Курс лекций по дисциплине " основы компьютерных технологий" Часть II. Microsoft Excel, 457.54kb.
• Методика оформление учебно-методической документации учителем географии в текстовом, 644.79kb.
• Пособие для начинающих Воробьев В. В. Microsoft Excel 2000, 451.03kb.
• Методическая разработка по дисциплине «Информатика» Тема: Табличный процессор Microsoft, 1299.63kb.
• Лекции по разделу «Табличный процессор», 589.27kb.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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



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




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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Используя переключатели в отчёте сводной таблицы, найти:
  
  1. Общее количество заказов по почте в 1 квартале;
     
  2. Общее количество проданных изданий № 30782;
     
  3. Количество изданий № 26059, проданных по международным каналам;
     
  4. Общее количество изданий, проданных во втором квартале.
     
• Отобразить все значения по всем полям сводной таблицы;
  
• Построить отчёт сводной диаграммы для всех изданий, проданных по международным каналам;
  
• Отобразить все значения по всем полям сводной таблицы;
  
• Построить отчёт сводной диаграммы для издания № 41210;
  
• Отобразить все значения по всем полям сводной таблицы;
  
• Приведите отчёт сводной таблицы к структурированному формату, используя автоформат «Отчёт4».
  
1. Создание и выполнение макросов:
   
   • Перейти на лист «Исходная» и выделить на нем произвольный диапазон числовых ячеек.
     
   • Произвести запись макроса, выполняющего установку полужирного курсивного шрифта в любом выделенном диапазоне ячеек;
     
   • Добавить на лист «Исходная» элемент управления «Кнопка» через меню панель инструментов «Формы» в меню «Вид»;
     
   • Назначить макрос элементу управления «Кнопка» с надписью «Полужирный+Курсив»
     
2. Построение трендовых моделей:
   
   • Создать новый лист с именем «Трендовая модель»;
     
   • Создать на листе таблицу, содержащую две колонки: «Код» и «Кол-во1». Колонка Код заполняется, начиная с числа равного номеру вашего студенческого, шаг заполнения +1. Колонка «Кол-во1» – содержит ссылки на ячейки в одноименной колонке на листе «Сводная».
     
   • Построить трендовую модель по данным получившейся таблицы;
     
   • По уравнению получившейся трендовой модели в отдельной ячейке вычислить значение столбца «Кол-во1» для кода, превышающего последнее значение столбца Код на 10.
     
3. Доложить преподавателю об окончании выполнения работы;
   

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

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)

Варианты задание к лабораторной работе

Таблица 1

Планирование сбыта
Цена за штуку	5.00
Расходы на штуку	3.50
Расходы по продаже	0.88	соответствуют 1/4 расходов на производство
Показатель		Месяц			За 1 квартал
		Январь	Февраль	Март
Количество		10000	20000	30000	60000
Производственные расходы		3500	7000	10500	21000
Расходы на сбыт		875	1750	2625	5250
Фиксированные расходы		1000	1250	1500	3750
Баланс оборота		5000	10000	15000	30000
Сумма		-375	 0	 375	 0

Примечание. Значения в нижней строке вычисляются как разность баланса оборота и суммарных расходов.

Таблица 2

ИТОГИ ТОРГОВ НА ММВБ	Курсы валют с 6.11.96 по 11.12.96
	6.11	15.11	22.11	29.11	6.12	11.12	Средний курс
Курс $	5461	5476	5492	5510	5521	5525	5497,5
Курс DM	3600,5	3641	3674	3598	3600	3563	3612,8
Изменение курса $	11	26	42	60	71	75
Изменение курса DM	40,5	81	114	38	40	3

Таблица 3

Рейтинг стран - поставщиков упаковки
Наименование товара	Общий объем ($ млн.)	Лидеры - экспортеры ($ млн.)				Другие ($ млн.)
		Финляндия	Германия	Турция	Китай
Пленка из полиэтилена	3,274	0,753	0,589			1,932
Пленка из полимеров винилхлорида	5,334		0,960	1,547		2,827
Мешки текстильные	7,421		1,261	1,558	2,004	2,598
Тара из бумаги	49,323	13,813	9,371	2,446		23,693
Фольга алюминиевая	9,299	1,209	1,674			6,416
Тара из древесины	0,360	0,108	0,040			0,212
ВСЕГО:	75,011	15,13	13,895	5,551	2,004	37,678

Примечание. Значения в столбце "Другие" вычисляются как разности между общим объемом и суммой лидеров-экспортеров.

Таблица 4

Итоги сессии (4 курс, спец. "математика")
Группа	Средние баллы по дисциплинам				В среднем по группам
	Философия	Физика	Математика	Информатика
141	4,5	3,9	4,3	4,4	4,3
142	4,3	4,1	4,1	4,3	4,2
143	4,3	3,7	3,9	3,9	4,0
В среднем по дисциплинам	3,3	2,9	3,1	3,2	2,5

Таблица 5.

Выдача зарплаты за октябрь 1998 г. Отдел №4
№	Фамилия	Начислено		Удержано			К выдаче
		Оклад	Допла-ты	Подо-ходный налог	Пенси-онный фонд	Аванс
1	Иванов И.И.	441	235	81,12	6,76	220	368,12
2	Сидоров С.С.	398	211	73,08	6,09	190	339,83
3	Петров П.П.	245	88	39,96	3,33	120	169,71
4	Федоров Ф.Ф.	435	217	78,24	6,52	215	352,24
Всего к выдаче							1229,90

Примечания.

1. Подоходный налог вычислить в размере 12% от суммы начислений.
   
2. Отчисления в пенсионный фонд равны 1% от суммы начислений.
   
3. "К выдаче" вычисляется как разность сумм начислений и удержаний.
   

Таблица 6

Среднемесячная температура в г. Белгород
Месяц	Средняя температура
	1991 г.	1992 г.	1993 г.	1994 г.
Январь	-10,1	-8,4	-6,2	-7,3
Февраль	-3,5	-4,6	-5,2	-4,1
Март	4,2	6,4	3,3	5,7
Апрель	11,5	13,9	11,1	14,3
Май	18,8	22,3	17,4	19,5
Июнь	21,0	23,3	20,5	22,4
Июль	23,4	24,5	21,4	23,6
Август	18,9	19,4	19,0	20,1
Сентябрь	11,3	13,7	10,5	14,0
Октябрь	4,5	7,9	6,6	8,3
Ноябрь	1,3	-1,2	3,2	-2,1
Декабрь	-8,3	-6,4	-4,6	-7,7
Средне-годовая	160,3	161,8	160,8	161,6

Таблица 7.

Амплитудно-частотная характеристика разделительного фильтра
Частота, кГц,	Ток, А (параметры фильтра: L1=7,9 мГн, С1=50 мкФ, R1=1,45 Ом)
	при R2=0 Ом	при R2=5 Ом	средний
0,04	0,95	0,75	0,85
0,08	0,88	0,7	0,79
0,15	1,03	0,75	0,89
0,3	0,9	0,67	0,79
0,5	0,3	0,54	0,42
1	0,05	0,26	0,16
2	0,02	0,1	0,06

Таблица 8

Прибыль от автомобильных перевозок за сентябрь 1998 г.
Населенный пункт	Рассто- яние, км.	Расходы на перевозку, руб	Цена рейса, руб	Количес-тво рейсов, руб	Полученная прибыль, руб
Воронеж	260	234	350	43	4988
Старый Оскол	130	125	195	62	4340
Шебекино	35	39	61	125	2750
Харьков	85	103	165	15	930
Курск	173	161	223	9	558
СУММА:					13566

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

Таблица 9.

Распределение индивидуальной нагрузки на 1997-98 уч.год. Доцент Иванов И.И.
Вид работы	Курс	Нагрузка (часов) по месяцам учебного года												Сумма
		9	10	11	12	1	2	3	4	5	6	7	8
ИВТ	5	76	76	18	10		58	76	64					378
ГЭК	5 з/о			26	22									48
ГЭК	5									34	30			64
Информ.	1	32	32	32	32	16	18	24	24	24	18			252
Методика	5 з/о							12			44			56
Практика	2											40		40
Курсовые	4	10	10	10	10		10	10	10	10				80
ВСЕГО:		118	118	86	74	16	86	122	98	68	92	40	0	918

Таблица 10.

Результаты многоборья
Вид спорта	Место, занятое факультетом
	Физико-матем.	Иностр. языка.	Истори-ческий	Русского языка	Экономи-ческий	Юриди-ческий
Кросс	1	6	3	5	2	4
Стрельба	2	3	6	4	5	1
Гимнастика	1	4	3	2	6	5
Волейбол	3	2	5	4	1	6
Лыжи	1	4	3	5	2	6
Сумма очков	22	11	10	10	14	8

Примечание. Сумма очков вычисляется как количество факультетов-участников (6) умноженное на количество видов соревнований (5) минус сумма мест участника.

Таблица 11.

Роза ветров для черноземья
Город	Количество дней в году
	С	С-В	В	Ю-В	Ю	Ю-З	З	С-З
Белгород	33	25	30	39	45	52	49	41
Воронеж	35	28	29	40	43	54	52	40
Курск	32	25	29	41	46	53	50	39
Липецк	35	27	28	38	44	55	53	40
Тамбов	38	29	30	37	43	56	52	40
В среднем	28,8	22,3	24,3	32,5	36,8	45,0	42,7	33,3

Таблица 12.

Производство продукции
Страны	1985	1990	1995	Всего	Динамика за 1985-95 гг.
Франция	128	131	134	393	3,0
Англия	158	161	169	488	5,5
Германия	141	143	149	433	4,0
Испания	105	111	115	331	5,0
Всего	532	546	567	1645	17,5
В среднем	133	137	142	411	4,5

Примечание. Динамика за 1985-95 гг. вычисляется как разность производства продукции в 1995 г. и 1985 г. деленная на 2.

Таблица 13

Результаты лабораторного эксперимента. Установка №1
№	X, В	Y, В	X среднее, В	Y среднее, В
1	0	-1	0,000	-1,000
2	0,5	-1,732	0,250	-1,366
3	0,866	-2	0,455	-1,577
4	1	-1,732	0,592	-1,616
5	0,866	-1	0,646	-1,493
6	0,5	0	0,622	-1,244
7	0	1	0,533	-0,923
8	-0,5	1,732	0,404	-0,592
9	-0,866	2	0,263	-0,304
10	-1	1,732	0,137	-0,100
11	-0,866	1	0,045	0,000
12	-0,5	0	0,000	0,000