Compter les cellules d’une couleur dans Excel

Pourquoi NB.SI ne peut pas compter une couleur dans Excel

NB.SI et NB.SI.ENS ne peuvent pas compter une couleur dans Excel, parce qu’elles comparent la valeur de chaque cellule à un critère, qu’il s’agisse d’un nombre, d’un texte ou d’une date. Or la couleur de fond que tu as posée sur tes cellules est une mise en forme, et non une valeur, si bien qu’aucun critère ne sait la lire. Leurs noms anglais n’y changent rien, puisque COUNTIF et COUNTIFS ont exactement la même limite.

Excel n’a pas non plus de fonction NB.SI.COULEUR, et la formule =NB.SI.COULEUR(B2:B7;B4), tapée dans Excel pour Microsoft 365, renvoie l’erreur #NOM?, la marque d’une fonction qu’il ne connaît pas. Deux autres réponses circulent sur les forums, et elles ne marchent pas davantage, parce qu’aucune des deux ne lit le fond de la cellule.

La première, =NB.SI(B2:B7;"orange"), cherche le mot « orange » écrit dans les cellules, et renvoie 0 puisqu’aucune quantité ne le contient. La seconde, =CELLULE("couleur";B4), dit seulement si le format de nombre de B4 affiche les nombres négatifs en couleur, ce qui n’a rien à voir avec son remplissage. Pour compter tes cellules orange, il te reste donc une fonction VBA comme NB_SI_COULEUR, le filtre par couleur suivi de SOUS.TOTAL, ou la recherche par format.

Faire un NB.SI par couleur avec la fonction VBA NB_SI_COULEUR

NB_SI_COULEUR est une fonction personnalisée, écrite en VBA, qui fait pour la couleur ce que NB.SI fait pour une valeur. Elle compte les cellules d’une plage dont le fond a exactement la couleur d’une cellule modèle. Une fois son code collé dans ton classeur, tu l’écris dans une cellule comme n’importe quelle formule.

Prends l’onglet Stock de l’exemple, où les trois quantités inférieures à 50, celles des articles à commander, sont surlignées en orange, et les deux stocks élevés en vert. Écrite en B8, la formule =NB_SI_COULEUR(B2:B7;B4) renvoie 3, le nombre de cellules qui portent l’orange de B4, et te dit donc combien d’articles sont à commander.

Compter les cellules de couleur sans VBA

Pour compter les cellules de couleur sans VBA, deux méthodes suffisent, le filtre par couleur suivi de SOUS.TOTAL et la recherche par format, dont la fenêtre affiche elle-même le nombre de cellules trouvées. Elles t’évitent de coller du code et d’activer les macros, mais le compte qu’elles donnent est à refaire dès que les couleurs changent. Ce sont donc elles qu’il te faut pour un nombre à lire une fois, ou dans un classeur qui doit rester sans macro.

Beaucoup de tutoriels en proposent une troisième, LIRE.CELLULE, qui repose pourtant sur les anciennes macros Excel 4.0, que Microsoft 365 désactive par défaut. Elle vient donc en dernier, puisqu’Excel peut refuser le nom défini dont elle a besoin.

Obtenir le nombre de cellules selon la couleur du texte

Quand tu as écrit certaines quantités en rouge plutôt que de colorer leur fond, les deux méthodes sans macro donnent aussi le nombre de cellules selon la couleur du texte. Dans Filtrer par couleur, choisis la couleur de police voulue, et =SOUS.TOTAL(103;A2:A7) compte les lignes restantes. La recherche par format trouve de la même façon les cellules écrites dans une couleur de police.

NB_SI_COULEUR, en revanche, ne lit que le fond, si bien qu’un compte sur la couleur du texte demande une variante de la fonction. Colle cette variante courte dans un module neuf, puis écris =NB_SI_COULEUR_POLICE(B2:B7;B6), dont le modèle est une quantité écrite en rouge.

Function NB_SI_COULEUR_POLICE(plage As Range, cellule_modele As Range) As Long
' Compte les cellules non vides de « plage » écrites dans la même couleur que « cellule_modele ».
    Dim cellule As Range
    For Each cellule In plage.Cells
        If Not IsEmpty(cellule.Value2) Then
            If cellule.Font.Color = cellule_modele.Font.Color Then
                NB_SI_COULEUR_POLICE = NB_SI_COULEUR_POLICE + 1
            End If
        End If
    Next cellule
End Function

La variante laisse de côté les cellules vides, qui n’ont pas de texte à colorer, alors que NB_SI_COULEUR les compte dès que leur fond porte la couleur. Elle confond en revanche un texte en couleur automatique et un texte noir, si bien qu’un modèle noir compte aussi les cellules restées en couleur automatique.

Compter les cellules colorées par une mise en forme conditionnelle

Pour compter les cellules colorées par une mise en forme conditionnelle, le plus sûr est de reprendre dans NB.SI la condition de la règle, puisque c’est elle qui décide de la couleur. Une couleur posée par une règle n’est en effet pas le fond de la cellule, et NB_SI_COULEUR, qui ne lit que ce fond, ne la voit pas. Dans un tableau où c’est une règle, et non un remplissage, qui colore en orange les quantités inférieures à 50, =NB_SI_COULEUR(B2:B7;B4) renvoie même 6. B4 n’a alors aucun remplissage propre, si bien que la fonction compte toutes les cellules sans remplissage de la plage.

Le filtre par couleur, lui, voit ces couleurs, et une fois le même tableau filtré sur l’orange de la règle, =SOUS.TOTAL(103;A2:A7) affiche bien 3. La recherche par format, en revanche, ne voit pas la règle et ne trouve aucune cellule. La formule ci-dessous, qui reprend la condition de la règle, donne ce même 3 sans filtre ni macro.

Avec des nuances de couleurs, la teinte change avec chaque valeur, si bien qu’un compte par couleur perd son sens. Mieux vaut alors compter les cellules au-dessus ou au-dessous d’un seuil, avec NB.SI ou NB.SI.ENS.

Pourquoi NB SI couleur ne fonctionne pas ou ne se met pas à jour

NB_SI_COULEUR ne fonctionne pas quand Excel ne trouve pas son code, ou quand la formule lui donne un code couleur au lieu d’une cellule. Si elle ne se met pas à jour, c’est qu’un changement de couleur ne relance aucun calcul. Un compte faux, enfin, tient à la cellule modèle ou à ce que la fonction compte vraiment. La liste ci-dessous part de ce qu’affiche ta cellule pour te mener à la cause.

• #NOM? s’affiche quand Excel ne trouve pas la fonction, parce que les macros sont peut-être désactivées (activer les macros sur Excel explique comment les autoriser). Le classeur a pu aussi être enregistré en « Classeur Excel », le format sans macros, qui supprime le code. Quant à Excel pour le web, il n’exécute pas les macros.

• Un #NOM? qui persiste alors que les macros sont actives vient sans doute d’un code collé dans le module d’une feuille ou dans ThisWorkbook, où Excel ne trouve pas la fonction. Recolle alors le code dans un module neuf, par le menu Insertion puis Module.

• Le compte ne bouge pas après un changement de couleur, parce que changer une couleur ne modifie aucune valeur et qu’Excel ne relance alors aucun calcul, pas même avec F9. Ctrl+Alt+F9 recalcule toutes les formules des classeurs ouverts, et le compte se met aussi à jour dès qu’une valeur de la plage change.

• Le compte vaut 0 ou oublie des cellules quand la cellule modèle n’a pas exactement la couleur des cellules à compter. Un orange choisi dans Autres couleurs n’est pas l’orange standard, et une cellule sans remplissage ne vaut pas, pour la fonction, une cellule remplie de blanc.

• Un compte trop grand peut venir des cellules vides qui portent la couleur, que NB_SI_COULEUR compte aussi, tout comme VRAI, FAUX et les erreurs. Si la cellule modèle n’a pas de remplissage, elle compte même toutes les cellules sans remplissage de la plage, soit plus d’un million sur une colonne entière comme B:B.

• #VALEUR! apparaît sans doute parce que le second argument est un code couleur, comme 49407, alors que NB_SI_COULEUR attend une cellule qui porte la couleur. Si ta formule vient d’une autre fonction du même nom, qui attendait un code, remplace ce code par une cellule modèle.

• Des cellules colorées sont ignorées, peut-être parce que leur couleur vient d’une mise en forme conditionnelle, que la fonction ne lit pas.

• Le compte inclut des lignes filtrées, parce que NB_SI_COULEUR compte aussi les lignes masquées, alors que SOUS.TOTAL(103) les écarte.