Мегаформули - част 2

Безплатни 20 урока
регистрирай се и научи

Ако сте вече потребител - Регистриран съм
Видео

Тест за преминаване към следващия урок

 
От твърденията 1) Rows връща реда на клетката, 2) Column връща колоната на клетка:
и двете са верни
и двете са грешни
1) е грешно, 2 е вярно
1) е вярно, 2 е грешно 

Въпроси и отговори

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

    Валентин Велев Стефанов:

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

    Краси Кръстев:

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

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

    Ето една 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 настройките.

    Kocho:

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

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

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

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

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

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

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

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

  • Може ли да споделя друг начин за изброяване на редове (съответно и колони)?

    Tatiana Petkova:

    Вместо да използваме ROW(A9)-8, за да получим k, можем да използваме друг начин, като "заковем" горния ред: '=VLOOKUP(LARGE($D$1:$D$13;ROWS(A$9:A9));$D$1:$E$13;2;0) ->

    с ROWS(A$9:A9)

    Краси Кръстев:

    Здравей, Татяна.

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

    Благодарим за допълнението към урока.

    Tatiana Petkova:

    Аз мисля, че този начин е по-универсален, тъй като не се налага да определяш, какво число трябва да изваждаш, когато стартираш от някакъв друг ред. 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;"-";"")). Извинявам се за спама. :)