Удалить лишние данные в Excel формулами можно на любом уровне: символ — =ПОДСТАВИТЬ(A2;"-";""), слово целиком — =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(" "&A2&" ";" купить ";" ")), повторы в столбце — =УНИК(A2:A100) в Excel 365, а пустые строки помечает =СЧЁТЗ(A2:C2)=0 для фильтра. Формулы удаляют содержимое ячеек не напрямую: результат появляется в соседнем столбце, и его вставляют поверх исходника как значения. Ниже — пример для каждого подраздела.
Как массово удалять ненужные данные в таблицах Excel?
Чтобы разобраться в этом вопросе, сперва нужно понять, из чего состоят данные, которые вы хотите удалить, что их объединяет.
Какие данные можно удалить в Excel
Удаляемые данные можно определять по:
- Объектной модели Excel:
- таблицы (книги)
- листы
- столбцы
- строки
- ячейки
- формулы
- текст ячеек
- форматирование ячеек
- Типу данных:
- числа
- текст
- даты
- время
- проценты
- Языковой модели:
- символы
- слова
- фразы
- предложения
- текст
- абзацы
- Определенным атрибутам самих этих удаляемых объектов:
- Наличие содержимого (пустые или содержащие что-либо)
- Уникальность (уникальные или повторяющиеся)
- Форматирование (шрифт, начертание, цвет, заливка и т.д.)
И вероятно это не все варианты, как можно определить данные перед их удалением.
Для многих из вышеперечисленных случаев существуют штатные процедуры в Excel. В первую очередь рекомендую почитать подробную статью, как процедура "Поиск и замена" позволяет массово находить лишние данные, прежде чем вы выделите и удалите их. В частности, замена на "пустоту" тоже является удалением данных.
Удаление данных с !SEMTools
Далеко не всегда для массового удаления данных в Excel есть простые и удобные инструменты.
Именно для таких случаев я разработал собственные, и организовал их в удобной иерархии в разделе "УДАЛИТЬ" в своей надстройке.
Перед применением функций этого раздела рекомендуется воспользоваться аналогичными возможностями в группах «ОБНАРУЖИТЬ» и «ИЗВЛЕЧЬ», чтобы сначала своими глазами увидеть, какие элементы будут удалены.
Символы
Меню позволяет удалить определенные символы из диапазона, например:
- удалить из текста цифры
- удалить пунктуацию (знаки препинания)
- убрать пробелы в начале или конце строк
- удалить определенное количество символов с начала
- аналогично, удалить N символов в конце ячейки (справа)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Текст | Без цифр | Без пунктуации | Без 1-го символа |
| 2 | Диван, 2 шт.! | Диван, шт.!=СЖПРОБЕЛЫ(REGEXREPLACE(A2;"\d";"")) | Диван 2 шт=REGEXREPLACE(A2;"[^A-Za-zА-Яа-яЁё0-9 ]";"") | иван, 2 шт.!=ПСТР(A2;2;1000) |
Все способы для символов — в разделе удалить символы.
Слова
Слова или целый список слов — Excel не разбирается в том, что такое слова, т.к. в его синтаксисе нет таких сущностей. Узнайте про все процедуры надстройки, которыми можно удалить слова из текста в Excel, не повреждая при этом остальные данные.
| A | B | |
|---|---|---|
| 1 | Запрос | Без «купить» |
| 2 | купить диван | диван=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(" "&A2&" ";" купить ";" ")) |
| 3 | диван купить недорого | диван недорого=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(" "&A3&" ";" купить ";" ")) |
| 4 | купитьдиван | купитьдиван=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(" "&A4&" ";" купить ";" ")) |
Пробелы вокруг слова не дают задеть «купитьдиван». Другие формулы — в разделе удалить слова.
В английском Excel: =TRIM(SUBSTITUTE(" "&A2&" "," купить "," "))
Ячейки
Удалить ячейки по условию - !SEMTools предлагает более десятка таковых.
| A | B | |
|---|---|---|
| 1 | Значение | Без ячеек с «тест» |
| 2 | заказ 125 | заказ 125=ЕСЛИ(ЕЧИСЛО(ПОИСК("тест";A2));"";A2) |
| 3 | тест 1 | =ЕСЛИ(ЕЧИСЛО(ПОИСК("тест";A3));"";A3) |
| 4 | заказ 126 | заказ 126=ЕСЛИ(ЕЧИСЛО(ПОИСК("тест";A4));"";A4) |
| 5 | ТЕСТОВЫЙ | =ЕСЛИ(ЕЧИСЛО(ПОИСК("тест";A5));"";A5) |
Ячейки, где найден фрагмент, становятся пустыми; остальные переносятся как есть. ПОИСК не различает регистр. Подробнее — удалить ячейки по условию.
В английском Excel: =IF(ISNUMBER(SEARCH("тест",A2)),"",A2)
Диапазоны
Сюда входят макросы, которые помогают:
- Удалить строки по условию;
- Удалить пустые строки в Excel, в т.ч. на нескольких листах
- аналогично, удалить пустые столбцы.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ФИО | Город | Сумма | Пустая строка? |
| 2 | Иванов | Москва | 100 | ЛОЖЬ=СЧЁТЗ(A2:C2)=0 |
| 3 | ИСТИНА=СЧЁТЗ(A3:C3)=0 | |||
| 4 | Петров | ЛОЖЬ=СЧЁТЗ(A4:C4)=0 |
Отфильтруйте ИСТИНА и удалите видимые строки. Строка 4 не пустая — в ней есть фамилия. Подробнее — удалить пустые строки.
В английском Excel: =COUNTA(A2:C2)=0
Также в этом меню находятся процедуры удаления листов открытых книг по условию.
Дубликаты
Раздел посвящён различным способам удалять дубликаты ячеек в Excel.
В Excel есть функция удаления дубликатов, но программа считает дублями только полностью совпадающие строки - достаточно 1 лишнего пробела и ячейки уже не будут считаться повторяющимися. Такие ячейки называются неявными дублями, им посвящен целый комплекс процедур !SEMTools.
А когда нужно удалить дубли без смещения строк, придется и вовсе использовать множество ухищрений, и здесь надстройка тоже приходит на помощь. Можно также сравнить 2 столбца и удалить дубли, с учётом порядка слов и без.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Город | Повтор? | Без дублей | |
| 2 | Москва | ЛОЖЬ=СЧЁТЕСЛИ($A$2:A2;A2)>1 | Москва=УНИК(A2:A6) | |
| 3 | Казань | ЛОЖЬ=СЧЁТЕСЛИ($A$2:A3;A3)>1 | Казань | |
| 4 | москва | ИСТИНА=СЧЁТЕСЛИ($A$2:A4;A4)>1 | Тула | |
| 5 | Тула | ЛОЖЬ=СЧЁТЕСЛИ($A$2:A5;A5)>1 | ||
| 6 | Казань | ИСТИНА=СЧЁТЕСЛИ($A$2:A6;A6)>1 |
СЧЁТЕСЛИ с растущим диапазоном $A$2:A4 помечает второе и следующие вхождения — их можно отфильтровать и удалить, не сдвигая остальные строки. УНИК сразу выводит список без повторов. Обе функции не различают регистр. Подробнее — удалить дубликаты.
В английском Excel: =COUNTIF($A$2:A2,A2)>1
Формулы
Удаление формул из всей книги при большом количестве листов — трудоемкая задача и может занять много времени. Узнайте, как удалить формулы, оставив значения, как из диапазона, так со всего листа и книги, в пару кликов.
Удаление данных в Excel — частые вопросы
Как удалить определённые символы из всех ячеек?
Через Ctrl+H с пустым полем «Заменить на» или формулой =ПОДСТАВИТЬ(A2;"-";"").
Как удалить строки по условию?
Добавьте столбец с проверкой условия, отфильтруйте ИСТИНА и удалите видимые строки. Подробнее — удалить строки по условию.
Как удалить дубликаты, не сдвигая строки?
Пометьте повторы формулой =СЧЁТЕСЛИ($A$2:A2;A2)>1 и очистите отмеченные ячейки — смотрите дубли без смещения строк.
Как удалить формулы и оставить значения?
Скопируйте диапазон и вставьте как значения: Ctrl+Alt+V → «значения». Подробнее — удалить формулы.
Удалить символы, слова, ячейки, строки, столбцы, дубли и формулы по десяткам условий — прямо на месте, без вспомогательных столбцов — можно в пару кликов с !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как удалить ненужные данные в Excel
| Что удаляем | Формула или способ |
|---|---|
| Символы | ПОДСТАВИТЬ, REGEXREPLACE, ПСТР |
| Слова | ПОДСТАВИТЬ с пробелами по краям |
| Ячейки по условию | ЕСЛИ + ПОИСК в соседнем столбце |
| Пустые строки | СЧЁТЗ(…)=0 + фильтр |
| Дубликаты | УНИК или СЧЁТЕСЛИ с растущим диапазоном |
| Формулы | Вставка значений Ctrl+Alt+V |
Надеюсь, на этой странице вы нашли то, что искали. Формулы удобны, когда исходные данные нужно сохранить, а для разовой чистки больших таблиц быстрее процедуры раздела УДАЛИТЬ.