5. Пример выполнения работы в ms Excel

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

   1. Выбрать в меню “Пуск” строку “Создать документ MS” в появившемся окне щелкнуть два раза левой кнопкой на картинку с подписью “Новая книга”.

   2. Выбрать в меню “Файл” команду “Сохранить как …”, указать место и имя документа, нажать “Сохранить”.

   3. Проверить параметры шрифта – в окне “шрифт” на Панели инструментов должен стаять шрифт “Arial Cyr” размер 10.

2. Оформление работы (см. приложение А1):

   1. Ввести в ячейки А1 - “Расчетная работа”, в А2 – “Выполнил студент группы 4ХХ ФИО.”, в А3 – “Таблица 1 – исходные данные”, А4 – “№”, В4 - “Х”, С4 - “У”, А54 – “Выполнил”, А55 - “Проверил”, В54 – “свою фамилию”, G55 - “Дата”, Н55 – “ввести дату”, F40 – “ Таблица 2 – Результаты эксперимента”.

   2. Ввести в ячейки А5 – А24 цифры от 1 до 20 и значения в ячейки, В5 – В24 и С5 – С24 согласно заданию на вариант.

   3. Обвести ячейки таблицы с исходными данными для этого навести курсор на середину ячейки А4 (№) нажать на левую кнопку мыши и не отпуская ее довести курсор до середины ячейки С24. При этом вся таблица исходных данных должна быть выделена темным цветом. Затем на Панели инструментов нажать кнопку рядом с кнопкой – Границы в появившемся окошке выбрать . Таблица 1 должна принять вид похожий на пример в приложении А1.

   4. Создать таблицу 2 при этом она должна располагаться в ячейках F41-I47. Для этого необходимо поставить курсор в ячейку F41 и после окончания набора слов: “Вид”, “R”, “а”, “b”, “c”, “x”, “y=y(x)”, каждый раз нажимать ENTER с тем, чтобы перейти к новой ячейке. Ввести в ячейки G41 - “Лин.” (линейная), H41 - “Квадр.” (квадратичная), I41 – “Эксп.” (экспоненциальная). Выделить таблицу и нажать на кнопку на Панели инструментов.

3. Создание и редактирование диаграммы:

   1. Запустить “Мастер диаграмм” нажав на пункт “Диаграммы” в меню “Вставка” или на кнопку – “Диаграмма” на Панели инструментов.

   2. В открывшемся окне задать тип диаграммы – Точечная нажав два раза на слово “точечная” в предлагаемом списке диаграмм. Произойдет переход на следующий “шаг” построения диаграммы рис.2.

Рис.2 – Окно Мастер диаграмм.

3. На втором шаге построения диаграммы перед переходом на закладку “Ряд” убедиться в том, что в строке “Диапазон” отсутствует какое либо значение, если значение присутствует, то его необходимо удалить. В верхней части Мастера диаграмм нажать на закладку “Ряд”. После этого на кнопку “Добавить” в середине активного окна. Результатом станет появление графика с единственной точкой.

4. Задать значения х. Для задания х нажать на кнопку напротив фразы “значение Х”. Окно мастера диаграмм свернется в полоску. Выделить курсором с помощью левой кнопки мыши ячейки В5 – В24. Вокруг выделенных ячеек побегут черные “муравьи” а в активном окошке появиться надпись – “Лист1!$В$5;$В$24”. Нажать на кнопку в активном окошке. Результатом станет переход к предыдущему окошку.

5. Задать значения Y аналогично Х. После задания значения Y в окошке мастера диаграмм должен появиться график зависимости Х от Y.

6. Перед переходом на следующий шаг убедитесь в том, что окошко имеет вид как на рис.3.

Рис.3 – Ввод исходных данных необходимых для построения диаграммы.

7. Перейти к следующему шагу построения диаграммы, нажав в нижней части Мастера диаграмм на кнопку “Далее”.

8. В пустых строках “Название диаграммы” набрать “Диаграмма разброса”, “Ось Х (категорий)” - “Х”, “Ось Y (значений)” - “Y”

9. Нажать на закладку “Линии сетки”. После перехода к новой страничке в пункте “Ось категорий” выставить флажок напротив фразы “Основные линии”.

10. Перейти к закладке “Легенда” и убрать флажок напротив фразы “Добавить легенду”.

11. Перед переходом на следующий шаг убедиться, что активное окошко имеет вид рис.4 Перейти к следующему шагу построения диаграммы (нажав на кнопку “Далее”).

