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

Як вставити числа або рядки для пропущених послідовних чисел в Excel?

Припустимо, у вас є список послідовних чисел на аркуші, але серед послідовності є деякі відсутні числа, і тепер вам потрібно вставити відсутні числа або порожні рядки, щоб переконатися, що послідовність є повною (показано на наступних знімках екрана). Як ви могли швидко вирішити цю проблему в Excel?

doc-insert-missing-number1 -2 doc-insert-missing-number2

Вставте відсутні цифри для послідовності за допомогою функції сортування та видалення дублікатів

Вставте відсутні цифри для послідовності з кодом VBA

Вставте порожні рядки для відсутності послідовності з кодом VBA

Вставте відсутні цифри або порожні рядки для послідовності за допомогою Kutools для Excel


стрілка синя права міхур Вставте відсутні цифри для послідовності за допомогою функції сортування та видалення дублікатів

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

1. Після кінця списку послідовностей заповніть інші порядкові номери від 2005023001 до 2005023011. Дивіться знімок екрана:

doc-insert-missing-number3

2. Потім виберіть діапазон двох порядкових номерів і натисніть дані > Сортувати від А до Я, див. скріншот:

doc-insert-missing-number4

3. І вибрані дані були відсортовані як наступний знімок екрана:

doc-insert-missing-number5

4. Потім потрібно видалити дублікати клацанням дані > Видалити дублікати, і в вискочив Видалити дублікати діалогове вікно, перевірте Колонка ім'я, яке потрібно видалити дублікатами, див. скріншоти:

doc-insert-missing-number6 -2 doc-insert-missing-number7

5. Потім натисніть OK, дублікати в Стовпець А було видалено, а відсутні числа у списку послідовностей вставлені, див. знімок екрана:

doc-insert-missing-number8


стрілка синя права міхур Вставте відсутні цифри для послідовності з кодом VBA

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

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

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

VBA: вставити відсутні цифри для послідовності

Sub InsertValueBetween()
'Updateby Extendoffice
Dim WorkRng As Range
Dim Rng As Range
Dim outArr As Variant
Dim dic As Variant
Set dic = CreateObject("Scripting.Dictionary")
'On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
num1 = WorkRng.Range("A1").Value
num2 = WorkRng.Range("A" & WorkRng.Rows.Count).Value
interval = num2 - num1
ReDim outArr(1 To interval + 1, 1 To 2)
For Each Rng In WorkRng
    dic(Rng.Value) = Rng.Offset(0, 1).Value
Next
For i = 0 To interval
    outArr(i + 1, 1) = i + num1
    If dic.Exists(i + num1) Then
        outArr(i + 1, 2) = dic(i + num1)
    Else
        outArr(i + 1, 2) = ""
    End If
Next
With WorkRng.Range("A1").Resize(UBound(outArr, 1), UBound(outArr, 2))
    .Value = outArr
    .Select
End With
End Sub

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

doc-insert-missing-number9

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

doc-insert-missing-number1 -2 doc-insert-missing-number2

стрілка синя права міхур Вставте порожні рядки для відсутності послідовності з кодом VBA

Іноді вам просто потрібно знайти місце пропущених цифр і вставити порожні рядки між даними, щоб ви могли вводити інформацію, як вам потрібно. Звичайно, наступний код VBA також може допомогти вам вирішити цю проблему.

1. Утримуйте клавішу ALT + F11 клавіші, і відкриється a Microsoft Visual Basic для додатків вікна.

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

VBA: вставити порожні рядки для відсутності послідовності

Sub InsertNullBetween()
'Updateby Extendoffice
Dim WorkRng As Range
Dim Rng As Range
Dim outArr As Variant
Dim dic As Variant
Set dic = CreateObject("Scripting.Dictionary")
'On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
num1 = WorkRng.Range("A1").Value
num2 = WorkRng.Range("A" & WorkRng.Rows.Count).Value
interval = num2 - num1
ReDim outArr(1 To interval + 1, 1 To 2)
For Each Rng In WorkRng
    dic(Rng.Value) = Rng.Offset(0, 1).Value
Next
For i = 0 To interval
    If dic.Exists(i + num1) Then
        outArr(i + 1, 1) = i + num1
        outArr(i + 1, 2) = dic(i + num1)
    Else
        outArr(i + 1, 1) = ""
        outArr(i + 1, 2) = ""
    End If
Next
With WorkRng.Range("A1").Resize(UBound(outArr, 1), UBound(outArr, 2))
    .Value = outArr
    .Select
End With
End Sub

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

doc-insert-missing-number9

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

doc-insert-missing-number1 -2 doc-insert-missing-number10

стрілка синя права міхур Вставте відсутні цифри або порожні рядки для послідовності за допомогою Kutools для Excel

Тут я представляю простий та зручний інструмент - Kutools для Excel, З його Знайдіть відсутній порядковий номер Ви можете швидко вставити відсутній порядковий номер або порожні рядки між існуючою послідовністю даних.

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

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

