Въпрос към урок: Мегаформули - част 2 от курс: Анализи с Excel

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

MS Excel
Електронни таблици
MS Office
Excel VBA
Анализи с Excel

Искам да видя сумата в края на колоната само от клетките които са оцветени в жълт фон /примерно/? Благодаря!

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

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

Ето една 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)

Снимка_1

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

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

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

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

Снимка_2

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

Снимка_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 функцията.

Вашият отговор:

Научи компютърните програми онлайн от топ експертите на България
Регистрацията в АУЛА ви дава:
  • 20 безплатни урока
  • Трикове и тънкости за софтуера
  • Отговори на вашите въпроси
  • Име
    Имейл
     


    © 2016 Фондация “Аула”. Всички права запазени.
    Autodesk AutoCAD, Autodesk Inventor, Autodesk Civil 3D, Autodesk Revit, Autodesk 3ds Max, Autodesk Maya, Microsoft Word, Microsoft Excel, SharePoint Online, Microsoft PowerPoint, Microsoft Project, Microsoft, Adobe Photoshop, Adobe Illustrator, Adobe InDesign, Adobe After Effects, Adobe Premiere, Adobe Dreamweaver, V-Ray, Solidworks са регистрирани търговски марки на Adobe Systems Incorporated, Microsoft Corporation, Chaos Software Ltd, Autodesk Inc. и Dassault Systèmes SolidWorks Corp.