Как подсчитать или суммировать ячейки с зачеркиванием в 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 Macro-Enabled Workbook).
Подсчет ячеек без зачеркивания в Excel
Бывают случаи, когда вам может потребоваться подсчет только тех ячеек, у которых нет форматирования зачеркивания в заданном диапазоне. Например, в таблицах отслеживания проектов или данных о продажах вы часто хотите подсчитывать только активные/действительные записи (т.е., те, у которых нет зачеркивания) для дальнейших операций. Функции COUNT или COUNTA по умолчанию в 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. Подтвердите фильтр
Появится диалоговое окно, показывающее, сколько ячеек соответствует условию. Нажмите OK, чтобы продолжить.
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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек