Розрахунки з використанням MS Excel

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

Для того щоб знайти і , виконують серію окремих вимірів. Розглянемо конкретний приклад. Хай при вимірюванні часу, протягом якого через водомір проходить певний об’єм води, отримані такі значення (у секундах): ; ; ; ; ; . Значення можуть і повторюватися, як значення і у нашому випадку. Хай потрібна надійна ймовірність . Рівень значущості .

Скористаймося табличним процесором MS Excel. Правою клавішею миші персонального комп’ютера клацніть по робочому столу і в контекстному меню виберіть Создать | Лист Microsoft Excel. Відкрийте документ MS Excel подвійним клацанням лівої клавіші миші по його піктограмі. У вікні MS Excel введіть експериментальні значення, як це показано на рис. 2.1.

Виконайте команду Сервис | Анализ данных…. Якщо в меню Сервис такої команди нема, то слід попередньо виконати команду Сервис | Надстройки…, у вікні Надстройки встановіть прапорець Пакет анализа і клацніть кнопку OK. Отже, виконуємо команду Сервис | Анализ данных… | Описательная статистика | OK. У вікні Описательная статистика встановіть параметри процедури, як це показано на рис. 2.1. Для цього поставте курсор у поле вводу Входной интервал і виділіть діапазон комірок з результатами окремих вимірювань (А1:А6), перемикач Параметры ввода поставте в положення Выходной интервал: і виділіть комірку Е1, поставте курсор у поле вводу Выходной интервал: установіть прапорці Итоговая статистика і Уровень надежности:, у полі вводу Уровень надежности: введіть значення надійної ймовірності в процентах - 90, клацніть кнопку OK. Результати аналізу з’являються в діапазоні комірок Е1:F16 (верхню частину діапазону видно на рис. 2.2, повністю діапазон видно на рис. 2.3).

Рис. 2.1. Установлення процедури Описательная статистика.

 

Рис. 2.2. Знаходження відхилення результату окремого виміру від середнього.

Середнє значення вимірюваної величини маємо в комірці F3. Знайдемо відхилення результатів окремих вимірів від середнього . Для цього ставимо курсор у комірку В1 і виконуємо команду Вставка | Функция… | Математические | ABS | OK. У вікні Аргументы функции курсор міститься в полі вводу Число. Клацаємо комірку А1, клавішу «мінус», комірку F3. У рядку вводу ставимо курсор перед цифрою 3 в адресі комірки Е3 і з клавіатури вводимо символ «$» (див. рис. 2.2). Клацаємо кнопку OK. Ставимо курсор у правий нижній кут комірки В1 так, щоб курсор набув вигляду тонкого чорного хрестика, натискуємо ліву клавішу миші і протягуємо курсор для заповнення діапазону комірок (В1:В6).

На рис. 2.3 показаний увесь діапазон комірок E1:F16, заповнених за допомогою функції Описательная статистика. У комірці F8 маємо дисперсію вибірки , розраховану за формулою (5), у комірці F7 - стандартне відхилення (6), у комірці F4 - стандартну похибку середнього (8), у комірці F16 - випадкову похибку (9).

 

Рис. 2.3. Знаходження критерію промаху.

Для знаходження значень критерію промаху (10) установіть курсор у комірку С1, введіть з клавіатури символ «=», клацніть лівою клавішею миші комірку В1, введіть із клавіатури символ «/», клацніть комірку F7. У рядку вводу поставте курсор перед цифрою 7 в адресі комірки F7 і з клавіатури введіть символ «$». Введіть із клавіатури символ «/». Виконайте команду Вставка | Функция… | Математические | КОРЕНЬ | OK. У вікні Аргументы функции курсор міститься в полі вводу Число. Введіть із клавіатури відкриваючу дужку «(». Клацніть комірку F15, клавіші «мінус», «1», «)», «/», комірку F15. У рядку вводу поставте курсор після літери F у кожній з адрес комірки F15 і з клавіатури введіть символ «$» (див. рис. 2.3). Клацаємо кнопку OK. Ставимо курсор у правий нижній кут комірки С1 так, щоб курсор набув вигляду тонкого чорного хрестика, натискуємо ліву клавішу миші і протягуємо курсор для заповнення діапазону комірок (С1:С6).

Найбільше значення критерію промаху маємо в комірці С4: . З табл. 1.2 при рівні значущості і кількості вимірів максимальне допустиме значення критерію промаху . Оскільки , то результат вимірювань є промахом і підлягає вилученню.

Виявлення промахів можна автоматизувати (див. рис. 2.4). Введіть у комірку F17 значення критерію промаху 1,89. Поставте курсор у комірку D1 і виконайте команду Вставка | Функция… | Логические | ЕСЛИ | OK. У вікні Аргументы функции курсор перебуває в полі вводу Лог_выражение. Клацніть комірку С1, введіть із клавіатури символ «>», клацніть комірку F17. У рядку вводу поставте курсор після літери F в адресі комірки F17 і введіть із клавіатури символ «$». Поставте курсор у поле вводу Значение_если_истина і введіть із клавіатури слово «промах». Поставте курсор у поле вводу Значение_если_ложь і натисніть клавішу пропуску (space). Клацніть кнопку OK. Поставте курсор у правий нижній кут комірки D1 так, щоб курсор набув вигляду тонкого чорного хрестика, натисніть ліву клавішу миші і протягніть курсор для заповнення діапазону комірок (D1:D6). У комірці D4 з’являється слово «промах».

Вилучаємо з вибіркової сукупності значення . Для цього виділяємо діапазон комірок А4:В4 і натискуємо клавішу Delete.

Тепер слід виконати статистичний аналіз для нової вибіркової сукупності. Викликаємо функцію Описательная статистика з тими самими параметрами (див. рис. 2.1). Значення в діапазоні комірок В1:F16 оновлюються (див. рис. 2.5). У комірку F17 вводимо нове значення . Тепер у діапазоні D1:D6 нема вказівок на те, що результат якогось виміру є промахом. У комірці F3 отримуємо середнє значення вибірки , у комірці F16 - випадкову похибку .

Вимірювальний прилад також дає внесок у похибку вимірювання. Згідно з (2) паспортна похибка приладу

.

(16)

Вона розраховується для надійної ймовірності . Похибка приладу, перерахована на потрібну надійну ймовірність ,

.

(17)

Вимірювання виконувалися цифровим секундоміром із паспортною похибкою (ціна найменшої поділки шкали). Введемо значення в комірку А18. У комірку F18 введемо формулу =А18/2,97*СТЬЮДРАСПОБР(1-А10;900000000). Після чого в цій комірці (див. рис. 2.6) отримуємо результат .

 

Рис. 2.4. Виявлення промахів із використанням функції ЕСЛИ.

 

Для знаходження межі довірчого інтервалу виміряної величини із надійною ймовірністю (12) у комірку F19 вводимо формулу =F16+F18. У комірці F19 (див. рис. 2.6) отримуємо .

Результат вимірювань наводимо у вигляді із зазначенням одиниці вимірювання та надійної ймовірності: при .

Для отримання відносної граничної похибки середнього значення вимірюваної величини (15) у комірку F20 вводимо формулу =F19/F3. У комірці F20 (див. рис. 2.6) маємо відносну похибку .

 

Рис. 2.5. Промах виявлено.

 

Рис. 2.6. Отримання кінцевого результату статистичного аналізу.