Как удалить строки, не содержащие определенный текст в 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) введите следующую формулу (замените "текст" на вашу желаемую строку для поиска):
=ISNUMBER(SEARCH("text",A2))Эта формула возвращает ИСТИНА, если ячейка в A2 содержит данный текст (как подстроку, без учета регистра), и ЛОЖЬ в противном случае.
3. Нажмите Enter, затем дважды щелкните нижний правый угол ячейки, чтобы заполнить формулу вниз по всем другим строкам в вашем наборе данных.
4. Используйте функцию фильтра на вспомогательном столбце. Нажмите стрелку вниз, выберите ЛОЖЬ, чтобы показать только строки, не содержащие ваш целевой текст. Выберите все эти отфильтрованные строки (исключая заголовок, если применимо), щелкните правой кнопкой мыши и выберите Удалить строку.
5. После удаления очистите фильтр, чтобы восстановить вид. Проверьте результаты и удалите вспомогательный столбец, если необходимо.
Параметры и заметки:
- Если вам нужно найти точное значение ячейки (не подстроки), измените формулу на
=A2="текст". - Если ваши данные содержат пустые ячейки, ISNUMBER(SEARCH(...)) возвращает ЛОЖЬ, и эти строки будут рассматриваться как не содержащие текст.
- Для чувствительного к регистру совпадения замените SEARCH на FIND в формуле.
Устранение неполадок: Подход с формулой является не разрушительным до тех пор, пока вы фактически не удалите строки. Всегда дважды проверяйте свои результаты ИСТИНА/ЛОЖЬ и рассмотрите возможность сохранения резервной копии перед массовым удалением.
Связанные статьи:
- Удаление целых строк, если ячейка не содержит ноль в 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек