Как вставить пустые строки при изменении значения в Excel?

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

Вставка пустых строк при изменении значения с помощью функции Промежуточный итог

Вставка определенного количества пустых строк при изменении значения с помощью мощной функции

Вставка пустых строк при изменении значения с использованием вспомогательных столбцов

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

Вставка пустых строк при изменении значения с помощью функции Промежуточный итог

С помощью функции Промежуточный итог можно вставлять пустые строки между данными при изменении значения, выполнив следующие шаги:

1. Выберите диапазон данных, который вы хотите использовать.

2. Нажмите Данные > Промежуточный итог, чтобы открыть диалоговое окно Промежуточный итог, и в диалоговом окне Промежуточный итог выполните следующие действия:

1: Выберите имя столбца, по которому вы хотите вставлять пустые строки при изменении значения в разделе При каждом изменении;

2: Выберите Количество из выпадающего списка Использовать функцию;

3: Установите флажок напротив имени столбца, в который вы хотите добавить промежуточный итог, в списке Добавить промежуточный итог к.

3. Затем нажмите OK, строки промежуточных итогов будут вставлены между разными продуктами, а символы контуров отобразятся слева от таблицы, см. скриншот:

4. Затем нажмите цифру 2 в верхней части символа контура, чтобы отобразить только строки промежуточных итогов.

5. Затем выберите диапазон данных промежуточных итогов и нажмите комбинацию клавиш Alt+;, чтобы выбрать только видимые строки, см. скриншот:

6. После выбора только видимых строк нажмите клавишу Delete на клавиатуре, и все строки промежуточных итогов будут удалены.

7. Затем щелкните любую другую ячейку и перейдите Данные > Группировать > Очистить контур, чтобы удалить контуры, см. скриншот:

8. Символы контуров будут очищены сразу, и вы увидите, что пустые строки были вставлены между данными при изменении значения, см. скриншот:

9. В конце вы можете удалить столбец A, если это необходимо.

Вставка определенного количества пустых строк при изменении значения с помощью мощной функции

Если вы устали от вышеупомянутых сложных методов, здесь я представлю полезный инструмент — Kutools для Excel. Функция Отметить изменения в столбце может помочь вам быстро и легко вставлять разрыв страницы, пустые строки, нижнюю границу или цвет заливки при изменении значения ячейки.

После установки Kutools для Excel сделайте следующее:

1. Нажмите Kutools > Формат > Отметить изменения в столбце, см. скриншот:

2. В диалоговом окне Отметить изменения в столбце выполните следующие действия:

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

3. Затем нажмите кнопку Ok, и определенное количество пустых строк будет вставлено в данные при изменении значения ячейки, см. скриншоты:

Нажмите, чтобы скачать Kutools для Excel и начать бесплатную пробную версию прямо сейчас!

Вставка пустых строк при изменении значения с использованием вспомогательных столбцов

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

1. В пустой ячейке C3 введите формулу =A3=A2, а в ячейке D4 введите формулу =A4=A3, см. скриншот:

2. Затем выберите C3:D4 и протяните маркер заполнения до диапазона, к которому вы хотите применить эти формулы, и вы получите Да или Нет в ячейках, см. скриншот:

3. Затем нажмите Ctrl + F, чтобы открыть диалоговое окно Поиск и замена, в появившемся диалоговом окне введите ЛОЖЬ в текстовое поле Найти под вкладкой Найти, и нажмите кнопку Опции, чтобы расширить это диалоговое окно, и выберите Значения из выпадающего списка Искать в, см. скриншот:

4. Нажмите Найти все, а затем нажмите Ctrl + A, чтобы выбрать все результаты поиска, все ячейки с ЛОЖЬ будут выбраны сразу, см. скриншот:

6. Закройте диалоговое окно Поиск и замена, на следующем шаге вы можете вставить пустые строки, нажав Главная > Вставить > Вставить строки листа, и пустые строки будут вставлены в данные при изменении значения на основе столбца A, см. скриншоты:

7. Наконец, вы можете удалить вспомогательные столбцы C и D, если это необходимо.

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

Если вы устали от использования вышеупомянутых методов, вот код, который также поможет вам вставить пустые строки между измененными значениями сразу.

1. Удерживайте клавиши ALT + F11, чтобы открыть окно Microsoft Visual Basic для приложений.

2. Нажмите Вставить > Модуль и вставьте следующий код в окно Модуль.

Код 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, пустые строки будут вставлены между данными при изменении значения на основе столбца A.