Искам да видя сумата в края на колоната само от клетките които са оцветени в жълт фон /примерно/? Благодаря!
Здравейте, инж. Стефан Велев Стефанов.
Супер въпрос :).
Ето една 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 функцията.
Вместо да използваме ROW(A9)-8, за да получим k, можем да използваме друг начин, като "заковем" горния ред: '=VLOOKUP(LARGE($D$1:$D$13;ROWS(A$9:A9));$D$1:$E$13;2;0) ->
с ROWS(A$9:A9)
Здравей, Татяна.
Много добро допълнение за алтернативен начин за изброяване на редове и колони, сигурен съм че в определени ситуации ще бъде доста практично за използване.
Благодарим за допълнението към урока.
Аз мисля, че този начин е по-универсален, тъй като не се налага да определяш, какво число трябва да изваждаш, когато стартираш от някакъв друг ред. ROWS(A$9:A9) (или каквато и да е начална клетка) предлага универсален начин за изброяване на редове, както и колоните, разбора се, с COLUMNS
Ситуацията е следната. Имаме множество данни на които получаваме автоматично, но ,,-'' е на последно място , а не на първо. И с помоща на ,,мегаформули'' успешно поправих този минус. Въпросът ми е чисто любопитен , дали има команда която да прави нещо подобно като операция.
Ето я и формулата :
=IF(VALUE(CONCATENATE(RIGHT(A1;1);1;))<0;VALUE(CONCATENATE(RIGHT(A1;1);LEFT(A1;(LEN(A1)-1))));VALUE(CONCATENATE("+";A1;)))
По-нататък в курса видях , че има и по лесен начин =NUMBERVALUE(SUBSTITUTE(A1;"-";"")). Извинявам се за спама. :)