Комп’ютерний аналіз даних засобами MS Excel

Размещено на http://www.allbest.ru/

Размещено на http://www.allbest.ru/

Міністерство освіти і науки України

Національний транспортний університет

Факультет транспортних та інформаційних технологій

Кафедра інформаційних систем і технологій

Курсова робота

з дисципліни «Комп'ютерні технології статистичної обробки інформації»

Комп'ютерний аналіз даних засобами MS Excel

Виконав:

студент групи КН-ІІІ-1

Биховський М.Р.

Перевірила:

доц. Парохненко Л.М.

Київ 2016



Зміст

чотирифакторний регресія довірчий границя

Вступ

Тема 1. Парна лінійна регресія

Завдання 1. Лінійна кореляційна модель

Завдання 2. Функції Excel для обробки даних

Тема 2. Множинні регресійні моделі

Завдання 3. Регресійний аналіз у матричній формі

Завдання 4. Багатовимірна лінійна модель

Завдання 4.1 Видалення стовпців

Завдання 5. Статистичний аналіз даних: розрахунок параметрів лінійної чотирифакторної регресії

Завдання 6. Лінійні залежності

Завдання 7. Довірчі границі на лінію регресії

Завдання 8. Довірчі границі в загальному випадку

Завдання 9. Виробничі функції Коба-Дугласа

Завдання 10. Використання надбудови «Пакет анализа»

Завдання 11. Дисперсійний аналіз (ANOVA)

Висновок

Список використаної літератури



Вступ

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

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

Електронна таблиця MS Excel є універсальним обчислювальним

інструментом та інструментом статистичного аналізу даних.

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

Метою даної курсової роботи є отримання та формування навичок у процесі вивчення навчальної дисципліни «Комп'ютерні технології статистичної обробки інформації» використовуючи універсальний обчислювальний інструмент та інструмент статистичного аналізу даних - електронні таблиці MS Excel.

Об'єктом дослідження є статистичний аналіз, збір статистичних відомостей, що характеризують окремі одиниці яких-небудь масових сукупностей та дослідження отриманих даних, що полягає в з'ясуванні тих закономірностей, які можуть бути встановлені на основі даних масового спостереження і розробка прийомів статистичного спостереження й аналізу статистичних даних.

Предметом дослідження є аналіз даних за допомогою різних методів зіставлення отриманих цифрових даних між собою та з іншими даними, їх узагальнення.



Тема 1. Парна лінійна регресія

Завдання 1. Лінійна кореляційна модель

У статистиці лінійна регресія -- це метод моделювання залежності між скаляром y та векторною (у загальному випадку) змінною X. У випадку, якщо змінна X також є скаляром, регресію називають простою.

При використанні лінійної регресії взаємозв'язок між даними моделюється за допомогою лінійних функцій, а невідомі параметри моделі оцінюються за вхідними даними. Подібно до інших методів регресійного аналізу лінійна регресія повертає розподіл умовної імовірності y в залежності від X, а не розподіл спільної імовірності y та X, що стосується області мультиваріативного аналізу.

При розрахунках параметрів моделі лінійної регресії як правило застосовується метод найменших квадратів, але також можуть бути використані інші методи. Так само метод найменших квадратів може бути використаний і для нелінійних моделей. Тому МНК та лінійна регресія хоч і є тісно пов'язаними, але не є синонімами.

Мета роботи. За даними спостережень (X,Y) потрібно:

оцінити параметри (коефіцієнти регресії) лінійної моделі

за відомими формулами:

де Rxy - коефіцієнт парної кореляції



Sxy, Sx, Sy -- коваріація і стандартні відхилення:

,

,

,

,

;

побудувати графік знайденої залежності разом з емпіричними точками;

дати інтерпретацію отриманим результатам;

оформити роботу у вигляді звіту на робочому листі Excel.



Завдання 2. Функції Excel для обробки даних

Мета роботи. Потрібно освоїти функції MS Excel для обробки даних:

графічні опції Добавить тренд. Показать уравнение і R2:

функції КОРРЕЛ(), НАКЛОН(), ОТРЕЗОК(), ПРЕДСКАЗ();

функцію діапазону ЛИНЕЙН();

вивчити статистичні показники функції ЛИНЕЙН.



Тема 2. Множинні регресійні моделі

Завдання 3. Регресійний аналіз у матричній формі

Мета роботи. За даними спостережень потрібно:

освоїти техніку роботи з матричними функціями Excel;

оцінити параметри лінійної моделі за допомогою матричних перетворень В=(Х^тХ)^-1Х^т, де Y - матриця-стовпець значень у_і, Х^т означає транспонування, матриця X = [Х0, Х1, ... , Хm] складається зі стовпців значень кожного показника-аргументу х₁, х₂, ... , x_m, перший стовпець Х0 складається з одних одиниць;

оформити роботу у вигляді звіту.

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

за відомими формулами лінійного регресійного аналізу; при розрахунках замість заповнення таблиць проміжних результатів можна використовувати функції Excel СЧЕТ(), СУММ(), СУММПРОИЗВ(), СУММКВ(), СУММКВРАЗН();

