Лінійна і експонентна регресії – ЧАСТИНА 4

Щоб застосувати функцію ЧАСТОТА, задайте межі інтервалів, як робили при роботі з інструментом Гістограма, потім виділіть діапазон, в який будуть записуватися вихідні дані, – В нашому прикладі це діапазон G2: G21 Він повинен бути частиною одного стовпця, це особливість функції ЧАСТОТА Введіть формулу, і в ній в якості першого аргументу массів_данних вкажіть діапазон вихідних даних, а друга (Масив _граііц)– Діапазон із заданими інтервалами кордонів Натисніть клавіші Ctrl + Shift + Enter, щоб перетворити цю формулу в формулу масиву Детальніше про створення масивів і роботі з ними розповідається в розділі «Масиви» глави 12

Ранг і персентіль

Припустимо, потрібно провести розбивку по рангах всіх екзаменаційних балів, представлених на рис 179 Це завдання можна вирішити самостійно: впорядкувати всі оцінки в порядку убування (кращі – Нагорі, найгірші – внизу),

потім створити поряд з впорядкованими даними зростаючу послідовність, в якій найкращою оцінкою буде призначений ранг 1, а найгіршою – ранг 1000

Рис 1713 Функція ЧАСТОТА може організувати звязок між результатами аналізу та вихідними даними

Але простіше скористатися інструментом Ранг і персентіль (Rank And Percentile) пакета аналізу Він не тільки виконає поставлене завдання, а й визначить процентний ранг для кожного вихідного значення Щоб активувати інструмент, виберіть у меню Сервіс команду Аналіз даних, виділіть в списку назва Ранг і персентіль і натисніть кнопку ОК На екрані зявиться однойменне діалогове вікно, зображене на рис 1714

Зверніть увагу на рекомендації, які не опиняться для вас зайвими:

► Слід завжди приймати рішення щодо стану прапорця Мітки у першому рядку (Labels In First Row) Якщо він встановлений, Excel як заголовка другого шпальти вихідний таблиці візьме назву відповідного стовпця вхідного діапазону В іншому випадку стовпець за замовчуванням отримає назву Столбец1 (Column 1)

ПРИМІТКА

Якщо цей прапорець встановити, але в вихідний діапазон не включити осередок з заголовком, діапазон результатів також не отримає заголовка – результати будуть слідувати з першої клітинки Так, у нашому прикладі з рис 1714, якщо в якості джерела вказати діапазон D2: D1001, заголовком діапазону-одержувача в стовпці G стане значення 936

► У показаному на рис 1714 прикладі ми аналізували тільки сумарні оцінки, але нам ніщо не заважає обробити всі дані одночасно (стовпці В, С і D) Тоді в якості вхідного діапазону необхідно вказати B1: D1001, і підсумкова таблиця буде складатися не з чотирьох стовпців, а з 12 – по чотири стовпці для кожного набору даних

Рис 1714 Для аналізу результатів іспитів можна використовувати інструмент Ранг і персентіль

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

► Тепер про те, як інтерпретувати підсумкові дані з нижньої частини рис 1714 З першого рядка таблиці результатів (F2: I2) видно, що 285-й елемент вхідного діапазону, бал якого дорівнює 1206, має ранг 1, і він вище всіх інших

Кореляція таблиць

Вхідний і вихідний діапазони, як показано на рис 1714 внизу, мають один спільний стовпець Всього і однакову кількість рядків Але, оскільки ці дані відсортовані по-різному, рядка вихідного і вихідного діапазонів не збігаються один з одним Найпростіший спосіб вирішити проблему – впорядкувати вихідні дані по стовпцю Точка, в якому відображаються номери позицій відповідних значень діапазонаісточніка Картина, яку ми після цього побачимо, показана у верхній частині наведеного нижче малюнка Якщо потім видалити зайві стовпці (у даному випадку Е, F і G), можна отримати зручний звіт, подібний представленому на тому ж малюнку внизу

Генерація випадкових чисел

Вбудована функція Excel СЛЧИС (RAND) генерує випадкові числа з сукупності, що має рівномірний розподіл в інтервалі від 0 до 1 Іншими словами, ймовірність появи будь-якого числа з цього інтервалу однакова Оскільки обсяг вибірок, як правило, не буває достатньо великим, то не можна вважати, що дані в них розподілені рівномірно Проте багаторазове використання функції СЛЧИС підтверджує, що вона не віддає переваги яким-небудь певним значенням усередині інтервалу розподілу Додаткову інформацію про цю функцію можна отримати в розділі «Функції СЛЧИС і СЛУЧМЕЖДУ» глави 14

ПРИМІТКА

Функція СЛЧИС відноситься до підлаштовуються функціях Excel, тобто її результат перераховується кожного разу при введенні нових значень або відкритті файлу Якщо потрібно «зафіксувати» отриманий ряд випадкових чисел, виділіть на листі всі комірки, що містять функції СЛЧИС, скопіюйте їх у буфер обміну, потім виберіть у меню Правка команду Спеціальна вставка (Paste Special) і в діалоговому вікні встановіть перемикач в положення Значення (Values) У цьому випадку подстраивающиеся формули в осередках будуть замінені повертаними ними значеннями Однак замість вбудованої функції СЛЧИС можна скористатися інструментом Генерація випадкових чисел, який завжди повертає числа, а не формули

Пакет аналізу дозволяє генерувати випадкові числа з розподілами, відмінними від рівномірного Такі безлічі випадкових чисел знаходять застосування в різних методах моделювання Всього в пакеті аналізу таких різних типів розподілу є шість: Рівномірний (Uniform), Нормальне (Normal), Бернуллі (Bernoulli), Біноміальний (Binomial), Пуассона (Poisson) і Дискретне (Discrete) (обумовлений користувачем) Крім того, для отримання невипадкових чисел в заданому інтервалі передбачений ще один тип розподілу – Модельне (Patterned)

Щоб скористатися інструментом Генерація випадкових чисел (Random Number Generation), виберіть у меню Сервіс команду Аналіз даних, виділіть в списку назва Генерація випадкових чисел і натисніть кнопку ОК На екрані зявиться однойменне діалогове вікно, зображене на мал 1715

Рис 1715 Значення в розділі Параметри діалогового вікна Генерація випадкових чисел змінюються залежно від обраного типу розподілу

При роботі з вікном Генерація випадкових чисел дотримуйтеся наступних правил:

► У полях Число змінних (Number Of Variables) і Число випадкових чисел (Number Of Random Numbers) вказується кількість шпальт і кількість випадкових чисел в кожному стовпці відповідно Наприклад, щоб створити по 100 випадкових чисел в 10 стовпцях, потрібно ввести значення 10 в поле Число змінних і 100 в поле Число випадкових чисел

► У полі Випадкове розсіювання (Random Seed) можна ввести «затравочное» значення для ініціалізації генератора випадкових чисел Тоді генеруються повторно послідовності для певного типу розподілу будуть завжди однакові З урахуванням цього задіяти параметр Випадкове розсіювання слід тільки в тих випадках, коли необхідно кілька разів відтворити одну і ту ж ланцюжок випадкових чисел

Вміст діалогового вікна Генерація випадкових чисел визначається обраним типом розподілу Як показано на рис 1715, коли встановлений тип Рівномірний (Uniform), є можливість задати початкову і кінцеву точки розподілу за допомогою полів Між (Between) і І (And)

Рівномірний розподіл

При обраному варіанті Рівномірний генерується безліч випадкових чисел, рівномірно розподілених в інтервалі, визначеному його початкової та кінцевої кордонами У цьому сенсі інструмент працює точно так само, як функція СЛЧІСМЕЖДУ Але в деяких випадках переважніше зупинитися саме на ньому, а не на вбудованої функції Excel, наприклад коли інтервал повинен бути відмінним від [0, 1] або якщо потрібно отримати кілька однакових послідовностей випадкових чисел, що генеруються за допомогою одного і того ж значення ініціалізації

