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 for 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, дубликаты в Колонка A был удален, и отсутствующие числа в списке последовательности были вставлены, см. снимок экрана:

doc-insert-missing-number8


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

doc-insert-missing-number9

4, Затем нажмите OK, недостающие номера добавлены в список последовательностей. Смотрите скриншоты:

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

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

doc-insert-missing-number9

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

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

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

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

Kutools for Excel : с более чем 300 удобными надстройками Excel, бесплатно и без ограничений в течение 30 дней

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

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

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

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

doc-insert-missing-number10

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

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

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


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

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

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

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


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

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

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

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

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Сортировать комментарии по
Комментарии (10)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
Спасибо ! Отличный сценарий! Как я могу изменить этот сценарий, если я скажу, что нам нужно обрабатывать не только столбец ID + столбец NAME, но столбец ID + столбец NAME + столбец NEW? Как я могу добавить новые столбцы в этот скрипт?
Этот комментарий был сведен к минимуму модератором на сайте
Ниже приведен измененный макрос для включения добавленного столбца. Еще одним важным моментом является то, что при появлении запроса на выбор диапазона вы должны выбрать только первый столбец - это заняло у меня несколько часов! надеюсь сэкономить чужое время

Sub InsertValueBetween ()
'Обновить Extendoffice
Dim WorkRng As Range
Dim Rng As Range
Dim outArr As Variant
Dim dic как вариант
Установить dic = CreateObject("Scripting.Dictionary")
Dim dic2 как вариант
Установите dic2 = CreateObject("Scripting.Dictionary")

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

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

Когда я использую код, он работает для первых 3 столбцов, но он путается, если я включаю 4-й столбец, поскольку он также включает числа.
Как я могу изменить код, чтобы числа в столбце 4 оставались прежними?
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места

Подписывайтесь на Нас

Copyright © 2009 - www.extendoffice.ком. | Все права защищены. Питаться от ExtendOffice, | Карта сайта
Microsoft и логотип Office являются товарными знаками или зарегистрированными товарными знаками Microsoft Corporation в США и / или других странах.
Защищено Sectigo SSL