KutoolsforOffice — Одно решение — пять мощных инструментов.Меньше усилий — больше результата.

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

АвторСуньДата изменения

При работе с данными в Excel часто встречаются списки, содержащие пустые ячейки. Если вы используете стандартные функции ранжирования Excel, такие как RANK или RANK.EQ, пустые ячейки обычно приводят к ошибкам или искажают результаты ранжирования. Это затрудняет интерпретацию данных, особенно если вы хотите оставить пустые ячейки без значений — а не отображать ошибки или случайные ранги. Эффективное ранжирование чисел с автоматическим пропуском пустых ячеек значительно повышает читаемость и удобство использования результатов, делая вашу таблицу более профессиональной и понятной.

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

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

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

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

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

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

1. Выберите пустую ячейку рядом со своими значениями — например, ячейку B2, если ваш список начинается с A2, — и введите следующую формулу:

=IF(ISBLANK($A2),"",VALUE($A2&,"."&,(ROW()-ROW($B$2))))

Формула возвращает пустое значение, если ячейка A2 пуста; в противном случае она генерирует десятичные числа на основе значения из A2, последовательно добавляя .0, .1, .2, .3 и так далее при протягивании маркера заполнения вниз для копирования формулы во все строки с данными.

Снимок экрана формулы для ранжирования значений с пропуском пустых ячеек в Excel

Пояснения параметров и советы:

  • $A2: первая ячейка для ранжирования. Измените её, если ваш список начинается с другой строки.
  • $B$2: ячейка, в которую вы вводите эту формулу. Обратите внимание на абсолютные ссылки (например, $A$2 и $B$2), чтобы формула корректно работала при копировании вниз.

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

=SMALL($B$2:$B$8,ROW()-ROW($C$1))

Эта формула последовательно извлекает наименьшее, следующее за ним и последующие значения из диапазона B2:B8 (примечание: измените диапазон, если ваши данные занимают больше строк) при копировании формулы вниз.

Снимок экрана формулы МАЛЫЙ, применённой для ранжирования значений в Excel

Пояснения параметров:

  • $B$2:$B$8: диапазон, в котором применяется предыдущая (первая) вспомогательная формула.
  • $C$1: ячейка, расположенная непосредственно над той, в которую вы вводите формулу; это смещение определяет порядок ранжирования.

3. В ячейке D2 введите следующую формулу, чтобы присвоить ранги, оставив пустые ячейки без изменений:

=IFERROR(MATCH($B2,$C$2:$C$8,0),"")

Эта формула ищет значение из B2 среди отсортированных результатов в диапазоне C2:C8. Если совпадение найдено, выводится ранг; если нет (например, для пустых ячеек) — ничего не выводится, сохраняя чистый вид. Протяните маркер заполнения вниз, чтобы применить формулу ко всем соответствующим строкам.

Снимок экрана формулы ПОИСКПОЗ для создания ранга с игнорированием пустых ячеек

Параметры:

  • $B2: ячейка, содержащая вспомогательное значение для ранжирования.
  • $C$2:$C$8: диапазон отсортированных вспомогательных значений.

Меры предосторожности: При добавлении или удалении данных не забудьте обновить все диапазоны в каждой формуле в соответствии с новым объёмом данных. Для очень больших списков рекомендуем использовать динамические диапазоны или таблицы Excel — это поможет свести к минимуму ручную настройку диапазонов.

Устранение неполадок: Если ранги пропущены или смещены, проверьте правильность выравнивания диапазонов во всех вспомогательных формулах. Несовпадение между столбцами приведёт к некорректному ранжированию или непреднамеренным ошибкам.

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

Когда нужно присвоить ранги в порядке убывания (наивысшее значение получает ранг 1), существует более быстрый способ — всего одна формула справится с задачей. Это особенно удобно для тестовых баллов, планов продаж и подобных наборов данных, где пустые ячейки означают отсутствующую или недоступную информацию, и вы не хотите, чтобы они занимали места в рейтинге или выводили ошибки.

Выберите ячейку в той же строке, что и первая запись данных, куда должен выводиться результат, и введите:

=IF(ISNA(RANK(A2,A$2:A$8)),"",RANK(A2,A$2:A$8))

После ввода формулы используйте маркер заполнения, чтобы скопировать её вниз по столбцу с вашими данными. Эта формула проверяет, возвращает ли функция RANKошибку (например, когда ячейка)A2 пуста); если да — результат остаётся пустым вместо отображения «#Н/Д». Если ячейка содержит допустимое значение, отображается соответствующий ранг.

Снимок экрана, показывающий, как ранжировать числа по убыванию с игнорированием пустых ячеек

Параметры:

  • A2: ячейка, которую нужно ранжировать (подставьте нужную ячейку из вашего диапазона данных).
  • A$2:A$8: полный диапазон ваших данных (используйте абсолютные ссылки для корректного копирования).

Напоминания об ошибках: Если вы всё ещё видите ошибки «#Н/Д», дважды проверьте, что ссылки в формуле соответствуют вашему целевому диапазону данных, и убедитесь, что в ранжируемых ячейках нет нечисловых значений.

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

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

Как использовать:

1. Перейдите на вкладку Разработчик и нажмите Visual Basic, чтобы открыть редактор Microsoft Visual Basic для приложений. Если вкладка «Разработчик» не отображается, ознакомьтесь с руководством: Отображение вкладки «Разработчик» в Excel.

2. В новом окне Microsoft Visual Basic для приложений нажмите Вставка > Модуль и вставьте один из приведённых ниже кодов в окно модуля:

  • Чтобы выполнить ранжирование по возрастанию с пропуском пустых ячеек:
    Sub RankSkipBlank_Ascending()
    Dim WorkRng As Range
    Dim Cell As Range
    Dim NumArr() As Double
    Dim Ws As Worksheet
    Dim OutputCell As Range
    Dim i As Long, j As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Please select the range to rank", xTitleId, WorkRng.Address, Type:=8)
    Set Ws = WorkRng.Worksheet
    
    Set OutputCell = Application.InputBox("Please select the first cell to output the ascending ranking", xTitleId, Type:=8)
    If OutputCell Is Nothing Then Exit Sub
    
    j = 0
    ReDim NumArr(1 To WorkRng.Rows.Count)
    
    For Each Cell In WorkRng
        If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then
            j = j + 1
            NumArr(j) = Cell.Value
        End If
    Next Cell
    
    Dim temp As Double
    Dim k As Long
    
    For i = 1 To j - 1
        For k = i + 1 To j
            If NumArr(i) > NumArr(k) Then    ' ← CHANGE HERE
                temp = NumArr(i)
                NumArr(i) = NumArr(k)
                NumArr(k) = temp
            End If
        Next k
    Next i
    
    Dim RankArr() As Double
    ReDim RankArr(1 To j)
    For i = 1 To j
        RankArr(i) = NumArr(i)
    Next i
    
    Dim RankValue As Long
    Dim r As Long: r = 0
    
    For Each Cell In WorkRng
        r = r + 1
        If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then
            RankValue = 0
            For k = 1 To j
                If Cell.Value = RankArr(k) Then
                    RankValue = k        ' 1 = smallest
                    Exit For
                End If
            Next k
            OutputCell.Offset(r - 1, 0).Value = RankValue
        Else
            OutputCell.Offset(r - 1, 0).Value = ""
        End If
    Next Cell

End Sub
  • Чтобы выполнить ранжирование по убыванию с пропуском пустых ячеек:
    Sub RankSkipBlank_Descending()
    Dim WorkRng As Range
    Dim Cell As Range
    Dim NumArr() As Double
    Dim Ws As Worksheet
    Dim OutputCell As Range
    Dim i As Long, j As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Please select the range to rank", xTitleId, WorkRng.Address, Type:=8)
    Set Ws = WorkRng.Worksheet
    
    Set OutputCell = Application.InputBox("Please select the first cell to output the descending ranking", xTitleId, Type:=8)
    If OutputCell Is Nothing Then Exit Sub
    
    j = 0
    ReDim NumArr(1 To WorkRng.Rows.Count)
    
    For Each Cell In WorkRng
        If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then
            j = j + 1
            NumArr(j) = Cell.Value
        End If
    Next Cell
    
    Dim temp As Double
    Dim k As Long
    
    For i = 1 To j - 1
        For k = i + 1 To j
            If NumArr(i) < NumArr(k) Then
                temp = NumArr(i)
                NumArr(i) = NumArr(k)
                NumArr(k) = temp
            End If
        Next k
    Next i
    
    Dim RankArr() As Double
    ReDim RankArr(1 To j)
    For i = 1 To j
        RankArr(i) = NumArr(i)
    Next i
    
    Dim RankValue As Long
    Dim r As Long: r = 0

    For Each Cell In WorkRng
        r = r + 1
        If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then
            RankValue = 0
            For k = 1 To j
                If Cell.Value = RankArr(k) Then
                    RankValue = k
                    Exit For
                End If
            Next k
            OutputCell.Offset(r - 1, 0).Value = RankValue
        Else
            OutputCell.Offset(r - 1, 0).Value = ""
        End If
    Next Cell

End Sub

3. Нажмите F5, чтобы запустить макрос. Сначала появится диалоговое окно с запросом на выбор диапазона для ранжирования, а затем — другое окно, предлагающее указать первую ячейку для размещения результатов. Макрос выведет ранги, начиная с выбранной ячейки, при этом все пустые ячейки в исходном диапазоне останутся пустыми.

Советы:

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

Лучшие инструменты повышения продуктивности в Office

🤖KUTOOLS AI Помощник: Преобразуйте Анализ данных с помощью:Интеллектуального выполнения   |  Генерации кода|  Создания пользовательские формулы  |  Анализа данных и построения диаграмм|  Вызова Расширенные функции…
Популярные функции:Поиск, выделение или Отметить дубликаты   |  Удалить пустые строки   |  Объединить столбцы или ячеек без потери данных   |  Округление без использования формул…
Супер ПОИСК:VLookup по нескольким критериям  |  VLookup по нескольким значениям  |   VLookup по нескольким листам   |   Распознавание нечетких соответствий…
Расширенный раскрывающийся список:Быстрое создание выпадающего списка   |  Зависимый выпадающий список   |  Выпадающий список с множественным выбором…
Управление столбцами:Добавление заданного количества столбцов|Перемещение столбцов|Переключение видимости скрытых столбцов|Сравнение диапазонов и столбцов…
Избранные функции:Сетка фокусировки   |  Просмотр дизайна   |Улучшенная строка формулы   | Управление рабочими книгами и листами   |  Библиотека ресурсов(автотекст)|  Выбор даты   |  Объединить листы  |  Шифрование/Расшифровать ячейки   | Отправка писем по списку   |  Супер фильтр   |   Специальный фильтр(Фильтр ячеек с жирным шрифтом/курсив/зачёркивание…) …
Лучшие наборы инструментов 15:12 Текстовыеинструменты(Добавить текст,Удалить определенные символы, …)|   50+Типыдиаграмм(Диаграмма Ганта, …)|   40+ Практические формулы(Рассчитать возраст на основе даты рождения, …)|   19 Инструментывставки(Вставить QR-код,Вставка изображения по пути, …)|   12 Инструментыпреобразования(Преобразовать в слова,Конвертация валют, …)|   7 Объединить и разделитьИнструменты(Расширенное объединение строк,Разделить ячейки, …)|… и многое другое
Используйте Kutools на предпочитаемом языке — поддержка английского, испанского, немецкого, французского, китайского и ещё 40+ языков!

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

Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу

  • Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
  • Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!

Все надстройки Kutools — один установщик

Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.

ExcelWordOutlookTabsPowerPoint
  • Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
  • Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
  • Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
  • 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
  • Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек