Как легко ранжировать числа, пропуская пустые ячейки в Excel?

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

Ранжирование значений с пропуском пустых ячеек по возрастанию

Ранжирование значений с пропуском пустых ячеек по убыванию

Ранжирование значений с пропуском пустых ячеек по возрастанию

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

1. Выберите пустую ячейку рядом со списком, который вы хотите ранжировать, например B2, и введите эту формулу =IF(ISBLANK($A2),"",VALUE($A2&"."&(ROW()-ROW($B$2))))и протяните маркер заполнения вниз, чтобы применить эту формулу к другим ячейкам. См. скриншот:

В формуле A2 — это первая ячейка списка, который вы хотите ранжировать, а B2 — первая ячейка, куда вы помещаете формулу.

2. Затем перейдите к следующей ячейке, например C2, и введите эту формулу =SMALL($B$2:$B$8,ROW()-ROW($C$1)) и протяните маркер заполнения вниз, чтобы заполнить ячейки этой формулой. См. скриншот:

В формуле B2:B8 — это диапазон, используемый первой формулой, а C1 — это ячейка над ячейкой, к которой вы применяете эту формулу.

3. В ячейке D2 вы можете ранжировать значения по возрастанию. Введите эту формулу =IFERROR(MATCH($B2,$C$2:$C$8,0),"")и протяните маркер заполнения вниз, чтобы применить эту формулу к ячейкам. См. скриншот:

В этой формуле B2 — это первая ячейка, к которой применяется первая формула, а C2:C8 — это диапазон, к которому применяется вторая формула.

Ранжирование значений с пропуском пустых ячеек по убыванию

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

Выберите ячейку, которая будет ранжировать числа, затем введите эту формулу =IF(ISNA(RANK(A2,A$2:A$8)),"",RANK(A2,A$2:A$8)), и протяните маркер автозаполнения вниз до ячеек, к которым необходимо применить эту формулу.

В формуле A2 — это первая ячейка списка, который вы хотите ранжировать, а A2:A8 — это список, который нужно ранжировать.