Как удалить большое количество строк в Excel

Обновлено: 29.06.2024

У меня есть большое количество строк и столбцов (например, 500 тыс. строк и 20 столбцов), заполненных числами.

Я пытаюсь удалить все данные в столбце I, которые имеют определенное значение (например, меньше или равно 8), но когда я пытаюсь использовать автофильтр для удаления значений, Excel зависает и не удаляется .

Это работает быстро для данных в столбце A. Я переделал аналогичные данные на новом листе, чтобы убедиться, что все ячейки заполнены, столбцы/строки не скрыты и т. д.

Почему зависает столбец I?

По моему опыту, самый быстрый способ удалить такой объем данных – создать вспомогательный столбец, в котором будет указано пустое значение или "", если строка должна быть удалена, и любое другое непустое значение, если вы хотите его сохранить, а затем используйте цикл с Cells(Counter, Aux_Column).SpecialCells(xlCellTypeBlanks).EntireRow.Delete, который удалит все строки пустых ячеек.

Проверьте этот пост. В этом файле с 6400 строками: xlCellTypeBlanks заняло 53 секунды, фильтрация 74 секунды, матрица 94 секунды и цикл по ячейке, как вы предлагаете, 64 секунды. Зависит от вашего компьютера, вы можете уменьшить это время, но для удаления время, затрачиваемое стратегией, составляет: Матрица > Фильтр > Цикл/Удалить по ячейке > Удалить пустым. Удалить пробелом, это быстрее всего.

@newtovba на SO много сообщений об удалении строк, некоторые хорошие, некоторые не очень. Двумя распространенными являются автофильтр (который вы используете) и построение диапазона с помощью Union (с одним из которых вас связал Дэвид). Для набора данных такого размера и такого количества удалений метод Union может оказаться не таким быстрым. Причина в том, что Union плохо масштабируется, так как диапазон, объединенный в Union, становится больше (точнее, по мере увеличения количества несмежных диапазонов, объединенных в Union), время, затрачиваемое на добавление каждой дополнительной строки, увеличивается. Так что к тому времени, как вы доберетесь до 130-го ряда, Union будет очень медленным

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

4 ответа 4

На SO много сообщений об удалении строк, некоторые хорошие, некоторые не очень.

Двумя распространенными являются автофильтр (который вы используете) и построение диапазона с помощью Union (с одним из которых вас связал Дэвид).

Для набора данных такого размера и такого количества удалений вы обнаружите, что любой метод, использующий ссылки на методы рабочего листа Excel (например, автофильтр, поиск, сортировка, объединение, формула и т. д.), работает медленно. Некоторые из них будут лучше, чем другие, в зависимости от точного характера ваших данных.

Есть еще один метод, который может вам подойти. То есть на самом деле не удалить строки, а перезаписать данные измененной версией.

Обратите внимание, что это работает только в том случае, если у вас НЕТ формул (ни на этом листе, ни на любом другом), которые ссылаются на обрабатываемые данные.

Я запустил этот код на примере набора данных из 500 тыс. строк, 20 столбцов со случайными числами 1..32 (таким образом, удалено около 25% строк)

Нажмите F5 или Ctrl+G, чтобы активировать диалоговое окно «Перейти». Введите 501:10000 в поле «Ссылка», затем нажмите «Ввод» или нажмите «ОК». Либо щелкните правой кнопкой мыши в любом месте выделенного диапазона и выберите "Удалить" в контекстном меню, либо нажмите верхнюю половину кнопки "Удалить" в группе "Ячейки" на вкладке "Главная" ленты.

Как удалить 50000 строк в Excel?

1:092:49Как удалить строку в Excel – YouTubeYouTubeНачало предложенного клипаКонец предложенного клипаНажмите левую кнопку мыши, затем удерживайте и перетащите через все строки, которые вы хотите удалить.ЕщеНажмите левую кнопку мыши, затем удерживайте и перетащите через все строки, которые вы хотите удалить.

Как удалить тысячи столбцов в Excel?

Чтобы удалить ненужные строки и столбцы в электронной таблице, просто выделите строку или столбец, щелкнув маркер в верхней части столбца или слева от строки, просто щелкните его правой кнопкой мыши и выберите "Удалить". Надеюсь, это поможет вам.

Как избавиться от бесконечных столбцов в Excel?

1:012:51Изучение Excel – Удаление пустых столбцов – Подкаст 2171 – YouTubeYouTube

Как удалить бесконечные строки и столбцы в Excel?

Удалить бесконечные строки из Excel

  1. Выберите весь диапазон и примените автофильтр.
  2. Нажмите раскрывающийся список автофильтра.
  3. Снимите все флажки, а затем установите флажок только для пробелов (чтобы были видны только пробелы).
  4. Если вы хотите удалить целые строки пробелов, а в некоторых столбцах есть данные, повторите 2 и 3 для всех столбцов данных.

Почему Excel показывает так много строк?

Два способа исправить это: выберите все столбцы и/или строки за пределами области, в которой существуют ваши данные, затем щелкните правой кнопкой мыши и удалите строки/столбцы. Если это просто статические данные, которые не охватывают вкладки, может быть проще просто выделить данные, скопировать и вставить на новую вкладку. Затем удалите старую вкладку.

Как быстро выбрать тысячи строк в Excel?

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

Как уменьшить количество строк в Excel?

Нажмите и удерживайте клавиши Shift и Ctrl на клавиатуре. В то же время нажмите клавишу со стрелкой вниз на клавиатуре, чтобы выбрать все строки от строки 31 до нижней части рабочего листа. Отпустите все клавиши.

Как удалить бесконечные пустые строки в Excel?

Удалить бесконечные пустые строки с помощью Go Special и Delete

  1. Выделите весь лист, нажав клавиши Alt + A, затем нажмите клавиши Ctrl + G, чтобы открыть диалоговое окно "Перейти", а затем нажмите "Специальные".
  2. В диалоговом окне «Перейти к специальному» установите флажок «Пробелы». …
  3. Нажмите "ОК", теперь все пустые ячейки в пустых строках выбраны.

Как выбрать 5000 строк в Excel?

Для Excel (по крайней мере, Windows 10, Office 365) это очень просто.

  1. Нажмите, чтобы выбрать ячейку в таблице данных.
  2. Нажмите клавишу "Конец", чтобы указать Excel, что вы хотите выбрать все ячейки до конца таблицы данных.
  3. Теперь нажмите и удерживайте клавишу "Shift", а затем нажмите одну из четырех клавиш со стрелками.

Как удалить все под строкой в ​​Excel?

  1. Выберите все столбцы справа от последнего столбца, содержащего данные, или выберите все строки ниже последней строки, содержащей данные. …
  2. На вкладке "Главная" в группе "Редактирование" нажмите стрелку рядом с кнопкой "Очистить", затем выберите "Очистить все".
  3. Сохраните лист и закройте его.

Как выбрать 50000 строк в Excel?

Для Excel (по крайней мере, Windows 10, Office 365) это очень просто.

  1. Нажмите, чтобы выбрать ячейку в таблице данных.
  2. Нажмите клавишу "Конец", чтобы указать Excel, что вы хотите выбрать все ячейки до конца таблицы данных.
  3. Теперь нажмите и удерживайте клавишу "Shift", а затем нажмите одну из четырех клавиш со стрелками.

Как выбрать 20000 строк в Excel?

Выберите одну или несколько строк и столбцов

  1. Нажмите букву вверху, чтобы выбрать весь столбец. Или щелкните любую ячейку в столбце, а затем нажмите Ctrl + Пробел.
  2. Выберите номер строки, чтобы выбрать всю строку. …
  3. Чтобы выбрать несмежные строки или столбцы, удерживайте клавишу Ctrl и выберите номер строки или столбца.

Как вы массово удаляете строки в числах?

0:492:00Как быстро удалить пустые строки Mac – YouTubeYouTube

Как выбрать 100 000 строк в Excel?

Например, чтобы выбрать первые 100 000 строк, введите 1:100000 (или A1:D100000 и т. д.). Вы также можете делать разные вещи, например нажимать Shift + Space и Ctrl + Shift + Down, предполагая, что 100000 — это конец, а ваш диапазон является непрерывным, Ctrl + A, чтобы выбрать весь используемый диапазон и т. д.

Как выбрать 1000 строк за раз в Excel?

Выберите одну или несколько строк и столбцов. Нажмите букву вверху, чтобы выбрать весь столбец. Или щелкните любую ячейку в столбце, а затем нажмите Ctrl + пробел. Выберите номер строки, чтобы выбрать всю строку. Или щелкните любую ячейку в строке, а затем нажмите Shift + Space.

Милан с детства увлекался ПК, и это побудило его проявить интерес ко всем технологиям, связанным с ПК. До прихода в WindowsReport он работал веб-разработчиком. Подробнее

  • Существует множество советов и приемов, которые можно использовать в Microsoft Excel.
  • В приведенном ниже руководстве показано, как можно удалить сразу несколько строк.
  • Дополнительные статьи об этом замечательном программном обеспечении можно найти в нашем специальном центре MS Excel.
  • Чтобы найти больше полезных руководств, посетите нашу страницу руководств по Microsoft Office.

Как удалить несколько строк в Microsoft Excel сразу

Для решения различных проблем с ПК мы рекомендуем DriverFix:
Это программное обеспечение будет поддерживать работоспособность ваших драйверов, тем самым защищая вас от распространенных компьютерных ошибок и сбоев оборудования. Проверьте все свои драйверы прямо сейчас, выполнив 3 простых шага:

Microsoft Excel — одна из популярных программ, которую используют миллиарды людей для управления своими данными и счетами. Он используется предприятиями для отслеживания своих финансов, запасов и многого другого.

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

Один из пользователей, у которого возникли проблемы при удалении нескольких строк, сообщил на форумах.

Как удалить несколько строк в Excel? Если я отмечу все и попытаюсь удалить, он просто удалит одну строку.Даже если я щелкну правой кнопкой мыши и выберу «Удалить», он все равно удалит одну строку за раз. Мне нужно удалить все строки, отдельно выделенные с помощью Ctrl, за один раз.

К счастью, существует множество способов удаления нескольких строк из листа Excel за один раз.

Как удалить несколько строк в Excel?

1. Удалить несколько строк через контекстное меню

2. Удалить несколько строк с помощью сочетания клавиш

Этот метод применяется как к нескольким последовательным, так и к непоследовательным строкам.

3. Удалить несколько строк с помощью выбора ячейки

4. Удалить несколько строк, запустив макрос

  1. Выберите целевые строки, которые хотите удалить.
  2. Откройте редактор VBA, нажав клавиши Alt+F11 на клавиатуре.
  3. Нажмите первый проект на левой панели.
  4. Перейдите к строке меню и выберите "Вставить>> Модуль".
  5. Вставьте следующий код в новое окно, чтобы выбрать несколько ячеек для удаления:
    • Подпрограмма Delete_Rows()
    • удалить несколько строк (строки 4, 5 и 6)
    • Рабочие листы("Лист1").Range("C4:C6").EntireRow.Delete
    • Конец подписки

запустить скрипт макроса удалить несколько строк в excel

Измените ссылки на номера строк («C4:C6»), чтобы выбрать определенные строки, которые вы хотите удалить. Например, если вы хотите удалить непоследовательные строки (3,7,8), вы можете указать диапазон как («C3,C7,C8»). Укажите имя рабочего листа, изменив Лист1.