Рис.4 – Окончательный вид окна Мастер диаграмм на третьем шаге.

12. Проследить за тем, чтобы в появившемся окне напротив слова “имеющемся” стоял флажок (флажок стоит по умолчанию).

13. Нажать на кнопку “Готово”.

14. Разместить диаграмму справа от таблицы 1 при этом диаграмма по ширине должна занимать пространство от середины столбика D до середины столбика I. По высоте диаграмма должна быть не выше таблицы 1 (смотри приложение А1).

15. Растянуть “Область построения диаграммы” на все окно диаграммы. Для выделения области построения диаграммы навести курсор на окно диаграммы и, пользуясь всплывающей под курсором подсказкой поместить его таким образом, чтобы он попадал на область построения диаграммы.

16. Сделать цвет Области построения диаграммы белым (если он любого другого цвета). Для этого выделить область построения диаграммы затем в меню “Формат” выбрать “Формат области построения” или нажать правой кнопкой мыши на выделенную область построения и выбрать “Формат области построения”. В появившемся окне в столбике заливка поставить флажок напротив слова “прозрачная” (ячейка под словом “образец” должна погаснуть).

17. Проверить правильность построения диаграммы рис.5.

Рис.5 – Диаграмма разброса.

4. Тиражирование диаграммы:

   1. С помощью меню “Правка” или меню вызываемого правой кнопки мыши создать три копии диаграммы. Для этого сначала надо выделить диаграмму, затем выбрать из меню Правка команду “копировать”. После этого выделить ячейку, в районе которой хотите вставить диаграмму, из меню Правка команду “вставить”.

   2. Разместить копии диаграммы под таблицей 1 и под диаграммой справа от таблицы 1, так как это показано в приложении А1. Возможно, для этого потребуется изменить их размеры. Нижняя часть диаграммы не должна “наезжать” на ячейку со словом “ Выполнил” и на фразу “Таблица2 – Результаты эксперимента”.

   3. Поменять название диаграмм. Для этого нужно навести курсор в окне диаграммы расположенной под таблицей 1 на “Название диаграммы” нажать на левую кнопку один раз после этого начать вводить слово “линейная” в Строке формул (вверху окна программы) появятся вводимые символы. По окончанию ввода нажать клавишу Enter. Аналогичным образом назвать диаграмму, расположенную под линейной диаграммой экспоненциальной и слева – квадратичной.

5. Обработка результатов эксперимента

   1. Построить линию Тренда для предполагаемой линейной зависимости. Для этого выделить на линейной диаграмме любую точку из графика. Выбрать пункт “Добавить линию Тренда” в меню Диаграмма или в меню, вызываемом правой кнопкой мышки. В появившемся окне “Линия Тренда” по умолчанию стоит аппроксимация с помощью линейной зависимости. Переходим к закладке “Параметры”. Необходимо выставить (по умолчанию не стоят) флажки напротив фраз “Показать уравнение на диаграмме” и “Показать величину достоверности аппроксимации” (рис.6). Нажать “ОК”. На диаграмме должна появиться аппроксимирующая зависимость, уравнение этой зависимости и величина достоверности аппроксимации.

   2. Навести на уравнение аппроксимации курсор, и нажать один раз на левую кнопку мышки после этого на Панели инструментов выбрать кнопку “Размер” и установить 10 размер шрифта. Разместить уравнение так чтобы оно “не заслоняло” график как это показано в приложении А1.

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

   4. Записать значение коэффициента корреляции (R) которое выводиться при построении линии Тренда в соответствующей закону столбик в строку “R” таблицы 2.

Рис.6 – Построение линии Тренда.

1. Выбрать регрессионное уравнение, которое наиболее статистически значимо (Далее в качестве примера рассмотрим линейное уравнение).