1. Виберіть послідовність даних, до якої потрібно вставити відсутні числа.

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

3, в Знайдіть відсутній порядковий номер діалогове вікно, поставте галочку Вставка відсутнього порядкового номера вставити пропущені цифри або Iвставка порожніх рядків при зустрічі відсутніх порядкових номерів щоб вставити порожні рядки, як вам потрібно. Дивіться знімок екрана:

doc-insert-missing-number10

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

doc-insert-missing-number10 2 doc-insert-missing-number10 2 doc-insert-missing-number10

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


стрілка синя права міхур  Демо: Вставте відсутні цифри або порожні рядки для послідовності за допомогою Kutools для Excel

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

Пов'язана стаття:

Як визначити послідовність пропущених чисел у Excel?


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

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

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

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

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (10)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
Спасибі ! Чудовий сценарій! Як я можу змінити цей сценарій, якщо я кажу, що нам потрібно обробити не тільки стовпець ID + стовпець NAME, але стовпець ID + стовпець NAME + НОВИЙ стовпець? Як я можу додати нові стовпці в цей скрипт?
Цей коментар був мінімізований модератором на сайті
Нижче наведено змінений макрос, який включає доданий стовпець. Ще один важливий момент полягає в тому, що коли буде запропоновано вибрати діапазон, ви повинні вибрати лише перший стовпець — це зайняло у мене кілька годин! сподіваюся заощадити час інших

Sub InsertValueBetween()
'Оновлення Extendoffice
Dim WorkRng як діапазон
Dim Rng як дальність
Dim outArr як варіант
Dim dic як варіант
Встановити dic = CreateObject("Scripting.Dictionary")
Dim dic2 як варіант
Встановити dic2 = CreateObject("Scripting.Dictionary")

'При помилці Відновити далі
xTitleId = "KutoolsforExcel"
Встановіть WorkRng = Application.Selection
Встановіть WorkRng = Application.InputBox("Діапазон", xTitleId, WorkRng.Address, Тип:=8)
num1 = WorkRng.Range("A1").Значення
num2 = WorkRng.Range("A" & WorkRng.Rows.Count).Value
інтервал = num2 - num1
ReDim outArr(1 до інтервалу + 1, 1 до 3)
Для кожного кільця в робочому кільці
dic(Rng.Value) = Rng.Offset(0, 1).Value
dic2(Rng.Value) = Rng.Offset(0, 2).Value
Далі
Для i = 0 До інтервалу
outArr(i + 1, 1) = i + num1
Якщо dic.Exists(i + num1) Тоді
outArr(i + 1, 2) = dic(i + num1)
outArr(i + 1, 3) = dic2(i + num1)
Ще
outArr(i + 1, 2) = ""
outArr(i + 1, 3) = ""

End If
Далі
З WorkRng.Range("A1").Resize(UBound(outArr, 1), UBound(outArr, 2))
.Value = outArr
.Виберіть
Кінець з
End Sub
Цей коментар був мінімізований модератором на сайті
це спрацювало і було дуже легко виконати завдання. Дякую.
Цей коментар був мінімізований модератором на сайті
Дуже дякую. Як змінити сценарій, якщо кроки становлять лише 0.02, а не 1 Це для сценарію InsertNullBetween()
Цей коментар був мінімізований модератором на сайті
Що робити, якщо я хочу вибрати 6 стовпців, а потім перевірити 1-й стовпець на наявність дат, а якщо дати відсутні, додати рядок (порожні клітинки) для всіх 6 стовпців
Цей коментар був мінімізований модератором на сайті
Привіт! Я хочу використати "Вставлення відсутнього порядкового номера", але він не підтримує, якщо немає. число цифр більше 12 чи можете ви допомогти?
Цей коментар був мінімізований модератором на сайті
Я хочу використовувати функцію «Вставлення відсутнього порядкового номера», але вона не підтримує цифри більше 12? є багато наборів, у які я хочу вставити послідовність між (це буквено-цифрова цифра) чи можете ви допомогти
Цей коментар був мінімізований модератором на сайті
Що робити, якщо я хочу вибрати 6 стовпців, а потім перевірити 1-й стовпець на наявність дат, а якщо дати відсутні, додати рядок (порожні клітинки) для всіх 6 стовпців
Цей коментар був мінімізований модератором на сайті
Дякую, дивовижно
Цей коментар був мінімізований модератором на сайті
Я намагаюся використовувати VBA для послідовних номерів. У мене є кілька стовпчиків, поряд із номерами яких теж. тобто
1. Лічильник HL 34
2. Водомір HL 40
4. HL CO2метр 24

Коли я використовую код, він працює для перших 3 стовпців, але він переплутується, якщо я включаю 4-й стовпець, оскільки він також містить числа.
Як я можу змінити код, щоб переконатися, що числа в стовпці 4 залишаються незмінними?
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL