Le plus j’utilise Google Sheets, plus je réalise à quel point il est puissant.
En même temps, il y a certaines choses que j’aimerais voir intégrées au programme, comme un moyen de compter les cellules colorées dans Google Sheets.
Par là, j’entends un moyen de compter le nombre de cellules en fonction de la couleur d’arrière-plan.
Par exemple, supposons que j’ai un ensemble de données comme indiqué ci-dessous et que je veuille savoir rapidement le nombre total de cellules ayant une couleur d’arrière-plan verte et jaune.
Mais comme cette fonctionnalité ne fait pas déjà partie de Google Sheets, vous pouvez le faire en utilisant un script Google Apps personnalisé pour créer votre propre fonction dans Google Sheets.
Dans ce tutoriel, je vais vous montrer comment vous pouvez facilement créer une fonction qui vous permettra de compter les cellules colorées dans Google Sheets en fonction de la couleur d’arrière-plan. Vous pouvez copier notre SPREADSHEET EXAMPE et suivre le tutoriel.
Alors commençons!
Se familiariser avec les compétences complexes de Google Sheets
Ce tutoriel couvre la création de votre propre fonction à l’aide de Google Apps Script, qui est une compétence assez avancée. Si cela dépasse actuellement vos capacités à suivre ce guide, nous vous recommandons de suivre un cours sur Google Sheets sur Udemy pour vous familiariser avec vos compétences globales en matière de feuilles de calcul.
Compter les cellules en fonction de la couleur d’arrière-plan
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez rapidement connaître le nombre total de cellules ayant une couleur d’arrière-plan verte et jaune.
La première étape consisterait à utiliser le script Google Apps pour créer une fonction personnalisée pouvant le faire dans Google Sheets.
J’ai essayé de trouver un tel script en ligne et j’en ai finalement trouvé un sur ce blog.
Voici le script Google Apps pour la fonction personnalisée qui vous permet de compter les cellules en fonction de la couleur d’arrière-plan:
function countColoredCells(countRange,colorRef) {
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = activeRange.getSheet();
var formula = activeRange.getFormula();
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 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;
};Voici les étapes à suivre pour mettre ce script dans votre document Google Sheets:
- Dans votre document Google Sheets, cliquez sur Outils.
- Dans les options qui s’affichent, cliquez sur Éditeur de scripts. Cela ouvrira l’éditeur de scripts d’Apps Script.
- Par défaut, vous serez dans la fenêtre de code Code.gs. Supprimez tout ce qui s’y trouve déjà, puis copiez et collez le code ci-dessus.
- Cliquez sur l’icône de sauvegarde dans la barre d’outils.
- Fermez la fenêtre Apps Script.
En effectuant les étapes ci-dessus, j’ai ajouté le code du script à Google Sheets afin de pouvoir maintenant utiliser mes nouvelles formules personnalisées dans la feuille de calcul (comme des formules normales).
Remarque : Si vous avez déjà un autre code dans la fenêtre Code.gs et que vous ne voulez pas le perturber, il est préférable d’insérer un nouveau fichier de script et de copier-coller le code là-bas. Pour ce faire, cliquez sur l’icône Plus à côté de Fichiers, puis sélectionnez Script. Cela insérera un nouveau fichier de script (donnez-lui un nom) et copie-collez le code ci-dessus dans ce nouveau fichier de script.
Maintenant que nous avons le code en place, voyons comment utiliser la formule personnalisée pour compter les cellules d’une couleur d’arrière-plan spécifique.
Dans notre exemple, je dois compter tous les noms qui sont de couleur verte.
Pour ce faire, saisissez la formule suivante dans la cellule où vous souhaitez obtenir le nombre de cellules colorées :
=countcoloredcells(A2:A11,C1)
Dans la formule ci-dessus, j’ai utilisé la cellule C1 comme celle à partir de laquelle la formule doit récupérer la couleur d’arrière-plan. Vous pouvez également utiliser n’importe quelle cellule dans la plage de données pour obtenir la couleur, puis compter en fonction d’elle.
Tout comme la formule pour compter les cellules de couleur verte, la formule ci-dessous compte toutes les cellules de couleur jaune :
=countcoloredcells(A2:A11,D1)
Comment cette formule fonctionne-t-elle ?
Permettez-moi de vous expliquer rapidement ce qui se passe ici.
La formule personnalisée que nous avons créée prend 2 arguments :
- la plage de cellules contenant les cellules colorées que nous devons compter
- la cellule qui a la couleur d’arrière-plan à compter
Dans notre exemple, j’ai pris le deuxième argument comme cellule C1, car elle a également la même couleur que je veux compter. Si vous le souhaitez, vous pouvez choisir n’importe quelle cellule de la plage et l’utiliser à la place de créer une nouvelle cellule.
Par exemple, vous pouvez choisir la cellule A3 à la place de C1 comme deuxième argument, car elle a également la même couleur.
Lorsque vous utilisez cette formule, elle parcourt toutes les cellules de la plage et vérifie la couleur d’arrière-plan de toutes les cellules. Si la couleur d’arrière-plan d’une cellule correspond à celle du deuxième argument, alors elle est comptée, sinon elle ne l’est pas.
Une fois que la formule a parcouru toutes les cellules de la plage, elle renvoie simplement le nombre total de cellules qu’elle a trouvées qui ont la même couleur d’arrière-plan.
Remarque : Vous pouvez utiliser la même formule pour compter les cellules de différentes couleurs d’arrière-plan. Par exemple, si dans le même ensemble de données, j’ai du vert et du rouge, je peux utiliser cette formule dans deux cellules différentes – une qui fait référence à la cellule verte et compte le nombre total de cellules vertes, et une autre qui fait référence à la cellule rouge et compte le nombre total de cellules rouges.
Comment faire pour que la formule se mette automatiquement à jour lors de la modification ?
Bien que la formule fonctionne parfaitement et compte le nombre de cellules d’une couleur d’arrière-plan spécifique, il y a un petit irritant.
Elle ne se rafraîchit pas automatiquement lorsque vous apportez une modification. Par exemple, si je supprime la couleur d’une des cellules ou si j’ajoute manuellement de la couleur à l’une des cellules, la formule ne se mettra pas automatiquement à jour pour me donner le bon résultat.
Même si je vais dans la cellule qui contient la formule, que je passe en mode édition et que j’appuie sur Entrée, elle ne se recalcule pas.
La raison en est qu’elle ne considère pas cela comme un changement nécessitant un recalcul. J’ai même essayé de rafraîchir le classeur et cela n’a pas fonctionné non plus (peut-être à cause d’un problème de cache).
Malheureusement, il n’y a pas de moyen automatique de le faire, mais il y a une méthode manuelle vraiment simple pour le faire.
Voici la solution : allez dans l’une des cellules de la plage, passez en mode édition (appuyez sur F2 ou double-cliquez sur la cellule), ajoutez un espace à la fin du contenu de la cellule et appuyez sur Entrée. Dès que vous ferez cela, vous verrez que la formule se recalcule et vous donne le bon résultat.
Maintenant, vous pouvez retourner dans la cellule où vous avez apporté cette modification en ajoutant un espace et le supprimer.
Bien que cela soit gênant, pour le moment, c’est la seule façon que j’ai trouvée pour m’assurer que nous obtenons le bon compte du nombre de cellules colorées en utilisant cette formule personnalisée.
Si vous travaillez avec un grand ensemble de données, cette formule peut prendre quelques secondes, voire quelques minutes, pour calculer le nombre total de cellules ayant une couleur d’arrière-plan spécifique. Vous pouvez voir un texte « Chargement » dans la cellule pendant ce temps (comme indiqué ci-dessous).
Comment compter les cellules colorées avec la fonction SOUSTOTAL
Il n’existe actuellement aucune formule COUNTIF couleur dans Google Sheets que vous pouvez utiliser pour compter les cellules par couleur. À la place, vous pouvez utiliser la fonction SOUSTOTAL de Google Sheets pour compter les cellules avec couleur. Ce processus est similaire à celui que vous utiliseriez pour compter les cellules surlignées dans Excel.
Vous devrez d’abord utiliser la fonction de filtre sur vos données pour les trier par couleur. Nous vous aiderons à le faire avec un guide étape par étape. Voici comment compter les cellules colorées dans Google Sheets en utilisant la fonction SOUSTOTAL :
- Sélectionnez la plage de données
- Allez dans Données > Créer un filtre.
- Si vous allez à l’option du filtre sur la ligne d’en-tête et sélectionnez « Filtrer par couleur » > « Couleur de remplissage », vous devriez voir vos options de couleur.
- Choisissez la couleur que vous voulez compter. Dans notre cas, nous choisirons le jaune.
- Maintenant, nous pouvons utiliser notre formule SOUSTOTAL =SOUSTOTAL(103, A2:A11). Les résultats seront de 4.
Le chiffre 103 est utilisé pour spécifier que le type de sous-total que nous utilisons est la version counta. Nous utilisons la fonction SOUSTOTAL au lieu de COUNTIF car elle ne compte que les cellules visibles. COUNTIF, quant à elle, est utilisée pour compter les cellules contenant des textes spécifiques.
Comment compter les cellules colorées à l’aide de l’extension
Vous pouvez également compter les cellules par couleur à l’aide de l’extension Function by Color. Cette extension fait en fait partie de l’extension Power Tools, qui a plus de fonctionnalités. Vous pouvez choisir de télécharger l’extension Power Tools ou simplement l’extension Function by Color seule.
Cette méthode est beaucoup moins intimidante que l’utilisation des scripts Google Apps.
Voici comment compter les cellules par couleur dans Google Sheets à l’aide de l’extension Function by Color :
- Pour télécharger l’extension, allez dans Extensions > Add-ons.
- Cliquez sur Obtenir des add-ons.
- Dans la fenêtre des add-ons, tapez « function by color » et cliquez sur Rechercher.
- Choisissez l’extension « Function by color » et cliquez sur Installer.
- Vous devrez peut-être autoriser les autorisations. Cliquez sur Continuer et choisissez votre compte Google.
Pour utiliser l’extension pour compter par couleur dans Google Sheets :
- Sélectionnez la plage de données.
- Allez dans Extensions.
- Choisissez Function by color et cliquez sur Démarrer.
- Dans la fenêtre de l’extension, choisissez la couleur d’arrière-plan que vous voulez compter et ajoutez la formule. Dans ce cas, nous utiliserons la formule COUNTA.
- Cliquez sur la liste déroulante pour les formules et choisissez COUNTA (texte).
- Cliquez dans la boîte de collage des résultats et choisissez la cellule D2.
- Vous pouvez choisir de décocher l’option « Remplir les résultats avec vos couleurs de modèle ».
- Cliquez sur Insérer la fonction.
Vous obtiendrez les résultats sous forme de 4 dans la cellule D2 avec une formule spéciale dans la boîte de formule.
Si vous avez choisi de télécharger l’extension Power Tools à la place, vous pouvez trouver l’option Function by Color dans la barre d’outils pour Power Tools ci-dessous.
L’extension est la même, vous pouvez donc suivre les étapes ci-dessus pour compter les cellules par couleur.
Comment faire la somme des valeurs par couleur
Jusqu’à présent, nous vous avons montré comment compter les cellules surlignées dans Google Sheets. L’extension Function by Color est également pratique pour d’autres fonctions que la simple comptabilité. Vous pouvez l’utiliser pour trouver la somme des valeurs par couleur également.
Utilisons notre exemple ci-dessous pour trouver la somme des notes en jaune :
- Sélectionnez la colonne avec les notes.
- Allez dans Extensions.
- Choisissez Function by color et cliquez sur Démarrer.
- Dans la barre latérale de l’extension, choisissez la couleur d’arrière-plan.
- Dans le menu déroulant pour utiliser la fonction, choisissez SUM.
- Choisissez où le résultat sera collé. Dans notre cas, nous avons choisi la cellule B12.
- Cliquez sur Insérer la fonction.
Vous obtiendrez les résultats dans la cellule B12, qui est la somme de toutes les valeurs en jaune. Une chose importante à noter est que l’extension Function by Color ne se met pas à jour automatiquement. Si vous mettez à jour votre feuille de calcul, vous pouvez toujours aller dans Extensions > Function by Color et cliquer sur Actualiser les résultats.
En résumé, dans cet article, nous vous avons montré 3 façons de compter les cellules colorées dans Google Sheets. Vous pouvez utiliser une formule personnalisée dans Google Sheets ou l’extension Function by Color. Vous pouvez également utiliser la fonction SOUSTOTAL comme formule COUNTIF par couleur en appliquant d’abord un filtre.
J’espère que vous avez trouvé ce tutoriel utile. Vous pouvez également consulter comment compter les cellules si elles ne sont pas vides dans Google Sheets.
D’autres tutoriels Google Sheets pourraient également vous intéresser :
- Compter les cellules SI NON Vides (cellules non vides) dans Google Sheets
- Comment compter les cellules avec un texte spécifique dans Google Sheets
- Comment trier par couleur dans Google Sheets
- Comment colorer les lignes alternées dans Google Sheets
- Comment filtrer par couleur dans Google Sheets (en utilisant une formule)
- Comment mettre en évidence la valeur la plus élevée ou la plus basse dans Google Sheets
- Comment compter les cases à cocher dans Google Sheets (formule facile)
- Est-ce que Excel peut compter les cellules avec du texte ? [Guide facile]
- Comment compter les valeurs uniques dans Excel [Guide]
