Calcul multi-critère couleurs et valeurs de cellules

[BOILEAU]

Bonjour,
Je suis dans la panade !!!! je tourne en rond, je décide donc de rechercher de l'aide. Je suis sous la dernière version d'Excel (V 16.24), sous Mac et j'ai installé le complément Pack de fonction XLP.

Dans une feuille Excel, j'ai une plage de données (D78:BM78) dans laquelle les cellules possède une information écrite BS ou MS ou HS.
J'ai une autre plage de données (D86:BM87) dans laquelle certaines cellules sont colorées en bleue.

Je souhaiterai obtenir dans une cellule de mon tableau le résultat suivant : Pour chaque cellule MS, compter le nombre de cellule bleue.
De même dans une autre cellule : Pour chaque cellule BS, compter le nombre de cellule bleue et dans une troisième cellule : Pour chaque cellule HS, compter le nombre de cellule bleue.

J'espère que quelqu'un pourra m'aider sur ce sujet.

restant à votre disposition.

Un grand merci d'avance.

Frédéric

 

[BOILEAU]

ThomasR et Job75
Je vous joint le fichier "FICHIER EXEMPLE (1)" avec le code de Job75 et le fichier "FICHIER EXEMPLE(2)" avec le code de ThomasR.
> Celui de Job75 prend en compte n'importe quelle couleur et ne prend pas en compte par exemple les deux lignes de La Discrète, de l'Intime, de La Généreuse,(80 et 81; 83 et 84; 86 et 87)
> Celui de ThomasR m'affiche des "#VALEUR!" dans mes cellules et je pense qu'il ne prend pas en compte par exemple les deux lignes de La Discrète, de l'Intime, de La Généreuse,(80 et 81; 83 et 84; 86 et 87).
J'ai indiqué en bleue en dessous de chacune des cellules de résultats les bonnes valeurs. Cela vous guidera peut-être.

Encore merci de votre aide précieuse. Moi je suis complètement dépassé par ces formules. Désolé...

Dans l'attente de vous lire.

Frédéric

 

[ThomasR]

hello,

Je trouve pas ma fonction dans ton fichier.

c'est cette formule ?

=ARRONDI.SUP(('C:\Applications\Utilities\Pack de fonctions XLP.xlam'!NB_SI_COULEUR(E32:BM33;'C:\Applications\Utilities\Pack de fonctions XLP.xlam'!NO_COULEUR($C$8)))/2;0)

 

[ThomasR]

bon peux-tu expliquer comment tu comptes car deja à la main je n'y comprend rien.
si deux case = 1 nuit ? ou une nuit c'est aumoins une case dans un jour ? l'un ou l'autre ne me donne pas tes valeurs en bleu

 

[ThomasR]

au mieux sans la règle j'ai ça mais je trouve la solution horrible

je pense qu'il est possible que ce soit faux donc donne la règle stp

Option Explicit
Function CompteTextCouleur(PlageText As Range, ref As String, PlageCouleur As Range, myColor As Range) As Integer
Application.Volatile
Dim i%
Dim d As Range
For i = 1 To PlageText.Count Step 2
 
    If CStr(PlageText(i).Value) = ref Then
        For Each d In PlageCouleur
            If d.Column = PlageText(i).Column Or d.Column = PlageText(i).Column + 1 Then
                If d.Interior.ColorIndex = myColor.Interior.ColorIndex Then
                    CompteTextCouleur = CompteTextCouleur + 1
                    'Exit For
                End If
            End If
        Next
    End If
Next
If CompteTextCouleur Mod 2 <> 0 Then CompteTextCouleur = CompteTextCouleur + 1
CompteTextCouleur = CompteTextCouleur / 2

End Function

 

[BOILEAU]

Bonjour ThomasR,

Merci pour tes efforts et ta patience.

Je vais essayer de te donner la règle !

