Головна

Вправа 18. Закріплення навичок виконання статистичних розрахунків в електронній таблиці Excel: порівняння вибіркових середніх величин за критерієм Стьюдента.

  1.  FV - future value, майбутня величина, нарощена сума.
  2.  I. Завдання для обов'язкового виконання
  3.  I. Завдання для обов'язкового виконання
  4.  I. Завдання для обов'язкового виконання
  5.  I. Завдання для обов'язкового виконання
  6.  I. Завдання для обов'язкового виконання
  7.  I. Завдання для обов'язкового виконання

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

Припустимо, що даний експеримент проводиться за схемою, що передбачає оцінки залежною змінною на початку і в кінці експерименту. На початку експерименту була отримана наступна вибірка даних: 2, 4, 5, 3, 2, 1, 3, 2, 6, 4; після експерименту - інша вибірка даних: 4, 5, 6, 4, 4, 3, 5, 2, 2, 7. Зробимо таке припущення, що обидві вибірки мають розподіл близьке до нормального. Визначте, чи розрізняються статистично достовірно вибірки експериментальних значень в наведеному вище прикладі. (У даному прикладі табличне значення t-критерію одно 2,1.)

Ключ до завдання:

: Розрахункова формула t-критерію приведена на стор. 81 даного посібника. Складіть її з використанням стандартних статистичних і математичних функцій Excel або звичайним способом.

: Проведіть розрахунки і зробіть висновки.

  А В С
   
   
   
   
   
   
   
   
   
   
 вибіркове середнє    
 вибіркова дисперсія    
 медіана    
 Мода    
     

Вправа 19. Закріплення навичок вставки формули з використанням Майстра функцій і поширення формули. Обчислення елементарних мате-матичних статистик (вибо-Рочной середнього, вибіркової дисперсії, медіани, моди) для вибірки експериментальних даних.

В результаті застосування психодіагностичної методики для оцінки деякого психологічного властивості отримані наступні приватні показники ступеня розвиненості даного властивості в окремих піддослідних: 5,4,5,6,7,3,6,2,8,4.

: Для обчислення вибіркового середнього необхідно виділити осередок В11, натиснути кнопку вставка функції, в списку статистичних функцій вибрати функцію СРЗНАЧ і вказати її аргументи як діапазон комірок (В1: В10). У осередок В11 повернеться обчислене значення.

: Аналогічно обчисліть значення інших статистик, використовуючи функції ДІСПР (В1: В10), медіа (В1: В10), МОДа (В1: В10).

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

Вправа 20. Самостійне виконання статистичних розрахунків в електронній таблиці Excel: порівняння вибіркових дисперсій з використанням F - критерію Фішера. Припустимо, що проводиться експеримент, в якому перевіряється гіпотеза про те, що одна з двох пропонованих програм або методик навчання забезпечує однаково успішне засвоєння знань учнями з різними здібностями, а інша програма або методика цією властивістю не володіє. Демонстрацією справедливості такої гіпотези було б доказ того, що індивідуальний розкид оцінок учнів за однією програмою або методикою більше (або менше), ніж індивідуальний розкид оцінок за іншою програмою або методикою. Перший ряд даних: 4, 6, 5,7,3,4, 5, 6. Другий ряд даних: 2,7, 3,6,1, 8, 4, 5.

Порівняйте дисперсії наступних двох рядів цифр з метою визначення статистично достовірних відмінностей між ними. (Табличне значення критерію Фішера одно 3,44 при ймовірності допустимої помилки не більше 0,05%.)

: Виконайте розрахунки самостійно по нижче наведеною формулою критерію Фішера:

,де

n1 - Кількість значення ознаки в 1-й вибірці;

n2 - кількість значень ознаки в 2-й вибірці;

(n1 -1, n2 -2) - Число ступенів свободи;

- дисперсія по 1-й вибірці;

- Дисперсія по 2-й вибірці.

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

Відповідь: Спробуйте виконати ті ж розрахунки з використанням стандартних статистичних функцій Excel.

В електронній таблиці Excel критерій Фішера обчислює функція ФТЕСТ, Її синтаксис:

ФТЕСТ (масив 1; масив 2)

Масив 1 - це перший масив або інтервал даних; Масив 2 - це другий масив або інтервал даних. Аргументи повинні бути числами або іменами, масивами або посиланнями, що містять числа. Якщо аргумент, який є масивом або посиланням, містить тексти, логічні значення або порожні клітинки, то такі значення ігноруються; проте осередки з нульовими значеннями враховуються. Якщо кількість точок даних в аргументі масив 1 або масив 2 менше 2, або якщо дисперсія аргументу массив1 або массів2 дорівнює нулю, то функція ФТЕСТ повертає значення помилки # СПРАВ / 0 !.

Вправа 21. Закріплення навичок виконання статистичних розрахунків в електронній таблиці Excel: обчислення коефіцієнта лінійної кореляції, побудова рівняння лінійної регресії. Припустимо, досліджували зв'язок між інтелектом учнів 9-х класів та успішністю (див. Табл. 9). Є два ряди даних: 23, 16, 18, 11, 25, 23, 12, 15, 16, 23, 13, 15,16, 23, 28 (бали з тесту інтелекту - змінна Х) І 4.2, 4.4, 4.0, 3.3, 4.0, 3.1, 3.0, 3.2, 4.3, 4.1, 3.0, 3.2, 4.0, 4.5, 4.5 (середній бал атестата - змінна Y).

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

Таблиця 9

 експериментальні дані  КОРРЕЛ (вич)  КОРРЕЛ (таб)
 Бали за тестом  0,594  0,513
 Середній бал атестата  4,2  4,4  3,3  3,1  3,2  4,3  4,1  3,2  4,5  4,5  зв'язок статистично значуща

Ключ до завдання:

:
 Внесіть експериментальні дані і сформатіруйте електронну таблицю Excel відповідно до зразка. Значення Бали по тесту (змінна Х) помістіть в діапазон комірок В3: Р3, значення Середній бал атестата (змінна Y) - в діапазон комірок В4: Р4.

Мал. 14. Приклад побудови точкової діаграми і лінійного рівняння регресії

: Виділіть клітинку Q3, клацніть кнопку вставка функції, Виберіть статистичну функцію Кореллі, в поле массив1 вкажіть діапазон комірок В3: Р3, в поле массів2 - Діапазон комірок В4: Р4 і, таким чином, в осередку Q3 з'явиться значення лінійного коефіцієнта кореляції. Табличне значення коефіцієнта кореляції для n-2 = 13 ступенів свободи і рівня значущості 0,05 виявляється менше обчисленого, т. Е. Можна стверджувати, що гіпотеза про статистичної значущості кореляційної зв'язку між інтелектом учнів 9-х класів та успішністю достовірна з імовірністю помилки 5 %.

: Для побудови точкової діаграми і лінійного рівняння регресії виділіть діапазон комірок В3: Р4 і клацніть кнопку Майстер діаграм (В Word точно така ж кнопка називалася Вставка діаграми, та й технологія створення діаграми мало чим відрізняється). Далі дотримуйтеся вказівок майстри: на 1-му кроці виберіть тип діаграми точкова, найперший її вид і перегляньте результат; на 2-му кроці на вкладці Діапазон переконайтеся, що він обраний правильно - дані для графіка будуть взяті звідти, звідки треба, а на вкладці ряд переконайтеся, що змінним X і Y відповідають правильно вибрані комірки (якщо є неточності, внесіть відповідні зміни); на 3-м кроці додайте заголовки, редагувати їхні інші елементи діаграми; на 4-му кроці виберіть, де буде розміщена діаграма, - на цьому ж аркуші або на окремому.

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

: Для апроксимації експериментальних даних виконайте команду [Діаграма-Додати лінію тренда ...],на вкладці Тип Виберіть лінійна, На вкладці параметри активізуйте прапорці Показувати рівняння на діаграмі и Помістити на діаграму величину достовірності апроксимації, т. е. коефіцієнт детермінованості. Дайте рівняння відповідну назву.

: Після подвійного клацання по осі Х відредагуйте її вид: змініть мінімальне значення з 0 на 10 і значення, в якому вісь Х перетинається з віссю Y. Таким же чином скоректуйте вид осі Y, і пряма лінія займе місце в центрі графіка. Саме рівняння регресії після виділення також можете перенести на вільне місце, а лінії сітки - прибрати.

Відносні, абсолютні і змішані посилання.При складанні формули в основному нам доводилося використовувати так звані відносні посилання на клітинки. Відносне посилання використовується у формулі для вказівки адреси осередку, що обчислюється у відносній сістемекоордінат з початком в поточному осередку. Відносні посилання мають наступний вигляд: А1, В1 ит. п. властивість відносних посилань проявляється при поширенні і копіюванні формул в інші осередки.

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

Для того щоб відносне посилання перетворити в абсолютну, досить після введення посилання (клацання по відповідному осередку) натиснути клавішу F4, - і знак долара з'являться автоматично. Таким чином, немає необхідності вводити знак $ з клавіатури.

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

  A B C D E
  Поправочний коефіцієнт:  
   1 шкала  2 шкала  3 шкала  Загальний бал
 1 випробуваний        
 2 випробуваний        
 3 випробуваний        
           

: Введіть формулу для обчислення загального бала для 1-го випробуваного.

: Розповсюдьте формулу вниз.

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

 Вправа 15. Закріплення навичок оформлення таблиці, введення формули в осередок: розрахунок кореляції між груповими профілями. |  Вправа 25. Закріплення навичок роботи з Книгою Excel: вставка і переміщення листів.


 Вправа 2. Закріплення навичок редагування діаграми. Тип, вид діаграми. |  Вправа 5. Закріплення навичок створення і редагування діаграми (кругова і кільцева діаграма). |  Причини інтересу до шкільних предметів |  Виконання обчислень і часткової автоматизації психодіагностики засобами електронної таблиці MS Excel |  Вправа 3. Закріплення навичок у введення даних в комірку таблиці. |  Вправа 6. Маркер заповнення. Заповнити форму. Заповнення сусідніх осередків однаковими даними. |  Вправа 7. Закріплення навичок заповнення осередків. Використання списків для автозаповнення. |  Вправа 10. Самостійний введення формули в осередок. |  Вправа 13. Закріплення навичок зі створення, оформлення електронної таблиці і введення формули. |  Зразок електронної таблиці |

© 2016-2022  um.co.ua - учбові матеріали та реферати