Référence à une table avec Sommeprod

mutzik

XLDnaute Barbatruc

• 7 Février 2011

• #2

Re : Référence à une table avec Sommeprod

bonjour,

désolé, j'ai laisé ma boule de cristal à la maison, je ne vois aucune formule sommeprod dans ton fichier, en fait pas de formule du tout, et je ne sais pas ce qu'il faut faire

 

Vote positif 0 Vote négatif

N

nap

XLDnaute Occasionnel

• 7 Février 2011

• #3

Re : Référence à une table avec Sommeprod

Oups, c'était le mauvais fichier (avec les résultats uniquement) Désolé

 

Pièces jointes

• reference table sommeprod.xls

  17.5 KB · Affichages: 47
• reference table sommeprod.xls

  17.5 KB · Affichages: 53
• reference table sommeprod.xls

  17.5 KB · Affichages: 52

Vote positif 0 Vote négatif

CISCO

XLDnaute Barbatruc

• 7 Février 2011

• #4

Re : Référence à une table avec Sommeprod

Bonjour

Cf une posssibilité en pièce jointe, à adapter à ton fichier réel.

@ plus

 

Pièces jointes

• reference%20table%20sommeprod(1).xls

  22.5 KB · Affichages: 72

Vote positif 0 Vote négatif

Dugenou

XLDnaute Barbatruc

• 7 Février 2011

• #5

Re : Référence à une table avec Sommeprod

Salut à tous,

Bravo Cisco ! j'ai séché 2 heures sans avoir cette idée !!

 

Vote positif 0 Vote négatif

N

nap

XLDnaute Occasionnel

• 7 Février 2011

• #6

Re : Référence à une table avec Sommeprod

Merci,

Moi je crois que je vais sécher bien davantage pour comprendre et adapter. Je m'y attaque demain et je reviens forcément avec des questions !

Bonne soirée et encore merci

 

Vote positif 0 Vote négatif

CISCO

XLDnaute Barbatruc

• 8 Février 2011

• #7

Re : Référence à une table avec Sommeprod

Bonjour à tous, bonjour Dugenou (merci aussi pour le compliment), Nap

Une petite explication

En E15, on a :

Code:

SOMMEPROD(ESTNUM(EQUIV(E$4:E$10;DECALER(A$3;1;EQUIV(D15;A$3:C$3;0)-1;2);0))*(MOIS(F$4:F$10)=MOIS(E$13)))

EQUIV(D15;A$3:C$3;0) renvoie la position de D15 (Paris) dans la plage A$3:C$3

DECALER(A$3;1;EQUIV(D15;A$3:C$3;0)-1;2) renvoie la plage A4:A5 (2 cellules à la verticale à cause du 2), donc ici FED et TEI.

EQUIV(E$4:E$10;DECALER(A$3;1;EQUIV(D15;A$3:C$3;0)-1;2);0) renvoie la position des noms de la plage E$4:E$10 dans la plage A$3:C$3 et #NA si ces noms ne sont pas dans cette plage.

ESTNUM(EQUIV(E$4:E$10;DECALER(A$3;1;EQUIV(D15;A$3:C$3;0)-1;2);0)) renvoie VRAI si les noms de la plage E$4:E$10 sont dans la plage A$3:C$3, FAUX dans le cas contraire.

Après, ça fonctionne comme un SOMMEPROD normal, les VRAI étant transformés en 1, et les FAUX en 0, par le produit *.

@ plus

 

Vote positif 0 Vote négatif

N

nap

XLDnaute Occasionnel

• 8 Février 2011

• #8

Re : Référence à une table avec Sommeprod

Bonjour CISCO,

Tu es très didactique mais mon niveau est en dessous de cette formule. Je n'arrive pas à l'appliquer et pourtant je crois faire ce que tu explique.
Je laisse un exemple de mon échec.

PS :Je ne préoccupe pas encore des dates dans mon fichier.

 

Pièces jointes

• table et sommeprod.xls

  17 KB · Affichages: 47
• table et sommeprod.xls

  17 KB · Affichages: 47
• table et sommeprod.xls

  17 KB · Affichages: 46

Vote positif 0 Vote négatif

Dugenou

XLDnaute Barbatruc

• 8 Février 2011

• #9

Re : Référence à une table avec Sommeprod

Salut à tous,

Cisco, si je peux me permettre...
Nap : le pb dans ton dernier fichier c'est la condition sur la date avec >"01/01/2010" tu n'aura rien car le > avec du texte c'est pas top. Essaie de reprendre le mois ou alors mets ta date de référence dans une cellule que tu utilises ensuite dans ta formule.

Cordialement

 

Vote positif 0 Vote négatif

N

nap

XLDnaute Occasionnel

• 8 Février 2011

• #10

Re : Référence à une table avec Sommeprod

Bonjour Dugenou,

Je reconnais que mes dates ne sont pas top mais si je retire la notion de date, je suis toujours à 0

 

Vote positif 0 Vote négatif

Dugenou

XLDnaute Barbatruc

• 8 Février 2011

• #11

Re : Référence à une table avec Sommeprod

Re,

il y a un espace incongru après tes codes à 3 lettres dans la zone d14:d50
🙄

 

Vote positif 0 Vote négatif

N

nap

XLDnaute Occasionnel

• 9 Février 2011

• #12

Re : Référence à une table avec Sommeprod

Bonjour,

Je sais que mon fichier de base est bourré d'espace en tous genres. Donc dans mon fichier exemple, j'ai retiré les espaces mais toujours sans succès. Je suis un peu bloqué 😕

By the way, comment fait-on pour savoir qu'une cellule a un espace "incongru" ?

 

Pièces jointes

• table et sommeprod.xls

  21.5 KB · Affichages: 39
• table et sommeprod.xls

  21.5 KB · Affichages: 35
• table et sommeprod.xls

  21.5 KB · Affichages: 35

Vote positif 0 Vote négatif

Dugenou

XLDnaute Barbatruc

• 9 Février 2011

• #13

Re : Référence à une table avec Sommeprod

Bonjour,

dans ta formule le *1 final a sauté : quand tu le remets ça colle !

=SOMMEPROD(ESTNUM(EQUIV($C$14:$C$50;DECALER(A$3;1;EQUIV(E5;A$3😀$3;0)-1;8);0))*1)

Pour savoir s'il y a un espace après je n'ai pas d'autre méthode que éditer la cellule et cliquer derrière le texte ....
pour supprimer les espaces : comme tu as fait avec supprespace, ou bien convertir la cononne avec un nb de caractères fixe, ou bien édition, remplacer " " par rien.

Cordialement

 

Dernière édition: 9 Février 2011

Vote positif 0 Vote négatif

N

nap

XLDnaute Occasionnel

• 9 Février 2011

• #14

Re : Référence à une table avec Sommeprod

Re bonjour,

Merci j'ai enfin réussi à faire tourner la formule mais pour le*1, je n’avais pas vu (à quoi il sert ?)

Maintenant que la 1ere étape est solutionner, je veux compléter la formule avec les autres critères mais bien sure, # REF ou autres massages.

Je refais un fichier plus prêt de mon fichier de travail pour montrer mon problème.

Merci encore

 

Pièces jointes

• date et autres critères.xls

  35.5 KB · Affichages: 50
• date et autres critères.xls

  35.5 KB · Affichages: 48
• date et autres critères.xls

  35.5 KB · Affichages: 44

Vote positif 0 Vote négatif

M

Monique

Nous a quitté

Repose en paix

• 9 Février 2011

• #15

Re : Référence à une table avec Sommeprod

Bonjour,

Pas vu le dernier fichier.

Une formule matricielle (à valider par ctrl, maj et entrée) si on laisse les espaces dans les cellules de dates et de codes

SommeProd sinon

J’ai changé les dates, les montants, les codes aussi, je crois.
Format conditionnel pour vérifier plus facilement.

 

Pièces jointes

• TableEtSommeprodNap.zip

  8.2 KB · Affichages: 29

Vote positif 0 Vote négatif