Cum se numără sau se sumează celulele pe baza culorii celulei din foaia Google?
Numărarea sau însumarea valorilor celulei pe baza culorii de fundal a celulei specifice pentru a obține rezultatul, după cum se arată în următoarea captură de ecran. În acest articol, voi vorbi despre cum să rezolvați această sarcină în foaia Google și Microsoft Excel.
Numărați valorile celulei pe baza culorii celulei cu script în foaia Google
Următorul script vă poate ajuta să numărați valorile celulei în funcție de culoarea specifică a celulei, procedați astfel:
1. Clic unelte > Editor de scripturi, vezi captura de ecran:
2. În fereastra de proiect deschisă, faceți clic pe Fișier > Nou > Fișier script pentru a deschide o fereastră de cod, vezi captura de ecran:
3. Și în caseta de prompt, vă rugăm să introduceți un nume pentru acest cod de script, consultați captura de ecran:
4. Clic OK și apoi copiați și lipiți următorul cod pentru a înlocui codul original în modulul de cod, consultați captura de ecran:
function countColoredCells(countRange,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 colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
var BackGround = activeSht.getRange(colorRefAddress).getBackground();
var countCells = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
countCells = countCells + 1;
return countCells;
};
5. Apoi salvați acest cod de script și reveniți la foaie, introduceți această formulă: = countcoloredcells (A1: E11, A1) într-o celulă goală, apoi apăsați Introduce cheie pentru a obține rezultatul calculat. Vedeți captura de ecran:
notițe: În această formulă: A1: E11este intervalul de date pe care doriți să îl utilizați, A1este celula umplută cu culoare specifică pe care doriți să o numărați.
6. Repetați formula de mai sus pentru a număra alte celule colorate specifice.
Suma valorilor celulei pe baza culorii celulei cu script în foaia Google
Pentru a însuma valorile celulei cu o anumită culoare a celulei, vă rugăm să aplicați mai jos codul scriptului.
1. Clic unelte > Editor de scripturi pentru a accesa fereastra proiectului și faceți clic pe Fișier > Nou > Fișier script pentru a insera un alt modul de cod nou, apoi, în caseta de prompt, introduceți un nume pentru acest script, vedeți captura de ecran:
2. Clic OK iar în modulul de cod deschis, copiați și lipiți mai jos codul scriptului pentru a înlocui codul original, vedeți captura de ecran:
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;
};
3. Și apoi salvați acest cod, reveniți la foaie și introduceți această formulă: = sumcoloredcells (A1: E11, A1) într-o celulă goală și apăsați Introduce cheie pentru a obține rezultatul calculat, vezi captura de ecran:
notițe: În această formulă: A1: E11 este intervalul de date pe care doriți să îl utilizați, A1 este celula cu o culoare de fundal specifică pe care doriți să o însumați.
4. Și apoi puteți repeta formula de mai sus pentru a însuma alte celule colorate specifice.
Numărați sau sumați valorile celulei pe culoarea celulei cu Kutools pentru Excel în Microsoft Excel
În Microsoft Excel, pentru a număra sau a însuma valorile celulei în funcție de culoarea specifică a celulei, Kutools pentru Excel'S Numără după culoare utilitarul vă poate ajuta să finalizați această sarcină cât de repede puteți.
Kutools pentru Excel: cu mai mult de 300 de programe de completare Excel la îndemână, gratuit pentru a încerca fără limitări în 30 de zile.
După instalare Kutools pentru Excel, vă rugăm să faceți acest lucru:
1. Selectați celulele pe care doriți să le numărați sau să le sumați în funcție de culoarea celulei, apoi faceți clic pe Kutools Plus > Numără după culoare, vezi captura de ecran:
2. În Numără după culoare caseta de dialog, alegeți Formatare standard de la Metoda culorii lista verticală, apoi selectați Context de la Tipul de numărare derulează, vezi captura de ecran:
3. Apoi apasa Generează raport buton, iar o foaie de lucru nouă cu rezultatele calculate este generată simultan, consultați captura de ecran:
notițe: Cu această caracteristică puternică, puteți calcula și valorile celulei pe baza formatării condiționate sau a culorii fontului.
Cele mai bune instrumente de productivitate Office
Kutools pentru Excel vă rezolvă majoritatea problemelor și vă crește productivitatea cu 80%
reutilizarea: Introduceți rapid formule complexe, diagrame și orice ai folosit anterior; Criptați celulele cu parola; Creați o listă de corespondență și trimiteți e-mailuri ...
Super Formula Bar (editați cu ușurință mai multe linii de text și formulă); Layout de citire (citiți și editați cu ușurință un număr mare de celule); Lipiți la interval filtrat...
Merge celule / rânduri / coloane fără a pierde date; Conținut de celule divizate; Combinați rânduri / coloane duplicate... Prevenirea celulelor duplicate; Comparați gamele...
Selectați Duplicat sau Unic Rânduri; Selectați Rânduri goale (toate celulele sunt goale); Super Find și Fuzzy Find în multe cărți de lucru; Selectare aleatorie ...
Copie exactă Mai multe celule fără modificarea referinței formulelor; Creați automat referințe la foi multiple; Introduceți gloanțe, Casete de selectare și multe altele ...
Extrageți textul, Adăugați text, eliminați după poziție, Eliminați spațiul; Creați și imprimați subtotaluri de paginare; Convertiți conținutul dintre celule și comentarii...
Super Filtru (salvați și aplicați scheme de filtrare altor foi); Sortare avansată după lună / săptămână / zi, frecvență și multe altele; Filtru special cu bold, italic ...
Combinați cărți de lucru și foi de lucru; Merge Tables pe baza coloanelor cheie; Împărțiți datele în mai multe foi; Conversia în loturi xls, xlsx și PDF...
Peste 300 de funcții puternice. Suportă Office / Excel 2007-2019 și 365. Suportă toate limbile. Implementare ușoară în întreprindere sau organizație. Funcții complete de încercare gratuită de 30 de zile. Garanție de restituire a banilor de 60 de zile.
function countColoredCells(countRange;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 colorRefAddress = activeformula.match(/\;(.*)\)/).pop().trim(); var BackGround = activeSht.getRange(colorRefAddress).getBackground(); var countCells = 0; for (var i = 0; i < backGrounds.length; i++) for (var k = 0; k < backGrounds[i].length; k++) if ( backGrounds[i][k] == BackGround ) countCells = countCells + 1; return countCells; };
Hello everyone. I need a little help here.. I used the formula and worked fine, but when I change the color of some cells the number result of the formula don´t update automatically.. I always need to copy and paste the formula again to update. Has someone resolved this!?
The formula does not work when the color changes, only when the cell values change. Just change the value inside the cell, not the color.
For convenience, I did this: I included a column with a checkbox in the required range, and when the value in it changes, my formula is updated. It's like an on / off switch.
The formula does not work when the color changes, only when the cell values change. Just change the value inside the cell, not the color.
For convenience, I did this: I included a column with a checkbox in the required range, and when the value in it changes, my formula is updated. It's like an on / off switch.
Works for code: function countColoredCells(countRange, colorRef, unUsed) { var backGrounds = SpreadsheetApp.getActiveSheet().getRange(countRange).getBackgrounds(); var backGround = SpreadsheetApp.getActiveSheet().getRange(colorRef).getBackground(); var countCells = 0; for (var i = 0; i < backGrounds.length; ++i) for (var k = 0; k < backGrounds[i].length; ++k) if ( backGrounds[i][k] == backGround ) ++countCells; return countCells; }
function onEdit(e) { SpreadsheetApp.getActiveSheet().getRange('A1').setValue(Math.random()); }
but You neet do call the function like that: =countColoredCells("C6:W21";"E2";$A$1)
where "C6-W21" is the area analysed "E2" is the model cell $A$1 - don't know why this is, but it writes a random number to the cell A1
"$A$1 - don't know why this is, but it writes a random number to the cell A1"
$A$1 is a cell in which calculations are made, a buffer.
Please note that the cell will be displayed in the entire workbook, in all tabs. I have not yet learned how to bind it to only one tab. Email me if you solve this.
A tip is to create a script that inserts a row in the range and then removes the same row again. And then set up a time-based trigger for the function =)
Thank you so much for these scripts, Truly appreciated, it's not to get something that works out of the box.
Question how would I go about modifying the values sum script to pull part of a cell, for exammple a cell that contains the following:
909 & 1909 {1 of 2} SM & M (3.5)
How would I go about pulling the "3.5" in parenthesis and sum that numerical value coming out multiple cells of the same color. I know how to pull the value out in google sheets but can't figure out how to combine it with the cell color script.
Has anyone updated this for the Sum colored cells? i'm ok with copying and pasting the forumla for now but it would be nice if i highlighted more cells, it would add those cells to the total automatically. I'm a newb when it comes to reading code, i tried a few things based on all the comments but alas, i'm still new.
PLEASE SOMEONE COULD HELP ME ?
I WAS LOOKING FOR HOW TO SOLVE THAT A MONTH AT LEAST. I TRIED MANY THINGS, CHANGED COMMAS .... AND NOTHING SOLVE THAT...
HI, Brian! Thank's for your effort to give us the updated code. The auto update feature doesn't works for me. It doesn't write a new random number in A1 automatically, s i have to do it manually to get refresh. Is it as planed or Do i miss something? Thanks for your reply.
hi Brian, I have tried to use the updated version. It is improved but still not always update after change color.
is it possible to add a command to refresh the 'A1' value timely. thanks.
Hi all, I wanted to give you an updated version of how to count the number of cells that are a given color. This version solves a couple of issues:
1. The count not updating when the doc changes and
2. Errors from adding another parameter to the function
// Unsed third argument
function countColoredCells(countRange,colorRef,unUsed) { var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = SpreadsheetApp.getActiveSheet(); var activeformula = activeRg.getFormula();
// The regex matches the parentheses as an array, gets the arguments as a string, // then splits the arguments on the comma into another array
var arrayOfArguments = activeformula.match(/\((.*)\)/).pop().trim().split(','); // Get the first argument which is the range
var countRangeAddress = arrayOfArguments[0]; // Get the second argument, which is the reference color
var colorRefAddress = arrayOfArguments[1]; var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
var BackGround = activeSht.getRange(colorRefAddress).getBackground(); var countCells = 0;
for (var i = 0; i < backGrounds.length; i++) for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround ) countCells = countCells + 1;
return countCells;
};
// Writes a random number to A1
function onEdit(e)
{ SpreadsheetApp.getActiveSheet().getRange('A1').setValue(Math.random());
}
Hello, Lenore,
The Brian's code is worked well to auto update the result when you change the cell color, please apply it.
Yes, you should pasting all of the above code to replace the original one.
Hola! Muchas gracias por tu artículo, ha sido de mucha ayuda, he logrado corregir el tema del formato regional de google cambiando . por ;
Lo que no consigo hacer es que se actualice la formula cuando ingreso nuevos datos. ¿Sabes cómo hacer esto?
Gracias
Hi everyone. I use this sumColoredCells function but it will NOT auto refresh if you change cell colors. The only way it refreshes is to change a value in the range. A work-around I found was to create a checkbox somewhere to add 1 (or use insignificant fractional (.000001) if you are rounding) to a number in the range, and unchecking will add 0. This will act as a refresh "toggle" switch. Not really ideal, I know... but this is for my personal use and only a minor hassle.
Hope you are keeping well. I have been facing the same problem, Can you please share how to create checkbox to add 0000.1 or so. my email id is sudarshan1210@gmail.com. Looking forward to your help.
There were some errors on the code, like the range not being used at all and that the calculation was only made when the parameters were changed. With this version the calculation will be updated when there is an update on the sheet and the range has to be passed as a string, also some dummy cell has to be used to save a random value, you can put the color of the text just like the background to make it invisible:
=countColoredCells("B3:B145","B1",$A$1)
function countColoredCells(countRange, colorRef, unUsed) { var backGrounds = SpreadsheetApp.getActiveSheet().getRange(countRange).getBackgrounds();
var backGround = SpreadsheetApp.getActiveSheet().getRange(colorRef).getBackground(); var countCells = 0;
for (var i = 0; i < backGrounds.length; ++i) for (var k = 0; k < backGrounds[i].length; ++k)
if ( backGrounds[i][k] == backGround ) ++countCells;
return countCells;
}
function onEdit(e)
{ SpreadsheetApp.getActiveSheet().getRange('A1').setValue(Math.random());
}
A quick solution but not the best is to add a new parameter and never use it, something like this:
function sumColoredCells(sumRange,colorRef,test)
The test parameter is never used, this issue here is that the values only will be refresed if first of all you set the color and then change the value.
Hi there,
on Google sheets, seems not to be working, I went on debug and for both processes, in row 5 seems to be a type error, the message is: impossible to recall the "pop2 method of null. Does it makes sense? Thanks a lot!
The SumColoredCells Script works great. Do you know if there is a way to display (in a single cell) the sum of 2 colors? For example, if I wanted the sum of both the Yellow and Green Cells to display in a single cell, is that possible? I would think the formula would be:
=SUM(sumcoloredcells(A1:E11,A1)),(sumcoloredcells(A1:E11,A5)) ....But that results in an ERROR. Any ideas?
Hello, Geoff,
May be there is no direct formula to solve your problem, but, you can sum the colored cells separately first, and then, sum the two calculated results as you need.
Thanks for the reply. I have done that, but would love to consolidate the formula into a single cell, as I have to update the ranges every week. Anyway, thanks for checking!
Is there a way to include negative numbers in the mix and get the correct sum? Mine keeps adding all the numbers in the range, even if the number has a negative sign on it.
Hey, thanks for this script. I'm having a problem in the script for google sheets: TypeError: Cannot call method "pop" of null. (line 5, file "SumByColor"). Can you please help?, thanks
The problem maybe is your regional configuration, this script thinks you are separating parameters by ',', but it is not your case probably. In my case, I use ';', so I modified the script, changing the character in the lines
var countRangeAddress = activeformula.match(/\((.*)\;/).pop().trim();
var colorRefAddress = activeformula.match(/\;(.*)\)/).pop().trim();
Hi, Laura,
The script works well in my google sheets.
If there is problem in your google sheet, I can share my google sheet with you.
Please give your email address.
Thank you!
Hello, Dave,
The above script works well in my Google sheet.
When applying the formula, you should save the script window first.
Please try it, Thank you!
Thanks for the help with "Count cells based on color for google sheets". Limitation seems to be that when cell colors are changed the sheet does not refresh and totals are incorrect. I have to delete the code in the total cell and copy again from neighbour cell. Not ideal, but it works. Thanks again.
Hello, Todd,
Yes, as you said, you should retype the formula when the cell color changes.
May be there is no other good ways to solve this problem.
If any other has good methods, please comment here.
Thank you!
Hey, thanks for this script. I'm having a problem in the script for google sheets: TypeError: Cannot call method "pop" of null. (line 5, file "SumByColor"). Can you please help?, thanks
Hi! Great script but I run into a problem when trying to edit the values within the coloured cells. The script does not refresh the values if I change the colour of the cell. I can only force it to refresh if i delete the contents of the cell, change the colour and then enter the value again.
