Аппроксимация данных линейной и показательной функциями

ФГБ ОУ ВПО

“МОСКОВСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ПУТЕЙ СООБЩЕНИЯ”

Кафедра «Вычислительные системы и сети»

Т.Г. ШАХУНЯНЦ

Microsoft Excel 2007

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

К лабораторным работам

По дисциплине

«Информатика»

Москва – 2011

ФГБ ОУ ВПО

“МОСКОВСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ПУТЕЙ СООБЩЕНИЯ”

Кафедра «Вычислительные системы и сети»

Т.Г. ШАХУНЯНЦ

Microsoft Excel 2007

Рекомендовано редакционно-издательским советом

Университета в качестве методических указаний

Для студентов I курса направлений

«Информатика и вычислительная техника» и «Информационная безопасность»

Москва – 2011

УДК 681.3

Ш-32

Шахунянц Т.Г. Microsoft Excel 2007: Методические указания. –М.: МИИТ, 2011. – 45 с.

Данные методические указания предназначены для выполнения лабораторных работ по изучению и освоению некоторых возможностей обработки данных в среде Microsoft Excel 2007. Для выполнения заданий к каждой из лабораторных работ приводятся соответствующие примеры.

© ФГП ОУ ВПО

“Московский государственный

университет путей сообщения”

Содержание

Введение………………………………………………………..…4

1. Лабораторная работа №1………….…………………….....…5

2. Лабораторная работа №2 ……………………....………...…10

3. Лабораторная работа №3 ……………………..……….……14

4. Лабораторная работа №4 ……………..……………….……19

5. Лабораторная работа №5 ……………….………………..…24

6. Лабораторная работа №6……………………………..…..…27

7. Лабораторная работа №7……...…………………….…....…33

8. Лабораторная работа №8……………………..……….….…37

9.Литература…………….………………………………….......44

Введение

Microsoft Excel относится к программам, позволяющим обрабатывать данные, представленные в форме таблиц («программам «электронные таблицы»). Электронные таблицы широко применяются в экономических и научно-технических задачах для проведения однотипных расчётов над большими наборами данных, построения диаграмм и графиков по имеющимся данным, решения уравнений, поиска значений параметров в задачах оптимизации и т.п.

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

В описываемых лабораторных работах используется версия MicrosoftExcel 2007, имеющая ряд усовершенствований, по сравнению с предыдущими. Одним из основных отличий от предыдущих версий программы является иной принцип организации интерфейса: вместо принципа командных меню со списком имеющихся операций используется принцип списка контекстных меню функций. То или иное действие пользователя активизирует соответствующие инструменты команд, объединенных во вкладки.

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

В данных методических указаниях использованы материалы монографии[1,2]

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

Основы работы в среде Microsoft Excel 2007.

1.1. Цель работы

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

1.2. Задания к выполнению лабораторной работы

Создать согласованные с преподавателем электронные таблицы с использованием абсолютных и относительных ссылок и копирования формул методом автозаполнения/

1.3. Подготовка к работе

Для выполнения работы следует ознакомиться с примером действий для выполнения заданий, рассмотренным в разделе 1.4

1.4. Пример действий по выполнению заданий к лабораторной работе

1. Запустите программу Excel (Пуск - > Все программы - > Microsoft Office->Microsoft Excel 2007).

2. Создайте новую книгу (Office-> Создать).

3. Дважды щелкните на ярлычке текущего рабочего листа и дайте этому рабочему листу имя Данные.

4. Cохраните книгу под именем examples (Office- > Сохранить как -> Книга Excel).

5. Сделайте ячейку А1 активной и введите в нее заголовок “Результаты измерений”.

Для расширения столбца активной ячейки нужно захватить курсором правую границу самого столбца и протянуть на нужную вам длину

6. Введите 9 произвольных чисел в последовательные ячейки столбца А, начиная с ячейки А2, заканчивая ячейкой А10.

7. Введите в ячейку В1 строку “Утроенное значение”.

8. Введите в ячейку С1 строку “Куб числа”.

9. Введите в ячейку D1 строку “Квадрат следующего числа”.

10. Введите в ячейку В2 формулу = 3*А2.

11. Введите в ячейку С2 формулу =А2*А2*A2.

12. Введите в ячейку D2 формулу =A3*A3.

13. Выделите протягиванием ячейки В2, С2 и D2.

14. Наведите указатель мыши на маркер заполнения в правом нижнем углу рамки, охватывающий выделенный диапазон. Нажмите левую кнопку мыши и перетащите этот маркер, чтобы рамка охватила столько строк в столбцах B,C,D, сколько имеется чисел в столбце A.

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

16. Введите в ячейку Е1 строку “Масштаб”.

17. Введите в ячейку Е2 число 5.

18. Введите в ячейку F1 строку “Масштабирование”.

19. Введите в ячейку F2 формулу =А2*Е2.

20. Используйте метод автозаполнения, чтобы скопировать эту формулу в ячейки столбца F, соответствующие заполненным ячейкам столбца А.

21. Убедитесь, что результат масштабирования оказался неверным. Это связано с тем, что адрес Е2 в формуле задан относительной ссылкой.

22. Щелкните на ячейке F2, затем в строке формул. Установите текстовый курсор на ссылку Е2 и нажмите клавишу F4. Убедитесь, что формула теперь выглядит как =А2*$Е$2, и нажмите клавишу ENTER.

23. Повторите заполнение столбца F формулой из ячейки F2.

24. Убедитесь, что благодаря использованию абсолютной адресации значения ячеек столбца F теперь вычисляются правильно. Сохраните книгу examples.

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

1. В чем отличия ввода данных от записи формул?

2. Как осуществляется копирование формул методом автозаполнения?

3. Чем отличаются относительные и абсолютные ссылки по форме и по результатам их обработки?

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

Использование стандартных и итоговых функций в среде Microsoft Excel.

2.1. Цель работы

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

2.2. Задания к выполнению лабораторной работы

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

2.3. Подготовка к работе

Для выполнения работы следует ознакомиться с примером действий для выполнения заданий, рассмотренным в разделе 2.4

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

1. Запустите программу Excel (Пуск - > Все программы - > Microsoft Office->Microsoft Excel2007) и откройте рабочую книгу examples, созданную ранее

(Office -> Открыть)

2. Выберите рабочий лист “Данные”(щёлкнув по вкладке на нижней панели).

3. Сделайте активной ячейку А11.

4. Щелкните на кнопке “Автосумма” на стандартной панели (значок ∑) на вкладке «Формулы»

5. Убедитесь, что программа автоматически подставила в формулу функцию СУММ и правильно выбрала диапазон ячеек для суммирования. Нажмите клавишу ENTER.

6. Сделайте активной следующую свободную ячейку в столбце А.

7. Щелкните на кнопке “Вставить функцию” (Значок f_X) на вкладке «Формулы».

8. В списке Категория выберите пункт “Статистические”.

9. В списке Функция выберите функцию СРЗНАЧ и щелкните на кнопке ОК.

10. Методом протягивания выберете ячейки от А2 до А10

11. Используя порядок действий, описанный в пп. 6-10, вычислите минимальное число в заданном наборе (функция МИН), максимальное число (МАКС), количество элементов в наборе (СЧЕТ).

12. Сохраните книгу examples.

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

1. Способы использования стандартных функций.

2. Способы использования итоговых функций.

3. Как определяется диапазон обрабатываемых функцией значений данных?

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

Создание, форматирование и подготовка к печати документов.

3.1. Цель работы

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

3.2. Задания к выполнению лабораторной работы

Создать, отформатировать и подготовить к печати согласованные с преподавателем документы.

3.3. Подготовка к работе

Для выполнения работы следует ознакомиться с примером действий для выполнения заданий, рассмотренным в разделе 3.4

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

1. Запустите программу Excel (Пуск > Все программы > Microsoft Office>Microsoft Excel 2007) и откройте книгу examples.

2. Выберите щелчком на ярлычке неиспользуемый рабочий лист или создайте новый (Горячая клавиша SHIFT + F11). Дважды щелкните на ярлычке нового листа и переименуйте его как Прейскурант.

3. В ячейку А1 введите текст “Прейскурант” и нажмите клавишу ENTER.

4. В ячейку А2 введите текст “Курс пересчета”: и нажмите клавишу ENTER. В ячейку В2 введите текст “1 у.е.=” нажмите клавишу ENTER. В ячейку С2 введите текущий курс пересчета и нажмите клавишу ENTER.

5. В ячейку A3 введите текст «Наименование товара» и нажмите клавишу ENTER. В ячейку ВЗ введите текст «цена (у.е.)» и нажмите клавишу ENTER. В ячейку СЗ введите текст «Цена (руб.)» и нажмите клавишу ENTER.

6. В последующие ячейки столбца А введите названия товаров, включенных в прейскурант.

7. В соответствующие ячейки столбца В введите цены товаров в условных единицах.

8. В ячейку С4 введите формулу: =В4*$С$2, которая используется для пересчета цены из условных единиц в рубли.

9. Методом автозаполнения скопируйте формулы во все ячейки столбца С, которым соответствуют заполненные ячейки столбцов А и В.

10. Измените курс пересчета в ячейке С2. Обратите внимание, что все цены в рублях при этом обновляются автоматически.

11. Выделите методом протягивания диапазон А1:С1 и дайте команду в контекстном меню Форматячеек. На вкладке “Выравнивание” задайте выравнивание: “По левому краю” и щелкните “Объединить по строкам”.

12. На вкладке Шрифт задайте размер шрифта в 14 и в списке “Начертание” выберите вариант “Полужирный”. Щелкните на кнопке ОК.

13. Щелкните правой кнопкой мыши на ячейке В2 и выберите в контекстном меню команду Формат ячеек. Задайте выравнивание по горизонтали: “По правому краю” и щелкните на кнопке ОК.

14. Щелкните правой кнопкой мыши на ячейке С2 и выберите в контекстном меню команду Формат ячеек. Задайте выравнивание по горизонтали: “По левому краю” и щелкните на кнопке ОК.

15. Выделите методом протягивания диапазон В2:С2. и выберите в контекстном меню команду Формат ячеек. На вкладке “Границы” задайте широкую внешнюю рамку.

16. Выделите методом протягивания диапазон В2:С2. и выберите в меню на вкладке «Главная» команду «Объединить и поместить в центре»

17. Дважды щёлкните по границе между заголовками столбцов A и B, B и C, C и D. Обратите внимание, как при этом изменяется ширина столбцов A, B, C.

18. Посмотрите, устраивает ли Вас полученный формат таблицы. Щёлкните на кнопке «Предварительный просмотр», нажав Office -> Печать -> Предварительный просмотр, чтоб увидеть, как будет выглядеть при печати.

19. Щёлкните по кнопке «Печать» (Office -> Печать -> Печать) и напечатайте документ.

Сохраните рабочую книгу examples.

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

1. Как осуществляется выравнивание текста в ячейках?

2. Способы изменения ширины столбцов и строк.

3. Как объединить ячейки таблицы?

4. Способы подготовки документа к печати.

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

Построение графиков и диаграмм

4.1. Цель лабораторной работы

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

4.2. Задания к выполнению лабораторной работы

Построить указанные преподавателем графики (диаграммы) на основе предложенных данных. Оформить полученную диаграмму.

4.3. Подготовка к работе

Для выполнения работы следует ознакомиться с примером действий для выполнения заданий, рассмотренным в разделе 4.4

4.4. Пример действий по выполнению заданий к лабораторной работе.

1. Запустите программу Excel (Пуск - > Все программы - > Microsoft Office ->Microsoft Excel 2007) и откройте книгу examples, созданную ранее.

2. Выберите щелчком на ярлычке неиспользуемый лист. Дважды щелкните на ярлычке листа и переименуйте его как “Построение графиков”

3. В столбец А, начиная с ячейки А1, введите произвольный набор из 5 значений независимой переменной. Значение переменной в ячейках A1÷A5 желательно вводить по возрастанию или по убыванию.

4. В столбец В, начиная с ячейки В1, введите произвольный набор значений функции, причем их количество должно совпадать с количеством значений столбца А. Значения функции желательно вводить по возрастанию или по убыванию.

(Последнее предложение необязательно, но гарантирует наглядность результатов лабораторной работы№6)

5. Методом протягивания выделите все заполненные ячейки столбцов А и В.

6. Щелкните на активное меню “Вставка” на стандартной панели инструментов и найдите вкладку “Диаграммы”.

7. В списке выберите «Точечная с гладкими кривыми».

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

Работа с диаграммами > Конструктор > (Выбрать любой стиль диаграммы)

9. Чтобы изменить название графика, нужно дважды щелкнуть на нем, и вписать новое.

Для изменения подписи графика следует совершить следующие действия:

Щелкнуть правой кнопкой мыши по графику -> Выбрать данные -> Изменить -> (Вписать название) ->ОК

10. Чтобы изменить название осей графика, нужно щелкнуть по нему, выбрать в меню вкладку «Макет»> «Название осей» и дать название каждой координатной прямой.

11. Сохраните рабочую книгу.

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

1. Как определяется диапазон аргументов для построения графиков?

2. Какие действия производятся для построения графиков?

3. Как поместить график (диаграмму) на уже созданный лист и как на отдельный?

4. Как задать имена графиков?

5. Как задавать названия осей?

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

Аппроксимация данных линейной и показательной функциями.

4.

5.

5.1. Цель работы

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

5.2. Задания к выполнению лабораторной работы

Получить значения параметров a и b прямой, заданной уравнением y = ax + b, и значения a и b показательной функции, заданной уравнением y = ba^x, для аппроксимации кривой, определенной заданным набором пар значений x и y. Набор пар значений x и y взять из лабораторной работы №4.

5.3. Подготовка к работе

Для выполнения работы следует ознакомиться с примером действий для выполнения заданий, рассмотренным в разделе 5.4

5.4. Примеры действий по выполнению заданий к лабораторной работе.

1. Запустите программу Excel (Пуск - > Все программы - > Microsoft Office->Microsoft Excel 2007) и откройте книгу examples, созданную ранее.

2. Щелчком на ярлычке выберите лист Обработка эксперимента.

3. В ячейку С1 введите формулу: =ИНДЕКС(ЛИНЕЙН(В1:В5;А1:А5);1)

4. В ячейку D1 введите формулу: =ИНДЕКС(ЛИНЕЙН(В1:В5;А1:А5);2)

Функция ЛИНЕЙН возвращает коэффициенты уравнения прямой, аппроксимирующей заданный набор пар значений в виде массива из двух элементов (в нашем случае a,b).

С помощью функции ИНДЕКС выбирается нужный элемент. При задании второго её параметра равным 1выбирается коэффициент a, при значении 2 выбирается b.

5. Теперь в ячейках С1 и D1 вычислены, соответственно, коэффициенты а и b уравнения прямой, наилучшей с точки зрения похожести на исходную кривую (аппроксимирующей исходную кривую)

6. В ячейку С2 введите формулу: =ИНДЕКС(ЛГРФПРИБЛ(В1:В5;А1:А5);1)

7. В ячейку D2 введите формулу: =ИНДЕКС(ЛГРФПРИБЛ(В1:В5;А1:А5);2)

8. Теперь ячейки С2 и D2 содержат, соответственно, коэффициенты а и Ь уравнения наилучшего показательного приближения к исходной кривой.

Сохраните рабочую книгу examples.

5.

5.1.

5.2.

5.3.

5.4.

5.5.

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

1. Какие программыExcel используются для получения линейной аппроксимации заданной кривой?

2. Какие программы используются для получения аппроксимации заданной кривой показательной функции?

3. Что является результатом аппроксимации?

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