Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - Sumowanie komórek po kolorze

FLUFF · Świeżak Posty: 6

Witam

Czy ktoś wie, jaką formułę wpisać abym mógł zsumować komórki w jednolitym kolorze ? Codziennie zmieniają mi się dane w komórce, i nie zawsze dany kolor jest w tej samej komórce, tylko się zmieniają. W załączniku podaje przykład.

Pozdrawiam

sumowanie.jpg

Plik ściągnięto 2 raz(y) 31.32 KB

FLUFF · Świeżak Posty: 6

Znalazłem coś takiego, ale nadal nie działa ;/

https://www.extendoffice....m-by-color.html

colpix · Stały bywalec Excelforum **Pomógł:** 75 razy Posty: 261

Witam,

a u mnie nadal działa ;-)

A Tobie co nie działa?

Może to,

sama zmiana koloru tła komórki nie dokona automatycznego przeliczenia formuły. Musisz zmienić jakąś wartość znajdującą się w zakresie Twoich obliczeń, by nastąpiła aktualizacja wyniku.

Możesz np. rozważyć czy da się powiązać kwoty z formatowaniem warunkowym, wtedy kolor komórki będzie się zmieniał z wprowadzoną w niej zmianą i formuła będzie automatycznie dokonywać obliczeń.

A może to?

zamień:

Kod:

var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();

na

Kod:

var countRangeAddress = activeformula.match(/\((.*)\;/).pop().trim();

i

Kod:

var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();

na

Kod:

var colorRefAddress = activeformula.match(/\;(.*)\)/).pop().trim();

no i teraz formuła nie taka

Kod:

=sumColoredCells(A1:E11,A1)

a taka

Kod:

=sumColoredCells(A1:E11;A1)

A może co innego?
Działa?

FLUFF · Świeżak Posty: 6

Dzięki colpix, ale już sobie poradziłem, i próbowałem te same rozwiązania które ty zaproponowałeś (zamiana "," na ";") i mimo to nie zadziałało, nie wiedzieć czemu. Pobawiłem się trochę i teraz działa. Nie pamiętam co zrobiłem ale dla tych których przyda się wklejam kod:

Kod:

/**
* @param {range} sumRange Range to be evaluated
* @param {range} colorRef Cell with background color to be searched for in sumRange
* @return {number}
* @customfunction
*/

function sumColoredCells(sumRange,colorRef) {
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = activeRange.getSheet();
var formula = activeRange.getFormula().toString();
formula = formula.replace(new RegExp(';','g'),',');

var rangeA1Notation = formula.match(/\((.*)\,/).pop();
var range = activeSheet.getRange(rangeA1Notation);
var bg = range.getBackgrounds();
var values = range.getValues();

var colorCellA1Notation = formula.match(/\,(.*)\)/).pop();
var colorCell = activeSheet.getRange(colorCellA1Notation);
var color = colorCell.getBackground();

var total = 0;

for(var i=0;i<bg.length;i++)
for(var j=0;j<bg[0].length;j++)
if( bg[i][j] == color )
total=total+(values[i][j]*1);
return total;
};

Temat do zamknięcia. Dzięki. Zdrówka i Pozdrawiam

colpix · Stały bywalec Excelforum **Pomógł:** 75 razy Posty: 261

W polskiej wersji Arkuszy Google argumenty jakie przekazuje się do funkcji muszą być rozdzielone znakiem średnika ( ; ), a nie przecinka ( , ).

Kod z Twojego linka

Kod:

function sumColoredCells(sumRange,colorRef) {
var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = SpreadsheetApp.getActiveSheet();
var activeformula = activeRg.getFormula();
var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
var sumValues = activeSht.getRange(countRangeAddress).getValues();
var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
var BackGround = activeSht.getRange(colorRefAddress).getBackground();
var totalValue = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
if ((typeof sumValues[i][k]) == 'number')
totalValue = totalValue + (sumValues[i][k]);
return totalValue;
};

będzie działał w oryginalnej postaci jeżeli w swoim arkuszu google wybierzesz: Plik – Ustawienia arkusza kalkulacyjnego, na karcie Ogólne w Ustawienia regionalne wybieramy np. Stany Zjednoczone i zatwierdzamy zmiany.

Jeżeli jednak chcemy zostawić Ustawienia regionalne na Polska i korzystać z powyższego kodu musimy dokonać zmiany jakie podałem w poprzednim poście (przecinki na średniki)

Widzę, że nie próżnowałeś i znalazłeś inne rozwiązanie.

Drugi kod jest inny, nie ma go w podanym linku i w nim nie trzeba nic zmieniać, gdyż - mówiąc w skrócie - linijka kodu

Kod:

formula = formula.replace(new RegExp(';','g'),',');

załatwia sprawę.

Niezależnie od zastosowanego kodu (pierwszy czy drugi) potrzebna jeszcze jest formuła do wyliczeń.

Przykładowo:

Kod:

=sumColoredCells(A1:A8;C1)

gdzie A1:A8 to zakres komórek, który poddajemy obliczeniom, natomiast komórka C1 zawiera kolor wg którego będziemy liczyć.

Suma po kolorze komórki.png

Plik ściągnięto 1 raz(y) 22.88 KB

FLUFF · Świeżak Posty: 6

Dizęki colpix, za wyjaśnienie. Właśnie też o tym czytałem co napisałeś, i pomimo tego nie działało. Także moją uwagą zwróciła linijka kodu "formula" - koniec końców udało się. Nie jestem programistą, ani jakiś tam wymiatacz w excelu, ale jak już czegoś potrzebuje to nie ma zmiłuj się - i siedziałem tak długo aż mi zadziałało ;)