Как отсортировать или отфильтровать данные по зачёркнутому тексту в Excel?
Пользователи Excel часто используют зачёркивание, чтобы помечать записи и элементы, которые устарели, завершены или больше неактуальны в наборе данных. При работе с большими списками — такими как учётные записи инвентаря, трекеры задач или журналы контактов — особенно удобно быстро выделять такие помеченные строки. Однако если вы хотите упорядочить или изолировать все строки, содержащие зачёркнутый текст, Excel не предлагает встроенных функций для сортировки или фильтрации непосредственно по этому форматированию. Это ограничение может замедлить работу, особенно при выполнении задач, таких как аудит, очистка данных или подготовка отчётов. К счастью, существуют несколько эффективных подходов, позволяющих сортировать или фильтровать данные на основе зачёркивания, чтобы вы могли сосредоточиться на соответствующих записях или предпринять дальнейшие действия с помеченными элементами.
Сортировка или фильтрация данных по зачёркнутому тексту с помощью пользовательской функции
Фильтрация данных по зачёркнутому тексту с помощью Kutools для Excel
Сортировка или фильтрация данных по зачёркнутому тексту с помощью пользовательской функции
Хотя Excel предлагает гибкие инструменты для работы с текстом и числами, его стандартные функции Сортировка и Фильтр не поддерживают прямую работу с форматированием ячеек, таким как зачёркивание. Из-за этого вы не можете, например, переместить все строки с зачёркнутым текстом вниз или отфильтровать их без дополнительных действий. Чтобы обойти это ограничение, создайте простую пользовательскую функцию (UDF) с помощью VBA — она поможет вам определить ячейки с зачёркнутым форматированием. После этого вы сможете легко применить стандартные инструменты Excel «Сортировка» или «Фильтр» к этим вспомогательным данным.
Рассмотрим следующий набор данных, в котором определённые строки содержат зачёркнутый текст для обозначения завершённых элементов. Выполнив приведённые ниже шаги, вы сможете эффективно сортировать или фильтровать ячейки с зачёркнутым текстом:
1. Нажмите одновременно ALT + F11, чтобы открыть окно Microsoft Visual Basic для приложений. Если вы впервые используете VBA, убедитесь, что параметры Excel разрешают запуск макросов.
2. В окне VBA выберите Вставка > Модуль. Затем скопируйте и вставьте следующий код в модуль. Эта пользовательская функция поможет определить, используется ли в ячейке зачёркивание.
Function HasStrike(Rng As Range) As Boolean
HasStrike = Rng.Font.Strikethrough
End Function
3. После вставки кода не забудьте сохранить и закрыть окно VBA. В пустой ячейке рядом с вашими данными — обычно в первой строке нового столбца — введите следующую формулу:=HasStrike(A2). В данном примере формула введена в ячейку C2. Эта формула вернёт значение ИСТИНА для ячеек с зачёркиванием и ЛОЖЬ для обычных записей.
Примечание: В примере формулы A2 указывает на ячейку, проверяемую на наличие зачёркивания. Если ваши данные начинаются с другой строки или столбца, скорректируйте ссылку на ячейку соответствующим образом. Чтобы избежать возможных ошибок, всегда убедитесь, что ссылка на целевую ячейку соответствует вашему диапазону данных.
4. Перетащите маркер заполнения (маленький квадрат в правом нижнем углу ячейки с формулой) вниз или вправо, чтобы применить формулу ко всему диапазону данных. Как показано ниже, все ячейки с зачёркиванием будут содержать значение ИСТИНА, а остальные — ЛОЖЬ.
Совет: Для больших наборов данных дважды щёлкните маркер заполнения, чтобы автоматически заполнить все оставшиеся строки таблицы. Если данные изменятся, возможно, потребуется повторно применить формулу к обновлённым строкам.
5. Благодаря этому новому вспомогательному столбцу вы теперь можете использовать встроенные функции сортировки и фильтрации в Excel:
A: Сортировка записей с зачёркиванием вверху или внизу списка
(1.)Выделите любую ячейку в вашем диапазоне данных и выберите Данные > Сортировка. Откроется диалоговое окно «Сортировка».
(2.)Если появится диалоговое окно Предупреждение о сортировке, выберите Расширить выделенный диапазон, чтобы отсортировать всю строку целиком и сохранить целостность данных.
(3.)В диалоговом окне Сортировка выберите вспомогательный столбец в качестве критерия сортировки из раскрывающегося списка Столбец. В поле Сортировать по выберите Значения. Затем задайте порядок Сортировкатак, чтобы записи с зачёркиванием отображались либо внизу ()От наименьшего к наибольшему), либо вверху (От наибольшего к наименьшему) набора данных — в зависимости от ваших предпочтений.
(4.) Нажмите ОК, чтобы завершить сортировку. Теперь все строки с зачёркиванием будут сгруппированы вместе — вверху или внизу таблицы, как вы и задали.
Совет: При сортировке, если у вас есть строки заголовков, убедитесь, что выделяете только данные. Чтобы записи с зачёркиванием отображались сверху, выберите в раскрывающемся списке порядка сортировки пункт От наибольшего к наименьшему.
B: Фильтрация записей по зачёркнутому тексту
(1.)Выделите весь диапазон данных, например A1:C14. Затем выберите Данные > Фильтр, чтобы включить раскрывающиеся списки фильтров для каждого столбца.
(2.)Щёлкните стрелку раскрывающегося списка фильтра рядом с заголовком вспомогательного столбца (например, C1) и установите флажок только напротив значения ИСТИНА. Так вы мгновенно отобразите только строки с зачёркнутым форматированием.
(3.)Нажмите ОК, чтобы применить фильтр — останутся видимыми только записи, помеченные зачёркиванием.
Примечание: После сортировки или фильтрации вы можете удалить вспомогательный столбец, чтобы привести лист в порядок. Убедитесь, что вам больше не понадобится повторно выполнять эти действия, прежде чем удалять столбец.
Мера предосторожности: Если ваши данные содержат формулы, ссылающиеся на другие листы, или если вы копируете лист в другое место, убедитесь, что код пользовательской функции остаётся доступным. Также помните: чтобы изменения форматирования вступили в силу после применения пользовательской функции, необходимо обновить вспомогательный столбец.
Устранение неполадок:Если при вводе формул появляется ошибка #ИМЯ?, убедитесь, что код VBA сохранён и макросы включены. Если результаты сортировки или фильтрации не обновляются корректно, попробуйте пересчитать лист (нажмите)F9) или повторно введите формулу.
Фильтрация данных по зачёркнутому тексту с помощью Kutools для Excel
Если вы ищете высокоэффективное решение для работы с ячейками, отформатированными с помощью Фильтр ячеек со стрижкой, без использования формул или VBA, Kutools для Excel предлагает простой способ — благодаря своей функции Фильтр ячеек со стрижкой. Эта функция мгновенно изолирует все записи, отформатированные зачёркиванием, экономя ваше время и усилия при управлении данными.
После установки Kutools для Excelвыполните следующие действия, чтобы напрямую фильтровать ячейки с зачёркнутым форматированием:
1. Выделите столбец, который нужно отфильтровать по ячейкам с зачёркиванием, затем перейдите в меню KUTOOLS PLUS > Специальный фильтр > Фильтр ячеек со стрижкой.
2. Появится диалоговое окно с информацией о количестве ячеек, соответствующих критерию зачёркивания.
3. Нажмите ОК, чтобы применить фильтр — теперь отобразятся все ячейки с зачёркнутым форматированием.
Скачайте Kutools для Excel и начните бесплатный пробный период прямо сейчас!
Преимущества: Kutools для Excel автоматизирует этот процесс — никакой ручной маркировки, формул или знаний VBA не требуется! Метод особенно полезен активным пользователям Excel, которым часто приходится сегментировать данные по форматированию. А ещё Kutools предлагает дополнительные инструменты для пакетного форматирования и фильтрации.
Альтернатива: обнаружение и Фильтр ячеек со стрижкой ячеек с помощью вспомогательного столбца в Excel и ручной маркировки
В ситуациях, когда использование VBA ограничено или запрещено — например, в определённых рабочих средах или при передаче файлов пользователям, не знакомым с включением макросов, — вы можете предпочесть подход на основе формул с ручной маркировкой.
Создайте новый столбец рядом с вашими данными и вручную укажите ИСТИНА для строк, отформатированных зачёркиванием, и ЛОЖЬ — для всех остальных. Несмотря на ручной ввод, этот метод не требует специальных разрешений и отлично подходит для небольших или редко используемых наборов данных.
После маркировки строк используйте стандартные функции Сортировка и Фильтр, описанные выше, чтобы упорядочить или выделить данные, помеченные зачёркиванием. Этот метод менее подвержен ошибкам в средах, где недоступен VBA, и обеспечивает совместимость между различными версиями Excel.
Совет: Используйте функцию Поиск и замена (Ctrl+F), установив в параметрах «Формат» значение «Зачёркивание», чтобы быстро находить такие ячейки и вручную помечать их.
См. также:
Как сортировать строки по нечётным или чётным числам в Excel?
Как отсортировать строки в Excel так, чтобы пустые ячейки оказались вверху?
Как сортировать данные по наиболее часто встречающемуся значению в Excel?
Как сортировать Адрес электронной почты по домену в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек