✅ Як порахувати кольорові клітинки в Excel (Покроковий посібник + ВІДЕО)

Зміст

Як порахувати кольорові клітинки в Excel

Подивіться відео - Як порахувати кольорові клітинки в Excel

Хіба не було б чудово, якби була функція, яка підраховувала кольорові клітинки в Excel?

На жаль, вбудованої функції для цього немає.

АЛЕ…

Це легко можна зробити.

Як порахувати кольорові клітинки в Excel

У цьому підручнику я покажу вам три способи підрахунку кольорових клітинок у Excel (з VBA та без неї):

Використання фільтра та функції SUBTOTAL
Використання функції GET.CELL
Використання користувацької функції, створеної за допомогою VBA

#1 Порахуйте кольорові клітинки за допомогою фільтра та SUBTOTAL

Щоб підрахувати кольорові клітинки в Excel, потрібно виконати наступні два кроки:

Фільтруйте кольорові клітини
Використовуйте функцію SUBTOTAL для підрахунку кольорових клітин, які видно (після фільтрації).

Припустимо, у вас є набір даних, як показано нижче:

У цьому наборі даних використовуються два кольори фону (зелений та оранжевий).

Ось кроки підрахунку кольорових клітинок у Excel:

У будь -якій комірці під набором даних використовуйте таку формулу: = ПІДСУМКИ (102, E1: E20)
Виберіть заголовки.
Перейдіть до Дані -> Сортування та фільтрування -> Фільтр. Це застосує фільтр до всіх заголовків.
Натисніть на будь-яке спадне меню фільтра.
Перейдіть до «Фільтрувати за кольором» і виберіть колір. У наведеному вище наборі даних, оскільки для виділення клітинок використовуються два кольори, фільтр показує два кольори для фільтрації цих комірок.

Як тільки ви відфільтруєте комірки, ви помітите, що значення у функції SUBTOTAL змінюється і повертає лише кількість клітинок, які видно після фільтрації.

Як це працює?

Функція SUBTOTAL використовує 102 як перший аргумент, який використовується для підрахунку видимих клітинок (приховані рядки не враховуються) у зазначеному діапазоні.

Якщо дані, якщо вони не відфільтровані, вони повертають 19, але якщо вони відфільтровані, то вони повертають лише кількість видимих клітинок.

Спробуйте самі… Завантажте приклад файлу

#2 Порахуйте кольорові клітинки за допомогою функції GET.CELL

GET.CELL - це функція Macro4, яка зберігається через причини сумісності.

Він не працює, якщо використовується як звичайні функції на аркуші.

Однак він працює в Excel з іменами діапазонів.

Дивитися також: Дізнайтеся більше про функцію GET.CELL.

Ось три кроки використання GET.CELL для підрахунку кольорових клітинок у Excel:

Створіть іменований діапазон за допомогою функції GET.CELL
Використовуйте Іменований діапазон, щоб отримати колірний код у стовпці
Використання номера кольору для підрахунку кількості кольорових клітинок (за кольором)

Давайте глибоко зануримось і подивимось, що робити на кожному з трьох згаданих кроків.

Створення іменованого діапазону

Перейдіть до Формули -> Визначити назву.
У діалоговому вікні «Нова назва» введіть:
- Назва: GetColor
- Область застосування: Робочий зошит
- Посилається на: = GET.CELL (38, Sheet1! $ A2)
  У наведеній вище формулі я використав Аркуш1! $ A2 як другий аргумент. Вам потрібно використати посилання на стовпець, де у вас є комірки з кольором фону.

Отримання колірного коду для кожної клітинки

У комірці поруч із даними використовуйте формулу = GetColor

Ця формула повертає 0, якщо в клітині НЕМАЄ кольору фону, і повертає певне число, якщо є колір фону.

Це число є специфічним для кольору, тому всі клітинки з однаковим кольором фону отримують однакове число.

Підраховуйте кольорові клітинки, використовуючи колірний код

Якщо ви будете слідувати вищеописаному процесу, у вас буде стовпець з номерами, що відповідають кольору фону.

Щоб отримати кількість певного кольору:

Десь нижче набору даних додайте той самий колір фону клітинці, яку потрібно підрахувати. Переконайтеся, що ви робите це в тому ж стовпці, який використовували для створення іменованого діапазону. Наприклад, я використовував стовпець А, тому я буду використовувати клітинки лише у стовпці „А“.
У сусідній клітці використовуйте таку формулу:

= COUNTIF ($ F $ 2: $ F $ 20, GetColor)

Ця формула дасть вам підрахунок усіх клітинок із зазначеним кольором фону.

Як це працює?

Функція COUNTIF використовує названий діапазон (GetColor) як критерій. Іменований діапазон у формулі відноситься до сусідньої комірки зліва (у стовпці А) і повертає код кольору для цієї комірки. Отже, цей колірний код є критерієм.

Функція COUNTIF використовує діапазон ($ F $ 2: $ F $ 18), який містить номери кольорових кодів усіх комірок і повертає підрахунок на основі номера критерію.

Спробуйте самі… Завантажте приклад файлу

#3 Кількість кольорових за допомогою VBA (шляхом створення користувацької функції)

У двох вищезгаданих методах ви навчилися рахувати кольорові клітини без використання VBA.

Але, якщо ви добре користуєтесь VBA, це найпростіший із трьох методів.

Використовуючи VBA, ми створили б власну функцію, яка б працювала як функція COUNTIF і повертала кількість клітинок із певним кольором фону.

Ось код:

'Код, створений Sumit Bansal з https://trumpexcel.com Функція GetColorCount (CountRange As Range, CountColor As Range) Dim Count CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Встановити rCell = CountRange Для кожного rCell rCell.Interior.ColorIndex = CountColorValue Тоді TotalCount = TotalCount + 1 End If Next rCell GetColorCount = TotalCount Кінцева функція

Щоб створити цю користувацьку функцію:

Коли ваша робоча книга активна, натисніть Alt + F11 (або клацніть правою кнопкою миші на вкладці аркуша і виберіть Переглянути код). Це відкриє редактор VB.
На лівій панелі під робочою книгою, у якій ви працюєте, клацніть правою кнопкою миші будь -який аркуш і виберіть Вставити -> Модуль. Це додасть новий модуль. Скопіюйте та вставте код у вікно коду модуля.
Двічі клацніть ім'я модуля (за замовчуванням ім'я модуля в Module1) і вставте код у вікно коду.
Закрийте редактор VB.
Це воно! Тепер у вас є спеціальна функція на аркуші під назвою GetColorCount.

Щоб використовувати цю функцію, просто використовуйте її як будь -яку звичайну функцію Excel.

Синтаксис: = GetColorCount (CountRange, CountColor)

Діапазон: діапазон, у якому потрібно підрахувати клітинки із зазначеним кольором фону.
CountColor: колір, для якого потрібно підрахувати клітинки.

Щоб використати цю формулу, використовуйте той самий колір фону (який потрібно підрахувати) у комірці та використовуйте формулу. Аргументом CountColor буде та сама клітинка, де ви вводите формулу (як показано нижче):