Rapport de message :*
 

Re: les quartés

Titre du sujet : Re: les quartés
par JeanMarie le 15/02/2013 10:21:57

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
=DECALER(Feuil1!$A$2;;;NB(Feuil1!$A:$A);20)
Ce qui donne pour ColA
=DECALER(Feuil1!$A$2;;;NB(Feuil1!$A:$A);20)
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
=FREQUENCE(BdD;W2:W71)
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

=EQUIV(MAX(X:X);X2:X71;0)
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
=SOMMEPROD(FREQUENCE(SI(BdD=AC$1;LIGNE(colA));LIGNE(BdD));FREQUENCE(SI(BdD=AB2;LIGNE(colA));LIGNE(BdD)))
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.
 =FREQUENCE(SI(FREQUENCE(SI(BdD=AC1;LIGNE(colA);"");LIGNE(colA));SI(BdD=AC1;"";BdD));$W2:$W71)
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 
=SI(FREQUENCE();BdD;"")
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 :

SI(FREQUENCE();SI(BdD=AC1;"";BdD))

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 :
=FREQUENCE(SI(FREQUENCE(SI(NB.SI(AI1:AJ1;BdD);LIGNE(colA);"");LIGNE(colA))=2;SI(NB.SI(AI1:AJ1;BdD);"";BdD));$W2:$W71)

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