Как удалить миллион пустых строк в Excel

Обновлено: 02.07.2024

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

Обычно я понимаю это, потому что некоторые операции, начиная от сохранения и заканчивая небольшими правками, начинают занимать слишком много времени. Раньше я думал, что это «виртуальные строки» (раздражает тем, что мешает полезной прокрутке, но не имеет других последствий), пока не заметил замедление работы и то, что файл становится гигантским.

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

(У меня Mac, если это имеет значение.)

Первое, что я пробую (это в Windows, так что ymmv), это просто принудительно сохранить. Вероятно, в 1/3 случаев это исправит ситуацию (я вижу, что полоса прокрутки становится больше, поскольку количество доступных строк резко уменьшается), еще 1/3 сохранения/выхода и возврата сделают это, а в другой 1/3 я приходится вручную с этим справляться.
опубликовано Lyn Never в 13:52, 30 октября 2020 г.

В Ye Olden Times вы перемещали мышь в ячейку A1 и сохраняли, и это убирало все лишнее.

Также: выделите первую пустую строку внизу, затем, удерживая нажатой клавишу Shift, нажмите клавишу End, а затем нажмите стрелку вниз. Это выделит все строки от той, которую вы выделили, до конца. (Конец, затем направление идет настолько далеко, насколько это возможно в этом направлении, пока оно либо не столкнется с чем-то, либо не дойдет до конца электронной таблицы. Внизу есть маленькая вещь, которая говорит «Конечный режим», пока она активна. Вы не удерживаете вниз в конце, просто нажмите один раз.)
опубликовано Хаффи Паффи в 15:00, 30 октября 2020 г. [2 избранных]

Паршивый обходной путь, но если вы буквально получаете сотни тысяч дополнительных строк, вы можете сэкономить немного времени, выполнив первое сохранение в старом формате файла .xls (не .xlsx), который (iirc) только сохранить до старого максимума в 65 536 строк.

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

В зависимости от того, насколько сложны ваши рабочие листы, вы также можете попробовать скопировать настоящие строки или ячейки, вставить их на новый рабочий лист и удалить оригинал.
опубликовано тригом в 15:22, 30 октября 2020 г.

Лучший ответ. Кроме того, согласно некоторым сайтам, это может быть вызвано случайным форматированием, которое каким-то образом было применено к этим строкам. Так что, вероятно, стоит обратить внимание на то, как вы обычно применяете форматирование, на случай, если есть какая-то важная привычка, которой вы можете научиться избегать.

И вы можете использовать подход, описанный в этом посте, чтобы попытаться выяснить, в чем проблема для любого конкретного файла: файл .xlsx на самом деле представляет собой .zip-архив, состоящий в основном из XML-файлов, поэтому в посте описывается изучение содержимого, поиск действительно огромного XML-файла и проверка последующих строк, чтобы выяснить, какое форматирование Excel связывает с ними. Затем вы можете либо использовать эту информацию для удаления форматирования в Excel, либо (возможно?) просто удалить все эти строки прямо из XML-файла с помощью удобного текстового редактора, который позволит вам удалить сразу огромное количество строк.

ETA: На самом деле я бы сначала попробовал вот это — инструкции Microsoft о том, как избавиться от постороннего форматирования.
опубликовано trig в 15:31, 30 октября 2020 г. [3 избранных]

Я не использовал его в последнее время, но когда я много работал в Excel, я использовал инструмент под названием ASAP Utilities, в котором было много функций Excel, одна из которых может удалить все неиспользуемое форматирование из каждой книги. Это не бесплатно и, возможно, не стоит того только из-за этой функции, но если вы часто используете Excel, у него также есть множество других удобных инструментов.
опубликовано nalyd в 7:27, 31 октября 2020 г.

Лучший ответ: это может быть вызвано выделением всей строки или столбца и применением к ним формата.
опубликовано soelo 31 октября 2020 г. в 14:28 [1 избранное]

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

Удалить бесконечные пустые строки в выделении/рабочем листе/книге с помощью Kutools for Excel

Показывать только выделение и скрывать другой диапазон с помощью Kutools for Excel

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

<р>1. Выберите весь рабочий лист, нажав клавиши Alt + A, затем нажмите клавиши Ctrl + G, чтобы открыть диалоговое окно «Перейти», а затем нажмите «Специальные». Смотрите скриншот:

<р>2. В диалоговом окне «Перейти к специальному» установите флажок «Пробелы». Смотрите скриншот:

<р>3. Нажмите OK, теперь все пустые ячейки в пустых строках выбраны.

<р>4. Щелкните правой кнопкой мыши одну из этих выделенных пустых ячеек и выберите «Удалить» в контекстном меню. Смотрите скриншот:

<р>5. В диалоговом окне "Удалить" установите флажок "Вся строка" и нажмите "ОК".

Теперь пустые строки удалены.

< /td>

Удалить бесконечные пустые строки в выделении/рабочем листе/книге с помощью Kutools for Excel

Если вы хотите удалить все пустые строки во всей книге, какие проблемы возникают при удалении строк по одному листу. Однако, если у вас есть утилита Kutools for Excel Удалить пустые строки, это проще простого.

После установки Kutools for Excel сделайте следующее: (Бесплатно загрузите Kutools for Excel сейчас!)

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

