Методичні вказівки до практичних занять по темі "Елементи статистичної обробки даних за допомогою електронних таблиць"



Сторінка1/5
Дата конвертації27.04.2016
Розмір0.61 Mb.
  1   2   3   4   5

{fd file=departments/biophysics/IT Pharm Lab Statistica 1.doc=Методичні вказівки до практичних занять по темі “Елементи статистичної обробки даних за допомогою електронних таблиць”}

Елементи статистичної обробки даних

за допомогою процесора електронних таблиць MS Excel



Мета заняття:

Загальна: ознайомитись з елементарними засобами статистичної обробки даних табличного процесора Excel.
Конкретна:

  1. Познайомитись з статистичними функціями процесора електронних таблиць (ПЕТ) Excel та принципами їх використання.

  2. Навчитись практично виконувати статистичну обробку даних засобами ПЕТ Excel.



Завдання по самопідготовці:

  1. Повторити теоретичний матеріал з математичної статистики.

  2. Ознайомитись з порядком виконання роботи.

  3. Повторити і підготувати у протоколі формули для розрахунку основних точкових та інтервальних оцінок варіаційних рядів, алгоритми 1-факторного дисперсійного, кореляційного та лінійного регресійного аналізу.

  4. Побудувати у протоколі кореляційне поле і орієнтовну лінію регресії до завдання 7.

  5. Підготувати протокол лабораторної роботи.


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

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

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

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

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

Засоби ПЕТ MS Excel, які дозволяють виконувати статистичний аналіз даних, можна розділити на три рівні:



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

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

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

Крім того, існує можливість створення власних програм обробки даних на основі Excel за допомогою мови програмування Visual Basic for Applications (VBA), яка входить до системи Office як загальний внутрішній засіб програмування і розширення можливостей аплікацій. Ця мова являє собою обмежену версію сучасної мови програмування Visual Basic, яка орієнтована на використання засобів та об’єктів системи Office. В такий спосіб можуть бути створені як програми, що просто автоматизують роботу користувача зі звичайними засобами Excel або організують зручний спеціалізований інтерфейс для деякої задачі, так і цілі додаткові аплікації для виконання складних алгоритмів обробки даних. Такий підхід вимагає глибокого знання математичної статистики і її конкретних прикладних аспектів, ґрунтовного володіння засобами ПЕТ Excel і системи Office, а також володіння навичками алгоритмізації і програмування задач, що ускладнює його застосування на рівні звичайних користувачів для типових задач аналізу даних.

Залежно від особливостей розв’язуваної задачі, власних потреб, уподобань та кваліфікації користувач може скористатися будь-якими з наявних засобів.

Як і в інших сферах застосування електронних таблиць, побудований для деякої задачі робочий аркуш (або робоча книга чи група акушів) являє собою по суті «спеціалізований калькулятор» для задач даного класу. Наявність такого «калькулятора» звільняє від необхідності проходження усієї процедури розв'язання задачі, знижує вимоги до рівня математичної підготовки користувача, усуває ризик обчислювальних помилок тощо. Розв'язання задачі потребує завдяки йому тільки правильного введення даних і наступної інтерпретації результатів. Правда, користувач повинен дбати про коректність даних, зокрема перевіряти їх припустимість, добре розуміти обмеження програми і правильно інтерпетувати результати. Перевірка коректності вхідних даних може бути забезпечена за допомогою спеціального додаткового програмування (за допомогою VBA), яке дозволяє створити цілу оболонку «калькулятора» зі зручним інтерфейсом користувача.

Статистичні функції, разом з іншими функціями (математичними, фінансовими, дати і часу та ін.) є обов’язковим компонентом ПЕТ Excel. Виклик функції здійснюється за її іменем у відповідності з наступним форматом:



<ім’я_функції> [(аргументи функції)],

де <ім’я_функції> – ідентифікатор функції, [(аргументи функції)] – це список діапазонів та чисельних значень, які є аргументами функції. Список аргументів заключається у дужки, елементи списку розділяються комами. Деякі функції можуть не мати списку аргументів.

Функції можна вставити у формулу шляхом введення її імені і аргументів за допомогою клавіатури, але цей спосіб повільний і складний, оскільки необхідно пам’ятати ім’я і формат виклику кожної функції. ПЕТ Excel надає інший спосіб виклику функцій – за допомогою майстра функцій. Майстер функцій виконує 2- крокову процедуру створення виклику функцій. На першому кроці за допомогою діалогового вікна здійснюється вибір функції, а на другому – задаються аргументи функцій.

Перше діалогове вікно майстра функцій наведено на рис.1. У верхній частині вікна ліворуч знаходиться список вибору категорій (груп) функцій, а праворуч – список вибору конкретної функції обраної категорії. На рис.1 обрано категорію Статистические і функцію ДОВЕРИТ. Нижче названих полів вибору знаходиться довідковий рядок, в якому наводиться формат виклику обраної функції, а під ним – коротка довідка по обраній функції. У кутку ліворуч знаходиться кнопка довідки (позначена знаком питання), за допомогою якої викликається Помощник, який дозволяє швидко отримати повну довідку по обраній функції. У кутку праворуч знаходяться керуючі кнопки ОК (затверджує результати роботи першого кроку) та Отмена (відміняє результати роботи і закриває майстра функцій).

Р
ис.1
На рис.2 наведено діалогове вікно другого кроку майстра функцій для функції ДОВЕРИТ (для інших функцій це вікно має таку ж будову, відмінність тільки у наборі аргументів функцій). У верхній частині вікна (у рамці з іменем функції) знаходиться група полів введення (для кожного аргументу функції – своє поле, позначене іменем аргумента). Під рамкою знаходиться поле результату, позначене знаком рівності, в якому відображається результат функції при заданих аргументах (це дозволяє судити про правильність задання функції в процесі роботи майстра). Нижче знаходиться текстове поле короткої довідки по даній функції (вміст його той самий, що і у довідці першого кроку). Під текстом довідки знаходиться текстове поле списку аргументів функції, в якому міститься ім’я аргументу, поле введення якого обрано в даний момент, і коротка довідка по цьому аргументу. У нижній частині вікна знаходяться кнопки довідки, ОК та Отмена, також поле Значение, в якому відображається результат обчислення значення функції.



Рис.2
Поля введення аргументів можуть містити ті ж значення, що і формули комірок Excel: константи (числові, текстові і логічні), посилання на комірки та діапазони, а також формули Excel, за якими обчислюються значення аргументу.

Введення значень аргументів може виконуватись за допомогою клавіатури шляхом редагування вмісту поля введення (зауважте, що при виборі поля введення в ньому з’являється блимаючий текстовий курсор, а це означає, що поле є текстовим). Якщо аргументами функції є комірки та діапазони таблиці, то більш зручним способом введення є вказування їх безпосередньо у таблиці. Для цього треба тимчасово прибрати з екрану діалогове вікно майстра за допомогою кнопки згорнення діалогового вікна (вона знаходиться на правому кінці поля введення і має вигляд квадрата з червою стрілкою, спрямованою з правого нижнього кута до лівого верхнього). При натисненні цієї кнопки діалогове вікно згортається і залишається тільки обране поле введення у вигляді довгого рядка під рядком формул. На правому кінці цього поля знаходиться кнопка розгорнення вікна (квадрат з червоною стрілкою, направленою вертикально донизу). Для введення в поле аргументу значення комірки слід клацнути на цій комірці, а для введення діапазону комірок – протягнути по ньому ГВ при натисненій лівій кнопці ГМ; в результаті у полі аргументу з’являється адреса комірки або діапазону комірок. Після задання аргумента слід розгорнути діалогове вікно (кнопкою розгорнення) і перейти до поля наступного аргумента або завершити роботу майстра функцій.

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

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

Деякі функції MS Excel мають дві версії, одна з яких ігнорує порожні комірки, текстові та логічні значення, а інша враховує текстові та логічні значення таким чином, що аргументи, які містять значення ИСТИНА, інтерпретуються як 1, а ті, що містять значення ЛОЖЬ, – як 0. Перелік таких функцій міститься у наступній таблиці:


Дія

функції


Ім.’я функції

Ігнорує текст та логічні значення

Враховує текст та логічні значення

Розрахунок дисперсії за виборкою

ДИСП

ДИСПА

Розрахунок дисперсії для генеральної сукупності

ДИСПА

ДИСПРА

Знаходження максимального значення з набору значень

МАКС

МАКСА

Знаходження мінімального значення з набору значень

МИН

МИНА

Розрахунок середнього значення

СРЗНАЧ

СРЗНАЧА

Розрахунок стандартного відхилення за вибіркою

СТАНДОТКЛОН

СТАНДОТКЛОНА

Розрахунок стандартного відхилення для генеральної сукупності

СТАНДОТКЛОНП

СТАНДОТКЛОНПА

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

Більш потужні засоби статистичної обробки даних надає пакет аналізу. Надбудова пакет аналізу викликається командою Анализ данных, яка входить у меню Сервис. По команді Анализ данных видається вікно (рис.3), яке містить список вибору інструментів аналізу, з якого обирається потрібний засіб, і керуючі кнопки ОК (підтверджує вибір і здійснює перехід до налаштування обраного інструмента), Отмена (відміняє виклик пакету аналізу), Справка (викликає розділ Статистический анализ довідкової системи). Після вибору певного інструменту видається діалогове вікно налаштування, за допомогою якого задаються вхідні дані та параметри інструменту. Способи введення вхідних даних (аргументів) повністю аналогічні тим, що надаються майстром функцій. Командна кнопка Справка викликає довідку по обраному інструменту, а кнопка ОК запускає обраний інструмент аналізу з встановленими параметрами.




Рис.3
Для прикладу на рис.4 наведене діалогове вікно налаштування інструменту Корреляция.

Пакет аналізу не є обов’язковим компонентом Excel, який встановлюється автоматично при інсталяції аплікації. Якщо команди Анализ данных в меню Сервис немає, слід обрати пакет аналізу і активізувати його за допомогою команди Надстройки меню Сервис. Якщо ж це виявляється неможливим, потрібно запустити програму інсталяції MS Excel і встановити пакет аналізу.

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





