Как подсчитать или просуммировать ячейки, содержащие зачеркнутый текст, в Excel?
В Excel зачеркивание часто применяют к отдельным ячейкам, чтобы визуально пометить данные как устаревшие, нерелевантные или недействительные. Такой приём значительно упрощает управление информацией и помогает сосредоточиться на актуальных данных при анализе. Однако при выполнении расчётов — например, подсчёта или суммирования — может возникнуть необходимость разделить результаты для ячеек с зачеркиванием и без него. Умение решать такие задачи оптимизирует ваш рабочий процесс и обеспечит более точные выводы. В этой статье представлены несколько практических методов подсчёта и суммирования ячеек с зачеркиванием или без него в Excel с помощью настраиваемых функций VBA, которые обеспечивают гибкость для различных сценариев управления данными.
Подсчёт ячеек с зачеркиванием с помощью VBA в Excel
Подсчёт ячеек без зачеркивания с помощью VBA в Excel
Суммирование с исключением ячеек с зачеркиванием с помощью VBA в Excel
Подсчёт или суммирование ячеек с зачеркиванием с помощью Kutools для Excel
Подсчёт ячеек с зачеркиванием в Excel
Если вам нужно подсчитать общее количество ячеек с зачёркнутым текстом в заданном диапазоне, стандартными средствами Excel это сделать невозможно — в программе отсутствует встроенная формула, способная распознавать текстовое форматирование, такое как зачёркивание. Практичным решением в этом случае становится создание пользовательской функции (UDF) на языке VBA. Такой подход особенно полезен при работе со списками задач, учёте складских запасов или в любых других ситуациях, где зачёркивание служит для отметки завершённых, недействительных или устаревших данных.
1. Удерживая клавиши ALT + F11, откройте окно Microsoft Visual Basic for Applications.
2. В окне VBA выберите Вставка > Модуль, чтобы создать новый модуль, затем вставьте приведённый ниже код в окно модуля.
Код VBA: Подсчёт ячеек с зачеркиванием
Public Function CountStrike(pWorkRng As Range) As Long
'Update 20140819
Application.Volatile
Dim pRng As Range
Dim xOut As Long
xOut = 0
For Each pRng In pWorkRng
If pRng.Font.Strikethrough Then
xOut = xOut + 1
End If
Next
CountStrike = xOut
End Function
3. После ввода кода сохраните книгу (чтобы макрос сохранился), закройте редактор VBA и вернитесь на лист. В любой пустой ячейке, где должен отобразиться результат, введите следующую формулу (например, для проверки ячеек с зачеркиванием в диапазоне)A2:B14):=CountStrike(A2:B14)
Совет: вы можете заменить диапазон A2:B14 на любой другой, который нужно проверить.
4. Нажмите клавишу Enter, чтобы выполнить формулу, и Excel немедленно покажет количество всех ячеек с зачёркиванием в ограниченном диапазоне. Это помогает эффективно отслеживать прогресс или удалять устаревшие записи по мере необходимости. См. снимок экрана:
Внимание: чтобы сохранить этот код VBA для дальнейшего использования, обязательно сохраните файл в формате .xlsm (книга Excel с поддержкой макросов).
Подсчёт ячеек без зачеркивания в Excel
Иногда требуется подсчитать только те ячейки, которые не имеют зачеркивания в заданном диапазоне. Например, в таблицах отслеживания проектов или продаж часто нужно учитывать только активные или действительные записи (то есть без зачеркивания) для последующих операций. Стандартные функции Excel — СЧЁТ и СЧЁТЗ — игнорируют текстовое форматирование, поэтому для точного контроля над такими задачами необходимо специализированное решение на VBA.
1. Нажмите ALT + F11, чтобы открыть окно Microsoft Visual Basic for Applications.
2. Перейдите в меню Вставка > Модуль, чтобы добавить новый модуль кода. Вставьте приведённый ниже код в окно этого модуля.
Код VBA: Подсчёт ячеек без зачеркивания
Public Function CountNoStrike(pWorkRng As Range) As Long
'Update 20140819
Application.Volatile
Dim pRng As Range
Dim xOut As Long
xOut = 0
For Each pRng In pWorkRng
If Not pRng.Font.Strikethrough Then
xOut = xOut + 1
End If
Next
CountNoStrike = xOut
End Function
3. Сохраните книгу с поддержкой макросов, вернитесь на лист и введите формулу в любую пустую ячейку (например, для диапазона)A2:B14):=countnostrike(A2:B14)Затем нажмите клавишу Enter, чтобы получить результат. Excel подсчитает только те ячейки в выбранном диапазоне, которые не имеют зачеркивания.
Примечание: в приведённом выше примере A2:B14 — это диапазон проверяемых ячеек. При необходимости измените ссылку на диапазон в соответствии с вашим листом. Также учтите, что объединённые ячейки или особое форматирование иногда могут повлиять на результаты — обязательно убедитесь, что ваш диапазон включает только нужные ячейки.
Суммирование с исключением ячеек с зачеркиванием в Excel
При работе с числовыми данными, где некоторые записи больше неактуальны или должны быть проигнорированы (то есть отображаются с зачёркиванием), часто требуется суммировать только действительные значения. Это особенно актуально для бюджетных таблиц, управления запасами или отслеживания задач — в таких случаях зачёркнутые записи обозначают устаревшие или завершённые элементы, которые необходимо исключить из итоговых сумм. Поскольку стандартная функция СУММ в Excel не учитывает форматирование ячеек, для выполнения такой операции потребуется пользовательская функция на VBA.
1. Используйте комбинацию ALT + F11, чтобы открыть редактор Microsoft Visual Basic for Applications.
2. Выберите Вставка > Модуль, чтобы добавить модуль, и вставьте приведённый ниже код в окно этого модуля.
Код VBA: Суммирование с исключением ячеек с зачеркиванием
Public Function ExcStrike(pWorkRng As Range) As Long
'Update 20140819
Application.Volatile
Dim pRng As Range
Dim xOut As Long
xOut = 0
For Each pRng In pWorkRng
If Not pRng.Font.Strikethrough Then
xOut = xOut + pRng.Value
End If
Next
ExcStrike = xOut
End Function
3. Закройте редактор VBA. На целевом листе выберите пустую ячейку и введите следующую формулу (например, для диапазона)B2:B14): =excstrike(B2:B14) Нажмите клавишу Enter, и Excel просуммирует только те ячейки в вашем ограниченном диапазоне, которые не имеют зачеркивания, мгновенно выдавая сумму без учёта устаревших значений.
Примечание: замените B2:B14 на ваш фактический диапазон данных. Эта функция игнорирует ячейки с зачёркиванием независимо от их значения. Если результаты кажутся неожиданными, убедитесь, что все ячейки содержат числовые данные и имеют корректное форматирование.
Практический совет: если после изменения статуса зачеркивания в какой-либо ячейке нужно обновить сумму, просто пересчитайте лист — нажмите клавишу F9.
Подсчёт или суммирование ячеек с зачеркиванием с помощью Kutools для Excel
Если вы хотите подсчитать или просуммировать только ячейки с зачеркиванием, Excel не предоставляет встроенных средств для этого — но Kutools для Excel упрощает эту задачу благодаря своей функции Специальный фильтр.
1. Выделите столбец
Щёлкните столбец, в ячейках которого есть зачёркивание.
2. Откройте специальный фильтр
Перейдите на вкладку KUTOOLS PLUS, затем нажмите Специальный фильтр > Фильтр ячеек со стрижкой.
3. Подтвердите применение фильтрации
Появится диалоговое окно с указанием количества ячеек, соответствующих условию. Нажмите кнопку ОК, чтобы продолжить.
4. Просмотр результатов
Ячейки с зачёркнутым текстом теперь отфильтрованы и отображаются. Выделите диапазон фильтрации — и вы сразу увидите сумму, среднее значение и количество в строке состояния в нижней части окна Excel.
✨Почему стоит использовать Kutools?
Kutools для Excel предлагает более 300 мощных и простых в использовании функций, подобных этой, помогая вам экономить время и повышать продуктивность без необходимости писать сложные формулы или код VBA.
Будь то фильтрация по формату, очистка данных, объединение листов или пакетное редактирование — Kutools выполняет всё это за считанные секунды, значительно упрощая вашу работу.
Попробуйте Kutools для Excel бесплатно в течение 30 дней и узнайте, что вы упускали!
См. также:
Как суммировать или подсчитать числа, выделенные жирным шрифтом, в диапазоне ячеек 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек