Come contare o sommare le celle in base al colore della cella in un foglio Google?
Nel lavoro quotidiano con i fogli di calcolo, potresti incontrare situazioni in cui è necessario contare o sommare i valori delle celle in base al colore di sfondo specifico, come illustrato nello screenshot sottostante. Ad esempio, potresti voler conteggiare o totalizzare solo quelle celle evidenziate in un determinato colore per analizzare rapidamente i dati per categoria o stato. Questa guida spiegherà come realizzare ciò non solo in Google Sheets, che non offre supporto incorporato per tali calcoli basati sul colore, ma anche in Microsoft Excel, che fornisce diversi approcci, dalle funzionalità integrate agli strumenti avanzati.
Comprendere come gestire l'analisi dei dati basata sul colore può rendere il tuo lavoro più efficiente, specialmente quando i colori vengono utilizzati per indicare stati, priorità o categorie. Discuteremo anche diverse soluzioni, confronteremo i loro scenari d'uso e forniremo consigli pratici e avvisi sugli errori per assicurarci che i tuoi compiti procedano senza intoppi.
- Conta i valori delle celle in base al colore della cella con uno script in un foglio Google
- Somma i valori delle celle in base al colore della cella con uno script in un foglio Google
- Conta o somma i valori delle celle in base al colore della cella con Kutools per Excel in Microsoft Excel
Conta i valori delle celle in base al colore della cella con uno script in un foglio Google
Google Sheets non offre un'opzione diretta per contare le celle in base al colore di sfondo. Tuttavia, puoi ottenere questo risultato con uno script Apps personalizzato. Questo script funge da funzione definita dall'utente, consentendoti di farvi riferimento come una formula. Ecco come configurare e utilizzare lo script:
1. Clicca su Strumenti > Editor di script per accedere all'ambiente di scripting. Vedi screenshot:
2. Nella finestra del progetto, seleziona File > Nuovo > File di script per aprire un nuovo modulo di codice, come mostrato:
3. Quando richiesto, inserisci un nome per il tuo nuovo codice script e conferma. Assegna un nome significativo allo script per aiutarti a identificarne lo scopo in seguito.
4. Clicca OK, quindi copia e incolla il seguente codice per sostituire qualsiasi codice di esempio nel modulo. Assicurati di incollarlo esattamente come viene fornito.
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. Salva questo file di script, torna al tuo foglio e usa la nuova funzione proprio come qualsiasi formula di Google Sheets. Inserisci: =countcoloredcells(A1:E11,A1) in una cella vuota per contare le celle nell'intervallo A1:E11 che corrispondono al colore di A1. Premi Invio per ottenere il risultato. Se richiesto per i permessi, autorizza lo script per eseguirlo nel tuo foglio.
Nota: A1:E11 è il tuo intervallo di dati; A1 è la cella di riferimento del colore da contare. Assicurati che le celle di riferimento abbiano esattamente lo stesso colore ed evita le celle unite per una maggiore affidabilità.
6. Per contare altri colori, ripeti la formula con una cella di riferimento colore diversa se necessario. Se il tuo intervallo cambia, regola l'intervallo nella formula di conseguenza.
Se ricevi un errore o un risultato inaspettato, verifica nuovamente che lo script sia stato salvato e che tu abbia usato il riferimento colore corretto. Le funzioni basate su Apps Script si ricalcolano solo se la funzione o i suoi argomenti cambiano - se in seguito ricolori le tue celle, reinserisci la formula o premi nuovamente Invio per aggiornarla.
Somma i valori delle celle in base al colore della cella con uno script in un foglio Google
Sommare i valori delle celle in base a un colore definito della cella in Google Sheets richiede un approccio simile utilizzando uno script Apps. Questo è particolarmente utile per i fogli finanziari, i registri di stato o qualsiasi scenario in cui i colori rappresentano categorie con dati numerici sottostanti.
1. In Google Sheets, apri l'Editor di script tramite Strumenti > Editor di script. Nella finestra del progetto, seleziona File > Nuovo > File di script per aggiungere un nuovo modulo di codice. Assegna un nome univoco nella richiesta per aiutarti a tracciarne lo scopo, ad esempio "SumColoredCells." Conferma per creare il modulo.
2. Clicca OK, e nella nuova finestra del modulo di codice, sostituisci qualsiasi codice predefinito incollando lo script fornito per sommare le celle colorate. Assicurati attentamente che tutto il codice sia copiato, poiché caratteri mancanti possono causare errori di sintassi.
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. Dopo aver salvato lo script, torna al tuo foglio e inserisci la formula =sumcoloredcells(A1:E11,A1) in una cella vuota, quindi premi Invio. Questa formula somma i valori in A1:E11 dove il colore di sfondo corrisponde ad A1. Quando utilizzi questa funzione, assicurati che tutte le celle target da sommare siano numeriche; i valori non numerici verranno ignorati.
Nota: A1:E11 rappresenta il tuo intervallo di dati, e A1 fornisce il riferimento colore. La formula sommerà solo i valori numerici visibili - assicurati che le celle unite o gli errori all'interno dell'intervallo non influiscano sui totali.
4. Puoi replicare il processo sopra descritto per sommare i valori per diverse categorie di colore cambiando la cella di riferimento colore nella tua formula. Se i tuoi dati vengono aggiornati o cambi il colore di sfondo, ricorda di aggiornare la formula per ottenere risultati aggiornati.
Se la somma restituisce zero o un valore di errore, verifica che l'intervallo contenga numeri e che la corrispondenza del colore sia esatta. Inoltre, il ricalcolo non è automatico se cambia solo il colore della cella - modifica la cella della formula per forzare un aggiornamento.
Conta o somma i valori delle celle in base al colore della cella con Kutools per Excel in Microsoft Excel
Quando lavori in Microsoft Excel, contare o sommare le celle per colore è un requisito frequente, specialmente nei report di gestione progetti, inventario o controllo qualità. Kutools per Excel offre un'utilità dedicata Conta per Colore, che ti consente di ottenere conteggi e totali direttamente in base al colore di sfondo o al colore del carattere - questo è particolarmente utile per intervalli di dati più grandi e quando hai bisogno di risultati rapidi e ripetibili.
Una volta installato Kutools per Excel, procedi con questi passaggi:
1. Evidenzia l'intervallo in cui desideri contare o sommare per colore, quindi clicca su Kutools Plus > Conta per Colore. Fai riferimento allo screenshot sottostante per la guida:
2. Appare la finestra di dialogo Conta per Colore. Imposta Formattazione standard sotto Metodo colore, e scegli Sfondo per Tipo di conteggio. Controlla attentamente l'anteprima e le opzioni:
3. Clicca Genera rapporto per creare un nuovo foglio di lavoro contenente una suddivisione dei conteggi e delle somme per ogni colore presente nel tuo intervallo. Questo rapporto include sia il numero che la somma delle celle colorate, consentendo un facile riferimento o ulteriori analisi.
Nota: Questa funzione può anche calcolare i valori in base alla formattazione condizionale o al colore del carattere. Usa le regole di formattazione condizionale per analisi dinamiche; altrimenti, lo strumento funziona meglio per riempimenti di colore statici. Qualsiasi cambiamento nei colori delle celle di origine richiederebbe di eseguire nuovamente l'utilità Conta per Colore per risultati aggiornati. Se riscontri problemi, conferma che Kutools è attivo e aggiornato.
Clicca Scarica e prova gratuita di Kutools per Excel Ora!
I migliori strumenti per la produttività in Office
Potenzia le tue competenze in Excel con Kutools per Excel e sperimenta un'efficienza mai vista prima. Kutools per Excel offre oltre300 funzionalità avanzate per aumentare la produttività e farti risparmiare tempo. Clicca qui per ottenere la funzione di cui hai più bisogno...
Office Tab porta le schede su Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede della stessa finestra invece che in nuove finestre.
- Aumenta la produttività del50% e riduce centinaia di clic del mouse ogni giorno!
Tutti gli add-in Kutools. Un solo programma di installazione
La suite Kutools for Office include add-in per Excel, Word, Outlook & PowerPoint più Office Tab Pro, ideale per i team che lavorano su più app di Office.
- Suite tutto-in-uno — Add-in per Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un solo programma di installazione, una sola licenza — configurazione in pochi minuti (pronto per MSI)
- Funzionano meglio insieme — produttività ottimizzata su tutte le app Office
- Prova completa30 giorni — nessuna registrazione, nessuna carta di credito
- Massimo risparmio — costa meno rispetto all’acquisto singolo degli add-in