Как удалить строки, не содержащие определённый текст, в Excel?
Многие пользователи Excel знакомы с удалением всей строки, если она содержит определённый текст. Однако бывают ситуации, когда нужно удалить строки, которые не содержат конкретный текст. Например, у вас может быть большой набор данных, и вы захотите оставить только те строки, где встречается нужное ключевое слово, удалив всё остальное. Это особенно полезно при фильтрации отчётов, очистке ответов на опросы, контроле качества данных и во многих других сценариях. Хотя Excel не предлагает готового решения в один клик для такой задачи, существует несколько практичных методов — каждый со своими преимуществами в зависимости от вашего уровня владения инструментами и конкретных требований. В этом руководстве мы рассмотрим эффективные способы удалить строки, не содержащие заданную текстовую строку в Excel, включая использование фильтров, VBA, Kutools и формул.
Удалить строки, не содержащие определённый текст, с помощью фильтра
Удалить строки, не содержащие определённый текст, с помощью VBA
Удалить строки, не содержащие определённый текст, с помощью Kutools для Excel
Удалить строки, не содержащие определённый текст, с помощью формулы Excel (вспомогательный столбец)
Удалить строки, не содержащие определённый текст, с помощью фильтра
Функция фильтрации Excel — это простой и доступный способ определить и удалить строки, не содержащие конкретного текста. Этот метод особенно полезен для тех, кто предпочитает быстрый визуальный подход, и подходит для таблиц небольшого и среднего размера. Однако он может стать менее эффективным для очень больших наборов данных или в случаях, когда целевой текст сильно варьируется.
1. Выделите столбец с текстом, по которому хотите выполнить фильтрацию. Затем нажмите Данные > Фильтр, чтобы применить фильтры к вашей таблице. См. скриншот:
 |
 |
 |
2. Нажмите кнопку фильтра (стрелку вниз) в заголовке нужного столбца, чтобы открыть выпадающее меню фильтрации. Снимите флажки со всех значений, кроме того текста, который вы хотите оставить — так на листе останутся видимыми только строки, содержащиеэтот текст. Если искомый текст встречается как часть значения или подстрока, выберите «Текстовые фильтры» > «Содержит…» для большей гибкости. См. скриншот:
3. Теперь в текущем представлении отображаются только заголовок и строки, содержащие указанный текст; все остальные строки скрыты. См. скриншот：
4. Выделите все видимые строки, кроме заголовка (щёлкните первую строку, удерживая Shift, и щёлкните последнюю строку), затем щёлкните правой кнопкой мыши и выберите Удалить строку в контекстном меню. Это действие удалит видимые (отфильтрованные) строки. После удаления останутся скрытыми только те строки, которые не содержат указанный текст.
5. Чтобы завершить процесс, снова нажмите Данные > Фильтр, чтобы отключить фильтрацию — и вы увидите, что на листе остались только строки, не содержащие указанный текст. См. скриншот:
 |
|
 |
Примечание: перед удалением дважды убедитесь, что выделены только нужные строки. Удалённые строки нельзя восстановить, если вы не отмените действие (Ctrl+Z). Этот метод работает плохо, если ваша таблица содержит объединённые или вложенные промежуточные итоги.
Удалить строки, не содержащие определённый текст, с помощью VBA
Если вам приходится работать с большими наборами данных или регулярно выполнять эту операцию, использование VBA (Visual Basic for Applications) существенно ускорит удаление строк. Решение на основе VBA автоматизирует процесс: строки удаляются в зависимости от того, содержат ли ячейки заданные текстовые фрагменты или нет. Это идеальный вариант для пользователей с базовыми навыками программирования или в случаях, когда требуется гибкая настройка логики — например, учёт регистра символов.
1. Нажмите Alt + F11, чтобы открыть окно редактора Microsoft Visual Basic for Applications.
2. В окне редактора нажмите Вставка > Модуль, чтобы открыть новое окно модуля. Скопируйте и вставьте следующий код VBA в окно модуля.
VBA: удалить всю строку, если ячейка не содержит заданный текст.
Sub DeleteRowNoInclude()
'Updateby Extendoffice
Dim xRow As Range
Dim rng As Range
Dim WorkRng As Range
Dim xStr As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xStr = Application.InputBox("Text", xTitleId, "", Type:=2)
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 1 Step -1
Set xRow = WorkRng.Rows(i)
Set rng = xRow.Find(xStr, LookIn:=xlValues)
If rng Is Nothing Then
xRow.Delete
End If
Next
Application.ScreenUpdating = True
End Sub
3. Нажмите кнопку Выполнить. После этого появится диалоговое окно с запросом на выбор диапазона для обработки (например, столбца, в котором вы хотите искать указанный текст). См. скриншот:
4. Нажмите ОК и введите текст, который вы хотите использовать в качестве критерия удаления, во всплывающем диалоговом окне. Для наилучших результатов вводите текст точно так, как он отображается в ваших данных. По умолчанию поиск выполняется без учёта регистра; при необходимости измените код, чтобы учитывать регистр. См. скриншот:
5. Нажмите ОК, и все строки, в которых ячейка не содержит указанного текста (например, «Apple»), будут немедленно удалены. Обязательно сохраните свою работу перед запуском макросов VBA — эту операцию нельзя легко отменить. Если результаты окажутся неожиданными, воспользуйтесь отменой (Ctrl+Z) или закройте файл без сохранения, а затем повторите попытку с правильными параметрами. Опытные пользователи могут при необходимости адаптировать этот код для поиска частичных совпадений или нескольких критериев.
Удалить строки, не содержащие определённый текст, с помощью Kutools для Excel
Для пользователей, которые предпочитают отказаться от ручной фильтрации или VBA, Kutools для Excel предлагает эффективный и интерактивный способ быстро выбрать и удалить строки, не содержащие конкретного текста — всего за несколько щелчков мыши. Это особенно полезно, если вы регулярно работаете с данными или сталкиваетесь с несколькими условиями поиска. Хотя для использования Kutools требуется установка, он значительно упрощает сложные операции в Excel и поддерживает пакетную обработку. Все функции интуитивно понятны и удобны даже для начинающих пользователей.
Kutools для Excel включает более 300 полезных инструментов для Excel. Бесплатная пробная версия без ограничений на 30 дней! Получить сейчас
1. Выделите столбец, в котором нужно удалить строки, не содержащие указанный текст, затем нажмите Kutools > Инструменты выделения > Выбрать определенные ячейки. См. скриншот:
2. В диалоговом окне Выбрать определенные ячейки установите флажок Вся строка, выберите Не содержит в выпадающем списке и введите целевой текст в текстовое поле. Кроме того, вы можете нажать значок выбора диапазона в разделе Указать тип, чтобы выбрать ячейку-эталон, содержащую нужный текст. Внимательно проверьте введённые данные на наличие опечаток или лишних пробелов — они могут повлиять на точность выделения.
3. Нажмите ОК. Строки, не содержащие заданного текста, будут автоматически выделены. Щёлкните правой кнопкой мыши по любой из выделенных строк, установите количество строк и выберите Удалить в контекстном меню. Это удалит все выделенные строки сразу. См. скриншот:
4. Теперь все строки в диапазоне «Выберите диапазон», не содержавшие указанный вами текст, удалены с листа. Дополнительные советы и последние функции см. в: Выбрать определенные ячейки документации.
Совет: перед удалением убедитесь, что выделение выполнено корректно, чтобы избежать случайной потери нужных данных, и обязательно создайте резервную копию для надёжности. Функцию выделения Kutools можно эффективно комбинировать с другими инструментами для ещё более гибкого управления данными.
Удалить строки, не содержащие определённый текст, с помощью формулы Excel (вспомогательный столбец)
Ещё один чрезвычайно практичный метод — использование формулы Excel во вспомогательном столбце. Он позволяет быстро определить, содержит ли каждая строка (или не содержит) нужный текст, а затем отфильтровать или удалить строки по мере необходимости. Этот подход идеален для пользователей, уверенно работающих с формулами и предпочитающих прозрачный, проверяемый процесс оценки результатов перед удалением. Особенно он полезен, когда искомый текст может встречаться в любом месте ячейки — а не только как точное совпадение.
Преимущества: Высокая гибкость, не требует надстроек или VBA, безопасна — данные не удаляются сразу, так как пользователь сначала проверяет результаты. Недостаток: Многоэтапный процесс и необходимость базового знакомства с формулами и фильтрами Excel.
Инструкции:
1. Добавьте новый вспомогательный столбец рядом с вашими данными. Например, предположим, что ваш текст находится в столбце A, а данные начинаются со строки 2 (заголовок не учитывается).
2. В первой строке вспомогательного столбца (например,)B2) введите следующую формулу (замените «text» на нужную вам строку для поиска):
=ISNUMBER(SEARCH("text",A2))Эта формула возвращает значение ИСТИНА, если ячейка A2 содержит указанный текст (как подстроку, без учёта регистра), и ЛОЖЬ — в противном случае.
3. Нажмите Enter, а затем дважды щелкните по правому нижнему углу ячейки, чтобы скопировать формулу во все остальные строки набора данных.
4. Примените фильтр к вспомогательному столбцу. Щёлкните стрелку раскрывающегося списка и выберите ЛОЖЬ, чтобы отобразить только строки, не содержащие искомый текст. Выделите все отфильтрованные строки (за исключением заголовка, если он есть), щёлкните по ним правой кнопкой мыши и выберите Удалить строку.
5. После удаления очистите фильтр, чтобы вернуть исходное представление. Проверьте результаты и при необходимости удалите вспомогательный столбец.
Примечания по параметрам и советам:
- Если вам нужно найти точное совпадение значения ячейки (а не подстроку), измените формулу на
=A2="text". - Если ваши данные содержат пустые ячейки, функция ISNUMBER(SEARCH(…)) вернёт значение ЛОЖЬ, и такие строки будут считаться не содержащими указанный текст.
- Чтобы учитывать регистр, замените в формуле функцию ПОИСК на функцию НАЙТИ.
Устранение неполадок: Подход с использованием формулы не приводит к потере данных до тех пор, пока вы фактически не выполните команду «Удалить строки». Всегда внимательно проверяйте результаты ИСТИНА/ЛОЖЬ, и перед массовым удалением настоятельно рекомендуется создать резервную копию.
Связанные статьи:
- Удалить Вся строка, если ячейка не содержит ноль в Excel
- Удалить строки, не соответствующие критериям на другом листе
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек