✅ Как да броите ОЦВЕТЕНИ клетки в Excel (Ръководство стъпка по стъпка + ВИДЕО)

Съдържание

Как да броите цветни клетки в Excel

Гледайте видео - Как да броите цветни клетки в Excel

Не би ли било чудесно, ако имаше функция, която може да брои цветни клетки в Excel?

За съжаление няма вградена функция за това.

НО…

Може лесно да се направи.

Как да броите цветни клетки в Excel

В този урок ще ви покажа три начина за броене на цветни клетки в Excel (със и без VBA):

Използване на филтър и функция SUBTOTAL
Използване на функцията GET.CELL
Използване на персонализирана функция, създадена с помощта на VBA

#1 Пребройте цветните клетки, използвайки филтър и SUBTOTAL

За да броите цветни клетки в Excel, трябва да използвате следните две стъпки:

Филтрирайте цветни клетки
Използвайте функцията SUBTOTAL, за да преброите цветните клетки, които са видими (след филтриране).

Да предположим, че имате набор от данни, както е показано по -долу:

В този набор от данни се използват два цвята на фона (зелен и оранжев).

Ето стъпките за броене на цветни клетки в Excel:

Във всяка клетка под набора от данни използвайте следната формула: = SUBTOTAL (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 CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Задайте rCell = CountRange За всеки rCell In Count rCell.Interior.ColorIndex = CountColorValue Тогава TotalCount = TotalCount + 1 End If Next rCell GetColorCount = TotalCount Крайна функция

За да създадете тази персонализирана функция:

При активна работна книга натиснете Alt + F11 (или щракнете с десния бутон върху раздела на работния лист и изберете Преглед на кода). Това ще отвори VB Editor.
В левия прозорец, под работната книга, в която работите, щракнете с десния бутон върху някой от работните листове и изберете Вмъкване -> Модул. Това ще вмъкне нов модул. Копирайте и поставете кода в прозореца за код на модула.
Щракнете двукратно върху името на модула (по подразбиране името на модула в Module1) и поставете кода в кодовия прозорец.
Затворете редактора на VB.
Това е! Вече имате персонализирана функция в работния лист, наречена GetColorCount.

За да използвате тази функция, просто я използвайте като всяка обикновена Excel функция.

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

CountRange: диапазона, в който искате да преброите клетките с посочения цвят на фона.
CountColor: цвета, за който искате да преброите клетките.

За да използвате тази формула, използвайте същия цвят на фона (който искате да преброите) в клетка и използвайте формулата. Аргументът CountColor би бил същата клетка, в която въвеждате формулата (както е показано по -долу):