Показать только выделение и скрыть другой диапазон с помощью Kutools for Excel

Если вы хотите показать только выбранный вами диапазон данных и скрыть другой пустой диапазон, вы можете применить Kutools for ExcelУтилита «Установить область прокрутки».

После установки Kutools for Excel сделайте следующее: (Бесплатно загрузите Kutools for Excel сейчас!)

Выберите область, которую вы хотите отображать только, и нажмите Kutools > Показать и скрыть > Установить область прокрутки. Смотрите скриншот:

Кнопка Excel Merge не является подходящим решением для объединения столбцов в один список без потери данных, поскольку она сохраняет только значение из верхней левой ячейки из выделения:

Объединить столбцы в один список с помощью функции СЦЕПИТЬ
Объединить столбцы Excel с помощью Блокнота

Это пошаговое руководство поможет пользователям Excel любого уровня объединить несколько столбцов в Excel и Google Таблицах в один столбец.

Удалить столбец в Excel и Google Таблицах

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

Рисунок 1. Удалить столбец в Excel

Для удаления столбцов в Google Sheets логика почти такая же. В заголовке столбца в правом нижнем углу откройте раскрывающийся список и выберите опцию Удалить столбец.

Рисунок 2. Удалить столбец в Google Таблицах

Удалить строку в Excel и Google Таблицах

Чтобы удалить строку в Excel, мы можем использовать вкладку «Удалить» в разделе «Главные ячейки» в Excel. Во-первых, мы должны выбрать ячейку в строке, которую нужно удалить, и щелкнуть вкладку «Удалить» в разделе «Главные ячейки».

Рисунок 3. Удалить строку в Excel используя вкладку Удалить

Выберите «Удалить строки листов», и строка 4 будет удалена:

Рисунок 4. Результат после удаления

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

Рисунок 5. Удалить строку в Google Таблицах

Удалить пустые ячейки в Excel

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

Рисунок 6. Удалить пустые ячейки в Excel

Выберите Пробелы и нажмите кнопку ОК:

Рисунок 7. Выбор пустых ячеек в Excel

В результате мы хотим удалить все строки, содержащие пустые ячейки в выбранном диапазоне. Мы должны перейти на вкладку Home-Cells-Delete и выбрать Удалить строки листа:

Рис. 8. Удаление строк, содержащих пустые ячейки

Наконец удаляются все строки, содержащие пустые ячейки:

Рис. 9. Окончательный результат удаления строк, содержащих пустые ячейки

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

Чтобы удалить пустые ячейки в Excel Mac, мы должны выполнить немного другие шаги:

Выберите диапазон ячеек, затем выберите Правка-Перейти к и нажмите кнопку Специальные.
Выберите Пробелы и нажмите кнопку ОК для подтверждения.
Щелкните правой кнопкой мыши по выбранной пустой ячейке, выберите Удалить и Вся строка
После нажатия кнопки ОК все строки с пустыми ячейками в Excel Mac будут удалены.

Удалить строки и столбцы в Excel, которые остаются навсегда

Если мы хотим удалить строки или столбцы в Excel, которые остаются навсегда, мы можем легко сделать это с помощью сочетаний клавиш. Во-первых, мы должны выбрать первую строку или столбец, щелкнув заголовок строки или столбца. Затем мы можем использовать сочетание клавиш Ctrl+Shift+Стрелка вниз, чтобы выбрать все строки, или Ctrl+Shift+Стрелка вправо, чтобы выбрать все столбцы справа от выбранного один.

Обратите внимание, что если после выбранных есть пустые строки или столбцы, мы должны несколько раз нажать стрелку вниз (стрелку вправо), удерживая при этом Ctrl+Shift, пока выделяются целые строки и столбцы.

Рисунок 10. Выбор строк в Excel, которые продолжаются вечно

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

Рисунок 11. Удаление строк или столбцов в Excel, которые остаются навсегда

Удалить несколько ячеек в Excel или Google Sheets

Чтобы удалить несколько ячеек в Excel и соответствующие строки, нам нужно выбрать ячейки, которые нужно удалить, и нажать Удалить строки листа на вкладке Главная-Ячейки-Удалить:

Рисунок 12. Выбор нескольких ячеек в Excel

В результате удаляются строки с выделенными ячейками:

Рисунок 13. Удалить несколько ячеек в Excel

Если мы хотим удалить столбцы с выбранными ячейками, мы должны выбрать Удалить столбцы листа на вкладке Главная-Ячейки-Удалить.

В Google Sheets мы можем удалить несколько столбцов или строк, выбрав заголовок столбца/строки, щелкнув левой кнопкой мыши и нажав вкладку клавиатуры Ctrl, чтобы выбрать столбцы/строки:

Рис. 14. Выбор нескольких столбцов в Google Sheets

Щелкните правой кнопкой мыши по выделенным ячейкам и выберите Удалить столбцы B-C:

Рисунок 15. Удаление нескольких столбцов в Google Sheets

Если были выбраны строки, у нас будет опция Удалить строки в списке.

В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.

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

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

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

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

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

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

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

< бр />

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

< бр />

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

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

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

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

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

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

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

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

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

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

< бр />

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

< бр />

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

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

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

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

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

< бр />

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

< бр />

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

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

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

Выводы

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

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

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

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

Об авторе

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

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

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