Удалить содержимое ячеек по условию в Excel формулой можно так: в соседнем столбце =ЕСЛИ(ЕЧИСЛО(ПОИСК("тест";A2));"";A2) оставит пустыми ячейки с нужным фрагментом и перенесёт остальные, а =ЕСЛИ(СЧЁТЕСЛИ($A$2:A2;A2)>1;"";A2) очистит повторы, не сдвигая строки. Затем результат вставляют поверх исходного столбца как значения. Ниже — формула под каждое условие.
Данный раздел посвящен ситуациям, когда нужно по определенному условию удалить содержимое ячеек в Excel.
Рекомендуется не рубить с плеча и не удалять ячейки сразу же, а сначала воспользоваться аналогичными макросами "Обнаружить ячейки" и "Извлечь ячейки", чтобы, соответственно, найти и посмотреть на те данные, которые предстоит удалить.
Наиболее часто требуется удалить содержимое ячейки, если в нем присутствует определенный текст или список текстовых паттернов. Нужные задачи отображены в соответствующих пунктах меню надстройки.
Удаление ячеек по их содержимому
Ячейки можно удалять в зависимости от того, что они содержат. Это могут быть слова, последовательности символов. Последовательности можно выражать как простой подстрокой, так и регулярным выражением. Для каждого из случаев в надстройке предусмотрен свой инструмент.
Удалить ячейки, содержащие слова
- По условию вхождения в ячейки определенных слов:
- Удалить → Ячейки → Содержащие слово
- Удалить → Ячейки → Содержащие слова — любое из списка
Очистить ячейки со словом A B 1 Значение Результат 2 аренда квартиры =ЕСЛИ(ЕЧИСЛО(ПОИСК(" аренда ";" "&A2&" "));"";A2) 3 арендатор арендатор=ЕСЛИ(ЕЧИСЛО(ПОИСК(" аренда ";" "&A3&" "));"";A3) 4 продажа продажа=ЕСЛИ(ЕЧИСЛО(ПОИСК(" аренда ";" "&A4&" "));"";A4) Пробелы вокруг слова: «арендатор» не считается словом «аренда» и остаётся.
В английском Excel: =IF(ISNUMBER(SEARCH(" аренда "," "&A2&" ")),"",A2)
Удалить ячейки по паттернам (вхождению подстрок)
- По условию простого вхождения паттернов:
- Содержащие …
- Начинающиеся с …
- Заканчивающиеся на …
Начинается с … или заканчивается на … A B C 1 Файл Без начинающихся с «тест» Без оканчивающихся на .tmp 2 тест_отчёт.xlsx =ЕСЛИ(ЛЕВСИМВ(A2;4)="тест";"";A2) тест_отчёт.xlsx=ЕСЛИ(ПРАВСИМВ(A2;4)=".tmp";"";A2) 3 итог.tmp итог.tmp=ЕСЛИ(ЛЕВСИМВ(A3;4)="тест";"";A3) =ЕСЛИ(ПРАВСИМВ(A3;4)=".tmp";"";A3) 4 план.docx план.docx=ЕСЛИ(ЛЕВСИМВ(A4;4)="тест";"";A4) план.docx=ЕСЛИ(ПРАВСИМВ(A4;4)=".tmp";"";A4) Число в ЛЕВСИМВ и ПРАВСИМВ — длина фрагмента. Сравнение через «=» не различает регистр.
В английском Excel: =IF(LEFT(A2,4)="тест","",A2)
Удалить ячейки по регулярному выражению
- Иногда простых паттернов может не быть достаточно, тогда пригодится удаление ячеек по соответствию регулярному выражению.
| A | B | |
|---|---|---|
| 1 | Значение | Без ячеек из одних цифр |
| 2 | 12345 | =ЕСЛИ(REGEXMATCH(A2;"^\d+$");"";A2) |
| 3 | SKU-12345 | SKU-12345=ЕСЛИ(REGEXMATCH(A3;"^\d+$");"";A3) |
| 4 | 2026 год | 2026 год=ЕСЛИ(REGEXMATCH(A4;"^\d+$");"";A4) |
^ и $ привязывают шаблон к началу и концу, поэтому подходят только ячейки, целиком состоящие из цифр. Подробнее о шаблонах — регулярные выражения в Excel.
Удаление повторяющихся ячеек
Если значение ячейки не является уникальным, такую ячейку обычно следует удалить. Но сценариев такого удаления несколько.
Удалить дубликаты ячеек
!SEMTools позволяет:
- Удалить дубли без смещения строк в Excel
- Удалить неявные дубли
- Удалить один список из другого: когда ячейки одного столбца нужно сравнить с ячейками другого и удалить найденные
| A | B | |
|---|---|---|
| 1 | Город | Без повторов |
| 2 | Москва | Москва=ЕСЛИ(СЧЁТЕСЛИ($A$2:A2;A2)>1;"";A2) |
| 3 | Казань | Казань=ЕСЛИ(СЧЁТЕСЛИ($A$2:A3;A3)>1;"";A3) |
| 4 | Москва | =ЕСЛИ(СЧЁТЕСЛИ($A$2:A4;A4)>1;"";A4) |
| 5 | Тула | Тула=ЕСЛИ(СЧЁТЕСЛИ($A$2:A5;A5)>1;"";A5) |
| 6 | Казань | =ЕСЛИ(СЧЁТЕСЛИ($A$2:A6;A6)>1;"";A6) |
Диапазон $A$2:A4 растёт вместе со строкой, поэтому первое вхождение остаётся, а повторы очищаются. Строки не сдвигаются. Подробнее — дубли без смещения.
В английском Excel: =IF(COUNTIF($A$2:A2,A2)>1,"",A2)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Значение | Результат | Исключить | |
| 2 | Москва | Москва=ЕСЛИ(СЧЁТЕСЛИ($D$2:$D$3;A2)>0;"";A2) | Казань | |
| 3 | Казань | =ЕСЛИ(СЧЁТЕСЛИ($D$2:$D$3;A3)>0;"";A3) | Самара | |
| 4 | Тула | Тула=ЕСЛИ(СЧЁТЕСЛИ($D$2:$D$3;A4)>0;"";A4) | ||
| 5 | Самара | =ЕСЛИ(СЧЁТЕСЛИ($D$2:$D$3;A5)>0;"";A5) |
Очищаются ячейки, которые есть во втором списке (D2:D3). Сравнение не различает регистр.
В английском Excel: =IF(COUNTIF($D$2:$D$3,A2)>0,"",A2)
Удалить уникальные и повторяющиеся ячейки
- Редкая задача — удалить ячейки в зависимости от их встречаемости в столбце:
- удалить дубли включая первый
- удалить уникальные (не повторяющиеся) ячейки
Удалить повторы вместе с первым или уникальные A B C 1 Город Без всех повторяющихся Без уникальных 2 Москва =ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$6;A2)>1;"";A2) Москва=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$6;A2)=1;"";A2) 3 Казань =ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$6;A3)>1;"";A3) Казань=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$6;A3)=1;"";A3) 4 Москва =ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$6;A4)>1;"";A4) Москва=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$6;A4)=1;"";A4) 5 Тула Тула=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$6;A5)>1;"";A5) =ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$6;A5)=1;"";A5) 6 Казань =ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$6;A6)>1;"";A6) Казань=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$6;A6)=1;"";A6) Здесь диапазон закреплён целиком ($A$2:$A$6): формула видит, сколько раз значение встречается во всём столбце.
В английском Excel: =IF(COUNTIF($A$2:$A$6,A2)>1,"",A2)
Дополнительные возможности удаления ячеек
Также в меню Удалить слова находится процедура, которая позволяет удалить повторяющиеся значения в одной ячейке. А меню «Удалить строки» позволяет удалять целые строки на основе значений ячеек выделенного столбца.
Смотрите также:
- Как удалить лишние символы в Excel
- Как удалять определенные слова в Excel
- Как удалять ненужные столбцы и строки по множеству условий
- Как убрать формулы из ячеек и оставить только значения
Удаление ячеек по условию — частые вопросы
Как удалить содержимое ячеек по условию?
Формулой ЕСЛИ в соседнем столбце: =ЕСЛИ(условие;"";A2), затем вставьте результат как значения. Без формул — «Найти все» в Ctrl+F, Ctrl+A в списке найденного и Delete.
Как удалить повторяющиеся значения, но оставить первое?
Формулой =ЕСЛИ(СЧЁТЕСЛИ($A$2:A2;A2)>1;"";A2) — строки не сдвигаются.
Как удалить из списка значения, которые есть в другом списке?
Формулой =ЕСЛИ(СЧЁТЕСЛИ($D$2:$D$100;A2)>0;"";A2) — D2:D100 — список исключений.
Чем удаление ячеек отличается от удаления строк?
Здесь очищается содержимое, а строка остаётся на месте. Чтобы убрать строки целиком, смотрите удалить строки и столбцы.
Удалить содержимое ячеек, содержащих слово, паттерн или регулярное выражение, а также дубли, уникальные значения и неявные повторы — прямо на месте — можно в пару кликов с !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как удалить ячейки по условию в Excel
| Условие | Формула |
|---|---|
| Содержит слово | ЕСЛИ(ЕЧИСЛО(ПОИСК(" слово ";" "&A2&" "));"";A2) |
| Начинается / заканчивается на | ЕСЛИ(ЛЕВСИМВ / ПРАВСИМВ(A2;N)="…";"";A2) |
| Регулярное выражение | ЕСЛИ(REGEXMATCH(A2;"шаблон");"";A2) |
| Повторы, первое оставить | ЕСЛИ(СЧЁТЕСЛИ($A$2:A2;A2)>1;"";A2) |
| Есть в другом списке | ЕСЛИ(СЧЁТЕСЛИ(список;A2)>0;"";A2) |
Надеюсь, на этой странице вы нашли то, что искали. Все формулы раздела устроены одинаково — ЕСЛИ с условием и пустой строкой, — а для регулярной чистки больших диапазонов быстрее процедуры УДАЛИТЬ → Ячейки.