в матричній формі, використовуючи вкладені функції Excel без формування і запису на робочому листі транспонованої матриці XT.



Завдання 4. Багатовимірна лінійна модель

Мета роботи. За даними спостережень (Х1, Х2, ХЗ, У) потрібно:

розрахувати параметри трифакторної моделі в матричній формі;

розрахувати ці самі параметри за допомогою функції ЛИНЕЙН();

заповнити таблицю дисперсійного аналізу для m <= 3;

виготовити шаблон для розрахунку параметрів багатовимірної моделі;

розрахувати параметри лінійної багатовимірної регресії у матричній формі і за допомогою функції ЛИНЕЙН(). Провести дисперсійний аналіз для оцінки значущості багатовимірної регресії.



Завдання 4.1 Видалення стовпців

Видаляються стовпці X2, X3 та проводяться аналогічні розрахунки.



Після видалення стовпців усе автоматично перераховується (перераховується правильно, за винятком дисперсійного відношення F, що тепер занижене у 2/1 =2 рази); у зайвих клітинках буде повідомлення #Н/Д.

Завдання 5. Статистичний аналіз даних: розрахунок параметрів лінійної чотирифакторної регресії

Мета роботи. За даними спостережень (Х1, Х2, ХЗ, Х4, Y) потрібно:

зробити розрахунок параметрів чотирифакторної моделі:

обчислити розрахункові значення Yр, варіюючи по черзі кожну пояснюючу змінну Xk при фіксованих значеннях інших аргументів;

побудувати графіки розрахункових значень за кожним аргументом;

