Rechercher combien de lignes contiennent plusieurs valeurs cherchée dans une plage!

Peanuts

XLDnaute Nouveau
[Résolu]

Bonjour à tous,

Après une longue et fastidieuse recherche sur google, je n'ai pas trouvé de formule ou macro pouvant résoudre mon problème.

Si quelqu'un a déjà vu une solution sur une page web ou veut bien s'intéresser à ce problème, j'aprécierai vraiment.

Voici le problème:

Sur une feuille Excel j'ai de nombreuses données rangée en plage à selectionner facultativement du type E8:H11 ou F8:I12 (ex.):

col/ligne.-E--------F-------G--------H---------------I

8-----Chocolat---rouge----1--------Bière----------caramel
9-----Banane----6---------Poulet---Camembert----Brie
10----8----------27-------Frite-----Poulet---------Sucre
11----Rouge-----Banane---Poulet---6--------------Camembert
12----Bière------8---------Rouge---Banane--------Poulet


Quand je veux connaître le nombre de chaque tout va bien, ex:

Nombre de Poulet: =NB.SI($E$8:$I$12;"Poulet") > 4
Nombre de Camembert: =NB.SI($E$8:$I$12;"Camembert") > 2
Nombre de 6: =NB.SI($E$8:$I$12;"6") > 2

Le problème se corse lorsque je cherche combien de ligne contiennent à la fois Poulet, Camembert et 6; ou autrement dit comment pour chaque ligne de la plage sélectionnée quel est le nombre de fois ou apparaissent ces trois valeurs.

Le résultat devrait être 2 dans cet exemple.

Je peux bien sur employer une formule du type:
=SI(ET(NB.SI(E8:I8;"Poulet");NB.SI(E8:I8;"Camembert");NB.SI(E8:I8;"6"))=VRAI;1;"")
L'incrémenter dans la colonne K et en dessous ajouter la formule:
=SOMME(K9:K12)

Mais ce n'est pas ce que je cherche car ceci a tendance a alourdir le fichier surtout si la plage de recherche contient jusqu'à 1000 lignes et 20 colonnes et si il faut répéter l'opération pour de nombreuse valeurs différentes (augmentées) cherchées. Je ne veux pas non plus utiliser un tableau croisé dynamique car je veux utiliser le résultat dans d'autre formule. Je recherche une formule qui fait ce calcul sans l'incrémenter puis qu'il soit possible d'incrémenter cette formule à une liste de plusieurs valeurs cherchée. ex:

Valeurs cherchée:
Camembert / Chocolat / 6-------------Formule
Bière / Saumon / Moutarde / Beurre----Incrémenter formule
25 / 4 / 2 / 7-------------------------etc.
etc.

La fonction NB.SI ne permet pas de chercher plusieurs valeurs sur plusieurs colonnes et lignes en même temps ou bien je suis vraiment nul.

J'ai bien tenté d'autre type de formules vue sur internet mais elles ne prennent pas en compte une plage avec plusieurs arguments de recherche, ex:

{=SOMME(NB.SI(E8:I12;G9)*NB.SI(E8:I12;I11)*NB.SI(E8:I12;H11))} > 16
=NB.SI(E8:I12;G9)*NB.SI(E8:I12;I11)*NB.SI(E8:I12;H11) > 16
=NB.SI(E8:I12;G9)+NB.SI(E8:I12;I11)+NB.SI(E8:I12;H11) > 8
Ou encore des formules avec =OU, =ET, NB.SI, =BDNB, NB.SI.ENS, SOMMEPROD,SOMME.SI etc.

Rien n'est satisfaisant car cela fonctionne souvent en tant que comparaisons de deux plages et non une recherche dans une plage pour chaque ligne.

Je ne suis pas un pro des Macros. Je n'y comprends pas grand chose. Je m'essayerais bien à une fonction du type:

Ps: Svp ne rigolez pas sur ma fonction car je n'y connait rien aussi je vais écrire de très grosses bétises et des nom des variables ou actions qui n'existe pas. C'est sans doute n'importe quoi.

-----

Function NB.SI.VALIGNES(Plage;Valeur1;Valeur2;...)

Dim Plage = Selection.PlageRange.ActiveCells

Dim Valeur = Selection.ActiveCell
'Comment permettre d'augmenter le nombre de argument à choix?

Dim nb As Integer
nb=1

ActiveCell.Offset(1, 0) .FirstRow("Plage").Select
'ou Selection.FirstRow.ActivePlage

While
If (And ( NB.SI(Plage;"Valeur1")=True , NB.SI(Plage;"Valeur2")=True , NB.SI(Plage;"Valeur3")=True ) = True Then
nb = nb + 1
Else
ActiveCell.Offset(1 +1, 0) .Row("Plage").Select
End If
Loop
' Comment arrêter la boucle ou autrement dit comment faire une boucle
' pour les lignes d'une plage sélectionné par la souri.

NB.SI.VALIGNES = nb

End Function

----

Bon c'est vraiment n'importe quoi, mais quand on ne sait pas autant demander. Il y a peut-être une fonction existante qui fait déjà quelque chose très proche et voir le code serait déjà super aussi je suis prenneur ou une formule simple sur le tableur me va très bien aussi.

En fait je recherche à voir combien de fois une certaine combinaison de valeur apparait mais je ne cherche pas toutes les combinaisons existantes possibles qu'on puisse obtenir car c'est ce que j'ai trouvé sur internet.

Merci à tous ceux qui prendront part à la discussion.

Peanuts!
 
Dernière édition:

Peanuts

XLDnaute Nouveau
Re : Rechercher combien de lignes contiennent plusieurs valeurs cherchée dans une pla

Bon le tableau ne sort pas bien
Je vais essayer autre chose:

col/ligne. E F G H I

8 Chocolat rouge 1 Bière caramel
9 Banane 6 Poulet Camembert Brie
10 8 27 Frite Poulet Sucre
11 Rouge Banane Poulet 6 Camembert
12 Bière 8 Rouge Banane Poulet
 

JCGL

XLDnaute Barbatruc
Re : Rechercher combien de lignes contiennent plusieurs valeurs cherchée dans une pla

Bonjour à tous,

Un TCD pourrait, il me semble, répondre à ta demande

Merci de joindre un bout de fichier

A+ à tous
 

Peanuts

XLDnaute Nouveau
Re : Rechercher combien de lignes contiennent plusieurs valeurs cherchée dans une pla

C'est peut-être bon cette fois!

col/ligne.-E--------F-------G--------H---------------I

8-----Chocolat---rouge----1--------Bière----------caramel
9-----Banane----6---------Poulet---Camembert----Brie
10----8----------27-------Frite-----Poulet---------Sucre
11----Rouge-----Banane---Poulet---6--------------Camembert
12----Bière------8---------Rouge---Banane--------Poulet
 

Peanuts

XLDnaute Nouveau
Re : Rechercher combien de lignes contiennent plusieurs valeurs cherchée dans une pla

Merci JCDL,

Je vais regarder sur internet ce qu'est un TCD.

J'ai oublié de mentionné que je suis novice en VBA.

OS: Windows XP, Excel XP
 

Peanuts

XLDnaute Nouveau
Re : Rechercher combien de lignes contiennent plusieurs valeurs cherchée dans une pla

Merci encore JCGL,
Un tableau croisé dynamique est très bien mais je cherche une formule ou une fonction d'ou je puisse utiliser le résultat dans un tableur.
A plus.
Peanuts!
 

JCGL

XLDnaute Barbatruc
Re : Rechercher combien de lignes contiennent plusieurs valeurs cherchée dans une pla

Bonjour à tous,

C'est un Tableau Croisé Dynamique : regarde dans l'aide XL
Les données sont exploitables

A+ à tous
 

Peanuts

XLDnaute Nouveau
Re : Rechercher combien de lignes contiennent plusieurs valeurs cherchée dans une pla

Merci encore JCGL,

mais cela ne va pas. Dans un tableau croisé dynamique il faut des entêtes de ligne et de colonne mais je recherche à pouvoir séléctionner de nombreuses petites plages différentes ou se chevauchant à l'intérieur d'un tableau. Elles n'ont pas d'entêtes et si je dois créer 200 tableaux pour analyser de nombreuses valeurs différent en même temps ce n'est pas très fonctionnel.

A plus!
 
Dernière édition:

Peanuts

XLDnaute Nouveau
Re : Rechercher combien de lignes contiennent plusieurs valeurs cherchée dans une pla

Bonjour à tous,

J'ai changé la fonction mais elle ne fonctionne pas.

Quelqu'un aurait-il l'amabilité me dire pourquoi?


Function NbSiValLignes(Plage As Range, Valeur1 As Range, Valeur2 As Range, Valeur3 As Range, Valeur4 As Range, Valeur5 As Range) As String

' Définition du recalcul automatique
Application.Volatile

Dim Row As Range
Dim Cellule As Range
Dim TotalPartiel As Double
Dim NbRow As Long
TotalPartiel = 0
NbRow = 0

For Each Row In Plage.Cells
' faire l'opération pour chaque ligne
' Si ligne égal Valeurs, rajouter 1 au total partiel
If (CountIf(Plage, Valeur1) & CountIf(Plage, Valeur2) & CountIf(Plage, Valeur3) & CountIf(Plage, Valeur4) & CountIf(Plage, Valeur5)) = True Then
TotalPartiel = TotalPartiel + 1
NbRow = NbRow + 1
End If
Next Row

If NbRow <> 0 Then
NbSiValLignes = TotalPartiel
Else
NbSiValLignes = 0
End If

End Function


Je reçois l'erreur "#Valeur"!


Merci à tous!

Peanuts.
 
G

Guest

Guest
Re : Rechercher combien de lignes contiennent plusieurs valeurs cherchée dans une pla

Bonjour,

Je pense que cela vient de là

Code:
If (CountIf(Plage, Valeur1) & CountIf(Plage, Valeur2) & CountIf(Plage, Valeur3) & CountIf(Plage, Valeur4) & CountIf(Plage, Valeur5)) = True Then
Et que cela pourrait quelquechose comme ceci:

Code:
If (CountIf(Plage, Valeur1)>0 and CountIf(Plage, Valeur2)>0 And CountIf(Plage, Valeur3)>0 And CountIf(Plage, Valeur4)>0 And CountIf(Plage, Valeur5)>0) = True Then
Ou encore:

Code:
If (CountIf(Plage, Valeur1)+ CountIf(Plage, Valeur2)+CountIf(Plage, Valeur3)+CountIf(Plage, Valeur4)+CountIf(Plage, Valeur5)) >0  Then

Le '&' sert à concatener des chaines et non additionner des valeurs booleennes.

De plus je vois que tu emploies une variable nommée 'Row'

Row est un mot clé de Vba---> éviter de l'employer comme nom de variable -> source d'erreurs ultérieures.

A+
 
Dernière édition par un modérateur:

Peanuts

XLDnaute Nouveau
Re : Rechercher combien de lignes contiennent plusieurs valeurs cherchée dans une pla

Merci Hasco,

Quand j'ai écrit la fonction je voulais utiliser la formule suivante:

If ( AND (CountIf(Plage, Valeur1),CountIf(Plage, Valeur2),CountIf(Plage, Valeur3),CountIf(Plage, Valeur4),CountIf(Plage, Valeur5)) =True Then

Mais je ne suis pas parvenu à valider la ligne. Je recevais chaque fois l'erreur d'expression sur le AND. C'est pour ça que j'ai utilisé le "&" mais je vois que de la manière que tu as écrit la ligne cela fonctionne.

Quand j'actionne la formule la première ligne est en jaune:

Function NbSiValLignes(Plage As Range, Valeur1 As Range, Valeur2 As Range, Valeur3 As Range, Valeur4 As Range, Valeur5 As Range) As Double

L'erreur vient probablement de là mais j'ai essayé sans "As Range" après les Valeurs mais cela ne change rien!

Erreur de compilation:
Sub ou Function non définie!

Alors je ne sais pas!

A+
 
Dernière édition:
G

Guest

Guest
Re : Rechercher combien de lignes contiennent plusieurs valeurs cherchée dans une pla

re,

si tu donnais un fichier exemple, dégraissé avec ta fonction perso en précisant exactement quel résultat tu veux obtenir, on tournerait moins en rond.

fichier à joindre sur ce forum. (J'ouvre pas sur ci-joint et compagnie)

A+
 

Peanuts

XLDnaute Nouveau
Re : Rechercher combien de lignes contiennent plusieurs valeurs cherchée dans une pla

Ok,

Excuse moi.

Voici le fichier

l'idée est d'éviter d'avoir des formules à rallonge à la fin des lignes d'une table et qui est ensuite incrémenté du type:

=SI(ET(NB.SI(E8:I8;"valeur1");NB.SI(E8:I8;"Valeur2");NB.SI(E8:I8;"Valeur3"))=VRAI;1;"")
L'incrémenter dans la colonne K et en dessous ajouter la formule:
=SOMME(K9:K12)

Ceci allourdi considérablement un fichier surtout si c'est utilisé très souvent.

La fonction intégrée NB.SI ne permet pas de chercher plusieurs conditions dans une plage mais uniquement sur une ligne alors qu'on peut le faire pour une condition unique. Cela ne fonctionne pas car excel ne prend pas les conditions comme une combinaison.

J'aimerai donc parvenir à utiliser la fonction NB.SI avec des conditions multiples sur une plage et qui recherche une combinaison ligne par ligne.

Je ne sais pas si c'est assez clair.

Sinon dit moi si tu n'a pas compris.

Peut-être l'erreur "#valeur" vient du fait que les données sont de type mixte AlphaNumérique et devrait être toutes numériques ou toutes Textuelles ou modifiées dans l'un des formats.
Dans la fonction "Combin" toutes les données doivent être numérique.

A plus!
 

Fichiers joints

Dernière édition:
G

Guest

Guest
Re : Rechercher combien de lignes contiennent plusieurs valeurs cherchée dans une pla

Re,

Voici une fonction qui semble fonctionner mais sur 1000 lignes, ça risque de ramer.

A tester:

Code:
Function NbSiValLignes(Plage As Range, Valeur1 As Range, Valeur2 As Range, Valeur3 As Range, Valeur4, Valeur5 As Range)
' Définition du recalcul automatique
    Application.Volatile
    Dim Ligne As Range
    Dim Ok As Boolean
    Dim Total As Long
    With Application
        For Each Ligne In Plage.Rows
            If Not IsEmpty(Valeur1) Then Ok = .CountIf(Ligne, Valeur1) > 0
            If Not IsEmpty(Valeur2) Then Ok = Ok And .CountIf(Ligne, Valeur2) > 0
            If Not IsEmpty(Valeur3) Then Ok = Ok And .CountIf(Ligne, Valeur3) > 0
            If Not IsEmpty(Valeur4) Then Ok = Ok And .CountIf(Ligne, Valeur4) > 0
            If Not IsEmpty(Valeur5) Then Ok = Ok And .CountIf(Ligne, Valeur5) > 0
            Total = Total + Abs(Ok)
        Next
    End With
    NbSiValLignes = Total
End Function
Sinon je vois pas.

A+
 

chris

XLDnaute Barbatruc
Re : Rechercher combien de lignes contiennent plusieurs valeurs cherchée dans une pla

Bonjour

Par formule
=SOMMEPROD((E7:I12=F17:J17)*1)
 

JCGL

XLDnaute Barbatruc
Re : Rechercher combien de lignes contiennent plusieurs valeurs cherchée dans une pla

Bonjour à tous,

As-tu essayé avec Application.CountIf à la place de CountIf ?

Il faut remplacer Row qui est une variable VBA...

A+ à tous

Edition : Bises à Chris :) et tapes dans le dos à Hasco :)
 
Dernière édition:

Peanuts

XLDnaute Nouveau
Re : Rechercher combien de lignes contiennent plusieurs valeurs cherchée dans une pla

Merci Hasco,

La fonction marche très bien pour la cellule de résultat mais dès qu'on l'incrémente elle ne fonctionne plus.

J'ai copié les trois dernières colonnes du tableau sur un autre emplacement et je m'en suis servis comme valeurs de références.

J'ai inséré la formule à la gauche avec les valeurs nouvellement copié comme conditions. Je devrais normalement trouver le résultat de 1 à chaque ligne incrémentée car les valeurs sont identiques mais j'obtiens les résultat suivant: 1, 1, 1, 2, 2.

Je ne sais pas!

En tout cas c'est déjà une autre version de laquelle je peux m'inspirer.

Merci beaucoup!

A +
 

Peanuts

XLDnaute Nouveau
Re : Rechercher combien de lignes contiennent plusieurs valeurs cherchée dans une pla

Merci à tous ceux qui participent à la discussion!

La formule de Chris confirme ce que je disais:

=SOMMEPROD((E7:I12=F17:J17)*1)

Ceci ne donne pas le résultat escompté par exemple avec 6, Poulet, Camembert on obtient 4 alors qu'on devrait avoir 2. Si on inverse les valeurs on obtient 0 ou 1.

Merci quand même.

La remarque de JCGL semble prometteuse...

Mais il me semble que:

With Application
...
... .CountIf(Ligne, Valeur1) > 0
...
End With

Signifie Application.CountIf

Mais je ne connais pas bien VBA

Merci à tous!

Peanuts!
 
Dernière édition:

JCGL

XLDnaute Barbatruc
Re : Rechercher combien de lignes contiennent plusieurs valeurs cherchée dans une pla

Bonjour à tous,

Avant d'affirmer des choses, peux-tu jeter un coup d'œil sur le fichier déposé... ?
La formule de Chris donne 2

A+ à tous
 

Créez un compte ou connectez vous pour répondre

Vous devez être membre afin de pouvoir répondre ici

Créer un compte

Créez un compte Excel Downloads. C'est simple!

Connexion

Vous avez déjà un compte? Connectez vous ici.

Haut Bas