Рис.4
Порядок виконання роботи



Завдання 1. Запустити ПЕТ Excel і відкрити нову робочу книгу.

Завдання 2. Розрахунок ймовірності і теоретичної частоти приналежності нормально розподіленої випадкової величини заданому проміжку.

Задача. Виміряно параметр х на виборці об’ємом М=1200 зразків. Виходячи з того, що параметр х має нормальний розподіл N(27;2,3), визначити ймовірність і теоретичну частоту приналежності параметра х проміжку [23,17; 28,1].



Теоретична довідка.

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



, (2.1)

де F(x) – функція розподілу випадкової величини х.

Якщо випадкова величина х підпорядковується нормальному розподілу N (μ; σ), то ймовірність приналежності випадкової величини проміжку [х1; х2] визначається за допомогою стандартного нормального розподілу наступним чином:


  1. Перейти до нормованої змінної z, яка підпорядковується стандартному нормальному розподілу N (0; 1):

. (2.2)

Проміжок [х1, х2] при цьому відображається у проміжок [z1; z2], де



, (2.3).

  1. Знайти значення функції Лапласа Ф(z) на межах проміжку [z1; z2], Ф(z1) і Ф(z2).

  2. Обчислити шукану ймовірність приналежності величині х проміжку

. (2.4)

Для виборки обсягом N нормально розподіленої величини х теоретична частота mT приналежності проміжку [х1, х2] розраховується за формулою:



. (2.5)

Практичне виконання .

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



2
.1.
На робочому аркушеві створити розрахункову таблицю за зразком рис.5.

Рис.5
У комірках А1-К1 знаходяться заголовки стовпчиків:

А1= „Мат.спод.” (математичне сподівання параметрів х)

В1= „Стандарт” (стандартне відхилення)

С1= „Об’єм” (об’єм виборки)

D1= „Межа Х1” (нижня межа проміжку)

E1= „Межа Х2” (верхня межа проміжку)

F1= „z1” (нижня межа нормалізованого проміжку)

G1= „z2” (верхня межа нормалізованого проміжку)

H1= „Ф(z1)” (значення функції Лапласа на нижній межі z1)

I1= „Ф(z2)” (значення функції Лапласа на верхній межі z2)

J1= „Р” (ймовірність приналежності параметра х проміжку [х1, х2])

K1= „n теор.” (теоретична частота приналежності параметра х заданому проміжку)

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

А2= 27

В2= 2,3


С2= 1200

D2= 23,17

E2= 28,1

2.2. Ввести у комірки робочого аркуша формули переходу до нормованої змінної. Перехід до нормованої змінної z виконується за формулою:

,

де μ – математичне сподівання;

σ – стандартне відхилення.

Для перерахунку нижньої межі х1 проміжку у комірку F2 ввести формулу „=(D2-A2)/B2”.

Для перерахунку верхньої межі х2 проміжку ввести відповідну аналогічну формулу у комірку G2.

2.3. Ввести формулу для знаходження значень функції стандартного нормального розподілу Ф(z) на межах проміжку [z1; z2].

Для цього:



    • обрати комірку Н2;

    • викликати майстра функцій за допомогою ЛК на піктограмі вставки функції на стандартній панелі інструментів (або командою Функція пункту Вставка головного меню);

    • у списку категорій функцій першого вікна майстра функцій обрати категорію Статистические, а у списку функцій – функцію НОРМСТРАСП;

    • ознайомитись з довідкою по даній функції і форматом її виклику;

    • перейти до другого кроку майстра за допомогою ЛК на кнопці ОК;

    • ознайомитись з вмістом другого вікна майстра функцій;

    • задати аргумент функції, для чого:

- перемістити у зручне положення або згорнути вікно майстра (дати ЛК на кнопці згорнення на правому кінці поля введення);

- дати ЛК на комірці F2 (в якій міститься значення нижньої межі проміжку [z1; z2]; в результаті у полі введення з’являється адреса комірки F2;



    • розгорнути вікно майстра за допомогою ЛК на кнопці розгорнення на правому кінці поля введення і пересвідчитись, що у полі введення міститься адреса комірки F2;

Зауваження. Адресу комірки F2 можна ввести і не згортаючи вікно майстра, а просто давши ЛК на полі введення (в результаті у полі з’являється текстовий курсор) і набравши на клавіатурі адресу F2, але для цього потрібно пам’ятати розміщення даних на аркуші, або мати його „тверду” копію.

    • затвердити виклик функції за допомогою ЛК на кнопці ОК вікна.

В результаті у комірці Н2 з’являється значення функції НОРМСТРАСП (значення функції Лапласа Ф(z2)).

Аналогічно описаному вище для комірки Н2 ввести у комірку І2 формулу виклику функції НОРМСТРАСП для верхньої межі проміжку (значення z2 у комірці G2).


  1   2   3   4   5


База даних захищена авторським правом ©shag.com.ua 2016
звернутися до адміністрації

    Головна сторінка