Réaliser des comparatifs en définissant des échantillons selon critères

[Julie-F]

Bonjour à tous,

Voilà ce qui m'amène. Je dois pouvoir comparer (onglet RESULT) un département (SELECTION = H2) à un échantillon de départements selon certains critères.
Mes élements comparatifs ainsi que les calculs se trouvent dans l'onglet BD1.
Si je ne rencontre aucune difficulté (dans l'onglet BD1) lorsqu'il s'agit d'obtenir la moyenne des échantillons devant répondre à un seul critère en combinant un SOMME SI et RECHERCHEV, je n'y parviens pas lorsqu'il faut que je cumule à la fois le critere de la colonne E et le critere rang contenu dans la colonne F (rang auquel on applique selon le contenu de E un chiffre de plus ou moins 10 ou plus ou moins 7....)
C'est à dire que mon échantillon devra être la moyenne des cumuls de lignes contenant à la fois (exemple SV1-IDF) mais également dvra répondre à plus à moins 3 (3 étant l'écart par rapport au rang de mon département analysé)

Je pense que ma demande doit être assez obscure aussi j'y joins un petit fichier.

Si l'un d'entre vous pouvait m'apporter ses lumières, ce serait vraiment super parce que là, je n'y arrive absolument pas.

 

[Modeste]

Re : Réaliser des comparatifs en définissant des échantillons selon critères

Bonjour Julie-F,

Je ne suis pas certain d'avoir compris tes différentes explications (elles sont sans doute claire pour toi qui jongle avec ton fichier). Je suis donc parti de ce que je croyais comprendre de ton petit exemple tout au fond de la feuille "bd1", mais sans arriver à faire un lien avec le petit tableau qui figure quelques lignes plus haut!?

Essaie toujours (en G342, par exemple) la formule suivante:

=MOYENNE(SI($E$3:$E$337=$E$340;SI($F$3:$F$337>=$F$340-10;SI($F$3:$F$337<=$F$340+10;$G$3:$G$337))))

Valider avec Ctrl+Shift+Enter

 

[Julie-F]

Re : Réaliser des comparatifs en définissant des échantillons selon critères

Bonjour Modeste,

Je me doute que ma demande n'était pas très claire aussi merci beaucoup pour ton aide.
J'ai modifié le fichier joint en y ajoutant un onglet exemple en ne reprenant que les lignes de mon onglet bd1 qui correspondent au 1er critère : SV7
Puis sachant que mon département choisi occupe le rang 18 , j'ai coloré en bleu les 10 rang supérieurs et les 10 rangs inférieurs à 18.
Tout d'abord je n'obtiens pas les même résultats que ceux de ta formule.
2ème difficulté : 10 s'applique UNIQUEMENT pour les lignes ayant en colonne E = SV7;
si j'ai SV4 en colonne E alors ce ne sont plus les 10 lignes avant et apres mais l'écart sera de plus ou moins 7
et enfin si j'ai SV1-IDF en colonne E alors l'écart sera de plus ou moins 5

Est ce plus explicite, ainsi ?

 

[Modeste]

Re : Réaliser des comparatifs en définissant des échantillons selon critères

Re-bonjour Julie-F,

Tout d'abord je n'obtiens pas les même résultats que ceux de ta formule.

Dans la formule proposée plus tôt j'incluais, dans le calcul de la moyenne, les valeurs pour SV7, dont le rang était égal à 18 ... ton calcul étant différent, voici une correction qui semble donner les mêmes résultats que dans ton exemple modifié:

=MOYENNE(SI($E$3:$E$337=$E$340;SI($F$3:$F$337>=$F$340-10;SI($F$3:$F$337<=$F$340+10;SI($F$3:$F$337<>$F$340;G$3:G$337)))))

... Toujours valider comme une formule matricielle!

Pour ce qui est des écarts, les trois cas que tu définis sont à ajouter dans la même formule (au lieu d'écrire simplement $F$340-10, il faudrait ajouter une condition??) ... mais quid si ce sont les autres valeurs?

 

[Julie-F]

Re : Réaliser des comparatifs en définissant des échantillons selon critères

Modeste,

Et bien pour les autres cas, seule le 1er critère sera pris en compte.
Autrement dit, nous cumulerons toutes les lignes comprenant une référence identique contenues en colonne E sans toutefois prendre le département selectionné.
En fait, comme dans l'exemple hors rang 18 (rang 18 étant sélectionné initialement)
Mais ne risque t on pas d'avoir des proclémes avec une formule qui serait trop longue

J'ignore si je me suis bien expliquée

 

[Modeste]

Re : Réaliser des comparatifs en définissant des échantillons selon critères

Re²,

J'ignore si je me suis bien expliquée

... Je crains que non (en ce qui me concerne, en tout cas )
La seule chose que je peux te proposer à ce stade (sous réserve que j'aie bien compris cette partie) est une adaptation de la formule proposée. Peux-tu tester ce qui suit et nous dire si ça gère correctement les 3 cas de figure SV4, SV7 et SV1-IDF ??

=MOYENNE(SI($E$3:$E$337=$E$340;SI($F$3:$F$337>=$F$340-SIERREUR(INDEX({3;7;10};EQUIV($E$340;$C$353:$C$355;0));0);SI($F$3:$F$337<=$F$340+SIERREUR(INDEX({3;7;10};EQUIV($E$340;$C$353:$C$355;0));0);$G$3:$G$337))))

... c'est encore et toujours une formule matricielle!

 

[Julie-F]

Re : Réaliser des comparatifs en définissant des échantillons selon critères

Modeste,

Je viens de tester la formule en la validant avec Ctrl+Shift+Enter.
J'obtiens en guise de résultat un #nom ?

 

[Modeste]

Re : Réaliser des comparatifs en définissant des échantillons selon critères

Re³,

Désolé, j'ai utilisé une fonction non-reconnue par Excel 2003 Essaye plutôt

=MOYENNE(SI($E$3:$E$337=$E$340;SI($F$3:$F$337>=$F$340-SI(ESTERREUR(INDEX({3;7;10};EQUIV($E$340;$C$353:$C$355;0)));0;INDEX({3;7;10};EQUIV($E$340;$C$353:$C$355;0)));SI($F$3:$F$337<=$F$340+SI(ESTERREUR(INDEX({3;7;10};EQUIV($E$340;$C$353:$C$355;0)));0;INDEX({3;7;10};EQUIV($E$340;$C$353:$C$355;0)));SI($F$3:$F$337<>$F$340;G$3:G$337)))))

Toujours Ctrl+Shift+Enter pour valider

 

[Julie-F]

Re : Réaliser des comparatifs en définissant des échantillons selon critères

Modeste, ne sois pas désolé c'est vraiment sympa de m'accorder de ton temps et cette nouvelle formule fonctionne à merveille.

Ne pourrait-on pas pour pallier les cas où il existe un rang des lignes qui n'en n'ont pas (colonne F) prévoir dans l'onglet RESUL, 2 cas :
1er cas si J5 > 0 ALORS prendre l'échantillon calculé qui se trouve sur la ligne 342
2eme cas si J5 est vide ALORS prendre l'échantillon que je calculerai par exemple en ligne 344

C'est absurde, comme raisonnement ?

 

[Modeste]

Re : Réaliser des comparatifs en définissant des échantillons selon critères

Julie,

C'est absurde, comme raisonnement ?

Certainement pas! ... puisque je travaillais dans le même sens
Deux formules différentes en lignes 342 et 343 (il te revient de vérifier la validité de celle-ci et de t'assurer que j'ai compris !!). En feuille "RESUL", colonne E, une formule qui affiche le contenu de la ligne 342, si J4 contient SV4, SV7 ou SV1-IDF; sinon c'est le contenu de la ligne 343 qui apparaît.

J'insiste, vérifie soigneusement

 

[Julie-F]

Re : Réaliser des comparatifs en définissant des échantillons selon critères

Modeste,

J'ai fait des vérifications çà et là et je peux te dire que çà fonctionne à merveille.
Demain je vais l'appliquer sur le fichier original (trop lourd et liens trop nombreux pour pouvoir le mettre ici)
Je ne manquerai pas de te laisser un petit message pour te donner des nouvelles.

En tout cas, mille merci. Ton aide m'a été vraiment TRES précieuse.

 

[Modeste]

Re : Réaliser des comparatifs en définissant des échantillons selon critères

Salut,

mille merci

Mais deux riens

Ceci dit, prudence, si le volume des données est beaucoup plus conséquent (avec de nombreux liens en plus), les formules matricielles risquent d'augmenter sensiblement le temps de recalcul ... travailler sur une copie est nettement préférable

Ton écho sera le bienvenu!

 

[KenDev]

Re : Réaliser des comparatifs en définissant des échantillons selon critères

Bonjour à tous,

@Julie : Je ne peux te répondre :
"Julie-F a dépassé son quota de messages privés et ne peut donc plus accepter de nouveaux messages tant qu'il n'aura pas libéré un peu d'espace."

KD