Note: The other languages of the website are Google-translated. Back to English

Як заповнити порядкові номери пропустити приховані рядки в Excel?

Припустимо, у вас є діапазон даних фільтра, який ви хочете заповнити порядковими номерами у відфільтровану порожню комірку, як показано на наступному знімку екрана. У цьому випадку перетягування ручки заповнення не заповнить клітинки правильно, загалом, у цій статті я розповім про кілька хороших прийомів для вирішення цього завдання в Excel.

doc заповнити пропустити приховано 1 2 doc заповнити пропустити приховано 2

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

Заповніть порядкові номери, щоб пропустити приховані рядки кодом VBA

Заповнюючи порядкові номери, пропускайте приховані рядки за допомогою Kutools для Excel


Щоб заповнити порядкові номери лише до видимих ​​комірок, вам може допомогти проста формула, будь-ласка, зробіть наступне:

1. Введіть цю формулу: = ПІДЗВІТ (103, $ B $ 3: $ B3)(B3 - це видима комірка, яка відповідає комірці, до якої потрібно розпочати заповнення порядкового номера) у першу комірку, де ви хочете заповнити порядкові номери, A3, для цього випадку, а потім натисніть вводити ключ, ви отримаєте номер 1, див. знімок екрана:

doc заповнити пропустити приховано 3

2. Потім виділіть комірку A3 і перетягніть маркер заповнення вниз до комірок, які потрібно заповнити порядковими номерами, див. Знімок екрана:

doc заповнити пропустити приховано 4


Наступний код VBA також може допомогти вам заповнити порядкові номери у лише видимі клітинки. Будь ласка, зробіть так:

1. Утримуйте клавішу ALT + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модуліта вставте наступний код у Модулі Вікно

Код VBA: Заповніть порядкові номери, пропустіть приховані рядки:

Sub FillValue()
'updateby Extendoffice
    Dim xRg As Range
    Dim xCell As Range
    Dim xTxt As String
    Dim xVal As Long
    Dim I As Long
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select the data range", "Kutools for Excel", xTxt, , , , , 8)
    Set xRg = xRg.SpecialCells(xlVisible)
    Debug.Print xRg.Address
    If xRg Is Nothing Then Exit Sub
    For Each xCell In xRg
        xVal = xVal + 1
        xCell = xVal
    Next
End Sub

3. Потім натисніть F5 клавішу для запуску цього коду, і з'явиться підказка, щоб нагадати вам вибрати діапазон даних, який потрібно заповнити, див. знімок екрана:

doc заповнити пропустити приховано 5

4. А потім клацніть OK , порядкові номери були заповнені лише у видимих ​​клітинках.


Вище двох методів можуть допомогти вам заповнити порядкові номери у видимі клітинки, якщо ви хочете заповнити послідовність як AA-0001-BB, AA-0002-BB, AA-0003-BB ..., вищезазначені методи не спрацюють. Але, будь ласка, не турбуйтеся про, Kutools для ExcelАвтора Вставте порядковий номер утиліта допоможе вам швидко і легко закінчити її.

Kutools для Excel : з більш ніж 300 зручними надбудовами Excel, які можна спробувати без обмежень протягом 30 днів

після установки Kutools для Excel, будь ласка, виконайте наступне:

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

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

2 doc заповнити пропустити приховано 7

3. У розгорнутому діалоговому вікні, будь-ласка, вкажіть потрібні параметри у нижній лівій частині, водночас ви можете переглянути послідовність у правій нижній панелі, див. Знімок екрана:

doc заповнити пропустити приховано 8

4. Після закінчення налаштувань натисніть додавати , щоб зберегти цей новий елемент у наведеному вище списку, див. знімок екрана:

doc заповнити пропустити приховано 9

6. А потім виберіть створений вами елемент і натисніть Діапазон заповнення , рядки послідовності були заповнені лише у видимі клітинки, див. знімок екрана:

doc заповнити пропустити приховано 10

Завантажте та безкоштовну пробну версію Kutools для Excel зараз!


Kutools для Excel: з більш ніж 300 зручними надбудовами Excel, спробуйте безкоштовно без обмежень протягом 30 днів. Завантажте та безкоштовно пробну версію зараз!

Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (12)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
Дякую! Це працює!!! Дійсно ціную це! Бережіть себе та бережи, ПБ
Цей коментар був мінімізований модератором на сайті
Як ми можемо використовувати це рішення зі користувацьким початковим номером: Заповніть порядкові номери, пропустіть приховані рядки за допомогою коду VBA
Цей коментар був мінімізований модератором на сайті
Усі наведені вище кроки є занадто складними, коли я просто хочу двічі клацнути маркером заповнення та заповнити вниз над прихованими рядками. Чи може Kutools дати нам можливість автозаповнення за допомогою маркера заповнення над відфільтрованими/прихованими рядками, просто за допомогою подвійного клацання?
Цей коментар був мінімізований модератором на сайті
Привіт, Мет,
Дякуємо за ваш коментар, Kutools поки що не підтримує цю функцію, але ми розглянемо вашу пропозицію.
Дякую!
Цей коментар був мінімізований модератором на сайті
Це не працює. Замість заповнення ряду, це просто копіювання першої клітинки.
Цей коментар був мінімізований модератором на сайті
У мене це теж не працює! Я отримую 1 у вихідній клітинці, а потім 1 у кожній клітинці, до якої я копіюю формулу.
Цей коментар був мінімізований модератором на сайті
З останніх кількох років я вручну додаю серійні номери до аркушів, які містять сотні, якщо не тисячі рядків. Наведена вище формула буквально заощадила мені багато годин на все життя. Дякую, оп.
Цей коментар був мінімізований модератором на сайті
ДУЖЕ ДЯКУЮ
Цей коментар був мінімізований модератором на сайті
У мене є різні робочі аркуші в книзі, і я хочу використовувати код VBA для всіх аркушів одночасно.
Чи може хтось мені допомогти.


Наприклад:
На кожному аркуші формату А1 у мене S.No. і я хочу, щоб це був порядковий номер 1 2 3 4 .....
Цей коментар був мінімізований модератором на сайті
жоден з цих BS не працював... Перший, який використовував формулу, пропустив числа та повторив інші числа... чи є простіший спосіб? FFS!
Цей коментар був мінімізований модератором на сайті
Це спрацювало!! Дуже дякую!
Цей коментар був мінімізований модератором на сайті
Що за магія з 103?
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0  Персонажі
Рекомендовані місця