Виконання завдання.

Постановка завдання.

II.2.1. Приклад виконання Завдання № 2.

II.2. Завдання № 2

Тема: Обробка експериментальних даних у MS Excel.

Мета роботи:Освоїтиметодику апроксимації величини отриманої в результаті експерименту.

Зміст завдання:

1. Побудувати графік функції y(х)на основі даних таблиці.

2. Відформувати графік.

3. Вибрати лінію тренда, що апроксимує табличні дані з більшою вірогідністю (достовірністю).

4. На підставі рівняння лінії тренда визначити значення y, що відповідає зазначеному значенню x.

В ході експерименту на вхід досліджуваної системи подаються значення вхідної величини х (як незалежної), а на виході спостерігаються значення вихідної величини у(х) (як залежної від х).

Результати експерименту представлені в табличній формі:

x 9,6 11,5 17,5 28,5 34,5 36,5 45,5 50,5
y 0,71 0,78 1,10 3,45 4,15 5,55 7,45 12,10

Завдання полягає в тому щоб визначити яка залежність у(х) має місце (апроксимувати величину у(х) лінією тренда). Лінії тренда дозволяють графічно відображати тенденції даних і прогнозувати їх подальші зміни.

1.Будуємо задану таблицю на аркуші MS Excel:

2.За даними таблиці будуємо графік залежності у(х).

Для побудови графіка необхідно:

- виділити діапазон чарунок таблиці B4:C12:

- викликати Мастера диаграммз панелі інструментів кнопкою або з менюВставка®Диаграмма:

- у першому діалоговому вікні Мастера диаграммвибрати Тип®Точечная, Вид- 1:

Точечная діаграма показує залежність між числовими значеннями в декількох рядах даних, або відображає дві групи чисел як один ряд координат x і y.

- клацнути по кнопці Далее і в другому діалоговому вікні Мастера диаграмм можна побачити як виглядатиме графічне зображення точок на площині з заданими координатами хі у:

- клацаємо Далее і в другому діалоговому вікніМастера диаграмм на вкладці Заголовки задаємо заголовки (діаграми, осі Ох і осі Оу):

- в цьому ж вікні на вибираємо вкладку Линии сетки і прибираємо горизонтальні лінії сітки (знімаємо галочку a з основні лінії:

- в цьому ж такі вікні на вкладці Легенда прибираємо Легенду, знявши галочку a з Добавитьлегенду:

 

- клацаємо Далее і в наступному діалоговому вікні погоджуємося розмістити діаграму на листі, що є (имеющемся) та клацаємо Готово:

 

 

 
 

Отримуємо:

Виділивши область побудови діаграми (замальована сірим кольором) і визвавши контекстне меню (правою клавішею миші) вибрати пункт «Формат области построения». В отриманому вікні вибрати пункти рамки «невидимая» та заливку білого кольору:

Після форматування області побудови діаграми вона буде мати такий вигляд:

З графіка видно, що точки розташовуються вздовж деякої кривої лінії (очевидно, що не прямої). Тобто робимо висновок, що залежність величиниу від величини х нелінійна.

3.Додавання Линии тренда.

Існує шість різних видів ліній тренда (апроксимація і згладжування), які можуть бути додані на діаграму Microsoft Excel. Спосіб слід вибирати залежно від типу даних.

Для цього необхідно:

- виділивши точки на графіку визвати правою клавішою миші контекстне меню рядка даних графіка і вибрати Добавить линию тренда:

- у вікніЛиния тренда, що з'явилося,на вкладці Тип - вибрати підходящий Тип (обраний«Экспоненциальный»);

- на вкладціПараметры встановити прапорці:

a показувати рівняння на діаграмі,

a помістити на діаграму величину вірогідності апроксимації R2.

 

На діаграмі буде виведена апроксимуюча експоненцальна крива та рівняння, яке наближено описує залежність величини у від х:

у = 0,3676е0,07х.

За допомогою контекстного меню можна відформувати тип лінії тренду і підпис даних тренду.

Вибір лінії тренда того або іншого виду визначається типом даних. При підборі лінії тренда до даних Excel автоматично розраховує значення R2.

Найбільш достовірна лінія тренда, для якої значення R2(коефіцієнт детермінованості) дорівнює або близько до 1.

4.Вибір лінії тренда, що найбільше вірогідно апроксимує табличні дані.

Добавимо на діаграму аналогічним способом ще одну лінію тренду, вибравши тип лінійної апроксимації:

Побудовано дві лінії тренда ( Експонентна, для якої R2 =0,9831 і Лінійна R2 =0,8858).

Достовірніше - Експонентна лінія тренда, тому що в неї R2 =0,9 більше 0,8856.

Знаючи рівняння y=0.3676e0.07x можна визначити значення у для будь-якого значення незалежної змінної х.

Наприклад потрібно дізнатися яким буде на виході значення величиниу, якщо на вході подати значення вхідної величини х=25.

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

- в чарунку B23 занесемо значення25,

- в чарунка C23введемо формулу для розрахунку у:

=0,3676*EXP(0,07*B23)

Отримаємо:

х у
2,115392

Зміна вмісту чарунки B23 автоматично змінює значення у .

II.2.2.
Поради щодо оформлення Завдання № 2.


II.2.3. Варіанти індивідуальних завдань № 2.

Варіант1

Результати експериментів

x 1,2 2,5 3,2 4,5 7,1 10,5 12,5
y 5,35 5,06 4,9 4,7 4,2 3,98 3,72 3,56

 

Значення х, для якого розрахувати у х=3

 

Варіант2

Результати експериментів

x 0,85 3,5 3,8 9,6 15,2 19,3 20,1 21,6
y 7,547 8,13 8,45 9,472 10,7 11,61 12,5 13,1

 

Значення х, для якого розрахувати у х=4

 

Варіант3

Результати експериментів

x 0,55 1,25 6,5 7,5 13,5 28,5
y -5,98 -5,46 -1,82 -0,96 3,36 9,68 13,2 15,1

 

Значення х, для якого розрахувати у х=5

 

Варіант4

Результати експериментів

x 0,9 2,9 8,5 12,5 17,5 20,5 22,5
y 9,56 20,3 43,2 51,5 57,36 60,5 64,5 67,7

 

Значення х, для якого розрахувати у х=10

 

Варіант5

Результати експериментів

x 1,2 2,5 3,2 4,5 7,1 15,5
y 5,55 5,2 4,976 4,65 4,12 2,36 3,45 3,45

 

Значення х, для якого розрахувати у х=13

 

Варіант6

Результати експериментів

x 0,9 2,9 7,8 12,5 13,5 14,37 15,5 16,5
y 7,173 19,5 31,5 37,6 39,1 42,6 44,3 45,2

 

Значення х, для якого розрахувати у х=1

 

Варіант7

Результати експериментів

x 5,5 10,5 15,5 20,5
y -1,5 1,6 2,7 3,75 4,85 5,7 7,25 7,9

Значення х, для якого розрахувати у х=5

Варіант8

Результати експериментів

x 15,5 40,6 53,6 68,1 76,5
y 1,1 1,6 2,7 4,15 4,85 5,7 6,65 7,15

 

Значення х, для якого розрахувати у х=20

 

Варіант9

Результати експериментів

x 0,53 3,25 6,3 7,5 13,5 24,5 27,5
y -5,1 -7,27 -9,57 -11,8 -17,6 -29,5 -37,8 -41,6

 

Значення х, для якого розрахувати у х=11

 

Варіант10

Результати експериментів

x 0,23 3,25 6,3 7,5 13,5 23,5 24,5 27,5
y -5,1 -2,35 -3,5 -11,8 -17,6 -31,5 -37,8 -41,5

 

Значення х, для якого розрахувати у х=14

 

Варіант11

Результати експериментів

x 0,47 3,25 6,3 7,5 13,5 21,5 24,5 27,5
y -6,52 -7,266 -9,57 -11,8 -17,6 -31,5 -37,8 -41,5

 

Значення х, для якого розрахувати у х=16

 

Варіант12

Результати експериментів

x 0,36 0,86 1,5 7,5 13,5 19,6 24,5 27,5
y -6,52 -7,266 -9,57 -11,8 -17,6 -31,5 -37,8 -41,5

Значення х, для якого розрахувати у х=18

 

Варіант13

Результати експериментів

x 0,63 4,25 6,3 7,5 13,5 23,5 24,5 27,5
y -13,1 -7,27 -9,57 -11,8 -17,6 -31,5 -37,8 -41,5

 

Значення х, для якого розрахувати ух=24

 

Варіант14

Результати експериментів

x 1,36 2,5 6,5 8,5 13,5 25,6 29,5
y 1,23 2,3 4,56 5,12 6,27 7,85 8,56 9,58

 

Значення х, для якого розрахувати у х=19

Варіант15

Результати експериментів

x 0,78 1,25 6,3 7,5 13,5 25,5 35,7
y 4,454 4,9 5,116 5,26 5,98 7,12 8,25 9,15

 

Значення х, для якого розрахувати у х=10

 

Варіант16

Результати експериментів

x 1,2 2,5 3,2 4,5 7,1 15,5
y 2,55 3,25 2,976 2,65 2,12 0,36 1,45 2,45

Значення х, для якого розрахувати у х=11

 

Варіант17

Результати експериментів

 

x 0,53 1,25 6,3 7,5 12,5 24,5 36,2
y 5,45 5,95 6,23 6,66 6,95 8,52 9,25 10,1

Значення х, для якого розрахувати у х=15

 

Варіант18

Результати експериментів

 

x 0,2 1,5 2,2 3,5 6,1 13,5
y 5,30 5,2 4,976 4,65 4,12 2,36 3,45 3,45

Значення х, для якого розрахувати у х=5

 

Варіант19

Результати експериментів

 

x 0,42 0,53 1,25 6,3 7,5 13,5
y -6,58 -5,98 -5,46 -1,82 -0,96 3,36 9,68 13,18

Значення х, для якого розрахувати у х=8

 

Варіант20

Результати експериментів

 

x 12,5 17,5 20,5 31,5 38,5 45,5 55,5
y 2,64 6,24 8,4 12,1 12,9 12,3 12,6 12,1

Значення х, для якого розрахувати у х=18

 


Література

1. Фигурнов В.Э. IBM PC для пользователя, М., Инфра.

2. Виктор Пасько Microsoft Office 2000 (русифицированная версия) – К.: Издательская гр. BHV, 2000.

3. Симонович С., Евсеев Г. Практическая информатика: универсальный курс. – М.: АСТ-ПРЕСС; Инфорком-Пресс, 1999,480 с.

4. Симонович С., Евсеев Г. , Алексеев А. Специальная информатика: универсальный курс. – М.: АСТ-ПРЕСС; Инфорком-Пресс, 1999,480 с.

5. Симонович С. Информатика : базовый курс. – СПб. : Питер, 1999, 640 с.

6. Воробьев В.В. Microsoft Excel 2000 : Пособие для начинающих -К. : 2000, 36 с.

7. Рычков В. Самоучитель Excel 2000. – СПб.: Питер, 1999, 512 с.

8. Волков В.Б. Понятный самоучитель Excel 2010. — СПб.: Питер, 2010. — 256 с.

9. Голышева А. В., Корнеев В. Н. Excel 2007 «без воды». Все, что нужно для уверенной работы— СПб.:.Наука и Техника, 2008. — 192 с: ил.

10. Т.В. Зудилова, СВ. Одиночкина, И.С. Осетрова, Н.А. Осипов Работа пользователя в Microsoft Excel 2010 - СПб: НИУ ИТМО, 2012. - 87 с.

11. Иванов И. Мicrosoft Excel 2010 для квалифицированного пользователя. Академия АЙТИ. www.academy.it.ru