janvier
2011
Un article de Pierre Fauconnier
Depuis la version 2007, Excel met à notre disposition quelques nouvelles fonctions, parmi lesquelles SOMME.SI.ENS(), NB.SI.ENS() et MOYENNE.SI.ENS(). Je vous en ai déjà parlé dans ce billet.
Après avoir testé ces nouvelles fonctions, certains m’ont affirmé qu’ils allaient se passer de SOMMEPROD() qui, par une syntaxe détournée, permet de réaliser une somme ou un dénombrement de données selon plusieurs conditions, et qui ferait donc double emploi.
Je vais quelque peu nuancer ces propos, car les limites dans l’expression des critères de ces fonctions conditionnelles ne permettent pas toutes les fantaisies.
Démonstration…
Sur base du tableau suivant nommé Relevés, il est aisé d’utiliser SOMME.SI.ENS() pour réaliser la somme des montants relatifs au personnel féminin d’un service.
Si l’on souhaite à présent réaliser la somme des montants du service Achats un lundi, cela se corse avec SOMME.SI.ENS(), car il n’est pas possible d’exprimer le critère « date est un lundi » au sein de SOMME.SI.ENS(). La solution consiste alors à ajouter une colonne dans laquelle on détermine le jour de la semaine relatif à la date en colonne A, et à appuyer le critère sur cette nouvelle colonne. On imagine aisément que cette technique devient ingérable lorsque plusieurs critères complexes doivent être appliqués.
En utilisant SOMMEPROD() dans sa syntaxe détournée, l’expression du critère est possible.
PS: Si SOMMEPROD() vous est inconnue, n’hésitez pas à me le signaler, et j’écrirai un billet expliquant cette fonction très intéressante pour la gestion des données dans Excel.
1 Commentaire + Ajouter un commentaire
Articles récents
- Formation EXCEL: Analyse des données sans formules grâce aux tableaux croisés dynamiques
- Access : Sélectionner une valeur selon l’une ou l’autre de ses propriétés
- Références structurées: absolues ou relatives?
- Excel 2013, un relooking commercial ou une révolution copernicienne?
- Outook sur Exchange mutualisé
Bonjour,
j’aimerai savoir si il est possible d’utiliser une fonction Sommeprod pour remplacer une fonction somme(si.
J’utilise une fonction somme(si pour faire la somme de montants selon des critères (mois, date, type d’opération, etc..)
le problème de la formule est que le recalcule est très long surtout que ma base de donnée est de plus en plus importante.
voici ma formule
=SOMME(SI(INDIRECT(CONCATENER(« ‘Source « ;K$8; »‘!$D$2:$D$100000″);VRAI)=K$7;SI(INDIRECT(CONCATENER(« ‘Source « ;K$8; »‘!$G$2:$G$100000″);VRAI)>=K$4;SI(INDIRECT(CONCATENER(« ‘Source « ;K$8; »‘!$G$2:$G$100000″);VRAI)=K$3;(INDIRECT(CONCATENER(« ‘Source « ;K$8; »‘!$Q$2:$Q$100000″);VRAI))))))))/1000
elle est de type
SOMME(SI(INDIRECT(CONCATENER(« ‘Source « ;cellule contenant une expession;Matrice);VRAI)=valeur de condition;SI(INDIRECT(CONCATENER(« ‘Source « ;cellule contenant une expession;Matrice);VRAI)=valeur de condition;INDIRECT(CONCATENER(« ‘Source « ;cellule contenant une expession;Matrice);VRAI)=valeur de condition)
cette formule me permet donc de recherche des montants d’une colonne dans un autre onglet et de les additionner selon les critères que je souhaite.
Je precise que la formule fonctionne très bien. Mon probleme vient du fait que le calcule est long, pour 30 cellules à peut pret 15 secondes. Dans mes tableaux j’ai environ 300 cellules dans chaque onglet et plus d’une centaine d’onglet par fichier. Le temps de calcule est donc interminable.
J’aimerai donc savoir si la fonction sommeprod est calculée plus rapidement que la fonction somme(si.
En effet j’ai entendu dire que pour la fonction somme(si, excel recret virtuellement un tableau pour le recalcule ce qui est la cause de la lenteur du calcule.
Si une fonction sommeprod est possible, quelle serait l’écriture?
J’ai essayé de placer un sommeprod devant ma formule comme suit.
SOMMEPROD(SOMME(SI(INDIRECT(CONCATENER(« ‘Source « ;K$8; »‘!$D$2:$D$100000″);VRAI)=K$7;SI(INDIRECT(CONCATENER(« ‘Source « ;K$8; »‘!$G$2:$G$100000″);VRAI)>=K$4;SI(INDIRECT(CONCATENER(« ‘Source « ;K$8; »‘!$G$2:$G$100000″);VRAI)=K$3;(INDIRECT(CONCATENER(« ‘Source « ;K$8; »‘!$Q$2:$Q$100000″);VRAI)))))))))/1000
Pour la plupart des ligne les valeurs sont identiques mais pour certaines cellules les valeurs sont différentes.
je pensais egalement à une fonction sommesiens mais je ne connais rien de cette fonction.
espérant que vous pourrez m’éclairer,