Si couleur cellule et si contient expression

Signaler
Messages postés
3390
Date d'inscription
samedi 26 avril 2008
Statut
Contributeur
Dernière intervention
21 novembre 2019
-
eZula
Messages postés
3390
Date d'inscription
samedi 26 avril 2008
Statut
Contributeur
Dernière intervention
21 novembre 2019
-
Bonjour,

La problématique est la suivante : dans un classeur excel, il y a 4 onglets : EST, SUD, NORD et CENTRE.
Dans chacun de ces onglets, il y a des cellules contenant des abréviations de type "MO", "TH", "CH", "JP", etc
Chacune de ces cellules peut être de couleur verte , rouge (code 255) ou blanche.

Je dois compter le nombre total de cellules dans l'ensemble du classeur :
- qui contient chacune des valeurs "MO", "TH", "CH", "JP"...
- et qui est de couleur rouge (code 255)

Résultat attendu :

JP : 6
MO : 14
TH : 9
etc.

J'ai installé le pack de fonctions xlp qui se trouve ici https://www.excel-pratique.com/fr/fonctions-complementaires.php afin de bénéficier de la fonction NO_COULEUR

J'en arrive donc à mes tests infructueux jusqu'à présent, limités pour le moment au seul onglet "EST", nombre d’occurrences de la valeur "JP" dans les seules cellules rouge (code 255).

=SI(ET(NO_COULEUR('EST'!1:1048576)="255");NB.SI('EST'!1:1048576;"JP"))

Le résultat est "faux". J'en viens à votre aide car je sens que ce n'est pas la bonne approche et qu'il faut sans doute tester les cellules une par une, le test du ET ne me semble pas pertinent.

Merci pour toute aide et bonne journée

Configuration: Windows / Firefox 70.0

15 réponses

Messages postés
24738
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
11 décembre 2019
4922
Bonjour
sans garantie
je n'ai pas le pack et ne peux pas vérifier, mais vous pouvez essayez
=NB.SI.ENS(NO_COULEUR('EST'!1:1048576);"255");'EST'!1:1048576;"JP"))
crdlmnt
Messages postés
3390
Date d'inscription
samedi 26 avril 2008
Statut
Contributeur
Dernière intervention
21 novembre 2019
395
Merci, Vaucluse

Une erreur est renvoyée mais je ne vois pas de quoi il s'agit ("êtes vus en train de taper une formule ?"...)

pour le code couleur j'ai vérifié il s'agit bien du 255, après je ne suis pas fermé à d'autres solutions si cela existe
je continue de chercher
Vaucluse
Messages postés
24738
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
11 décembre 2019
4922
je n'ai pas grand chose d'autre à proposer sinon peut être ceci sans trop y croire
=SI(NO_COULEUR('EST'!1:1048576)="255";NB.SI('EST'!1:1048576;"JP"))
(avec ou sans les guillemets sur le 255
matricielle: entrer avec touche enter en maintenant les touches ctrl et shift enfoncées
mais peu de chance que cela fonctionne et vu le poids de la formule, mieux vaudrait limiter les plages en lignes et colonnes
Messages postés
3390
Date d'inscription
samedi 26 avril 2008
Statut
Contributeur
Dernière intervention
21 novembre 2019
395
Effectivement cela ne fonctionne pas
Pour ce qui est de la plage on peut la restreindre, très précisément à A18:AH154
Vaucluse
Messages postés
24738
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
11 décembre 2019
4922
je ne connais pas la fonction couleur et je n'en dispose pas, donc difficile de vous aider
1°) est ce qu'elle compte au moins les cellules de couleurs sans prendre en compte ce qu'elle contiennent
2°)si la couleur change avec une MFC, prend elle en compte la couleur modifiée où la couleur d'origine,
Mike-31
Messages postés
16994
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
12 décembre 2019
3717
Salut mon ami,
je n'ai pas le temps de traiter cette discussion en plein déménagement, mais as tu songé à la syntaxe LIRE.CELLULE
voir cette publication
https://www.commentcamarche.net/faq/48690-excel-compter-ou-additionner-les-cellules-colorisees-manuellement
Cordialement
Messages postés
11475
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
16 décembre 2019
1553
Bonsoir à tous

Je n'ai pas non plus le pack mais en regardant le lien fourni la fonction NO_COULEUR ne peut s'appliquer qu'à une cellule et pas à une plage

Il faut donc dans ton cas créer une fonction personnalisée qui dans une plage donnée va comptabiliser les cellules de la couleur indiquée et comportant l'item recherché
La voici, à mettre dans un module de l'éditeur VBA
Function nb_si_ens_coul(plage As Range, coul As Integer, item As String)
For Each cell In plage
If cell.Interior.Color = coul And cell.Value = item Then n = n + 1
Next
nb_si_ens_coul = n
End Function

La syntaxe de la fonction est :
=nb_si_ens_coul(plage à traiter;code couleur;"texte")
soit par ex =nb_si_ens_coul(EST!A18:AH154;255;"JP")

La fonction ne fonctionne qu'avec des couleurs mises manuellement , pas sur celles provenant d'une MFC

Cdlmnt
Via

eZula
Messages postés
3390
Date d'inscription
samedi 26 avril 2008
Statut
Contributeur
Dernière intervention
21 novembre 2019
395
Bonjour à tous,

Merci pour votre aide.
Je me suis penché un peu plus sur l'approche suggérée par via55.
Ces couleurs ne sont pas attribuées via une mfc, tant mieux.
A priori la fonction fait son travail, juste un détail, le résultat ne s'actualise pas s'il y a un changement.
Après une modification de couleur de cellule contenant le motif, il faut cliquer sur le résultat et re-valider la formule
Existe-t-il un moyen de faire en sorte que si une couleur change le décompte se fasse en temps réel sans action manuelle supplémentaire ? Il me semble avoir fait quelque chose dans le genre procédure évènementielle il y a quelques années mais je ne vois pas a différence avec une fonction...

Encore merci et bonne journée
Messages postés
11475
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
16 décembre 2019
1553
Bonjour

Rajoutes en début de macro l'instruction suivante :
Application.volatile


Bonne journée également
Messages postés
3390
Date d'inscription
samedi 26 avril 2008
Statut
Contributeur
Dernière intervention
21 novembre 2019
395
aïe, appremment, si je modifie la couleur d'une cellule rouge contenant "JP" en vert par exemple, il n'enlève pas 1
aurais-je loupé une étape ?

Function nb_si_ens_coul(plage As Range, coul As Integer, item As String)
Application.Volatile
For Each cell In plage
If cell.Interior.Color = coul And cell.Value = item Then n = n + 1
Next
nb_si_ens_coul = n
End Function
Messages postés
11475
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
16 décembre 2019
1553
Apparemment non
mais en fait après un changement de couleurs je m'aperçois que le recalcul ne se fait pas
donc en plus il faut à l'activation de la feuille dans laquelle il y a la formule la faire recalculer
Dans l'éditeur VBA tu doubles-clic sur le nom de la feuille dans l'arborescence et tu colle la macro suivante :
Private Sub Worksheet_Activate()
ActiveSheet.Calculate
End Sub



eZula
Messages postés
3390
Date d'inscription
samedi 26 avril 2008
Statut
Contributeur
Dernière intervention
21 novembre 2019
395
Hello,

Tu veux dire imbriquer ce code dans le précédent, comme ceci :

Private Sub Worksheet_Activate()
ActiveSheet.Calculate
End Sub
Function nb_si_ens_coul(plage As Range, coul As Integer, item As String)
Application.Volatile
For Each cell In plage
If cell.Interior.Color = coul And cell.Value = item Then n = n + 1
Next
nb_si_ens_coul = n
End Function


En l'état ça ne fait pas le recalcul à la volée
Messages postés
11475
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
16 décembre 2019
1553
Bonjourr

Non je n'ai pas dit d'imbriquer le code

la Function est dans un module

Le code de recalcul lui doit être dans le worksheet de la feuille dans laquelle s’applique la fonction, je te disais bien :
Dans l'éditeur VBA tu doubles-clic sur le nom de la feuille dans l'arborescence et tu colles la macro suivante :
quand tus as double-cliqué sur le nom de la feuille concernée une page blanche s'affiche à droite, c'est là que tu colle la macro

eZula
Messages postés
3390
Date d'inscription
samedi 26 avril 2008
Statut
Contributeur
Dernière intervention
21 novembre 2019
395
Edité

en cours...