Посмотреть видео - 7 быстрых и простых способов пронумеровать строки в Excel
При работе с Excel есть небольшие задачи, которые нужно выполнять довольно часто. Знание «правильного пути» может сэкономить вам много времени.
Одна из таких простых (но часто необходимых) задач - пронумеровать строки набора данных в Excel (также называемые серийными номерами в наборе данных).
Теперь, если вы думаете, что один из способов - просто ввести серийный номер вручную, что ж, вы правы!
Но это не лучший способ сделать это.
Представьте, что у вас есть сотни или тысячи строк, для которых вам нужно ввести номер строки. Это было бы утомительно и совершенно ненужно.
В Excel есть много способов пронумеровать строки, и в этом уроке я расскажу о некоторых способах, которые я рекомендую и часто использую.
Конечно, их будет больше, и я буду ждать - с кофе - в области комментариев, чтобы услышать от вас об этом.
Как пронумеровать строки в Excel
Лучший способ пронумеровать строки в Excel будет зависеть от типа имеющегося у вас набора данных.
Например, у вас может быть непрерывный набор данных, который начинается с строки 1, или набор данных, который начинается с другой строки. Или у вас может быть набор данных, в котором есть несколько пустых строк, и вы хотите пронумеровать только заполненные строки.
Вы можете выбрать любой из методов, которые работают на основе вашего набора данных.
1] Использование маркера заливки
Маркер заполнения определяет узор из нескольких заполненных ячеек и может легко использоваться для быстрого заполнения всего столбца.
Предположим, у вас есть набор данных, как показано ниже:
Вот шаги, чтобы быстро пронумеровать строки с помощью маркера заполнения:
- Введите 1 в ячейку A2 и 2 в ячейку A3.
- Выделите обе ячейки (A2 и A3).
- Обратите внимание, что в правом нижнем углу выделения будет небольшой квадрат.
- Наведите курсор на этот квадрат, и вы заметите, что курсор изменится на значок плюса.
- Дважды щелкните квадрат маркера заполнения (пока курсор находится в форме значка плюса), и он автоматически заполнит все ячейки до конца набора данных.
Обратите внимание, что ручка заливки автоматически определяет узор и заполняет оставшиеся ячейки этим узором. В этом случае закономерность заключалась в том, что числа увеличивались на 1.
Если в наборе данных есть пустая строка, дескриптор заполнения будет работать только до последней непрерывной непустой строки.
Также обратите внимание, что если у вас нет данных в соседнем столбце, двойной щелчок маркера заполнения не сработает. Однако вы можете поместить курсор на маркер заливки, удерживая правую клавишу мыши и потянуть вниз. Он заполнит ячейки, покрытые перетаскиванием курсора.
2] Использование серии заливки
В то время как Fill Handle - это быстрый способ пронумеровать строки в Excel, Fill Series дает вам гораздо больше контроля над тем, как вводятся числа.
Предположим, у вас есть набор данных, как показано ниже:
Вот шаги, чтобы использовать серию заливки для нумерации строк в Excel:
- Введите 1 в ячейку A2.
- Перейдите на вкладку "Главная".
- В группе редактирования щелкните раскрывающееся меню «Заливка».
- В раскрывающемся списке выберите «Серия…».
- В диалоговом окне «Серии» выберите «Столбцы» в параметрах «Серии в».
- Укажите значение Stop. В этом случае, поскольку у нас есть 26 записей, мы можем ввести 26. Если вы не введете никакого значения, Fill Series не будет работать.
- Щелкните ОК.
Это позволит мгновенно пронумеровать строки от 1 до 26.
Использование «Серии заливки» может быть полезно, когда вы начинаете с ввода номеров строк. В отличие от Fill Handle, он не требует, чтобы соседние столбцы уже были заполнены.
Даже если у вас ничего нет на листе, серия заливок все равно будет работать.
Примечание. Если в середине набора данных есть пустые строки, ряд заполнения все равно будет заполнять число для этой строки.
3] Использование функции ROW
Вы также можете использовать функции Excel для нумерации строк в Excel.
В описанных выше методах Fill Handle и Fill Series введенный серийный номер является статическим значением. Это означает, что если вы переместите строку (или вырежете и вставите ее в другое место в наборе данных), нумерация строк не изменится соответствующим образом.
Этот недостаток можно устранить с помощью формул в Excel.
Вы можете использовать функцию СТРОКА, чтобы получить нумерацию строк в Excel.
Чтобы получить нумерацию строк с помощью функции СТРОКА, введите следующую формулу в первую ячейку и скопируйте для всех остальных ячеек:
= СТРОКА () - 1
Функция ROW () выдает номер текущей строки. Итак, я вычел из него 1, начиная со второго ряда. Если ваши данные начинаются с 5-й строки, вам нужно использовать формулу = СТРОКА () - 4.
Лучшее в использовании функции СТРОКА заключается в том, что она не испортит нумерацию, если вы удалите строку в своем наборе данных.
Поскольку функция СТРОКА не ссылается на какую-либо ячейку, она автоматически (или, я бы сказал, AutoMagically) отрегулирует, чтобы дать вам правильный номер строки. Что-то вроде того, что показано ниже:
Обратите внимание, что как только я удаляю строку, номера строк автоматически обновляются.
Опять же, при этом не будут учитываться пустые записи в наборе данных. Если у вас есть пустые строки, он все равно будет отображать номер строки.
Вы можете использовать следующую формулу, чтобы скрыть номер строки для пустых строк, но она все равно не изменит номера строк (так, чтобы следующий номер строки был назначен следующей заполненной строке).
ЕСЛИ (ISBLANK (B2), "", ROW () - 1)
4] Использование функции COUNTA
Если вы хотите пронумеровать строки таким образом, чтобы только те, которые были заполнены, получали порядковый номер, то этот метод - лучший вариант.
Он использует функцию COUNTA, которая подсчитывает количество непустых ячеек в диапазоне.
Предположим, у вас есть набор данных, как показано ниже:
Обратите внимание, что в показанном выше наборе данных есть пустые строки.
Вот формула, которая будет нумеровать строки без нумерации пустых строк.
= ЕСЛИ (ISBLANK (B2); ""; COUNTA ($ B $ 2: B2))
Функция ЕСЛИ проверяет, пуста ли соседняя ячейка в столбце B. Если он пуст, он возвращает пустое значение, а если нет, он возвращает количество всех заполненных ячеек до этой ячейки.
5] Использование SUBTOTAL для отфильтрованных данных
Иногда у вас может быть огромный набор данных, в котором вы хотите отфильтровать данные, а затем скопировать и вставить отфильтрованные данные на отдельный лист.
Если вы воспользуетесь каким-либо из методов, показанных выше, вы заметите, что номера строк остались прежними. Это означает, что при копировании отфильтрованных данных вам придется обновить нумерацию строк.
В таких случаях функция ПРОМЕЖУТОЧНЫЙ ИТОГ может автоматически обновлять номера строк. Даже при фильтрации набора данных номера строк останутся неизменными.
Позвольте мне показать вам, как именно это работает, на примере.
Предположим, у вас есть набор данных, как показано ниже:
Если я отфильтрую эти данные на основе продаж продукта А, вы получите что-то, как показано ниже:
Обратите внимание, что серийные номера в столбце A также фильтруются. Итак, теперь вы видите только номера видимых строк.
Хотя это ожидаемое поведение, если вы хотите получить последовательную нумерацию строк - чтобы вы могли просто скопировать и вставить эти данные в другое место - вы можете использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ.
Вот функция ПРОМЕЖУТОЧНЫЙ ИТОГ, которая гарантирует, что даже отфильтрованные данные имеют непрерывную нумерацию строк.
= ПРОМЕЖУТОЧНЫЙ ИТОГ (3; $ B $ 2: B2)
Цифра 3 в функции ПРОМЕЖУТОЧНЫЙ ИТОГ указывает на использование функции СЧЁТ. Второй аргумент - это диапазон, к которому применяется функция СЧЁТ.
Преимущество функции ПРОМЕЖУТОЧНЫЙ ИТОГ заключается в том, что она динамически обновляется при фильтрации данных (как показано ниже):
Обратите внимание, что даже когда данные фильтруются, нумерация строк обновляется и остается непрерывной.
6] Создание таблицы Excel
Таблица Excel - отличный инструмент, который необходимо использовать при работе с табличными данными. Это значительно упрощает управление данными и их использование.
Это также мой любимый метод из всех техник, показанных в этом уроке.
Позвольте мне сначала показать вам, как правильно пронумеровать строки с помощью таблицы Excel:
- Выберите весь набор данных.
- Перейдите на вкладку "Вставка".
- Щелкните значок Таблица (вы также можете использовать сочетание клавиш Control + T).
- В диалоговом окне «Создание таблицы» убедитесь, что диапазон указан правильно.
- Щелкните ОК. Это преобразует ваши табличные данные в таблицу Excel.
- В ячейке A2 введите следующую формулу. Обратите внимание, что как только вы вводите формулу, она автоматически заполняет все ячейки в этом столбце (подробнее о вычисляемых столбцах вы можете узнать здесь).
= СТРОКА () - СТРОКА (Таблица2 [#Headers])
Обратите внимание, что в приведенной выше формуле я использовал Таблица 2, так как это имя моей таблицы Excel. Вы можете заменить Table2 именем имеющейся у вас таблицы.
Использование таблицы Excel при нумерации строк в Excel дает некоторые дополнительные преимущества:
- Поскольку таблица Excel автоматически вставляет формулу во весь столбец, она работает, когда вы вставляете новую строку в таблицу. Это означает, что когда вы вставляете / удаляете строки в таблице Excel, нумерация строк автоматически обновляется (как показано ниже).
- Если вы добавите больше строк к данным, таблица Excel автоматически расширится, чтобы включить эти данные как часть таблицы. А поскольку формулы автоматически обновляются в вычисляемых столбцах, он вставляет номер строки для вновь вставленной строки (как показано ниже).
7] Добавление 1 к предыдущему номеру строки
Это простой метод, который работает.
Идея состоит в том, чтобы добавить 1 к номеру предыдущей строки (число в ячейке выше). Это гарантирует, что последующие строки получат число, увеличивающееся на 1.
Предположим, у вас есть набор данных, как показано ниже:
Вот шаги, чтобы ввести номера строк с помощью этого метода:
- В ячейке в первой строке введите 1 вручную. В данном случае это ячейка A2.
- В ячейке A3 введите формулу = A2 + 1.
- Скопируйте и вставьте формулу для всех ячеек в столбце.
Вышеупомянутые шаги позволят ввести серийные номера во все ячейки столбца. Если есть пустые строки, для них все равно будет вставлен номер строки.
Также обратите внимание, что если вы вставляете новую строку, номер строки не обновляется. В случае удаления строки во всех ячейках под удаленной строкой будет отображаться ошибка ссылки.
Это несколько быстрых способов, которые вы можете использовать для вставки серийных номеров в табличные данные в Excel.
Если вы используете какой-либо другой метод, поделитесь им со мной в разделе комментариев.
