Вам нужно присвоить каждому числу в Excel свой номер, чтобы можно было их отсортировать по этому номеру? Можно придумать сложные конструкции для текстовых данных, но для числовых данных есть специальная функция РАНГ в Excel. Относится к числу статических функций и бывает довольно полезной. В статье мы так же рассказываем о новых функциях из Excel 2010 РАНГ.CP() и РАНГ.РВ()

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

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

• Число —число для которого нужно определить ранг/место в массиве
• Ссылка — массив значений в котором определяется ранг/место числа
• Порядок — необязательный аргумент.

Если поставить 0 или не заполнять этот аргумент то ранг определяется по убыванию. Т.е. наибольшее число 2000 будет на 1ом месте.

Если поставить любое другое число, то максимальное значение будет под последним местом.

Подробно показано, как использовать функцию на первой картинке.

Примечание. Если списке 2 одинаковых числа — в нашем случае 1 400, то им будет присвоены одинаковые значения ранга — 2, т.е. второе место. При этом следующее место будет уже не 3, а 4 (1 250). Довольно важное свойство, которое не всех устраивало. Поэтому разработчики пошли дальше и сделали дополнительные функции РАНГ.CP() и РАНГ.РВ() в Excel 2010.

РАНГ.CP

Если чисел с одинаковым значением 1400 3 и более, всем им будет присвоен ранг =2 — это для обычной функции РАНГ. Функция РАНГ.СР посчитает место как среднее из 3 мест, т.е. 3 места занимают второе, третье и четвертое место

РАНГ присвоит всем 3 числам второе место.

РАНГ.CH присвоит всем троим четвертое место.

А. Ранжирование качественных признаков

Пример 1.

Испытуемому предлагается задание, в котором семь личностных качеств необходимо упорядочить (проранжировать) в двух столбцах: в левом столбце в соответствии с особенностями его «Я реального», а в правом столбце в соответствии с особенностями его «Я идеального». Результаты ранжирования даны в таблице 2.

Таблица 2.

Я реальное	Качества личности	Я идеальное
	ответственность
	общительность
	настойчивость
	энергичность
	жизнерадостность
	терпеливость
	решительность

Б. Ранжирование количественных признаков

Пример 2.

В результате диагностики невроза у пяти испытуемых по методике К.Хека и Х. Хесса были получены следующие баллы: 24, 25, 37, 13, 12. Этому ряду чисел можно проставить ранги двумя способами:

большему числу в ряду ставится больший ранг, в этом случае получится: 3, 4, 5, 2, 1;

большему числу в ряду ставится меньший ранг: в этом случае получится: 3, 2, 1, 4, 5.

4.2. Проверка правильности ранжирования

А. Формула для подсчета суммы рангов по столбцу (строчке)

Если ранжируется N чисел, то сумма рангов расчитывается по формуле (1.1):

1+2+3+...+N =N(N+ 1)/2 (1.1)

В случае примера 1 число ранжируемых признаков было равно N =7, поэтому сумма рангов, подсчитанная по формуле (1.1), должна равняться 7(7+1)/2=28.

Сложим величины рангов отдельно для левого и правого столбца таблицы:

7 + 1 + 3+ 2 + 5 + 4 + 6 = 28 - для левого столбца и

1 + 5+ 7+ 6 + 4 + 3 + 2 = 28 - для правого столбца.

Суммы рангов совпали.

Б. Формула для расчета суммы рангов в таблице

Ранжирование по столбцам.

Пример 3. Результаты тестирования двух групп испытуемых по 5 человек в каждой по методике дифференциальной диагностики депрессивных состояний В. А. Жмурова представлены в таблице 3.

Таблица 3.

Номер испытуемого

Задача: проранжировать обе группы испытуемых как одну, т. е. объединить выборки и проставить ранги объединенной выборке, сохраняя, однако различие между группами. Сделаем это в таблице 4, причем так, что максимальной величине будем ставить минимальный ранг.

Таблица 4.

Номер испытеумого

Поскольку у нас получены суммы ранга по столбцам, то общую сумму рангов можно получить, сложив эти суммы: 31+24= 55.

Чтобы применить формулу (1.1), нужно подсчитать общее количество испытуемых - это 5+5=10.

Тогда по формуле (1.1) получаем: 10(10+1)/2=55.

Ранжирование прведено правильно.

Если в таблице имеется большое число строк и столбцов, то можно использовать модификацию формулы (1.1)

Сумма рангов в таблице

= (kc+1)kc/2 , (1.2)

где k - число строк, с - число столбцов.

Вычислим сумму рангов по формуле (1.2.) для нашего примера. В таблице 2 имеется 5 строк и 2 столбца, сумма рангов = ((5·2+1)·5·2)/2=55

Ранжирование по строкам

Пример 4.

Таблица 5. Проведем ранжирование по строчкам.

Номер испытуемого
Суммы по столбцам

В этой таблице минимальному по величине числу ставится минимальный ранг. Сумма рангов по каждой строчке должна быть равна 6, поскольку у нас ранжируется три величины: 1+2+3= 6. В нашем случае так оно и есть. Теперь просуммируем ранги по каждому столбцу отдельно и сложим их.

Расчетная формула общей суммы рангов для ранжирования по строчкам для таблицы определяется по формуле:

Сумма рангов = nc(c+1)/2, (1.3.)

где n – количество испытуемых в столбце, с - количество столбцов (групп).

Проверим правильность ранжирования для нашего примера.

Реальная сумма рангов в таблице 8+10+12= 30

По формуле (1.3): 5·3·(3+1)/2=30.

Следовательно, ранжирование проведено правильно.

Случай одинаковых рангов

Ранжирование качественных признаков

А. Ранжирование качественных признаков

Модифицируем пример 1. и перепишем его в табл. 6. Предположим, что при оценке особенностей «Я реального» испытуемый считает, что такие качества, как «настойчи­вость» и «энергичность», должны иметь один и тот же ранг. При проведении ранжирования (столбец 1 табл. 6) этим качествам необходимо проставить мысленные ранги (М.Р.), как числа, обязательно идущие по порядку друг за другом, и от­метить эти ранги круглыми скобками - (). Однако посколь­ку эти качества, по мнению испытуемого, должны иметь одинаковые ранги, то во втором столбце табл. 6, относяще­муся к «Я реальному», следует поместить среднее арифмети­ческое рангов, проставленных в скобках, т.е. (2 + 3)/2 = 2,5. Таким образом, второй столбец табл. 6 и будет окончатель­ным итогом ранжирования особенностей «Я реального», данным испытуемым, а проставленные в этом столбце ран­ги будут носить название - реальные ранги (P.P.).

Аналогично при ранжировании «Я идеального» испыту­емый считает, что такие качества, как «общительность», «энергичность» и «жизнерадостность», должны иметь один и тот же ранг. Тогда при проведении ранжирования (см. столбец 5 табл. 6) этим качествам необходимо проставить мысленные ранги, как числа, обязательно идущие по поряд­ку друг за другом, и отметить эти ранги круглыми скобка­ми - (). Однако поскольку эти качества, по мнению испы­туемого, должны иметь одинаковые ранги - то в четвертом столбце табл. 6, относящемся к «Я идеальному», следует поместить среднее арифметическое рангов, проставленных в скобках, т.е. (4 + 5 + 6)/3 = 5. Таким образом, четвертый столбец таблицы 6 и будет окончательным итогом ранжи­рования особенностей «Я идеального», данным испытуе­мым, а проставленные в этом столбце ранги будут носить название - реальные ранги. Подчеркнем еще раз, что мыс­ленные (условные) ранги, как числа, должны располагаться друг за другом по порядку, несмотря на то что ранжируемые качества в таблице данных не находятся рядом друг с дру­гом.

Таблица 6.

Я реальное		Качества личности	Я идеальное
		Ответственность
		Общительность
		Настойчивость
		Энергичность
		Жизнерадостность
		Терпеливость
		Решительность

Обозначения: М.Р. - мысленные, или условные, ранги; P.P. - ре­альные ранги.

Проверим правильность ранжирования во втором столб­це табл. 6, т.е. реальные ранги, относящиеся к «Я реаль­ному»:

1 + 2,5 + 2,5 + 5 + 4 + 6 = 28.

Проверим правильность ранжирования в четвертом столбце табл. 6, т.е. реальные ранги, относящиеся к «Я идеальному»:

1 + 2 + 3 + 5 + 5 + 5 + 7 = 28.

По формуле (1.1) сумма рангов также равняется 28. Сле­довательно, ранжирование проведено правильно.

Б. Ранжирование количественных характеристик (чисел)

Ранжирование чисел рассмотрим на примере.

Пример. Психолог получил у 11 испытуемых следующие значения показателя невербального интеллекта: 113,102,123,122, 117, 117, 102, 108, 114, 102, 104. Необходимо проранжировать эти показатели, и лучше всего это сделать в таблице 7.

Таблица 7

Номер испытуемых	Показатели интеллекта	Мысленные ранги (М.Р.)	Реальные ранги (P.P.)

В примере встретились две группы из равных чисел (102, 102 и 102; 117 и 117), поскольку числа в группах раз­личны, то и скобки, проставленные этим группам чисел, также различны.

Проверим правильность ранжирования по формуле (1.1). Под­ставив исходные значения в формулу, получим: 11·12/2 = 66. Суммируя реальные ранги, получим:

6 + 2 + 11 + 10 + 8,5 + 8,5 + 2 + 5 + 7 + 2 + 4 = 66.

Поскольку суммы совпали, следовательно, ранжирование про­ведено правильно.

Правила ранжирования чисел таковы.

1. Наименьшему (наибольшему) числовому значению приписывается ранг 1.

2. Наибольшему (наименьшему) числовому значению приписывается ранг, равный количеству ранжируемых ве­личин.

3. Одинаковым по величине числам должны проставлять­ся одинаковые ранги.

4. Если в ранжируемом ряду несколько чисел оказались равными, то им приписывается реальный ранг, равный средней арифметической величине тех рангов, которые эти числа получили бы, если бы стояли по порядку друг за дру­гом.

5. Если в ранжируемом ряду имеется две и больше групп равных между собой чисел, то для каждой такой группы применяется правило 4, и мысленные ранги каждой группы заключаются в разные скобки.

6. Общая сумма реальных рангов должна совпадать с расчетной, определяемой по формуле (1.1).

При необходимости ранжирования достаточно большого числа объектов следует объединять их по какому-либо при­знаку в достаточно однородные классы (группы), а затем уже ранжировать полученные классы (группы).

Наиболее часто к измерениям, полученным в ранговой шкале, применяются коэффициенты корреляции Спирмена и Кэндалла, и, кроме того, используются разнообразные критерии различий.

10 Июл

Добрый день, уважаемые подписчики и читатели блога. Сегодня мы поговорим о сортировке данных в таблицах Excel. Но сортировке не простой, а с определённым условием — менять очередность данных в таблице нельзя.

Действительно, наши проблемы решил бы простой фильтр, но любая сортировка по возрастанию или убыванию изменит положение данных в таблице согласно их величине. Рассмотрим вот такой пример. Имеется таблица:

Если мы включаем фильтр от большего к меньшему, то порядок магазинов изменится, так как необходимо увидеть лучший и худший результаты. Выделяем заголовок таблицы, на вкладке «Главная» нажимаем кнопку «Сортировка и фильтр» далее выбираем строчку «Фильтр». Появится возможность выбирать критерии сортировки. Согласно условию выбираем сортировку «От максимального к минимальному».

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

Главное отличие функций — РАНГ.РВ учитывает равные значения и присваивает им одинаковый ранг.

РАНГ.СР — возвращает среднее между двумя одинаковыми рангами (их порядковыми номерами).

Синтаксис формулы будет следующий:

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

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

Совершим следующие действия:

• добавим в нашу таблицу дополнительный столбец «Место в прибыли»;
• вызываем мастер функций;
• вводим в поле поиска РАНГ.РВ;
• нажимаем «ОК»;
• заполняем аргументы функции.

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

Для ранжирования данных в Excel применяются статистические функции РАНГ, РАНГ.РВ, РАНГ.СР. Все они возвращают номер числа в ранжированном списке числовых значений. Рассмотрим подробнее синтаксис, примеры.

Пример функции РАНГ в Excel

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

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

1. Число . Для, которого необходимо определить номер в ранжировании.
2. Ссылка . На массив чисел или диапазон ячеек с числовыми значениями. Если задать в качестве аргумента просто числа, то функция вернет ошибку. Нечисловым значениям номер не присваивается.
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 и выше. В более ранних версиях для этих целей можно задействовать формулу массива.

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