Нормальний розподіл

Нормальний розподіл має такі особливості:

► середнє значення випадає частіше, ніж будь-яке інше

► значення, розташовані ближче до середнього, генеруються з більшою ймовірністю,

ніж значення, наближені до кордонів інтервалу

► значення вище середнього випадають з тією ж частотою, що і значення нижче середнього

Видно, що тут середнє значення є головним параметром Але для генерації нормально розподілених випадкових чисел необхідно задати і другий параметр: стандартне відхиленняСтандартне відхилення –це усереднене відстань по модулю між випадковим числом і середнім значенням (Близько 68% всіх нормально розподілених випадкових чисел знаходяться в межах одного стандартного відхилення від середнього)

Розподіл Бернуллі

Розподіл Бернуллі характеризується тим, що всі випробування мають однакову ймовірність успіху, причому результат однієї спроби не впливає на результат наступних (В даному випадку слово «успіх не несе ніякого смислового навантаження, точно так само можна моделювати зворотне подія – «неуспіх») Всі значення в розподілі Бернуллі рівні або 0, або 1

Шанс того, що в кожному осередку діапазону зявиться значення 1, знаходиться «в руках» єдиного параметра розподілу р,обираного з інтервалу від Про до 1 Так, щоб отримати послідовність з 100 випадкових чисел, найбільш ймовірна сума яких дорівнюватиме 27, визначте вихідний інтервал в 100 осередків і вкажіть для параметра р значення 0,27

Біноміальний розподіл

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

(Точно так само в цьому випадку можна моделювати замість числа успішних випробувань кількість невдач)

Так, припустимо, ви проводите по 10 ділових переговорів за тиждень і в 20% випадків укладаєте вигідні контракти Вам би хотілося знати, наскільки плідним у цьому випадку може виявитися наступний рік Введіть у поле Число випадкових чисел (Number Of Random Numbers) значення 50 (робочих тижнів у році), 0,2 – у полі Значення р (Р Value) і число 10 – у полі Число випробувань (Number Of Trials) В результаті вийде, що в майбутньому році вас не порадують всього чотири тижні, коли кількість угод впаде до нуля Бачите: до ворожки ходити не треба

Розподіл Пуассона

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

Розподіл Пуассона має тільки один параметр, Лямбда (Lambda), що представляє собою очікувану кількість подій Наприклад, в сервісному центрі в середньому за день приймають 10 викликів Требуется дізнатися, як часто протягом року можна чекати 18 і більше викликів на день Для цього введіть значення 260 (52 тижні по 5 робочих днів) в поле Число випадкових чисел і 10 в поле Лямбда (очікуване середня кількість викликів) Результати моделювання говорять про те, що на рік доведеться всього близько трьох напружених днів, коли можна очікувати по 18 і більше клієнтів

Дискретний розподіл

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

Хороший приклад дискретного розподілу – дані продажів снігоприбиральних лопат по сезонах Для цього в перший стовпець вводяться номери місяців, а в другій – ймовірність випадання снігу

Модельне розподіл

У варіанті Модельне (Patterned) генерується безліч чисел, частина з яких випадкові, а частина ні При виборі цього типу розподілу діалогове вікно Генерація випадкових чисел приймає кілька інший вид, представлений на рис 1716

Рис 1716 Модельне розподіл дозволяє створювати арифметичні послідовності з циклічними внутрішніми повтореннями

Робота інструмента Модельне розподіл нагадує команду Прогресія (Series) Він створює одну або кілька арифметичних послідовностей с (необовязковими) внутрішніми циклічними повтореннями Наприклад, щоб отримати послідовність чисел 1, 1, 4, 4, 7, 7, 10, 10, 1, 1, 4, 4, 7, 7, 10, необхідно заповнити вікно так, як це показано на рис 1716 Тобто задати дві послідовності чисел з 1 по 10, з кроком 3 і повторенням кожного числа в послідовності два рази

Якщо для обраного кроку останнє число ланцюжка виходить за межі верхньої межі інтервалу, в якості нього підставляється сам кордон Відповідно, якщо в інтервалі з 1 по 10 задати крок 4, Excel створить наступний ряд: 1, 5, 9 і 10

Вибірка

Інструмент Вибірка (Sampling) витягує підмножина чисел з великої групи або генеральної сукупності чисел Задавши вхідний діапазон, можна вибрати з нього задану кількість значень або випадковим чином, або тільки кожнеп-е значення Потім вибрані числа копіюються в діапазон-одержувач Щоб застосувати цей інструмент, виберіть у меню Сервіс команду Аналіз даних, виділіть в списку назва Вибірка і клацніть на кнопці ОК Зявиться однойменне діалогове вікно, зображене на мал 1717

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

Рис 1717 Інструмент Вибірка дозволяє витягти підмножина чисел з вихідного набору випадковим або заданим чином

РАДА

Щоб зробити вибірку з діапазону, що містить текстові значення, необхідно спочатку створити стовпець з порядковими номерами тестових записів – зростаючою послідовністю цілих чисел, починаючи з 1 Потім потрібно за допомогою інструменту Вибірка витягти числа з цієї послідовності і зіставити їх з текстовими значеннями, підставивши номера як аргументи функції ІНДЕКС Детальніше про цю функцію розповідається в розділі «Функція ІНДЕКС» глави 14

Ковзне середнє

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

Нехай у нас є крива попиту на продукцію підприємства за 18 місяців роботи, подібна показаної на рис 1718 Щоб згладити її, можна використовувати метод змінного середнього з періодом усереднення в шість місяців

Перша точка на кривій – це виведене середнє за перші шість місяців продажів (з січня по червень 2003 року) другий – середнє з другого по сьомий місяць (з лютого по липень 2003 року) і т д Щоб не виконувати усереднення вручну, скористаємося інструментом Ковзаюче середнє Для цього виберемо в списку діалогового вікна Аналіз даних інструмент Ковзаюче середнє і натиснемо кнопку ОК На екрані зявиться однойменне діалогове вікно, представлене на рис 1719

Рис 1718 Крива фактичних обсягів продажів за півтора року роботи підприємства

Рис 1719 Параметри згладжування кривих встановлюються в діалоговому вікні Ковзаюче середнє

Для застосування інструменту Ковзаюче середнє слід задати три параметри: діапазон, що містить аналізовані дані, діапазон для обчислюваних усереднених значень і власне сам інтервал усереднення Наприклад, щоб визначити ковзне середнє за три місяці, потрібно присвоїти інтервалу значення 3

На графіку рис 1720 усереднена крива з інтервалом усереднення 6 місяців накладена на вихідну криву з рис 1718 Обчислені згладжені значення записані в стовпці С, саме вони і використовувалися для побудови більш гладкою ламаної на графіку Зверніть увагу, що перші пять осередків вихідного діапазону містять помилкові значення # Н / Д Взагалі кажучи, якщо задано інтервал усередненняп,то в першу п-1 осередках вихідного діапазону завжди будуть виходити помилки Але вони не викликають проблем – Excel ігнорує їх, залишаючи порожній початкову частину кривої

Рис 1720 Після застосування інструменту Ковзаюче середнє загальна тенденція розвитку виявляється більш наочно

РАДА

Зверніть увагу, що на рис 1720 кожен осередок, в якій знаходяться згладжені методом змінного середнього значення, у верхній лівій частині маркована прапорцем Ці прапорці вказують на помилку Якщо клацнути на такій комірці, на екрані зявиться смарт-тег з попередженням, що формула в цьому осередку посилається на діапазон, до якого прилягають інші значення, тобто формула не охоплює суміжні комірки У нашому випадку так і має бути Щоб видалити прапорці, виділіть всі марковані ними осередки, клацніть на значку смарт-тега і в його меню виберіть команду Пропустити помилку (Ignore Error)

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*