EXCEL :: TRIER - TRIER.PAR - SOMMEPROD - CHOISIR - INDEX - SOMME.SI - NB.SI - DECALER - SEQUENCE : Tris dynamiques, regroupements et somme

[oguruma]

Bonjour le Forum,
Dans une application de gestion de personnel sur les évolutions de carrières - confronté à la production de KPI j'ai donc été amené à présenter des données triées et regroupées. Pour cela j'ai dû faire appel aux fonctions citées dans le titre. J'en profite aussi pour vous en faire partager avec un contenu totalement anonyme. Occasion aussi de rappeler comment on utilise ces fonctions avec quelques petites astuces.

Les données utilisées
Dans ce tableau on y trouve les effectifs d'une agence avec les évolutions de carrières sur l'indice, le type d'emploi et la classification.
Les chiffres exprimés totalement inventés pour le support montre le nombre d'évolutions par catégorie (indice, emploi, classification).

1. Tri le plus simple
Par défaut sur la 1ère colonne.

2. Tri sur une plage dynamique, choix des colonnes à trier
La construction de la plage dynamique passe par la fonction DECALER puis pour préciser sur quelles colonnes on souhaite trier : {1\4}

=TRIER(DECALER($A$4;;;NBVAL($A$4:$A$103);7);{1\4})

3. Tri sur une plage dynamique et choix des colonnes à trier et ordre pour chaque colonne
On ajoute {1\-1}

=TRIER(DECALER($A$4;;;NBVAL($A$4:$A$103);7);{1\4};{1\-1})

Variante sur une seule colonne : =TRIER(DECALER($A$4;;;NBVAL($A$4:$A$103);7);4;-1)

4. Tri avec un TOP (n) sur les résultats
Là c'est un peu plus complexe.
Le top(n) est détecté par la fonction SEQUENCE qui va retenir le n premiers résultats indiqués dans la liste déroulante. Ensuite il suffit de jongler avec les fonctions avancées de INDEX.
SEQUENCE($AR$2) = Nbre de lignes
{2\4\6} : Les colonnes

Le tri est réalisé par : TRIER(DECALER($A$4;;;NBVAL($A$4:$A$103);7);4;-1) ==> tri sur la colonne effectif

=INDEX(TRIER(DECALER($A$4;;;NBVAL($A$4:$A$103);7);4;-1);SEQUENCE($AR$2);{2\4\6})

5. Tris évolués avec TRIER.PAR

On indique la plage à trier et la plage critère -> sur quel plage doit-on s'appuyer pour trier les données (colonne effectif $D$4:$D$23) ?

6. Tri dynamique à la demande sur plusieurs colonnes de tri

Cette méthode demande un peu préparation au préalable.
Les liste déroulantes présente des noms de colonnes en texte qu'il faudra traduire en plage d'adresses.
Pour cela on passera par des champs nommés.
Le nom des champs nommés doit correspondre au nom présenté dans la liste déroulante.
Exemple :

Pour traduire la plage nommée en adresse plage dans la formule il faut donc passer par la fonction INDIRECT.
On applique la fonction sur le contenu de la cellule déroulante - exemple : INDIRECT($T$25)
En ce qui concerne l'ordre pas d'autre choix que de passer par la fonction SI... [enfin... je n'ai pas trouvé mieux]

=TRIERPAR($A$4:$G$23;INDIRECT($T$25);SI($U$25="CROISSANT";1;-1);INDIRECT($T$26);SI($U$26="CROISSANT";1;-1);INDIRECT($T$27);SI($U$27="CROISSANT";1;-1))

7. Regroupements et sommes

Comme on peut le voir on regroupe les données par paquet de 5. On connait ici le facteur de groupage.
Le groupage est réalisé par NBVAL($AA$29:AA29)*5-5;0;5 qui reste assez simple à comprendre (pas trouvé mieux pour le cas que j'ai eu à traiter en réel concernant la formule)

=SOMME(DECALER($D$4;NBVAL($AA$29:AA29)*5-5;0;5))

8. Regroupements et sommes - facteur de groupage dynamique
Le nombre d'éléments par paquet peut en fait être différent (cas de mon application réelle).
Pour cela plusieurs manières d'y parvenir. Les voici progressivement

On passe par un NB.SI que l'on répète chaque fois que l'on en a besoin. C'est assez lourd.

=SOMME(DECALER($D$4;NBVAL($AA$29:AA29)*NB.SI(DECALER($A$4;;;NBVAL($A$4:$A$103);1);AI29)-NB.SI(DECALER($A$4;;;NBVAL($A$4:$A$103);1);AI29);0;NB.SI(DECALER($A$4;;;NBVAL($A$4:$A$103);1);AI29)))

Le plus simple étant de le calculer une seule fois et de l'appliquer. Pour cela nous avons la fonction LET qui permet de stocker en mémoire le résultat d"un calcul pour pouvoir l'utiliser après

=LET(facteur;NB.SI(DECALER($A$4;;;NBVAL($A$4:$A$103);1);AI29);SOMME(DECALER($E$4;NBVAL($AA$29:AA29)*facteur-facteur;0;facteur)))

Contrainte : le critère de groupage doit correspondre à l'ordre des données conformément au tableau

On peut au final se libérer de cette contrainte en passant par la fonction très puissante SOMMEPROD

=SOMMEPROD(--(ETAB=AA36);EFFECTIF)

Et aussi une méthode via un SOMME.SI

=SOMME.SI(ETAB;AA44;EFFECTIF)

9. Le tout en dynamique par sélection du KPI à obtenir

=SOMMEPROD(--(ETAB=$AJ$35);INDIRECT($AJ$36))

10. Tri avec choix de la présentation des colonnes

=TRIERPAR(CHOISIR({1\2\3\4};CODE_AGENCE;AGENCE;INDICE;CLASSIFICATION);EFFECTIF;-1)

11. Via un tableau structuré - quelques exemples