Наконец, нажмите кнопку "Выполнить" или нажмите клавишу F5, чтобы запустить скрипт.

Какой из следующих методов вам помог? Дайте нам знать в разделе комментариев ниже.

idee restoro< бр />

По-прежнему возникают проблемы? Исправьте их с помощью этого инструмента:

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

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

В этом посте мы рассмотрим 9 способов удаления пустых строк из данных Excel.

Удалить пустые строки вручную

Первый способ — ручной.

Не волнуйтесь, позже мы перейдем к более простым методам. Но если у нас всего пара строк, ручной способ может быть быстрее.


Выберите пустые строки, которые мы хотим удалить. Удерживая нажатой клавишу Ctrl, щелкните строку, чтобы выбрать ее.

< бр />

Когда строки, которые мы хотим удалить, выбраны, мы можем щелкнуть правой кнопкой мыши и выбрать в меню Удалить.


Мы также можем удалять строки с помощью команды на ленте. Перейдите на вкладку «Главная» ➜ нажмите команду «Удалить» ➜, затем выберите «Удалить строки листа».


Есть также очень удобная комбинация клавиш для удаления строк (столбцов или ячеек). Нажмите Ctrl + – на клавиатуре.

Вот оно! Наших пустых строк больше нет.

Удалить пустые строки с помощью функции «Перейти к специальному»

Выбор и удаление строк вручную допустимо, если нам нужно удалить только пару строк.

Что делать, если в наших данных есть много пустых строк? Выбор вручную был бы мучением!

Не волнуйтесь, в Excel есть команда, которая выделяет для нас все пустые ячейки.


< /p>

Во-первых, нам нужно выбрать столбец наших данных, включая все пустые строки. Самый простой способ сделать это — выделить первую ячейку (в данном примере A1), затем, удерживая клавишу Shift, выбрать последнюю ячейку (в данном примере A14).


Теперь мы можем использовать команду «Перейти к специальному», чтобы выбрать только пустые ячейки. Перейдите на вкладку «Главная» ➜ нажмите команду «Найти и выбрать» ➜ выберите «Перейти к специальному» в меню.


Есть также удобное сочетание клавиш для меню "Перейти". Нажмите Ctrl + G, чтобы открыть меню «Перейти», затем нажмите кнопку «Специальные», чтобы открыть меню «Перейти к специальным».


Независимо от того, откроем ли мы меню «Перейти» и нажмем «Специальные» или перейдем непосредственно к меню «Перейти к специальным», мы попадем в одно и то же меню «Перейти к специальным».

Теперь все, что нам нужно сделать, это выбрать «Пробелы» в параметрах и нажать кнопку «ОК». Это выберет только пустые ячейки из нашего первоначального выбора столбца.


< /p>

Теперь нам нужно удалить эти выбранные строки.

  1. Используйте любой метод удаления строк из раздела "Удаление пустых строк вручную".
    • Щелкните правой кнопкой мыши ➜ Удалить
    • Вкладка "Главная" ➜ "Удалить" ➜ "Удалить строки листа".
    • Ctrl + – сочетание клавиш
  2. В меню "Удалить" выберите "Вся строка" и нажмите кнопку "ОК".

Как по волшебству, мы можем найти и удалить сотни пустых строк в наших данных за несколько секунд. Это особенно удобно, когда у нас много пустых строк, разбросанных по длинному набору данных.

Удалить пустые строки с помощью команды «Найти»

Этот метод будет очень похож на описанный выше метод удаления пустых строк с помощью перехода к специальному методу. Единственная разница в том, что мы будем выбирать пустые ячейки с помощью команды «Найти».

Как и раньше, нам нужно выбрать столбец в наших данных.


< /p>

Перейдите на вкладку "Главная" ➜ нажмите команду "Найти и выбрать" ➜ выберите "Найти" в меню.


Есть также сочетание клавиш, которое мы можем использовать, чтобы открыть меню поиска. Нажмите Ctrl + F на клавиатуре.


В любом случае это откроет для нас меню "Найти и заменить".

  1. Разверните дополнительные параметры в меню поиска.
  2. Оставьте поле ввода «Найти» пустым.
  3. Выберите параметр «Соответствовать всему содержимому ячейки».
  4. Поиск на листе.
  5. Посмотрите на значения.
  6. Нажмите кнопку "Найти все", чтобы вернуть все пустые ячейки.


Появится список всех пустых ячеек, найденных в выбранном диапазоне, в нижней части меню "Найти".

Мы можем выбрать их все, нажав Ctrl + A. Затем мы можем закрыть меню «Найти», нажав кнопку «Закрыть». Теперь мы можем удалить все пустые ячейки, как и раньше.

Удалить пустые строки с помощью фильтров

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

< бр />

Во-первых, нам нужно добавить фильтры к нашим данным.

  1. Выберите весь диапазон данных, включая пустые строки.
  2. Перейдите на вкладку "Данные".
  3. Нажмите кнопку "Фильтр" в разделе "Сортировка и фильтр".


Мы также можем добавить фильтры к диапазону, используя сочетание клавиш Ctrl + Shift + L.


< /p>

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

  1. Нажмите переключатель фильтра в одном из столбцов.
  2. Используйте переключатель "Выбрать все", чтобы отменить выбор всех элементов.
  3. Проверьте пробелы.
  4. Нажмите кнопку ОК.


< /p>

Когда наши данные отфильтрованы, номера строк отображаются синим цветом, а в отфильтрованных строках отсутствуют номера.

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

Затем мы можем нажать кнопку OK, когда Excel спросит нас, хотим ли мы удалить всю строку листа.

Когда мы очистим фильтры, все наши данные останутся, но без пустых строк!


Мы можем использовать фильтры немного по-другому, чтобы избавиться от пустых строк. На этот раз мы будем фильтровать пробелы. Щелкните переключатель фильтра в одном из столбцов ➜ снимите флажок «Пробелы» ➜ нажмите кнопку «ОК».

< бр />

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

Удалить пустые строки с помощью расширенных фильтров

Подобно методу фильтрации, мы можем использовать параметр «Расширенные фильтры», чтобы получить копию наших данных за вычетом пустых строк.


Чтобы использовать функцию расширенных фильтров, нам потребуется выполнить небольшую настройку.

  1. Нам нужно настроить диапазон критериев фильтрации. Мы собираемся фильтровать только на основе одного столбца, поэтому нам нужен один заголовок столбца из наших данных (в этом примере F1). Под заголовком столбца нам нужны наши критерии (в этом примере F2), нам нужно ввести ="" в эту ячейку в качестве наших критериев.
  2. Выберите диапазон данных для фильтрации.
  3. Перейдите на вкладку "Данные".
  4. Выберите «Дополнительно» в разделе «Сортировка и фильтрация».


Теперь нам нужно настроить меню расширенного фильтра.

  1. Выберите Копировать в другое место.
  2. Выберите диапазон данных для фильтрации. Это поле уже должно быть заполнено, если диапазон был выбран до открытия меню расширенных фильтров.
  3. Добавьте критерии в диапазон критериев (в данном примере F1:F2).
  4. Выберите место на листе для копирования отфильтрованных данных.
  5. Нажмите кнопку ОК.

Теперь мы получаем копию наших данных в новом месте без пробелов.

Удалить пустые строки с помощью функции фильтра

Если мы используем Excel онлайн или Excel для Office 365, мы можем использовать одну из новых функций динамического массива, чтобы отфильтровать наши пустые строки.

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

Синтаксис функции FILTER

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

Функция FILTER для фильтрации пробелов


Вышеуказанная функция должна быть введена только в одну ячейку, и результаты будут распространяться на остальные ячейки по мере необходимости. Функция отфильтрует CarData в столбце Make и отфильтрует все пробелы.

Это просто, а главное, динамично. Поскольку наши данные находятся в таблице Excel, когда мы добавляем новые данные в таблицу CarData, они появляются в наших отфильтрованных результатах.

Удалить пустые строки путем сортировки


В дополнение ко всем методам фильтрации мы можем отсортировать наши данные, чтобы получить все пустые строки.

  1. Выберите диапазон данных.
  2. Перейдите на вкладку "Данные".
  3. Нажмите команду сортировки. Подойдет как восходящий, так и нисходящий порядок.


Теперь все наши пустые строки появятся внизу, и мы можем их игнорировать.

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

Удалить пустые строки с помощью Power Query

Power может легко удалить пустые строки в наших данных.

Это здорово, поскольку мы постоянно получаем обновленные данные с пробелами и должны учитывать это на этапах подготовки данных.


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

  1. Перейдите на вкладку "Главная" в редакторе расширенных запросов.
  2. Нажмите кнопку "Удалить строки".
  3. Выберите в меню параметр "Удалить пустые строки".

При этом будет сгенерирован указанный выше M-код с использованием функции Table.SelectRows для выбора ненулевых строк. Это приведет к удалению только тех строк, в которых вся запись имеет нулевые значения.

< бр />

Мы также могли бы получить тот же результат, отфильтровав нулевые значения в наших данных. Щелкните правой кнопкой мыши любой из переключателей сортировки и фильтрации, затем снимите флажок с нулевого значения и нажмите кнопку ОК.

Запрос Power снова создаст шаг с функцией Table.SelectRows, возвращая ненулевые значения в определенном столбце.

Удалить пустые строки с помощью Power Automate

Возможно, этот способ не такой быстрый, простой и практичный, как другие методы, но его можно сделать.

Мы можем использовать Power Automate для удаления пустых строк в наших таблицах Excel.

< бр />

Чтобы сделать это с помощью Power Automate, нам потребуется иметь данные в таблице Excel, а также столбец идентификатора, который однозначно идентифицирует каждую строку.

< бр />

Для этого мы можем настроить небольшую автоматизацию Flow.

  1. Мы будем использовать ручную кнопку для запуска потока, но мы можем использовать любое количество триггеров.
  2. Затем нам нужно составить список строк, присутствующих в таблице, чтобы получить все строки данных из нашей таблицы Excel. Лучшим вариантом также было бы использовать фильтры Odata в разделе Показать дополнительные параметры для фильтрации пустых строк, но в настоящее время фильтрация пустых строк невозможна.
  3. Поскольку мы не можем фильтровать пустые значения с помощью фильтров Odata, для этого нам нужно использовать действие операции с данными массива Filter. Мы можем отфильтровать значения из строк списка, присутствующих в табличном действии, и установить условие, что Make равно пустому (оставьте значение пустым). Это даст нам все строки с пустыми ячейками.
  4. Теперь мы можем использовать действие "Удалить строку", чтобы удалить эти пустые строки. Мы можем выбрать наш столбец идентификатора в качестве ключевого столбца, а затем добавить поле идентификатора из действия массива фильтров. Это должно обернуть действие в Применить к каждому шагу, чтобы удалить все пробелы.

При запуске этой автоматизации все пустые строки в таблице будут удалены.

Выводы

Пустые строки в наших данных могут доставлять неудобства.

Удалить их легко, и у нас есть много вариантов.

Возможно, мой любимый способ – это сочетание клавиш "Перейти". Это быстро, просто и эффективно.

Я пропустил какие-либо методы? Дайте мне знать в комментариях ниже!

Об авторе

Джон МакДугалл

Джон — Microsoft MVP, внештатный консультант и тренер, специализирующийся на Excel, Power BI, Power Automate, Power Apps и SharePoint. Другие интересные статьи Джона можно найти в его блоге или на канале YouTube.

Читайте также: