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


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

Если у вас есть Kutools for Excel's Различать различия вы можете быстро вставить разрыв страницы, пустые строки, нижнюю границу или цвет заливки, когда значение изменяется по мере необходимости. См. Демонстрацию ниже.         Нажмите, чтобы загрузить Kutools for Excel!


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

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

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

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

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

4. Нажмите Найти все кнопку, а затем нажмите Ctrl + для выбора всех результатов поиска были выбраны сразу все ячейки FALSE, см. снимок экрана:

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.


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

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

Советы:Чтобы применить это Различать различия функция, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Нажмите Кутулс > Формат > Различать различия, см. снимок экрана:

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

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

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

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


Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

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

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Сортировать комментарии по
Комментарии (16)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
У меня есть столбец с числами 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 WorkRng = Application.Selection Установите WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) Application.ScreenUpdating = False For 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 после каждого разрыва... Спасибо
Этот комментарий был сведен к минимуму модератором на сайте
Привет всем спасибо!! это здорово, ребята, не могли бы вы также дать мне, как вставить 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. Отсортируйте всю колонку A
8. Удалить столбец А

Кажется, что много шагов, но это занимает всего несколько секунд.
Этот комментарий был сведен к минимуму модератором на сайте
Код VBA сработал с первого раза и сделал именно то, что я пытался сделать. Большое спасибо!
Этот комментарий был сведен к минимуму модератором на сайте
RE: вставлять пустые строки при изменении значения с помощью кода vba
Есть ли способ сохранить диапазон и не выбирать его каждый раз, когда я его запускаю?
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте,
Это очень полезно, если мне нужно вставить 1 строку, но если мне нужно вставлять 145 строк каждый раз, когда данные изменяются в столбце spwcific, как я могу это сделать??
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Хасан,
Чтобы вставить несколько пустых строк при изменении значения в определенном столбце, вы должны применить следующий код VBA:

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

Подпрограмма InsertRowsAtValueChange()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Установите WorkRng = Application.Selection
Установите WorkRng = Application.InputBox ("Диапазон", xTitleId, WorkRng.Address, Type: = 8)
Приложение.ScreenUpdating = False
Для 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, но я считаю, что если вы хотите добавить больше строк под данные, которые уже имеют интервал, должен быть способ игнорировать пробелы.

Можно ли добавить строку для игнорирования или пропуска пустых строк? Это может сделать этот код более универсальным и повторяемым, если это необходимо. Также может быть полезна функция удаления, похожая на эту, поэтому отмена не требуется.
Этот комментарий был сведен к минимуму модератором на сайте
Можно ли адаптировать метод VBA для игнорирования пустых ячеек? У меня есть файл, в который мне нужно вставить строки на основе двух разных столбцов, но когда я запускаю макрос во втором столбце, я получаю три пустые строки, где первый макрос запускает вставленные строки.
Или он может работать на двух столбцах одновременно?
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Хасан,

Этот vba потрясающий, тогда как мне нужен еще один альтернативный код для вставки одной пустой строки после изменения порядковых номеров. Пример: - В столбце с числовыми сериями, такими как 1, 2, 3, 5, 6, 9 и т. Д.
Нужно добавить одну пустую строку между 3 и 5, а также 6 и 9.
Пожалуйста, вы можете с этим.
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места

Подписывайтесь на Нас

Copyright © 2009 - www.extendoffice.ком. | Все права защищены. Питаться от ExtendOffice, | Карта сайта
Microsoft и логотип Office являются товарными знаками или зарегистрированными товарными знаками Microsoft Corporation в США и / или других странах.
Защищено Sectigo SSL