Використання надбудови «Аналіз даних»

Установка надбудов Excel

У програмі Excel можна користуватися надбудовами – модулями, що надають додаткові можливості по виконанню фінансових, а також наукових розрахунків. Окрім вбудованих в Excel надбудов, можна завантажувати надбудови, створені сторонніми виробниками. Що стосується вбудованих надбудов, інтегрованих в Excel, то з них досить встановити наступне:

  •     Пакет аналізу, який містить фінансові, а також наукові функції і допоміжні засоби. Наприклад, в цій надбудові, окрім фінансових, математичних функцій, а також функцій дати і часу, міститься також набір функцій, призначених для інженерних розрахунків. Надбудова Пакет аналізу також містить інструменти статистичного аналізу, про які детально розказано в цьому розділі.
  • Analysis ToolPack-VBA включає фінансові і математичні функції,
    а також функції дати і часу. Наприклад, для розрахунку відсотків, що нараховуються, завантажується функція ACCRINT.


  • Майстер підсумовування дозволяє швидко створити формулу, яка розраховується тільки за вказаних умов.

    У програмі Excel надбудови зберігаються у вигляді файлів з розширенням .xla; їх список відображається в діалоговому вікні Надбудови і автоматично встановлюється в одну з наступних тек жорсткого диска:

    C:\program Files\microsoft Office\office 10\addins

    C:\Documents and Settings\ім’я користувача\Application Data\Microsoft\Addins

    Після завантаження надбудов програма Excel додає рядок меню і інші елементи інтерфейсу.

    Щоб звільнити пам’ять, необхідно встановлювати в Excel тільки ті надбудови, які необхідні, а непотрібні, навпаки, вивантажувати.

    Якщо складно знайти ефективну надбудову для виконання того або іншого завдання, то треба створити свою власну. Як надбудову можна зберегти будь-яку книгу Excel. У подібному збереженому файлі, за великим рахунком, використовуються тільки макроси. Що стосується інших елементів книги Excel, то в надбудові вони не використовуються. Тому надбудови створюються виключно на основі робочих книг з макросами.

    Якщо потрібно розповсюдити макроси серед інших користувачів, то ідеальним способом буде створення надбудов. В результаті VBA-код макросів буде прихований, і користувачі не зможуть його проглядати і тим більше редагувати.

    Щоб зберегти книгу Excel як надбудову, потрібно вибрати команду Файл — Зберегти як. Буде відображено діалогове вікно Збереження документа. У полі Тип файлу вибрати значення Надбудова Microsoft Excel (*.xla). Вказуються також ім’я надбудови і місце, в якому слід зберегти її файл. Коли натиснути кнопку Зберегти, програма Excel створить надбудову, яка містить макроси і призначені для користувача діалогові вікна, збережені в початковій книзі Excel.

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

    Використання майстра підсумовування

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

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

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

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

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

    У програмі Excel майстер підсумовування запускається як надбудова, яку можна встановити в діалоговому вікні Надбудови. Встановивши, її можна знайти в меню Формули, команда Часткова сума.

    У другому вікні майстра підсумовування визначається критерій підсумовування, використовуючи умовного оператора. Цей оператор визначає, яким чином умова інтерпретуватиметься програмою Excel. Наприклад, в умовній інструкції можна використовувати наступного оператора порівняння:

    Значення > 586203

    В результаті підсумовуватимуться тільки ті значення, які більше 586203 і відповідають іншим критеріям.

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

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

    Використання майстра підсумовування (продовження)

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

    Захід = Схід

    Арізона = Каліфорнія

    Програма Excel дозволяє вставляти значення 586203 і Техас в різні осередки робочого листа. Ці значення ви можете використовувати на робочому листі як значення умови, яка перевіряється. У третьому вікні майстра підсумовування указується, чи слід вставляти значення умови разом з формулою.

    На жаль, не можна вказати, які саме значення умови вставити, а які – ні. Вставляються всі значення умови або тільки обчислена сума.

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

    При використанні майстра підсумовування створюється умовна формула, в якій застосовуються функції СУММ і ЯКЩО, і ця формула розміщується у вказані осередки. Формулу можна відредагувати у будь-який час за допомогою діалогового вікна Аргументи функції.
    Як тільки вікно майстра підсумовування закриється, то вже редагувати отриману формулу з його допомогою не можливо. При повторному запуску майстра передбачається, що потрібно буде створити нову умовну суму.

    Вираз умовного підсумовування створюється в результаті комбінування функції СУММ з вкладеною інструкцією ЕСЛИ. Якщо планується змінювати функцію в майбутньому, то в ній буде не просто розібратися. Вкладена інструкція ЯКЩО є головним аргументом функції СУММ, тому підсумовані значення повертаються тільки після виконання функції ЯКЩО. Наприклад, програма Excel створює наступну формулу для списку, в якій підсумовуватимуться значення понад 596203, а в стовпці Штат – введено значення Техас :

    =СУММ(ЕСЛИ($DS2:$D$20=”Техас”;ЕСЛИ($Г$2:$F$2O>596203; $F$2: $F$20;0);0))

    В даному прикладі спочатку перевіряється, чи рівне значення запису в стовпці D рядку Техас. Якщо після перевірки першої умови повертається Істина, то друга інструкція перевіряє, чи більше $50000 значень, введеного в стовпці F. Якщо обидві умови виконуються, то значення із стовпця F додається до загальної суми, і процес повторюється для наступного рядка діапазону. Якщо ж певний рядок не відповідає одній з умов, то до загальної суми додається нуль.

    Дисперсійний аналіз списків значень

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

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

    :≠ и :=

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

    Щоб виконати таке обчислення, необхідно вказати діапазон осередків із значеннями, які слід проаналізувати, а також рівень статистичної достовірності, що виражається числом від 0 до 1 і указуваний в полі Альфа. Значення в полі Альфа – це вірогідність того, що гіпотеза є істинною. Рівень 0,05, заданий за умовчанням, еквівалентний 5-процентному рівню достовірності.

    У даному прикладі приведений однофакторний дисперсійний аналіз, проте дії з виконання двохфакторного дисперсійного аналізу майже не відрізняються. У Excel дисперсійний аналіз доступний тільки завдяки надбудовам.

    Програма Excel виконала дисперсійний аналіз. Щоб визначити, чи істинна гіпотеза, потрібно подивитися, в таблиці, створеною Excel, на F-статистику. Велике значення в стовпці F означає, що нуль-гіпотезу слід відкинути і утвердити в думці про істинність припущень. Якщо значення F близьке до 1, то середні значення дійсно рівні, і нуль-гіпотезу знову ж таки слід відкинути. Крім того, необхідно проаналізувати Р-значение і F-критическое. Якщо значення F більше значення F-критического, то нуль-гіпотезу доведеться відкинути. Інакше відкидати нуль-гіпотезу не можна. Що стосується Р-значения: якщо воно велике того значення F, цілком імовірно, набуте випадково. Якщо Р-значение маленьке, то значення F точно набуте невипадково. Нижче приведена таблиця, в якій схемний представлено, як слід інтерпретувати результати дисперсійного аналізу.

F -статистика

F-критичне

P-значення

Дія

Мале

F -статистика

Велике

Не відкидайте нуль гіпотезу

Велике

F -статистика

Мале

Нуль гіпотеза винна     бути знехтувана


Знаходження кореляції між двома наборами даних

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

Для обчислення кореляції необхідно вказати програмі Excel діапазон осередків для обох наборів даних, а також спосіб їх групування. Щоб результат аналізу було легко зрозуміти, можливо, доведеться ввести в першому рядку робочого листа Excel підписи даних.

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

При використанні інструменту Кореляція програма Excel обчислює коефіцієнт кореляції на основі поточного набору даних. Якщо після кореляційного аналізу одне із значень в наборі даних міняється, то набутого Значення автоматично оновлюватися не буде. Щоб відновити коефіцієнт кореляції, необхідно знову запустити інструмент Кореляція.

Якщо ви постійно змінюєте початкові дані і хочете, щоб програма Excel автоматично оновлювала коефіцієнт кореляції, то створіть формулу, в якій використовується функція КОРРЕЛ. З її допомогою можна перевіряти значення в двох різних списках і визначати кореляцію. У функції КОРРЕЛ є два аргументи:

= КОРРЕЛ (массив1;массив2)

Кожен аргумент представляє діапазон осередків, що містить значення, які потрібно порівняти. Функція КОРРЕЛ повертає значення в діапазоні від -1 до 1, яке визначає кореляцію, що існує між двома наборами значень.

Якщо необхідно порівняти декілька наборів значень, то при порівнянні кожних два набори даних необхідно створювати окрему формулу з функцією КОРРЕЛ.

Визначення коваріації двох наборів даних

Іструмент Коваріація визначає відхилення значень від середнього в кожному наборі даних. Якщо ви представили дані у вигляді точкової діаграми і бачите на ній пряму лінію, то можете статистично довести існування зв’язку, обчисливши кореляцію і коваріацію. В той час, як кореляція визначає, чи залежать один від одного два набори даних, коваріація указує на ступінь взаємозв’язку два наборовши даних або їх отлічность один від одного. Розрахувавши коваріацію і кореляцію, ви за допомогою отриманих результатів зможете визначити, чи існує насправді зв’язок між двома наборами даних.

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

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

Якщо необхідно набути значення коваріації всього лише для двох наборовши даних, використовуйте функції Excel КОВАР для створення відповідної формули осередку робочого листа. Застосувавши цю функцію, ви визначите коваріацію два, наборовши даних, яка рівна середньому твору відхилень між всіма можливими парами значень, збережених у вигляді набору даних. Для функції кожен нрбор даних представлений окремим масивом.

Функция-коварт-как показане в наступному синтаксисі, використовує два аргументи:

=К0ВАР (массив1;массив2)

Кожен аргумент представлений діапазоном осередків, що містить значення, які не обходжений порівняти. Обидва аргументи включають однакову кількість значень, інакше функція повертає повідомлення про помилку. Програма Excel ігнорує будь-які осередки, які містять текст, логічні Значення, а також порожні осередки.

Для порівняння декілька наборовши даних створіть окрему функцію КОВАР для кожної пари наборовши. Наприклад, якщо потрібно порівняти набори А, В і З, то створіть три різні формули, щоб виконати аналіз для пар А і В, А і З, В і С.

Отримання статистичних зведень

За допомогою інструменту Описова статистика програма Excel обчислює шістнадцять різних статистичних параметрів і відображає їх у вигляді списку. Для аналітика це неймовірно корисний інструмент, оскільки дозволяє швидко отримувати статистичні відомості для великих баз даних або робочих листів Excel. З допомогою цього інструменту програма Excel створює таблицю із стандартними статистичними відомостями для кожної групи значень у вказаному вами списку. У цій таблиці указуються середнє значення, стандартна помилка, медіана, мода, стандартне відхилення, дисперсія вибірки, ексцес, асиметричність, інтервал, мінімум, максимум, сума, кількість, найбільше значення, найменше значення і довірчий рівень. Наприклад, якщо застосувати цей інструмент до списку, що містить зведення про об’єми продажів в різних штатах, то Excel створить таблицю із статистичними відомостями для кожного штату.

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

Останні чотири параметри діалогового вікна Описова статистика призначені для вказівки тих статистичних параметрів, які повинні обчислюватися програмою Excel. Щоб отримати повні статистичні відомості, встановите прапорець Підсумкова статистика. Якщо потрібно обчислити довірчий рівень для середнього, необхідно поставити прапорець Рівень надійності. Наприклад, значення 90% відповідає довірчому інтервалу 10%. Прапорці К-ий найбільший і К-ий найменший дозволяють знайти вказані значення в групі, наприклад, друге найменше або третє найбільше число. Якщо ви указуєте 1, то набуваєте тих же самих значень, які приводяться як мінімум і максимум.

За допомогою інструменту Описова статистика обчислюється декілька статистичних величин.

Статистична
величина

Опис

Середнє

Середнє арифметичне або центр розподілу для групи даних    

Стандартна помилка

Квадратний корінь розміру вибірки (n), що ділиться на стандартне відхилення від квадратного кореня розміру вибірки (n)

Медіана

Середнє значення в наборі даних

Мода

Найпоширеніше значення в групі даних

Стандартне
відхилення

Розкид значень набору даних

Дисперсія вибірки

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

Ексцес

Напрям кривизни розподілу даних

Асиметричність

Ступінь симетрії розподілу щодо центральної осі

Інтервал    

Різниця між найбільшим і найменшим значеннями

Мінімум/максимум    

Найменше і найбільше значення в групі

Сума

Сума, що отримується при складанні всіх значень в групі

Рахунок    

Кількість значень в групі

Найбільше(М)/

найменше(М)

Найбільше і найменше значення в групі, де N є вказаним цілим числом

Довірливий
рівень

Визначає, наскільки значення відхиляється від середнього


Порівняння дисперсії в двох групах даних

Для порівняння дисперсії двох груп даних використовуйте такий інструмент Ехсеl як Двохвибірковий F-тест для дисперсії. Дісперсия- це показник того, наскільки значення “розсіваються” щодо середнього значення в наборі даних. Наприклад, існують два заводи, які проводять один і той же продукт. Один завод знаходиться в Індіані, а інший – в Техасі. Обидва мають ефективність виробництва 95 %, але вам необхідно знати точно, яким з цих заводів стабільніше проводить продукцію протягом року.

Під час F-теста програма Excel порівнює дисперсію двох груп даних. Excel обчислює значення F для двох наборовши даних, яке є відношенням середньої стандартно-квадратичної помилки (MS) між групами до неї ж усередині груп. Якщо значення F менше значення F-критичного, то не можна відкинути нуль-гіпотезу про те, що дисперсії двох груп однакові. Якщо F-статистика близька до Л, то це означає, що у двох груп рівні дисперсії.

Щоб виконати цей тест, в Excel потрібно буде вказати діапазони обох груп даних, а також рівень очікуваної статистичної надійності. Значення поля Альфа – це вірогідність того, що істинно. Довірчий рівень указується в діапазоні від 0 до 1. За умовчанням використовується значення 0,05, що еквівалентно 95% рівню надійності. Щоб представити результат в наочній формі, доведеться вказати в першому рядку робочого листа підписи даних.

Коли застосовують інструмент Двохвибірковий F-тест для дисперсії, програма Excel обчислить декілька значень, описаних в наступній таблиці.

Статистична величина

Опис

Середнє

Середнє значення або центр розподілу групи даних

Дисперсія

Ступінь розповсюдження або дисперсії даних.

Середньоквадратична відстань між кожним значенням і середнім

Спостереження

Визначає кількість значень в кожному списку

df

Визначає кількість мір свободи або кількість значень, які можуть вільно змінюватися після обчислення статистичних величин для набору даних

Статистична величина

Опис

F

Відношення дисперсії окремих груп до всього діапазону значень. Значення F – це відношення середньоквадратичної відстані між наборами даних до середньоквадратичної відстані усередині наборів даних

P(F< = f) одностороннє

Значення в діапазоні від 0 до 1, яке указує вірогідність спостереження хоч би однієї екстремальної крапки в тесті. Чим ближче це значення до одиниці, тим вказана вірогідність вища

F критичне одностороннє

Критичне значення розподілу F. Воно залежить як від довірчого рівня, так і від мір свободи. Це стандартне значення, з яким порівнюється значення F


Обчислення «скользящего среднего»

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

У полі Інтервал вкажіть число значень або інтервалів, які програма Excel повинна використовувати для обчислення ковзаючого середнього. Якщо в це поле нічого не введено, то за умовчанням використовується значення, рівне 3. Це означає, що ковзаюче середнє обчислюватиметься шляхом розрахунку середнього останніх трьох значень.

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

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

Інструмент Ковзаюче середнє є частиною надбудови Пакет аналізу, встановлюваної за допомогою діалогового вікна Надбудови.

Працюючи, з інструментом Ковзаюче середнє, можна визначити ковзаюче середнє для кожного із значень, застосувавши функцію СРЗНАЧ. Перші декілька осередків стовпця містять значення #н/д. Кількість цих осередків на одиницю менше того числа, яке вказане в полі Інтервал. Наприклад, якщо в полі Інтервал представлено значення 3, то #н/д знаходитиметься    по-перше двох осередках стовпця Ковзаюче середнє. Це говорить про те, що для обчислення середнього в кожному з цих осередків неможливо знайти три значення, що фігурують в розрахунках.

Якщо, працюючи з інструментом Ковзаюче середнє, потрібно створити діаграму, то для кожного набору даних, осей і самої діаграми програма Excel використовує м

Отже, щоб змінити текст назв діаграми і осей, виберіть команду Діаграма => Параметри діаграми. У діалоговому вікні Параметри діаграми клацніть на вкладці Заголовки і вкажіть необхідні назви.

Щоб змінити імена рядів даних, що відображаються в легендах, виберіть команду Діаграма => Початкові дані, а потім клацніть на вкладці Ряд. Клацніть на кожному ряду і вкажіть нове ім’я. Ви можете або ввести ім’я, або задати посилання на осередок з відповідною назвою.

Визначення рангу і персентіля

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

За один раз можна збудувати по рангу тільки одну рядок або один стовпець. Програма Excel дозволяє визначати як початковий діапазон декілька рядків або стовпців, але аналізуватися буде тільки перший рядок або перший стовпець цього діапазону. У першому рядку діапазону можуть міститися підписи даних. Якщо в цьому діапазоні знаходиться будь-який інший текст, то відображається повідомлення про помилку.

Результати, отримані інструментом Ранг і персентіль, можна заносити у вказаний діапазон осередків поточного листа, на новий лист або в нову книгу. Якщо вибраний перемикач Новий лист, то слід вказати ім’я цього листа або дати завдання Excel привласнити листу ім’я, задане за умовчанням.

Інструмент Ранг і персентіль є частиною надбудови Пакет аналізу, встановлюваної в діалоговому вікні Надбудови.

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

Параметр

Опис

Крапка

Розташування значення даних у вказаному початковому діапазоні. Наприклад; якщо в початкових даних значення спочатку малося в своєму розпорядженні третім, то в цьому стовпці для нього указується значення 3

Стовпець

Містить початкові значення, відсортовані по рангу

Ранг

Число, вказуюче ранг кожного значення; 1 означає найвищий ранг в списку

Відсоток

Процентний ранг початкового значення. Указує частку у відсотках тих значень, які менші по рангу, чим вказане значення

Щоб створити формулу для обчислення рангу того або іншого значення із списку даних, використовуйте функцію РАНГ. Ця функція повертає число, яке показує, наскільки воно порівнянне з іншими числами із списку даних. Для функції ранг використовується наступний синтаксис: =РАНГ (Число; Посилання; [Порядок]). Нижче приведена таблиця з описом її трьох аргументів.

Аргумент

Опис

Число

Числове значення, для якого необхідно знайти ранг

Посилання

Діапазон осередків, який використовується, щоб знайти ранг вказаного значення

Порядок

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

ЗАВАНТАЖИТИ

Для скачування файлів необхідно або Зареєструватись

Використання надбудови «Аналіз даних» (доробити) (3.3 MiB, Завантажень: 0)

завантаження...
WordPress: 22.99MB | MySQL:26 | 0,347sec