Przejdź do głównej zawartości

Jak liczyć lub sumować komórki na podstawie koloru komórki w arkuszu Google?

Liczenie lub sumowanie wartości komórek na podstawie określonego koloru tła komórki, aby uzyskać wynik, jak pokazano na poniższym zrzucie ekranu. W tym artykule omówię, jak rozwiązać to zadanie w arkuszu Google i programie Microsoft Excel.

Policz wartości komórek na podstawie koloru komórki za pomocą skryptu w arkuszu Google

Sumuj wartości komórek na podstawie koloru komórki za pomocą skryptu w arkuszu Google

Policz lub podsumuj wartości komórek w kolorze komórki za pomocą Kutools for Excel w programie Microsoft Excel


Policz wartości komórek na podstawie koloru komórki za pomocą skryptu w arkuszu Google

Poniższy skrypt może pomóc w policzeniu wartości komórek na podstawie określonego koloru komórki, wykonaj następujące czynności:

1. Kliknij Tools > Edytor skryptówzobacz zrzut ekranu:

2. W otwartym oknie projektu kliknij filet > Nowości > Plik skryptu aby otworzyć okno kodu, zobacz zrzut ekranu:

3. W polu zachęty wprowadź nazwę tego kodu skryptu, patrz zrzut ekranu:

4. Kliknij OK a następnie skopiuj i wklej następujący kod, aby zastąpić oryginalny kod do modułu kodu, patrz zrzut ekranu:

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. Następnie zapisz ten kod skryptu i wróć do arkusza, wprowadź następującą formułę: = countcoloredcells (A1: E11, A1) do pustej komórki, a następnie naciśnij Wchodzę aby uzyskać obliczony wynik. Zobacz zrzut ekranu:

Note: W tym wzorze: A1: E11 to zakres danych, którego chcesz użyć, A1 to komórka wypełniona określonym kolorem, który chcesz policzyć.

6. Powtórz powyższą formułę, aby policzyć inne określone kolorowe komórki.


Sumuj wartości komórek na podstawie koloru komórki za pomocą skryptu w arkuszu Google

Aby zsumować wartości komórek z określonym kolorem komórki, zastosuj poniższy kod skryptu.

1. Kliknij Tools > Edytor skryptów aby przejść do okna projektu i kliknij filet > Nowości > Plik skryptu aby wstawić kolejny nowy moduł kodu, w polu zachęty wpisz nazwę tego skryptu, patrz zrzut ekranu:

2. Kliknij OK aw otwartym module kodu skopiuj i wklej poniższy kod skryptu, aby zastąpić oryginalny kod, patrz zrzut ekranu:

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. A następnie zapisz ten kod, wróć do arkusza i wprowadź następującą formułę: = sumcoloredcells (A1: E11, A1) do pustej komórki i naciśnij Wchodzę klucz, aby uzyskać obliczony wynik, patrz zrzut ekranu:

Note: W tym wzorze: A1: E11 to zakres danych, którego chcesz użyć, A1 to komórka z określonym kolorem tła, który chcesz zsumować.

4. Następnie możesz powtórzyć powyższą formułę, aby zsumować inne określone kolorowe komórki.


Policz lub podsumuj wartości komórek w kolorze komórki za pomocą Kutools for Excel w programie Microsoft Excel

W programie Microsoft Excel, aby policzyć lub zsumować wartości komórek na podstawie określonego koloru komórki, Kutools dla programu Excel'S Policz według koloru narzędzie może pomóc w wykonaniu tego zadania tak szybko, jak to tylko możliwe.

Kutools dla programu Excel : z ponad 300 poręcznymi dodatkami Excela, które można wypróbować bez ograniczeń w ciągu 30 dni. 

Po zainstalowaniu Kutools dla programu Excel, zrób tak:

1. Wybierz komórki do zakresu, które chcesz policzyć lub zsumować na podstawie koloru komórki, a następnie kliknij Kutools Plus > Policz według koloruzobacz zrzut ekranu:

2, w Policz według koloru okno dialogowe, wybierz Standardowe formatowanie z Metoda kolorystyczna listę rozwijaną, a następnie wybierz Tło z Typ zliczania rozwiń, zobacz zrzut ekranu:

3. Następnie kliknij Generuj raport przycisk, a nowy arkusz roboczy z obliczonymi wynikami jest generowany od razu, patrz zrzut ekranu:

Note: Dzięki tej potężnej funkcji możesz również obliczać wartości komórek na podstawie formatowania warunkowego lub koloru czcionki.

Kliknij Pobierz i bezpłatną wersję próbną Kutools dla programu Excel teraz!

Najlepsze narzędzia biurowe

🤖 Pomocnik AI Kutools: Zrewolucjonizuj analizę danych w oparciu o: Inteligentne wykonanie   |  Wygeneruj kod  |  Twórz niestandardowe formuły  |  Analizuj dane i generuj wykresy  |  Wywołaj funkcje Kutools...
Popularne funkcje: Znajdź, wyróżnij lub zidentyfikuj duplikaty   |  Usuń puste wiersze   |  Łącz kolumny lub komórki bez utraty danych   |   Okrągły bez wzoru ...
Super wyszukiwanie: Wiele kryteriów VLookup    Wiele wartości VLookup  |   Przeglądanie pionowe na wielu arkuszach   |   Wyszukiwanie rozmyte ....
Zaawansowana lista rozwijana: Szybko twórz listę rozwijaną   |  Zależna lista rozwijana   |  Lista rozwijana wielokrotnego wyboru ....
Menedżer kolumn: Dodaj określoną liczbę kolumn  |  Przesuń kolumny  |  Przełącz stan widoczności ukrytych kolumn  |  Porównaj zakresy i kolumny ...
Polecane funkcje: Fokus siatki   |  Widok projektu   |   Duży pasek formuły    Menedżer skoroszytów i arkuszy   |  Biblioteka zasobów (Automatyczny tekst)   |  Selektor dat   |  Połącz arkusze   |  Szyfruj/odszyfruj komórki    Wysyłaj e-maile według listy   |  Super filtr   |   Specjalny filtr (filtruj pogrubienie/kursywa/przekreślenie...) ...
15 najlepszych zestawów narzędzi12 Tekst Tools (Dodaj tekst, Usuń znaki, ...)   |   50 + Wykres rodzaje (Wykres Gantta, ...)   |   40+ Praktyczne Wzory (Oblicz wiek na podstawie urodzin, ...)   |   19 Wprowadzenie Tools (Wstaw kod QR, Wstaw obraz ze ścieżki, ...)   |   12 Konwersja Tools (Liczby na słowa, Przeliczanie walut, ...)   |   7 Połącz i podziel Tools (Zaawansowane wiersze łączenia, Podział komórki, ...)   |   ... i więcej

Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas.  Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...

Opis


Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę

  • Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
  • Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
  • Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
Comments (79)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Hey I just wanted to thank you for this guide. It totally worked! Awesome! 👍
This comment was minimized by the moderator on the site
Hii, i need help!
Did somebody have the right script?
if u have it, can u pls send me?
thanks
This comment was minimized by the moderator on the site
i need the auto update for the sumcoloredcells formula
do you know how can i have it?
This comment was minimized by the moderator on the site
questo funziona

// restituisce la somma dei valori in un range in base al colore di sfondo della cella
// uso: =SommaSeSfondo(range;cella colore di riferimento )
// =SommaSeSfondo(B1:B8;E1)

function SommaSeSfondo(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;
};
This comment was minimized by the moderator on the site
Hi Samal,

You have to use the formula:

=SommeCouleurs(A:A;A1) => A:A : columns that contain the colors to count and A1 : The cell that contains the example of the color.
This comment was minimized by the moderator on the site
'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?
This comment was minimized by the moderator on the site
'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?
This comment was minimized by the moderator on the site
'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?
This comment was minimized by the moderator on the site
Bonjour a tous,

J'utilise Google Sheet en version Française et voici le script le plus fonctionnel que j'ai trouvé:

function SommeCouleurs(plage,couleur) {
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = activeRange.getSheet();
var formule = activeRange.getFormula();


var laplage = formule.match(/\((.*)\;/).pop();
var range = activeSheet.getRange(laplage);
var bg = range.getBackgrounds();
var values = range.getValues();

var lacouleur = formule.match(/\;(.*)\)/).pop();
var colorCell = activeSheet.getRange(lacouleur);
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;

};


function CompteCouleurs(plage,couleur) {
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = activeRange.getSheet();
var formule = activeRange.getFormula();


var laplage = formule.match(/\((.*)\;/).pop();
var range = activeSheet.getRange(laplage);
var bg = range.getBackgrounds();
var values = range.getValues();

var lacouleur = formule.match(/\;(.*)\)/).pop();
var colorCell = activeSheet.getRange(lacouleur);
var color = colorCell.getBackground();

var count = 0;


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

Formule a utiliser:

=CompteCouleurs(A2:A;A1)

Il s'actualise automatiquement.
En espérant que ca vous aidera
This comment was minimized by the moderator on the site
Добрый день!
Проверил, автоматически не пересчитывает, если изменить цвет в диапазоне ((
This comment was minimized by the moderator on the site
非常感謝大大分享~很實用我是用Google 試算表編輯
請問程式碼能教怎麼加總指定的文字顏色的數字總和嗎? (把 儲存格顏色 改為 文字顏色) 拜託~~
This comment was minimized by the moderator on the site
Hi, I have set it in google sheets as You have described, but it showed me error "Type error: Cannot read property 'pop' of null". PLS can you help me?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations