Как ранжировать данные в Excel, игнорируя нулевые значения?
При работе с наборами данных в Excel нулевые значения часто встречаются среди числовых данных. По умолчанию стандартные функции РАНГ учитывают эти нули при ранжировании, что может искажать реальную картину — например, в случае продаж, оценок или измерений, где ноль означает отсутствие данных, а не самый низкий результат. Для точного анализа, такого как составление рейтингов, фильтрация по показателям эффективности или подготовка статистической отчётности, зачастую требуется ранжировать только положительные числа, полностью игнорируя нули. На изображении ниже показан именно такой случай: ранги присваиваются исключительно ненулевым значениям, а нули либо остаются без рангов, либо отображаются как пустые ячейки для наглядности.
Формула: ранжирование ячеек с игнорированием Нулевые значения
Формула: ранжирование ячеек с игнорированием Нулевые значения
Чтобы ранжировать данные в Excel, исключая Нулевые значения, можно использовать пользовательские формулы, которые присваивают ранги только ячейкам с ненулевыми числами. Это особенно полезно, когда нули обозначают отсутствующие или нерелевантные данные, позволяя сосредоточиться исключительно на значимых записях. Такой подход хорошо подходит для небольших и средних наборов данных и обеспечивает мгновенную визуальную фильтрацию — достаточно протянуть формулу вниз по столбцу. Однако при частом изменении структуры Диапазон данных или условий ранжирования такие формулы могут потребовать корректировки.
Ранжирование с игнорированием нулей по возрастанию
Enter the following formula in the cell adjacent to your first data value (for example, if your data starts at B2, enter this in C2):
=IF(B2=0,"",RANK(B2,IF($B$2:$B$12>,0,$B$2:$B$12,0))+COUNTIF($B$2:$B2,$B2)-1)После ввода формулы в ячейку C2 нажмите Enter. Перетащите маркер заполнения из правого нижнего угла ячейки C2 вниз, чтобы применить формулу ко всему столбцу. Эта формула проверяет, равно ли значение нулю, и оставляет ячейку ранга пустой. Если вы копируете или перемещаете диапазон, обязательно убедитесь, что ссылки на ячейки по-прежнему соответствуют целевому диапазону данных.
Ранжирование с игнорированием нулей по убыванию
Для ранжирования по убыванию с игнорированием нулей введите следующую формулу (предполагается, что ваш список начинается с B2; введите её в C2):
=IF(B2,RANK(B2,B$2:B$12,1)-COUNTIF(B$2:B$12,0),"")Снова нажмите Enter после вставки формулы, затем перетащите маркер заполнения вниз, чтобы применить её ко всем строкам. Этот метод присваивает корректные ранги только ненулевым значениям, оставляя пустые ячейки вместо рангов для всех нулей.
В обеих формулах B2 — это начальная ячейка, а B2:B12определяет диапазон для ранжирования. Если в вашем наборе данных больше или меньше строк, скорректируйте ссылки на диапазон. Имейте в виду: при обновлении или расширении набора данных формулы могут потребовать корректировки. Кроме того, будьте внимательны при копировании формул — использование абсолютных ссылок (знаков)$) блокирует изменение адреса при заполнении вниз.
| Ранжирование с игнорированием нулей по возрастанию |
 |
| Ранжирование с игнорированием нулей по убываниюпорядок |
 |
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал 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.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек