Как да сумирам клетки с еднакъв цвят или през ред?

Здравейте, Емилия Радкова.

Ето тук:

Как да сумирам клетки, с определен цвят, в Excel ?

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

Т.к. казвате че сумирането е през ред може да ползвате следния вариант.

=SUMPRODUCT(B1:B15;MOD(ROW(B1:B15)+0;2))

На снимка 1 позиция 1 е показана таблицата за която е създадена формулата.Идеята ѝ е сумира през ред.

Обръщам внимание диапазона (B1:B15) започва с един ред нагоре над първия ред, в който е първата числова стойност таблицата.

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

Ако срещнете проблем в разбирането и прилагенето ѝ, пише ще помогнем тук във форума.

Весели празници.

Благодаря Ви, Краси за бързия отговор! Беше ми много интересна UDF функцията, въпреки че го направих механично (без да съм наясно с VBA модул и т.н.), но разясненията Ви са безупречно ясни:) Резултата е налице! Разбира се опитах и със Sumproduct...MOD...ROW и естествено и с нея се получи. Но не мога да я "разчета" и да си обясня логически нейното действие, за което моля за малко подробности. Сърдечно благодаря!

Радвам се че се е получило!

Така ще се опитам да съм максимално кратък :) при обяснението.

Формулата е : =SUMPRODUCT(B1:B15;MOD(ROW(B1:B15)+0;2))

Нека първо започанем с Sumproduct: това е формула, която първо умножава N на брой еднакви масива, след това събира получените стойности.

Какво имам предвид?

На снимка 1 позиция 1 и 2 в колоните A и B са имам два еднакво дълги масива (Array).Искам за тези два масива да използвам формулата Sumproduct.

Формулата би изглеждала така =Sumproduct (масив 1; масив 2) ИЛИ

=SUMPRODUCT(A3:A6;B3:B6) ИЛИ = A3 . B3 + A4 . B4 + A5 . B5 + A6 . B6

Както описах и по - горе - > sumproduct е функция, която първо умножава отделните клетки на масива и след това сумира получените стойности от това умножението.

Така в нашата формула =SUMPRODUCT(B1:B15;MOD(ROW(B1:B15)+0;2)), ясно се вижда КОЙ е първия масив = B1:B15.

В първия масив, реално не е нищо особено, той е чисто на просто колонката от числа които искам да сумирам. Обаче имам и друго условие, да сумирам през ред.Тук вече идва хитринката да опиша друг масив от данни, с който да направя така че да сумирам през ред. В случая този масив от данни е представен по следния начин - MOD(ROW(B1:B15)+0;2)

Нека да разгледаме какъв масив създаваме посредством функциите MOD(ROW(B1:B15)+0;2) .

Ще разгледаме две функции изграждащи масива:

=Row(B1:B15) - това е функция която връща (показва) поредния номер на реда от дадения диапазон, т.е. за B1 - показва 1, за B2 показва 2 (снимка 2 позиция 1 и 2). Т.е. от aбревиатура B1, 1 e реда и формулата Row взима този номер и го записва в клетката, в която е приложена формулата.

=MOD(число;делител). Формулата има следната идея, имам едно число и делител, формулата записва остатъка от делението на числото на делителя (снимка 3).

Т.е. ако имам 15/2 (число/делител) следва да имам остатък 1 ако имам 14/2 следва да имам остатък 0. ИЛИ за всяко четно число разделено на 2 аз ще имам остатък 0, а за за всяко нечетно ще имам остатък 1.

Какво е записано в нашата функция MOD, която представлява втория ни диапазон (Array).

=MOD(ROW(B1:B15)+0;2)

Числото в MOD формулата е ROW(B1:B15)+0, което след обяснението по - горе би следвало да означава следното:

• намирам се на ред 1 -> ров следва да ми даде резултат *1, която единица се събира с 0 т.е. 1+ 0 = 1

• намирам се на ред 2 -> ров следва да ми даде резултат *2, която единица се събира с 0 т.е. 2+ 0 = 2

• намирам се на ред 3 -> ров следва да ми даде резултат *3, която единица се събира с 0 т.е. 3+ 0 = 3

  • ............... и т.н. N на брой четни и нечетни числа.

Така получаваме стойности на параметъра число 1,2,3,4,5,6,7,8,9,10...N

Делителя в MOD е константен той е 2.

Така изхождайки от горните разсъждения, следва че MOD(ROW(B1:B15)+0;2) за всяко четно число получено в ROW(B1:B15)+0, с константен делител 2 ще ни дава остатък 0, А за всяко нечетно ще дава остатък 1.

Т.е. MOD(ROW(B1:B15)+0;2), минавайки през масива B1:B15 ползвайки ROW, следва MOD да запише следния масив

за 1 ->1; за 2 -> 0; за 3 -> 1;

Втория масив се получава следния 1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0....... Все едно съм направим нова колона подобна на тази от снимка 4 позиция 1.

И сега уточнихме че посредством MOD(ROW(B1:B15)+0;2) ще опишем втория масив...

Какво имаме за Sumproduct?

Все едно имаме =Sumproduct(B1:B15;C1:C15), само дето C1:C15 е представен не посредством колона ами посредством - MOD(ROW(B1:B15)+0;2), с която създаваме подобен масив :).

И сега от определението за Sumproduct и просто математическо правило че умножавайкли по 0 получаваме 0, следва :

= B3 . C3 + B4 . C4 + B5 . C5 + B6 . C6 + ....... BN . CN, така на всеки четен ред имаме нули, т.е. при умножението следва да елиминираме стойностите т.к. ги умножаваме по 0 и дават 0.Остават само нечетните редове, които пък от своя страна ги умножаваме с единици :), т.е. получава същите стойности.

Накрая както вече от по - горе знаем след като извърши тези сложни умножения с 0 и 1, функцията Sumproduct сумира тези прозиведения. В нашия случай ще сумира прозиведенията на нечетните редове с единици, което естествено дава сумата на нечетните редове.

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

Само да вмъкна ако искахме да съберем четните редове, ТО само трябва да променим логическата последователност на масива или от 1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0 да стане на 0;1;0;1;0;1;0;1;0;1;0;1;0;1;0.

Как би станало това?

=MOD(ROW(B1:B15)+0;2), трябва само да променим +0 да стане на +1, така следва да обърнем започнем втория масив с 0, на ниво нечетна стойност и съответно започвайки с нула за нечетните следва те да бъдат игнорирани при сумирането:)

Надявам се съм бил полезен с отговора.

Весело посрещане на новата 2017! Здраве и Късмет!

Здравей, "Как се сумират клетки с еднакъв цвят?" - Мога да ти предложа едно универсално решение, което няма да се влияе от последователността на оцветените клетки. Слагаш филтър на колоната, където са клетките. От опциите на филтъра се избира Filter by color - избираш си цвета. В приложения филтър си правиш сумата по познатия начин.

Здравейте и през новата 2017г., Бъдете здрави и все така позитивни и успешни! Благодаря отново за изчерпателния и подробен отговор. До скоро се опитвах от други източници да си изяснявам и прилагам формулите, които ме интересуват, но след като "намерих" Аула смятам да забравя за другите. Единствено с Вашето разяснение вече смело мога да кажа, че това е най-ясното, последователно и разбираемо илюстриране на логиката на Ексел и неговите формули. Благодаря:)

@Емилия Радкова.

Радвам се, че всичко се е изяснило! Малко "разтегнато" обяснение, но се радвам че е помогнало в разбирането на идеята.

Здрава, мирна и спокойна 2017 година, ти пожелава!

Честита Нова година! Здраве, късмет и успехи на всички! И ако тази тема е все още актуална, едно предложение като отговор на г-жа Емилия Радкова. Имаме таблицата с данни и оцветени клетки (черна стрелка 1). Маркираме я и избираме Filter от Home, Editing, Sort & Filter (червенатастрелка)..След няколко стъпки(стрелки2, 3, 4, 5 и 6) имаме филтър и прилагаме формулата (стрелка7). За да не се появява забележката (стрелка 6), предлагам просто да добавим една клетка НАД таблицата(стрелка 8) и в нея да напишем каквото и да е - то няма да участвува в сумирането. По този начин обаче, не можем да съберем всички разноцветно оцветени клетки. По - добрият вариант е, да използваме СОРТИРАНЕ по цвят (Sort by Color,Sort by Cell Color) (стрелки2, 3 и 4) на тази снимка след избор на Filter от Home, Editing, Sort & Filter (червенатастрелка). Сортираме последователно клетките до получаване на групирането им по цветове(стрелка 5). С натиснат бутон Ctrl избираме посочените редове и с десен бутон избираме Insert (стрелка 6). Получаваме оцветени в съответният цвят празни клетки(стрелки 7а, 7в, 7с и 7d - тя не се вижда). Заставаме последователно в тях и натискаме комбинацията Alt и + (плюс), съответно оцветения диапазон се маркира, натискаме Enter и получаваме сумата в оцветените в еднакъв цвят клетки!(стрелки 8a, която е по - нагоре, 8в и 8с и показаната сега 7d). Извинения за малко пространното обяснение, но дано е станало ясно! Още веднъж честита Нова година, здраве, късмет и успехи на всички!

Дължа едно пояснение - за да се сумират правилно оцветените клетки, трябва да се натисне тази комбинация Alt и = (или +), както е посочено, десният плюс от цифровата клавиатура не дава резултат! Извинения за подвеждането!

Благодаря и за Вашия детайлен отговор на въпроса ми за сумиране по цвят. Варианта обаче е неприложим в моя казус (съжалявам, че не изпратих пример и снимки), но въпреки всичко е едно прекрасно допълнение към темата:)