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

Автоматическая нумерация ячеек таблицы в Word и Excel

Автоматическая нумерация ячеек таблицы в Word и Excel

Разработчики Word и Excel предусмотрели несколько простых приемов для быстрой и лёгкой автоматической нумерации ячеек, даже в очень большой таблице. Сайт office.microsoft.com даёт два основных способа нумерации столбцов и строк в Эксель – «Заполнение ячеек последовательностью чисел» и «Использование функции СТРОКА» для нумерации строк. Есть ещё третий способ, прогрессивный – это использование кнопки «ЗАПОЛНИТЬ»

Способ 1 нумерации ячеек в Excel (просто и быстро)
«Заполнение ячеек последовательностью чисел»

«Заполнение ячеек последовательностью чисел» – очень простая и удобная методика автоматической нумерации ячеек в Excel. Этот способ идеально подходит для небольших таблиц. Весь процесс нумерации сводится к введению в первых двух ячейках начальных чисел ряда нумерации и последующего простого перетаскивания маркера выделения на нужное количество строк или столбцов.
Для того нужно:

1. В первой ячейке введите начальное значение для ряда чисел заполнения. Во второй ячейке введите следующее значение для ряда номеров заливки, чтобы определить шаблон заливки. Например, если вам нужна последовательность 1, 2, 3, 4, 5, введите в первые две ячейки 1 и 2. Если вам нужна последовательность 2, 4, 6, 8, введите 2 и 4, и так далее.
2. Выделите обе ячейки в блоке выделения
3. Перетащите блок выделения в нужном направлении на нужное количество ячеек. Этот метод работает во всех направлениях (вверх, вниз, влево, вправо). Соответственно, нумерация будет вестись в порядке возрастания или убывания.

Прим.
Что такое маркер выделения и как его перетащить
Маркер выделения – это выделение ячейки или ряда ячеек при помощи прямоугольной области (курсором). Чтобы перетащить маркер выделения, нужно навести курсор мыши на квадратик в правом нижнем углу выделенной ячейки или ряда ячеек. Курсор мыши при этом изменяется с белого крестика на чёрный. Выглядит это, примерно так:

Маркер в Эксель

Вы можете сделать любую таблицу в Excel красивой, перетаскивая маркер выделения в различных направлениях:

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

Прим.
Перетаскивание маркера относится к недостаткам этого метода нумерации, поскольку в больших таблицах это весьма трудоемкий процесс.

Способ 2 нумерации ячеек в Excel (очень заумно)
«Использование функции СТРОКА»

Способ, более подходящ для автоматической нумерации ячеек столбца. Принципиально, всё почти как в первом случае, с той лишь разницей, что вместо введения первых двух чисел, для задания образца значений ряда нумерации нужно ввести формулу с использованием функции «СТРОКА». Для этого, ставим курсор в ячейку, в строку формул вводим =СТРОКА(A1) и тащим наш любимый маркер вниз, на нужное количество ячеек.

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

Как в Эксель пронумеровать строки ФУНКЦИЯ СТРОКА

Способ 3 нумерации ячеек в Excel (прогрессивно)
«Использование функции ПРОГРЕССИЯ»

Метод очень хорош для больших и очень больших столов, когда утомительно носить с собой маркер.

В этом случае ничего не нужно перетаскивать. Потребуется только указать в нужной ячейке начальное число, вызвать функцию «ПРОГРЕССИЯ» и указать направление заполнения (строка или столбец), шаг заполнения (на сколько прибавлять значение) и предельное значение для заполнения (количество будущих пронумерованных ячеек). Внизу, на скриншотах показан порядок перещёлкивания кнопок в окне Excel для достижения требуемого результата.

Програссия в Эксель 1

Програссия в Эксель 2

Програссия в Эксель 3

1. После заполнения клеток прогрессией можно добавить нумерацию клеток, перетаскивая маркер (способ 1)
2. Виды прогрессий (арифметическая и геометрическая) описаны в учебнике алгебры для третьего класса.

Способ 4 нумерации ячеек в Excel (очень быстро и прогрессивно)
«Быстрое использование функции ПРОГРЕССИЯ»

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

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

1. Ввести в первую ячейку требуемую начальную цифру нумерации, например «1», «101» или «19» (без кавычек)
2. Выделить в блок первую ячейку с начальной цифрой («1», «101» или «19»), и ниже – то количество ячеек, которое нужно пронумеровать.
3. Указанным выше способом вызвать окно «ПРОГРЕССИЯ»
4. Ни на что не смотреть. Просто нажать кнопку Ok!
5. В результате – нумерация на весь выделенный блок ячеек и в заданном диапазоне.

Как быстро выделить блок ячеек в Excel
Движением курсора с зажатой Левой Клавишей Мыши (ЛКМ).

Как быстро выделить большой блок ячеек в Excel
Нужно поставить курсор в начальную ячейку, ввести в окно активной ячейки адрес-номер конечной ячейки, нажать комбинацию клавиш Shift+Enter. Расположение окна активной ячейки указано на скриншоте. При введении адреса-номера конечной ячейки обратить внимание на синтаксис (порядок написания).

Быстрое выделение в Эксель

Нумерации ячеек таблицы в Word (дёшево и сердито)

Нумеровать ячейки таблицы в Word гораздо проще. Это делается с помощью кнопки для создания нумерованного списка. Для этого вам необходимо:

1. Выделить нужные ячейки в блок
2. Нажать кнопку для создания нумерованного списка (1. 2. 3. …)
3. В результате, получим таблицу с блоком ячеек (столбцами или строками, варианты), в котором нумерация ячеек будет выполняться автоматически при их удалении или добавлении.

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

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

Автонумерация строк и столбцов в базе данных Excel

Задача: Вы хотите пронумеровать записи (строки) и заголовки столбцов в базе данных Excel. Позже, при использовании автофильтра, некоторые записи будут скрыты, но номера записей будут пересчитаны, и в нумерации не будет пробелов.

В базе данных (рис. 1) записи и столбцы пронумерованы. Применив автофильтр в столбце G (> 335) и скрыв два столбца (D и F), пробелы в автонумерации не появились. 2).

Примечание: Поскольку нумерация во второй строке основана на формулах, после скрытия столбцов нажмите F9, чтобы пересчитать формулы (скрытие столбцов само по себе не является действием, вызывающим пересчет формул).

Рисунок 1. Полная база данных

Скачайте заметку в формате Word или pdf и примеры в формате Excel.

Рисунок 2. Ваша цель — иметь метки строк/столбцов, которые нумеруют только видимые строки/столбцы

В качестве первого шага назовите базу данных в диапазоне A2:G13 (рис. 3). [1]

Рис. 3: Создание наименования

Это позволит исключить строку 1 из выбора по умолчанию при применении автофильтра. Также следует добавить пробел сразу после последней записи (в нашем примере это ячейка A14). Существует досадная ошибка Excel, которая показывает последнюю запись (строку) независимо от того, соответствует она критериям фильтра или нет. На рис. в ячейке A14 ничего нет. Когда применяется фильтр по столбцу G (больше 345), отображается последняя запись со значением 342. На рис. В ячейке A14 в 4b имеется пустое место. Аналогичный фильтр не показывает последнюю строку.

Рисунок 4: Добавьте пробел, чтобы фильтр корректно работал для последней записи в диапазоне

Заполните ячейку A3 следующей формулой: =СУММЕСЛИ. Итого 3;B$3:B3 и скопируйте ее в диапазон A4:A13. Здесь две ссылки, первая — абсолютная, а вторая — относительная. Поэтому при копировании вдоль столбца формула будет меняться. Ячейка A13, например, будет иметь формулу: =INTERMEDIATE. ИТОГИ(3;B$3:B13).

В ячейку A2 можно ввести следующую формулу: =If(«ширина»;A1)=0;0;1). В ячейку B2 введите формулу: =If(ФИГУРА(«ширина » ;B1)=0;0;MAX($A2:A2)+1) и скопируйте ее по радиусу в ячейки C2:G2. Ссылка на первый столбец является абсолютной, а на второй — относительной. В G2 у нас есть формула: =If(FACE(«width» ;G1)=0;0;MAX($A2:F2)+1).

Вот как работают формулы. Для начала я хотел бы поговорить об автоматической нумерации записей. Синтаксис функции PROMOTE. ИТОГИ(номер_функции;ссылка). 3-й тип соответствует СЧЕТ. Формула в ячейке A3 использует тот факт, что функция СУММЕСЛИ. Функция СУММЕСЛИ исключает из расчета скрытые ячейки.

Рассмотрим формулу в A10: =TOTALS.TOTALS(3;B$3:B12). Общее количество значений текста в диапазоне B$3:B12 равно восьми, но видимыми являются только пять значений. Поэтому формула возвращает значение 5, которое является порядковым номером видимых строк.

Предупреждение. К сожалению, функция TOTALS.TOTALS работает только при вычислении итогов по столбцам. Если вы попытаетесь применить его к ряду 2, это приведет к неудаче. Вот что говорится в справке Excel об этой функции Функция ИТОГИ.ИТОГИ применяется к столбцам данных или вертикальным наборам данных. Он не предназначен для данных строк или горизонтальных наборов данных. Поэтому при определении промежуточных итогов для горизонтального набора данных скрытие столбца не повлияет на результат. Однако это влияет на скрытие строки при суммировании промежуточных итогов для вертикального набора данных.

Если вы хотите пронумеровать столбцы автоматически, используйте функцию CHAIN. Синтаксис этой функции — FEATURE(info_type;[ref]). Если указать info_type » width «, то функция возвращает ширину столбца левой верхней ячейки аргумента ссылки.

Формула в ячейке A2 = IF(FEATHER(«width » ; «A1)=0;0;1) возвращает 0, если столбец скрыт, и 1 в противном случае. Формула в D2 = IF(FEATHER(«width » ; «D1)=0;0;MAX($A2:C2)+1). Поскольку FACE(«width » ;D1)=0 (столбец D скрыт; см. рис. 2), формула возвращает 0. Аналогично, формула возвращает 0 в ячейке E2 (столбец также скрыт; см. Рисунок 2). Формула в следующей (теперь видимой) ячейке — F2: =If(«width » ; «F1)=0;0;MAX($A2:E2)+1). Поскольку FACE(«width » ; «F1)>0, =If() возвращает формулу MAX($A2:E2)+1. Фрагмент MAX() возвращает наибольшее значение, соответствующее последней видимой строке. В нашем примере на рисунке 2 это значение 3 для столбца C. Следовательно, значение в F2 = 4.

Можно создать формулы для автонумерации записей (строк) и заголовков столбцов в базе данных, где применяется автофильтр и некоторые столбцы скрыты.

[1] Причина такого выбора неясна из более позднего текста Жалена — примечание Багузина.