bonjour le forum

je recherche une formule ou code vba pour sortir d'une BdD les 4 N° qui sont le plus souvent ensemble.
Actuellement je procéde en 4 étapes
1-col WX les N° les + sortis
2-col AB:AC le N° le + sorti avec son ami
3-col AH:AI l'ami des 2 précédent N°
4- l'ami des 3 précédent N°
Malgré des macros c'est assez long
Y aurait-il une façon de faire en une seule étape
merci

Bonjour Jad73, Didier, le Forum 

Pour commercer, Je pense qu'Eric ou Mireille aimerait un petit retour sur ton dernier fil.

Pour ton fil, cela ne pas être simple de tout expliquer.

Premier point, je pense que tu as une erreur dans la définition dynamique de tes plages BdD et ColA, la soustraction -1 te fait perdre une ligne de donnée, est-ce voulu ?. Je te conseille d'utiliser NB à la lace de NBVAL
Ce qui donne pour BdD
Ce qui donne pour ColA
Voilà la correction effectué passons à la formule de la colonne W.
Tu utilises la fonction NB.SI qui comptabilise le nombre d'occurrence d'une valeur dans une plage, et tu répètes donc cette formule 70 fois, mais tu as une autre fontion qui fera le même travail, d'ailleurs tu l'utilises par la suite, c'est la fonction FREQUENCE.
Pour mettre en oeuvre cette formule, il faut avant de la saisir, il faut au préalable sélectionner la plage de cellules qui permettra de recevoir la "matrice" retournée par FREQUENCE.
Dans ton fichier, sélectionne la plage X2:X71, puis tape dans la zone de saisie la formule
ensuite tu la valides en tant que matricielle (touches Ctrl+Shift+entrer)
Cela va afficher les occurrences des valeurs de 1 à 70 qui sont présentes dans la plage DdB.

Ensuite il faut déterminer la valeur de la cellule AC1, tu peux utiliser la formule

Cette formule recherche dans la plage X2:X71 la valeur retournée par la fonction MAX(X:X)
Voilà on vient de déterminer la valeur du 1er Niveau

Passons à la suite, tu utilises une formule à base de SOMMEPROD(FREQUENCE;FREQUENCE) qui ne retournera qu'une seule valeur, donc tu dois aussi la répéter 70 fois. Je te propose encore une fois une formule qui fera le même travail mais pour la globalité des valeurs à tester, et surtout en une seule fois.
Tourjours basée avec la fonction FREQUENCE.
Dans ta formule
dans la partie FREQUENCE, tu testes pour chaque valeur de la plage BdD si celle-ci est égale pour la valeur retenue au 1er niveau, dans la partie vrai tu en retournes le n° de la ligne, dans le cas contraire tu retournes une valeur nulle. Tu obtiens donc par FREQUENCE une matrice contenant les numéros des lignes contenant la valeur du 1er niveau. je vais me service de cela pour la formule que je te propose.
Il y a trois imbrications dans cette formule, donc trois étapes, la première c'est celle que je viens de décrire, la deuxième étape, c'est de retournée les valeurs de BdD associée aux lignes retournées. On pourrait le faire simplement avec 
mais il faut neutraliser la valeur cible du 1er Niveau, si on ne le fait pas, c'est encore la même valeur qui sortirait au 2ème niveau. C'est donc pour cela que j'ai rajouté la condition :

La dernière étape de la fonction est identique à la première formule originelle, comptabiliser le nombre d'occurrences des valeurs isolées par les valeurs allant de 1 à 70. La mise en oeuvre reste la même.

La formule pour connaître la valeur de la cellule AJ reste la même, il suffit d'adapter la plage de la fonction MAX. Voilà pour le deuxième niveau, passons aux prochains niveaux 

Même procédé il faut isoler les lignes contenant les valeurs à comptabiliser, mais il faut rajouté ou modifier notre formule du 2ème niveau pour isoler non pas par rapport à une valeur met par x valeurs.
On ne peut pas utiliser les fonctions OU ou ET, une seule méthode à ma connaissance, la fonction NB.SI.
Avec une particularité ce n'est pas les valeurs retournées dans les différents niveaux qui sera la plage des critères mais les valeurs de la plage BdD
Voilà la formule de la colonne AI :

Outre la modification des deux NB.SI, tu peux observer que j'ai rajouté le teste d'un comparateur =2 sur la matrice des n° des lignes retournée par FREQUENCE. Cela permet de ne prendre en compte que les lignes contenant les valeurs des 2 niveaux. Même mise en oeuvre.
Même formule en AQ pour déterminer la valeur du 3ème niveau.

Tu l'auras sans doute compris, la formule est identique pour le niveau suivant, il suffit d'adapter les plages NB.SI et du comparateur du nombre de lignes de la matrice.

Voilà ce qui permet d'alléger ton fichier.
Je reviendrais pour t'expliquer une autre méthode en utilisant la fonction MODE.MULTIPLE.

Met déjà en pratique cette méthode, et fait moi un retour des questions que tu auras.

@+Jean-Marie

Re...

Près pour la suite .... lol

La précédente version était utilisable avec toutes les versions d'Excel. Cela ne sera pas le cas pour la solution de ce post.
Dans les anciennes versions d'Excel, la fonction MODE() permet de retourner la valeur la plus représentée dans une liste de valeurs. Mais elle a une petite particularité, ce n'est que la première valeur dans l'ordre d'apparition dans la liste (on commence toujours par lire la liste de la gauche vers la droite et vers le bas) qui est retournée dans le cas ou plusieurs valeurs avaient le même nombre d'occursences.
Dans ton exemple les valeurs 4 - 29 - 49 ont le même nombre d'occurrences (18), mais dans l'ordre d'apparition c'est la valeur 49 qui se trouve sur la 1ère ligne, elle aurait été retournée par la fonction.

Avec la nouvelle version d'Excel, la fonction MODE.MULTIPLE() a été rajoutée. Cette fonction retourne non pas une valeur mais une matrice verticale des différentes valeurs ayant le même nombre d'occurrences et ce dans l'ordre d'apparition dans la liste.

Pour retrouver le même résultat, il faut récupéré la valeur la plus faible de cette matrice, cette simple formule suffit
pour info c'est inutile de la valider par Ctrl+shift+Entrer, je place cette formule en BC2


Pour le 2ème niveau, on réutilise le même principe de filtration des valeurs indiquées dans le post précédent en l'intégrant la formule précédente. Attention il faut valider cette formule par Ctrl+Shift+entrer, formule à mettre en BC3

Pour les deux autres niveaux si tu as compris mon post précédent, cela doit être un jeu d'enfant, lol

Voilà en 4 formules tu obtiens le même résultat.

@+Jean-Marie


 

bonsoir Jean-Marie,le forum
merci pour la modification des formules,pour le moment je n'ai fait que le post1,j'ai pas mal de SOMMEPROD à changer,je verrais plus tard le 2.
merci encore
a+