Сумиране само на клетки, оцветени в един и същ цвят

Здравейте, Юрий,

Този въпрос е свързан с възможностите на Microsoft Excel и изисква по-специфичен подход.

За съжаление, в Excel няма вградена стандартна функция, която директно да сумира стойностите в клетки въз основа на техния цвят на запълване (Fill Color) или цвят на шрифта (Font Color), независимо дали оцветяването е приложено ръчно или чрез Conditional Formatting.

За да постигнете сумиране по цвят, трябва да използвате един от следните по-сложни методи:

1. Използване на функцията SUBTOTAL след филтриране по цвят.
2. Използване на Get & Transform (Power Query).
3. Използване на макрос (VBA - Visual Basic for Applications).

Поради сложността и факта, че стандартните формули не поддържат тази функционалност, препоръчваме метода с филтриране и SUBTOTAL като най-достъпен, въпреки че той не използва формула, базирана на цвета.

Метод с филтриране по цвят

Този метод не използва формула, която автоматично се актуализира при промяна на цвета, а изисква ръчно филтриране всеки път, когато искате да видите сумата.

1. Приложете филтър (Filter):

   • Селектирайте диапазона от клетки, който включва хедърите на колоните и данните, които искате да сумирате.
   • Отидете в раздела Data на лентата (Ribbon).
   • В групата Sort & Filter, кликнете на Filter. До заглавията на колоните ще се появят стрелки за филтриране.

2. Филтрирайте по цвят (Filter by Color):

   • Кликнете върху стрелката за филтриране (Filter arrow) в хедъра на колоната, в която се намират оцветените клетки.
   • В падащото меню изберете Filter by Color.
   • Ще видите опции за филтриране по Cell Color или Font Color. Изберете желания цвят от наличните, за да филтрирате само клетките с този цвят.

3. Сумирайте видимите клетки:

   • Кликнете в празна клетка под филтрираната колона с числа (или където искате да видите сумата).
   • Въведете формулата SUBTOTAL (Partial sum): $$=\text{SUBTOTAL}(9; \text{диапазон})$$
     • Аргументът 9 указва на функцията SUBTOTAL да изпълни операция SUM.
     • диапазон е обхватът от клетки с числа, които искате да сумирате (например $C2:C100$).
   • Натиснете Enter.

Резултат: Функцията SUBTOTAL с аргумент 9 ще сумира само стойностите във видимите клетки (тези, които отговарят на филтъра по цвят).

Важно: Ако искате сумирането да става с формула, която се актуализира автоматично при промяна на цвета, единственият начин е чрез VBA (Visual Basic for Applications), като се създаде потребителска функция (User-Defined Function - UDF) като например SUMBYCOLOR. Този метод е извън обхвата на стандартните формули в Excel.

Да, чрез филтриране на клетки по цвят знам, как мога да го направя. Интересуваше ме вариантът "с формула, която се актуализира автоматично при промяна на цвета", защото оцветените клетки може да са разположени шахматно в различни колони или пък трябва да се сумират само оцветени клетки на даден ред, където филтри не работят. Тези User-Defined Functions има ли ги в някой от вашите курсове?