Аналіз розподілу даних

При аналізі даних часто виникає необхідність визначення різних статистичних характеристик або параметрів розподілу У статистиці набір даних, отриманих шляхом вимірювань, називається розподілом Excel пропонує ряд засобів, призначених для аналізу розподілу даних Це вбудовані статистичні функції, функції для оцінки розкиду даних, а також інструмент Ранг і персентіль (Rank And Percentile)

ПРИМІТКА

Аналізувати розподілу даних можна також за допомогою інструментів Описова статистика (Descriptive Statistics) і Гістограма (Histogram), які входять до складу надбудови Пакет аналізу Детальніше про ці інструменти розповідається далі в розділі «Інструменти пакета аналізу»

Вбудовані статистичні функції

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

ПРИМІТКА

Крім стандартних засобів, Excel пропонує набір інших більш складних функцій: ЛИНЕЙН (LINEST), ЛГРФПРІБЛ (LOGEST), ТЕНДЕНЦІЯ (TREND) і РОСТ (GROWTH), що оперують масивами даних Детальніше про них розповідається в розділі «Лінійна і експонентна регресії» даної глави

Функція СРЗНАЧ

Функція СРЗНАЧ (AVERAGE) обчислює середнє арифметичне: ряд числових значень підсумовується, а потім ділиться на кількість значень у цьому ряду Функція має такі аргументи: число 1 число 2 ..; кількість яких може досягати 30, при цьому, якщо в число аргументів потрапляють посилання на порожні клітинки і осередки, містять логічні та текстові значення, помилки не виникне – вони просто будуть проігноровані Наприклад, щоб обчислити середнє значення в осередках В4: В15 робочого листа, можна записати вираз

= (В4 + В5 + Вб + В7 + В8 + В9 + В10 + В11 + В12 + В13 + В14 + В15) / 12,

але набагато простіше застосувати формулу = СРЗНАЧ (В4: В15)

Функції МЕДИАНА, МОДА, МКС, МІН і РАХУНОК

Аргументами всіх цих функцій може бути діапазон комірок або список чисел, розділених знаком крапки з комою, наприклад: число 1 число 2…. Їх кількість не повинна перевищувати 30 Як аргументи не можна використовувати текстові,

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

► Функція МЕДИАНА (MEDIAN) обчислює медіану вихідних чисел Медіана – це число, що є «серединою» безлічі чисел, тобто половина чисел набору мають значення більші, ніж медіана, а половина чисел – менші, ніж медіана Якщо кількість чисел парне, функція повертає середнє значення між двох чисел, що знаходяться в середині множини

► Функція МОДА (MODE) визначає найбільш часто зустрічається чи повторювана значення в масиві або інтервалі даних Як і функція МЕДИАНА, вона є мірою взаємного розташування значень Якщо безліч даних не містить однакових значень, функція МОДА видає помилку # N / A

► Функція МАКС (МАХ) повертає найбільше значення з діапазону, заданого аргументами

► Функція МІН (MIN) повертає найменше значення з діапазону, заданого аргументами

► Функція РАХУНОК (COUNT) підраховує кількість чисел у діапазоні, заданому аргументами Дати та формули прирівнюються до чисел

ПРИМІТКА

Для визначення кількості непорожніх осередків (незалежно від їх вмісту) використовується функція СЧЕТЗ (COUNTA), що належить до розряду додаткових, загальна інформація про яких представлена ​​далі в розділі «А-версії статистичних функцій»

Функції СУММЕСЛІ і РАХУНОК ЄС Л І

Функція СУММЕСЛІ (SUMIF) аналогічна функції СУММ, але перш ніж провести операцію складання, вона перевіряє кожну клітинку діапазону підсумовування на виконання умови Її аргументи:діапазон критерій діапазон підсумовування, де в якості аргументу діапазон задається діапазон перевіряються осередків, критерій – Умова, яка повинна виконуватися в кожному осередку перевіряється діапазону, адіапазон

^ Підсумовування – Це ті осередки, значення яких будуть складатися Так, якщо для стовпця, в якому записані назви місяців, визначено імя Місяць, а в суміжному стовпці з назвою Продажівведені дані про продажі за кожен місяць, формула

= СУММЕСЛІ (Місяці Червень; Продажу) поверне значення обсягу продажів в червні Другий приклад: за формулою = СУММЕСЛІ (Прода-жи > = 999; Продажі) можна обчислити сукупний обсяг продажів тільки для тих місяців, в яких кожна окрема виручка перевищила $ 999

РАДА

Для складання складних формул зручно використовувати надбудову Майстер підсумовування Докладніше про це розповідається в розділі «Створення формул за допомогою Майстра підсумовування» глави 12

Функція СЧЕТЕСЛІ (C0UNTIF) аналогічним чином визначає кількість осередків, що задовольняють заданому критерію Вона оперує схожими аргументами: діапазон критерій В умовах наведеного вище прикладу можна дізнатися кількість місяців, в яких обсяг продажів виявився нижче $ 600 Для цього достатньо скласти формулу = СЧЕТЕСЛІ (Продажі <600").

ДИВИСЬ ТАКОЖ

Дві останні формули містять умовні вирази Їх створення і застосування докладно розглядається в розділі «Умовні вирази» глави 12 Також в прикладах двом стовпцях присвоєні назви Цей процес описується в розділі «Присвоєння імен осередкам і діапазонам» тієї ж голови

Функції для обчислення порядкових статистик

Пакет аналізу включає в себе декілька функцій, призначених для обчислення порядковихстатистик– Характеристик розподілу, які визначаються на основі упорядкованого ряду вхідних значень: ПРОЦЕНТРАНГ (PERCENTRANK), ПЕРСЕНТІЛЬ (PERCENTILE), Квартиль (QUARTILE), НАЙМЕНШИЙ (SMALL), НАЙБІЛЬШИЙ (LARGE) і РАНГ (RANK)

Функція ПРОЦЕНТРАНГ

Функція ПРОЦЕНТРАНГ (PERCENTRANK) повертає процентну норму значення в наборі даних З її допомогою, наприклад, можна створити таблицю значень у відсотках, привязану до вихідного діапазону так, що при зміні вихідної величини відповідно коректувалося значення (Ранг) у відсотках Ми застосували функцію ПРОЦЕНТРАНГ для оцінювання становища студентів в загальному рейтингу (стовпець Е на рис 171)

Рис 171 Функція ПРОЦЕНТРАНГ повязує процентні значення з вхідними

Функція ПРОЦЕНТРАНГ має такі аргументи:масив х розрядність,де масив– Масив або діапазон даних із числовими значеннями (у нашому прикладі D2: D1001), х – значення, для якого визначається його ранг, а необовязковий аргумент розрядність визначає кількість значущих цифр для вихідного значення Якщо цей аргумент опущений, залишаються три цифри (у форматі 0, Ххх або хх, х%)

А-версії статистичних функцій

У Excel основний набір вбудованих статистичних функцій доповнюється їх А-версіями, які дозволяють працювати не тільки з числами, але також з текстовими і логічними значеннями У їх число входять: СРЗНАЧ (AVERAGEA), СЧЕТЗ (CОUNTA), МАКСА (МАХА), МІНА (MINA), СТАНДОТКЛОНА (STDEVA), СТАНДОТКЛОНПА (STDEVPA), ДІСПА (VARA) і ДІСПР (VARPA) Всі вони мають однакові аргументи (Значення 1 значення 2 ..), Загальна кількість яких не повинна перевищувати 30

Їх стандартні аналоги при розрахунках не враховують комірки, що містять текстові значення Так, якщо в діапазон з 10 осередків потрапила одна рядок символів, стандартна функція СРЗНАЧ проігнорує цей осередок і при обчисленні середнього поділить всю

суму на 9 А функція СРЗНАЧ буде ділити все на 10 Така особливість А-версій статистичних функцій може дуже стати в нагоді, якщо в обчисленнях необхідно враховувати всі комірки діапазону, наприклад, коли застосовуються формули, що повертають при виконанні певних умов текстові «прапорці» типу «немає даних» Детальніше функції СТАНДОТКЛОНА, СТАНДОТКЛОНПА, ДІСПА і ДІСПР розглядаються далі в цьому розділі

Функції ПЕРСЕНТІЛЬ і Квартиль

Функція ПЕРСЕНТІЛЬ (PERCENTILE) використовується для визначення того, який елемент вхідного діапазону відповідає заданим рівнем у відсотках Ця функція має два аргументи:масив кПроцентильдозадається у вигляді десяткового дробу в діапазоні від 0 до 1 Наприклад, щоб визначити, який бал на аркуші, зображеному на рис 171, представляє 87-й процентиль, запишемо формулу

= ПEPCEHTІЛЬ (D2: D1001 0,87)

Функція Квартиль (QUARTILE), що має аргументимасивічастина,працює аналогічно функції ПЕРСЕНТІЛЬ, за винятком того, що вона може повертати тільки мінімальний процентиль або будь-який з квартилей набору даних Аргумент масив –це масив або діапазон комірок з числовими значеннями, для яких визначаються значення квартилей, ачастина –значення, яке потрібно повернути, згідно наведеній нижче табл 171

Таблиця 171 Значення аргументу «частина» функції Квартиль

Аргумент Значення, що повертається

1&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 25-й процентиль

2&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Медіана (50-й процентиль)

3&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 75-й процентиль

4&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Найбільше значення

РАДА

Функція Квартиль дуже практична і працює досить швидко, якщо потрібно повернути 25й або 75-й процентиль набору даних Але у всіх інших випадках, особливо при обробці великих масивів, краще використовувати стандартні вбудовані функції, такі як МІН замість Квартиль (масив 0), МЕДИАНА замість Квартиль (масив 2) і МАКС замість Квартиль (масив 4)

Функції найменше та найбільше

Функції НАЙМЕНШИЙ (SMALL) і найбільша (LARGE) повертають відповідно k-e найменше та k-e Найбільше значення з набору даних Обидві функції мають однакові аргументи: масив k, де k визначає номер позиції від найбільшого або найменшого значення у вхідному масиві даних Наприклад, щоб знайти 15-й найбільший бал на аркуші, зображеному на рис 171, зручно застосувати формулу

= НАЙБІЛЬШИЙ (D2: D1001 15)

Функція РАНГ

Функція РАНГ (RANK) повертає ранг числа в наборі числових даних Ранг числа – Це його порядковий номер щодо інших значень у списку (Якщо список відсортувати, ранг числа стане рівносильний його позиції) Ця функція має такі аргументи:  число посилання порядок,дечисло– Число, для якого визначається ранг, посилання – Посилання на діапазон, що містить числові дані (інші значення в діапазоні ігноруються), апорядок –величина, що визначає спосіб упорядкування Так, щоб зясувати, який ранг має бал 1200 на аркуші, представленому на рис 171, можна застосувати формулу = PAHГ (1200 D2: D1001)

За замовчуванням максимальне значення має ранг 1, другий найбільший – ранг 2 і т д Якщо функція РАНГ не в силах знайти точної відповідності між першим аргументом і одним з вхідних значень, вона повертає помилку # N / A

Функції для оцінки розкиду даних

Дисперсія і стандартне відхилення є статистичними характеристиками розкиду множини значень у генеральній сукупності данихСтандартне відхилення – Це квадратний корінь з дисперсії Як правило, близько 68% значень випадкової величини, що має нормальний розподіл, знаходиться в межах одного стандартного відхилення від середнього значення, і близько 95% – в межах подвоєного стандартного відхилення Велике стандартне відхилення вказує на те, що дані сильно розпорошені щодо середнього значення, а мале – на те, що випадкові значення компактно розташовані навколо свого середнього

Чотири статистичні функції ДИСП (VAR), ДІСПР (VARP), СТАНДОТКЛОН (STDEV) і СТАНДОТКЛОНП (STDEVP) – обчислюють дисперсію і стандартне відхилення для набору чисел, обмеженого діапазоном листа Перш ніж застосовувати ці функції, необхідно визначити, є представлені значеннягенеральною сукупністю данихабо ж вони представляютьвибіркуз цієї сукупності Функції ДИСП і СТАНДОТКЛОН працюють тільки з вибірками з генеральної сукупності, а функції ДІСПР і СТАНДОТКЛОНП – з усією сукупністю даних

Функції ДИСП і СТАНДОТКЛОН

Функції ДИСП і СТАНДОТКЛОН обчислюють, відповідно, дисперсію і стандартне відхилення в припущенні, що їхні аргументи є вибіркою з генеральної сукупності даних Обидві функції мають однакові аргументи {Число 1 число 2…), Кількість яких не повинна перевищувати 30 На аркуші, зображеному на рис 172, представлені результати складання іспитів пятьма студентами, при цьому також передбачається, що оцінки в діапазоні В4: Е8 є частиною генеральної сукупності даних

В осередку J3 записана формула = ДИСП (В4: Е8), і ми бачимо дисперсію для цієї вибірки,

а стандартне відхилення для цієї ж вибірки отримано в комірці J4 за формулою

= СТАНДОТКЛОН (В4: Е8)

Рис 172 Функції ДИСП і СТАНДОТКЛОН дозволяють оцінити дисперсію екзаменаційних оцінок за вибіркою

Припускаючи, що екзаменаційні оцінки студентів укладаються в нормальний розподіл, можна очікувати, що близько 68% студентів отримали бал між 83,65 (середнє значення 89,20 мінус стандартне відхилення 5,55) і 94,75 (89,2 плюс 5,55 відповідно)

Функції ДІСПР і СТАНДОТКЛОНП

Якщо аналізоване безліч числових значень утворює генеральну сукупність, а не окрему вибірку, то для обчислення дисперсії і стандартного відхилення такої сукупності даних краще застосовувати призначені для цього функції ДІСПР і СТАНДОТКЛОНП Вони мають однакові аргументи (Число 1 число 2 ..), Кількість яких не повинна перевищувати 30

Для прикладу покладемо, що значення в осередках В4: Е8 представляють генеральну сукупність (див рис 172) Тоді дисперсію і стандартне відхилення для них можна розрахувати за формулами = ДІСПР (В4: Е8) і = СТАНДОТКЛОНП (В4: Е8) Перша з них поверне значення 29,26, а друга – 5,41

РАДА

Функції ДИСП, ДІСПР, СТАНДОТКЛОН і СТАНДОТКЛОНП не включають в розрахунки текстові значення і порожні осередки Для того щоб врахувати останні, слід застосувати аверсі цих функцій: ДІСПА, ДІСПР, СТАНДОТКЛОНА і СТАНДОТКЛОНПА Огляд аверсі функцій знаходиться в розділі «А-версії статистичних функцій» цієї глави

Джерело: Ефективна робота: 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>

*

*