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

Содержание

Вопрос или проблема
Ответ или решение
Проблема с использованием RANK
Альтернативные формулы для точного ранжирования
Заключение

Вопрос или проблема

1) Скопируйте следующие значения и вставьте в ячейки A1:A8 в Excel.

Заголовок столбца A

600

1200

500

900

500

1300

300

2) Скопируйте следующие значения и вставьте в ячейки B1:B8 в Excel.

Заголовок столбца B

3) Скопируйте следующие значения и вставьте в ячейки C1:C8 в Excel.

Заголовок столбца C

=RANK(A2,$A$2:$A$8,0)

=RANK(A3,$A$2:$A$8,0)

=RANK(A4,$A$2:$A$8,0)

=RANK(A5,$A$2:$A$8,0)

=RANK(A6,$A$2:$A$8,0)

=RANK(A7,$A$2:$A$8,0)

=RANK(A8,$A$2:$A$8,0)

4) Следующая картинка показывает, что формула RANK дает неправильный результат.

5) Формула RANK дает неправильный результат, потому что 500 существует в столбце A дважды.

6) Знаете ли вы альтернативную формулу для формулы RANK?

7) Обновление: Желаемое решение найдено благодаря Mayukh Bhattacharya следующим образом.

SUMPRODUCT((A2<=$A$2:$A$8)/COUNTIF($A$2:$A$8;$A$2:$A$8))

Формула, которая может помочь, это:

[B2]=LET(src,A2:A8,u,UNIQUE(src),XLOOKUP(src,SORT(u,,-1),SEQUENCE(ROWS(u))))

UNIQUE оставляет только уникальные продажи, чтобы позволить им занимать одно место.

SORT сортирует уникальные продажи по убыванию.

Для каждой продажи (src) XLOOKUP находит её в отсортированных уникальных продажах, чтобы получить соответствующее место из последовательности мест (от 1 до 6).

LET позволяет определить временные переменные, такие как ‘src’ и ‘u’, для повторного использования.

Короткие формулы!

[B2]=LET(src,A2:A8,XMATCH(src,SORT(UNIQUE(src),,-1)))

В этой формуле XMATCH предоставляет номер места без генерации SEQUENCE.

То же самое без LET:

=XMATCH(A2:A8,SORT(UNIQUE(A2:A8),,-1))

Еще одна формула, которая считает количество уникальных значений, больших левого значения, для каждой ячейки B2:B8:

=SUM(--(UNIQUE($A$2:$A$8)>A2))+1

Введите эту формулу в B2 и заполните вниз до B8.

Ответ или решение

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

Проблема с использованием RANK

В приведенном примере вы имеете столбец со значениями в ячейках A1:A8:

При использовании формулы =RANK(A2,$A$2:$A$8,0) для вычисления ранга значений Excel возвращает неправильные результаты, когда несколько ячеек содержат одинаковые значения, как это происходит с числом 500, которое встречается дважды.

Функция RANK присваивает одинаковый ранг для одинаковых значений. Например, если 500 находится на третьем и четвертом месте по величине, то оба значения будут иметь ранг 3, что приводит к пропуску следующего ранга. Это может запутать пользователей и сделать результаты менее интуитивно понятными.

Альтернативные формулы для точного ранжирования

Существует несколько способов, как можно обойти проблемы с RANK и корректно вычислить ранги. Вот некоторые из наиболее рекомендуемых подходов:

Использование формулы SUMPRODUCT:
```
=SUMPRODUCT((A2<=$A$2:$A$8)/COUNTIF($A$2:$A$8,$A$2:$A$8))
```
Эта формула использует комбинацию SUMPRODUCT и COUNTIF для вычисления ранг без пропусков, учитывая количество дубликатов.
Использование функции LET с XLOOKUP:
```
=LET(src,A2:A8,u,UNIQUE(src),XLOOKUP(src,SORT(u,,-1),SEQUENCE(ROWS(u))))
```
Функция LET позволяет создавать временные переменные, такие как src и u, для упрощения формулы. UNIQUE исключает дубликаты, SORT упорядочивает уникальные значения по убыванию, а XLOOKUP использует эти отсортированные значения для нахождения ранга.
Короткая версия с XMATCH:
```
=LET(src,A2:A8,XMATCH(src,SORT(UNIQUE(src),,-1)))
```
Эта версия более краткая и использует XMATCH для поиска места без необходимости создавать последовательность.
Формула без использования LET:
```
=XMATCH(A2:A8,SORT(UNIQUE(A2:A8),,-1))
```
Подсчет чисел уникальных значений, больших заданного:
```
=SUM(--(UNIQUE($A$2:$A$8)>A2))+1
```
Эта формула вычисляет количество уникальных значений, которые больше значения в текущей ячейке, добавляя 1, чтобы получить правильный ранг.

Заключение

Функция RANK в Excel может выдавать некорректные результаты, когда в ваших данных есть дубликаты. Для точного ранжирования чисел в таких случаях рекомендуется использовать альтернативные формулы, которые учитывают уникальные значения. Эти методы не только обеспечивают корректное вычисление ранга, но и разъясняют ситуацию с дублирующимися данными. Опираясь на данные альтернативы, вы сможете эффективно управлять большими массивами данных и извлекать из них полезную информацию.