[XL-2007] quartile VS sommeprod

[fourchette]

salut,

j'ai un gros tableau (qui change dynamiquement parce que c'est un extract database) et je voudrais calculer des quartiles / centiles mois par mois, personne par personne
ex du tableau :
jack jan 23
jack jan 2.5
paul jan 25.2
paul fev 25
jack fev 65
jack fev 65

pb : la fonction quartile demande à ce qu'on lui file une plage de cellule bien définie. or les cellules à retenir pour le calcul sont dépendantes de certains critères (mois, personne)

d'habitude pour des trucs un peu tordus, j'y vais avec mon amie sommeprod ou qd c'est vicelard à la formule matricielle (mais je préfère éviter, trop lourd à maintenir si qqn d'autre doit relire)

là je sèche. qqn a une idée ?

[Pierre Fauconnier]

Bonjour

Perso, je ne vois pas d'autres techniques que Sommeprod ou matricielle...

=QUARTILE((A2:A10000="Pierre")*(B2:B10000="Janvier")*C2:C10000;2)

validée en matricielle renverra le 2ième quartile.

Travailler avec une (des) plage(s) nommée(s) permettra d'intégrer la dimension variable des plages.

[fourchette]

merci pierre, une formule du type de cette que tu as montée est effectivement ma conclusion à ce jour.

qd tu parles de plage nommée, il s'agit de faire un nom de référence dont la dimension est variable (ie la référence pointe vers une sorte de formule). tu confirmes ?

personnellement, depuis excel 2007, je préfére utiliser les tableau nommés puis en appeler les colonnes. c'est un peu plus long à écrire mais ca permet de rester variable. en bonus, lorsque le tableau d'entrée est updaté, il n'est pas besoin de mettre éventuellement à jour un qqconque formule.

par exemple je préfère
remplacer $A$2:$A$9999 par mon_tableau[colonne_1]
remplacer $B$2:$B$9999 par mon_tableau[colonne_2]

on parle bien d'une technique similaire n'est-ce pas ?

[fourchette]

je maintiens ma question sur le nommage des plages

par contre, j'ai testé avec

Code :

1
2
3
4
5
6
7
8
 
{=QUARTILE(
 ( 
    (tableau_export[mois_cloture]=E$62) 
 * (tableau_export[Niveau]=$C63)
 * (tableau_export[nb.jours.ouvres])
 )
;$D63)}

mais ca ne marche pas vraiment.
en fait le quartile 0 (minimum) et le quartile 4 (max) marchent bien je crois.
mais les quartiles 1, 2 et 3 (respectivement 25eme, 50eme et 75eme) retournent zero.

en regardant la formule en question sur un 25eme quartile à l'évaluation de formule, on voit que le cross join sur les trois conditions se fait bien, mais du coup, on dirait qu'au lieu d'exclure les cellules qu'il faut sortir du filtre, elles sont comptées comme étant à zero :

Code :

1
2
 
=quartile({0;0;0;0;0;0;....<<plein de zero correspondant à toutes les cells qui ne vérifient pas la condition>>...;0;1;12;15;14;2;2;1;1;0};1)

bref, j'ai l'impression que la formule est mathématiquement correcte, mais que l'approche ne convient pas.

non ?

[Pierre Fauconnier]

Plages dynamiques: Je parlais bien de plages créées avec DECALER(). Les tableaux 2007 permettent effectivement un peu plus de souplesse.

Par contre, pour revenir sur la fonction Quartile, les multiplications de valeurs booléennes renvoient 0 si une condition n'est pas remplie, ce qui modifie les valeurs des quartiles, bien entendu.

Il faut utiliser une syntaxe comme celle-ci (validée en matricielle, bien sûr)

=QUARTILE(SI((tableau_export[mois_cloture]=E$62)*(tableau_export[Niveau]=$C63);tableau_export[nb.jours.ouvres];"");$D63)

qui renvoient la valeur de la colonne nb.jours.ouvres lorsque les conditions sont remplies ou une chaine vide (non prise en compte par les fonctions quartile, Centile, etc) lorsque l'une ou l'autre condition n'est pas remplie.

[fourchette]

bien vu le coup du si pour retourner une chaine vide.

je vais vite tester ca dans la journée

[fourchette]

ca a l'air bon avec cette formule.

il faudrait que je checke cellule par cellule, mais pour l'instant ca tient la route.

je reviendrai ds qqs jours mettre à résolu si effectievment ca marche bien comme attendu.

[fourchette]

en pleine archéologie dans les faq (c'est qu'y en a un sacré paquet :p)

je tombe sur http://excel.developpez.com/faq/?pag...lePlageFiltree

qui est finalement très proche je crois (pas pu ouvrir le fichier pour l'instant coz proxy )

si ca peut aider qqn un jour