Такое понятие, как порядковый номер, встречается во многих таблицах Excel. И как же утомительно вносить каждый порядковый номер с клавиатуры. В этой статье рассмотрим, как добавить нумерацию строк в Excel, и как этот процесс можно автоматизировать.
В этой статье:
- Нумерация строк простым протягиванием
- Как добавить нумерацию строк в excel при помощи формулы
- Еще один способ добавить нумерацию строк в excel формулой
- Автоматическая нумерация при удалении строки
- Добавляем нумерацию строк в excel при помощи “умной” таблицы
- Нумерация отфильтрованных строк в Excel
Нумерация строк простым протягиванием
Это самый простой способ добавить нумерацию строк в Excel. Впрочем, этим способом пользуются не только новички. Иногда он оправдан, если таблица статическая, и не будет пополняться новыми данными.
Чтобы пронумеровать строки простым протягиванием значений, нужно пронумеровать вручную с клавиатуры две первые строки цифрами 1 и 2. Далее выделить обе ячейки и подвести курсор к нижнему правому углу до появления маленького черного крестика. И зажав левую кнопку мыши, потянуть вниз до нужного номера строки.
Как добавить нумерацию строк в excel при помощи формулы
Это один из самых универсальных способов, как можно установить нумерацию строк в Excel.
В ячейку A2 введем следующую формулу:
=СЧЁТЗ($B$2:B2)
И протянем ее вниз до конца таблицы.
Также для лучшей визуализации можно добавить проверку заполненности соседней ячейки.
=ЕСЛИ(B2<>»»;СЧЁТЗ($B$2:B2);»»)
Еще один способ добавить нумерацию строк в excel формулой
Еще один достаточно простой прием как установить нумерацию строк в excel — при помощи функции ЕСЛИ.
Как правило, порядковый номер требуется присвоить только тем строкам, в которых заполнены какие-то данные. Например, у нас есть таблица, во втором столбце которой указаны города, а в первом столбце нужно сделать автоматическую нумерацию строк. Чтобы при добавлении следующего города порядковый номер проставлялся автоматически.
=ЕСЛИ(B3<>»»;A2+1;»»)
Разберемся, как она работает.
Функция ЕСЛИ проверяет выполнение условия, и в зависимости от этого подставляет значение. В данном случае проверяется, есть ли значение в соседней ячейке и, если есть, к предыдущему порядковому номеру прибавляется единица. Если в соседней ячейке значения нет, указывается пустота “”
Такой способ имеет недостаток: если удалить строку в середине таблицы, то во всех строках ниже возникнет ошибка. Зато нумерация не сломается, если добавить строки перед таблицей.
Автоматическая нумерация при удалении строки в excel
Этот способ позволяет сохранить автоматическую нумерацию даже при удалении строк внутри таблицы excel. Он основан на использовании функции СТРОКА. Эта функция выводит номер строки, в которой она написана. Но также она может участвовать в вычислениях, что мы и будем использовать.
В том же примере установим курсор в ячейку A2 и в строке формул напишем:
=ЕСЛИ(B2<>»»;СТРОКА()-1;»»)
B2<>»» — в этом примере также функция ЕСЛИ проверяет выполнение условия: наличие данных в соседней ячейке
СТРОКА()-1 — и если соседняя ячейка не пустая, то получаем порядковый номер строки по данной формуле.
СТРОКА() — получаем порядковый номер строки, в нашем примере результат будет 2, т.к. формула находится во второй строке. И далее отнимаем единицу, чтобы получить порядковый номер 1. Поэтому если ваша таблица начинается с другой строки, то нужно отнимать не единицу, а цифру на единицу меньше номера строки.
Например, если таблица начинается с четвертой строки, то формула будет выглядеть так:
=ЕСЛИ(B4<>»»;СТРОКА()-3;»»)
У этого способа также есть особенность, которую стоит учитывать. В отличие от предыдущего способа, при удалении строки нумерация не сбивается. Однако, если добавить строки перед таблицей, то нумерация строк будет начинаться уже не с единицы.
Чтобы это исправить, нужно переписать формулу в первой строке таким образом, чтобы получить единицу (опираясь на последний пример) и протянуть ее до конца таблицы.
Добавляем нумерацию строк в excel при помощи “умной” таблицы
Чтобы максимально автоматизировать предыдущий способ и исключить ручное протягивание формулы, можно воспользоваться таким инструментом, как “умная” таблица.
Создадим “умную” таблицу. Для этого щелкнем на любой ячейке таблицы и нажмем сочетание клавиш Ctrl + T.
И теперь добавим в ячейку А2 такую же формулу, как в предыдущем примере, и нажмем Enter.
Одним из преимуществ “умных” таблиц является то, что не нужно протягивать формулы вниз, они добавляются автоматически.
Попробуем теперь добавить новое название города. Как видите, нумерация строки добавилась автоматически, и excel самостоятельно расширил границы таблицы.
Нумерация отфильтрованных строк в Excel
Зачастую требуется пронумеровать только строки, выбранные фильтром. В этом нам поможет функция АГРЕГАТ. У этой функции достаточно интересные возможности, одной из которых является нумерация только видимых строк.
Функция АГРЕГАТ имеет следующий синтаксис:
АГРЕГАТ(номер_функции; параметр; массив; [k])
В ячейке A2 введем такую формулу:
=АГРЕГАТ(3;3;$B$2:B2)
Рассмотрим на примере, что означает каждый параметр.
Номер функции — здесь нужно выбрать функцию, по которой будет работать функция АГРЕГАТ. Для нашего примера подходит функция номер 3 — СЧЁТЗ (счет количества непустых ячеек).
Параметр — это условие, по которому АГРЕГАТ будет рассчитывать функцию, указанную в предыдущем аргументе “Номер функции”.
Здесь выберем параметр номер 3, чтобы не считались скрытые строки.
$B$2:B2 — это ссылка на массив, в котором будет считаться количество ячеек при помощи выбранной функции (СЧЁТЗ). Обратите внимание, что начало массива закреплено *абсолютной ссылкой*, а конец массива не закреплен. При копировании формулы вниз массив будет увеличиваться.
Теперь добавим фильтр и отфильтруем несколько городов из списка. Нумерация строк в excel пересчиталась автоматически.
В этой статье мы познакомились с примерами, как можно добавить нумерацию строк в Excel различными способами.
Статья взята с моего сайта https://excel-analytics.ru/
