Как да сумирам клетки в колони , които са форматирани по определен начин?

Здравейте, инж. Стефан Велев Стефанов.

Супер въпрос :).

Ето една UDF функция:

Function SumByColor(CellColor As Range, rRange As Range) Dim cSum As Long Dim ColIndex As Integer ColIndex = CellColor.Interior.ColorIndex For Each cl In rRange If cl.Interior.ColorIndex = ColIndex Then cSum = WorksheetFunction.Sum(cl, cSum) End If Next cl SumByColor = cSum End Function

Зареждате я в нов модул на VBA (снимка 1 позиция 1)

След това следва да си имате една UDF функция, която се стартира с SumByColor

=SumByColor(A8;A1:A14) (снимка 2 позиция 1 );

• A8 - е избраната клетка с цвета за който искате да правите сумирането ( няма значение, може да е от редицата, може да не е идеята е от нея да се вземе дадения цвят)

• A1:A14 - е диапазона които сумираме.

Резултата е на снимка 3.

Надявам се това да свърши работа.

Ако не сте ползвали UDF функции, питайте ще дообесня. Идеята е че на база на други функции се създава една изцяло нова персонална функция.

Направете следното: Маркирайте най-горната осветена клетка

След това отидете на Home, Editing, Sort & Filter, Filter. Ще се покаже падаща клетка със стрелка.Натиснете с левият бутон стрелката и в показалият се прозорец изберете Filter by color.От стрелката в дясно изберете цвета на клетките, които искате да сумирате (ако са повече от един цвят). Маркирайте клетката под оцветените и натиснете клавишната комбинация Alt и + (плюс) Ще се появи конструкцията:

Натиснете Enter и ще получите сумата. Лек ден и успехи!

До Никола Петров.

Добър вариант, малко повече операции, но пък без да се прави и ползва UDF.

Варианта с филтрите е по- популярен , а този с UDF е професионален. Втория определено е това, което ми свърши перфектно работа, защото е за цветове по мой избор и резултата не скрива останалите разноцветните редове. Още един въпрос към Краси Кръстев - защо резултатът не се изчислява автоматично, а се налага да се потвърди формулата с ОК. за да преизчисли?

Най-вероятно е от тази настройка показана на снимка 1 позиция 3.

Може би не е Automatic. Или варианта е да е от Trusted настройките.

Не е от настройките на Ексел. Преизчисляването е събитие, което се активира при настъпването на някакво събитие с данните във файла. Форматирането не е такова събитие. То просто е оцветяване, не води до промяна в числата. Затова и не се активира преизчисляването. И колкото и да се опитвате да прекалкулирате с Calculate бутона - няма да стане. Трябва да се влезе с F2 в клетката с формулата за сумиране и да се даде Enter, за да преизчисли. Дори Save на файла и неговото затваряне и отваряне не променя стойността. Това е голям недостатък на VВА решенията в този случай.

Ще предложа едно друго полуавтоматично решение, което гарантира автоматично преизчисляване, макар и да е свързано с известно неудобство.

Прави се нова колона, в която се попълва някакъв код за цвета. Примерно 1 за жълто и 2 за синьо. Ако е по-лесно просто пишете "жълто", "синьо". Да я наречем тази колона "Код на цвета"

След това в колоната с числата се задава с Conditional Formatting, когато в "Код на цвета" е 1 да оцвети клетката в жълто, а когато е 2 - в синьо.

И когато искаме да оцветим някоя клетка с жълто, няма да я цапаме нея, а ще въведем 1 в колона "Код на цвета".

Ако искаме да съберем стойностите на жълтите клетки - правим SUMIF, който сумира числата в зависимост от стойността в колона "Код на цвета"

Неудобно е, знам, но пък се преизчислява автоматично.

Всъщност, главоболията отпадат от само себе си, ако оцветяването е направено по някаква логика. Примерно - стойности по-големи от 1,000 са в жълто, а тези между 500 и 999 - в синьо. Тогава просто тази логика може да се заложи в SUMIF функцията.