Удаление пустых строк и столбцов в данных
Пустые строки и столбцы могут быть головной болью в таблицах во многих случаях. Стандартные функции сортировки, фильтрации, подведения итогов, создания сводных таблиц и т.д. воспринимают пустые строки и столбцы как разрыв таблицы, не подхватывая данные, расположенные за ними далее. Если таких разрывов много, то удалять их вручную может оказаться весьма затратно, а удалить сразу всех "оптом", используя фильтрацию не получится, т.к. фильтр тоже будет «спотыкаться» на разрывах.
Давайте рассмотрим несколько способов решения этой задачи.
Способ 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"
а вся строка без первой ячейке в ней, и вот если в этой строке (без первой ячейки) пусто,
то удалить строку, включая первую ячейку, т.е. как тут и есть?
поменял в коде 0 на "" - не получается.
И что значит, в вашем понимании, "заполненная строка"?
Выручил многократно, работа набирает темпы.
буду очень признательна за помощь.
Тоже волнует этот вопрос, который задал предыдущий автор.
Подскажите, пожалуйста, как удалить пустые строки в таблице (таблица А8:К999)
Скажите пожалуйста, можно ли как-то оптимизировать процесс удаления ненужных столбцов, не прибегая к стандартным "выделить, удалить"?
Например, я получаю отчёт, из которого мне нужны определённые столбцы, по которым я буду считать далее. Как я сейчас это делаю:
1. Получаю отчёт
2. Выделяю нужные столбцы и копирую их на новый лист
Почему хочу найти оптимизацию:
1. Отчёты немалых размеров
2. Естественно нужные столбцы идут не друг за другом
3. Каждый раз сижу и выделяю эти столбцы ЛКМ + Ctrl
Нажимаю ctrl+end выделяется ячейка за пределами заполненной области (иногда далеко за пределами)
удаляю все строки от заполненной области до выделенной ячейки включительно
удаляю все столбцы от заполненной области до выделенной ячейки включительно
Нажимаю ctrl+end выделяется ячейка всё равно за пределами заполненной области
Подкорректировав макрос, получил следующее:
Есть ли идеи как реализовать его более научно? Суть: определить диапазон в столбце A, проверяем столбец F на пустые ячейки, и если они пустые - удалять эти строки.
Рекомендую для ускорения добавить в начало переключение пересчета в ручной режим и отключение обновления экрана:
Но, я ставил вопрос иначе: 2+ минуты на удаление строк, в диапазоне 150 значений - это катастрофа )
Вручную: сортировка-выделение-удаление занимает 5 секунд времени, независимо от диапазона )
Только по вашему коду и описанию, боюсь, невозможно будет определить где именно тормоза возникают.