Si couleur cellule et si contient expression

eZula Messages postés 3509 Statut Contributeur -  
eZula Messages postés 3509 Statut Contributeur -
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

8 réponses

  1.
    Vaucluse Messages postés 27336 Date d'inscription   Statut Contributeur Dernière intervention   6 453
     
    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
    0
  2. eZula Messages postés 3509 Statut Contributeur 392
     
    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
    0
    1. Vaucluse Messages postés 27336 Date d'inscription   Statut Contributeur Dernière intervention   6 453
       
      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
      0
  3. eZula Messages postés 3509 Statut Contributeur 392
     
    Effectivement cela ne fonctionne pas
    Pour ce qui est de la plage on peut la restreindre, très précisément à A18:AH154
    0
    1. Vaucluse Messages postés 27336 Date d'inscription   Statut Contributeur Dernière intervention   6 453
       
      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,
      0
  4. via55 Messages postés 14391 Date d'inscription   Statut Membre Dernière intervention   2 759
     
    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

    0
    1. eZula Messages postés 3509 Statut Contributeur 392
       
      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
      0

  5. Vous n’avez pas trouvé la réponse que vous recherchez ?

    Posez votre question
  6. via55 Messages postés 14391 Date d'inscription   Statut Membre Dernière intervention   2 759
     
    Bonjour

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


    Bonne journée également
    0
  7. eZula Messages postés 3509 Statut Contributeur 392
     
    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
    0
  8. via55 Messages postés 14391 Date d'inscription   Statut Membre Dernière intervention   2 759
     
    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


    0
    1. eZula Messages postés 3509 Statut Contributeur 392
       
      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
      0
  9. via55 Messages postés 14391 Date d'inscription   Statut Membre Dernière intervention   2 759
     
    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

    0
    1. eZula Messages postés 3509 Statut Contributeur 392
       
      Edité

      en cours...
      0

Discussions similaires

Excel formule : savoir si une cellule contient partie texte
plunderer007 -
francois -

14 réponses
Excel: Si contient *partie texte1* OU *partie de texte2 alors...
Fab -
Fab -

2 réponses
Si cellule contient...
Ludo_du33 -
Ludo_du33 -

3 réponses
Formule Excel : "SI" une cellule contient un mot, alors ?
Tonio5933 -
Mike-31 -

9 réponses
Changer la couleur d'une cellule selon son texte
fantomex -
Picton -

5 réponses