Как пронумеровать строки в excel по порядку при фильтре

Как в Excel пронумеровать строки по порядку

Работа с Microsoft Excel — отличный вариант, если нужно составить таблицу, отчет и т.д. Но хотя возможностей здесь больше, обычным пользователям сложнее разобраться даже с такими простыми действиями как нумерация строк по порядку. Это одно из базовых действий, а для его исполнения есть не менее 5 разных способов.

Полуавтоматическая нумерация строк

Наиболее простой способ в эксель пронумеровать строки по порядку — протянуть значения столбца.

Для этого:

1. В первой ячейке вручную прописывается цифра “1”.

2. Во второй ячейке столбца вручную прописывается цифра “2”.

3. Обе ячейки выделяются и в нижнем правом краю области захвата появляется крестик, который с помощью мышки перетаскивается вниз на нужное количество строк.

Обратите внимание! Если нумерации будет недостаточно, нужно захватить лишь значения последних двух строк и снова протащить вниз.

Простые формулы для нумерации строк

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

Вот формулы:

1. =А1+1. В первой ячейке прописывается вручную цифра “1”, а во вторую вводится формула. Вводить вручную А1 не нужно. Это адрес ячейки, достаточно просто на нее нажать при написании формулы. Дальше остается только протянуть вниз показатель из последней заполненной ячейки.

2. =СТРОКА()-1. Формула прописывается сразу в первой ячейке. Автоматически ее значение будет 0. Для дальнейшей нумерации достаточно протянуть последнюю заполненную ячейку.

Эти формулы можно просто скопировать и вставить в таблицу, они будут работать сразу же.

Сложные формулы с условиями

С этими формулами необходимо немного разобраться. Используются они если пронумеровать строки по порядку нужно с соблюдением определенных условий.

=ЕСЛИ(B1<>»»;СЧЁТЗ($B$1:B1);»»)/IF(B1<>»»;COUNTA($B$1:B1);»»).

Строки будут пронумерованы подряд при соблюдении определенного условия. Формула составляется по аналогии, где В1 — адрес ячейки, информация из которой учитывается при подсчете. В данном случае нумеруются подряд строки, если в ячейке из столбца В прописано слово “номер”.

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;$B$2:B2)/=SUBTOTAL(3;$B$2:B2).

Формула пригодится при использовании фильтров. Строки нумеруются по порядку, даже если фильтр убирает часть строк. Пример ситуации на скриншоте. В стандартной ситуации при фильтрации произведения Булгакова останутся с прежними номерами, т.е. 1, 3, 4. Цифра “2” будет пропущена. Однако если использовать формулу “промежуточные итоги”, нумерация будет всегда по порядку вне зависимости от того, использован фильтр или нет. Но при написании формулы нужно подставить значения соответствующих ячеек.

Автоматическая перенумерация строк при применении фильтра в EXCEL

Пусть имеется таблица с нумерацией строк (в столбце № введены числа от 1 до 16).

Если к таблице применить фильтр (нажав в строке заголовков таблицы CTRL+SHIFT+L и установив критерии отбора), то нумерация строк будет сбита (см. столбец А ).

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

Решение основано на использовании функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ() , которая «чувствует», когда строка скрыта фильтром .

Столбец с нумерацией расположим в столбце А (заголовок таблицы разместим в строке 6 ). Полезные данные, которые вводятся БЕЗ ПРОПУСКОВ, расположим в столбце В (см. Файл примера ):

Записав в ячейке A 7 формулу =ЕСЛИОШИБКА(ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;B7);A6+1;A6);1)

и скопировав ее в ячейки ниже, получим желаемый результат. Если строка скрыта, то функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() возвращает 0 (ЛОЖЬ) и счетчик номера строки не увеличивается на 1, а остается, как у предыдущей строки.

После применения фильтра нумерация строк таблицы автоматически изменится.

Как пронумеровать строки в excel по порядку при фильтре

Если Вы скачали или приобрели надстройку PLEX для Microsoft Excel и у Вас есть вопросы или пожелания — Вам сюда.

Нормализация — Plex (Пользователь Google) 19.11.2023 21:35:50
Оффтопьте на здоровье
ячейки с зада 🙂 (Бахтиёр) 23.11.2023 09:29:35
Обсуждение функционала, правил и т.д.
Некорректная работа сс. (Alien Sphinx) 09.11.2023 10:04:40
Сейчас на форуме

Сегодня отмечают день рождения kuklp ( 63 ), Юpий М ( 63 ), JRivera ( 39 ), YKA ( 40 ), VistaSV30 ( 48 ), andylu ( 42 ), guzay-puzay ( 29 ), Владимир Самойлов ( 36 ), Дамир Сартин ( 39 ), Валерий Попович ( 36 ), Роб Атиссон ( 39 )

Всего зарегистрированных пользователей: 129517
Приняло участие в обсуждении: 47761
Всего тем: 151517
Всего сообщений: 1193024
есть новые сообщения
нет новых сообщений

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

покупка

Как автоматически пронумеровать или перенумеровать после фильтра в Excel?

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

Auto number or fill sequence numbers after filter:

Kutools for Excel’s Insert Sequence Number utility can help you quickly insert unique sequential numbers into a range of cells or a filtered list, it also can insert sequence into merged cells as you need.

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!

Автоматический номер или перенумеровать после фильтра с кодом VBA

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

1. Удерживайте ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.

2. Нажмите Вставить > Модули, и вставьте следующий макрос в Окно модуля.

Код 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. Затем нажмите OK, и выбранные ячейки диапазона были последовательно перенумерованы, как показано на скриншоте ниже:

Внимание: Этот код уничтожит номера строк исходной серии, когда вы отмените функцию фильтрации. Так что сначала вам нужно сделать резервную копию ваших данных.

Автонумерация или перенумерация после фильтра с помощью Kutools for Excel

Если вы установили Kutools for Excel, с его мощным Вставить порядковый номер функция, вы можете быстро и легко вставлять порядковые номера в отфильтрованные данные.

После установки Kutools for Excel, пожалуйста, сделайте следующее:

1. Выделите ячейки, которые вы хотите изменить нумерацию для отфильтрованных данных.

2. Затем нажмите Кутулс > Вставить > Вставить порядковый номер, см. снимок экрана:

3. Затем в выскочившем Вставить порядковый номер диалоговое окно, нажмите Новая кнопку, см. снимок экрана:

4. А затем создайте номера списка последовательностей по мере необходимости в левом нижнем разделе, см. Снимок экрана:

5. После установки последовательности нажмите Добавить кнопку, и элемент последовательности был добавлен в диалоговое окно, см. снимок экрана:

6, Затем нажмите Диапазон заполнения, и отфильтрованные данные были сразу перенумерованы. Смотрите скриншот:

Демонстрация: Автоматическая нумерация или перенумерация после фильтра с помощью Kutools for Excel

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Связанная статья: