Как автоматически нумеровать или перенумеровывать строки после фильтрации в Excel?
При работе с большими наборами данных в Excel часто требуется присвоить последовательность чисел в качестве идентификаторов строк для упорядочивания или отслеживания записей. Однако после применения фильтра для отображения определённых строк видимая последовательность или Установить количество строк остаётся такой же, как в исходном наборе данных — она не обновляется в соответствии с отфильтрованным, видимым порядком. Это может вызвать путаницу и затруднить представление чётко пронумерованного списка для отчётов или презентаций. Иногда требуется, чтобы последовательность динамически перестраивалась так, чтобы только видимые (отфильтрованные) строки имели сквозную нумерацию, как показано на приведённых ниже снимках экрана. В этой статье мы рассмотрим, как автоматически нумеровать или перенумеровывать после фильтрации в Excel, чтобы повысить ясность и удобство управления отфильтрованными данными.
Автоматическая нумерация или перенумерация после фильтрации с помощью кода VBA
Автоматическая нумерация или перенумерация после фильтрации с помощью Kutools для Excel
 |  |  |
Автоматическая нумерация или перенумерация после фильтрации с помощью кода VBA
В Excel при фильтрации данных стандартная функция автозаполнения не заполняет последовательность чисел в соответствии с видимыми строками. Вместо этого она продолжает нумерацию, включая скрытые строки, что часто означает, что номера будут ссылаться на исходные позиции строк, а не только на отфильтрованные данные. Ручной ввод номеров для каждой видимой строки быстро становится утомительным и непрактичным для больших списков. В таких случаях простой код VBA эффективно перенумерует строки в вашем отфильтрованном Диапазон данных, значительно экономя время и снижая вероятность ошибок.
1. Удерживая клавиши ALT + F11, откройте окно Microsoft Visual Basic для приложений. Это основной интерфейс для написания и выполнения кода VBA в Excel.
2. В окне VBA выберите Вставка > Модуль, чтобы добавить новый модуль. Вставьте следующий макрос в окно Модуля.
Код VBA: Автоматическая нумерация или перенумерация после фильтрации
Sub Renumbering()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type: = 8)
Set WorkRng = WorkRng.Columns(1).SpecialCells(xlCellTypeVisible)
xIndex = 1
For Each Rng In WorkRng
Rng.Value = xIndex
xIndex = xIndex + 1
Next
End Sub
3. После вставки кода нажмите клавишу F5, чтобы запустить его. Появится диалоговое окно с запросом на выбор диапазона ячеек, которые нужно автоматически пронумеровать или перенумеровать только для видимых строк после фильтрации. См. снимок экрана:
4. После выбора целевого диапазона нажмите кнопку ОК. Видимые (отфильтрованные) ячейки в вашем диапазоне теперь пронумерованы по порядку — как показано ниже:
Примечания и советы: Этот код перезаписывает нумерацию в выбранном вами диапазоне и заменяет исходную последовательность чисел. Если вы позже отмените фильтр, исходный порядок (до фильтрации) будет утерян — останутся только номера для отфильтрованных строк: последовательные числа. Чтобы избежать потери исходных данных, рекомендуем создать резервную копию листа или соответствующего столбца перед запуском макроса. Кроме того, убедитесь, что макросы разрешены для вашей рабочей книги Excel, и будьте осторожны при попытке отмены изменений — действия макросов не всегда можно отменить с помощью Ctrl + Z.
Автоматическая нумерация или перенумерация после фильтрации с использованием формулы Excel (SUBTOTAL и ROW)
Многим пользователям больше нравится решение на основе формул, ведь оно остаётся динамическим — автоматически обновляя количество строк при каждом изменении условий фильтрации. Комбинация функций Excel ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СТРОКА позволяет генерировать последовательность только для видимых строк, нумеруя подряд исключительно те, что отображаются после фильтрации. Это решение особенно ценно, если вы часто применяете разные фильтры или ожидаете изменения данных со временем: не нужно заново запускать VBA или вручную проставлять номера — всё обновляется автоматически в режиме реального времени.
Применимые сценарии: Используйте этот подход, если хотите, чтобы нумерация автоматически обновлялась при каждом применении или изменении фильтра — без необходимости вручную вводить данные заново или запускать скрипты VBA. Это идеальное решение для создания динамических отчётов и совместных таблиц.
1. В первой ячейке столбца, где должна начинаться нумерация — например, если ваш диапазон данных начинается со строки 2 (заголовки находятся в строке 1), а нумерация должна начинаться с ячейки A2, — введите следующую формулу:
=SUBTOTAL(3, $B$2:B2)Здесь $B$2:B2ссылается на столбец в ваших данных, который всегда заполнен (например, столбец с именами или идентификаторами). Возможно, вам потребуется скорректировать ссылку, указав подходящий столбец в вашей таблице. Сделайте первую часть ссылки абсолютной ()$B$2), а во второй части оставьте относительную ссылку, чтобы номер строки автоматически увеличивался (например, B2, B3 и т.д.).
2. Нажмите Enter, чтобы подтвердить. Чтобы применить нумерацию ко всему списку, перетащите маркер заполнения вниз по столбцу нумерации, выделив все строки.
После фильтрации данных только видимые строки получат последовательные номера (1, 2, 3, …), а скрытые строки автоматически исключаются. Этот метод не требует ручного обновления при каждом изменении фильтра — нумерация всегда будет точно соответствовать количеству отображаемых строк.
Дополнительные советы:Убедитесь, что столбец, используемый в формуле в качестве ссылки, не содержит пустых ячеек в той части данных, которую вы хотите пронумеровать. Если ваши данные начинаются не со строки 2, скорректируйте абсолютную ссылку на ячейку соответствующим образом (например,)$C$5:C5, если данные начинаются со строки 5, а в качестве ссылочного используется столбец C). При появлении пропусков в нумерации проверьте наличие скрытых строк или пустых ячеек в диапазоне ссылки. Если вы работаете с таблицей Excel, формулу можно ещё больше упростить с помощью структурированных ссылок (например, =SUBTOTAL(3,OFFSET([@Column],0,0,ROW()-ROW(Table1[#Headers])))), однако стандартный подход, описанный выше, подходит для большинства случаев.
Автоматическая нумерация или перенумерация после фильтрации с помощью Kutools для Excel
Если вы установили Kutools для Excel, функция Вставить номер последовательности позволяет быстро и легко вставлять номера последовательности в отфильтрованные данные. В отличие от стандартных возможностей Excel, которые не умеют учитывать только видимые строки при заполнении номеров, Kutools автоматически подстраивается под ваш отфильтрованный диапазон, значительно ускоряя нумерацию и сводя ручную работу к минимуму. Эта функция особенно полезна при работе со сложными наборами данных или когда требуется гибкая настройка нумерации — например, добавление префиксов, выбор начального числа или задание шага инкремента.
После установки Kutools для Excelвыполните следующие практические шаги:
1. Выделите ячейки, которые нужно перенумеровать в отфильтрованных данных. Чтобы избежать путаницы, лучше выделять только видимые строки.
2. Перейдите в меню Kutools > Вставка > Вставить номер последовательности, как показано:
3. В диалоговом окне Вставить номер последовательности нажмите кнопку Создать, чтобы задать шаблон нумерации.
4. Создайте последовательность с помощью элементов управления в левом нижнем углу, задав начальное число, шаг, количество цифр и другие необходимые параметры.
5. Когда всё будет готово, нажмите кнопку Добавить, чтобы сохранить шаблон последовательности — он сразу появится в списке последовательностей в диалоговом окне.
6. Нажмите кнопку Заполнить диапазон. Последовательность будет немедленно применена к указанному (видимому) диапазону данных. Только строки, оставшиеся видимыми после фильтрации, будут пронумерованы по порядку, как показано здесь:
Примечание: Функция «Вставить номер последовательности» из Kutools для Excel не только экономит время при работе с большими наборами данных, но и снижает количество ошибок, нумеруя только отфильтрованные строки. Вы можете повторно применить или обновить нумерацию в любое время — особенно полезно, если фильтр часто меняется. При нумерации объединённых или нерегулярных диапазонов обязательно проверяйте предварительный просмотр в диалоговом окне, чтобы увидеть итоговый результат.
Скачайте Kutools для Excel и начните бесплатный пробный период прямо сейчас!
Демонстрация: Автоматическая нумерация или перенумерация после фильтрации с помощью Kutools для Excel
Связанная статья:
Как автоматически пронумеровать объединённые ячейки в Excel?
Лучшие инструменты повышения продуктивности в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек