Как отфильтровать зачеркнутый текст в excel

Обновлено: 07.07.2024

Зачастую текст, который достается нам для работы в ячейках листа Microsoft Excel далек от совершенства. Если он был введен другими пользователями (или выгружен из какой-нибудь корпоративной БД или ERP-системы) не совсем корректно, то он легко может содержать:

лишние пробелы перед, после или между словами (для красоты!)
ненужные символы ("г." перед названием города)
невидимые непечатаемые символы (неразрывный пробел, оставшийся после копирования из Word или "кривой" выгрузки из 1С, переносы строк, табуляция)
апострофы (текстовый префикс – спецсимвол, задающий текстовый формат у ячейки)

Давайте рассмотрим способы избавления от такого "мусора".

Замена

"Старый, но не устаревший" трюк. Выделяем зачищаемый диапазон ячеек и используем инструмент Заменить с вкладки Главная – Найти и выделить (Home – Find & Select – Replace) или жмем сочетание клавиш Ctrl+H.

Изначально это окно было задумано для оптовой замены одного текста на другой по принципу "найди Маша – замени на Петя", но мы его, в данном случае, можем использовать его и для удаления лишнего текста. Например, в первую строку вводим "г." (без кавычек!), а во вторую не вводим ничего и жмем кнопку Заменить все (Replace All). Excel удалит все символы "г." перед названиями городов:

Только не забудьте предварительно выделить нужный диапазон ячеек, иначе замена произойдет на всем листе!

Удаление пробелов

Если из текста нужно удалить вообще все пробелы (например они стоят как тысячные разделители внутри больших чисел), то можно использовать ту же замену: нажать Ctrl+H, в первую строку ввести пробел, во вторую ничего не вводить и нажать кнопку Заменить все (Replace All).

Однако, часто возникает ситуация, когда удалить надо не все подряд пробелы, а только лишние – иначе все слова слипнутся друг с другом. В арсенале Excel есть специальная функция для этого – СЖПРОБЕЛЫ (TRIM) из категории Текстовые. Она удаляет из текста все пробелы, кроме одиночных пробелов между словами, т.е. мы получим на выходе как раз то, что нужно:

Удаление непечатаемых символов

В некоторых случаях, однако, функция СЖПРОБЕЛЫ (TRIM) может не помочь. Иногда то, что выглядит как пробел – на самом деле пробелом не является, а представляет собой невидимый спецсимвол (неразрывный пробел, перенос строки, табуляцию и т.д.). У таких символов внутренний символьный код отличается от кода пробела (32), поэтому функция СЖПРОБЕЛЫ не может их "зачистить".

Вариантов решения два:

Аккуратно выделить мышью эти спецсимволы в тексте, скопировать их (Ctrl+C) и вставить (Ctrl+V) в первую строку в окне замены (Ctrl+H). Затем нажать кнопку Заменить все (Replace All) для удаления.
Использовать функцию ПЕЧСИМВ (CLEAN) . Эта функция работает аналогично функции СЖПРОБЕЛЫ, но удаляет из текста не пробелы, а непечатаемые знаки. К сожалению, она тоже способна справится не со всеми спецсимволами, но большинство из них с ее помощью можно убрать.

Функция ПОДСТАВИТЬ

Замену одних символов на другие можно реализовать и с помощью формул. Для этого в категории Текстовые в Excel есть функция ПОДСТАВИТЬ (SUBSTITUTE) . У нее три обязательных аргумента:

Текст в котором производим замену
Старый текст – тот, который заменяем
Новый текст – тот, на который заменяем

С ее помощью можно легко избавиться от ошибок (замена "а" на "о"), лишних пробелов (замена их на пустую строку ""), убрать из чисел лишние разделители (не забудьте умножить потом результат на 1, чтобы текст стал числом):

Удаление апострофов в начале ячеек

Апостроф (') в начале ячейки на листе Microsoft Excel – это специальный символ, официально называемый текстовым префиксом. Он нужен для того, чтобы дать понять Excel, что все последующее содержимое ячейки нужно воспринимать как текст, а не как число. По сути, он служит удобной альтернативой предварительной установке текстового формата для ячейки (Главная – Число – Текстовый) и для ввода длинных последовательностей цифр (номеров банковских счетов, кредитных карт, инвентарных номеров и т.д.) он просто незаменим. Но иногда он оказывается в ячейках против нашей воли (после выгрузок из корпоративных баз данных, например) и начинает мешать расчетам. Чтобы его удалить, придется использовать небольшой макрос. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert - Module) и введите туда его текст:

Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то апострофы перед содержимым выделенных ячеек исчезнут.

Английские буквы вместо русских

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

Можно, конечно, вручную заменять символы латинцы на соответствующую им кириллицу, но гораздо быстрее будет сделать это с помощью макроса. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert - Module) и введите туда его текст:

Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу :)

На листе Excel вы можете отформатировать некоторые значения ячеек с зачеркиванием, чтобы пометить ненужные ячейки. Иногда вам нужно отфильтровать все ячейки, отформатированные с зачеркиванием, чтобы обрабатывать их по мере необходимости. На самом деле Excel не поддерживает функцию быстрого решения этой задачи, но с Kutools for ExcelАвтора Зачеркнутый фильтр вы можете легко отфильтровать все зачеркнутые значения ячеек.

Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу . Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Фильтрация ячеек с зачеркиванием в определенном столбце в Excel

