Как вставить номера или строки, чтобы восстановить пропущенные последовательные номера в Excel?
Предположим, у вас на листе есть список последовательных номеров, но некоторые из них пропущены. Как быстро вставить недостающие номера или пустые строки, чтобы восстановить полную последовательность (см. скриншоты ниже)?
Вставка пропущенных номеров в последовательность с помощью сортировки и функции Удалить дубликаты
Можно находить пропущенные номера по одному и вставлять их, но если в последовательности сотни номеров, определить места пропусков становится крайне затруднительно. В Excel для решения этой задачи я могу воспользоваться сортировкой и функцией «Удалить дубликаты».
1. После завершения списка последовательности заполните ещё один — последовательные числа от 2005023001 до 2005023011. См. скриншот:
2. Затем выделите диапазон из двух последовательных чисел и нажмите Данные > По возрастанию, см. скриншот:
3. Данные «Выбрать данные» были отсортированы, как показано на следующем скриншоте:
4. Затем удалите дубликаты: нажмите Данные > Удалить дубликаты, и в появившемся диалоговом окне Удалить дубликаты установите флажок напротив имени столбца, из которого нужно удалить дубликаты. См. скриншоты:
 |  |  |
5. Затем нажмите кнопку ОК, и дубликаты в столбце A будут удалены, а пропущенные номера в списке последовательности — вставлены. См. скриншот:
Вставка пропущенных номеров или Пустые строки в последовательность с помощью Kutools для Excel
Здесь я представлю простой и удобный инструмент — Kutools для Excel. С помощью его функции Найти отсутствующую последовательность вы сможете быстро вставить пропущенные номера последовательности или пустые строки между существующими данными.
Если вы установили Kutools для Excel, выполните следующие действия:
1. Выделите диапазон данных, в который нужно вставить пропущенные номера.
2. Нажмите Kutools > Вставка > Найти отсутствующую последовательность, см. скриншот:
3. В диалоговом окне Найти отсутствующую последовательность установите флажок Вставить отсутствующие данные, чтобы автоматически добавить пропущенные номера, или выберите Вставка пустых строк при обнаружении пропущенных последовательных чисел, чтобы вставить пустые строки по вашему выбору. См. скриншот:
4. Затем нажмите кнопку ОК, и пропущенные последовательные числа или пустые строки будут добавлены в данные. См. скриншоты:
 | |  | |  |
Скачайте 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. Затем нажмите кнопку ОК, и пропущенные номера автоматически вставятся в последовательность. См. скриншоты:
 | |  |
Вставка Пустые строки для пропущенной последовательности с помощью кода 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. Затем нажмите кнопку ОК, и пустые строки будут вставлены для списка с пропущенной последовательностью. См. скриншоты:
| |  |
Связанная статья:
Как найти пропущенные номера в последовательности в Excel?
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек