Discussion :

[niko69]

Bonjour,

J'ai essayé la solution proposée dans cette vieille discussion pour un SOMME.SI.ENS devant utiliser un critère OU un autre.

La solution proposée fonctionne parfaitement si on veut plusieurs choix dans un seul des critères (dans le fichier exemple 2 mois, 1 période).
Cependant, lorsque l'on souhaite plusieurs choix dans plus d'un critère, cela ne fonctionne plus. Dans le fichier d'exemple (ci joint), j'ai essayé de faire une somme.si.ens avec mois 1 et 2, période A et B.

J'ai trouvé une solution avec sommeprod. Dans cet exemple, cela fait le taf, mais sur un gros fichier, c'est beaucoup plus long à calculer que le somme.si.ens... J'ai un fichier où j'ai pour le moment un seul critère où je sélectionne plusieurs choix, le somme.si.ens met environ 2 secondes à se calculer tandis que le sommeprod met plus de 8 secondes. Je cherche une solution pour permettre un choix multiple dans un second critère en gardant un somme.si.ens pour optimiser le temps de calcul.

J'espère que c'était assez clair

Merci.

SOMMESIENS VARIABLE MULTICRITERE.xlsx

[ARTURO83]

Bonjour,
Pour la période A seule:
=SOMME.SI.ENS($C$2:$C$16;$B$2:$B$16;G1;$D$2:$D$16;G3)+SOMME.SI.ENS($C$2:$C$16;$B$2:$B$16;G2;$D$2:$D$16;G3)

Pour les périodes A et B ensembles:
=SOMME.SI.ENS(Feuil1!$C$2:$C$16;Feuil1!$B$2:$B$16;G1;Feuil1!$D$2:$D$16;G3)+SOMME.SI.ENS(Feuil1!$C$2:$C$16;Feuil1!$B$2:$B$16;G2;Feuil1!$D$2:$D$16;G3)+SOMME.SI.ENS(Feuil1!$C$2:$C$16;Feuil1!$B$2:$B$16;G1;Feuil1!$D$2:$D$16;G4)+SOMME.SI.ENS(Feuil1!$C$2:$C$16;Feuil1!$B$2:$B$16;G2;Feuil1!$D$2:$D$16;G4)

Si ça vous convient
Cdlt

[niko69]

Bonjour,

Malheureusement cette solution n'est pas satisfaisante car dans cet exemple, j'ai uniquement 4 possibilités, mais dans mon cas concret, j'en ai beaucoup plus (7 pour mon critère 1 et 9 pour mon critère 2), et 7-8 critères dans mon somme.si.ens.

Merci de votre contribution

[ARTURO83]

Alors il ne reste plus qu'à faire une fonction personnalisée en vba,

[Pierre Fauconnier]

Salut.

J'ai essayé avec un million de lignes (sur seulement trois colonnes), et un critère du type ET(OU(mois=1;mois=2);OU(période="A";période="B")) se calcule en +/- 1 seconde.

J'ai essayé BDSOMME, plus lourd à mettre en place mais aussi plus lent.

Une solution serait de calculer le critère dans une colonne supplémentaire pour chaque ligne, et de calculer un SOMME.SI.ENS sur ce seul critère. Le temps de calcul peut alors être réduit puisque, à valeurs égales, le VRAI/FAUX n'est calculé qu'une fois par ligne, et non à chaque recalcul de la matricielle (ou de SOMME.SI.ENS).

Ca permet aussi de manipuler "plus facilement" les critères avec les ET et OU habituels d'Excel plutôt que d'utiliser l'arithmétique booléenne avec * et +, mais cela coûte une colonne de calcul.

Outre le fait que le VBA devrait être modifié si les critères venaient à l'être, une fonction perso ne serait pas plus rapide ici puisqu'il faudrait boucler sur les éléments d'un tableau

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
Function Calcul(Mois1, Mois2, Periode1, Periode2) As Double
  Dim t
  Dim i As Long
  Dim Value As Double
 
  t = (Range("a2:c1000000"))
  For i = 1 To UBound(t, 1)
    If (t(i, 1) = Mois1 Or t(i, 1) = Mois2) And (t(i, 2) = Periode1 Or t(i, 2) = "B") Then Value = Value + t(i, 3)
  Next i
  Calcul = Value
End Function

[78chris]

Bonjour

Calcul rapide aussi chez moi, par contre SOMME.SI en matriciel ne donne pas les mêmes résultats que SOMMEPROD.

A priori il fait (G1 et G3) ou (G2 et G4)...

Pour ma part je ferai un TCD avec juste le CA et deux segments !

[Pierre Fauconnier]

Salut Chris

Je n'avais pas regardé son fichier et de mon côté j'ai fait (1 ou 2) et (A ou B)... Je ne sais pas si Excel est optimisé sur le OU. Si oui, ça pourrait modifier le temps de traitement puisque si la première opérande du OU est VRAI, alors on ne calculerait pas la seconde. Autrement dit, si c'est 1, alors on ne teste pas si c'est 2.

Faudrait tester...

[niko69]

Merci pour vos suggestions.

J'ai contourné mon problème (solution qui ne me satisfait pas entièrement, mais des fois il faut faire des concessions !)

J'ai ajouté une colonne qui concatène mes deux critères dans ma feuille de données...En fait dans mes 70 onglets de données (c'est pour a que cette solution ne me plait pas entièrement, je pense au futur de l'outil que je construit quand il faudra le mettre à jour avec de nouvelles données).

Du coup j'ai pu utiliser la formule {somme(somme.si.ens(...))}

Merci.

[78chris]

Re

Tu as vu ma proposition de TCD : ce serait mille fois plus souple et sûr...

Tu as quelle version d'Excel ? Possible avec 2016 d'utiliser PowerQuery intégré...

[Pierre Fauconnier]

En fonction de ta version d'Excel (toujours intéressant de la mentionner), tu pourrais utiliser des outils très puissants pour analyser tes données, notamment POWER QUERY, POWER PiVOT (même déjà un simple TCD)

Ces outils utilisent un autre langage de formules que celui d'Excel, mais sont très puissants, par leur capacités d'analyse d'abord, mais aussi par les possibilités de regroupement de données en provenances de sources diverses (plusieurs onglets, plusieurs classeurs, mais aussi tout ce que tu peux récupérer comme données en fonction des drivers installés sur ta machine - fichiers texte, Access, SQL, Oracle, ...). Ces outils sont disponibles selon différentes façons en de puis XL2010, sont gratuits et proviennent de Microsoft.

Baser une analyse sur 70 onglets ne me semble pas réaliste comme approche.

[Pierre Fauconnier]

Chris, on s'est croisés...

J'ajouterai, par rapport au TCD, que l'on peut aussi en lire les données par une simple formule, ce qui permet d'utiliser le TCD comme outil de calcul temporaire, que l'on peut alors ramener dans un tableau de bord plus formel et plus esthétique. Et là, les calculs sont quasi instantanés, même sur de très grandes tables.

[niko69]

En fait PowerPivot ne fonctionne pas sur mon poste (et pour que l'informatique trouve pourquoi c'est long...et je n'ai pas d'accès admin sur ma machine)

J'utilise pas mal de TCD dans mon quotidien mais dans ce cas cette solution me semble plus pertinente (aux vues des utilisateurs finaux)

mon somme.si.ens ne va piocher que dans un seul onglet, en fonction d'un paramètre renseigné par l'utilisateur. J'ai créé 70 onglets car j'ai près de 3 millions de lignes de données.

[Pierre Fauconnier]

Il ne faut dès lors pas s'étonner d'un certain temps de traitement.

Tu ne renseignes toujours pas ta versions d'Excel. Comme je doute que tu travailles sur une XL2003 (j'ose espérer une version plus récente), tu pourrais déjà travailler sur moins d'onglets (chaque feuille Excel depuis XL2007 contient 1 048 576 lignes).

Après, la solution dépendra évidemment de la finalité de l'outil, mais d'une façon générale, contourner la non-installation d'un outil sous de faux prétextes ne pourra amener qu'à des solutions moins pertinentes, performantes, professionnelles.

Si tu arrives à persuader ton boss de la pertinence d'installer un outil, l'IT ne devrait pas pouvoir bloquer (je sais que c'est hélas en partie utopique ).

[niko69]

Je bosse sur 2013.

En fait j'ai créé 70 onglets en fonction d'une nomenclature interne, ce qui me permet également de ne pas faire mon somme.si.ens matriciel sur 1 000 000 de lignes, mais uniquement sur les plages renseignées.

Le problème c'est que si PowerPivot venait à fonctionner chez moi mais que l'utilisateur a la même soucis avec son pc, ca foire.

En tout cas le détournement de la colonne concaténée fait le taf !

Merci de votre temps !