Порахувати кольорові клітини в Excel (Зміст)

  • Вступ до підрахунку кольорових клітин в Excel
  • Як порахувати кольорові клітини в Excel?

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

Усі ми добре знаємо той факт, що функції COUNT та COUNTIF - це ті, які можна використовувати для підрахунку комірок із числами та комірок, які відповідають будь-яким конкретним критеріям. Хіба не фантазії мати функцію, яка рахує кольорові клітини? Я маю на увазі щось, що підраховує різні клітини залежно від їх кольору? На жаль, такої функції безпосередньо в Excel немає. Однак ми можемо використовувати комбінацію різних функцій для досягнення того ж. Завдяки універсальності Microsoft Excel. Тому ось методи, за допомогою яких ми можемо порахувати кількість кольорових комірок в Excel.

  • Використання формули SUBTOTAL та кольорових фільтрів.
  • Використовуючи COUNTIF та GET.CELL для підрахунку кольорових комірок.

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

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

Ви можете завантажити шаблон підрахунку кольорових комірок Excel тут - Підрахунок кольорових клітин Шаблон Excel

Приклад №1 - Використання фільтрів формул SUBTOTAL і кольорів

Припустимо, у нас є дані з 1000 кольорових рядків, як показано нижче, і все, що ми хотіли, - це дізнатись кількість кожного кольору в межах цілих даних. Дивіться частковий макет даних, наведених нижче:

Крок 1: Перейдіть до комірки F1002, де ми можемо використовувати формулу SUBTOTAL для зйомки підсумкової інформації.

Крок 2: Почніть вводити формулу для SUBTOTAL всередині комірки F1002. Ця формула має кілька варіантів функціонування всередині неї. Ви можете побачити список, як тільки формула активна.

Крок 3: Зі списку потрібно вибрати номер функції 102, який пов’язаний із функцією COUNT. За допомогою миші перетягніть функцію 102 та двічі клацніть її, щоб вибрати.

Крок 4: Використовуйте комірки F1 до F10001 в якості еталонного масиву, в межах якого функція COUNT буде використовуватися для підрахунку комірок. Зафіксуйте посилання на клітинку за допомогою знака долара, як показано на скріншоті нижче.

Крок 5: Закрийте дужки, щоб завершити формулу, і натисніть клавішу Enter, щоб побачити вихід. Ви можете бачити 1000 як число у комірці F1002.

Крок 6: Тепер застосуйте фільтр до всіх даних і використовуйте параметр « Фільтр за кольором клітини» для фільтрації даних за кольорами комірок. Як тільки ви відфільтруєте певний колір, значення комірки F1002 зміниться на кількість комірок, які є з цим конкретним кольором. Дивіться скріншоти нижче.

Ви можете бачити, що існує 419 комірок, які мають той же колір, що і відфільтрований, тобто світло-помаранчевий. Ви також можете перевірити кількість інших кольорів.

Це прекрасно працює через використання функції COUNT (102) в SUBTOTAL як аргумент. Функція COUNT підраховує всі непусті клітинки в заданому діапазоні, а підсумкова сума дає загальну кількість підрахунків. Якщо ми застосовуємо фільтр з будь-якими конкретними критеріями, через характер SUBTOTAL ми отримуємо лише загальний підрахунок, пов'язаний з цими критеріями (у цьому випадку колір).

Приклад №2 - Використання COUNTIF та GET.CELL

Ми також можемо використовувати COUNTIF та функцію GET.CELL у поєднанні, щоб отримати кількість кольорових комірок. GET.CELL - стара функція макросу, яка не працює з більшістю функцій. Однак він має сумісність з названими діапазонами. Ця функція може витягти зайву інформацію, яку нормальна функція CELL не змогла витягти. Ми використовуємо цю функцію для отримання інформації про колір комірки. Виконайте кроки, щоб знати, як ми можемо порахувати клітини за кольором за допомогою цих двох функцій.

Крок 1: Створіть новий названий діапазон ColorCode, використовуючи опцію названих діапазонів на вкладці Формули.

Перейдіть на вкладку Формули > всередині розділу « Визначені імена», натисніть « Визначити ім’я», щоб мати змогу створити названий діапазон.

Крок 2: З'явиться спливаюче вікно з " Новим ім'ям ". Згадайте ім'я як ColorCode та в межах Посилається на: розділ, використовуйте формулу як = GET.CELL (38, 'Приклад №2'! $ A2) . Після поправок натисніть кнопку OK. Ваш названий діапазон буде заповнений та готовий до використання.

38 ось номер операції, пов'язаний з GET.CELL. Він пов'язаний з операцією, яка фіксує унікальні кольорові номери в Excel.

Перейменуйте стовпчик G як ColorCode, де ми будемо фіксувати числові коди, пов'язані з кожним кольором. Ці коди унікальні для кожного кольору.

Крок 3: У комірці G2 почніть вводити = ColorCode . Це названий діапазон, який ми тільки що створили на попередньому кроці. Натисніть клавішу Enter, щоб отримати унікальний числовий код, пов'язаний з кольором комірки A2 (пам'ятайте, визначаючи названий діапазон, ми використовували A2 як посилання на аркуші).

40 - код, пов'язаний з рожевим кольором в Excel.

Крок 4: Перетягніть і вставте формулу по комірці G3: G1001, і ви побачите різні числа, які є не що інше, як кольорові коди, пов'язані з кожним кольором. Дивіться частковий знімок екрана нижче.

Крок 5: Тепер нам потрібно обчислити, яка кількість кожного кольору на аркуші. Для цього ми будемо використовувати функцію COUNTIF. Почніть вводити = COUNTIF у комірці B1003 даного аркуша excel.

Зверніть увагу, що ми пофарбували комірку 1003: 1006 кольорами, які ми використали відповідно у нашому прикладі.

Крок 6: Використовуйте стовпчик G як аргумент діапазону для цієї функції, а ColorCode назвав діапазон як критерій. Доповніть формулу, закривши дужки та побачте вихід у комірці B1003.

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

Перетягніть та вставте цю формулу по решти комірок і побачите кількість кольорів, що залишилися.

У цій статті ми побачили різні методи, за допомогою яких можна з’ясувати кількість кольорових клітин. Давайте обернемо речі деякими пунктами, які слід запам'ятати.

Що потрібно пам’ятати про кількість кольорових клітин у Excel

  • У нас немає жодної вбудованої функції, яка може рахувати кольорові комірки на аркуші excel. Однак ми можемо використовувати різну комбінацію формул, щоб захопити одне і те ж.
  • Функція CELL, яку ми використовуємо у другому прикладі, дає більше інформації, що стосується комірки, яку загальна функція CELL не може надати.
  • Числовий код 102, який ми використовуємо під функцією SUBTOTAL, є числовим кодом функції COUNT під ним.

Рекомендовані статті

Це посібник з підрахунку кольорових клітин в Excel. Тут ми обговорюємо, як використовувати кількість кольорових комірок в Excel разом із практичними прикладами та шаблоном Excel, який можна завантажити. Ви також можете ознайомитися з іншими запропонованими нами статтями -

  1. Модель даних в Excel
  2. Захист аркуша в Excel
  3. Порахувати клітини з текстом в Excel
  4. Excel Show Formula

Категорія:

Поради Excel