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

Удалить строки по условию в Excel проще всего через вспомогательный столбец: формула помечает ненужные строки (например, =ЕЧИСЛО(ПОИСК("аренда";A2)) или =СЧЁТЗ(A2:C2)=0 для пустых), затем фильтр показывает только ИСТИНА, и видимые строки удаляются одним действием. В Excel 365 можно сразу получить таблицу без лишних строк формулой =ФИЛЬТР(A2:B100;НЕ(ЕЧИСЛО(ПОИСК("аренда";A2:A100)))). Ниже — формула для каждого условия.

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

Что такое паттерны? Это признаки, по которым вы делаете выборку строк или столбцов, которые будете удалять.

Примеры таких паттернов:

• пустота столбца или строки,
• наличие в столбце пустых ячеек,
• наличие определенного символа или нескольких символов,
• наличие нескольких сочетаний букв,
• наличие нескольких слов,
• наличие нескольких словосочетаний.

Для тех, кто ценит время и ищет методы быстрого решения проблем, были созданы инструменты по удалению строк и столбцов в !SEMTools. Все макросы в этом меню работают в бесплатной версии, поэтому смело переходите в раздел Скачать !SEMTools, устанавливайте надстройку и пользуйтесь — они довольно удобны. Вот какие возможности есть в разделе:

• Удалить полностью пустые столбцы и строки на листе:
  
  • удалить пустые столбцы,
  • удалить пустые строки.
    
• Удалить всю строку по условию
  
  • Отсутствия чего-либо (если ячейка выделенного столбца не содержит):
    • ничего (пустая),
    • букв или цифр,
    • латиницу,
    • цифры,
    • конкретное слово,
    • ни одно из слов из списка.
  • Наличия чего-либо (если ячейка выделенного столбца содержит):
    • латиницу,
    • цифры,
    • конкретное слово,
    • слово из цифр (число),
    • любое слово из списка.
  • Длины содержимого ячейки — меньше или больше заданного числа символов.

Формулы для удаления строк и столбцов

Порядок для всех формул один: введите формулу в свободный столбец, протяните вниз, включите фильтр (Ctrl+Shift+L), оставьте ИСТИНА, выделите видимые строки и удалите их, затем снимите фильтр.

Пустые строки и строки с пропусками

СЧЁТЗ считает заполненные ячейки, СЧИТАТЬПУСТОТЫ — пустые. Первая формула находит полностью пустые строки, вторая — строки, где пуста хотя бы одна ячейка. Подробнее — удалить пустые строки.

Строки по содержимому и длине текста

Каждый столбец — отдельное условие: слово, наличие цифр, длина текста. Отфильтруйте нужный столбец по ИСТИНА. Подробнее — удалить строки по условию и по длине текста.

Таблица без лишних строк одной формулой

В Excel 365 и 2021 ФИЛЬТР выводит копию таблицы только с нужными строками, исходник не меняется.

Пустые столбцы

Та же проверка СЧЁТЗ, только по столбцу: формулу ставят под таблицей и протягивают вправо. Столбцы B и D пусты, хотя у них есть заголовки, — строка заголовков в проверку не входит. Подробнее — удалить пустые столбцы.

Смотрите также: удаление строк и столбцов

• Как удалять ячейки по условию в Excel
• Как удалять определенные слова в Excel
• Как удалить лишние символы в Excel
• Как убрать формулы из ячеек и оставить только значения

Удаление строк и столбцов — частые вопросы

Как удалить строки по условию в Excel?

Пометьте их формулой во вспомогательном столбце, отфильтруйте ИСТИНА и удалите видимые строки.

Как удалить все пустые строки?

Формулой =СЧЁТЗ(A2:C2)=0 и фильтром или процедурой !SEMTools — подробно в статье удалить пустые строки.

Как удалить строки, содержащие слово?

Условие — =ЕЧИСЛО(ПОИСК("слово";A2)); в Excel 365 нужные строки сразу оставит ФИЛЬТР.

Как удалить пустые столбцы?

Проверьте каждый столбец формулой =СЧЁТЗ(A2:A100)=0 и удалите отмеченные — подробнее удалить пустые столбцы.

Итоги: как удалить строки и столбцы в Excel

Надеюсь, на этой странице вы нашли то, что искали. Связка «формула-признак + фильтр» решает почти любое условие, а для регулярной чистки больших таблиц быстрее процедуры раздела УДАЛИТЬ → Диапазоны.