Le principe générale est de comptabiliser le nombre de nuits (cellules bleus) pour chacune des chambres et selon les périodes des saisons (basse, moyenne et haute)
Pour chaque chambre il-y-a deux lignes, exemple l'Intime (lignes 83 et 84), cela pour mettre en quinconce les réservations lorsqu'il y a changement de locataire qui se suivent.
Une nuit est matérialisée par deux cellules (après-midi du jour d'arrivée et matin du jour de départ). Celle qui compte dans le calcul est celle qui matérialise l'arrivée (l'après-midi), par exemple (AY 80) la nuit sera comptée en BS (Basse saison) et non en MS (Moyenne saison).
Les cellules matérialisées par une autre couleur que le bleu ou par des rayures sont des indisponibilités de location.

Je te renvois ton fichier parce que j'ai replacé correctement pour la Discrète la nuit du 24 au 25 (j'avais fais une erreur !)

J'espère avoir été le plus claire possible.

Merci beaucoup.

Frédéric

 

[BOILEAU]

Je renvoie ton fichier, je n'avais pas changé les valeurs en bleue dans le tableau des résultats !!!!

 

[David Aubert]

Bonjour,
J'ai supprimé tous les fichiers de Boileau et ceux réalisés à partir de ce fichier.
Il y avait des données personnelles de réservation...
Merci Boileau d'être très attentif à ce point de la charte.
Bonne journée à tous

 

[BOILEAU]

Bonjour David XLD,
Désolé pour cette très grosse étourderie. J'ai supprimé dans le fichier toutes données personnelles.
Je joint donc à l'intention de ThomasR le fichier de travail sans aucune info particulière.
Merci.

 

[ThomasR]

Merci pour ces explications c'est tout de suite plus simple. cela m'a pris 30 secondes lol.

Si tu souhaites apprendre le VBA et que de ce fait tu souhaites que je commente chaque ligne de code dis le moi.
je peux aussi te dire pourquoi j'ai fait certain choix plutôt que d'autre (comme l'utilisation d'un range pour la variable d)

Cordialement,
Thomas

Option Explicit
Function CompteTextCouleur(PlageText As Range, ref As String, PlageCouleur As Range, myColor As Range) As Integer
Application.Volatile
Dim i%
Dim d As Range
For i = 1 To PlageText.Count Step 2
    If CStr(PlageText(i).Value) = ref Then
        For Each d In PlageCouleur
            If d.Column = PlageText(i).Column + 1 Then
                If d.Interior.ColorIndex = myColor.Interior.ColorIndex Then
                    CompteTextCouleur = CompteTextCouleur + 1
                    'Exit For
                End If
            End If
        Next
    End If
Next
set d = nothing
CompteTextCouleur = CompteTextCouleur

End Function

 

[ThomasR]

pour info tu peux utiliser la même formule pour compter les nuits d'indisponibilité de tes chambres cela fonctionne.

 

[BOILEAU]

Bonsoir ThomasR,
MERCI MERCI MERCI
C'est super, ça marche trop bien.
Pour ta proposition d'explications, je suis très intéressé. Mais je ne veux pas t'embêter et te faire perdre du temps.
Très bonne soirée.
Cordialement,
Frédéric

 

[ThomasR]

Avec plaisir et heureux que cela te convient

 

[ThomasR]

voila le code documenté

Option Explicit
Function CompteTextCouleur(PlageText As Range, ref As String, PlageCouleur As Range, myColor As Range) As Integer
Application.Volatile 'ceci pour que la formule se recalcule après chaque update de ta feuille
Dim i as integer
Dim d As Range 'un object cellule
For i = 1 To PlageText.Count Step 2 'boucle sur ta ligne d'entête avec un step 2 car tes cellules sont fusionnées par deux
    'de ce fait i aura comme valeur 1 puis 3, puis 5...
    If CStr(PlageText(i).Value) = ref Then 'si la valeur de la cellule est égale à la valeur de ton paramètre alors vrai et donc il rentre dans la condition
        'cstr() permet de convertir la valeur en texte car dans ton besoin tu utilises une valeur texte. cint() pour un integer (un nombre entier de petite taille)
        For Each d In PlageCouleur 'boucle sur toutes les cellules de ta plage avec les couleurs
            If d.Column = PlageText(i).Column + 1 Then 'si la cellule actuelle (de la boucle) est dans la colonne qui suit (+1) la colonne du texte trouvé alors vrai (donc je rentre dans la condition)
                If d.Interior.ColorIndex = myColor.Interior.ColorIndex Then 'test si la cellule est de la même couleur (de base attention, ne fonctionnera pas avec un thème) que la cellule couleur passé en paramètre alors vrai
                    CompteTextCouleur = CompteTextCouleur + 1 'j'incrémente le résultat de 1 (en gros je prends le résultat précédent que je fais plus 1)
                End If 'fin du test sur la couleur
            End If'fin du test sur la colonne
        Next 'arrivé ici je recommence le for each jusqu'à être passé sur toutes les cellules de ma plage colorie
    End If 'fin du test sur le texte recherché
Next 'je retourne au for i tant que i est inférieur au count (total du nombre de colonne)
set d = nothing ' je supprime mon object cellule pour libérer de la mémoire
CompteTextCouleur = CompteTextCouleur 'je retourne le resultat à celui qui à fait appel à la fonction (dans ton cas la cellule de ta feuille)

End Function

Là ce qu'il manque pour que ce soit utilisable par d'autre c'est une gestion des erreurs qui permet à l'utilisateur de bien utiliser ta fonction
ex : si les paramètres sont pas bon (ex: les colonnes entre les textes et couleur qui ne coinciderait pas.
l'utilisation de thème à la place de couleur de base...

Si besoin fais moi signe

Bonne soirée
Thomas

 

[ThomasR]

je pense que la dernière ligne de code est inutile je pensais utiliser une variable mais comme je suis reparti du code de job75 j'ai pas fait attention qu'il travaillait directement avec la fonction elle même.
donc si tu l'as supprime cela ne changera rien.

je parle de

CompteTextCouleur = CompteTextCouleur

 

[BOILEAU]

Un GRAND MERCI !!!!!

Frédéric