1. Выберите столбец, в котором вы хотите отфильтровать все ячейки с зачеркиванием, а затем щелкните Kutools Plus > Специальный фильтр > Зачеркнутый фильтр, см. снимок экрана:

2. Затем появится окно с подсказкой, чтобы напомнить вам, сколько сопоставленных ячеек найдено, см. Снимок экрана:

3. Затем нажмите OK все ячейки, отформатированные с зачеркиванием, были отфильтрованы, как показано на следующем снимке экрана:

Фильтрация ячеек с зачеркиванием и затенением шрифта или цвета фона в Excel

Эта удобная утилита также может помочь вам затенять шрифт или цвет фона для отфильтрованных результатов, пожалуйста, сделайте следующее:

1. Выберите столбец, в котором вы хотите отфильтровать все ячейки с зачеркиванием, а затем щелкните Kutools Plus > Специальный фильтр > Специальный фильтр, см. снимок экрана:

2. В выскочившем Специальный фильтр диалоговом окне выполните следующие операции:

(1.) Выберите Формат вариант в Правила фильтрации коробка;

(2.) Затем выберите Зачеркнутый шрифт из выпадающего списка;

(3.) Наконец, в Обработка результатов В поле раздела укажите цвет шрифта или цвет фона для отфильтрованных результатов.

Внимание: Если вы проверите Выбрать всю строку вариант, после фильтрации будут выбраны все строки.

3. Затем нажмите Ok Кнопка, и появится окно подсказки, чтобы напомнить вам, сколько найдено зачеркнутых ячеек, см. снимок экрана:

4, Наконец, нажмите OK все ячейки с зачеркиванием были отфильтрованы, а отфильтрованные результаты будут заполнены шрифтом или цветом фона, как вы указали. Смотрите скриншот:

Демонстрация: фильтрация ячеек с зачеркиванием в определенном столбце

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Рекомендуемые инструменты для повышения производительности
Следующие ниже инструменты могут значительно сэкономить ваше время и деньги. Какой из них вам подходит?
Office Tab : Использование удобных вкладок в вашем офисе , как и в случае Chrome, Firefox и New Internet Explorer.
Kutools for Excel : Более 300 дополнительных функций для Excel 2019, 2016, 2013, 2010, 2007 и Office 365.
Classic Menu for Office : Верните знакомые меню в Office 2007, 2010, 2013, 2016, 2019 и 365, как если бы это были Office 2000 и 2003.

Kutools for Excel

Описанная выше функциональность - лишь одна из 300 мощных функций Kutools for Excel.

Разработано для Excel (Office) 2019, 2016, 2013, 2010, 2007 и Office 365. Бесплатная загрузка и использование в течение 60 дней.

В Excel мы всегда форматируем зачеркивание некоторых ячеек, которые указывают, что значения ячеек бесполезны или недействительны, чтобы мы могли более точно анализировать данные. В этой статье я расскажу о том, как выполнить некоторые вычисления в диапазоне с этими зачеркнутыми ячейками в Excel.

Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Подсчет зачеркнутых ячеек в Excel

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

Если вы хотите узнать, сколько ячеек с зачеркнутым форматом в диапазоне, вы можете создать функцию, определяемую пользователем, выполните следующие действия:

1. Удерживайте ALT + F11 , чтобы открыть Окно Microsoft Visual Basic для приложений.

2. Нажмите Вставить > Модульи вставьте следующий код в Окно модуля.

Код VBA: подсчет зачеркнутых ячеек

3. Затем сохраните и закройте этот код, вернитесь на рабочий лист и введите эту формулу = Счетчик (A2: B14) в пустую ячейку, см. снимок экрана:

4. Затем нажмите Enter key, и подсчитаны все зачеркнутые ячейки. Смотрите скриншот:

Подсчет без зачеркивания ячеек в Excel

Но иногда вы можете подсчитать количество только нормальных ячеек, исключая зачеркнутые. Следующий код может вам помочь.

1. Удерживайте ALT + F11 , чтобы открыть Окно Microsoft Visual Basic для приложений.

2. Нажмите Вставить > Модульи вставьте следующий код в Окно модуля.

Код VBA: подсчет без зачеркивания ячеек

3. Затем сохраните и закройте этот код, вернитесь на свой рабочий лист, введите эту формулу = countnostrike (A2: B14) в пустую ячейку и нажмите Enter key, то вы получите нужный результат.

Внимание: В формулах выше A2: B14 - это диапазон, в котором вы хотите применить формулы.

Сумма исключить зачеркнутые ячейки в Excel

Поскольку зачеркнутые ячейки не используются, здесь я хочу суммировать только нормальные числа без зачеркнутых чисел. Для решения этой задачи вам также понадобится функция, определяемая пользователем.

1. Удерживайте ALT + F11 , чтобы открыть Окно Microsoft Visual Basic для приложений.

