Как да сумирам клетки, с определен цвят, в Excel?
Как да сумирам клетки с Excel 2010 като водещо условие е цвета на клатката
Здревей, интересен въпрос първо :)
Това лесно може да стане като се използва една 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
Вкарай я като отделен модул в VBА прозореца - запиши си я.
След това лесно може да я използваш в Excel. Реално ще имаш функция която ще е с име SumByColor. Как работи функцията.
На снимка 1 съм показал как работи функцията.
В случая съм събрал клетките които са боядисани в зелено.
Как съм го направил - изписвам UDF функцията SumByColor в клетката, след което посочвам една клетка, която ми съдържа критерия на сумиране ( цвета - в случая избирам A7 ), след което избирам диапазона в който да ми търси зелени клетки и съответно да ми сумира стойностите записани в тези зелени клетки.
=SumByColor(A7;A2:A9)
Ако не си работил с VBA и по специално с UDF функции пиши допълнително ще помогна.
Като цяло идеята е UDF функциите е че с помощта на VBA в Excel си създаваш своя собствена функция която да изпълнява нещо ( все едно функцията SUM само дето не са ти я направили от Microsoft, ами си я правиш ти така че да ти свърши работа за определен казус.
Надявам се да съм бил полезен с отговора.
Ако може малко помощ за VBA и UDF функции
Добре, няма проблем.
Няма да навлизам кой знае в какви подробности в курса по VBA има цял модул в това направление.
Както написах по - горе UDF е функция като например функцията SUM, НО не е функция която е вградена в самия, ами се създава от потребителя.
Как да приложиш функцията която съм написал по - горе :
1. Отвори си Excel - ския файл
Отвори си Excel - ския файл с клетките които са оцветени по определен начин ( Снимка 1 )
2. Отвори прозореца за VBA редактиране.
Отвори си прозореца за VBA редактиране, в който в нов модул ще запишем UDF функцията. Отварянето става като си в режим на Excel и натиснеш Alt + F11 Прозореца е подобен на този показан на снимка 2.
3. Вкарваме Нов модул в VBA прозореца.
Вкарваме нов модул в VBA прозореца, като отидем в менюто Insert ( докато сме в прозореца на VBA - снимка 3 позиция 1 ) и от там изберем Module ( снимка 3 позиция 2 )
Следва да ти се отвори нов прозорец за модул - снимка 4 , който е изцяло празен.
4. Копиране на кода на функцията.
Копираме кода на функцията и я поставяме в този прозорец.
ФУНКЦИЯТА
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
На снимка 5 е показан крайния резултат.
5. Записваме.
Така създадения модул го записваме - Натискаме бутона Save в прозореца на VBA - снимка 6.
6. Връзщаме се в средата на Excel.
Връзщаме се в средата на Excel, като пак използваме клавишната комбинация Alt + F11.
СЕГА вече имаме създадена функцията - SumByColor
Отиваме в някоя клетка и прилагаме създадена от нас функция ( снимка 7 )
Изписваме =SumByColor ( тук поставяме клетката - критерии, т.е. клетка с цвета за която правя сумиранет , в случая съм избрал една зелена клетка - А4, след което пиша ; и записвам диапазона от който да се формира сумата на стойностите на клетките с определение от мен критерии ( зелен цвят ) в случая диапазона е A1:A13.
7. Резултат.
Резултата е на снимка 8. Имаме сума от стойностите на всички зелени клетки в диапазона A1:A13.
Надявам се да съм бил полезен с отговора и нещата да се получили
Благодаря за помощта, наистина интересен урок
Благодаря, много интересна идея ще се опитам да и приложа при мен.
Здравейте, тази UDF функция може ли да се използва при OpenOfficeCal
@Андрияна Ангелова.
За съжаление намям инсталиран OpenOfficeCal, а и не съм ползвал OpenOfficeCal кой знае колко.
Може да пробвате ако операторите са същите не би трябвало да е проблем, поне според мен.
А доколко имам опит функциите в OpenOfficeCal като цяло копират тези на Excel, UDF функцията по - горе не
Ако някой друг от форума има опит с OpenOfficeCal да сподели.
Пробвайте и споделете.
Интересен ми е отговра и по - специално дали има съвместимост между двете програми.
Здравейте, така създадената функция, как мога да я запазя за да я използвам в друг файл.
Здравей, ето една тема:
Как да запиша файл с макроси и те да се запазят?
в която наркатко е описан един от вариантите за запазване на функцията.
А в урока: Как да използваме UDF в други Excel файлове? от курса VBA за Excel лектора в 15 минутен много точно, ясно и изчерпателно е обяснил в подробности как може да се съхрани и използва една UDF функция и в други файлове.
Благодаря
Защо приизползването на функция SumbyColor пресмята сумата със закръглените стойности,не използва десетичната запетая.
Работи, но само с принудително оцветени клетки. Не работи с клетки, оцветени чрез Conditional formating примерно. Защо?
Когато сме избрали таблица със данни и използваме Условно Форматиране(Conditional Formating), Excel приема максималната стойност в таблицата за 100% и изчислява останалите стойности в процент спрямо максималната!
А може ли да попитам ако искам да брои не цифри а символи как да създам допълнителен критерии. Създала съм функцията и ми брои цветните клетки но само ако има цифра, а на мен ми трябва да брои колко "н" има. Благодаря.
@ Mimozavar
Ами може, вместо Sum във кода, използвай Count. T.e. направи замяна навякъде на Sum с Count и следва да препобряваш боря на оцветените по определен начин клетки.
Благодаря :)
Може ли да се използват в Google Таблици VBA и UDF функции
https://imgur.com/a/jaesm
Къде греша?
Полученият резултат от сумирането чрез тази формула, се закръгля, има ли начин да коригирам това?
Здравей, Симона.
Предпоалгам че формулата не закръгля стойностите, ами трябва да увеличиш ... видимостта на символите в клетката.
Ето в тази тема тук:
Защо не се получава числото и не се закръгля правилно числото?
Защо функцията SumbyColor не може да използва версия 2010
Здравей, Shemsie.
Преди това си създала UDF функцията нали така?
Здравейте! И при мен закръгля сумата до цяло число. Пробвах да увелича видимостта на символите и след десетичната запетая - същата работа.... Но по ме вълнува друго. А какъв трябва да е кодът на функцията във VBA за да събира еднакви клетки, но не по оцветеност на фона на клетката, а по цвета на самия шрифт? Пробвах да заменя във Вашия код "Cell" с "Font" , но не се получава. Благодаря Ви!
Много полезна функция, но по никакъв начин не мога да получа стойностите след десетичната запетая. Не помага нито форматиране на клетката, нито функцията round.
Вашият отговор
