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

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

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

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

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

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

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

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

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

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

Вставте відсутні цифри для послідовності з кодом 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 клавішу для запуску цього коду, і з'явиться вікно запиту, виберіть діапазон даних, в який потрібно вставити відсутні цифри (не вибирайте діапазон заголовків), див. знімок екрана:

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

Вставте порожні рядки для відсутності послідовності з кодом 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 клавішу для запуску цього коду, і відобразиться вікно запиту, а потім виберіть діапазон даних, до якого потрібно вставити порожні рядки для відсутньої послідовності (не вибирайте діапазон заголовків), див. знімок екрана:

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

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

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

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

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

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

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

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

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

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

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

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

---

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

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

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

Детальніше ... Завантажити безкоштовно... Придбати ... 

---

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

• Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
• Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
• Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!

Детальніше ... Завантажити безкоштовно... Придбати ...