2. Нажмите Вставить > Модульи вставьте следующий код в Окно модуля.

Код VBA: Сумма исключить зачеркнутые ячейки

3. Затем сохраните и закройте этот код, вернитесь на свой рабочий лист, введите эту формулу = excstrike (B2: B14) в пустую ячейку и нажмите Enter нажмите клавишу, и вы получите суммирование всех чисел без зачеркнутых ячеек. Смотрите скриншот:

Внимание: В формулах выше B2: B14 - это диапазон, в котором вы хотите суммировать ячейки без зачеркивания.

В Excel мы часто форматируем данные с зачеркиванием, чтобы указать, что элементы больше не требуются. А иногда нам нужно отфильтровать или отсортировать данные, содержащие зачеркивание, чтобы мы могли быстро обработать эти строки. Есть ли какие-нибудь быстрые приемы для сортировки или фильтрации данных по зачеркиванию?

Сортировка или фильтрация данных по зачеркиванию с помощью функции, определяемой пользователем

Фильтрация данных путем зачеркивания с помощью Kutools for Excel

Быстрая и простая фильтрация данных путем зачеркивания:

Kutools for Excel Утилита Фильтр зачеркивания поможет вам быстро и легко отфильтровать все ячейки, отформатированные как зачеркнутые.

Сортировка или фильтрация данных путем зачеркивания с помощью функции, определяемой пользователем

К сожалению, у нас нет прямого способа сортировать или фильтровать зачеркнутые данные с помощью функции Sort или Filter , но мы можем создать простую функцию, определяемую пользователем чтобы сначала пометить данные зачеркиванием, а затем применить функцию сортировки или фильтрации.

Предположим, у меня есть следующий диапазон данных с зачеркиванием, как показано на скриншоте, вы можете отсортировать или отфильтровать зачеркнутые данные с помощью следующих шаги:

1 . Удерживая нажатыми клавиши ALT + F11 , откройте окно Microsoft Visual Basic для приложений .

2 . Нажмите Вставить > Module и вставьте следующий код в окно модуля .

3 . Затем сохраните и закройте это окно, в пустой ячейке рядом с вашими данными введите эту формулу = HasStrike (A2) , в этом случае я введу ее в ячейку C2. См. Снимок экрана:

4 . Затем перетащите маркер заполнения к ячейкам, к которым вы хотите применить эту формулу. Как показано на следующем снимке экрана, вы можете видеть, что если данные отформатированы как зачеркнутые, они отображаются как TRUE , а обычные данные отображаются как FALSE . .

5 . Затем вы можете отсортировать или отфильтровать по этому новому столбцу C.

A: Сортировка зачеркнутых данных внизу или вверху записей:

(1.) Нажмите Данные > Сортировка , см. снимок экрана:

(2.) Затем в появившемся Предупреждение о сортировке , установите флажок Развернуть выделение .

(3.) И нажмите кнопку Сортировка в В диалоговом окне «Сортировка выберите Столбец C , который представляет собой ваш новый созданный столбец, из раскрывающегося списка Столбец , а затем выберите Значения. в разделе Сортировка , наконец, выберите Порядок , который вы хотите отсортировать. Смотрите снимок экрана:

(4. ) Затем нажмите OK , чтобы закрыть это диалоговое окно, и зачеркнутые данные будут отсортированы в нижней части значения. См. Снимок экрана:

Совет : если вы хотите отсортировать зачеркнутые данные в верхней части всех данных, просто выберите на шаге 3 от наибольшего до наименьшего.

B: Отфильтруйте записи зачеркнутым

(1.) Выберите весь диапазон данных, здесь я выбираю диапазон A1: C14 и нажимаю Данные > Фильтр , см. снимок экрана:

(2.) Затем нажмите кнопку раскрывающегося списка рядом с ячейкой C1, а затем просто отметьте параметр ИСТИНА , см. снимок экрана:

(3.) Затем нажмите OK , и все зачеркнутые данные будут отфильтрованы.

Примечание : после завершения сортировки и фильтрации вы можете удалить значение в столбце C.

Фильтровать данные с помощью зачеркивания с помощью Kutools for Excel

Если вы есть Kutools for Excel , с его функцией Зачеркнутый фильтр , вы можете быстро отфильтровать все ячейки, отформатированные как зачеркнутые.

Примечание. Чтобы применить этот зачеркнутый фильтр , во-первых, вы должны загрузить Kutools for Excel , а затем быстро и легко примените эту функцию.

После установки Kutools for Excel сделайте следующее:

1 . Выберите столбец, в котором вы хотите отфильтровать ячейки с зачеркиванием, а затем нажмите Kutools Plus > Специальный фильтр > Зачеркнутый фильтр , см. снимок экрана:

2 . Затем появится окно с подсказкой, чтобы напомнить вам, сколько ячеек соответствует критериям, см. Снимок экрана:

3 . Затем нажмите кнопку OK , все ячейки, сформированные с зачеркиванием, будут отфильтрованы, см. Снимок экрана:

Как отсортировать строки по нечетным или четным числам в Excel?

Как отсортировать строки, чтобы пустые ячейки располагались сверху в Excel?

Читайте также: