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:

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 +
 

JCGL

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

Bonjour à tous,

Un fichier avec la formule de Chris, la fonction d'Hasco et une MeFC :
Code:
=NB.SI($E7:$I7;$F$17:$J$17)

A+ à tous
 

Pièces jointes

  • Fonction combinaison.xls
    25 KB · Affichages: 54
  • Fonction combinaison.xls
    25 KB · Affichages: 57
  • Fonction combinaison.xls
    25 KB · Affichages: 59

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
 

Peanuts

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

Bonjour JCGL,

Je m'excuse si quelqu'un a pensé que je gueulais.
j'ai le même fichier. Dans la cellule j'ai mis:
F7= 6
G7= Poulet
H7= Camembert

Dans la cellule I26 J'ai mis la formule de Chris (j'ai modifié la plage car je n'ai pris que la plage encadrée ou doit se situer la recherche). Ce qui donne:

A la formule:
=SOMMEPROD((E8:I12=F17:J17)*1)
j'obtient 0

A la formule:
=NbSiValLignes(E8:I12;F17;G17;H17;I17;J17)
quand je compte manuellement les valeurs j'obtiens 2

Si j'inverse les valeurs ex:
F7= Poulet
G7= 6
H7= Camembert
j'obtient 1

Alors mon programme Excel fonctionne peut-être mal.

Enfin ce n'est pas une méchanceté que je dis c'est juste les résultats de ce que j'ai constaté. Je me suis peut-être trompé.

Pardon.
 

JCGL

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

Bonjour à tous,

Sur mon fichier déposé as-tu :

  • 2 lignes en saumon
  • 2 en fonction
  • 2 en formule
Si non, voir si tu n'es pas en Calcul sur Ordre

A+ à tous

Edition : Effectivement si tu poses :

F7= 6
G7= Poulet
H7= Camembert

La formule ne fonctionne plus
 
Dernière édition:

JeanMarie

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

Bonjour tout le monde

Une proposition en formule, avec la fonction FREQUENCE, formule à valider par la combinaison de touche Ctrl+Shift+Entrer.

Ne t'attend pas à des miracles, vu le nombre de données de ton tableau.

@+Jean-Marie
 

Pièces jointes

  • Recherche Combinée.xls
    22.5 KB · Affichages: 54

Peanuts

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

Bonjour Jean-Marie,

Merci de prendre part à la discussion.

Avec la proposition de Hasco il me semble que c'est ce qui est le plus pertinent.

Je vais jongler avec ce que tu as fait pour voir les limites de cette formule.

Il me semble qu'il faut ajouter devant cette formule:

{=SI(ET(E18="";F18="";G18="";H18="";I18="");0;...FORMULE...)}

car sinon elle retourne 6 quand toutes les conditions sont vides.

Mais en tout cas je tire mon chapeau: Bravo les experts!

Merci!
 
Dernière édition:

Peanuts

XLDnaute Nouveau
[Résolu] Rechercher combien de lignes contiennent plusieurs valeurs cherchée dans une

[Résolu]

Bonjour à tous!

Un grand bravo à Jean-Marie!

J'ai testé la formule avec succès (comparaison manuelle vs résultat) sur de grande plages situé à d'autres emplacement, en inversion, etc.

Les seules choses que je n'ai pas testés sont la pertinence si dans une plage de 20 colonnes, et qu'il y a deux fois la combinaison représentée sur la même ligne et mélangées si la formule ressort 2 ou 1. Je n'ai pas non plus cherché si dans la base de données il y a des #NA ou #Valeur si la formule fonctionne toujours. J'ai quand même regardé si quand une partie de la base de donnée est vide si cela fonctionne et la réponse est oui. Mais en tout cas si il n'y a pas de répétition des données dans la ligne, ça marche impec et c'est ce que je cherchais.

Merci beaucoup à tous ceux qui ont pris part à la discussion. Vous êtes des chefs!

Peanuts!
 
Dernière édition:

Discussions similaires

Statistiques des forums

Discussions
311 720
Messages
2 081 915
Membres
101 837
dernier inscrit
Ugo