Як в excel порахувати кількість осередків за кольором клітинки або кольором тексту

Ми з вами вже розглядали питання про те як порахувати в Excel кількість осередків / значень в докладному відео уроці. Сьогодні ми б хотіли трохи розширити цю статтю для вирішення більш вузької задачі. Припустимо, вам необхідно порахувати кількість осередків в залежності від кольору осередків або кольору тексту.

Починаючи з Excel 2007 в програмі вбудували можливість сортування осередків за кольором. Таким чином, можна відфільтрувати потрібний нам колір, виділити залишилися на увазі осередки і візуально подивитися загальна кількість осередків. Але що робити, якщо нам потрібно робити це часто і при цьому нам необхідно, щоб всі вважалося і перераховувалося за допомогою формул.

Для цих цілей необхідно використовувати дуже простенький макрос, а точніше для користувача функцію, назвемо її ColorNom, онапозволіт нам витягати числовий код кольору заливки і далі за цим кодом ми і буде вважати загальна кількість осередків, використовуючи прийоми, описувані в статті як порахувати в Excel кількість осередків / значень

Отже, приступимо. Зайдіть в редактор Visual Basic, для цього:

в Excel 2003 Натисніть на сервіс, далі Макрос і потім Редактор Visual Basic.

в Excel 2007, 2010 і 2013 це робиться по-іншому. Зайдіть в розділ Розробник, далі виберіть Visual Basic

Увага! Розділ панелі інструментів Розробник в Excel 2007 доступний за умовчанням, а в Excel 2010 і 2013 його необхідно включити. Це особливо корисно зробити тим користувачам, які будуть часто працювати з макросами. Щоб включити панель інструментів Розробник в Excel 2010 або 2013 необхідно запустити файл | параметри | Налаштування стрічки після цього необхідно з правого боку необхідно поставити галочку навпроти напису Розробник

Після того як відкриється редактор Visual Basic, вставте порожній модуль, для цього виберіть меню Insert і далі Module

і скопіюйте туди текст простий функції:

Public Function ColorNom (Cell As Range)
ColorNom = Cell.Interior.ColorIndex
End Function

Після цього закрийте редактор Visual Basic і можна повернутися до нашого файлу. У будь-який порожній осередки введіть призначену для користувача функцію, яку ми ввели раннє. У нашому випадку це функція ColorNom, її можна викликати або через меню Вставка, Функція - категорія певні користувачем, або просто можна надрукувати її в самій комірці =ColorNom (A1), де A1 - це наш осередок, в якій нам необхідно визначити індекс кольору.

Після цього вже не важко порахувати кількість осередків або значень в залежності від кольору комірки. Використовуйте нашу статтю як порахувати в Excel кількість осередків / значень

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

Public Function ColorNom (Cell As Range)
ColorNom = Cell.Font.ColorIndex
End Function

Важливо! Ви не зможете знаходити за допомогою даної функції номер кольору комірки при використанні умовного форматування. Крім того, при зміні кольору комірки Excel немає перераховує значення, необхідно це робити в ручну, натискаючи Ctrl + Alt + F9, або зміни будуть відбуватися при новому відкритті даного файлу. Це відбувається через те, що Excel не вважає зміну кольору осередки редагуванням формули. У зв`язку з цим, якщо це критично, то можна внести зміну в саму формулу, просто додавши функцію, яка постійно перераховується і при цьому не вплине на визначення кольору комірки. Наприклад, вказати функцію визначення поточної дати, помножену на нуль.
У нашому випадку функція буде виглядати наступним чином.

=ColorNom (A1) + Сьогодні () * 0

Відео: Урок №13. Підсумовування значень в забарвлених осередках Excel

Приклад підрахунку кількості значень за кольором кольором заливки осередків в Excel

Розглянемо вищевказаний приклад з переліком фруктів. Ми визначили код осередків і відобразили його навпроти кожного осередку.

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

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

Вважати кількість ми будемо за допомогою функції СЧЁТЕСЛІ

Відео: Як в excel знайти суму осередків, які відповідають кільком умовам?

Ось так виглядають аргументи цієї функції

= СЧЁТЕСЛІ (діапазон-критерій)

Пропишемо формулу:

= СЧЁТЕСЛІ ($ B $ 1: $ B $ 8-E2)

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

Завантажити приклад файлу: Цвет_Ячеек.xlsm (Файл з підтримкою макросів)