Блог

Как формулы массива работают с ранжированием? Ранжирование с дубликатами без пробелов, по порядку.

У меня есть задача, которая должна ранжировать отделы по порядку (путем оценки нескольких показателей). Но иногда возникает ситуация, когда несколько отделов получают одинаковый ранг (дублирующий ранг). И простая формула РАНГА () не работает должным образом из-за пробелов в рангах (если два отдела занимают первое место, следующий будет на третьей позиции).

Я нашел решение этого небольшого затруднения — https://www.extendoffice.com/documents/excel/4283-excel-rank-without-skipping-numbers.html

Но я не могу понять, как именно работает эта формула…

Как это работает?

  {=SUM(IF(A2>$A$2:$A$14,1/COUNTIF($A$2:$A$14,$A$2:$A$14))) 1}
 

Не очень интуитивно понятно, как и все формулы массива на самом деле. Объяснение предполагает, что вы знаете, что такое формула массива, массив значений и как они работают.
Если вы разберете формулу на несколько частей, возможно, ее будет проще понять:

1. COUNTIF($A$2:$A$14,$A$2:$A$14) -> создает ad-массив одинакового размера диапазона (A2: A14) и для каждой строки подсчитывает, сколько раз находит значение строки, поэтому, если у вас есть два отдела с одинаковым показателем (в данном случае указанным в столбце A), в обеих строках двух отделовформула дает вам 2, если у вас есть три отдела с одинаковым показателем, формула дает 3 для каждой строки и так далее.
2. 1 / пункт 1 -> массив с обратным числом, описанным в пункте 1. Теперь, если вы суммируете все n вхождений строк с одинаковым показателем этого массива, вы получаете 1, и здесь у вас есть трюк. Когда (после) мы суммируем количество показателей, превышающих число показателей в строке, которую мы оцениваем, этот трик позволяет нам считать 1 для каждого показателя вместо подсчета n, если есть больше показателей с одинаковым значением.
3. IF(A2>$A$2:$A$14,*pt.2*) -> возвращает arry значений пункта 2, «отфильтрованных» только со значениями, которые соответствуют строкам A2>An , где, поэтому те, в которых оцениваемый вами индикатор более сложный, чем тот, в строке, в которой вы находитесь.
4. SUM(*pt.3*) 1 -> суммирует значения отфильтрованного массива pt. 3. Обратите внимание, что если у вас более одной строки с одним и тем же показателем, для трика пункта 2 их сумма равна 1, а не n.

Обратите внимание, что строки A2>$A$2:$A$14 будут нумероваться в порядке возрастания от меньшего значения показателя к большему, если вы предпочитаете обратное, вы можете использовать меньший размер вместо терки в формуле: A2<$A$2:$A$14