Удаление пустых строк и столбцов в данных
Пустые строки и столбцы могут быть головной болью в таблицах во многих случаях. Стандартные функции сортировки, фильтрации, подведения итогов, создания сводных таблиц и т.д. воспринимают пустые строки и столбцы как разрыв таблицы, не подхватывая данные, расположенные за ними далее. Если таких разрывов много, то удалять их вручную может оказаться весьма затратно, а удалить сразу всех "оптом", используя фильтрацию не получится, т.к. фильтр тоже будет «спотыкаться» на разрывах.
Давайте рассмотрим несколько способов решения этой задачи.
Способ 1. Поиск пустых ячеек
Это, может, и не самый удобный, но точно самый простой способ вполне достойный упоминания.
Предположим, что мы имеем дело вот с такой таблицей, содержащей внутри множество пустых строк и столбцов (для наглядности выделены цветом):
Допустим, мы уверены, что в первом столбце нашей таблицы (колонка B) всегда обязательно присутствует название какого-либо города. Тогда пустые ячейки в этой колонке будут признаком ненужных пустых строк. Чтобы быстро их все удалить делаем следующее:
- Выделяем диапазон с городами (B2:B26)
- Нажимаем клавишу F5 и затем кнопку Выделить (Go to Special) или выбираем на вкладке Главная - Найти и выделить - Выделить группу ячеек (Home - Find&Select - Go to special).
- В открывшемся окне выбираем опцию Пустые ячейки (Blanks) и жмём ОК – должны выделиться все пустые ячейки в первом столбце нашей таблицы.
- Теперь выбираем на вкладке Главная команду Удалить - Удалить строки с листа (Delete - Delete rows) или жмём сочетание клавиш Ctrl+минус - и наша задача решена.
Само-собой, от пустых столбцов можно избавиться совершенно аналогично, взяв за основу шапку таблицы.
Способ 2. Поиск незаполненных строк
Как вы, возможно, уже сообразили, предыдущий способ сработает только в том случае, если в наших данных обязательно присутствую полностью заполненные строки и столбцы, за которые можно зацепиться при поиске пустых ячеек. Но что, если такой уверенности нет, и в данных могут содержаться и пустые ячейки в том числе?
Взгляните, например, на следующую таблицу - как раз такой случай:
Здесь подход будет чуть похитрее:
-
Введём в ячейку A2 функцию СЧЁТЗ (COUNTA), которая вычислит количество заполненных ячеек в строке правее и скопируем эту формулу вниз на всю таблицу:
- Выделим ячейку А2 и включим фильтр командой Данные - Фильтр (Data - Filter) или сочетанием клавиш Ctrl+Shift+L.
- Отфильтруем по вычисленному столбцу нули, т.е. все строки, где нет данных.
- Осталось выделить отфильтрованные строки и удалить их командой Главная - Удалить - Удалить строки с листа (Home - Delete - Delete rows) или сочетанием клавиш Ctrl+минус.
- Отключаем фильтр и получаем наши данные без пустых строк.
К сожалению, со столбцами такой трюк уже не проделать – фильтровать по столбцам Excel пока не научился.
Способ 3. Макрос удаления всех пустых строк и столбцов на листе
Для автоматизации подобной задачи можно использовать и простой макрос. Нажмите сочетание клавиш Alt+F11 или выберите на вкладке Разработчик - Visual Basic (Developer - Visual Basic Editor). Если вкладки Разработчик не видно, то можно включить ее через Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon).
В открывшемся окне редактора Visual Basic выберите команду меню Insert - Module и в появившийся пустой модуль скопируйте и вставьте следующие строки:
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 Then
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
If Application.CountA(Columns(r)) = 0 Then
If rng Is Nothing Then Set rng = Columns(r) Else Set rng = Union(rng, Columns(r))
End If
Next r
If Not rng Is Nothing Then rng.Delete
End Sub
Закройте редактор и вернитесь в Excel.
Теперь нажмите сочетание Alt+F8 или кнопку Макросы на вкладке Разработчик. В открывшемся окне будут перечислены все доступные вам в данный момент для запуска макросы, в том числе только что созданный макрос DeleteEmpty. Выберите его и нажмите кнопку Выполнить (Run) - все пустые строки и столбцы на листе будут мгновенно удалены.
Способ 4. Запрос Power Query
Ещё один способ решить нашу задачу и весьма частый сценарий - это удаление пустых строк и столбцов в Power Query.
Сначала давайте загрузим нашу таблицу в редактор запросов Power Query. Можно конвертировать её в динамическую "умную" сочетанием клавиш Ctrl+T или же просто выделить наш диапазон данных и дать ему имя (например Данные) в строке формул, преобразовав в именованный:
Теперь используем команду Данные - Получить данные - Из таблицы/диапазона (Data - Get Data - From table/range) и грузим всё в Power Query:
Дальше всё просто:
- Удаляем пустые строки командой Главная - Сократить строки - Удалить строки - Удалить пустые строки (Home - Remove Rows - Remove empty rows).
- Щёлкаем правой кнопкой мыши по заголовку первого столбца Город и выбираем в контекстном меню команду Отменить свёртывание других столбцов (Unpivot Other Columns). Наша таблица будет, как это технически правильно называется, нормализована - преобразована в три столбца: город, месяц и значение с пересечения города и месяца из исходной таблицы. Особенность этой операции в Power Query в том, что она пропускает в исходных данных пустые ячейки, что нам и требуется:
- Теперь выполяем обратную операцию - сворачиваем полученную таблицу обратно в двумерную, чтобы вернуть ей исходный вид. Выделяем столбец с месяцами и на вкладке Преобразование выбираем команду Столбец сведения (Transform - Pivot Column). В открывшемся окне в качестве столбца значений выбираем последний (Значение), а в расширенных параметрах - операцию Не агрегировать (Don't aggregate):
- Останется выгрузить результат обратно в Excel командой Главная - Закрыть и загрузить - Закрыть и загрузить в... (Home - Close&Load - Close&Load to...)
Ссылки по теме
- Что такое макрос, как он работает, куда копировать текст макроса, как запустить макрос?
- Заполнение всех пустых ячеек в списке значениями вышестоящих ячеек
- Удаление всех пустых ячеек из заданного диапазона
- Удаление всех пустых строк на листе с помощью надстройки PLEX
Для ускорения можно использовать такой вариант:
И еще как можно поменять код, чтобы вместо Selection.Rows были конкретные строки, например только для диапазона Range("B3:B10"
Sub DeleteEmptyRowsInRange() Dim r As Long, rng As Range Range("B3:B10").Copy Range("H3:H10") Range("H3:H10").Select For r = 1 To Selection.Rows.Count If Application.CountA(Selection.Rows(r)) = 0 Then If rng Is Nothing Then Set rng = Selection.Rows(r) Else Set rng = Union(rng, Selection.Rows(r)) End If End If Next r If Not rng Is Nothing Then rng.Delete Range("H3:H10").Copy Range("B3:B10") Range("H3:H10").ClearContents End Subа вся строка без первой ячейке в ней, и вот если в этой строке (без первой ячейки) пусто,
то удалить строку, включая первую ячейку, т.е. как тут и есть?
поменял в коде 0 на "" - не получается.
И что значит, в вашем понимании, "заполненная строка"?
Выручил многократно, работа набирает темпы.
буду очень признательна за помощь.
Тоже волнует этот вопрос, который задал предыдущий автор.
Подскажите, пожалуйста, как удалить пустые строки в таблице (таблица А8:К999)