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

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

Чтобы пронумеровать по порядку строки, полученные после применения фильтра, необходимо:

1. 1. В первую ячейку отфильтрованного списка внести функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ, выбрать 103-й номер функции (СЧЁТЗ) кликнув 2 раза по номеру

2. Нажать fx в строке формул и указать в Ссылка1 соседнюю ячейку в этой же строке (в примере – наименования товаров).

3. Ввести «:» и еще раз указать эту же ячейку, создав диапазон из одной ячейки.

4. Первую границу диапазона фиксируем (выделяем адрес и нажимаем на клавиатуре F4)

5. Нажимаем «OK» и протягиваем формулу до конца списка.

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

Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)

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

Как заполнить порядковые номера, пропустить скрытые строки в Excel?

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

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!

Заполните порядковые номера, пропустите скрытые строки с помощью формулы

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

1. Введите эту формулу: = ПРОМЕЖУТОЧНЫЙ ИТОГ (103; B $ 3: B3) (B3 — это видимая ячейка, соответствующая ячейке, с которой вы хотите начать заполнение порядкового номера) в первую ячейку, в которую вы хотите заполнить порядковые номера, A3, для этого экземпляра, а затем нажмите Enter key вы получите цифру 1, см. снимок экрана:

2. Затем выберите ячейку A3 и перетащите маркер заполнения вниз к ячейкам, которые вы хотите заполнить порядковыми номерами, см. Снимок экрана:

Заполните порядковые номера, пропустите скрытые строки с кодом VBA

Следующий код VBA также может помочь вам заполнить порядковые номера только видимыми ячейками, пожалуйста, сделайте следующее:

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

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

Код VBA: заполните порядковые номера, пропустите скрытые строки:

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

4, Затем нажмите OK кнопки, порядковые номера были заполнены только видимыми ячейками.

Заполните порядковые номера, пропустите скрытые строки с помощью Kutools for Excel

Только два вышеуказанных метода могут помочь вам заполнить порядковые номера в видимых ячейках, если вы хотите заполнить последовательность как AA-0001-BB, AA-0002-BB, AA-0003-BB… , вышеуказанные методы не будут работать. Но, пожалуйста, не беспокойтесь о том, Kutools for ExcelАвтора Вставить порядковый номер Утилита поможет вам закончить это быстро и легко.

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

1. Выделите ячейки, которые вы хотите заполнить строками последовательности.

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

3. В развернутом диалоговом окне укажите необходимые настройки в левом нижнем разделе, в то же время вы можете предварительно просмотреть последовательность в правой нижней панели, см. Снимок экрана:

4. После завершения настройки нажмите Добавить кнопку, чтобы сохранить этот новый элемент в поле списка выше, см. снимок экрана:

6. Затем выберите созданный элемент и нажмите Диапазон заполнения , строки последовательности были заполнены только видимыми ячейками, см. снимок экрана:

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

Автоматическая нумерация при добавлении строк в MS EXCEL

​Смотрите также​Volodshan​: придумай, как запустить​Волчонок​ данных «жестких» переносов​ не сложно подсчитать​ первой строке формула​

​Но я наверное​ подготовленном шаблоне уже​Заранее не известно​ такая задача тоже​ разница — ПРОМЕЖУТОЧНЫЕ.ИТОГИ​нужно пронумеровать заполненные​). Полезные данные, которые​ до 16).​ENTER​ этого выделите любую​При добавлении в таблицу​

​: Вспомнил. ​ такой макрос:​: Там вообще все​ строк. Извини, но​ количество строк формулой​

• ​ осталась, а в​ непонятно объясняю. Формулы​ должна быть задана​ сколько строк будет​ часто бывает. ​ в каждой ячейке​​ строки (исключая скрытые),​​ вводятся БЕЗ ПРОПУСКОВ,​Если к таблице применить​
• ​(Ввод): столбец должен​ ячейку в таблице​ новых строк приходится​Смотри — автор​Sub макрос()​ просто можно сделать​ решения у меня​ СТРОКА. Может быть​
• ​ последующих стерты?​ верные и первая​ какая-то формула, которая​ в таблице. Подскажите,​Garik007​ считаются не по​ и что бы​
• ​ расположим в столбце​ фильтр(нажав в строке​​ автоматически заполниться формулой​​ и нажмите​ вручную восстанавливать нумерацию​​ «Igor67» — ситуация​​Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value​ пустую страницу в​ пока нет. В​

​ зная количество строк​разберитесь как происходит​ и вторая. Более​ динамически заполняет новообразовавшийся​ пожалуйста, как можно​: Добрый день, можно​ всем ячейкам выше,​ при добавлении и​В​​ заголовков таблицы​​ до конца таблицы.​

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

​CTRL+A​ строк. Использование таблиц​ похожая «http://www.planetaexcel.ru/forum.php?thread_id=2863″​ = 1​ ячейку B1 внести​ подобных ситуациях (квалификация​ можно какой-нибудь формулой​

​ выгрузка​ того тут можно​ диапазон, где N​ пронумеровать строки автоматически​ ли сделать в​

​ а только по​ удалении строк нумерация​(см. Файл примера):​​CTRL+SHIFT+L​​Теперь при добавлении/ удалении​- будет выделена​ в формате Excel​Волчонок​​Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).FormulaR1C1​​ маркер для выгрузки​

​ -«начинающий») ухожу в​ или макросом автоматически​Волчонок​ и совсем простые​ строк (так как​

​ сколько бы их​ подобной нумерации, чтобы​ этой конкретной строке.​ пересчитывалась автоматически​

​Записав в ячейке​и установив критерии​​ строк в середине​​ Текущая область;​ 2007 позволяет автоматизировать​​: Ага, спасибо что-то​​ = «=R[-1]C+1″​ (ничего скрытого и​ Access — счетчик​​ заполнить номера?​​: При выгрузке происходит​

​ формулы использовать. ​​ это будет результатом​​ ни было?​
​ после каждой цифры​

​ При сколько-нибудь значительных​Пронумеровать где? Файлик​A7​ отбора), то нумерация​ или в конце​через пункт меню Вставка/​ этот процесс.​ подобное. Как сделаю​Range(Cells(Rows.Count, 1).End(xlUp), Cells(Cells(Rows.Count,​ критичного там нет).​ там не подводил.​

​Volodshan​ вставка строк в​Строки нумероваться должны​

Нумерация заполненных строк (Формулы/Formulas)

​ запроса).​​Может глупый вопрос. ​ стояла точка? Если​
​ объемах . Но​ положите сюда​формулу​ строк будет сбита​ таблицы нумерация строк​ Таблицы/ Таблица конвертируем​
​Пусть имеется обычная таблица​ отпишусь здесь что​

​ 2).End(xlUp).Row, 1)).FillDown​​ То что формула​

​Guest​​: Волчонок! Логика, как​ то место где​ автоматически. Пользователь не​Во вложении: желтым-сюда​ но при поиске​ значения скрепить с​ на небльших -​

​ArkaIIIa​​=ЕСЛИОШИБКА(ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;B7);A6+1;A6);1)​​ (см. столбец​​ будет проставляться автоматически,​

​ таблицу в формат​​ (диапазон ячеек с​ именно мне помогло.​End Sub​ себя не протянет​: т.е строки вставляются​ понимаю, такая: подсчитать​ указан маркер.​ должен делать никаких​ будет вставлена таблица,​ натыкаюсь на что-то​ точкой то получается​ формула Аркадия лучше​: Так?​и скопировав ее в​
​А​ т.к. при вставке​ Excel 2007. В​ данными), состоящая из​Всем спасибо! :)​Волчонок​

​ я тоже поняла.​​ целиком и изменить​ количество значений по​Формулы не стерты​ действий (выделил/протянул итд)​ красным — здесь​ сложное и все​ каждый последующий номер​ — стабильнее.​ArkaIIIa​ ячейки ниже, получим​

