Somme.si avec référence cellule variable

[rivette]

Bonjour à tous,

Je cherche en vain depuis de longues heures la solution à ce problème :

Pour un suivi de salaires mensuel, je saisis des données salariales dans une feuille présentée classiquement comme suit :
- une ligne correspond à un salarié
- une colonne correspond à Heures travail, Salaire brut, Charges patronales, etc...

A chaque mois correspond une feuille différente. Si la longueur de mes feuilles varie de mois en mois selon le nombre de salariés, leur présentation reste identique.

J'additionne dans une feuille "Total année" mes feuilles "Mois" au fur et à mesure de leur création dans l'année. J'utilise SOMME.SI avec comme critère le nom du salarié en colonne B (plage B5:B40 pour janvier par exemple).

En janvier 2009 : la formule de la 1ère case est
=SOMME.SI('1 09'!$B$5:$B$40;$B5;'1 09'!C$5:C$40)
Puis recopie sur le reste du tableau.

En février 2009 : la formule de la 1ère case devient
=SOMME.SI('1 09'!$B$5:$B$40;$B5;'1 09'!C$5:C$40)
+SOMME.SI('2 09'!$B$5:$B$48;$B5;'2 09'!C$5:C$48)
car il y a maintenant 44 salariés en février . Puis recopie sur le reste du tableau.

Objectif : afin d'éviter la recopie fastidieuse de ma formule chaque mois (car en réalité mes feuilles sont immenses), je souhaite saisir dès le départ mes douze SOMME.SI dans toutes mes cases alors que :
1) toutes les feuilles Mois n'existent pas encore pendant l'année
2) la longueur des feuilles varie

Solutions envisagées :

au 1) activer par un oui/non dans une case l'utilisation du SOMME.SI du mois concerné.
Je sais le faire.

au 2) rendre variable une partie de la référence de la cellule par l'emploi d'une case intermédiaire. Exemple :
Saisir 40 en case C1 pour janvier
Saisir 48 en case C2 pour février
Cela deviendrait :
=SOMME.SI('1 09'!$B$5:$B$&C1;$B5;1 09!C$5:C$&C1)
+SOMME.SI('2 09'!$B$5:$B$&C2;$B5;2 09!C$5:C$&C2)

J'ai essayé différentes combinaisons avec INDIRECT, &, ADRESSE, etc... sans succès... Et j'ignore tout de la pratique des macros...

PS : même raisonnement pour le nom de la feuille à saisir une fois seulement dans une case et à rendre variable lui aussi dans la formule.

De grâce, si une personne peut me venir en aide, je lui en serais gré. Puisse la solution de mon problème éclairer aussi d'autres internautes.

A tous, un grand MERCI !!

Michel

 

[systmd]

Re : Somme.si avec référence cellule variable

bonjour,

Un petit exemple (fichier) aurait été plus simple non?

En janvier 2009 : la formule de la 1ère case est
=SOMME.SI('1 09'!$B$5:$B$40;$B5;'1 09'!C$5:C$40)
Puis recopie sur le reste du tableau.

En février 2009 : la formule de la 1ère case devient
=SOMME.SI('1 09'!$B$5:$B$40;$B5;'1 09'!C$5:C$40)
+SOMME.SI('2 09'!$B$5:$B$48;$B5;'2 09'!C$5:C$48)

si la 1ere case s'appelle par exemple D1
la formule de la 1ère case devient en fevrier devient
= 1 09!D1+SOMME.SI('2 09'!$B$5:$B$48;$B5;'2 09'!C$5:C$48)

la formule de la 1ère case devient en Mars devient
= 2 09!D1+SOMME.SI('3 09'!$B$5:$B$48;$B5;'2 09'!C$5:C$48)
....

au final total annuel

=1 09!D1 + 2 09!D1 + .... + 12 09!D1

J'espère t'avoir aidé

 

[rivette]

Re : Somme.si avec référence cellule variable

Bonjour à vous Systmd et à tous,

Merci de m'avoir répondu.

Effectivement, un exemple chiffré est plus réaliste. Je viens de le préparer et ai expliqué dans la feuille "2009" ce que j'essaie de construire.

A noter :

1) J'ai saisi dans la feuille "2009" mes 12 SOMME.SI dans les cases J19 à L31
avec des références à des feuilles qui n'existent pas encore, car je ne sais pas encore faire autrement... Cela entraîne l'affichage du message "La mise à jour des liens a été désactivée".

2) Un salarié présent un mois sur un numéro de ligne peut l'être sur un autre numéro un autre mois ou ne plus l'être du tout...

J'échoue à rendre variable les chiffres de la référence d'une cellule.

Je pense que la solution passe par l'utilisation de la formule INDIRECT mais je ne parviens pas à la faire fonctionner...

N'hésitez pas aussi à me dire si la construction de mon tableau peut être envisagée autrement pour éviter ce problème. J'accepte toutes les critiques !

Michel

 

[systmd]

Re : Somme.si avec référence cellule variable

Bonjour

Il manque la PJ

 

[rivette]

Désolé de cette attente.

J'avais enregistré sous format xlsx et non xls. De ce fait, j'ai été obligé de raccourcir encore le tableau pour passer sous la barre fatidique des 50ko.

Mais le raisonnement reste le même.

Michel

 

[systmd]

Re : Somme.si avec référence cellule variable

Bon j'ai regardé,

Je doit être fatigué, mais franchement j'y perd mon latin
Tu fais reférence aux cases I19 à I24, tu veux dire H?
... Pour mars tu parle de la feuille "3 09"
...

Pour mars, $B$7 deviendrait $B$D4 et D$7 deviendrait D$D4 en se servant d'une case intermédiaire D4 saisie avec 7

???

Peut-être que quelqu'un comprend, mais pas moi, pour le moment.

Désespére pas, il y a bien une personne qui va passer par là.

 

[Modeste]

Re : Somme.si avec référence cellule variable

Bonjour rivette, systmd,

Si j'ai bien compris, au départ d'une solution proposée par BOISGONTIER, sur https://www.excel-downloads.com/threads/somme-si-dans-plusieurs-feuilles.125534/
Les noms de feuilles sont gérés dynamiquement (voir Insertion --> Nom) et la formule fait le reste. Le nombre de lignes utilisées dans chaque feuille n'a pas vraiment de raison d'être (... me semble-t-il !?)

 

[systmd]

Re : Somme.si avec référence cellule variable

Bonjour Modeste

J'étais loin de tout ça et en plus je ne maitrise pas du tout le SommeProd
Je m'y met mais ...

Bien vu, à Rivette de nous dire si ça lui convient et je crois que sur le sujet je vais te passer le relais

 

[BOISGONTIER]

Re : Somme.si avec référence cellule variable

Bonjour,

Voir PJ

=SOMMEPROD(SOMME.SI(INDIRECT("'"&NomFeuilles&"'!b4:b10");F19;INDIRECT("'"&NomFeuilles&"'!d4:d10")))

ou

=SOMMEPROD(SOMME.SI(INDIRECT("'"&LIGNE(INDIRECT("1:"&$J$18))&" 09'!b4:b10");F19;INDIRECT("'"&LIGNE(INDIRECT("1:"&$J$18))&" 09'!d4:d10")))

ou

=SOMMEPROD(SOMME.SI(INDIRECT("'"&nf&"'!b4:b10");F19;INDIRECT("'"&nf&"'!d4:d10")))

JB

 

[rivette]

Bonsoir à tous,

Merci pour toutes vos réponses et liens proposés !

Laissez-moi un peu de temps pour ingurgiter ses nouvelles formules, plus complexes que celles que j'ai l'habitude de manipuler, et je vous dirai si je m'en sors...

Encore un grand merci et bonne soirée à tous.

Michel

 

[rivette]

Bonsoir à tous,

Encore MERCI à vous tous qui m'avez répondu !!!

A Jean-Marc :
Votre macro fonctionne mais elle se heurte à mon ignorance totale de ce langage et serai incapable actuellement de me débrouiller pour le réutiliser dans mon tableau.
Petite remarque : elle ne cumule pas les différents salaires d'une personne sur une seule ligne (sans doute un "réglage" à faire)
Par contre, votre solution offre le grand avantage de dépasser le problème de la longueur variable des feuilles.

A Modeste et Boisgontier :
Vous parvenez tous les deux à une solution "pratiquable" par moi. J'en suis béat d'admiration, et cela va me permettre de progresser !!!

Permettez-moi tout de même deux remarques :

1) Dans la solution de Modeste, si je créée une nouvelle feuille, 8 09 par exemple après 3 09, cela ne fonctionne plus. Il faut respecter scrupuleusement l'ordre chronologique, et tout fonctionne parfaitement (il est vrai que c'est un détail, je chipote...)

2) Plus important : Votre formule a une référence de longueur figée par un nombre (b10 et d10 pour Boisgontier et $B$1000 et $D$1000 pour Modeste).

Or, supposons que je créée une nouvelle feuille avec 1200 salariés, la formule ne fonctionne plus.

La solution "basique" consiste à indiquer dans la formule un nombre tellement important que le calcul fonctionnera dans tous les cas.

Mais est-il possible de rendre, par une manoeuvre dont vous avez le secret, ce nombre, ou plutôt cette partie de référence de cellule, variable ? Et ce, exactement calé sur la longueur exacte de chaque feuille (solution idéale), ou au pire sur la plus longue de toutes.

Voilà pourquoi j'essayais, bien maladroitement, de transformer le $B$1000 en $B$quelquechosedevariable...

Encore une fois, votre formule me convient déjà formidablement.

Dans tous les cas, je vous remercie chaleureusement de venir en aide à tous les Béotiens dans mon genre.

Bonne soirée.

Michel

 

[BOISGONTIER]

Re : Somme.si avec référence cellule variable

Bonjour,

Voir PJ

JB

 

[Modeste]

Re : Somme.si avec référence cellule variable

Bonjour à tous,

Dans tous les cas, je vous remercie chaleureusement de venir en aide à tous les Béotiens dans mon genre.

Béotiens ... béotiens, comme tu y vas, rivette A bien regarder les solutions proposées au fur et à mesure, par JB, je te dirais bien que nous en sommes tous!
... Quand je pense que systmd envisageait de me passer le relais !?
P.S.: une petite pensée émue aussi pour Jean-Marcel (que tu as rebaptisé?)

 

[bertrand1202]

Re : Somme.si avec référence cellule variable

Bonjour Rivette, le Forum , et les participants à ce post.


Ayant essayé de reproduire la formule dernière ligne de J Boisgontier, je ne comprends pas le &e2(ça ok mais le " 09) puis le "*"

Comment peut on adapter cela quand ily a des feuilles avec des noms, a b c ou janv fev mars. ?

Si quelqu'un peut m'aider à comprendre je serai ravi car cela peut être très utile.
Dans ce cas , comment faire la liste nommée.? decaler (la feuillerecap! cellule nbval;;;colonne:colonne;1) ou autrment ?
Ce post me paraissant génial par toutes les réponses ,je cherche déjà à comprendre la partie Formules.

Merci à vous si quelqu'un peut m'expliquer.

Bonne journée

 

[BOISGONTIER]

Re : Somme.si avec référence cellule variable

>Pourquoi le 09?
Les feuilles sont nommées '1 09','2 09',...
>Pourquoi *?
Pour compter toutes les lignes (Nbval ne fonctionne pas)

-Si les feuilles sont nommées Janvier,Février,Mars,,,
-Nb de mois variable, nb de ligne variable

=MAX(NB.SI(INDIRECT("'"&TEXTE(DATE(2009;LIGNE(INDIRECT("1:"&$E$2));1);"mmmm")&"'!b4:b10000");"*"))+3

=SOMMEPROD(SOMME.SI(INDIRECT("'"&TEXTE(DATE(2009;LIGNE(INDIRECT("1:"&$E$2));1);"mmmm")&"'!b4:b"&$G$2);B4;INDIRECT("'"&TEXTE(DATE(2009;LIGNE(INDIRECT("1:"&$E$2));1);"mmmm")&"'!c4:c"&$G$2)))

Si nb mois fixe, peut être simplifié
Si nb lignes fixe, peut être simplifié


-Noms de feuilles non génériques: aa,bb,cc,...

=SOMMEPROD(SOMME.SI(INDIRECT("'"&DECALER(NomFeuilles;;;$E$3)&"'!b4:b1000");B4;INDIRECT("'"&DECALER(NomFeuilles;;;$E$3)&"'!c4:c1000")))

JB