Note: The other languages of the website are Google-translated. Back to English

Як вставити порожні рядки при зміні значення в Excel?

Припустимо, у вас є діапазон даних, і тепер ви хочете вставити порожні рядки між даними, коли значення змінюється, щоб ви могли відокремити послідовні однакові значення в одному стовпці, як показано на наступних скріншотах. У цій статті я розповім про деякі трюки для вирішення цієї проблеми.

Вставляйте порожні рядки, коли значення змінюється за допомогою функції Проміжні підсумки

Вставте порожні рядки, коли значення змінюється за допомогою допоміжних стовпців

Вставляйте порожні рядки, коли значення змінюється за допомогою коду VBA

Вставте певну кількість порожніх рядків, коли значення змінюється за допомогою потужної функції


Вставляйте порожні рядки, коли значення змінюється за допомогою функції Проміжні підсумки

З Разом: Ви можете вставити порожні рядки між даними, коли значення змінюється, як описано нижче:

1. Виберіть діапазон даних, який ви хочете використовувати.

2. Клацання дані > Разом: відкрити Разом: і у вікні Разом: діалогове вікно, будь ласка, виконайте такі параметри:

1: Виберіть назву стовпця, до якого потрібно вставити порожні рядки, залежно від того, коли значення змінюється під При кожній зміні в розділі;
2: Виберіть Рахувати від Використовуйте функцію випадаючий список;
3: Позначте назву стовпця, до якого потрібно вставити проміжний підсумок у Додайте проміжну суму до список

3. Потім натисніть OK, між різними продуктами вставлені рядки проміжних підсумків, а символи контурів відображаються зліва таблиці, див. знімок екрана:

4. А потім клацніть цифру 2 у верхній частині символу структури, щоб просто відобразити лише проміжні підсумки.

5. Потім виберіть дані про проміжний підсумок і натисніть Alt +; комбінації клавіш, щоб вибрати лише видимі рядки, див. знімок екрана:

6. Вибравши лише видимі рядки, натисніть кнопку видаляти клавішу на клавіатурі, і всі проміжні підсумки видалено.

7. Потім клацніть будь-яку іншу комірку і поверніться назад дані > Ungroup > Чіткий контур щоб видалити Контури, див. знімок екрана:

8. Символи контуру були очищені відразу, і ви можете бачити, що між даними вставляються порожні рядки, коли значення змінюється, див. Знімок екрана:

9. Нарешті, ви можете видалити стовпець А, як вам потрібно.


Вставте розрив сторінки, порожні рядки, нижню межу або колір заливки, коли значення швидко змінюється

Якщо у вас є Kutools для Excel's Розрізняють відмінності функцію, ви можете швидко вставити розрив сторінки, порожні рядки, нижню межу або колір заливки, коли значення змінюється, як вам потрібно. Дивіться нижче демонстраційну версію.         Клацніть, щоб завантажити Kutools для Excel!


Вставте порожні рядки, коли значення змінюється за допомогою допоміжних стовпців

За допомогою допоміжних стовпців ви можете спочатку вставити формулу, а потім застосувати Знайти і замінити функція, нарешті, вставте порожні рядки між змінними значеннями. Будь ласка, виконайте наступне:

1. В пусту клітинку С3 введіть цю формулу = A3 = A2, а в комірку D4 введіть цю формулу = A4 = A3, див. скріншот:

2. Потім виберіть C3: D4 і перетягніть маркер заповнення до діапазону, до якого ви хочете застосувати формули тез, і ви отримаєте Правда or Помилковий у клітинках див. знімок екрана:

3. А потім натисніть Ctrl + F ключі, щоб відкрити Знайти і замінити діалогове вікно, у спливаючому діалоговому вікні введіть ПОМИЛКОВИЙ в Знайти те, що текстове поле під знайти та натисніть опції , щоб розгорнути це діалогове вікно, і виберіть Цінності від Заглядати випадаючий список, див. знімок екрана:

4. Натисніть Знайти все , а потім натисніть Ctrl + A щоб вибрати всі результати пошуку, відразу були вибрані всі FALSE комірки, див. знімок екрана:

6. Закрийте Знайти і замінити на наступному кроці ви можете вставити порожні рядки, клацнувши Home > Insert > Вставити рядки аркуша, а порожні рядки були вставлені в дані при зміні значення на основі стовпця A, див. скріншоти:

7. Нарешті, ви можете видалити допоміжні стовпці C та D, як вам потрібно.


Вставляйте порожні рядки, коли значення змінюється за допомогою коду VBA

Якщо ви втомилися використовувати вищезазначені методи, ось код також може допомогти вам вставити порожні рядки між зміненими значеннями одночасно.

1. Утримуйте клавішу ALT + F11 ключі, щоб відкрити Вікно Microsoft Visual Basic для програм.

2. Клацання Insert > Модуліта вставте наступний код у Вікно модуля.

Код VBA: вставляйте порожні рядки, коли значення змінюється

Sub InsertRowsAtValueChange()
'Update by Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 2 Step -1
    If WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value Then
        WorkRng.Cells(i, 1).EntireRow.Insert
    End If
Next
Application.ScreenUpdating = True
End Sub

3. Потім натисніть F5 клавішу для запуску цього коду, і з'явиться вікно запиту, яке дозволить вам вибрати дані одного стовпця, які ви хочете вставити порожні рядки при зміні значення, яке ви базуєте, див. знімок екрана:

4. А потім клацніть OK, порожні рядки були вставлені між даними, коли значення змінюється на основі стовпця А.


Вставте певну кількість порожніх рядків, коли значення змінюється за допомогою потужної функції

Якщо вас спробували з наведеними вище клопіткими методами, тут я запропоную корисний інструмент, Kutools для Excel's Розрізняють відмінності може допомогти вам вставити розрив сторінки, порожні рядки, нижню межу або колір заливки, коли значення комірки змінюється швидко і легко.

Порада:Щоб застосувати це Розрізняють відмінності По-перше, вам слід завантажити Kutools для Excel, а потім швидко та легко застосувати функцію.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Клацання Кутулс > сформований > Розрізняють відмінності, див. скріншот:

2, в Розрізнити відмінності за ключовим стовпцем діалоговому вікні, виконайте такі дії:

  • Виберіть діапазон даних, який ви хочете використовувати, а потім виберіть ключовий стовпець, на основі якого потрібно вставити порожні рядки;
  • Потім перевірити Пустий ряд опція від опції розділу та введіть кількість порожніх рядків, які потрібно вставити.

3. Потім натисніть Ok , а конкретна кількість порожніх рядків було вставлено в дані, якщо значення комірки змінюється, див. скріншоти:

Натисніть, щоб завантажити Kutools для Excel та безкоштовну пробну версію зараз!


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

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

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (15)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
У мене є стовпець із числами 5,4,3,2 та 1 (їх БАГАТО), і я хочу відокремити п’ятірки від інших, наприклад: 5 5 5 4 3 2 1 5 5 5 5 5 4 3 2 1 Числа розташовані в порядку опису, і завжди є чотири нижче п'яти, тому я спробував: Sub InsertRowsAtValueChange() 'Update 20140716 Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkR. Установіть WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) Application.ScreenUpdating = False Для i = WorkRng.Rows.Count до 2 Крок -1 Якщо WorkRng.Cells(i, 1). Значення = 5 І WorkRng.Cells(i - 1, 1).Value = 4 Потім WorkRng.Cells(i, 1).EntireRow.Insert End If Next Application.ScreenUpdating = True End Sub Але це не спрацювало. Можеш мені допомогти?
Авнер
Цей коментар був мінімізований модератором на сайті
Формула працює чудово, однак є кілька прикладів, коли вона не розпізнає
Сем Джей Кармін
Цей коментар був мінімізований модератором на сайті
Щиро дякую, дивився всюди, і це було єдине рішення, яке спрацювало для мене, яке також було простою англійською. Усі інші веб-сайти з підручниками Excel не розбивали це покроково, як це робили ви, дуже дякую. ура :)
Саул
Цей коментар був мінімізований модератором на сайті
Щиро дякую за опис кількох варіантів вирішення проблеми. Я використовував Visual Basic і змінив [quote]WorkRng.Cells(i, 1).EntireRow.Insert[/quote] на [quote]With WorkRng.Cells(i, 1).EntireRow.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlMedium End With[/quote] Це накреслить лінії між рядками зі змінними значеннями замість порожніх рядків.
Іво Тамм
Цей коментар був мінімізований модератором на сайті
Де в коді мені потрібно змінити, щоб включити більше одного рядка, мені потрібно додати 10 після кожної перерви... Дякую
Rafael
Цей коментар був мінімізований модератором на сайті
Привіт всім дякую!! це чудово, ви також можете дозволити мені вставити 2 рядки, коли значення змінюється у VBA або через excel.
Гудзон
Цей коментар був мінімізований модератором на сайті
Привіт, це майже корисно! Перший метод для мене не працює, тому що, коли я чітко виконую ці кроки, дані, які я видаляю на панелях проміжних підсумків, видаляють усі стовпці, які я відсортував. У другому методі, коли я переходжу до кроку, де я вставляю рядки аркуша, рядки вставляються НАД клітинками FALSE, що розбиває дані, але останній вибір кожної групи потім додається до групи нижче. Будь-яка порада???
Девіс
Цей коментар був мінімізований модератором на сайті
[quote]Привіт всім дякую!! це чудово, ви також можете дозволити мені вставити 2 рядки, коли значення змінюється у VBA або через excel.За Хадсоном[/quote] Скажіть, будь ласка, як вставити більше 1 рядка.
Мадхур
Цей коментар був мінімізований модератором на сайті
Я використовую своє власне рішення деякий час.
1. Вставте допоміжний стовпець у стовпець A
2. У A2 введіть "if(B2=B1,A1,A1+1)"
3. Скопіюйте цю формулу до останнього рядка
4. Скопіюйте всі заповнені клітинки в стовпці A та вставте спеціальні (значення) поверх них
5. Скопіюйте всі клітинки ще раз і вставте їх у стовпець A першої незаповненої клітинки (наприклад, якщо у вас є 104 рядки даних плюс рядок заголовка, ви вставте їх у клітинку A106)
6. Натисніть «Дані та видалити копії» (лише в клітинках, які ви щойно вставили на кроці 5; не в усіх рядках)
7. Відсортуйте весь стовпець А
8. Видалити стовпець A

Здається, що кроків багато, але це займає лише кілька секунд.
Стів
Цей коментар був мінімізований модератором на сайті
Код VBA спрацював у перший раз і зробив саме те, що я намагався зробити. Дуже дякую!
Ерік Ф
Цей коментар був мінімізований модератором на сайті
RE: вставляти порожні рядки, коли значення змінюється за допомогою коду vba
Чи є спосіб зберегти діапазон і не вибирати його щоразу, коли я запускаю його?
Suzi
Цей коментар був мінімізований модератором на сайті
Здравствуйте,
Це дуже корисно, якщо мені потрібно вставити 1 рядок, але якщо мені потрібно вставити 145 рядків щоразу, коли дані змінюються в стовпці spwcific, як я можу це зробити??
Хасан
Цей коментар був мінімізований модератором на сайті
Привіт, Хасан,
Щоб вставити кілька порожніх рядків, коли значення змінюється в певному стовпці, слід застосувати такий код VBA:

Примітка: у коді нижче ви повинні змінити число 99 відповідно до ваших потреб, наприклад, коли ви вставляєте 145 порожніх рядків, ви повинні змінити число 99 на 144. Спробуйте, сподіваюся, це допоможе вам!

Sub InsertRowsAtValueChange()
Dim Rng як дальність
Dim WorkRng як діапазон
On Error Resume Next
xTitleId = "KutoolsforExcel"
Встановіть WorkRng = Application.Selection
Встановіть WorkRng = Application.InputBox("Діапазон", xTitleId, WorkRng.Address, Тип:=8)
Application.ScreenUpdating = Невірний
Для i = WorkRng.Rows.Count To 2 Крок -1
Якщо WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value Тоді
Діапазон(WorkRng.Cells(i, 1).EntireRow, WorkRng.Cells(i + 99, 1).EntireRow).Insert
End If
Далі
Application.ScreenUpdating = True
End Sub
скайян
Цей коментар був мінімізований модератором на сайті
Дуже корисний. Код, який skyyang показує вище, працював ідеально. Просто переконайтеся, що в даних ще немає пробілів.

Я не розумію VBA, але я вважаю, що якщо ви хочете додати більше рядків під дані, які вже мають пробіли, має бути спосіб ігнорувати пробіли.

Чи можна додати рядок, щоб ігнорувати або пропускати порожні рядки? Це може зробити цей код більш універсальним і повторюваним, якщо це необхідно. Також функція видалення, подібна до цієї, може бути корисною, тому скасування не потрібно.
Dcalk
Цей коментар був мінімізований модератором на сайті
Чи можна адаптувати метод VBA, щоб ігнорувати порожні клітинки? У мене є файл, у який мені потрібно вставити рядки на основі двох різних стовпців, але коли я запускаю макрос у другому стовпці, у мене виходять три порожні рядки, де перший макрос запускає вставлені рядки.
Або він може працювати на двох стовпцях одночасно?
Грабувати
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0  Персонажі
Рекомендовані місця