Функція ранг в прикладах. Статистичні функції Excel

10 Липень

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

Справді, наші проблеми вирішив би простий фільтр, але будь-яке сортування за зростанням або зменшенням змінить положення даних у таблиці відповідно до їх величини. Розглянемо такий приклад. Є таблиця:

Якщо ми вмикаємо фільтр від більшого до меншого, то порядок магазинів зміниться, тому що необхідно побачити кращий і найгірший результат. Виділяємо заголовок таблиці, на вкладці «Головна» натискаємо кнопку «Сортування та фільтр», далі вибираємо рядок «Фільтр». З'явиться можливість вибирати критерії сортування. Згідно умови вибираємо сортування «Від максимального до мінімального».

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

Головна відмінність функцій - РАНГ.РВ враховує рівні значення і надає їм однаковий ранг.

РАНГ.СР - повертає середнє між двома однаковими рангами (їх порядковими номерами).

Синтаксис формули буде наступним:

число— числове значення якого обчислюється ранг (обов'язковий аргумент);

порядок — порядок виставлення рангу (якщо коштує 0 або поле залишиться порожнім значить присвоєння рангу буде за спаданням, якщо коштує 1, то проводитиметься ранжування за зростанням, необов'язковий аргумент).

Зробимо такі дії:

додамо до нашої таблиці додатковий стовпець «Місце в прибутку»;
викликаємо майстер функцій;
вводимо у полі пошуку РАНГ.РВ;
натискаємо "ОК";
заповнюємо аргументи функції.

У цій формулі спеціально закріплений діапазон від B2 до B10, щоб можна було використовувати маркер автозаповнення. Протягнемо за нього до кінця таблиці. Результат не змусить на себе довго чекати.

Функція РАНГ.РВ повертає ранг числа списку чисел, тобто його величину щодо інших значень у списку.

Опис функції РАНГ.РВ

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

Синтаксис

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

Аргументи

Обов'язковий.Число, якого визначається ранг.

Необов'язковий.Число, що визначає спосіб упорядкування.

Зауваження

Якщо значення аргументу "порядок" дорівнює 0 (нулю) або опущено, ранг числа визначається в Excel так, якби посилання було списком, відсортованим за спаданням.
Якщо значення аргументу «порядок» - будь-яке число, крім нуля, то ранг числа визначається Excel так, якби посилання була списком, відсортованим за зростанням.
Функція РАНГ.РВ надає повторюваним числам однакові значення рангу. Однак наявність чисел, що повторюються, впливає на ранги наступних чисел.
Наприклад, якщо у списку цілих чисел, відсортованих за зростанням, двічі зустрічається число 10, що має ранг 5, число 11 матиме ранг 7 (жоден з чисел не матиме рангу 6).
У деяких випадках може бути потрібне використання визначення рангу з урахуванням зв'язків. Припустимо, що у попередньому прикладі вам потрібно отримати ранг 5,5 для числа 10. Для цього слід додати поправний коефіцієнт (див. нижче) до значення, що повертається функцією РАНГ.РВ. Цей коефіцієнт може застосовуватися у випадках, коли ранг обчислюється і за спаданням (аргумент «порядок» має нульове значення або опущений), і за зростанням (значення аргументу «порядок» не дорівнює нулю).
Поправочний коефіцієнт для пов'язаних рангів

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

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

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

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

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

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

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

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

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

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

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

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

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

Приклад функції РАНГ у Excel

Функція використовується під час ранжирування в переліку чисел. Тобто дозволяє дізнатися величину числа щодо інших числових значень. Якщо відсортувати список за зростанням, функція поверне позицію числа. Наприклад, у масиві чисел (30; 2; 26) число 2 матиме ранг 1; 26 -2; 30 –3 (як найбільше значення у списку).

Синтаксис функції:

Число. Для якого необхідно визначити номер у ранжируванні.
Посилання. На масив чисел чи діапазон осередків із числовими значеннями. Якщо задати як аргумент просто числа, то функція поверне помилку. Нечисловим значенням номер не надається.
Порядок. Спосіб упорядкування чисел у списку. Варіанти: аргумент дорівнює «0» чи опущений – значення 1 присвоюється максимальному числу у списку (начебто список відсортований гаразд зменшення); аргумент дорівнює будь-якому нерівному нулю числу – номер ранжирування 1 присвоюється мінімальному числу у списку (начебто список відсортований у порядку зростання).

Визначимо ранжування чисел у списку без повторів:

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

У третьому стовпці наведено формулу з рангом зростання.

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

Жовтим кольором виділені числа, що повторюються. Для них визначається той самий номер. Наприклад, числу 7 у другому стовпці надано номер 9 (і в другому рядку, і в дев'ятому); у третьому стовпці – 3. Але жодне з чисел у другому стовпці не матиме 10, а третьому – 4.

Щоб ранги не повторювалися (іноді це заважає користувачеві вирішити поставлене завдання), використовується така формула:

Для роботи функції можна встановити межі. Наприклад, необхідно ранжувати лише значення від 0 до 30. Щоб розв'язати задачу, застосуємо функцію ЯКЩО (=ЯКЩО(A2

Сірим кольором виділено значення, які відповідають заданій умові. Для чисел більше 30 виводиться порожній рядок.

Приклад функції РАНГ.РВ Excel

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

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

Приклад функції РАНГ.СР в Excel

Повертає номери числового значення у списку (порядковий номер щодо інших значень). Тобто виконує те саме завдання. Тільки за виявлення ідентичних значень повертає середній показник.

Ось результат роботи функції:

Формула в стовпці «за спаданням»: = РАНГ.СР (A2; $ A $ 2: $ A $ 9; 0). Так, функція значення 87 привласнила середній номер 1,5.

Припустимо, у списку чисел три значення, що повторюються (виділені помаранчевим кольором).

Функція надала кожному з них ранг 5, що є середнім для 4, 5 і 6.

Порівняємо роботу двох функцій:

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

Завантажити приклади функції ранжування РАНГ у Excel.

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

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

Як користуватися. Функція РАНГ в Excel

Функцією РАНГ користуватись досить просто. Набір аргументів також нескладний, а саме:

Опис аргументів:

Числочисло для якого потрібно визначити ранг/місце в масиві
Посилання- масив значень у якому визначається ранг/місце числа
Порядок – необов'язковий аргумент.

Якщо поставити 0 або не заповнювати цей аргумент, то ранг визначається за спаданням. Тобто. Найбільше 2000 буде на 1ом місці.

Якщо встановити будь-яке інше число, то максимальне значення буде під останнім місцем.

Докладно показано, як використовувати функцію на першому зображенні.

Примітка. Якщо списку 2 однакових числа - у разі 1 400, їм буде присвоєно однакові значення рангу - 2, тобто. друге місце. При цьому наступне місце буде вже не 3, а 4 (1250). Досить важлива властивість, яка не всіх влаштовувала. Тому розробники пішли далі та зробили додаткові функціїРАНГ.CP() та РАНГ.РВ() в Excel 2010.

РАНГ.CP

Якщо чисел з однаковим значенням 1400 3 і більше, всім їм буде надано ранг =2 - це для звичайної функції РАНГ. Функція РАНГ.СР вважатиме місце як середнє із 3 місць, тобто. 3 місця займають друге, третє та четверте місце

РАНГ надасть усім 3 числам друге місце.

РАНГ.CH надасть усім трьом четверте місце.

РАНГ.РВ

Все просто – це та ж функція РАНГ тільки для нових версій Excel – 2010 та старших.

Приклад файлу

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

(Visited 7 463 times, 16 visits today)

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

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

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

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

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

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

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

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

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

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

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

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

Виробляємо виділення осередку на аркуші для виведення результату. Так само, як і в попередній раз, переходимо в Майстер функцій через кнопку «Вставити функцію».
Після відкриття вікна Майстра функцій виділяємо в переліку категорії "Статистичні" найменування РАНГ.СР і тиснемо на кнопку "OK".
Активується вікно аргументів. Аргументи у даного оператора такі самі, як і у функції РАНГ.РВ:
- Число (адреса осередку містить елемент, рівень якого слід визначити);
- Посилання (координати діапазону, ранжування всередині якого виконується);
- Порядок (необов'язковий аргумент).
Внесення даних у поля відбувається таким самим способом, як і в попереднього оператора. Після того, як всі налаштування виконані, натискаємо на кнопку «OK».
Як бачимо, після виконаних дій результат розрахунку був виведений у комірку, зазначену у першому пункті даної інструкції. Сам результат є місцем, яке займає конкретне значення серед інших величин діапазону. На відміну від результату РАНГ.РВ, результат оператора РАНГ.СР може мати дробове значення.
Як і у випадку з попередньою формулою, за допомогою зміни посилань з відносних на абсолютні та маркери виділення, шляхом автозаповнення можна проранжувати весь діапазон даних. Алгоритм дій такий самий.

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

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

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

Ми раді, що змогли допомогти Вам у вирішенні проблеми.

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

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

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

Excel 2010 містить нову функцію, названу РАНГ.CPяка виправляє цю проблему. На рис. 98.1 показано таблицю, де використовується два методи для ранжування значень у стовпці. Перший метод (стовпець С) застосовує функцію РАНГ, а в стовпці D задається нова функція РАНГ.СР. Діапазон В2:В16 названий Продажі.

Зверніть увагу на те, що дві людини стоять на третьому місці. Функція РАНГприсвоює кожному їх значення 3, а функція РАНГ.CP -3,5. Крім того, троє людей стоять на десятому місці. Функція РАНГдає кожному їх ранг 10, а функція РАНГ.CP, З іншого боку, дає їм трьом ранг 11, що є середнім для 10,11 і 12.

Формули, що використовують функцію РАНГ.CP, повернуть помилку в попередніх версіях Excel. Якщо ви збираєтеся ділитися книгою з тими, хто використовує версію Excel нижче 2010, можете задіяти формулу масиву замість функції РАНГ.СР.

Подивіться на рис. 98.1 і введіть наступну формулу масиву в рядок 4. Потім скопіюйте її в комірки, що стоять нижче. Результат буде таким самим, як і при використанні функції РАНГ.СР:
= Сум (1 * (B4)

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

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