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

 

[bertrand1202]

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

Bonjour Jacques


Merci de ton explication, je teste et éventuellement je reviendrai.

Cette méthode me paraît géniale, encore faut il bien la comprendre et l'assimiler.

Merci de ton aide . Cette fonction sommeprod somme si indirect m'évitera de faire des sommeprod sur chaque feuille et donc allégerl le traitement .

Peut on dans cette fonction faire références à trois paramètres :

Exemple:en lgne chantier, en colonne 2: type travaux et ventilé par salarié MrA Mr B MR C de manière à obtenir par chantier et type de travaux le temps passé par chacun des salariés?


Encore merci de ton explication . J'ai du mal avec cette fonction.

 

[systmd]

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

Bonjour à tous

... Quand je pense que systmd envisageait de me passer le relais !?

Heureusement , quand même que vous avez tous repris le relais sinon RIVETTE etait mal partie avec moi
Bon courage

 

[BOISGONTIER]

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

>Peut on dans cette fonction faire références à trois paramètres :

-Somme.SI() n'accepte qu'une condition.
-Pour plusieurs conditions, il faut procéder autrement

Voir exemple en PJ

=SOMMEPROD((T(DECALER(INDIRECT(TEXTE(DATE(2009;COLONNE(A:L);1);"mmmm")&"!a4");LIGNE($1:$1000)-1;0))=$A4)*
(T(DECALER(INDIRECT(TEXTE(DATE(2009;COLONNE(A:L);1);"mmmm")&"!b4");LIGNE($1:$1000)-1;0))=B$4)*
(N(DECALER(INDIRECT(TEXTE(DATE(2009;COLONNE(A:L);1);"mmmm")&"!c4");LIGNE($1:$1000)-1;0))))


JB

 

[bertrand1202]

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

Bonsoir le forum, bonsoir Jacques


Encore merci pour toutes tes explications. Je continue sur cette fonction , c'est un réel plus .

Merci encore à tous qui nous permettez de progresser.
Bonne soirée

 

[rivette]

Bonjour à tous,

Comment ne pas vous dire MERCI, à vous tous et particulièrement à Boisgontier !

1) Toutes mes excuses "exceliennes" à Jean-Marcel pour ce changement de nom involontaire...

Votre solution par macro représente, je pense, l'étape ultime de développement vers laquelle je dois tendre... Mais elle m'oblige à apprendre à courir avant de savoir trottiner... D'autant plus que le tableau que j'essaie de mettre en place est utilisé par d'autres encore moins expérimentés que moi... (oui, c'est possible ! )

2) C'est pourquoi je préfère la solution de Boisgontier dont le langage m'est plus immédiatement accessible :

"Cher Maître" Boisgontier, vous nous démontrez qu'il est possible de rendre fluctuante la référence de la dernière ligne. Fantastique !

Mon tableau ayant une vocation budgétaire avec répartitions analytiques, je préfère m'orienter vers un développement de votre solution Mat3D24 qui offre une souplesse maximale, à savoir un tableau de bord permettant le choix exact des mois souhaités (janvier, mars, juillet par exemple) plutôt qu'une incrémentation automatique comme développé pour Bertrand1202.

Par contre, dans "sa" solution, le choix de la consolidation devient individualisé par personne ! Cela offre une souplesse très tentante également. Tout dépend de la vocation du tableau.

NB : déplacer le $ de B$5 en $B5 pour permettre la recopie de la ligne

Dernière îdée : En combinant les 2 solutions, il serait possible de choisir son type de consolidation !

- Soit une consolidation globale par un tableau de bord qui s'imposerait à tous avec l'exact choix des mois (janvier, mars, juillet par exemple comme dans la présentation Mat3D24) et ce, quel que soit le mois choisi dans la case individualisée du salarié

- Soit une consolidation individualisée (comme dans la présentation Mat3DNomFeuillesAutoV4) avec neutralisation du tableau de bord général par un choix oui/non par exemple

Et nous obtiendrions un tableau génial ? Ou plutôt VOUS nous obtiendriez un tableau génial.

Solution que nous pourrions transposer dans nos cas réels (Bertrand1202, moi ou d'autres) et reproposer à tous les internautes par la suite.

Avec toute ma gratitude.

Michel

PS : sans oublier le fait que les feuilles mensuelles sont créées à mesure du déroulement de l'année.