Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Лабораторна робота №
Тема : Використання формул в Excel для розв’язування статистичних та економічних задач
Мета : Навчитись використовувати формули та функції в Excel для розв’язування статистичних та економічних задач.
Теоретичні відомості.
Можливість використання формул і функцій є одним з найважливіших властивостей програми обробки електронних таблиць.
Основний сенс використання таблиць Excel полягає у тому, що один раз склавши формули і ввівши їх у необхідні комірки, є можливість потім тільки змінити дані, а всі розрахунки лежать на програмі. Це, по - перше, заощаджує час, а, по – друге, “машина не робить помилок”. При цьому в будь-який час є можливість відредагувати формули і тим самим змінити порядок розрахунків, що дає дуже велику гнучкість розрахунків.
Формула в табличному процесорі починається із символу “=” і являє собою сукупність операндів, з’єднаних між собою знаками операцій і круглих дужок. Операндом може бути число, текст, логічне значення, адреса комірки (посилання на комірку), функція.
У формулах розрізняються арифметичні операції і операції відношень. Excel допускає:
Арифметичні операції і операції відношень виконуються над числовими операндами. Над текстовими операндами виконується операція &, яка до тексту першого операнда приєднує текст другого операнда.
Хід виконання
За статистичними даними за 10 років (в тис.грн) про валовий випуск продукції Y та основних виробничих фондів Х , які наведені в табл.1 необхідно :
Таблиця 1
Статистичні дані
|
Рік |
Y |
Х |
|
|
415 + а5 |
280+ а1 |
|
|
481 + а4 |
310+ а2 |
|
|
500 + а3 |
325+ а3 |
|
|
565 + а2 |
360+ а4 |
|
|
592 + а1 |
400+ а5 |
|
|
618 + а5 |
420+ а1 |
|
|
615 + а4 |
532+ а2 |
|
|
645 + а3 |
556+ а3 |
|
|
741 + а2 |
637+ а4 |
|
|
554+ а1 |
332+ а5 |
Параметри а1, а2, а3, а4, а5 вибираються з таблиці.2
Таблиця 2
Значення параметрів
|
0 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
13 |
14 |
15 |
|
|
а1 |
4 |
0 |
1 |
4 |
7 |
6 |
2 |
1 |
3 |
5 |
9 |
1 |
10 |
11 |
0 |
4 |
|
а2 |
1 |
3 |
4 |
8 |
9 |
10 |
5 |
7 |
1 |
4 |
9 |
8 |
0 |
4 |
5 |
6 |
|
а3 |
7 |
11 |
48 |
9 |
7 |
1 |
3 |
5 |
8 |
7 |
14 |
2 |
6 |
9 |
1 |
1 |
|
а4 |
15 |
5 |
8 |
9 |
7 |
4 |
6 |
1 |
1 |
2 |
3 |
1 |
8 |
4 |
8 |
0 |
|
а5 |
1 |
7 |
8 |
9 |
2 |
5 |
8 |
4 |
6 |
3 |
1 |
5 |
7 |
2 |
5 |
9 |
Розв’язок
Заповнимо таблицю даними з урахуванням коефіцієнтів 0 варіанту
|
а1 |
а2 |
а3 |
а4 |
а5 |
|
4 |
1 |
7 |
15 |
1 |
Таблиця 3
Значення даних для розв’язування задачі
|
а1 |
а2 |
а3 |
|
4 |
1 |
7 |
|
а1 |
а2 |
а3 |
|
4 |
1 |
7 |
|
а1 |
а2 |
а3 |
|
4 |
1 |
7 |
|
а1 |
а2 |
а3 |
|
4 |
1 |
7 |
|
а1 |
а2 |
а3 |
|
4 |
1 |
7 |
|
а1 |
а2 |
а3 |
Щоб визначити форму зв’язку між валовим випуском продукції Y та основними виробничими фондами Х необхідно побудувати графік – кореляційне поле .
На осі абсцис відкладемо значення факторної ознаки Х, а на осі ординат – результативної ознаки Y. Лінійна залежність зображена на рис.1.
Рис.1 Кореляційне поле залежності валового випуску продукції від основних фондів
Графік показує , що зв’язок близький до прямолінійного і його можна виразити рівнянням прямої лінії:
ỹх=а+bx,
де ỹх – валовий випуск продукції, тис.грн;
х – задіяні основні виробничі фонди, тис.грн;
а, b – параметри рівняння.
Параметри рівняння прямої а, b знайдемо із системи нормальних рівнянь
Усі потрібні для системи рівнянь дані визначимо в табл.2.4:
Таблиця 2.4.
|
№ |
Рік |
Валовий випуск продукції, тис.грн |
Основні виробничі фонди, тис. грн |
Розрахункові величини |
|||
|
|
|
Y |
Х |
XY |
Y2 |
X2 |
ỹх |
|
1 |
1993. |
416 |
284 |
118144 |
173056 |
80656 |
483,2608 |
|
2 |
1994. |
496 |
311 |
154256 |
246016 |
96721 |
501,9988 |
|
3 |
1995. |
507 |
332 |
168324 |
257049 |
110224 |
516,5728 |
|
4 |
1996. |
566 |
375 |
212250 |
320356 |
140625 |
546,4148 |
|
5 |
1997. |
596 |
401 |
238996 |
355216 |
160801 |
564,4588 |
|
6 |
1998. |
619 |
424 |
262456 |
383161 |
179776 |
580,4208 |
|
7 |
1999. |
630 |
533 |
335790 |
396900 |
284089 |
656,0668 |
|
8 |
2000. |
652 |
563 |
367076 |
425104 |
316969 |
676,8868 |
|
9 |
2001. |
742 |
652 |
483784 |
550564 |
425104 |
738,6528 |
|
10 |
2002. |
558 |
333 |
185814 |
311364 |
110889 |
517,2668 |
|
Разом |
--- |
5782 |
4208 |
2526890 |
3418786 |
1905854 |
5782 |
|
В серед ньому |
--- |
578,2 |
420,8 |
252689 |
341878,6 |
190585,4 |
578,2 |
Поділимо рівняння на коефіцієнти при a (на 10 і 4208 відповідно), отримаємо наступну систему
Віднімемо від другого рівняння перше
22,29=32,1b, звідки b=0,694.
Підставляємо в перше рівняння
5782=10а+4208*0,694;
5782=10а+2920,352;
5782-2920,352=10а; а=286,1648
Кореляційне рівняння буде мати вигляд ỹх=286,1648+0,694x
Для перевірки правильності обчислень коефіцієнтів побудуємо лінію тренду (виділити графік, на ньому клацнути правою кнопкою миші, вибрати операцію „Добавить линию тренда”, вибрати на закладці Тип – Линенйная, на закладці Параметры – Ввести назву лінії та включити параметр „Показывать уравнение на диаграме”)
Рис.2. Кореляційне поле залежності валового випуску продукції від основних фондів із лінією тренду
Побудувавши лінію тренду (за допомогою спеціальної функції в MS Excel) рис.2, визначаємо рівняння і бачимо, що коефіцієнти збігаються із розрахованими.
Параметри рівняння регресії можна також визначити за іншими формулами:
Підставляємо значення:
Перевіримо правильність розв’язання системи рівнянь , виходячи із рівності
ỹх=а+bx
578,2=286,022+0,694*420,8
578,2=578,2
Обчислимо лінійний коефіцієнт кореляції за формулою
, де ,
Коефіцієнт кореляції показує, що між кількістю основних виробничих фондів та валовим випуском продукції спостерігається тісний зв’язок – 92,8%, 7,2% - це вплив інших факторів, які не були враховані в даному випадку.
Використана література
PAGE 1