Як вставити числа або рядки для пропущених послідовних чисел в Excel?
Припустимо, у вас є список послідовних чисел на аркуші, але серед послідовності є деякі відсутні числа, і тепер вам потрібно вставити відсутні числа або порожні рядки, щоб переконатися, що послідовність є повною (показано на наступних знімках екрана). Як ви могли швидко вирішити цю проблему в Excel?
Вставте відсутні цифри для послідовності за допомогою функції сортування та видалення дублікатів
Можливо, ви можете знайти пропущені числа по одному, а потім вставити їх, але вам важко визначити місце розташування зниклих, якщо є сотні послідовних чисел. В Excel я можу використовувати функцію сортування та видалення дублікатів для вирішення цього завдання.
1. Після кінця списку послідовностей заповніть інші порядкові номери від 2005023001 до 2005023011. Дивіться знімок екрана:
2. Потім виберіть діапазон двох порядкових номерів і натисніть дані > Сортувати від А до Я, див. скріншот:
3. І вибрані дані були відсортовані як наступний знімок екрана:
4. Потім потрібно видалити дублікати клацанням дані > Видалити дублікати, і в вискочив Видалити дублікати діалогове вікно, перевірте Колонка ім'я, яке потрібно видалити дублікатами, див. скріншоти:
5. Потім натисніть OK, дублікати в Стовпець А було видалено, а відсутні числа у списку послідовностей вставлені, див. знімок екрана:
Вставте відсутні цифри для послідовності з кодом VBA
Якщо ви вважаєте, що з наведеними вище методами існує так багато кроків, тут також є код VBA, який допоможе вам вирішити цю проблему. Будь ласка, виконайте наступне:
1. Утримуйте клавішу ALT + F11 і відкриває Microsoft Visual Basic для додатків вікна.
2. Клацання Insert > Модуліта вставте наступний код у Модулі вікна.
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 клавіші, і відкриється a Microsoft Visual Basic для додатків вікна.
2. Клацання Insert > Модуліта вставте наступний код у Модулі вікна.
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 для Excel
Тут я представляю простий та зручний інструмент - Kutools для Excel, З його Знайдіть відсутній порядковий номер Ви можете швидко вставити відсутній порядковий номер або порожні рядки між існуючою послідовністю даних.
Kutools для Excel : з більш ніж 300 зручними надбудовами Excel, які можна спробувати без обмежень протягом 30 днів. |
Якщо ви встановили Kutools для Excel, будь ласка, виконайте наступне:
1. Виберіть послідовність даних, до якої потрібно вставити відсутні числа.
2. Клацання Кутулс > Insert > Знайдіть відсутній порядковий номер, див. скріншот:
3, в Знайдіть відсутній порядковий номер діалогове вікно, поставте галочку Вставка відсутнього порядкового номера вставити пропущені цифри або Iвставка порожніх рядків при зустрічі відсутніх порядкових номерів щоб вставити порожні рядки, як вам потрібно. Дивіться знімок екрана:
4. А потім клацніть OK , а відсутні дані порядкових номерів або порожні рядки були вставлені в дані, див. скріншоти:
Завантажте та безкоштовну пробну версію Kutools для Excel зараз!
Демо: вставте пропущені числа або порожні рядки для послідовності за допомогою Kutools для Excel
Пов'язана стаття:
Як визначити послідовність пропущених чисел у Excel?
Найкращі інструменти продуктивності офісу
Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу. Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...
Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу
- Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
- Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
- Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!