Foreversoft.ru

IT Справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функция rank excel

РАНГ (функция РАНГ)

В этой статье описаны синтаксис формулы и использование функции РАНГ в Microsoft Excel.

Описание

Возвращает ранг числа в списке чисел. Ранг числа — это его величина относительно других значений в списке. (Если отсортировать список, то ранг числа будет его позицией.)

Важно: Эта функция была заменена одной или несколькими новыми функциями, которые обеспечивают более высокую точность и имеют имена, лучше отражающие их назначение. Хотя эта функция все еще используется для обеспечения обратной совместимости, она может стать недоступной в последующих версиях Excel, поэтому мы рекомендуем использовать новые функции.

Дополнительные сведения о новых функциях см. в разделах Функция РАНГ.СР и Функция РАНГ.РВ.

Синтаксис

Аргументы функции РАНГ описаны ниже.

Число Обязательный. Число, для которого определяется ранг.

Ссылка Обязательный. Массив или ссылка на список чисел. Нечисловые значения в ссылке игнорируются.

Порядок Необязательный. Число, определяющее способ упорядочения.

Если значение аргумента «порядок» равно 0 или опущено, ранг числа определяется в Microsoft Excel так, как если бы ссылка была списком, отсортированным в порядке убывания.

Если значение аргумента «порядок» — любое число, кроме нуля, то ранг числа определяется в Microsoft Excel так, как если бы ссылка была списком, отсортированным в порядке возрастания.

Замечания

Функция РАНГ присваивает повторяющимся числам одинаковые значения ранга. Однако наличие повторяющихся чисел влияет на ранги последующих чисел. Например, если в списке целых чисел, отсортированных по возрастанию, дважды встречается число 10, имеющее ранг 5, число 11 будет иметь ранг 7 (ни одно из чисел не будет иметь ранга 6).

Для некоторых целей может потребоваться определение ранга, в котором учитывается наличие связей. В предыдущем примере один из них мог бы получить пересмотренный ранг 5,5 для числа 10. Это можно сделать, добавив следующий коэффициент корректировки к значению, возвращаемому РАНГом. Этот коэффициент подходит для случая, когда Ранжирование вычисляется в порядке убывания (порядковый номер = 0 или опущен) или в возрастающем порядке (порядковый номер = ненулевое значение).

Поправочный коэффициент для связанных рангов = [СЧЕТ(ссылка) + 1 – РАНГ(число, ссылка, 0) – РАНГ(число, ссылка, 1)]/2.

В следующем примере РАНГ(A2,A1:A5,1) равен 3. Поправочный коэффициент равен (5 + 1 – 2 – 3)/2 = 0,5, а ранг, пересмотренный с учетом связей в учетной записи, равен 3 + 0,5 = 3,5. Если то или иное число появляется в ссылке только один раз, поправочный коэффициент будет равен 0, поскольку РАНГ для связи не будет изменяться.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Функция РАНГ в Excel

Функция РАНГ в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

В аргументе функции «Порядок» был выставлен 0, поэтому ранг 1 назначен самому большому значению, 6 — самому маленькому.

Дальнейшую сортировку можно усложнять с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ, о которых мы говорили в предыдущих статьях.

Если появились вопросы — пишите комментарии! Всем удачи!

Функция РАНГ() в EXCEL

Число — число, для которого определяется ранг.

Ссылка — ссылка на список чисел (диапазон ячеек с числами). Напрямую массив задать нельзя, формула =РАНГ(10;<10:50:30:40:50>) работать не будет. Но, если ввести формулу =РАНГ(B7;$A$7:$A$11) , то она будет работать (хотя ячейка B7 — вне списка с числами). Если в B7 содержится число вне списка с числами, то формула вернет ошибку #Н/Д.

Читать еще:  Типы данных в таблице excel

Нечисловые значения в ссылке игнорируются. Числам, сохраненным в текстовом формате, ранг также не присваивается, функция воспринимает их как текст.

Порядок — число, определяющее способ упорядочения.

  • Если порядок равен 0 (нулю) или опущен, то MS EXCEL присваивает ранг=1 максимальному числу, меньшим значениям присваиваются б о льшие ранги.
  • Если порядок — любое ненулевое число, то то MS EXCEL присваивает ранг=1 минимальному числу, б о льшим значениям присваиваются б о льшие ранги.

Примечание : Начиная с MS EXCEL 2010 для вычисления ранга также используются функции РАНГ.СР() и РАНГ.РВ() . Последняя функция аналогична РАНГ() .

Определяем ранг в списке без повторов

Если список чисел находится в диапазоне A7:A11 , то формула =РАНГ(A7;$A$7:$A$11) определит ранг числа из ячейки А7 (см. файл примера ).

Т.к. аргумент порядок опущен, то MS EXCEL присвоил ранг=1 максимальному числу (50), а максимальный ранг (5 = количеству значений в списке) — минимальному (10).

Альтернативный вариант: =СЧЁТЕСЛИ($A$7:$A$11;»>»&A7)+1

В столбце С приведена формула =РАНГ(A7;$A$7:$A$11;1) с рангом по возрастанию, ранг=1 присвоен минимальному числу. Альтернативный вариант: =СЧЁТЕСЛИ($A$7:$A$11;»

Если исходный список отсортировать , то ранг числа будет его позицией в списке.

Ранг по условию

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

В файле примера ранг по условию (условием является принадлежность значения к групп) вычислен с помощью формулы:

В столбце А содержатся названия группы, в столбце В — значения.

Связь функций НАИБОЛЬШИЙ() / НАИМЕНЬШИЙ() и РАНГ()

Функции НАИБОЛЬШИЙ() и РАНГ() являются взаимодополняющими в том смысле, что записав формулу =НАИБОЛЬШИЙ($A$7:$A$11;РАНГ(A7;$A$7:$A$11)) мы получим тот же исходный массив A7:A11 .

Определяем ранг в списке с повторами

Если список содержит повторы , то повторяющимся значениям (выделено цветом) будет присвоен одинаковый ранг (максимальный, если использована функция РАНГ() или РАНГ.РВ() ) или среднее значение, если РАНГ.СР() ). Наличие повторяющихся чисел влияет на ранги последующих чисел. Например, если в списке целых чисел, отсортированных по возрастанию, дважды встречается число 10, имеющее ранг 5, число 11 будет иметь ранг 7 (ни одно из чисел не будет иметь ранга 6).

Иногда это не удобно и требуется, чтобы ранги не повторялись (например, при определении призовых мест, когда нельзя занимать нескольким людям одно место).

В этом нам поможет формула =РАНГ(A37;A$37:A$44)+СЧЁТЕСЛИ(A$37:A37;A37)-1

Предполагается, что исходный список с числами находится в диапазоне А37:А44 .

Примечание . В MS EXCEL 2010 добавилась функция РАНГ.РВ(число;ссылка;[порядок]) Если несколько значений имеют одинаковый ранг, возвращается наивысший ранг этого набора значений (присваивает повторяющимся числам одинаковые значения ранга). В файле примера дается пояснение работы этой функции. Также добавилась функция РАНГ.СР(число;ссылка;[порядок]) Если несколько значений имеют одинаковый ранг, возвращается среднее.

Массив рангов

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

Как видно из картинки выше, значения из диапазона В60:В67 и в ячейке D60 совпадают. Такой массив можно получить с помощью формулы =РАНГ(A60:A67;A60:A67) или с помощью формулы =СЧЁТЕСЛИ(A60:A67;»>»&A60:A67)+1

Ранги по возрастанию можно получить с помощью формулы =РАНГ(A60:A67;A60:A67;1) или =СЧЁТЕСЛИ(A60:A67;» .

Excel RANK Function Examples

Use the RANK function to compare numbers to other numbers in the same list. Watch the videos, and get the free RANK workbook

How to Use the RANK Function

Use the RANK function to compare numbers to other numbers in the same list. See the steps in this video, and the written instructions are below the video.

Your browser can’t show this frame. Here is a link to the page

How to Use the RANK Function

If you give the RANK function a number, and a list of numbers, it will tell you the rank of that number in the list, either in ascending or descending order.

For example, in the screen shot below, there is a list of 10 student test scores, in cells B2:B11.

To find the rank of the the first student’s score in cell B2, enter this formula in cell C2:

=RANK(B2,$B$2:$B$11)

Then, copy the formula from cell C2 down to cell C11, and the scores will be ranked in descending order.

RANK Function Arguments

There are 3 arguments for the RANK function:

  • number: in the above example, the number to rank is in cell B2
  • ref: We want to compare the number to the list of numbers in cells $B$2:$B$11. Use an absolute reference ($B$2:$B11), instead of a relative reference (B2:B11)so the referenced range will stay the same when you copy the formula down to the cells below
  • order: (optional) This argument tells Excel whether to rank the list in ascending or descending order.
    • Use zero, or leave this argument empty, to find the rank in the list in descending order. In the example above, the order argument was left blank, to find the rank in descending order.
      =RANK(B2,$B$2:$B$11)
    • For ascending order, type a 1, or any other number except zero.
      If you were comparing golf scores, you could type a 1, to rank in ascending order.
      =RANK(B2,$B$2:$B$11 ,1 )
Читать еще:  Как ускорить работу excel

RANK Function Order

In the RANK function, the 3rd argument (order), is optional. The order argument tells Excel whether to rank the list in ascending or descending order.

Descending Order

If you use a zero as the setting for order, or if you don’t use the 3rd argument, the rank is set in descending order.

  • The largest number gets a rank of 1
  • The 5th largest number gets a rank of 5.

Ascending Order

If you use a 1 as the setting for order, or if you enter any number except zero as the 3rd argument, the rank is set in ascending order.

  • The smallest number gets a rank of 1
  • The 5th smallest number gets a rank of 5.

Flexible Formula

Instead of typing the order argument number into a RANK formula, use a cell reference, to create a flexible formula.

For example, type a 1 in cell E1, and link to cell E1 for the order argument.

NOTE: Be sure to use an absolute reference ($E$1), if the formula will be copied down to other rows. If you use a relative reference (E1), the reference will change in each row.

=RANK(B2,$B$2:$B$6,$E$1)

By linking to a cell, you can quickly see different results, without changing the formula. Type a zero in cell E1, or delete the number, and the rank will change to Descending order.

Use a Check Box

For the order option, there are only 2 choices — Ascending or Descending. To make it easier for people to change the order, use a check box to turning Ascending order ON or OFF.

  • If it is turned ON, the RANK order will be Asscending
  • If it is turned OFF, the RANK order will be Descending

In the RANK function sample file, there is a check box example on the RankOrderCheck worksheet. For instructions on how to add a check box, and use it in a formula, see my blog post, Use Check Box Result in Excel Formula.

More Examples of Flexible Formulas

For more examples of flexible formulas, see

  • how to use drop downs for the CONVERT function units
  • how to choose options for SUBTOTAL and AGGREGATE functions.

RANK Function Ties

What happens to the ranking if some of the scores are tied? Excel will skip subsequent numbers, if necessary, to show the correct rank.

  • In this example above, the last two scores in the list are the same — 38. The two students, Ivy and Joe, are both ranked as 4th.
  • The next highest score — Ed’s score of 36 — is ranked as 6th, not 5th, because there are 5 students ahead of him.

If you have to break the ties, or award prize money based on ties, there are examples below.

Break Ties With RANK Function

In some cases, ties aren’t allowed, so you have to find a way to break the tie.

Watch this video to see the steps, and the written instructions are below the video.

Your browser can’t show this frame. Here is a link to the page

Break Ties With RANK Function

In some cases, ties aren’t allowed, so you have to find a way to break the tie.

In this example, you could keep track of the number of minutes that each student worked on the test, and use that time to break any ties. If scores are tied, the student who takes less time to complete the test will rank ahead of the other student with the same score.

Calculate Decimal Amount for Tied Scores

I added the Test Times in column C, and a TieBreak formula in column E.

=IF(COUNTIF($B$2:$B$11,B2)>1,
RANK(C2,$C$2:$C$11,1)/100,0)

How the Tie Break Formula Works

The Tie Break formula uses COUNTIF and RANK functions, wrapped with an IF function, to see if a tie breaking decimal amount should be added to the original Rank.

  1. First, the TieBreak formula checks to see IF there is more than one instance of the number in the entire list:
    IF(COUNTIF($B$2:$B$11,B2)>1
  2. If there is more than one instance, it ranks the Times in ascending order, because a lower time is better:
    RANK(C2,$C$2:$C$11 ,1 )
  3. Next, it divides that amount by 100, to get a decimal amount. Later, you’ll add this decimal amount to the original Rank.
    Note: The divisor, 100, could be changed to another number, if you are working with a longer list.
    /100
  4. Finally, to complete the IF function, if there is only one instance a a Rank, the result for the TieBreak is zero.
    ,0)

Calculate the Final Rank

After calculating the tie breaking decimal amounts, you can add the RANK function results to the TieBreak results, to get the final ranking.

Читать еще:  Vba excel функции даты

In this example, two students were tied in 4th place. Joe took 27 minutes to complete the test, and his Time was ranked 5th. Ivy took 29 minutes to complete the test, and her Time was ranked 9th.

The Tie Break formula adds a decimal of 0.09 to Ivy’s score, and 0.05 to Joe’s score. In the final ranking, Joe, with 4.05 ranks higher than Ivy, with 4.09.

Split Winnings for Tied Rank

In a tournament, instead of breaking the ties, you might want to split the winnings among any tied players, if you’re awarding a cash prize, or points. To see the steps for splitting the winnings, watch this short video. The written instructions are below the video.

Your browser can’t show this frame. Here is a link to the page

Split Winnings for Tied Rank

In a tournament, instead of breaking the ties, you might want to split the winnings among any tied players, if you’re awarding a cash prize, or points. If 2 or more players have the same rank, they split the prize amount available for that rank, down to the next occupied rank.

Below is a sample prize table, showing the amount awarded for each rank. In this example, if 3 players are at rank 1, they would split the total amount (10+9+8=27) for ranks 1, 2 and 3.

Each of the 3 players at rank 1 earns 9 (27/3 = 9) and the player with the next highest score would be ranked 4th, and earn 7.

Calculate the Split Amount

To split the prize amount among tied players, the Prize formula uses the AVERAGE function, with the OFFSET function finding the range of cells to average.This formula is entered in cell D2 and copied down to cell D11.

How the Prize Formula Works

The Prize formula uses the AVERAGE function, with the OFFSET function finding the range of cells to average.

  1. The AVERAGE function will calculate the amount for each player, based on a specific range of cells:
    AVERAGE(
  2. The OFFSET function returns the range with the amounts to use for the average:
    OFFSET(
  3. In the OFFSET formula, the 1st argument is the reference cell. In this example, that is cell K1 — the heading for the Prize amounts column.
    $K$2,
  4. In the OFFSET formula, the 2nd argument is the number of rows down from the reference cell, that the cells to average start. The ranks are listed in ascending order, so for the Rank of 1, the cells to average would start 1 row down from the Reference cell of $K$1. The first player’s rank is in cell C2, so refer to that in the formula
    C2,
  5. In the OFFSET formula, the 3rd argument is the number of columns to the right of the reference cell, that the cells to average start. You want to find amounts in the same column, so the number is zero
    0,
  6. In the OFFSET formula, the 4th argument is the number of rows to include in the range. This should be the number of players who are tied at that rank. The COUNTIF function will count the instances of the rank in column C, that are equal to the rank in C2
    COUNTIF($C$2:$C$11,C2)

RANK IF Formula

Instead of using the RANK function to compare a number to an entire list of numbers, you might need to rank a value within a specific subset of numbers. For example, rank each day’s sales compared to other days in the same week.

In the screen shot below, there are sales records for two weeks.

  • Jan 2nd and Jan 4th have the highest sales in week 1, so they should have a rank of 1.
  • In week 2, Jan 10th has the highest sales, so it should have a rank of 1 for that week.

No RANKIF Function

There isn’t a RANKIF function, but you can use the COUNTIFS function to calculate the rank based on items with the same week number.

Enter this formula in cell D2, and copy it down to the last row with data:

=COUNTIFS([Wk], [@Wk], [Sales], «>»&[@Sales])+1

How It Works

The first criterion in the formula checks for other sales with the same week number:

=COUNTIFS([Wk], [@Wk]

The second criterion find items with a larger amount in the Sales column.

[Sales],»>»&[@Sales])

Then, 1 is added to that number, to get the ranking.

+1

For example, in week 1, look at the sales for Jan 3rd — 237.

  • There are 2 dates with a larger sales in week 1 — Jan 2nd and Jan 4th
  • Add 1 to that number, and Jan 3rd has a rank of 3

Download the Sample File

Download the zipped sample Excel RANK Function file. The file is in xlsx format, and does not contain macros.

Ссылка на основную публикацию
Adblock
detector