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

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

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

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

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

1. После окончания списка последовательности заполните другую последовательность чисел от 2005023001 до 2005023011. Смотрите скриншот:

2. Затем выберите диапазон двух последовательностей чисел и нажмите Данные > Сортировать по возрастанию, смотрите скриншот:

3. И выбранные данные будут отсортированы, как показано на следующем скриншоте:

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

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

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

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

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

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

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

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

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

Скачайте и попробуйте Kutools для Excel прямо сейчас!

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

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

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