Fonction SOMMEPROD combinée avec OU et ET

Bonjour le forum,

Je me tourne à nouveau vers vous pour une fonction SOMMEPROD que je n'arrive pas à écrire.

J'y suis depuis hier soir, et je ne vois vraiment plus de solution.

Mais peut-être faudrait-il passer par une autre fonction ?

Je joins un petit fichier avec les données.

Je voudrais faire le total dans 'Feuil1' A3 de la colonne D3:D27 de "C1"

• si le code en A3:A27 de la feuille "C1" = 548 ou 599 ou 600
• et que la date en B3:B27 de cette même feuille "C1" est égale ou supérieure au 01/01/2014 ('Feuil1' B1) et inférieure ou égale au 31/12/2014 ('Feuil1' E1)

Voilà mon tout dernier essai (qui aboutit au résultat #VALEUR!

J'ai déjà aussi scindé la formule en deux sans obtenir de résultat plus probant.

=SOMMEPROD(OU(('C1'!$A$3:$A$27=548);('C1'!$A$3:$A$27=599);('C1'!$A$3:$A$27=600));ET(OU(('C1'!$B$3:$B$27)>=$B$1;('C1'!$B$3:$B$27)<=$E$1))*('C1'!$D$3:$D$27))

Je remercie vivement quiconque saura m'aider

Joseph

Bonjour

ci joint une proposition avec somme.si.ens

j'ai nommé tes colonnes et supprimé les fusions de cellules

si tu pouvais passer le solde au dessus tu pourrai transformer ton tabelau en tableau dynamique avec mise à jour automatique des noms et taille des colonnes.

je continue à revoir ta formule avec somme.prod, le, ou ne me semble pas faire partie de cette syntaxe..

A tester

Cordialement

FINDRH

Bonjour,

Je plussoie vivement pour intervertir les lignes 1 et 2 de ton tableau ce qui permettrait de transformer l'ensemble en Tableau (genre base de donnée)

Ceci dit SOMMEPROD et SOMME.SI.ENS même combat : Moi je préfère les BD** surtout si tu as de très grands tableaux de chiffres les fonctions de Base de Donnée sont mes préférées : même si elles demandent un petit apprentissage elles sont les championnes toutes catégories des tableaux de bord complexes pour données volumineuses !

A+

Bonjour FINDH, le forum,

Ta proposition fonctionne très bien. Test probant !

Ta suggestion concernant un tableau dynamique m'intéresse fortement....sauf que niveau TCD, eh bien je m'y connais encore moins !!!

Je joins un 2e fichier avec le solde au-dessus (est ce que c'est comme ça que tu le pensais ?)

Maintenant, dans le fichier réel, il n'y a pas que l'onglet C1, mais C1, C2, etc. jusqu'à C30

Cela ne risque t'il pas de poser problème s'il y a les mêmes noms dans tous les onglets "C", ou au contraire le total se fera t'il automatiquement pour tous les onglets "C" ?

Merci

Joseph

Bonjour galopin, ravi de te "revoir"

Youah.

Si tu veux peut-être regarder le 2e fichier, c'est mieux ?

Donc, comme pour FINDH, le fichier réel comporte 30 comptes (C1 à C30)

Je ne sais pas quelle taille ils atteindront avec le temps, j'avais prévu 20.000 lignes pour chacun, sachant bien qu'il y aura beaucoup de lignes sur un compte courant et très peu sur un compte de dépôt.

Je vais essayer de me dépatouiller pour comprendre ta méthode....qui demande un apprentissage comment déjà ?

Ah oui, PETIT, c'est ça ?

Merci et à bientôt

Joseph

Je confirme ! L'occasion fait le larron...

J'ai souvent travaillé sur de très grandes bases de données nationales à une époque ou je ne pouvais pas me permettre de longues sieste pendant que le PC moulinait et les BD** m'ont bien souvent sauvé la mise.

Adossées à un peu de VBA et à quelques combos pour faire tourner tes requêtes, tu gagnes un temps considérable.

Bon il faut reconnaître que je n'étais pas très fortiche en fonctions... Mébon, je ne montre plus cela qu'à titre anecdotique... Un peu comme des pièces de musée.

Il reste que dans sur de gros coups, ce sont des monstres de puissance car elle sont implémentées depuis la naissance d'Excel donc dans un langage machine dépourvu de toute la lourdeur des interpréteurs d'interfaces évoluées... Je crois d'ailleurs savoir que ce sont elles qui sont utilisées à la base de la création des SOMMEPROD et autres joyeusetés...

Aujourd'hui, je fais parti des vieux dinosaures qui s'en souviennent encore mais il est parfois bon de le rappeler !

Non, non pas de problème avec les noms d'en-têtes, au contraire ça t'évitera d'avoir à te demander à chaque fois quel est l'en-tête.

Il suffit que dans chaque feuille chaque base soit référencée dans le Gestionnaire de nom (de préférence avec INDIRECT DECALER afin quelles recouvrent toujours exactement tous les enregistrements -en-têtes compris- ) ensuite c'est le rectangle de la requête lui-même qui est sollicité. Pour le nom des bases il est n'est pas interdit de trouver des noms astucieux qui seront facile à incrémenter (BaseC1, BaseC2... BaseC30 !) Quelle imagination... Merci galopin !

Après ça même les tableaux de bord les plus complexes te sont promis... les BD** autorisent même les caractères génériques (*?) et les champs calculés ! Mais pour cela je te laisse regarder l'aide de µsoft...

[EDIT] remplacé INDIRECT par DECALER...

A+

En phase avec Galopin, m^me sur l'aspect dinosaures...

Je te prépare un exemple sur la base de tes données

Au plus tard demain matin je te l'envoie, car les retraités ont encore du boulot

Cordialement

FINDRH

Galopin,

Ton discours mérite bien au moins une réponse personnelle.

C'est vraiment un plaisir de te lire.

Question dinosaure, j'en suis un. Mais plutôt un bon vieil herbivore lent et débonnaire (et avec peu de mémoire).

Rien à voir avec un tyrannosaure qui court dans tous les sens.

Pour dire que si je veux arriver à quelque chose cette année encore, il faudra que j'utilise quelque chose qui me semble moins inaccessible que ces bases qui ne demandent qu'un tout PETIT apprentissage (ça doit concerner les tyrannosaures donc, ceux qui courent dans tous les sens) et à des formules qui font référence à des cellules vides (c'est bien ça, non ? je n'ai rien vu en H14:L14 de ton fichier)

Je garde donc ce fichier bien au chaud pour étude, mais je reviens toutefois à la charge avec le mien (enfin le mien modifié par FINDH).

J'ai calculé en C1!G1 la dernière ligne non vide.

Est-il possible de modifier dans le gestionnaire de noms pour Code par exemple :

='C1'!$A$2:$A$27

='C1'!$A$2:$A$la valeur de G1

Merci pour tout

Joseph

Zut, j'ai oublié le fichier

Voici, voilà :

Bonsoir FINDH,

En continuant comme ça, on finira avec un troupeau !!

Moi aussi, la suite sera pour demain.

Je remets le fichiers, car j'ai calculé la dernière ligne non vide en C1!G1

Je me demandais s'il était possible dans le gestionnaire de noms, quand on détermine la référence du nom

de modifier ='C1'!$A$2:$A$27 par ='C1'!$A$2:$A$ la valeur de C1!G1

Je ne sais pas si c'est possible, mais en tout cas moi je me retrouve systématiquement avec #VALEUR!

Je viens de voir ton message. Merci beaucoup

Joseph

bonsoir,

la ligne 14 est vide car il n'y a pas de filtre : on Somme TOUT c'est pourquoi on obtient bien le résultat que tu as trouvé dans la feuille C1.

Pour le gestionnaire de nom, pas besoin de G1 : DECALER fait ça très bien tout seul ! voir pièce jointe.

Maintenant si tu insistes je te le fais aussi avec G1...

A+

Bonjour (soir)

si j'ai compris... moi je vais utiliser les filtres élaborés.

P.

bon...jour(nuit!)

Etonnante mutation génétique pour cette solution qui ne renie pas son ADN !

Bonjour les travailleurs nocturnes

ci joint une proposition qui fonctionne avec somme.si ens et indirect,

Dans la feuille synthèse à voir et vérifier, on change les paramètres de feuille et de date et cela fonctionne

J'ai rajouté une macro( bouton sur synthèse) pour créer les feuilles de base (C1 à CxxX) avec les tableaux et les formules crédit débit

Il serait intéressant d'avoir une entete de ces feuilles avec 3 4 lignes pour alimenter automatiquement ce classeur

A tester

Cordialement

FINDRH

Bonjour,

Une variation en BDSOMME avec juste une petite validation de donnée pour changer de feuille...

A+

Bonjour galopin, FINDH, Patrick, le forum

Quelle pléthore de propositions...je ne m'attendais pas à ça!

Les BD de galopin, j'adore, et l'onglet "Tables" dans le fichier de FINDH je le trouve génial.

J'ai enregistré tous vos fichiers pour pouvoir travailler dessus. (PS : les cellules vides, j'ai toujours pas compris)

Seulement voilà, moi je n'ai pas ce niveau.

J'voudrais ben, mais j'peux point comme le chantait Annie CORDY...

Et j'ai toujours préféré dire que je ne comprenais pas, plutôt que de faire semblant.

Donc entre le rêve de ce que vous me faites miroiter à travers vos exemples et le réel de mon (plutôt) mes fichiers, il y a un monde.

Avec des codes fixes, des onglets fixes, etc. Bref, rien que du basique. Du très basique.

Je vais donc dans un 1er temps adapter ce que j'arrive à faire : le SOMME.SI.ENS

Euh, galopin,

Pour le gestionnaire de nom, pas besoin de G1 : DECALER fait ça très bien tout seul ! voir pièce jointe.

Maintenant si tu insistes je te le fais aussi avec G1...

Insister n'est pas le mot que j'emploierai.

Mais si tu veux bien, moi je n'ai rien contre.

Au moins j'apprendrai la syntaxe.

Et vraiment un grand merci à tous.

Ca c'est de l'aide !!

Joseph

galopin01 a écrit :

bon...jour(nuit!)

Etonnante mutation génétique pour cette solution qui ne renie pas son ADN !

Pas compris ...

Tu veux expliquer ?

Bonjour Joseph67 et le forum

La pléthore de réponse correspond à l’Intérêt de ton sujet, qu'on a apparemment plutôt compris....

Pour boucler le travail il ne manque qu'un fichier source ( une partie...) pour t'automatiser totalement le travail, c'est à dire y récupérer les données (choix entre C1 à C30) et les mettre sur la bonne feuille, avec les indications au bon endroit, les bonnes formules, pour que tu n'aies plus qu'à modifier tes paramètres dans ta synthèse..... et avoir tous les résultats voulus.....

C'est un peu du chinois pour les novices, mais tu t'es attaqué à du sommeprod multifactoriel dont la syntaxe n'est pas toujours évidente, on comprend mieux somme.si.ens qui est plus limitée dans certains cas.

On peut documenter mieux les macros pour que tu comprennes le cheminement des opérations réalisées...

Prêt à en parler, mais pas en alsacien du Nord, je ne maîtrise que l'alsacien non technique du sud ... que je ne pratique plus très couramment dans ma région actuelle !

Cordialement

FINDRH

bonjour,

patrick1957

Tous les autres SOMMEPROD, SOMME.SI.ENS et filtres en tout genre ne sont jamais que des variations interfacées avec une sur-couche d'un langage plus récent qui ne fait probablement que reprendre les fonctions d'origine avec un habillage à la mode et une ergonomie plus intuitive...

Joseph67

Comme le dis très bien FINDRH toutes ces solutions ne sont en fait que des variations sur le thème des filtres :

Question 1. Tu as posée une question sur une requête ou (ligne 14) il n'y avait aucun filtre (la ligne est vide) donc la fonction renvoie le total général de la BD...

Question 2

Dans l'exemple que tu fournis tu demandes que tes plages nommées s'étendent avec la formule G1 (qui compte le nombre de lignes...) Dans le fichier joint en réponse (Essai_SOMMEPROD_2 VG.xlsx), j'ai modifié tes plages nommées sans tenir compte de G1 car c'est inutile de passer par une formule intermédiaire qui ne ferait qu'alourdir les formules. Ainsi nommées les plages sont maintenant dynamiques et suivent le nombre de lignes de ta base.

Pour éviter de noyer ce fil dans des digressions oiseuses, je m'abstiendrai d'essayer d'incorporer ce G1 qui me semble sans intérêt.

Toujours pour éviter d'alourdir ce fil, je me retire maintenant sur la pointe des pieds : Je voulais juste montrer cette solution particulièrement intéressante sur de très grosses bases de données (plusieurs centaines de milliers de lignes) et plusieurs centaines de requêtes. Comme je le dis souvent les BD** trouvent leur intérêt quand toutes les autres solutions commencent à s'écrouler...

Bonne continuation.

A+

@Galopin:

Je comprends mieux à présent et j'ignorais (ou j'avais un peu oublié) ce que tu décris pour les fonction BD* ...

Toujours intéressant d'apprendre ça, alors que j'étais persuadé que ces fonctions (parmi les plus anciennes sur les tableurs je pense) étaient obsolètes

P.

Bonjour à tous,

Galopin, merci d'avoir montré ces BD.

C'est un peu comme ces reportages sur les peoples aves les grandes maisons, les yachts etc...

Non pas que j'en voudrai un de yacht (même pas de baignoire pour le garer !), mais si une fois je pouvais en voir un de près, ça a l'air chouette.

Et tes BD c'est pareil. Je me dis waouh, un jour...

En tout cas, ça fout la patate pour continuer à apprendre.

A bientôt, j'espère

Joseph

Bonjour FINDH

Le forum,

Je veux bien mettre un fichier source, mais si je n'en copie que 2 feuilles sur les 15, j'obtiens des #REF partout et il devient carrément illisible. En effet, beaucoup d'informations sont reprises à partir de mon fichier "maitre" qui pilote les fichiers sources (ou clients) et dans lequel j'ai par exemple les clients avec leur adresse, les codes débit/crédit et leurs intitulés, etc...

Un fichier source en entier pèse lui environ 2500 Ko

Je ne vois pas trop comment je pourrai le faire "maigrir" d'avantage pour arriver aux 500 Ko possibles

A moins d'une possibilité que j'ignore

Cordialement

Joseph

Re bonjour

pour le fichier il suffit d'avoir;

- le vrai nom du classeur d'extraction et des feuilles si plusieurs feuilles

-toutes les entêtes des colonnes 'avec les lignes avant l'entete à l'extraction

un plus,, mettre en rouge les colonnes qui peuvent être supprimées

puis 5 lignes ( anonymisées ) remplies suffisent pour bâtir la macro,

ça ne sera pas bien lourd...

A ta dispo

Cordialement

FINDRH

Bonjour FINDRH,

Ouf,ça y est.

Donc voilà un fichier source (client)que j'ai zippé pour passer sous la barre des 500 Ko.

Les informations telles que les noms, prénoms, n° de code, intitulés de codes, etc... proviennent d'un fichier que j'ai appelé Directeur.

J'espère qu'il n'est pas nécessaire, parce que celui là pèse plus de 3000 Ko.

les fichiers clients portent comme nom un chiffre. Pour l'instant de 1000001 à 1000098.

Les fichiers comme le 9999996 sont ceux que j'utilise pour des essais.

Je ne pense pas retourner sur le PC ce soir.

Merci encore pour tout. Je n'en demandais pas tant, ça en devient presque gênant...

Joseph