Это руководство научит вас некоторым простым приемам безопасного удаления нескольких пустых строк в Excel без потери информации.
Пустые строки в таблице — это проблема, с которой мы все время от времени сталкиваемся, особенно при объединении данных из разных источников или импорте информации из какого-либо отчета. Они могут помешать вашим вычислениям, испортить вид вашей электронной таблицы, а их удаление вручную может оказаться долгим и подверженным ошибкам процессом.
В этой статье вы узнаете несколько простых и надежных методов устранения пробелов в таблицах Excel.
Как НЕ НАДО удалять пустые строки в Excel
Есть несколько способов удалить пустые строки в Microsoft Excel. Но что удивительно, многие интернет-ресурсы в своих рекомендациях придерживаются самого опасного.
Возможно, самый популярный совет — использовать «Найти и выделить> Перейти к специальному> Пробелы» .
Что не так с этой техникой? Выделите все пробелы в диапазоне данных. Но может быть, например, только одна пустая ячейка в строке, а остальные будут содержать какие-то данные. И, следовательно, вы в конечном итоге удаляете много строк, которые содержат хотя бы одну пустую ячейку. Некоторые необходимые данные, естественно, будут потеряны.
Я не буду специально описывать последовательность действий в этом методе подробно, чтобы случайно не повторить ее.
И ключевой вопрос в этом случае: «Вы уверены, что все ячейки в вашей строке пусты? Наверняка у вас нет тех, в которых по какой-то причине одна или несколько ячеек не заполнены, а остальные содержат данные?»
Просто помните: «Вы не должны использовать метод« Найти пустую ячейку »для выбора и удаления пустых строк».
В качестве иллюстрации на изображении ниже слева показана исходная таблица, а справа — итоговая таблица. И в итоговой таблице пропали все незавершенные строки, даже 4,6 и 10, которые были заполнены частично:
Также есть советы по удалению незаполненных строк путем сортировки таблицы или установки фильтра.
Но здесь мы сталкиваемся с такой же опасностью. Если вы отсортируете данные по столбцу, например, от наименьшего к наибольшему, строки с пустыми ячейками в этом столбце будут внизу. Но где гарантия, что в других столбцах справа или слева нет информации? И если стол достаточно большой, его не так-то просто контролировать.
В результате вы снова рискуете удалить данные, которые случайно содержат разрыв в этом конкретном столбце.
Фильтрация — это более или менее та же история. Вы можете установить фильтр для пустых ячеек в определенном столбце. Так что все может происходить так же, как история с сортировкой. Некоторые ячейки можно заполнить необходимой информацией. Для защиты от его случайной потери необходимо установить такой фильтр с пустыми ячейками в каждой колонке. Хорошо, если в вашей таблице их 5. А что, если их 20? А 30? У вас вряд ли возникнет желание установить, а затем удалить даже 10 фильтров.
Поэтому даже эти методы — не наш выбор.
Итог: если вы не хотите испортить или потерять данные, никогда не удаляйте пустые строки, выбирая каким-либо образом отдельные пустые ячейки в таблице. Вместо этого используйте один из наиболее продуманных подходов, обсуждаемых ниже.
Формула для удаления пустых строк в Excel
Мы будем использовать формулу в дополнительном столбце, который лучше всего разместить справа от таблицы. Поясним на примере.
У нас есть таблица с заказами от разных покупателей. В нем есть пробелы. Некоторые ячейки не заполнены. Мы удаляем пустые строки, но сохраняем все остальные данные.
Если вы хотите точно знать, что удаляете, используйте следующую формулу:
= ЕСЛИ (СЧЁТ (LA2: MI2) = 0, «Пробел»;»»)
Где A2 — первая, а E2 — последняя использованная ячейка первой строки данных.
Введите эту формулу в F2 или любой другой свободный столбец в строке 2 и перетащите маркер заполнения, чтобы скопировать формулу вниз.
В результате у вас будет «Пусто» записано в пустых строках и ничего, если есть хотя бы одна ячейка с данными:
Логика формулы ясна: вы подсчитываете заполненные ячейки с помощью функции COUNT и используете оператор IF, чтобы вернуть NULL для нулевого результата. Если в запрошенном диапазоне найдена хоть какая-то информация, мы ничего не возвращаем.
Фактически можно обойтись без IF:
= СЧЁТ (LA2: MI2) = 0
В этом случае формула вернет ИСТИНА для пустых строк и ЛОЖЬ для полных строк.
Существуют также аналогичные решения, в которых используется функция СЧИТАТЬПУСТОТЫ. В нем мы указываем диапазон ячеек, а затем сравниваем с количеством столбцов.
= ЕСЛИ (СЧИТАТЬ ПУСТОЙ (A2: E2) = ЧИСЛО ПУСТОЙ (A2: E2), «Пусто»;»»)
Если равенство достигнуто, количество пустых ячеек равно количеству столбцов, и в строке нет данных. Поэтому его можно пометить для удаления.
Как мы делаем эту отмену?
Давайте посмотрим, как быстро удалить пустые строки:
- Выберите ячейку в заголовке и нажмите «Фильтр» на вкладке «Данные». Это добавит стрелки раскрывающегося списка фильтра ко всем ячейкам заголовка.
- Щелкните стрелку в заголовке столбца формулы, снимите флажок (Выбрать все), выберите Пусто и нажмите ОК:
- Выбрать все отфильтрованные. Для этого щелкните верхнюю ячейку и нажмите Ctrl + Shift + End, чтобы расширить выделение до последней ячейки в отфильтрованной области.
Или щелкните номер первой пустой строки на вертикальной шкале координат. В этом случае все должно быть выделено. Затем введите на клавиатуре комбинацию Ctrl + Shift + (стрелка вниз). Это выделит всю область до конца таблицы. В этом поле щелкните правой кнопкой мыши выделение и выберите «Удалить» из контекстного меню. Все выбранные строки будут удалены.
Нет необходимости вызывать контекстное меню, достаточно просто набрать комбинацию клавиш Ctrl и — на клавиатуре — .
- Удалите фильтр, нажав Ctrl + Shift + L. Либо поместите курсор в самую верхнюю позицию и снова щелкните значок фильтра в меню «Данные».
- Удалите столбец формулы проверки, так как он больше не нужен.
В результате мы имеем чистую таблицу без пробелов, но с сохранением всей информации. Частично заполненные строки остались в таблице, мы их не потеряли.
Совет. Вместо удаления вы можете скопировать непустые строки в другое место. Для этого отфильтруйте все, кроме «Пустой», выберите его и нажмите Ctrl + C. Затем переключитесь на другой лист, выберите верхнюю левую ячейку целевого диапазона и нажмите Ctrl + V.
Как удалить пустые строки в Excel с помощью Power Query
В Excel 2016 и Excel 2019 есть еще один способ удалить незаполненные строки с помощью инструмента Power Query. В Excel 2010 и 2013 его можно загрузить как надстройку.
Важная заметка! Этот метод работает со следующим предостережением: Power Query преобразует данные в таблицу Excel и изменяет форматирование, такое как цвет заливки, границы и некоторые числовые форматы. Если для вас важно форматирование исходных данных, лучше выбрать другой метод удаления.
Тогда пойдем по порядку.
- Выберите интервал, в течение которого вы хотите выполнить отмену.
- Перейдите на вкладку «Данные» и нажмите «Получить данные — из других источников — из таблицы / диапазона». Это загрузит вашу электронную таблицу в редактор Power Query.
На вкладке «Главная» редактора Power Query щелкните «Удалить строки»> «Удалить пробел…» (как показано на рисунке).
Нажмите кнопку «Закрыть и загрузить». Полученная таблица загрузится на новый лист, и редактор запросов закроется.
В результате этих манипуляций я получил следующую таблицу без предыдущих белых полос в середине данных, но с парой неудачных настроек: формат чисел потерян, а даты отображаются в формате по умолчанию, а не в пользовательском один.
Как удалить строки, если ячейка в определенном столбце пуста
В начале этого урока мы предупреждали вас не удалять пустые строки, выбирая пробелы. Однако этот метод полезен, когда вы хотите удалить строки на основе пробелов в определенном столбце.
Например, удалим все строки, в которых ячейка в столбце A не содержит никакой информации:
- Выберите ключевой столбец, в нашем случае столбец A.
- На вкладке «Главная» нажмите «Найти и выделить»> «Перейти…»> «Выбрать». Или просто нажмите F5, а затем — Выбрать… .
- В диалоговом окне выберите Пробелы, а затем ОК. Это выделит все пробелы в столбце A.
- Щелкните правой кнопкой мыши любую выделенную ячейку и выберите «Удалить…» из контекстного меню.
- В диалоговом окне «Удалить» нажмите «Строка» и нажмите «ОК».
Готово! Строки без данных в столбце A больше не существуют.
Вы можете добиться того же результата, отфильтровав пробелы в ключевом столбце.
Как удалить лишние строки под данными или скрыть их.
Иногда строки, которые кажутся совершенно пустыми, могут содержать пробелы, переводы строк или непечатаемые символы. Чтобы проверить, действительно ли последняя ячейка данных является последней ячейкой, использованной на листе, нажмите Ctrl + End. Если это привело вас к визуально свободной от данных строке под вашими данными, с точки зрения Excel, на самом деле в ней что-то написано. Чтобы удалить лишние строки под таблицей, сделайте следующее:
- Щелкните заголовок первой свободной строки под данными, чтобы выделить всю строку.
- Нажмите Ctrl + Shift + End. Это выберет все строки ниже, которые содержат что-либо, включая пробелы и непечатаемые символы.
- Щелкните выделение правой кнопкой мыши, как мы делали ранее, и выберите «Удалить…».
Однако удаление — довольно радикальный шаг. Поэтому думаю стоит поискать более «мягкие» варианты. Например, ничто не может помешать вам заполнить пустые строки или просто скрыть столбцы. Вот простая инструкция:
- Выберите строку под последней строкой с данными, щелкнув ее заголовок.
- Нажмите Ctrl + Shift +, чтобы полностью расширить выделение.
- Нажмите Ctrl + 9, чтобы скрыть выделенные строки. Или щелкните выделение правой кнопкой мыши и выберите «Скрыть».
Чтобы снова показать то, что было ранее скрыто, нажмите Ctrl + A, чтобы выделить весь лист, затем нажмите Ctrl + Shift + 9, чтобы снова сделать все видимым.
Точно так же вы можете скрыть неиспользуемые столбцы справа от ваших данных.
Как быстро удалить пустые строки в Excel с помощью VBA
Excel VBA может решить многие задачи, включая некоторые пустые строки. Лучшее в этом подходе — то, что он не требует каких-либо навыков программирования. Возьмите один из следующих кодов и запустите его в своем Excel.
Макрос 1. Удалить пустые строки в выбранном диапазоне.
Этот код VBA автоматически удалит все пустые строки в выбранном диапазоне, не показывая пользователю никаких сообщений или диалоговых окон.
Макрос удаляет строку, только если она пуста. Определите количество ячеек данных в каждой строке, а затем удалите строки с нулевым результатом.
Публичный Sub DeleteBlankRows()
Dim SourceRange как диапазон
Затемнить всю строку как диапазон
Установите SourceRange = Application.Selection
Если нет (SourceRange — это ничего), то
Application.ScreenUpdating = False
Для I = SourceRange.Rows.Count 1 проход -1
Установите CompleteRow = SourceRange.Cells (I, 1) .EntireRow
Если Application.WorksheetFunction.CountA (CompleteRow) = 0, то
Вся строка Удалить
Конец, если
Следующий
Application.ScreenUpdating = True
Конец, если
Конец подзаголовка
Чтобы дать пользователю возможность выбрать целевой диапазон после запуска макроса, используйте этот код:
Публичная подписка RemoveBlankLines()
Dim SourceRange как диапазон
Затемнить всю строку как диапазон
В случае ошибки Продолжить Далее
Установите SourceRange = Application.InputBox (_
«Выбрать диапазон:», «Удалить пустые строки», _
Address.Selection.Application, тип: = 8)
Если нет (SourceRange — это ничего), то
Application.ScreenUpdating = False
Для I = SourceRange.Rows.Count 1 проход -1
Установите CompleteRow = SourceRange.Cells (I, 1) .EntireRow
Если Application.WorksheetFunction.CountA (CompleteRow) = 0, то
Вся строка Удалить
Конец, если
Следующий
Application.ScreenUpdating = True
Конец, если
Конец подзаголовка
После запуска макроса отображается поле ввода, вы указываете целевой диапазон и нажимаете ОК:
Через мгновение все ненужные строки в выбранном диапазоне будут удалены, а остальные переместятся вверх.
Макрос 2. Удалить все пустые строки в Excel
Чтобы массово удалить все пустые строки на активном листе, макрос находит последнюю строку используемого диапазона (т.е содержащую последнюю ячейку с данными), затем перескакивает вверх, удаляя строки, в которых нет информации:
Sub Удалить RowIndex)) = 0 Тогда Rows (RowIndex). Удалить конец, если следующий RowIndex Application.ScreenUpdating = True End Sub
Макрос 3. Удалить строку, если ячейка пуста.
С помощью этого макроса вы можете удалить всю строку, если ячейка в указанном столбце не содержит данных.
Этот код проверяет столбец A на наличие пробелов. Чтобы удалить строки на основе другого столбца, замените «A» другой подходящей буквой.
Sub DeleteRowIfCellBlank()
В случае ошибки Продолжить Далее
Столбцы («А»). SpecialCells (xlCellTypeBlanks) .EntireRow.Delete
Конец подзаголовка
Важно! Макрос удаляет неиспользуемые строки со всего листа, поэтому будьте очень осторожны при его использовании. В качестве меры безопасности рекомендуется создать резервную копию рабочего листа перед запуском этого макроса.
Чтобы удалить пустые строки в Excel с помощью макроса, вам необходимо вставить код VBA в книгу.
Как добавить макрос в свою книгу?
Чтобы вставить макрос в книгу, выполните следующие действия:
- Откройте лист, который хотите удалить.
- Нажмите Alt + F11, чтобы открыть редактор Visual Basic.
- На левой панели щелкните правой кнопкой мыши и выберите Вставить> Модуль. Эти же элементы можно выбрать выше в главном меню.
- Вставьте код в появившееся окно кода.
- Нажмите F5, чтобы запустить макрос (но вы можете сделать это позже прямо из книги).
- Щелкните по кнопке «Сохранить». Вам будет предложено изменить формат вашей книги. Однако вы можете запустить макрос без сохранения. Закройте окно Visual Basic и вернитесь к своей таблице. Переходите непосредственно к шагу 3 ниже.
Чтобы запустить макрос в книге Excel, сделайте следующее:
- Откройте книгу и при необходимости включите макросы.
- Перейти к нужной таблице.
- На листе нажмите Alt + F8, выберите макрос и нажмите «Выполнить».
Вы можете запустить один из следующих макросов:
DeleteBlankRows — удаляет строки без данных в текущем выбранном диапазоне.
RemoveBlankLines — удаляет пустые строки и переходит в выбранный диапазон после запуска макроса.
DeleteAllEmptyRows — удаляет все пустые строки на активном листе.
DeleteRowIfCellBlank — удаляет строку, если ячейка в определенном столбце пуста.
Учтите, что в этом случае отменить удаление с помощью меню или комбинации клавиш Ctrl + Z будет невозможно. Поэтому я рекомендую вам позаботиться о сохранении резервной копии ваших данных перед использованием макросов.
Самый быстрый способ удалить пустые строки в Excel
Разве это не было похоже на использование кувалды, чтобы расколоть орех, прочитав приведенные выше примеры? Есть способ буквально удалить пустые строки в Excel двумя щелчками мыши.
С помощью надстройки Ultimate Suite вы можете удалить все пустые строки на листе:
- На вкладке Инструменты Ablebits в группе Преобразование щелкните Подавить пробелы> Пустые строки) :
- Надстройка сообщит вам, что все пустые строки будут удалены с активного листа, и попросит вас подтвердить. Нажмите ОК, и все строки без данных будут немедленно удалены.
Как показано на скриншоте ниже, мы удалили только абсолютно пустые строки, не содержащие ни одной ячейки с данными:
Сравните это с первым изображением, и вы легко заметите 3 линии, которые были стерты одним щелчком мыши. Согласитесь — это самый простой и быстрый способ удалить пустые строки среди всех других, обсуждаемых в этой статье.
