Fonction SOMMEPROD combinée avec OU et ET
Bonjour,
Comme les réponses ne se bousculent pas, je donne ici une adaptation en BD** :
Quand on voit plus d'une centaine de formule comme ça :
=SOMME.SI.ENS('C1'!D:D;'C1'!B:B;">="&$D$19;'C1'!B:B;"<="&$H$19;'C1'!A:A;"="&A313)+SOMME.SI.ENS('C2'!D:D;'C2'!B:B;">="&$D$19;'C2'!B:B;"<="&$H$19;'C2'!A:A;"="&A313)+SOMME.SI.ENS('C3'!D:D;'C3'!B:B;">="&$D$19;'C3'!B:B;"<="&$H$19;'C3'!A:A;"="&A313)+SOMME.SI.ENS('C4'!D:D;'C4'!B:B;">="&$D$19;'C4'!B:B;"<="&$H$19;'C4'!A:A;"="&A313)+SOMME.SI.ENS('C5'!D:D;'C5'!B:B;">="&$D$19;'C5'!B:B;"<="&$H$19;'C5'!A:A;"="&A313)+SOMME.SI.ENS('C6'!D:D;'C6'!B:B;">="&$D$19;'C6'!B:B;"<="&$H$19;'C6'!A:A;"="&A313)+SOMME.SI.ENS('C7'!D:D;'C7'!B:B;">="&$D$19;'C7'!B:B;"<="&$H$19;'C7'!A:A;"="&A313)+SOMME.SI.ENS('C8'!D:D;'C8'!B:B;">="&$D$19;'C8'!B:B;"<="&$H$19;'C8'!A:A;"="&A313)+SOMME.SI.ENS('C9'!D:D;'C9'!B:B;">="&$D$19;'C9'!B:B;"<="&$H$19;'C9'!A:A;"="&A313)+SOMME.SI.ENS('C10'!D:D;'C10'!B:B;">="&$D$19;'C10'!B:B;"<="&$H$19;'C10'!A:A;"="&A313)+SOMME.SI.ENS('C11'!D:D;'C11'!B:B;">="&$D$19;'C11'!B:B;"<="&$H$19;'C11'!A:A;"="&A313)+SOMME.SI.ENS('C12'!D:D;'C12'!B:B;">="&$D$19;'C12'!B:B;"<="&$H$19;'C12'!A:A;"="&A313)+SOMME.SI.ENS('C13'!D:D;'C13'!B:B;">="&$D$19;'C13'!B:B;"<="&$H$19;'C13'!A:A;"="&A313)+SOMME.SI.ENS('C14'!D:D;'C14'!B:B;">="&$D$19;'C14'!B:B;"<="&$H$19;'C14'!A:A;"="&A313)+SOMME.SI.ENS('C15'!D:D;'C15'!B:B;">="&$D$19;'C15'!B:B;"<="&$H$19;'C15'!A:A;"="&A313)+SOMME.SI.ENS('C16'!D:D;'C16'!B:B;">="&$D$19;'C16'!B:B;"<="&$H$19;'C16'!A:A;"="&A313)+SOMME.SI.ENS('C17'!D:D;'C17'!B:B;">="&$D$19;'C17'!B:B;"<="&$H$19;'C17'!A:A;"="&A313)+SOMME.SI.ENS('C18'!D:D;'C18'!B:B;">="&$D$19;'C18'!B:B;"<="&$H$19;'C18'!A:A;"="&A313)+SOMME.SI.ENS('C19'!D:D;'C19'!B:B;">="&$D$19;'C19'!B:B;"<="&$H$19;'C19'!A:A;"="&A313)+SOMME.SI.ENS('C20'!D:D;'C20'!B:B;">="&$D$19;'C20'!B:B;"<="&$H$19;'C20'!A:A;"="&A313)+SOMME.SI.ENS('C21'!D:D;'C21'!B:B;">="&$D$19;'C21'!B:B;"<="&$H$19;'C21'!A:A;"="&A313)+SOMME.SI.ENS('C22'!D:D;'C22'!B:B;">="&$D$19;'C22'!B:B;"<="&$H$19;'C22'!A:A;"="&A313)+SOMME.SI.ENS('C23'!D:D;'C23'!B:B;">="&$D$19;'C23'!B:B;"<="&$H$19;'C23'!A:A;"="&A313)+SOMME.SI.ENS('C24'!D:D;'C24'!B:B;">="&$D$19;'C24'!B:B;"<="&$H$19;'C24'!A:A;"="&A313)+SOMME.SI.ENS('C25'!D:D;'C25'!B:B;">="&$D$19;'C25'!B:B;"<="&$H$19;'C25'!A:A;"="&A313)+SOMME.SI.ENS('C26'!D:D;'C26'!B:B;">="&$D$19;'C26'!B:B;"<="&$H$19;'C26'!A:A;"="&A313)+SOMME.SI.ENS('C27'!D:D;'C27'!B:B;">="&$D$19;'C27'!B:B;"<="&$H$19;'C27'!A:A;"="&A313)+SOMME.SI.ENS('C28'!D:D;'C28'!B:B;">="&$D$19;'C28'!B:B;"<="&$H$19;'C28'!A:A;"="&A313)+SOMME.SI.ENS('C29'!D:D;'C29'!B:B;">="&$D$19;'C29'!B:B;"<="&$H$19;'C29'!A:A;"="&A313)+SOMME.SI.ENS('C30'!D:D;'C30'!B:B;">="&$D$19;'C30'!B:B;"<="&$H$19;'C30'!A:A;"="&A313)plus quelques matricielle comme ça :
{=SI(SOMME('C1'!$D$3:$D$29999)=0;" ";SOMME(SI(('C1'!$A$3:$A$29999<>0)*('C1'!$A$3:$A$29999<>548)*('C1'!$A$3:$A$29999<>599)*('C1'!$A$3:$A$29999<>600)*('C1'!$B$3:$B$29999>=$D$19)*('C1'!$B$3:$B$29999<=$H$19);'C1'!$D$3:$D$29999)))}... moi j'hésite pas !
Remarque : Le fichier joint est bouclé sommairement. je me suis contenté de remplacer les formules par des BD** sans considérations esthétiques : J'en ai laissé trainer quelques unes dans la feuille "Décompte" alors qu'il eut-été plus clean de les mettre également dans la feuille "Calc" pour ne laisser figurer que les résultats.
De même j'ai laissé figurer toutes les requêtes dans la feuille "Calc" alors que seule la première est indispensable : Une petite boucle sur toutes les feuilles n'aurait demandé que quelques secondes à VBA pour sommer directement TOUSSA dans la feuille "Décompte".
Bon lecture !
A+
Bonjour Joseph, bonjour Galopin
Les réponses ne se bousculent pas car un micro qui chauffe trop le weekend n'est pas performant en semaine..., les neurones non plus...
Je vais regarder cela demain matin, tranquillement, les banques étant fermées le lundi .....
Bonne fin de weekend...
Cordialement
FINDRH
Bonjour galopin, FINDH
Bonjour le forum,
Oui, je sais. Je crois même me souvenir que pour mes formules SOMME.SI.ENS j'ai arrêté le nombre de comptes à 30, parce qu'au delà, excel me disant que les formules étaient trop longues!
Je vous remercie infiniment de l'aide que vous m'apportez.
Je pense avoir des réponses me permettant de poursuivre mon travail.
En tout cas j'ai de quoi m'inspirer (vos fichiers)
Je vais déjà commencer par transposer, puis tester avec les données des années précédentes.
En conséquence je vais mettre résolu pour mon sujet. (Ce qui m'empêche pas de rajouter d'autres remarques)
Encore merci.
Cordialement
Joseph
bonjour,
Pour conclure sur ce sujet... (en attendant la production de FINDRH
Je suppose qu'on ta imposé cette conception en multiple feuilles. C'est une conception qui était nécessaire avec les premières versions d'Excel.
En fait aujourd'hui, en particulier avec les BD** il serait bien plus intéressant de mettre tous les comptes sur la même feuille ! (avec juste une colonne supplémentaire pour différencier les comptes C1,C2...)
Au lieu d'avoir 30 feuilles ridicules et 150 formules interminables tu n'aurais plus qu'une feuille et la feuille de BD** ne comporterait qu'une seule batterie de requête... (pour les requêtes de bilan.
Les requêtes pour la situation des comptes étant gérées au moyen de la colonne supplémentaire. En rajoutant astucieusement la colonne "comptes" à droite de tes tableaux de requêtes tu n'as même pas besoin de rajouter une série dedans ta feuille de requêtes, (puisque tu as la possibilité de créer plusieurs extractions à partir de la même grille : Tu as juste à faire varier la zone de critères dans la formule)
Je ne sais pas si tes TCD sont capables de gérer 600 000 lignes sans inconvénient mais je sais à coup sur que les BD** les gèrent sans problème d'autant que là, tu divises par 30 le nombre de requêtes...
En résumé tu gères toutes tes requêtes les plus complexes en 300 lignes maximum et 8 colonnes !
En terme de temps de recalcul et de poids du fichier je t'explique même pas...
A+
Bonjour,
en écoutant (lisant) Galopin - que je salue - et en prenant le fichier de base du demandeur (du 6 octobre , de 27 lignes), j'ai testé un somme.si.ens et les formules bd de Galopin, j'arrive au même résultat (heureusement !) mais à la même vitesse l'un n'étant pas plus rapide que l'autre sur 500.000 lignes.
Je n'ai pas testé avec sommeprod ou tcd ou encore vba mais les fonctions BDxxx ne me semblent pas finalement plus rapide mais je suis tout ouvert à un essai proposé sur un grand nombre de lignes
D'où ma question: est ce vraiment intéressant, Galopin, d'utiliser les fonctions BD ? et as tu un exemple pour me montrer que c'est vraiment plus rapide; je ne me permets pas de douter de tes compétences largement plus élevées que les miennes, mais un exemple est toujours plus parlant
Merci
P.
(WIn 10 Excel2007-2016) Ram 24Gg
Bonjour à tous
Je ne m’étendrais pas sur la vitesse de traitement... je n'en suis pas encore là, mais je me concentre sur ce qui marche et que je comprends......
Suie au dernier envoi de joseph ( fichier 999..) j'ai automatisé la récup de ses infos de ce classeur vers des feuilles Cxx de son dossier de synthèse.
En feuille synthèse changer le nom du classeur met les chiffres à jour ( somme si ens avec indirect...
Cordialement
FINDRH
Bonjour à tous,
Quel travail formidable !
Alors déjà, tout à l'air de fonctionner.
Je vais tester sur des données existantes, puis adapter.
en supposant que copier suffise...
parce que vitesse de traitement et tout le toutim, c'est bien; mais n'oublions pas la vitesse de traitement du Joseph !!!
Mille et un merci à chacun de vous
Joseph
bonjour,
[patrick1957]
Malheureusement je n'ai plus de bases de données intéressantes à produire... donc je ne peux donnée une statistique valable sur le gain de temps. D'autant que de plus ce gain de temps est très variable selon l'expériences des uns et des autres à optimiser ses propres requêtes...
J'ai évacué depuis belle lurette quelques fichiers inutilement lourds. Et les quelques fichiers (trop petits) que je montre aujourd'hui ne peuvent prétendre à un gain de temps significatifs.
Ce dont je reste persuadé, pour l'avoir éprouvé : ces BD** permettent un gain de temps effectif dans la construction des requêtes :
Il ne faut pas une heure pour faire plusieurs centaines de requêtes différentes sur la base de SOMMEPROD, alors que la seule rédaction (et correction) de formules matricielles peut rapidement devenir scatologique...
Les BD** permettent des requêtes qui seraient impossible avec des formules quelconques ni même avec des TCD :
Je me rappelle un TDB, une des stats les plus spectaculaire du genre à une époque ou France Télelecom commercialisait de nombreux produits différents dont (entre autres) les télécartes et je travaillais sur une base de donnée nationale ou il était question de faire une stat par ville, par département, par région pour chaque produit... Bien que c'était encore sous Excel 5 (donc limité à 60 000 lignes) vu le nombre de colonnes c'était déjà monstrueux !
De plus le TDB ne se contentait pas de sommes, il fallait aussi calculer des moyennes, ratios...
Un des problèmes étant que la plupart des regroupements devait pouvoir faire l'objet d'un graphique instantané :
Le problème fut résolu en quelques heures au grand étonnement de mes interlocuteurs qui n'en croyaient pas leurs yeux...
Bien sur, comme il n'était pas question de mettre plusieurs dizaines (centaines ?) de graphiques dans ce classeur, je ne me suis pas privé des macros pour faire tourner les paramètres et afficher les résultats... Mais c'est un souvenir qui m'est resté et qui m'a bien servi de leçon !
Les curieux trouveront sous ce lien geficca v2.xlsm un petit exemple de ce genre de construction...
A+