перевірити стійкість обчислених ОЦІНОК пробним вибракуванням сумнівних даних (викидів або незначущих за Ст'юдентом членів);

установити факт наявності або відсутності мультиколінеарності;

записати рівняння регресії і дати інтерпретацію кожному параметру;

привести значення коефіцієнта детермінації і пояснити його зміст;

оцінити значимість моделі в цілому за критерієм Фішера;

оцінити значимість кожного члена моделі за критерієм Ст'юдента;

побудувати довірчі інтервали на коефіцієнти регресії.



Завдання 6. Лінійні залежності

Роботу виконуємо в наступній послідовності:

у попередній роботі замінюємо вихідні дані. Видаляємо стовпець F, що містить значення змінної х4; додаємо ще один рядок у середині таблиці (додається новий рядок робочого аркуша); на підготовлене місце копіюємо нові дані. Пересуваємо на одну позицію рядок заголовків bЗ, b2, b1, b0 перед висновком функції ЛИНЕЙН(). Усе одразу перераховується, за винятком стовпця Ур(х4) - цієї змінної більше немає. Останній стовпчик висновку функції ЛИНЕЙН() зайвий та заповнений повідомленнями #Н/Д;

змінюємо на порожніх графіках заголовки осей і для кожної осі вказуємо нові мінімальні і максимальні значення, а також нові ціни основних ділень. З'являються три графіки заданих залежностей. Четвертий графік залишається порожнім (немає змінної х4);

обчислюємо кореляційну матрицю і переконуємося в існуванні тісних кореляційних ЗВ'ЯЗКІВ між пояснюючими змінними;

робимо повний статистичний аналіз залежності, інтерпретуємо оцінки параметрів, оцінюємо значимість моделі в цілому і значимість її окремих членів;

для демонстрації апроксимаційних властивостей багатовимірної моделі будуємо графік Спостереження-Розрахункові значення (У - Ур);

робимо пробні вибракування незначущих членів, домагаючись мінімуму незміщеної оцінки залишкової дисперсії (MSE).



Завдання 7. Довірчі границі на лінію регресії

Мета роботи. За даними спостережень (Х,У) потрібно побудувати лінію регресії разом із 95%-и довірчими смугами на розрахункові значення Ур і на очікуваний розкид даних навколо лінії регресії (прогнози).

Роботу виконуємо в наступному порядку:

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

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

обчислюємо залишкову дисперсію МSЕ;

для кожного спостереження підраховуємо дисперсію розрахункового значення Sрр за відомою формулою для одновимірного випадку (m=1):

обчислюємо дисперсії Sрр за загальною формулою в матричній формі:

розраховуємо границі 95%-х довірчих інтервалів на розрахункові значення (95%р) і на прогнози (95%q):

будуємо графік залежності разом з довірчими границями.



Завдання 8. Довірчі границі в загальному випадку

Мета роботи. За даними спостережень (Х1, Х2, ХЗ, У) потрібно побудувати графіки компонентних ефектів разом з 95%-и довірчими смугами на розрахункові значення Ур і на очікуваний розкид даних навколо лінії регресії (прогнози).

Роботу виконуємо в наступному порядку:

робимо розрахунок параметрів трифакторної моделі в матричній формі;

для кожного спостереження X обчислюємо розрахункові значення Ур;

обчислюємо залишкову дисперсію МSЕ;

варіюємо кожен показник окремо при середніх значеннях інших;

для кожного варіанта підраховуємо розрахункові значення і дисперсію розрахункового значення Sрр за загальною формулою в матричній формі;

розраховуємо границі 95%-х довірчих інтервалів на розрахункові значення (95%р) і на прогнози (95%q);

будуємо графіки залежності від кожної змінної разом з довірчими границями.



Завдання 9. Виробничі функції Коба-Дугласа

Мета роботи. Освоїти обробку даних за загальною степеневою моделлю й моделлю Коба - Дугласа, оцінити еластичності виробничих факторів і ефект масштабу виробництва, увести в модель експоненціальний часовий тренд для оцінки ефекту науково-технічного прогресу.

Роботу виконуємо в наступному порядку:

скласти модель Коба - Дугласа.

оцінити значущість масштабного ефекту і часового тренда.

оцінити еластичності виробничих факторів.



Завдання 10. Використання надбудови «Пакет анализа»

Мета роботи. Ознайомитися з можливостями надбудови “Пакет анализа" і освоїти процедуру “Регрессия” цієї надбудови. Порівняти результати роботи процедури «Регрессия» с висновками функції ЛИНЕЙН.

Роботу виконуємо в наступному порядку:

За допомогою надбудови "Пакет аналіза" зробить математичну обробку тих самих даних, що були прийняті як до попереднього завдання;

Порівняти отримані результати.



Завдання 11. Дисперсійний аналіз (ANOVA)

Мета роботи. Познайомитися з процедурами дисперсійних аналізів надбудови «Пакет аналіза» вивчити застосування однофакторного і двофакторного дисперсійного аналізу. У складі регресійного аналізу також є декілька специфічний блок дисперсійного аналізу для перевірки значимості регресійної моделі. Коли є можливість застосувати обидві різновиди аналізу, перевіряють також адекватність прийнятої моделі, тобто адекватність прийнятої форми зв'язку.

Завдання:

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



Висновок

У процесі виконання курсової роботи було отримано та сформовано навички, які були здобуті у процесі вивчення навчальної дисципліни "Комп'ютерні технології статистичної обробки інформації", а саме: було визначено ознаковий простір для опису реальних об'єктів і процесів; сформовано репрезентативні сукупності спостережень ознак економічних процесів, перевірено їх однорідність; с модельовано залежності результативних ознак від пояснюючих факторів; проаналізовано отримані результати; визначено стандартні похибки параметрів моделі і перевірено їх значущість; було застосовано статистичні критерії для перевірки якості моделі у цілому; як враховувати у моделі якісні змінні, коли серед факторів присутні як кількісні, так і якісні змінні; використані інструментальні і заміщаючі змінні, с модельовано вплив запізнюючої дії факторів на результативні ознаки; складено регресійні моделі; проаналізовано такі моделі і визначено як короткочасні, так і довгострокові прогнози; проаналізовано часові ряди спостережень для прогнозування розвитку об'єктів, явищ і процесів.

У процесі виконання курсової роботи було засвоєно особливості обчислювальних алгоритмів і доводять теоретичні міркування до практичних розрахунків. Усе це передбачає подальший змістовний аналіз проблеми, інтерпретацію отриманих результатів та їх економічні висновки.

Потужність програм електронних таблиць визначається складністю завдань, які можна вирішувати з їх допомогою. В Excel для вирішення складних завдань передбачена надбудова Пакет аналізу. Ця надбудова пропонує прості у використанні, але досить потужні засоби для вирішення фінансових, статистичних, інженерних завдань, задач з області наукових досліджень, освіти та багатьох інших.

Огляд пакету аналізу:

Пакет аналізу - це надбудова, що забезпечує доступ до засобів аналізу, які зазвичай не входять в стандартну поставку Excel. Пакет аналізу складається з двох частин:

аналітичні процедури;

вбудовані функції.

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

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

Дисперсійний аналіз (гри виду).

Кореляційний аналіз.

Коваріаційний аналіз.

Описова статистика.

Експоненційне згладжування.

F-тест.



Список використаної літератури

1. Microsoft Excel 2003. Стислий курс.: - М.: Видавничий дім «Вільямс», 2007. - 288 с.

2. Боровиков В.П. Искусство анализа данных на компьютере: Для профессионалов. 2-е изд. - СПб. - Питер, 2003. - 688 с.

3. Дэвид М., Левин и др. Статистика для менеджеров с использованием Microsoft Excel, 4 -е изд. М. 2004 г. - 1312 с.

4. Тюрин Ю.Н., Макаров А.А. Анализ данных на компьютере. - М.: ИНФА-М., 2003. - 544 с.

5. Макарова Н.В., Трофимец В.Я. Статистика в Excel: Учеб. Пособие. М.: Финансы и статистика. 2003. - 386 с.

6. http://office.microsoft.com - офіційний сайт Корпорації Майкрософт (Microsoft Corporation).

7. Тюрин Ю.Н., Макаров А.А. Анализ данных на компьютере. - М.:ИНФА- М., 2003. - 544 с.

8. https://ru.wikipedia.org - вільна енциклопедія.

Размещено на Allbest.ru

...