Перегляд відео - 7 швидких і простих способів нумерації рядків у Excel
Під час роботи з Excel є деякі невеликі завдання, які потрібно виконувати досить часто. Знання «правильного шляху» може заощадити багато часу.
Одним з таких простих (але часто необхідних) завдань є нумерація рядків набору даних в Excel (також називається серійними номерами в наборі даних).
Тепер, якщо ви думаєте, що один із способів - це просто ввести цей серійний номер вручну, - ви праві!
Але це не найкращий спосіб це зробити.
Уявіть, що у вас є сотні або тисячі рядків, для яких вам потрібно ввести номер рядка. Це було б нудно - і зовсім непотрібно.
Існує багато способів нумерації рядків у Excel, і в цьому підручнику я збираюся поділитися деякими способами, які я рекомендую та часто використовую.
Звичайно, було б більше, і я буду чекати - з кавою - у зоні коментарів, щоб почути від вас про це.
Як нумерувати рядки в Excel
Найкращий спосіб нумерації рядків у Excel залежатиме від типу набору даних, який у вас є.
Наприклад, у вас може бути безперервний набір даних, що починається з рядка 1, або набір даних, що починається з іншого рядка. Або, можливо, у вас є набір даних, який містить кілька порожніх рядків, і вам потрібно лише пронумерувати заповнені рядки.
Ви можете вибрати будь -який із методів, які працюють на основі вашого набору даних.
1] За допомогою ручки заповнення
Маркер заповнення ідентифікує візерунок з кількох заповнених клітинок і може бути легко використаний для швидкого заповнення всього стовпця.
Припустимо, у вас є набір даних, як показано нижче:
Ось кроки для швидкої нумерації рядків за допомогою ручки заповнення:
- Введіть 1 у клітинку A2 і 2 у клітинку A3.
- Виділіть обидві клітинки (A2 та A3).
- Зауважте, що в нижньому правому куті виділення буде невеликий квадрат.
- Наведіть курсор на цей квадрат, і ви помітите, що курсор зміниться на значок плюса.
- Двічі клацніть по квадрату ручки заповнення (поки курсор у формі значка плюс), і він автоматично заповнить усі клітинки до кінця набору даних.
Зауважте, що маркер заповнення автоматично ідентифікує візерунок і заповнює залишкові клітинки цим шаблоном. У цьому випадку закономірність була такою, що числа збільшувалися на 1.
Якщо у наборі даних є порожній рядок, маркер заповнення буде працювати лише до останнього суміжного непустого рядка.
Також зверніть увагу, що якщо у сусідньому стовпці немає даних, подвійне натискання маркера заповнення не спрацює. Однак ви можете навести курсор на маркер заповнення, утримувати праву клавішу миші та перетягувати вниз. Він заповнить клітинки, охоплені перетягуванням курсору.
2] Використання серії заповнення
Хоча функція заповнення - це швидкий спосіб нумерації рядків у програмі Excel, серія заповнення дає вам набагато більший контроль над способом введення чисел.
Припустимо, у вас є набір даних, як показано нижче:
Нижче наведено кроки для використання ряду заповнення для нумерації рядків у Excel:
- Введіть 1 у клітинку А2.
- Перейдіть на вкладку Домашня сторінка.
- У групі редагування натисніть спадне меню "Заповнити".
- У спадному меню виберіть "Серія …".
- У діалоговому вікні "Серія" виберіть "Стовпці" у параметрах "Серія в".
- Вкажіть значення зупинки. У цьому випадку, оскільки у нас є 26 записів, ми можемо ввести 26. Якщо ви не введете жодного значення, серія заповнення не працюватиме.
- Натисніть OK.
Це миттєво нумерує рядки від 1 до 26.
Використання "Заповнити серію" може бути корисним, коли ви починаєте, вводячи номери рядків. На відміну від заповнювача, він не вимагає заповнення сусідніх стовпців.
Навіть якщо у вас немає нічого на аркуші, заповнення серії все одно працюватиме.
Примітка: Якщо у вас є порожні рядки посередині набору даних, заповнення ряду все одно заповнить номер цього рядка.
3] Використання функції ROW
Ви також можете використовувати функції Excel для нумерації рядків у Excel.
У наведених вище методах заповнення та серії заповнення введений серійний номер є статичним значенням. Це означає, що якщо ви перемістите рядок (або виріжете та вставте його кудись ще в наборі даних), нумерація рядків не зміниться відповідно.
Цей недолік можна усунути за допомогою формул у Excel.
Ви можете скористатися функцією ROW, щоб отримати нумерацію рядків у Excel.
Щоб отримати нумерацію рядків за допомогою функції ROW, введіть таку формулу в першу клітинку та скопіюйте для всіх інших клітинок:
= ROW ()-1
Функція ROW () дає номер рядка поточного рядка. Тому я відняв 1, починаючи з другого ряду. Якщо ваші дані починаються з 5-го рядка, вам потрібно використати формулу = ROW ()-4.
Найкраще у використанні функції ROW - це те, що вона не зіпсує нумерацію, якщо видалити рядок у наборі даних.
Оскільки функція ROW не посилається на жодну клітинку, вона автоматично (або я повинен сказати AutoMagically) налаштується, щоб дати правильний номер рядка. Щось, як показано нижче:
Зверніть увагу: щойно я видаляю рядок, номери рядків автоматично оновлюються.
Знову ж таки, це не враховує жодних порожніх записів у наборі даних. Якщо у вас є порожні рядки, він все одно відображатиме номер рядка.
Ви можете використати таку формулу, щоб приховати номер рядка для порожніх рядків, але він все одно не буде коригувати номери рядків (таким чином, щоб номер наступного рядка призначався наступному заповненому рядку).
IF (ISBLANK (B2), "", ROW ()-1)
4] Використання функції COUNTA
Якщо ви хочете пронумерувати рядки таким чином, щоб лише заповнені отримали серійний номер, тоді цей спосіб є правильним.
Він використовує функцію COUNTA, яка підраховує кількість порожніх клітинок у діапазоні.
Припустимо, у вас є набір даних, як показано нижче:
Зауважте, що у наведеному вище наборі даних є порожні рядки.
Ось формула, яка буде нумерувати рядки без нумерації порожніх рядків.
= IF (ISBLANK (B2), "", COUNTA ($ B $ 2: B2))
Функція IF перевіряє, чи порожня сусідня комірка у стовпці B чи ні. Якщо він порожній, він повертає пробіл, а якщо ні, то повертає кількість усіх заповнених клітинок до цієї клітинки.
5] Використання SUBTOTAL для відфільтрованих даних
Іноді у вас може бути величезний набір даних, де потрібно відфільтрувати дані, а потім скопіювати та вставити відфільтровані дані в окремий аркуш.
Якщо ви досі використовуєте будь -який із методів, показаних вище, ви помітите, що номери рядків залишаються незмінними. Це означає, що під час копіювання відфільтрованих даних вам доведеться оновити нумерацію рядків.
У таких випадках функція SUBTOTAL може автоматично оновлювати номери рядків. Навіть коли ви фільтруєте набір даних, номери рядків залишаться незмінними.
Дозвольте мені показати вам, як це працює, на прикладі.
Припустимо, у вас є набір даних, як показано нижче:
Якщо я відфільтрую ці дані на основі продажів продукту А, ви отримаєте щось, як показано нижче:
Зверніть увагу, що серійні номери у стовпці А також відфільтровуються. Тож тепер ви бачите лише видимі рядки.
Хоча це очікувана поведінка, якщо ви хочете отримати послідовну нумерацію рядків - щоб ви могли просто скопіювати та вставити ці дані кудись інше - ви можете скористатися функцією SUBTOTAL.
Ось функція SUBTOTAL, яка забезпечить безперервну нумерацію рядків навіть у відфільтрованих даних.
= ПІДСУМКИ (3, $ B $ 2: B2)
3 у функції SUBTOTAL визначає використання функції COUNTA. Другий аргумент - це діапазон, до якого застосовується функція COUNTA.
Перевагою функції SUBTOTAL є те, що вона динамічно оновлюється під час фільтрації даних (як показано нижче):
Зауважте, що навіть коли дані фільтруються, нумерація рядків оновлюється і залишається безперервною.
6] Створення таблиці Excel
Таблиця Excel - це чудовий інструмент, який необхідно використовувати під час роботи з табличними даними. Це значно спрощує управління та використання даних.
Це також мій улюблений метод серед усіх технік, показаних у цьому уроці.
Спочатку дозвольте мені показати вам правильний спосіб нумерації рядків за допомогою таблиці Excel:
- Виберіть весь набір даних.
- Перейдіть на вкладку Вставка.
- Натисніть на піктограму Таблиця (також можна скористатися комбінацією клавіш Control + T).
- У діалоговому вікні Створення таблиці переконайтеся, що діапазон правильний.
- Натисніть OK. Це перетворить ваші табличні дані в таблицю Excel.
- У клітинці А2 введіть таку формулу. Зауважте, що як тільки ви введете формулу, вона автоматично заповнить її у всіх клітинках у цьому стовпці (докладніше про розрахункові стовпці можна прочитати тут).
= ROW ()-ROW (Таблиця 2 [#Headers])
Зауважте, що у формулі вище я використав Таблиця 2, оскільки це назва моєї таблиці Excel. Ви можете замінити Table2 на ім'я таблиці, яка у вас є.
Існує кілька додаткових переваг використання таблиці Excel під час нумерації рядків у програмі Excel:
- Оскільки таблиця Excel автоматично вставляє формулу у весь стовпець, вона працює, коли ви вставляєте новий рядок у таблицю. Це означає, що під час вставлення/видалення рядків у таблиці Excel нумерація рядків автоматично оновлюється (як показано нижче).
- Якщо до даних додати більше рядків, таблиця Excel автоматично розгорнеться, включивши ці дані як частину таблиці. А оскільки формули автоматично оновлюються у обчислюваних стовпцях, до них буде вставлено номер рядка для знову вставленого рядка (як показано нижче).
7] Додавання 1 до номера попереднього ряду
Це простий метод, який працює.
Ідея полягає в тому, щоб додати номер 1 до попереднього рядка (номер у комірці вище). Це переконається, що наступні рядки отримають число, збільшене на 1.
Припустимо, у вас є набір даних, як показано нижче:
Ось кроки для введення номерів рядків за допомогою цього методу:
- У клітинці першого рядка введіть 1 вручну. У цьому випадку він знаходиться у комірці А2.
- У клітинці A3 введіть формулу = A2+1
- Скопіюйте та вставте формулу для всіх клітинок у стовпці.
Наведені вище кроки вводять серійні номери у всі комірки стовпця. Якщо є чисті рядки, це все одно вставить номер рядка для нього.
Також зверніть увагу, що у разі вставлення нового рядка номер рядка не оновлюватиметься. У разі видалення рядка всі клітинки під видаленим рядком відображатимуть помилку посилання.
Ось кілька швидких способів вставки серійних номерів у табличні дані в Excel.
Якщо ви використовуєте будь -який інший метод, поділіться ним зі мною у розділі коментарів.