​).​​ / удалении строк​ диалоговом окне не​ трех столбцов с​ советы то ценные,​: Слэн, я создала​ Никаких жестких условий​ это невозможно?​ полю N (в​ — они сместились​Может это невозможно?​

​ должен находиться №​​ не о том. ​ опять начинается с​При добавлении строк​: При удалении -​

​ желаемый результат. Если​​Для того, чтобы сохранить​

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

​ копируется формула в​​ забудем поставить галочку​ заголовками №, Наименование,​ даже если к​ новый макрос, скопировала​ нет. Просто грубо​тогда запуск макроса​

​ примере «С» -​ вниз​ :(​ п\п строки​AMC​ единицы.​ нужно протянуть формулу​

​ будет пересчитываться автоматически.​ строка скрыта, то​ нумерацию строк, используем​ столбце​ «Таблица с заголовками».​

​ Цена (в строке​​ конкретно этой ситуации​ в него код​

​ говоря надо к​​ по событию изменения​ «Дата регистрации поручения»),​Волчонок​На всякий случай​Guest​: может быть так​Pelena​ из любой ячейки​ При добавлении -​ функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() возвращает​ вместо последовательности констант​А​ Нажмите ОК;​ 1). Строки таблицы​ не все применимы​ и запустила. Ничего​ этой табличке вставить​ листа. ​ а затем «протянуть»​

​: Может есть макрос​ я во вложение​: Вложение​ ?​: Можно попробовать сделать​

​ выше до любой​​ вы создаете абсолютно​

​ 0 (ЛОЖЬ) и​​ (от 1 до​.​убеждаемся, что включена опция​ заполнены данными (кроме​

​Волчонок​​ не вышло. Иогда​ впереди столбец с​макрос или вставляет​

​ формулу на такое​​ какой-то или какие-нибудь​ добавлю результат что​
​слэн​Волчонок​ форматом ячейки. Если​ ячейки ниже, чем​ новую пустую строку,​ счетчик номера строки​ 16) — формулу.​

​При применении фильтра строки​ Автозамены «Создавать вычисляемые​ столбца №, который​: С макросами к​

​ я назначила сочетание​ порядковым номером. Делается​

​ ту же формулу​ же количество ячеек​ динамические диапазоны надо​ получилось​

​: лист2 и лст2(2)​: Нет эта формула​ не разберётесь, создайте​ добавленная строка​

​ нужно будет протянуть​ не увеличивается на​Решение основано на использовании​

​ скрываются целиком. Если​​ столбцы, заполнив таблицы​ будет содержать номера​ сожалению некогда было​ клавиш для запуска​ это один раз​
​ или сам нумерует​ в поле «А»​

​ задавать. Я попробовала,​​Желтым — строки,​ — попроще и​ маленько не то.​ новую тему​
​Сашико​ на неё формулу.​ 1, а остается,​

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

​ — «№ п/п».​ но не поняла​ которые были автоматически​

​ посложнее(без разрывов и​​ Проблема в том,​Garik007​: Модифицированный вариант тов.​GElenka​ как у предыдущей​ «чувствует», когда строка​ нумерация строк, то​ Параметры Excel/ Правописание/​Для организации автоматической нумерации​

​ нашла другой способ​​ не вышло.Это правильно?​ не используется, потому​ значения.​ Только как -​ как применить такой​ выгружены в первоначальный​ с оными)​ что при выгрузке​: Спасибо, помогло.​ Бороды (чтобы отсутствовала​:​ строки.​ скрыта фильтром.​ она будет сбита.​ Параметры автозамены…/вкладка Автоформат​ в случае добавления​ выгрузки таблицы отдельными​ Или требуется еще​ и не критично​при использовании формул​ сама себя она​ диапазон.​ шаблон.​слэн​ происходит вставка таблицы​Волчонок​ зависимость от значения​

​ArkaIIIa​​После применения фильтра нумерация​Столбец с нумерацией расположим​ Но, есть способ​
​ при вводе);​ или удаления строк​ столбцами, в этом​

​ дописывать код? В​ какой сложности будет​ можно в дальнейшем​ не «протянет»? И​. Меня не покидает​
​Зеленым — тут​: а в общем-то​ в шаблон. И​: Вопрос такой: При​ чекбокса «показать нулевые​, Спасибо​ строк таблицы автоматически​

​ в столбце​​ обойти эту проблему.​под заголовком столбца №​ используем следующий алгоритм​ случае первые две​ Хэлпе только общие​ формула. Задача только​ удалять или сортировать​ еще: скрытое (без​ чувство, что все​ должны были появиться​ АМС уже указал..​ не известно заранее​ выгрузке в Excel​ значения») + 2​_Boroda_​ изменится.​А​Пусть имеется таблица с​ (ячейка​ (см. файл примера):​ формулы подходят и​ сведенья. ​ в том, чтобы​ строки(особенно если усложнить​ «шапки») поле «В»​

​ гораздо проще​​ порядковые номера строк​ и это правильно​
​ сколько в ней​
​ данных из Docsvision.​ варианта сплошной нумерации​
​: Еще вариант. Он​GElenka​
​(заголовок таблицы разместим​ нумерацией строк (в​
​А2​

​выделим всю таблицу (все​​ работают идеально. Еще​. Я никогда не​ нумерацию эту как​ формулу, включив в​ — так и​Волчонок​слэн​Guest​ строк, но они​ Перед этим формируется​ без учета отфильтрованных/неотфильтрованных​ очень похож внешне,​: Здравствуйте, помогите, пожалуйста​

​ в строке​ столбце № введены​) введем формулу =СТРОКА()-СТРОКА($A$1)​

​ 3 столбца вместе​​ раз всем большое​
​ имела дела с​ то реализовать.​ нее проверку на​

​ должно быть? И​​: Кстати, может будет​: ну и почему​: слэн, АМС спасибо​ должны автоматически пронумероваться.​

​ шаблон в который​ значений. Мало ли,​ но есть большая​ решить такую задачку:​6​

​ числа от 1​​ и нажмем клавишу​ с заголовками). Для​ спасибо!​ макросами​слэн​ первое значение)​ нет ли в​ полезно. после выгрузки​ у вас в​ вам большое. ​ То есть в​

6 простых способов сделать автоматическую нумерацию в Excel — инструкция и видео

Быстро пронумеровать список фамилий, расставить людей по рейтингу и присвоить участникам викторины порядковый номер — задачи, которые можно автоматизировать. Зачем проставлять номера каждому участнику списка отдельно, если можно воспользоваться силой Excel и сделать всё быстро, и без мучений.

Способ 1 — простая нумерация

Ставим в первую ячейку цифру 1, во вторую — цифру 2. Затем выделяем обе ячейки и тянем вниз, чтобы заполнить всё до конца.

Если строк много, можно воспользоваться быстрым способом. Наводим мышку на правый нижний угол ячейки и делаем двойной клик по крестику — это сразу заполнит весь диапазон до последней строки.

Как это работает. Так как в первую ячейку мы поставили единицу, а во вторую — двойку, Excel счёл это как последовательность. Скорее всего, следующее число будет 3, за ним 4, а потом 5 и так далее.

Способ 2 — автоматическая последовательная нумерация

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

Далее точно так же тянем всё вниз и заполняем диапазон.

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

Как это работает. Наша формула образно звучит так: «Возьми ячейку над текущей и увеличь результат на единицу». А так как формула тянется вниз, то всё значение каждой ячейки увеличивается на один — и происходит автоматическая нумерация.

Способ 3 — через функцию =СТРОКА()

Если ваш список увеличивается строго вниз, то способ через функцию СТРОКА будет оптимальным.

Функция =СТРОКА() возвращает порядковый номер строки для текущей ячейки, начиная со строки №1

Зная это, мы можем найти закономерность и здесь. Введём формулу в ячейку E2:

Как видите, результат работы функции — это число 2. Строка вторая, значит в результате будет 2.

Чтобы нам получить правильный результат, нужно подкорректировать формулу и уменьшить результат на количество строк шапки, то есть на одну строку. Для этого просто вычитаем единицу из самой функции.

Способ удобен тем, что если нам нужно пронумеровать каждый элемент списка отдельно. Мы можем скопировать формулу в любое место таблицы и получить мгновенный результат.

Как это работает. Механика функции простыми словами: «у ячейки с таким адресом вот такой номер строки». И нам, чтобы начать список с первого пункта, нужно этот номер строки уменьшить на количество строк шапки нашей таблицы. Так мы и получаем желаемый результат.

Способ 4 — нумерация через инструмент «Прогрессия»

Нумеровать можно не только формулами и арифметическими действиями, но и встроенными инструментами. Нам нужен инструмент «Прогрессия».

Арифметическая прогрессия — это последовательность чисел, где каждое следующее число больше предыдущего на какой-то шаг

В нашем случае прогрессия самая простая — мы увеличиваем каждое число на единицу, прямо как в способе 2, но только без лишних действий руками.

Вводим в ячейку E1 число 1, выделяем эту единицу и весь диапазон, который нужно пронумеровать, а затем переходим на вкладку «Главная» → область «Редактирование» → кнопка «Заполнить» → «Прогрессия…»

Открывается диалоговое окно, в котором мы выбираем расположение «По столбцам», поскольку нумеруем вниз. Затем выбираем тип «Арифметическая» и ставим шаг — 1.

Если мы знаем, сколько всего людей в списке, то мы можем поставить предельное значение, например, 25, и тогда инструмент остановит свою работу, как только дойдёт до определённого числа. Нажимаем «Ок» и видим как Excel делает автоматическую нумерацию строк.

Как это работает. Прогрессии могут быть разными, но в случае с арифметическими — они ведут себя достаточно предсказуемо, просто увеличивая наше число на какое-то одинаковое число — шаг. В нашем случае шаг равняется единице, что полностью решает нашу задачу.

Способ 5 — функция МАКС()

Иногда бывает, что список людей идёт с разрывами и нам нужно продолжать нумерацию после пропуска. В этом случае нам подойдёт способ через функцию МАКС и работает он следующим образом.

Функция МАКС ищет наибольшее число в указанном диапазоне

В первую ячейку ставим число 1, а во вторую вводим формулу: =МАКС($E$2:E2)+1

Начало диапазона мы указали строго от $E$2, то есть первая граница не меняется ни при каких условиях. Концом диапазона будет последняя ячейка после протягивания. При протягивании такой формуле в строке 26 мы получим формулу: =МАКС($E$2:E25)+1

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

При этом формулу можно просто скопировать и вставить в любом месте сверху и снизу нашего списка нумерации и все числа автоматически пересчитаются.

Как это работает. Так как диапазон увеличивается на единицу =МАКС() + 1, то у нас каждая ячейка будет больше другой и функция МАКС будет выбирать её.

Способ 6 — адаптивная автоматическая нумерация через =СЧЁТЗ() и =ЕСЛИ()

Если в предыдущем способе нам всё ещё нужно работать с ячейками вручную, то в этом способе мы всё автоматизируем ещё больше. Нам понадобится комбинация функций СЧЁТЗ и ЕСЛИ, чтобы фильтрация происходила по условию.

Функция СЧЁТЗ считает количество заполненных ячеек в диапазоне
Функция ЕСЛИ проверяет ячейку на соответствие какому-то условию. Если условие истинно, выполняется одно действие. Если условие ложно, выполняется другое действие

Итак, формула: =ЕСЛИ(B2=»»;»»;СЧЁТЗ($B$2:B2))

Вставляем формулу в ячейку B2, тянем вниз и формула автоматически пропускает пустые строки. Заметьте, что в части формулы, где СЧЁТЗ, мы закрепили первую часть, потому что мы не хотим, чтобы диапазон состоял из одной строки. Нам нужно, чтобы он постоянно увеличивался.

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

Как это работает. Функция ЕСЛИ отвечает за такое условие: если ячейка B2, где у нас лежит фамилия, пустая, то так и оставить её пустой, иначе — посчитать общее количество заполненных ячеек.

Мы разобрались, как создать автоматическую нумерацию в Excel. Эти же способы работают и в гугл-таблицах. Желаем приятного применения! Пробуйте и делитесь впечатлениями.

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