Excel

Виконання ранжирування в Microsoft Excel

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

функції ранжирування

Для виконання ранжирування в Ексель передбачені спеціальні функції. У старих версіях програми був один оператор, призначений для вирішення цього завдання - РАНГ. З метою сумісності він залишений в окремій категорії формул і в сучасних версіях програми, але в них все-таки бажано працювати з більш новими аналогами, якщо є така можливість. До них відносяться статистичні оператори РАНГ.РВ і РАНГ.СР. Про відмінності і алгоритмі роботи з ними ми поговоримо далі.

Спосіб 1: функція РАНГ.РВ

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

Синтаксис цього оператора записується в такий спосіб:

=РАНГ.РВ(число;ссылка;[порядок])

Аргументи «число» і «посилання» є обов'язковими, а «порядок» - необов'язковим. Як аргумент «число» потрібно ввести посилання на ту осередок, де міститься значення, порядковий номер якого потрібно дізнатися. Аргумент «посилання» містить адресу всього діапазону, який ранжируется. Аргумент «порядок» може мати два значення - «0» і «1». У першому випадку відлік порядку йде у напрямку убування, а в другому - по зростаючій. Якщо цей аргумент не вказано, то він автоматично вважається програмою рівним нулю.

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

  1. Виділяємо на аркуші клітинку, в яку буде виводитися результат обробки даних. Клацаємо по кнопці «Вставити функцію». Вона локалізується зліва від рядка формул.

    2. Перехід в Майстер функцій в Microsoft Excel

  2. Ці дії призводять до того, що запускається вікно Майстра функцій. У ньому представлені всі (за рідкісним виключенням) оператори, які можна використовувати для складання формул в Excel. У категорії «Статистичні» або «Повний алфавітний перелік» знаходимо найменування «РАНГ.РВ», виділяємо його і клацаємо по кнопці «OK».

    3. Перехід до аргументів функції РАНГ.РВ в Microsoft Excel

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

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

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

    Після того, як всі вищезгадані дані внесені, тиснемо на кнопку «OK».

    4. Аргументи функції РАНГ.РВ в Microsoft Excel

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

    Результат обчислення функції РАНГ.РВ в Microsoft Excel

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

    Абсолютне посилання в Microsoft Excel

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

    Маркер заповнення в Microsoft Excel

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

Ранжування за допомогою функції РАНГ.РВ в Microsoft Excel

урок: Майстер функцій в Excel

урок: Абсолютні і відносні посилання в Excel

Спосіб 2: функція РАНГ.СР

Другою функцією, яка виробляє операцію ранжирування в Ексель, є РАНГ.СР. На відміну від функцій РАНГ і РАНГ.РВ, при збігу значень декількох елементів даний оператор видає середній рівень. Тобто, якщо два значення мають рівну величину і слідують після значення під номером 1, то їм обом буде присвоєно номер 2,5.

Синтаксис РАНГ.СР дуже схожий на схему попереднього оператора. Виглядає він так:

=РАНГ.СР(число;ссылка;[порядок])

Формулу можна вводити вручну або через Майстер функцій. На останньому варіанті ми докладніше зупинимося.

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

    2. Перехід в Майстер функцій в Microsoft Excel

  2. Після відкриття вікна Майстра функцій виділяємо в переліку категорії «Статистичні» найменування РАНГ.СР і тиснемо на кнопку «OK».

    3. Перехід до аргументів функції РАНГ.СР в Microsoft Excel

  3. Активується вікно аргументів. Аргументи у даного оператора точно такі ж, як і у функції РАНГ.РВ:
    • Число (адреса осередку містить елемент, рівень якого слід визначити);
    • Посилання (координати діапазону, ранжування всередині якого виконується);
    • Порядок (необов'язковий аргумент).

    Внесення даних в поля відбувається точно таким же способом, як і у попереднього оператора. Після того, коли всі налаштування виконані, тиснемо на кнопку «OK».

    4. Аргументи функції РАНГ.СР в Microsoft Excel

  4. Як бачимо, після виконаних дій результат розрахунку був виведений в клітинку, зазначену в першому пункті даної інструкції. Сам підсумок являє собою місце, яке займає конкретне значення серед інших величин діапазону. На відміну від результату РАНГ.РВ, підсумок оператора РАНГ.СР може мати дробове значення.

    5. Результат обчислення функції РАНГ.СР в Microsoft Excel

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

Ранжування за допомогою функції РАНГ.СР в Microsoft Excel

урок: Інші статистичні функції в Microsoft Excel

урок: Як зробити автозаповнення в Ексель

Як бачимо, в Ексель існує дві функції для визначення ранжирування конкретного значення в діапазоні даних: РАНГ.РВ і РАНГ.СР. Для більш старих версій програми використовується оператор РАНГ, який, по суті, є повним аналогом функції РАНГ.РВ. Головна відмінність формул РАНГ.РВ і РАНГ.СР полягає в тому, що перша з них вказує найвищий рівень при збігу значень, а друга виводить середній показник у вигляді десяткового дробу. Це єдина відмінність між цими операторами, але його потрібно враховувати при виборі того, який саме функцією користувачеві краще скористатися.