1. Поиск оптимального значения (на примере линейной функции)

   1. Ввести полученные значения коэффициентов регрессионных уравнений a, b, c в соответствующие ячейки, где а – коэффициент при х, b – свободный член в линейном уравнении или а – коэффициент при х², b – при х, с – свободный член в квадратичном уравнении или а – коэффициент при экспоненте, b – коэффициент при х в экспоненциальном уравнении, т.е.: у(х)=а*х+в, у(х)=а*х²+в*х+с, у(х)=а*ехр(в*х).

   2. Ввести регрессионное уравнение для линейной функции в ячейку G47 для этого: ввести символ “=” после этого создать ссылку на ячейку G43 – кликнуть на ячейку со значением коэффициента а (G43) вокруг ячейки G43 побегут синие “муравьи” а в ячейке G47 появиться ссылка на ячейку G43 (символы G43 синего цвета после знака “=”). После этого ввести знак умножения и создать ссылку на ячейку содержащую аргумент функции (G46), ввести знак сложения и добавить ссылку со значением b G44. Результатом станет следующая запись в ячейке “=G43*G46+G44” нажать на Enter. Правильным результатом ввода целевой функции (регрессионного уравнения) станет появление в ячейке G47 значения коэффициента b, при условии что ячейка со значением аргумента пуста.

Аналогично можно ввести регрессионные уравнения для квадратичной и экспоненциальной функции. При этом для квадратичной зависимости необходимо создавать ссылки на ячейки Н43…Н46, а для экспоненциальной I43, I44, I46. Правильным результатом будет появление в ячейках со значением функции (H50 и I50), значений коэффициентов “с” – для квадратичной, “а” – для экспоненциальной функций. При этом ячейки со значениями х должны быть равны нулю – в них не должно быть никаких значений.

3. В меню “Сервис” выбрать пункт “Поиск решения”

4. В появившемся окне ввести ячейку, содержащую исследуемую регрессионную зависимость (в нашем случае линейную), для этого нажать на кнопку напротив фразы “Установить целевую ячейку” в результате окно свернется в строку после этого выделить ячейку, содержащую регрессионное уравнение (G47) и нажать на кнопку . В окне “Поиск решения” напротив фразы “Установить целевую ячейку” появиться надпись $G$47

5. Далее необходимо установить флажок напротив значения, на которое исследуется функция – “максимальное” или “минимальное” согласно заданию на вариант.

6. В пункте “Изменяя ячейки” установить по аналогии с пунктом “Установить целевую ячейку” ссылку на ячейку, содержащую х (для линейной функции - G46)

7. В пункте “Ограничения” необходимо установить интервал изменения аргумента х. Для этого нажать на кнопку “Добавить”. В появившемся окне Добавление ограничений (рис.7) в пункте “Ссылка на ячейку” ввести ячейку, на которую накладываются ограничения (для линейной функции это G46), поставить символ “” и в пункт “Ограничения” ввести нижнюю границу исследуемого интервала в нашем случае это 0.

Рис.7 – Окно Добавление ограничений.

8. Поскольку ограничения могут накладываться только односторонние то для ввода интервала изменения аргумента функции необходимо ввести два ограничения. Для ввода второго ограничения нажать на кнопку “Добавить” и аналогично создать ограничения вида $G$46<=12, где 12 это наибольшее значение, которое может принимать х на исследуемом интервале.

9. Нажать на кнопку “ОК” окно Поиск решения примет вид представленный на рис.8.

10. Нажмите на кнопку “Выполнить”. Появится окно “Результаты поиска решения”, убедиться в том, что напротив “Сохранить найденное решение” стоит флажок. Нажать “ОК”. Результатом выполнения процедуры “Поиск решения” станет появление в ячейках “аргумент функции” (G46) и “значения функции” (G47) оптимальных значений.

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

Рис.8 – Иллюстрация к выполнению команды Поиск решения.

7. Построение регрессионной зависимости без учета влияния грубых ошибок измерения на регрессионную зависимость на примере линейной зависимости.

   1. Скопировать лист с расположенными на нем данными. Для этого нажать правой клавишей мыши внизу рабочего поля на закладку “лист1” после этого из контекстного меню выбрать пункт “переместить/скопировать” в появившемся окошке поставить флажок напротив “создать копию”.

   2. Переименовать “Лист 1” и “Лист 1 копия” в листы под названием “до удаления промахов” и “после удаления промахов”. Для этого два раза щелкнуть левой кнопкой мыши на название листа, перейти на нужный лист и вызвать правой кнопкой мыши меню выбрать пункт “переименовать” и вести соответствующее название.

   3. Перейти на лист под названием “после удаления промахов”. Найти по Диаграмме разброса в таблице 2 промахи и удалить их так чтобы ячейки со значением х и у были пусты. Произойдет пересчет уравнений аппроксимации и коэффициента корреляции.

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

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

8. Подготовка к печати:

   1. В меню “Файл” выбрать “Предварительный просмотр”, после этого нажать на кнопку Поля.

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