Как вставить числа или строки для отсутствующих последовательных чисел в Excel?
Предположим, у вас есть список последовательных чисел на листе, но среди последовательности есть пропущенные числа, и теперь вам нужно вставить недостающие числа или пустые строки, чтобы сделать последовательность полной (как показано на следующих скриншотах). Как можно быстро решить эту проблему в Excel?
Вставка отсутствующих чисел в последовательность с помощью функции Сортировка и Удаление дубликатов
Может быть, вы можете найти отсутствующие числа по одному, а затем вставить их, но если имеется сотни последовательных чисел, будет сложно определить местоположение отсутствующих. В 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?
Лучшие инструменты для повышения продуктивности в 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек