Somme avec condition sur une plage appartenant à un autre classeur

[delphinew]

Bonjour,

j'ai fait une recherche mais je n'ai pas trouvé la réponse à ma question.
J'ai deux fichiers excel distincts (et rangés dans des dossiers différents) : 2013 et 2014.
Dans chacun de ces fichiers, j'ai un tableau croisé dynamique qui me reprend les CA hebdomadaires réalisés. L'onglet porte le même nom dans chacun de ces fichiers et le tableau est positionné au même endroit. Ce que je cherche à faire, c'est la somme des CA hebdomadaires de 2013 pour toutes les semaines passées de 2014. C'est-à-dire, si j'ai les 8 premières semaines de 2014, je voudrais le total des 8 premières semaines de 2013, et ainsi de suite à chaque fois qu'une semaine de plus se passe (incrémentation automatique).
Le tableau est ainsi fait :
colonne A : S1, S2, S3, ...
colonne C : les chiffres d'affaires correspondant
dans une cellule en G, je voudrais rajouter mon total à date de l'année précédente.
j'ai essayé de faire ceci : {=+SOMME(SI('[Sell out hebdomadaire 2013.xlsx]Synthèse sem'!$A$4:$A$55=A4:A13;'[Sell out hebdomadaire 2013.xlsx]Synthèse sem'!$C$4:$C$55;0))} en calcul matriciel donc, où de A4 à A55 j'ai mes 52 semaines de 2013, mais cela ne fonctionne pas. Le résultat est #N/A (Excel trouve bien les premières semaines, mais pas les suivantes, d'où #N/A). Si j'intervertis la condition (2014 = 2013), Excel me donne le CA total de 2013 et non simplement la sommes des semaines communes. Ce qu'il me faudrait c'est quelque chose qui fasse "somme (Ci) où si Ai dans le fichier de 2013 est inclue dans la plage de mes semaines de 2014, alors Ci, sinon Ci = 0" pour i variant de 4 à 55.

Merci beaucoup pour votre aide !

 

[Gen Rose]

Re : Somme avec condition sur une plage appartenant à un autre classeur

Bonjour delphinew,
Le Forum

S.v.p. poster les 2 fichiers ( enlever les données confidentielles et rafraîchir les TCD).

@ te relire,

 

[delphinew]

Re : Somme avec condition sur une plage appartenant à un autre classeur

Bonjour,

j'ai un fichier qui fait 10Mo (2013) et l'autre 3Mo (2014), alors j'ai copié les données en dur sur un autre fichier excel.

Merci !

 

[JBOBO]

Re : Somme avec condition sur une plage appartenant à un autre classeur

Bonjour,

Pas du tout sur d'avoir compris mais peut-etre :

=SOMMEPROD(NON(ESTNA((INDIRECT("Feuil1!$A$4:$A$"&EQUIV("Total général";A:A;0)-1)=[2013.xlsx]Feuil1!$A$4:$A$55)))*([2013.xlsx]Feuil1!$C$4:$C$55))

 

[Gen Rose]

Re : Somme avec condition sur une plage appartenant à un autre classeur

Re,

J'ai du ajouter une colonne factice au tableau 2013 afin qu'il soit pareil au tableau 2014.
J'ai mis tes 2 tableau en forme de Tableau.
J'ai ensuite utilisé l'assistant de tableau croisé dynamique;
choisir Plage de feuille avec calcul et étiquettes, suivant;
Plusieurs (création manuelle), suivant;
Il faut sélectionner le premier Tableau avec l'entête et faire ajouter; ensuite sélectionner le 2e puis faire ajouter;
suivant, Nouvelle feuille.

Cela va consolider tes 2 tableaux en un seul TCD; tu obtiens alors une fusion.

Lorsque tu ajoutes des données à ton Tableau 2014, tu n'as qu'à Actualiser(clic droit; Actualiser) le TCD et il va tout ajuster avec les nouvelles données.

Si tu veux que ça soit par semaine, évidemment, il te faut ajouter une colonne semaine, ou date dans tes tableaux.

Tu peux ensuite trier les semaines que tu veux voir seulement.

Voir p.j. pour exemple; le TCD de consolidation est dans le fichier 2014, mais il pourrait tout aussi bien être dans une nouveau classeur.

Cdt,

 

[Gen Rose]

Re : Somme avec condition sur une plage appartenant à un autre classeur

Re,

Il me viens à l'idée que S1, S2 sont les semaines; simplement faire un tri pour vois les semaines voulues.

Bonjour JBOBO

 

[Gen Rose]

Re : Somme avec condition sur une plage appartenant à un autre classeur

Re,

Nouvelle propo ajustée.

- Considérant que S1, S2 etc sont les numéros de semaines, il vaut mieux mettre 1, 2 ,3 etc si l'on veut les voir en ordre croissant dans le TCD;

- J'ai nommé le tableau 2014; la sélection manuelle a ce problème de ne pas s'ajuster si on ajoute des données.

De cette façon, lorsque tu ajouteras des lignes au tableau 2014, le rafraîchissement du TCD va inorporer les nouvelles données.

Par contre, pour les zones dans les autres classeurs, on ne peux pas les nommer. Si tu ajoutes des lignes au tableau 2013 par exemple, il te faudra retourner dans la création du TCD avec l'assistant, reculer à l'étape de sélection des zones et ajuster cette dernière.

J'ai filtré les 12 premières semaines sur le TCD (suivant la progression 2014).

Cdt,

 

[delphinew]

Re : Somme avec condition sur une plage appartenant à un autre classeur

Bonjour,

JBOBO : la formule fonctionne nickel merci. Je n'aurais jamais pensé à utiliser une sommeprod qui pour moi traduit une somme de x[SUB]i[/SUB]y[SUB]i[/SUB]z[SUB]i[/SUB] (et ainsi de suite). Pourquoi d'ailleurs (pour ma culture personnelle et comprendre la formule) ? J'ai beaucoup de mal avec la fonction indirect aussi...

Genevieve78 : non car je ne cherche pas à additionner les semaines des 2 années, mais bien à comparer le cumul des semaines à un instant t.

Merci beaucoup en tous cas !!!

 

[Gen Rose]

Re : Somme avec condition sur une plage appartenant à un autre classeur

Ah, bien c'est possible aussi, en ajoutant une colonne année, par exemple.

@ plus

 

[JBOBO]

Re : Somme avec condition sur une plage appartenant à un autre classeur

re,

En fait j'ai utiliser sommeprod car c'est une formule matricielle qui n'as pas besoin d'etre valider par Ctrl+maj+entree. et j'ai utilisé indirect pour que la plage des semaines prises en compte s'incremente automatiquement et le non(estna()) pour ne pas prendre en compte les valeurs d'erreurs. peut-etre plus comprehensible avec l'audit de formule (evaluation).

Bonjour genevieve78

 

[delphinew]

Re : Somme avec condition sur une plage appartenant à un autre classeur

Bonsoir JBOBO,

oui j'ai fait l'évaluation de la formule. En fait Excel transforme VRAI en 1 et FAUX en 0, d'où la possibilité du calcul matriciel, c'est ça ?

 

[JBOBO]

Re : Somme avec condition sur une plage appartenant à un autre classeur

RE,

Oui tout à fait. le "estna" transforme les erreurs en vrai(1) qui sont ensuite transformé en faux(0) grace à la fonction "non" qui inverse la valeur logique (vrai devient faux et faux devient vrai).