Google Sheets est un outil véritablement puissant. Il y a pourtant certaines fonctionnalités qui manquent dans cet outil. Je pense en particulier à un moyen de compter les cellules en fonction de leur couleur dans Google Sheets.
J'entends par là un moyen simple de compter le nombre de cellules en fonction de la couleur de remplissage des cellules.
Par exemple, supposons que vous avez la sélection de données ci-dessous et que vous souhaitez connaître rapidement le nombre total de cellules qui sont remplies avec les couleurs vertes et jaunes.
Même si cette fonctionnalité n’existe pas nativement dans Google Sheets, vous pouvez tout de même la créer grâce à un script personnalisé pour obtenir votre propre fonction dans Google Sheets.
Dans ce tutoriel, je vais vous montrer comment créer facilement une fonction qui va vous permettre de compter les cellules de couleur dans Google Sheets en fonction de la couleur de remplissage.
Compter les cellules de couleur dans Google Sheets (guide vidéo)
Si vous préférez apprendre comment compter les cellules de couleur dans Google Sheets avec une vidéo, je partage ci-dessous mon écran pour tout vous montrer :
Avant de compter les cellules de couleur : familiarisez-vous avec Google Apps Script
Ce tutoriel couvre la création de votre propre fonction à l'aide de Google Apps Script, qui est une compétence assez avancée. S’il s’avère qu’il est un peu difficile pour vous de suivre ce guide, je vous recommande de parcourir les autres articles du site TopSheets pour vous familiariser avec Google Sheets.
Je vais publier d’autres articles qui vont vous aider à vous familiariser avec Google Apps Script et qui vont vous aider à progresser. Moi aussi j’essaie de progresser tous les jours…
Compter les cellules en fonction de la couleur de remplissage
Supposons que vous disposez des données présentées dans l’image ci-dessous et que vous souhaitez connaître rapidement le nombre total de cellules qui sont remplies avec les couleurs vertes et jaunes.
La première étape consiste à avoir un script Google Apps pour créer une fonction personnalisée qui peut faire ce travail dans Google Sheets.
Je ne suis pas un spécialiste de Google Apps Script. J'ai donc recherché un tel script en ligne et j'en ai trouvé un sur internet, mais j’ai rencontré quelques difficultés à le faire fonctionner.
Vous trouverez ci-dessous le script Google Apps pour créer une fonction personnalisée qui vous permet de compter les cellules en fonction de la couleur de remplissage de la cellule.
Notez que j’ai dû légèrement adapter le script pour qu’il puisse fonctionner correctement quand Google Sheets est paramétré en langue française. Contrairement aux formules que vous pouvez trouver sur des sites internet en langue anglaise (et qui ne vont pas fonctionner pour vous), la formule ci-dessous fonctionne si vous utilisez Google Sheets en français :
function CompterCellulesdeCouleur(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 (c’est vraiment très simple) pour insérer ce script dans votre document Google Sheets :
- Dans votre document Google Sheets, cliquez sur Extension (dans la barre de menu)
- Dans les options qui s'affichent, cliquez sur Apps Script. Cela ouvrira l'éditeur de 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 que je vous ai donné ci-dessus
- Cliquez sur l'icône Enregistrer dans la barre d'outils de Apps Script
- Fermez la fenêtre Apps Script.
En suivant les étapes ci-dessus, vous avez ajouté le code de script à Google Sheets afin que vous puissiez maintenant utiliser cette fonction personnalisée nouvellement créée dans la feuille de calcul (tout comme s’il s’agissait d’une fonction normale).
Remarque : Si vous avez déjà un autre code dans la fenêtre Code.gs et que vous ne voulez pas le gâcher, il est préférable d'insérer un nouveau fichier de script et d'y coller le code. 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 copiez-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 qui sont remplies avec une couleur précise.
Dans notre exemple, je souhaite compter tous les prénoms qui sont dans des cellules en vert.
Pour ce faire, entrez la formule ci-dessous dans la cellule où vous voulez compter le nombre de cellules de couleur :
=comptercellulesdecouleur(A2:A11;C1)
Dans la formule ci-dessus, j'ai utilisé la cellule C1 comme cellule de référence pour la couleur de remplissage de la formule. Vous pouvez également utiliser n'importe quelle cellule dans la plage de données pour obtenir la couleur, puis compter en fonction de celle-ci.
Tout comme la formule pour compter les cellules de couleur verte, la formule ci-dessous compte toutes les cellules de couleur jaune :
=comptercellulesdecouleur(A2:A11;D1)
Comment fonctionne cette formule ?
Je vais vous expliquer rapidement comment fonctionne cette formule.
La formule personnalisée que nous avons créée comprend 2 arguments :
- la plage de cellules qui ont les cellules colorées que nous devons compter
- la cellule qui a la couleur de remplissage qui doit être comptée
Dans notre exemple, j'ai pris le deuxième argument comme cellule C1, car il a également la même couleur que je veux compter. Si vous le souhaitez, vous pouvez sélectionner n'importe quelle cellule de la plage et l'utiliser au lieu de créer une nouvelle cellule avec la couleur souhaitée.
Par exemple, vous pouvez choisir la cellule A3 au lieu 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 de remplissage de toutes les cellules. Si la couleur de remplissage d'une cellule correspond à celle du deuxième argument, alors elle est comptée, sinon elle n’est pas comptée.
Une fois que la formule a parcouru toutes les cellules de la plage, elle renvoie simplement le nombre total de cellules trouvées qui contiennent la même couleur de remplissage.
Remarque : Vous pouvez utiliser la même formule pour compter les cellules avec des couleurs de remplissage différentes. Par exemple, si dans la même plage 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 qui fait référence au nombre de cellules rouges et compte le nombre total de cellules rouges.
Comment mettre à jour automatiquement la formule en cas de modification
Bien que la formule fonctionne parfaitement et compte le nombre de cellules avec une couleur de remplissage spécifique, il existe un problème mineur à résoudre.
Cette formule ne s'actualise pas automatiquement quand vous apportez une modification. Par exemple, si je supprime la couleur de l'une des cellules ou si je modifie manuellement la couleur de remplissage d'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 contenant la formule, que je passe en mode édition et que j'appuie sur Entrée, le calcul ne sera toujours pas réactualisé.
La raison en est qu'il ne considère pas cela comme un changement entraînant un nouveau calcul. J'ai même essayé d'actualiser 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 existe une méthode manuelle très simple pour le faire.
Voici donc le correctif : accédez à 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 faites cela, vous verrez que la formule va recalculer et vous donner le bon résultat.
Vous pouvez maintenant revenir à la cellule où vous avez effectué cette modification en ajoutant un espace et vous pouvez simplement supprimer cet espace.
Bien que cela ne soit pas très pratique, c'est pour l'instant le seul moyen que je puisse trouver pour m'assurer que nous obtenons comptage correct de cellules de couleur à l'aide de cette formule personnalisée.
Si vous travaillez avec un grand nombre de données, cette formule peut prendre quelques secondes ou même quelques minutes pour calculer le nombre total de cellules avec une couleur de remplissage spécifique. Vous pouvez voir le texte "Loading…" apparaître (cela signifie chargement en français) dans la cellule pendant ce temps (comme indiqué ci-dessous).
Comment compter les cellules de couleur avec la fonction SOUS.TOTAL
Il n'existe actuellement aucune formule Google Sheets “NB.SI” de couleur que vous pourriez utiliser pour compter les cellules de couleur. Au lieu de cela, vous pouvez utiliser la fonction SOUS.TOTAL de Google Sheets pour compter les cellules avec des couleurs. Ce processus est similaire à la façon dont vous compteriez les cellules mises en évidence dans Excel.
Il va d'abord vous falloir utiliser la fonction “filtre” sur vos données pour les trier par couleur. Je vais vous aider et décomposer chaque étape pour que ce soit facile à réaliser pour vous. Voici comment compter les cellules de couleur dans Google Sheets à l'aide de la fonction SOUS.TOTAL :
- Sélectionnez la plage de données
- Accédez à Données > Créez un filtre
- Cliquez ensuite sur le symbole de “filtre” (qui ressemble à un triangle à l’envers) dans la cellule d’en-tête pour pouvoir accéder aux options de filtre. Dans le menu déroulant, sélectionnez “Filtrer par couleur” puis “Couleur de remplissage” et vous devriez voir les options de couleur disponibles.
- Choisissez la couleur que vous voulez compter. Dans notre cas, nous choisirons le jaune.
- Vous pouvez maintenant utiliser notre formule SOUS.TOTAL avec écrivant la formule suivante : =SOUS.TOTAL(103; A2:A11). Le résultat va être de 3.
Le nombre 103 est utilisé pour spécifier que le type de sous-total que nous voulons utiliser est la version de la fonction NBVAL. Nous utilisons le sous-total au lieu de NB.SI car il permet de ne compter que les cellules visibles. NB.SI, d'autre part, est une fonction utilisée pour compter les cellules avec des textes spécifiques.
Comment compter les cellules colorées à l’aide d’un module complémentaire
Vous pouvez également compter les cellules par couleur à l'aide du module complémentaire “Fonction par couleur” (ou la version en anglais Function by Color).
Cette méthode est beaucoup moins intimidante que l'utilisation de scripts Google App. Par contre, si ces modules complémentaires sont prévus pour Google Sheets en langue anglaise ils ne vont pas fonctionner pour vous si vous utilisez Google Sheets en français.
Comme je n’ai pas testé ces modules complémentaires, je ne vais pas détailler ici comment faire. Mais si vous voulez tenter votre chance, vous devez aller dans Extensions puis Modules complémentaires et ensuite Télécharger des modules complémentaires. Ensuite vous pouvez chercher le module complémentaire que vous souhaitez utiliser.
Apprendre d’autres astuces Google Sheets
Compter les cellules qui ne sont pas vides
Savoir compter les cellules qui ne sont pas vides dans Google Sheets est très utile. C’est une astuces complémentaire au comptage des cellules de couleur. Si vous travaillez souvent avec Google Sheets, cela vous sera utile.
Filtrer dans Google Sheets
Il est important de savoir filtrer dans Google Sheets et en particulier de savoir filtrer par couleur. Les filtres sont une fonction intégrée dans Google Sheets qui est très utile.
Comment compter les cellules colorées dans Google Sheets
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 ;
- vous pouvez aussi utiliser la fonction SOUS.TOTAL comme formule pour compter les cellules de couleur en appliquant d'abord un filtre ;
- ou vous pouvez utiliser un module complémentaire de “fonction par couleur”.
J'espère que vous avez trouvé ce tutoriel utile. N’hésitez pas à consulter les autres articles de TopSheets.fr pour vous aider à mieux utiliser Google Sheets.
