Как сортировать значения в Excel, игнорируя нули?
При работе с набором данных в Excel, содержащим как числа, так и нули, стандартная функция сортировки обычно перемещает все Нулевые значения в начало списка при сортировке по возрастанию. Однако во многих ситуациях может потребоваться отсортировать данные так, чтобы Нулевые значения оставались внизу, эффективно игнорируя их в процессе сортировки. Например, при анализе сумм продаж, производственных показателей или результатов тестов вы можете захотеть сосредоточиться на ненулевых результатах и просматривать записи с нулями только в конце. Это делает отчёты более понятными и значимыми для дальнейшего анализа.
Сортировка значений с игнорированием нулей с помощью вспомогательного столбца
Sort Values Ignoring Zeros with a Single Formula Column (Alternative Method)
Сортировка значений с игнорированием нулей с помощью кода VBA (альтернативное решение)
Сортировка значений с игнорированием нулей с помощью вспомогательного столбца
Чтобы отсортировать данные, оставив нулевые значения внизу, используйте вспомогательный столбец с подходящей формулой. Этот метод прост в реализации и работает во всех версиях Excel, что особенно удобно, если нули в вашем списке перемешаны с другими значениями, а вы хотите сгруппировать все ненулевые числа вместе, разместив нули в конце.
1. В пустой ячейке рядом с вашими данными (например, B2 — если исходные значения находятся в столбце A) введите следующую формулу:
=IF(A2=0,"",A2)Эта формула проверяет значение в ячейке A2. Если значение равно нулю, формула возвращает пустую ячейку («»); если значение не равно нулю, возвращается исходное значение. Используйте маркер заполнения, чтобы скопировать эту формулу на все соответствующие строки ваших данных. В результате вспомогательный столбец будет отображать ненулевые Нулевые значения, а нули останутся пустыми ячейками. На снимке экрана показано, как выглядит вспомогательный столбец после применения формулы:
Совет: убедитесь, что в формуле указаны правильные столбец и диапазон ячеек, соответствующие реальной структуре вашего листа, — это поможет избежать ошибок.
2. После создания вспомогательного столбца выделите все ячейки в этом столбце, перейдите на вкладку Данные и выберите Сортировка от наименьшего к наибольшему. В появившемся диалоговом окне Предупреждение о сортировке убедитесь, что выбрана опция Расширить выделенный диапазон, чтобы вся строка данных сортировалась вместе со значениями вспомогательного столбца.
Совет: обязательно расширяйте выделенный диапазон — в противном случае сортировке подвергнется только вспомогательный столбец, и ваши данные потеряют согласованность. Всегда убедитесь, что сортируете весь диапазон данных.
3. Нажмите Сортировка. Данные будут отсортированы по возрастанию: все ненулевые значения окажутся вверху, а нули — внизу, как и требовалось. После сортировки вспомогательный столбец можно удалить или скрыть, если он больше не нужен.
Примечание: если ваши данные содержат формулы, перед сортировкой рекомендуем скопировать и вставить их как значения — это поможет избежать неожиданных результатов.
Главное преимущество этого метода — его простота и совместимость со всеми версиями Excel. Однако он требует добавления вспомогательного столбца, что может быть неудобно для пользователей, стремящихся избегать лишних столбцов. Кроме того, если ваш набор данных часто обновляется, убедитесь, что новые строки включены в диапазон действия формулы вспомогательного столбца.
Устранение неполадок: если нули не перемещаются вниз, как ожидалось, убедитесь, что формула правильно введена и скопирована во все соответствующие ячейки, а также проверьте, был ли расширен выделенный диапазон при сортировке.
Совет. По умолчанию Excel помещает пустые ячейки в конец списка. Если вам нужно, чтобы пустые ячейки (обозначающие исходные нули) отображались в начале, воспользуйтесь функцией Kutools для Excel Расширенная сортировка и выберите опцию Пустые ячейки в начале, как показано на снимке экрана ниже. Эта функция обеспечивает гибкость при сортировке — например, когда важно в первую очередь обратить внимание на пропущенные или нулевые значения. Инструмент доступен для бесплатного тестирования со всеми функциями на 30 дней. Скачайте пробную версию здесь.
Sort Values Ignoring Zeros with a Single Formula Column (Alternative Method)
Этот метод идеально подходит, когда нужно извлечь и отсортировать только ненулевые значения из списка — без лишних шагов и без использования встроенных функций сортировки. Он особенно удобен, если требуется получить отдельный отсортированный список ненулевых значений, оставив исходные данные нетронутыми.
1. Введите следующую формульную формулу в новый столбец (например, C2):
=SMALL(IF($A$2:$A$11<,>,0,$A$2:$A$11),ROW(A1))2. Нажмите Ctrl+Shift+Enter (для Excel 2019 и более ранних версий) или просто Enter (для Excel 365/Excel 2021), а затем перетащите маркер заполнения вниз на нужное количество строк. Эта формула возвращает отсортированные ненулевые значения из исходного диапазона данных в столбце A.
Пояснение: IF($A$2:$A$110,$A$2:$A$11) фильтрует ненулевые значения, а SMALL(...,ROW(A1)) возвращает результаты, отсортированные по возрастанию. Измените диапазон $A$2:$A$11, чтобы он соответствовал вашим данным, и убедитесь, что в столбце C достаточно строк для размещения всех ненулевых значений — иначе формула вернёт ошибки в лишних ячейках.
Этот подход эффективен и сохраняет исходный список без изменений, однако отсортированные значения отображаются только в новом месте.
Примечание: формула сначала извлекает уникальные значения, а затем сортирует их.
Сортировка значений с игнорированием нулей с помощью кода VBA (альтернативное решение)
Если вам регулярно приходится сортировать списки, игнорируя нули, автоматизация с помощью VBA может значительно сэкономить время. Это решение идеально подходит пользователям, уже знакомым с макросами и работающим с большими или часто обновляемыми списками. Однако перед запуском любого скрипта VBA обязательно создавайте резервную копию своих данных.
1. Перейдите в меню Инструменты разработчика > Visual Basic, чтобы открыть окно Microsoft Visual Basic для приложений. Нажмите Вставка > Модуль, затем вставьте следующий код в модуль:
Sub SortIgnoreZeros()
Dim rng As Range
Dim lastRow As Long
Dim ws As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
Set rng = Application.InputBox("Select the range to sort", xTitleId, "", Type:=8)
lastRow = rng.Rows.Count
' Move nonzeros to top
rng.Sort Key1:=rng, Order1:=xlAscending, Header:=xlNo
End Sub2После ввода кода нажмите 
, чтобы запустить макрос. Появится диалоговое окно с запросом на выбор диапазона для сортировки. Скрипт VBA отсортирует выбранный диапазон по возрастанию, поместив нули в конец. Убедитесь, что выделен только нужный столбец со значениями — иначе строки могут оказаться несогласованными.
Этот подход с использованием VBA отлично подходит для выполнения повторяющихся задач, но будьте осторожны с несохранёнными данными и всегда проверяйте выделение перед запуском макроса.
В заключение, отсортировать значения в Excel с игнорированием нулей можно несколькими способами — каждый со своими преимуществами. Выбор оптимального метода зависит от частоты выполнения задачи, объёма данных и необходимости сохранять исходные данные без изменений. Если результаты сортировки кажутся неожиданными, всегда дважды проверяйте диапазоны формул, параметры расширения выделения при сортировке и обязательно делайте резервную копию листа. Освоение встроенных возможностей Excel, дополнительных формул, макросов VBA или расширенных функций сортировки из Kutools поможет вам подобрать наиболее эффективный рабочий процесс для ваших задач.
Лучшие инструменты повышения продуктивности в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек