Microsoft 365 Somme avec plusieurs critères

[olivier777]

Hello,
alors moi j'ai une question
Si dans ma formule je veux mettre le contenu d'une cellule et non pas une valeur figée
=SOMME(SOMME.SI.ENS(B1:B11;A1:A11;{"A";"B"}))

Au lieu de mettre "A" et "B" je veux avoir les valeur se trouvant dans 2 cellules
si par exemple j'ai une donnée dans la cellule D2 et une autre en D3, je ne peux pas faire un truc du style =SOMME(SOMME.SI.ENS(B1:B11;A1:A11;{D2;D3}))
Comment faire ?
Merci

 

[olivier777]

Bonjour Chris,
ce fichier est globalement représentatif
Pour les combinaisons de filtres par exemple, les sommes à faire,..
c'est d'avantage qu'il peut y avoir de nouvelles choses à ajouter, des nouvelles colonnes, pour faire des tableaux sur d'autres choses (des quantités, des couts,... )
je ne connais pas tout à ce stade et j'essaye de trouver la meilleure façon de l'aborder.
en fait, ce qu'il me manque c'est juste de comprendre comment je peux ajouter 1 ou plusieurs colonnes dans le fichier source afin qu'il soit pris en compte correctement.

Si tu as la possibilité de m'aider sur ce point ce serait cool.

En m'expliquant comment je peux rajouter et prendre en compte de nouvelles colonnes dans le modèle, ou en rajoutant par exemple une 20aine de colonnes supplémentaires (pour être tranquille), qui serait à traiter comme celle des "Montant": (Montant2, Montant3,...)
Comme ça, lorsque j'aurai de nouvelles données en colonnes qui viendront enrichir ma base, je pourrais les prendre en compte (Éventuellement en modifiant le nom de la colonne dans PoweQuery ?)

Qu'en penses-tu ?

Et encore merci pour le temps que tu as consacré à ma demande

 

[Staple1600]

Bonjour le fil, olivier777, et les vieux de la vieille

[aparté]
Depuis le message#9, (le fil est arrivé désormais à #54 !), personne (parmi les vieux de la vielle )pour suggérer à olivier777 que l'usage c'est de poser sa question dans sa discussion (en la créant donc) plutôt que de "squatter" un fil initié par un autre membre du forum (ici ElsaG en avril 2016)?
Étonnant, non ?

Etant un vieux de la vielle, je m'y suis collé alors

NB: Etrange également, la présence dans ce fil d'un "Liker fou" qui like quasiment toutes les réponses

PS: J'ai pris soin:
1) de mettre des guillemets
2) d'ajouter des emoticones (signalant donc un ton amicalement badin avec une pointe d'ironie)
3) et enfin de signaler que ceci est un aparté.
Donc en théorie, avec ces précautions, je devrais être à l'abri de réactions véhémentes à mon endroit
[/aparté]

 

[chris]

Bonjour à tous

PowerQuery est un requêteur, il a donc une logique base de données
Et comme en base de données, la conception c'est 80% du boulot et la réalisation 20%
(Dans un tableur comme Excel c'est d'ailleurs une pratique qui devrait aussi présider : réfléchir, analyser, avant de créer...)

Un cahier des charges non finalisé et évolutif n'est pas le bon point de départ surtout avec un projet de 30 000 lignes où il faut optimiser
20 colonnes ocazou de 30 000 lignes cela fait 6 00 000 données....

Les requêtes se servent des noms des colonnes et de leur type.
Pas d'une adresse genre bataille navale comme le tableur.
Les actions de la requête vont donc agir sur telle ou telles colonnes, unitairement ou simultanément.

Donc des colonnes d'avances dont on ne sait ni le nom définitif, ni le type, ni l'usage qui devra en être fait... il faudrait modifier chaque ligne quasiment de la requête...

Salut l'agrafe : oui je me suis aussi posé la question du pourquoi s'accrocher à un vieux fil, mais une fois que c'est parti comme cela c'est difficile de repartir sur un autre fil neuf. Il le faire dès le 1er ou 2ème post

 

[CISCO]

Bonjour

Bonjour le fil, olivier777, et les vieux de la vieille

[aparté]

[/aparté]

Et voui, tu as bien raison. Perso, j'ai pensé que cela serait réglé en 3 coups de cuillère, et donc... n'ai pas fait la remarque...
Si David pouvait créer un nouveau fil, et y déposer tous les posts depuis le #9 compris, cela serait pas mal...

@ plus

 

[olivier777]

Un cahier des charges non finalisé et évolutif n'est pas le bon point de départ surtout avec un projet de 30 000 lignes où il faut optimiser
20 colonnes ocazou de 30 000 lignes cela fait 6 00 000 données....

Je comprends ton point de vue Chris et je suis navré de ne pas avoir présenter un cahier des charges plus abouti.

Pouvoir modifier, corriger, faire évoluer les choses dans un projet cela devient normal maintenant
Cela permet de valider l'intérêt et surtout le besoin dans la durée.
Cela permet également de pouvoir livrer des choses régulièrement, de manière agile.

se servir d'Excel avec une logique de base de données, c'est bien, mais si 6M de données fait peur alors ce n'est pas vraiment une conception de BDD

Enfin, les colonnes "ocazou" sont toutes les mêmes que celle Montant.
ce peut être des frais, des montants ht, des tarifs spéciaux,... mais toujours la même le même type

Ps : je suis navré de m'être incrusté sur ce fil et j'aurais du procéder différemment.

Bien à vous

 

[olivier777]

Bonjour olivier777, excfl, le forum,

Voici un essai en pièce jointe.
Les formules utilisées se trouvent dans les celulles en vert

Edit : bonjour CISCO, nos messages se croisés
Je viens de contôler votre fichier, votre formule renvoie 0 lorsque les 3 types sont décochés !

Cordialement

Bonjour,
la solution d'Amilo est peut être plus simple à utiliser pour moi
Il manque dans la formule la prise en compte du dernier filtre ("Agglomeration" : ville hors ville)
je ne sais pas si c'est compliqué à faire dans la formule en cours


Merci
(dois-je créer un nouveau fil ? après la réponse à ce dernier message je ne polluerai plus le fil, promis )

 

[CISCO]

Bonsoir

Sans prendre en compte le critère "Agglomération", est-ce que tu as fait un test avec quelques milliers de lignes avec la formule d'Amilo ? Ce n'est pas évident que cela soit si rapide que ça?

@ plus

 

[Amilo]

Bonsoir à tous, bonsoir CISCO,

C'est exactement la question que je me suis posée,
J'avais déjà fait le test en mettant 100 000 lignes et sans le critère "Agglomération", le résultat est instantané comme pour le fichier initial à 20 lignes
Par contre j'ai transformé la plage source en une table

olivier777, ayant passé une journée de télétravail interminable, je regarderai éventuellement demain à tête reposée pour l'intégration du critère "Agglomération" mais la formule risque d'être très longue et sans rien présager sur la rapidité de la formule !!

Il y a peut-être plus court à faire avec une formule non matricielle, mais l'idéal étant du VBA.
Pour Power query, je n'ai pas testé et je ne préfère pas me lancer dans cette solution pour notamment les raisons évoquées par chris

Edit : pour l'ouverture d'un nouveau fil, je pense au stade où on est, c'est peut-être trop tard et plus trop nécessaire.
A vous de voir...pour peut-être attiser la curiosité et un soutien plus large, notamment en VBA.
Le message est en tout cas passé sur ce fil.

Cordialement

 

[olivier777]

Hello à tous !
merci beaucoup à tous pour le temps que vous consacrez à ma demande
cela fait vraiment plaisir
et j'ai appris pas mal de choses grâce à vous tous (formules matricielles, initiation au power Query, formules un peu plus complexes,...)

CISCO, tes formules fonctionnent très bien.
Le seul souci est que cela prend beaucoup de temps lors des recalculs (jusqu'à 9, 10 secondes après un clic sur une case à cocher)
ces délais ne semblent donc pas être compatibles avec l'utilisation attendue

chris, ta solution semble fonctionner, et je te remercie encore pour le temps que tu as consacré à ma demande incomplète (qui doit l'être dans le cas de la conception du modèle de données)

Amilo, je confirme qu'avec les 2 premiers critères ( donc 5 cases à cocher possibles) les résultats sont instantanés, même avec 40000 lignes dans la base
si cela pouvait en être de même avec la prise en compte de la colonne agglomération alors ce serait le top extrême !

ps :
oups, j'ai oublié dans le fichier de rajouter les autres types de formules qui seraient à mettre en place
(Par exemple, dans le fichier joint, les résultats par semaine, donc rajout d'une condition supplémentaire pour le calcul, ça pourrait être par mois, par année, par jour pourquoi pas)
C'est ça qui je l'impression, pénalise fortement les formules matricielles, la démultiplication des formules sur beaucoup de cellules....


Après résolution de ce sujet, je vais avoir d'autres petites questions à vous poser.
Je ne manquerai pas alors de créer un nouveau fil et valider que ma demande est assez complète )

Dans l'attente de vous lire,

Bien à vous, Stay Safe !

 

[Amilo]

Bonjour olivier777,

J'ai passé plusieurs heures sur le dernier fichier et je galère vraiment..
Les formules sont beaucoup plus longues et la moindre erreur ne pardonne pas.
Désolé, je crains de ne pouvoir aller jusqu'au bout.

Cordialement

 

[olivier777]

Hello Amilo,
aïe !
Je suis désolé pour tout ce temps passé.
Je ne sais pas comment résoudre du coup ce souci
Je dois présenter une solution la semaine prochaine
Si jamais une idée lumineuse te vient n'hésite pas ;-)
Encore merci pour le temps passé

olivier

 

[CISCO]

Bonsoir

Comme j'ai des difficultés pour comprendre la formule proposée par Amilo, j'ai repris la formule du fichier sept, de mon post #30, en donnant dans le gestionnaire, des noms aux conditions, cond A, condB, condC, et mis plus de 22000 lignes.

cond A =R$20:R$21+(R$20=R$21)*(R$22+R$23+R$24+R$31+R$32)

condB =R$31:R$32+(R$31=R$32)*(R$20+R$21+R$22+R$23+R$24)

condC=R$22:R$24+(R$22=R$23)*(R$23=R$24)*(R$20+R$21+R$31+R$32)

La formule dans M19 devient alors, en matriciel,

SOMME(ESTNUM(EQUIV(A2:A22011;SI(condA;P20:P21);0)*EQUIV(B2:B22011;SI(condB;P31:P32);0)*EQUIV(C2:C22011;SI(condC;P22:P24);0))*D2:D22011)

ou encore, pas en matriciel

SOMMEPROD(ESTNUM(EQUIV(A2:A22011;SI(condA;P20:P21);0)*EQUIV(B2:B22011;SI(condB;P31:P32);0)*EQUIV(C2:C22011;SI(condC;P22:P24);0))*D2:D22011)

Et le calcul est presque instantané chez moi dans les deux cas !!!

Il manque la condition sur les dates et d'autres choses, mais bon...

Le fichier correspondant (1,5 Mo) ne passe pas sur le forum, même si je ne laisse que 500 lignes dessus!

@ plus

 

[olivier777]

Hello
Merci CISCO
Merci
je suis en train de regarder tout ça
en revanche je n'arrive pas à faire fonctionner la formule non matricielle
(mais je me suis peut être trompé)
Je vais refaire un fichier résumant les différentes méthodes / formules
sauf celle de chris car je ne parviendrai pas à initier un Power Query

 

[Staple1600]

Bonjour le fil

•>olivier777
Il serait peut-être temps de suivre ton idée, non?

Je ne manquerai pas alors de créer un nouveau fil et valider que ma demande est assez complète )

Avant que ce fil entame sa cinquième page.
Cela permettra d'avoir une lecture plus fluide et recentrée sur ta problématique.
Et surtout ce sera ton fil discussion...

 

[Amilo]

Bonjour à tous, bonjour Staple1600, CISCO

Certainement mon dernier post pour ce fil

Sinon, pourquoi ne pas faire avec un TCD et des segments (voir pièce jointe) ?
J'ai personnalisé les segments mais vous pouvez aller plus loin dans les options
Les Items sélectionnés restent en vert et les autres en blanc,
Les Items qui ne peuvent pas être sélectionnés ont une couleur rouge au survol de la souris, modifier la couleur de la police aussi pour ces derniers...etc
Vous pouvez personnaliser tout cela comme bon vous semble.

Vous pouvez aussi faire aouter un bouton avec un code VBA pour désélectionner l'ensemble des filtres. Vous pourrez le demander dans une autre discussion si cela vous intéresse.

Idem pour le TCD, si vous pourrez toujours demander à le personnaliser, comme afficher ou non les semaines sans valeur…etc

Vous pourrez aussi ajouter 2 ou 3 cases à cocher avec du VBA pour masque/afficher les Segments si vous préférez.

Pas certain que cela réponde à votre demande mais l'intérêt est que c'est rapide et facile pour la maintenance, pas de formules complexes.

Cordialement