Содержание:
Пустые строки и столбцы во многих случаях могут создавать проблемы в таблицах. Стандартные функции сортировки, фильтрации, суммирования, создания сводных таблиц и т. д. воспринимают пустые строки и столбцы как разрыв таблицы, не подхватывая данные, расположенные дальше за ними. Если таких пробелов много, то удалить их вручную может быть очень затратно, а удалить все сразу «оптом» с помощью фильтрации не получится, потому что фильтр тоже будет «спотыкаться» на разрывах.
Давайте рассмотрим несколько способов решения этой проблемы.
Способ 1. Поиск пустых ячеек
Возможно, это не самый удобный, но определенно стоит упомянуть самый простой способ.
Предположим, мы имеем дело с такой таблицей, содержащей внутри множество пустых строк и столбцов (выделены для наглядности):
Предположим, мы уверены, что первый столбец нашей таблицы (столбец B) всегда содержит название города. Тогда пустые ячейки в этом столбце будут признаком ненужных пустых строк. Чтобы быстро удалить их все, сделайте следующее:
- Выберите диапазон с городами (B2:B26)
- Нажмите кнопку F5 и затем нажмите Выделите (Перейти в раздел Специальные) или выберите на вкладке Главная — Найти и выбрать — Выбрать группу ячеек (Главная страница — Найти и выбрать — Перейти к специальному).
- В открывшемся окне выберите опцию Пустые ячейки (Пробелы) и нажмите OK – должны быть выделены все пустые ячейки в первом столбце нашей таблицы.
- Теперь выберите на вкладке Главная Command Удалить – удалить строки из листа. (Удалить — Удалить строки) или нажмите сочетание клавиш Ctrl+минус – и наша задача решена.
Конечно, точно таким же способом можно избавиться и от пустых столбцов, взяв за основу заголовок таблицы.
Способ 2: поиск пустых строк
Как вы уже могли догадаться, предыдущий способ сработает только в том случае, если наши данные обязательно содержат полностью заполненные строки и столбцы, на которые можно подцепиться при поиске пустых ячеек. Но что, если такой уверенности нет, и данные могут содержать и пустые ячейки?
Взгляните на следующую таблицу, например, именно для такого случая:
Здесь подход будет немного сложнее:
- Введите в ячейку А2 функцию СЧИТАТЬ (СЧЁТКА), который посчитает количество заполненных ячеек в строке справа и скопирует эту формулу во всю таблицу:
- Выделите ячейку A2 и включите фильтр командой Данные – Фильтр (Данные — Фильтр) или сочетание клавиш Ctrl+Shift+L.
- Отфильтруем нули по вычисляемому столбцу, т.е. по всем строкам, где нет данных.
- Осталось выбрать отфильтрованные строки и удалить их командой Главная — Удалить — Удалить строки с листа (Главная страница — Удалить — Удалить строки) или сочетание клавиш Ctrl+минус.
- Отключаем фильтр и получаем наши данные без пустых строк.
К сожалению, со столбцами этот трюк уже невозможно проделать — Excel еще не научился фильтровать по столбцам.
Способ 3. Макрос для удаления всех пустых строк и столбцов на листе
Вы также можете использовать простой макрос для автоматизации этой задачи. Нажмите сочетание клавиш другой+F11 или выберите на вкладке разработчик - Visual Basic (Разработчик — редактор Visual Basic). Если вкладки разработчик не отображается, вы можете включить его через Файл – Параметры – Настройка ленты. (Файл — Параметры — Настроить ленту).
В открывшемся окне редактора Visual Basic выберите команду меню Вставка – Модуль и в появившемся пустом модуле скопируйте и вставьте следующие строки:
Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 Тогда If rng Is Nothing then Set rng = Rows(r) Else Set rng = Union(rng, Rows(r)) End If Next r If Not rng Is Nothing then rng.Delete 'удаляем пустые столбцы Set rng = Nothing For r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count Если Application.CountA(Columns(r)) = 0 Тогда Если rng ничего не значит, то Set rng = Columns(r) Иначе Set rng = Union(rng, Columns( r)) End If Next r Если не rng — это ничего, то rng.Delete End Sub
Закройте редактор и вернитесь в Excel.
Теперь нажмите комбинацию другой+F8 или кнопка Макрос таб разработчик. В открывшемся окне будут перечислены все макросы, доступные на данный момент для запуска, включая только что созданный макрос. Удалитьпустой. Выберите его и нажмите кнопку Run (бежать) – все пустые строки и столбцы на листе будут мгновенно удалены.
Способ 4: Power Query
Другой способ решения нашей проблемы и очень распространенный сценарий — удалить пустые строки и столбцы в Power Query.
Сначала давайте загрузим нашу таблицу в редактор запросов Power Query. Вы можете преобразовать его в динамический «умный» с помощью сочетания клавиш Ctrl+T или просто выделить наш диапазон данных и дать ему имя (например Данные) в строке формул, преобразуя в именованный:
Теперь используем команду Данные – Получить данные – Из таблицы/диапазона (Data – Get Data – From table/range) и загружаем все в Power Query:
Дальше все просто:
- Пустые строки удаляем командой Главная – Уменьшить строки – Удалить строки – Удалить пустые строки (Главная – Удалить строки – Удалить пустые строки).
- Щелкните правой кнопкой мыши заголовок первого столбца «Город» и выберите команду «Отменить сведение других столбцов» в контекстном меню. Наша таблица будет, как это технически правильно называется, нормированный – преобразовано в три столбца: город, месяц и значение от пересечения города и месяца из исходной таблицы. Особенность этой операции в Power Query в том, что она пропускает пустые ячейки в исходных данных, что нам и нужно:
- Теперь проделываем обратную операцию – превращаем полученную таблицу обратно в двумерную, чтобы вернуть ей первоначальный вид. Выберите столбец с месяцами и на вкладке трансформация выбрать команду Сводный столбец (Преобразование — сводный столбец). В открывшемся окне в качестве столбца значений выберите последнее (Значение), а в дополнительных параметрах – операцию Не суммируйте (Не суммируйте):
- Осталось загрузить результат обратно в Excel командой На главную — Закрыть и загрузить — Закрыть и загрузить… (Главная страница — Закрыть и загрузить — Закрыть и загрузить в…)
- Что такое макрос, как он работает, куда скопировать текст макроса, как запустить макрос?
- Заполнение всех пустых ячеек списка значениями родительских ячеек
- Удаление всех пустых ячеек из заданного диапазона
- Удаление всех пустых строк на листе с помощью надстройки PLEX