oguruma
XLDnaute Impliqué
Bonjour le Forum,
Ce post a pour but de montrer comment totaliser la colonne d'un tableau soit en mode plage de données soit en mode tableau. Pour ma part je préfère la méthode tableau qui offre de nombreux avantages.
Extrait des colonnes d'un tableau
Voici les différents cas exposés (il y a peut-être encore d'autres solutions)
Cas quand le tableau ou la plage sont filtrés
Prenons les dans l'odre
=INDIRECT("T_VENTES_SUR_12_MOIS[[#En-têtes];[Janvier]:[Décembre]]")
Pour le mode plage ==>
Obtention du TotalV1 (non filtré)
=SOMME(INDIRECT("T_VENTES_SUR_12_MOIS["&$C$2&"]"))
C'est sans doute l'une des plus adaptée et certainement recommandée. Il faut bien entendu si vous avez une plage de données la transformer en Tableau Structuré.
On cible la colonne Mois via la fonction INDIRECT qui a été présentée dans l'un de mes post récemment.
Obtention du TotalV2 (non filtré)
=SOMME(INDEX($I7:$T1100;0;EQUIV(F2;$I6:$T$6;0)))
Il faudra être vigilant sur l'orthographe du mois et aussi qu'il soit bien au format texte !
Obtention du TotalV3 (non filtré)
=SOMME(INDEX($I$7:$T$1100; 0; RECHERCHEV($F$2; {"Janvier"\1;"Février"\2;"Mars"\3;"Avril"\4;"Mai"\5;"Juin"\6;"Juillet"\7;"Août"\8;"Septembre"\9;"Octobre"\10;"Novembre"\11;"Décembre"\12}; 2; 0)))
Celle-ci est pour les inconditionnels du RECHERCHEV (ils ne plus peuvent pas s'en passer)
Via la constante matricielle {"Janvier"\1;"Février"\2;"Mars"\3;"Avril"\4;"Mai"\5;"Juin"\6;"Juillet"\7;"Août"\8;"Septembre"\9;"Octobre"\10;"Novembre"\11;"Décembre"\12} on va créer un tableau virtuel en mémoire permettant de retrouver la colonne mois dans l'extrait de plage ne contenant que les mois.
On peut aussi préférer cette version sans le rechercheV : =SOMME(INDEX(I7:T1100; 0; EQUIV(F2; I6:T6; 0)))
Obtention du TotalV4 (non filtré)
=SOMMEPROD(($I$6:$T$6=$F$2)*($I$7:$T$1100))
SOMMEPROD reste aussi une bonne méthode par sa puissance d'exécution. A préférer à la fonction RECHERCHEV.
Obtention du TotalV5 (non filtré)
=SOMME(RECHERCHEX($F$2; $I$6:$T$6; $I$7:$T$1100))
L'utilisation de la fonction RECHERCHEX est aussi un bon compromis si vous ne voulez pas utiliser les tableaux structurés.
Il faut être bien entendu sous Office 365.
SI filtreV1
=AGREGAT(9; 5; INDIRECT("T_VENTES_SUR_12_MOIS[" & $C$2 & "]"))
Une nouvelle l'utilisation des tableaux structurés est plus adaptée.
5 : Indique d'ignorer les lignes masquées
SI filtreV2
=AGREGAT(9; 5; INDEX(I7:T1100; 0; EQUIV($F$2; $I$6:$T$6; 0)))
SI filtreV3
=SOMMEPROD(($I$6:$T$6=$F$2)*($I$7:$T$1100)*(SOUS.TOTAL(103;DECALER($I$7;LIGNE($I$7:$I$1100)-LIGNE($I$7);0;1))))
Cette version est mise ici pour le "fun" et l'apprentissage. Elle est plus complexe et rappelant que DECALER est volatile. Donc attention si votre plage de données comporte beaucoup de lignes. La petite astuce que j'ai dû employer est de simuler si la ligne est affichée ou pas via le témoin 1 pour affichée ou via le témoin 0 pour masquée. La présentation des lignes une à une est fournie par la fonction LIGNE(). Bon pas évident....un peu tordu...pour la recherche.... mais cette solution c'est juste pour la gymnastique intellectuelle et la manipulation des fonctions.
SI filtreV4
=SOUS.TOTAL(109; DECALER($I$7:$I$1100; 0; EQUIV($F$2; $I$6:$T$6; 0)-1))
Rappel : 109 pour demander la somme à la fonction SOUS.TOTAL
EQUIV renvoi la position dans la matrice. 1 Pour janvier... etc. 12 pour décembre
SI filtreV5
=AGREGAT(9; 7; INDEX(I7:T1100; 0; EQUIV(F2; I6:T6; 0)))
Ici avec AGREGAT on va sécuriser les résultats. Les erreurs dans les colonnes ne ignorées. Cela évite les #Erreur, plantage etc.
SI filtreV6
=AGREGAT(9; 7; INDIRECT("T_VENTES_SUR_12_MOIS[" & $F$2 & "]"))
Une nouvelle fois, la méthode la plus adaptée et la plus sécurisée avec les tableaux structurés.
SI filtreV7
=AGREGAT(9; 7; RECHERCHEX($F$2; $I$6:$T$6; $I$7:$T$1100))
Oui il est aussi possible de mixer AGREGAT et RECHERCHEX si on ne veut toujours pas utiliser les tableaux structurés.
J'espère qu'à travers tous ces exemples la totalisation de colonnes dans un tableau Excel n'aura plus de secret. Je n'ai peut-être pas exploiter toutes les solutions... il doit bien en exister d'autres.
Ce post a pour but de montrer comment totaliser la colonne d'un tableau soit en mode plage de données soit en mode tableau. Pour ma part je préfère la méthode tableau qui offre de nombreux avantages.
Extrait des colonnes d'un tableau
Voici les différents cas exposés (il y a peut-être encore d'autres solutions)
Cas quand le tableau ou la plage sont filtrés
Prenons les dans l'odre
=INDIRECT("T_VENTES_SUR_12_MOIS[[#En-têtes];[Janvier]:[Décembre]]")
Pour le mode plage ==>
Obtention du TotalV1 (non filtré)
=SOMME(INDIRECT("T_VENTES_SUR_12_MOIS["&$C$2&"]"))
C'est sans doute l'une des plus adaptée et certainement recommandée. Il faut bien entendu si vous avez une plage de données la transformer en Tableau Structuré.
On cible la colonne Mois via la fonction INDIRECT qui a été présentée dans l'un de mes post récemment.
Obtention du TotalV2 (non filtré)
=SOMME(INDEX($I7:$T1100;0;EQUIV(F2;$I6:$T$6;0)))
Il faudra être vigilant sur l'orthographe du mois et aussi qu'il soit bien au format texte !
Obtention du TotalV3 (non filtré)
=SOMME(INDEX($I$7:$T$1100; 0; RECHERCHEV($F$2; {"Janvier"\1;"Février"\2;"Mars"\3;"Avril"\4;"Mai"\5;"Juin"\6;"Juillet"\7;"Août"\8;"Septembre"\9;"Octobre"\10;"Novembre"\11;"Décembre"\12}; 2; 0)))
Celle-ci est pour les inconditionnels du RECHERCHEV (ils ne plus peuvent pas s'en passer)
Via la constante matricielle {"Janvier"\1;"Février"\2;"Mars"\3;"Avril"\4;"Mai"\5;"Juin"\6;"Juillet"\7;"Août"\8;"Septembre"\9;"Octobre"\10;"Novembre"\11;"Décembre"\12} on va créer un tableau virtuel en mémoire permettant de retrouver la colonne mois dans l'extrait de plage ne contenant que les mois.
On peut aussi préférer cette version sans le rechercheV : =SOMME(INDEX(I7:T1100; 0; EQUIV(F2; I6:T6; 0)))
Obtention du TotalV4 (non filtré)
=SOMMEPROD(($I$6:$T$6=$F$2)*($I$7:$T$1100))
SOMMEPROD reste aussi une bonne méthode par sa puissance d'exécution. A préférer à la fonction RECHERCHEV.
Obtention du TotalV5 (non filtré)
=SOMME(RECHERCHEX($F$2; $I$6:$T$6; $I$7:$T$1100))
L'utilisation de la fonction RECHERCHEX est aussi un bon compromis si vous ne voulez pas utiliser les tableaux structurés.
Il faut être bien entendu sous Office 365.
SI filtreV1
=AGREGAT(9; 5; INDIRECT("T_VENTES_SUR_12_MOIS[" & $C$2 & "]"))
Une nouvelle l'utilisation des tableaux structurés est plus adaptée.
5 : Indique d'ignorer les lignes masquées
SI filtreV2
=AGREGAT(9; 5; INDEX(I7:T1100; 0; EQUIV($F$2; $I$6:$T$6; 0)))
SI filtreV3
=SOMMEPROD(($I$6:$T$6=$F$2)*($I$7:$T$1100)*(SOUS.TOTAL(103;DECALER($I$7;LIGNE($I$7:$I$1100)-LIGNE($I$7);0;1))))
Cette version est mise ici pour le "fun" et l'apprentissage. Elle est plus complexe et rappelant que DECALER est volatile. Donc attention si votre plage de données comporte beaucoup de lignes. La petite astuce que j'ai dû employer est de simuler si la ligne est affichée ou pas via le témoin 1 pour affichée ou via le témoin 0 pour masquée. La présentation des lignes une à une est fournie par la fonction LIGNE(). Bon pas évident....un peu tordu...pour la recherche.... mais cette solution c'est juste pour la gymnastique intellectuelle et la manipulation des fonctions.
SI filtreV4
=SOUS.TOTAL(109; DECALER($I$7:$I$1100; 0; EQUIV($F$2; $I$6:$T$6; 0)-1))
Rappel : 109 pour demander la somme à la fonction SOUS.TOTAL
EQUIV renvoi la position dans la matrice. 1 Pour janvier... etc. 12 pour décembre
SI filtreV5
=AGREGAT(9; 7; INDEX(I7:T1100; 0; EQUIV(F2; I6:T6; 0)))
Ici avec AGREGAT on va sécuriser les résultats. Les erreurs dans les colonnes ne ignorées. Cela évite les #Erreur, plantage etc.
SI filtreV6
=AGREGAT(9; 7; INDIRECT("T_VENTES_SUR_12_MOIS[" & $F$2 & "]"))
Une nouvelle fois, la méthode la plus adaptée et la plus sécurisée avec les tableaux structurés.
SI filtreV7
=AGREGAT(9; 7; RECHERCHEX($F$2; $I$6:$T$6; $I$7:$T$1100))
Oui il est aussi possible de mixer AGREGAT et RECHERCHEX si on ne veut toujours pas utiliser les tableaux structurés.
J'espère qu'à travers tous ces exemples la totalisation de colonnes dans un tableau Excel n'aura plus de secret. Je n'ai peut-être pas exploiter toutes les solutions... il doit bien en exister d'autres.
Dernière édition: