Перейти до основного матеріалу

Як вставити числа або рядки для пропущених послідовних чисел в 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 AI Aide: Революціонізуйте аналіз даних на основі: Інтелектуальне виконання   |  Згенерувати код  |  Створення спеціальних формул  |  Аналізуйте дані та створюйте діаграми  |  Викликати функції Kutools...
Популярні функції: Знайдіть, виділіть або визначте дублікати   |  Видалити порожні рядки   |  Об’єднайте стовпці або клітинки без втрати даних   |   Раунд без Формули ...
Супер пошук: VLookup за кількома критеріями    Багатозначний VLookup  |   VLookup на кількох аркушах   |   Нечіткий пошук ....
Розширений розкривний список: Швидке створення випадаючого списку   |  Залежний спадний список   |  Виберіть розкривний список, що вибирається ....
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  |  Порівняйте діапазони та стовпці ...
Особливості: Фокус сітки   |  Перегляд дизайну   |   Велика панель формул    Диспетчер робочих книг і аркушів   |  Бібліотека ресурсів (автотекст)   |  Вибір дати   |  Об’єднайте робочі аркуші   |  Шифрування/розшифрування клітинок    Надсилайте листи за списком   |  Супер фільтр   |   Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи, ...)   |   50 + Графік типи (діаграма Ганта, ...)   |   40+ Практичний Формули (Розрахуйте вік на основі дня народження, ...)   |   19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху, ...)   |   12 Перетворення Tools (Числа до слів, Валютна конверсія, ...)   |   7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділені клітини, ...)   |   ... і більше

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

Опис


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

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
Comments (12)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I have used the code for "VBA: insert blank rows for missing sequence" as listed above and works great - but i need it to insert rows across the all columns it only adds rows to the first 2 columns of my selection - not my entire table.
This comment was minimized by the moderator on the site
Hello, Melanie,

To solve your problem, maybe the following code can help you: (Note: A indicates the column contains the missing sequence, please change it to your need.)
Sub InsertBlankRowsForMissingSequence()
    Dim i As Long
    On Error Resume Next
    Application.ScreenUpdating = False
    For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
        If IsNumeric(Cells(i, "A").Value) And IsNumeric(Cells(i - 1, "A").Value) And Cells(i, "A").Value <> "" And Cells(i - 1, "A").Value <> "" Then
            If Cells(i, "A").Value - Cells(i - 1, "A").Value > 1 Then
                Debug.Print Cells(i, "A").Value - Cells(i - 1, "A").Value - 1
                Rows(i).Resize(Cells(i, "A").Value - Cells(i - 1, "A").Value - 1).Insert
            End If
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
I am trying to use the VBA for sequential numbers. I have several columns next to the numbers of which numbers too. I.e.
1. HL Meter 34
2. HL Watermeter 40
4. HL CO2meter 24

When I use the code it works for the first 3 columns but it gets mixed up if I include the 4th column since it includes numbers too.
How can I change the code to make sure the numbers in column 4 stay the same?
This comment was minimized by the moderator on the site
Thank you amazing
This comment was minimized by the moderator on the site
What if i want to select 6 columns and then check 1st column for dates and if dates are missing add a row(blank cells) for all 6 columns
This comment was minimized by the moderator on the site
I want to use "Inserting missing sequence Number" feature but it's not supporting for digits more than 12 ? there are many sets in which I want to insert the sequence between (it's a alpha-numeric digit) can you help
This comment was minimized by the moderator on the site
Hi, I want to use "Inserting Missing Sequence Number", but it's not supporting if the no. of digits are more than 12 can you help ?
This comment was minimized by the moderator on the site
What if i want to select 6 columns and then check 1st column for dates and if dates are missing add a row(blank cells) for all 6 columns
This comment was minimized by the moderator on the site
Thank you very much. How do i change the script if the increments is only 0.02 and not 1 This is for the script InsertNullBetween()
This comment was minimized by the moderator on the site
this worked and was very easy to complete the task. Thank you.
This comment was minimized by the moderator on the site
Thanks ! Great script ! How i can modify this script if i say we need to process not only ID column + NAME column, but ID column + NAME column + NEW column ? How i can add new columns in this script?
This comment was minimized by the moderator on the site
The following is the modified macro to include an added column - Another important point is that when prompted to select the range, you should only select the first column - these took me a few hours! hope to save others' time

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")
Dim dic2 As Variant
Set dic2 = 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 3)
For Each Rng In WorkRng
dic(Rng.Value) = Rng.Offset(0, 1).Value
dic2(Rng.Value) = Rng.Offset(0, 2).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)
outArr(i + 1, 3) = dic2(i + num1)
Else
outArr(i + 1, 2) = ""
outArr(i + 1, 3) = ""

End If
Next
With WorkRng.Range("A1").Resize(UBound(outArr, 1), UBound(outArr, 2))
.Value = outArr
.Select
End With
End Sub
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations