Intégrer un nom de feuille variable (valeur d'une cellule) dans une formule SOMMEPROD

[Darko]

Bonjour,

Je cherche à améliorer un classeur excel que j’ai élaboré.

C’est un classeur commercial, avec une feuille par mois sur lesquelles sont entrées toutes les transactions du mois (achat/vente, prix, référence de l’article concerné).

Pour le moment j’ai également une feuille par mois qui me sert de synthèse mensuelle elle m’indique pour chaque type d’article : quantité achetée ou vendue, prix total, prix moyen.

Au lieu d’avoir une feuille par mois pour la synthèse, je voudrais avoir une feuille unique, dans laquelle je pourrais sélectionner le mois qui m’intéresse et la synthèse irait chercher les données dans la feuille qui correspond au mois choisi.

En gros, il faudrait que la valeur de cellule correspondant au mois choisi, soit reprise dans la formule en tant que nom de page.

Voici par exemple la formule que j’utilise actuellement, elle me sert à faire le cumul des achats du mois d’avril (nom de page) correspondant à l’article dont le nom figure en A2 de la feuille actuelle.

=SOMMEPROD((avril!$D$2:$D$9987=$A2)*(avril!$H$2:$H$9987="Buy")*(avril!$J$2:$J$9987))

Je me demande comment je pourrais remplacer dans cette formule le nom de page fixe (avril !) en variable qui correspond à la valeur d’une cellule de la page actuelle (le nom du mois tout simplement).

J’ai essayé de m’inspirer de la FAQ (https://www.excel-downloads.com/thr...s-references-structurees-des-tableaux.116187/) mais je n’ai pas bien compris.

Au passage, je me demande aussi comment je peux faire pour éviter de fixer un nombre de lignes arbitraire (ici 9987) en indiquant que la fonction SOMMEPROD devra être exécutée sur toute la feuille, quel que soit le nombre de lignes.

D’avance merci.

 

[Darko]

Re : Intégrer un nom de feuille variable (valeur d'une cellule) dans une formule SOMM

Je n'ai pas réussi à régler mon problème, alors je l'ai contourné, en rassemblant toutes les données qui étaient réparties dans une feuille par mois, en une seule feuille, à laquelle j'ai ajouté une colonne correspondant au mois.

Ce qui amène une 2e question : je souhaite faire une statistique mensuelle au moyen de SOMMEPROD en saisissant le numéro du mois concerné, comment faire pour que si aucun numéro de mois n'est saisi, la statistique s'applique à l'ensemble des lignes, quel que soit le mois concerné.

 

[jeanpierre]

Re : Intégrer un nom de feuille variable (valeur d'une cellule) dans une formule SOMM

Bonjour Darko,

Avec un test en début de formule :

Si(ta cellule est vide;alors ta formule actuelle sans la partie =le mois;sinon ta formule actuelle)

Sinon, reviens avec exemple.

Bonne journée.

Jean-Pierre

 

[Monique]

Re : Intégrer un nom de feuille variable (valeur d'une cellule) dans une formule SOMM

Bonjour,

Le 1er message
Avec Indirect()
Le nom de l’onglet en B1 :

=SOMMEPROD((INDIRECT(B$1&"!D2:D9987")=$A2)*(INDIRECT(B$1&"!H2:H9987")="Buy")*(INDIRECT(B$1&"!J2:J9987")))

Comme ça c’est mieux :

=SOMMEPROD((INDIRECT(B$1&"!D2:D9987")=$A3)*(INDIRECT(B$1&"!H2:H9987")="Buy");INDIRECT(B$1&"!J2:J9987"))

 

[Darko]

Re : Intégrer un nom de feuille variable (valeur d'une cellule) dans une formule SOMM

Bonjour et merci beaucoup pour vos réponses !

Jeanpierre

Tu as vu juste, c'est la solution que j'ai utilisé : en fait, pour être complet j'ai deux "filtres" : le mois et la région. Je me suis servi des fonctions SI & ET, avec 3 imbrications :

1- SI mois est vide ET région est vide il affiche les opérations toutes régions et tous mois;

2- SI mois est vide, il affiche les opérations tous mois pour la région sélectionnée;

3- SI région est vide, il affiche les opérations toutes régions pour le mois sélectionné;

dans le cas où les conditions ci-dessus sont fausses, alors il affiche les opérations du mois et de la région sélectionnés.

Au final la formule fait 3 kilomètres de long et j'ai du la décomposer pour arriver à la pondre sans me tromper, mais ça marche (et ça rame un peu) !

La voici pour info (une saisie vide ou 0 renvoie à tous les mois, une saisie "All" renvoie à toutes les régions, une colonne mois a été ajoutée par rapport à la formule de mon premier post et j'ai allongé le nb de ligne puisque j'ai rassemblé toutes les opérations sur une seule feuille) .

=SI(ET($B$1<1;$E$1="All");(SOMMEPROD((Transactions!$D$2:$D$50000=$A3)*(Transactions!$H$2:$H$50000="Buy")*(Transactions!$C$2:$C$50000)));SI($B$1<1;(SOMMEPROD((Transactions!$D$2:$D$50000=$A3)*(Transactions!$H$2:$H$50000="Buy")*(Transactions!$K$2:$K$50000=$E$1)*(Transactions!$C$2:$C$50000)));SI($E$1="All";(SOMMEPROD((Transactions!$D$2:$D$50000=$A3)*(Transactions!$H$2:$H$50000="Buy")*(Transactions!$L$2:$L$50000=$B$1)*(Transactions!$C$2:$C$50000)));(SOMMEPROD((Transactions!$D$2:$D$50000=$A3)*(Transactions!$H$2:$H$50000="Buy")*(Transactions!$L$2:$L$50000=$B$1)*(Transactions!$K$2:$K$50000=$E$1)*(Transactions!$C$2:$C$50000))))))
Ouf

Monique

Ta formule, ou plutôt tes formules fonctionnent parfaitement !

N'ayant toujours rien compris à la logique de la syntaxe de la fonction INDIRECT, je comprends pas pourquoi les 2 formules fonctionnent, mais j'avoue qu'après la migraine d'hier, je vais les récupérer sans me poser trop de questions !

Cette solution me permet donc de revenir à ma première option : celle d'éclater les transactions sur plusieurs feuilles on fonction des mois, super !