Somme en fonction de plusieurs variables

[Logan-Kyles]

Bonjour tout le monde 🙂

Je viens vous demander un peu d'aide pour la réalisation de mon outil de gestion d'intermittence v3 avec l'espoir de pouvoir enfin proposer une version à d'autres intermittents pour qu'ils y voient plus clair 🙂

Mon problème est le suivant je voudrais calculer un salaire journalier brut en fonction de la date du jour travaillé, d'un salaire de référence (qui change suivant la période de travail) et du type de journée (coefficient multiplicateur de salaire)

J'ai les données suivantes

Page "Annee"
- colonne B "jour" comportant chaque jour du mois
- colonne C "heures" contenant un menu déroulant du type de journées
- colonne D "salaire" case d'arrivée de mon calcul

Page "Parametres"
- colonne B "date" qui comporte la date de changement de salaire
- colonne C "salaire" qui comporte la nouvelle valeur du salaire
- colonne F "coef.salaire" qui contient le facteur qui permet de passer du salaire normal au salaire majoré

Pas mal de variables donc, histoire d'être plus clair je vais "traduire" ce que j'aimerai faire comme fonction

D5 = Parametres!C? (ou C? est déterminé en fonction de Annee!B?) * Parametres!F? (ou F? est déterminé par Annee!C?)


Désolé ce n'est pas très clair tel quel, j'ai donc joint un fichier pour aider à me comprendre.

Merci d'avance pour votre aide 🙂

 

[Calvus]

Re : Somme en fonction de plusieurs variables

Bonsoir,

En D5

=SIERREUR(SOMMEPROD((RECHERCHEV(C5;Parametres!$D$5:$F$10;3;0)*RECHERCHEV($B$2;Parametres!$B$5:$C$7;2)));"")

A copier vers le bas.

J'ai ajouté une colonne dans la feuille Parametres

Regarde si ça te convient.

Je joins le fichier.

A+

 

[Logan-Kyles]

Re : Somme en fonction de plusieurs variables

Bonjour 🙂

Merci beaucoup pour ton aide déjà 😉

Ta fonction (que j'ai un peu eu du mal à comprendre je l'avoue ^^) fonctionne très bien mais la variable "changement de salaire" est actuellement traitée au mois et non à la journée. Dans le régime intermittent les augmentations peuvent avoir lieu dans le mois en cours. Est-il possible de chercher jj/mm/aaaa au lieu de mm/aaaa ?

Au passage il y a une chose qui m'échappe concernant le premier RECHERCHEV

=SIERREUR(SOMMEPROD((RECHERCHEV(C5;Parametres!$D$5:$F$10;3;0)*RECHERCHEV($B$2;Parametres!$B$5:$C$7;2)));"")

Tu utilises 4 arguments mais je ne connais cette fonction qu'avec 3 arguments, pourrais-tu m'expliquer l'intérêt du 4ème ?

 

[Calvus]

Re : Somme en fonction de plusieurs variables

Bonjour,

Voilà, c'est modifié. Il suffisait de modifier le format dans la cellule B2.

Ensuite, aide excel :

RECHERCHEV(valeur_cherchée;table_matrice;no_index_col;valeur_proche)

valeur_proche représente une valeur logique indiquant si vous souhaitez que la fonction RECHERCHEV recherche une valeur exacte ou voisine de celle que vous avez spécifiée.

Si cet argument est VRAI ou omis, une donnée exacte ou proche est renvoyée. Si aucune valeur exacte n'est trouvée, la valeur immédiatement inférieure à valeur_cherchée est renvoyée.
Les valeurs de la première colonne de table_matrice doivent être classées en ordre croissant ; sans cela, il se peut que RECHRCHEV ne renvoie pas la bonne valeur. Pour plus d'informations, consultez la rubrique Trier des données.

Si cet argument est FAUX, RECHERCHEH recherche exclusivement une correspondance exacte. Dans ce cas, il n'est pas indispensable que les valeurs de la première colonne de table_matrice soient triées. Si plusieurs valeurs de la première colonne de table_matrice correspondent à valeur_cherchée, c'est la première valeur trouvée qui est utilisée. Si aucune valeur ne correspond, la valeur d'erreur #N/A est renvoyée.


J'ai supprimé le zéro qui ne servait à rien dans ce cas là.
Egalement supprimé le Sommprod, qui n'était pas non plus utile, suite à une remarque pertinente.

Voilà, j'espère que c'est plus clair.

Bonne journée

 

[Calvus]

Re : Somme en fonction de plusieurs variables

Fichier modifié pour fonctionner sous excel 2003.

 

[Logan-Kyles]

Re : Somme en fonction de plusieurs variables

Merci pour l'explication. Par contre la modification ne semble pas fonctionner, si je modifie les date d'augmentation au 01/07/2010, 04/07/2010 et 08/07/2010 le salaire indiqué pour les journées est identique quel que soit la journée (et sa valeur est celle de l'augmentation du 08/07/2010).

Vu que le problème pourrait venir du fait que j'utilise Excel 2007 et que j'ai exporté en xls je te joins le fichier en xlsx.

Merci encore pour ton aide 🙂

 

[Calvus]

Re : Somme en fonction de plusieurs variables

Re,

Regarde si ça va maintenant. J'ai fait plusieurs tests, mais n'ai peut être pas vu quelque chose.

Dis moi.

A+

PS: tu es intermittent du spectacle donc ? Comme moi alors 😉

 

[Logan-Kyles]

Re : Somme en fonction de plusieurs variables

Ca fonctionne parfaitement je te remercie 🙂

En effet je suis intermittent depuis un an et j'ai mis au point un fichier excel qui a bien très fonctionné cette année mais je voudrais le simplifier et rajouter quelques nouvelles fonctions pour le mettre à disposition d'autre intermittents qui ont du mal avec tous ces chiffres et calculs 🙂

 

[Logan-Kyles]

Re : Somme en fonction de plusieurs variables

Désolé je vais te déranger à nouveau, je voudrais pousser un peu le concept pour bien assimiler la fonction RECHERCHEV. J'aimerai maintenant que tout fonctionne, chercher le coefficient en fonction d'une date (de manière à pouvoir mettre le coefficient à jour sans modifier les salaires antérieure à la date de mise à jour du coef).

Mon seul problème est que je ne sais pas comment faire une recherche dans une recherche (chercher dans un premier temps J,W,WS ..etc.. dans une plage puis retourner la valeur de la colonne 4 en fonction de la date dans la colonne 3)

Je te joins un nouveau fichier où se trouve le nouveau format de mon tableau de coefficient, si tu peux m'aider je te devrai une reconnaissance éternelle (oui je suis allé voir toy story 3 🙂)

 

[Logan-Kyles]

Re : Somme en fonction de plusieurs variables

petit up je n'ai pas encore trouvé de solution 🙁

 

[Tibo]

Re : Somme en fonction de plusieurs variables

Bonjour Logan, salut Calvus,

Le souci dans ton fichier est la fusion des cellules sur l'onglet Paramètres en colonne F.

Ca fait joli, c'est plus présentable, mais ça complique nettement l'exploitation d'un tel tableau.

Je te propose une formule matricielle qui, probablement, doit pouvoir être simplifiée :

en E5 :

=SI(C5="";"";INDEX(Parametres!$C$5:$C$25;EQUIV(B5;Parametres!$B$5:$B$25))*
INDEX(DECALER(Parametres!$E$4;EQUIV(C5;Parametres!$E$5:$E$25;0);3;MIN(SI((
Parametres!$E$5:$E$25<>"")*LIGNE($1:$21)>EQUIV(C5;Parametres!$E$5:$E$25;0);
(Parametres!$E$5:$E$25<>"")*LIGNE($1:$21)))-EQUIV(C5;Parametres!$E$5:$E$25;
0));EQUIV(B5;DECALER(Parametres!$E$4;EQUIV(C5;Parametres!$E$5:$E$25;0);2;
MIN(SI((Parametres!$E$5:$E$25<>"")*LIGNE($1:$21)>EQUIV(C5;Parametres!$E$5:
$E$25;0);(Parametres!$E$5:$E$25<>"")*LIGNE($1:$21)))-EQUIV(C5;Parametres!
$E$5:$E$25;0)))))

Formule matricielle à valider par CTRL + MAJ + ENTREE

Voir le fichier joint avec la formule.

@+

 

[Calvus]

Re : Somme en fonction de plusieurs variables

Bonjour Logan-Kyles, bonjour Tibo,

Tibo, j'attendais secrètement que tu viennes à ma rescousse, c'est fait 😉

J'ai essayé je ne sais combien de formules depuis hier soir, mais j'ai tourné autour toujours confronté à des #N/A.

J'ai réussi à aider jusque là, mais mes compétences s'arrêtent ici pour l'instant.

Ca fait plaisir de voir qu'on est surveillés par les dieux, comme ceux de l'Olympe.

Bonne journée

 

[Logan-Kyles]

Re : Somme en fonction de plusieurs variables

alors comment dire ... oh mon dieu 😱

ah oui la forcément je risquais pas de trouver tout seul 🙄

merci infiniment pour votre aide (Tibo et Calvus), je vais tout de même vous embêter un peu plus, tu me disais Tibo que ma mise en page "Ca fait joli, c'est plus présentable, mais ça complique nettement l'exploitation d'un tel tableau." c'est visiblement la raison pour laquelle je n'ai pas réussi à me sortir de mes formules, du coup est-ce qu'il existe une manière plus simple de faire cette opération si je revois ma copie de mise en page ? (car j'aimerai pour le moment utiliser des fonctions que je comprends et que je peux modifier par la suite, je t'avoue que ta formule a l'air super fonctionnelle mais j'ai peur de ne pas réussir à modifier quoi que ce soit sans tout foutre en l'air 🙄 )

merci encore pour le temps que vous m'accordez et votre patience, il faudrait plus de gens comme vous sur terre (et dans les administrations 😀)

 

[Tibo]

Re : Somme en fonction de plusieurs variables

Bonjour,

Une solution en évitant la fusion de cellules sur la feuille Paramètres_2, avec une formule également matricielle, mais nettement plus simple :

=SI(C5="";"";RECHERCHEV(B5;SI(Paramètres_2!$E$5:$E$25=C5;SI(Paramètres_2!
$G$5:$G$25<>"";Paramètres_2!$G$5:$H$25));2)*RECHERCHEV(B5;Parametres!
$B$5:$C$25;2))

Formule matricielle à valider par CTRL + MAJ + ENTREE

Voir le fichier joint

@+

 

[Logan-Kyles]

Re : Somme en fonction de plusieurs variables

Merci encore, cette fonction est plus simple a assimiler je pense que je vais travailler avec 😉

Encore merci pour votre aide 🙂