Как вставить числа или строки для пропущенных порядковых номеров в Excel?

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

Вставка отсутствующих номеров для последовательности с функцией сортировки и удаления дубликатов Вставьте недостающие числа для последовательности с кодом VBA Вставьте пустые строки для отсутствующей последовательности с кодом VBA Вставьте отсутствующие числа или пустые строки для последовательности с помощью Kutools for Excel

Вставка отсутствующих номеров для последовательности с функцией сортировки и удаления дубликатов

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

1. После конца списка последовательностей введите другие порядковые номера от 2005023001 до 2005023011. См. Снимок экрана:

2. Затем выберите диапазон из двух порядковых номеров и нажмите Данные > Сортировка от А до Я, см. снимок экрана:

3. И выбранные данные были отсортированы как следующий снимок экрана:

4. Затем нужно удалить дубликаты нажатием Данные > Удалить дубликаты, а в выскочившем Удалить дубликаты диалоговое окно, проверьте Колонка имя, дубликаты которого вы хотите удалить, смотрите на скриншотах:

5. Затем нажмите OK, дубликаты в Колонка A был удален, и отсутствующие числа в списке последовательности были вставлены, см. снимок экрана:

Вставьте недостающие числа для последовательности с кодом VBA

Если вы чувствуете, что с помощью вышеуказанных методов требуется так много шагов, здесь также есть код VBA, который поможет вам решить эту проблему. Пожалуйста, сделайте следующее:

1. Удерживайте ALT + F11 ключи, и он открывает Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в Модули окно.

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 ключи, и он открывает Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в Модули окно.

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 for Excel

Здесь я представлю простой и удобный инструмент - Kutools for Excel, С его Найти отсутствующий порядковый номер функция, вы можете быстро вставить отсутствующий порядковый номер или пустые строки между существующей последовательностью данных.

Если вы установили Kutools for Excel, пожалуйста, сделайте следующее:

1. Выберите последовательность данных, в которую вы хотите вставить недостающие числа.

2. Нажмите Кутулс > Вставить > Найти отсутствующий порядковый номер, см. снимок экрана:

3. В Найти отсутствующий порядковый номер диалоговое окно, отметьте Вставка отсутствующего порядкового номера вставить недостающие числа или яn вставка пустых строк при обнаружении отсутствующих порядковых номеров чтобы вставить пустые строки по мере необходимости. Смотрите скриншот:

4, Затем нажмите OK кнопку, и в данные были вставлены отсутствующие порядковые номера или пустые строки, см. снимки экрана:

Загрузите бесплатную пробную версию Kutools for Excel прямо сейчас!

  Демо: вставьте отсутствующие числа или пустые строки для последовательности с помощью Kutools for Excel

Связанная статья:

Как определить последовательность недостающих чисел в Excel?

---

Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

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

Больше информации... Скачать бесплатно... Покупка ... 

---

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

• Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
• Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
• Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Больше информации... Скачать бесплатно... Покупка ...