Microsoft 365 Tableau suivi de billeterie d'un CSE

[LAETI-TOINOU]

Bonjour à toutes et à tous,

Je fais une nouvelle fois appel à vous, les experts, pour m'aider sur un nouveau sujet, qui est le suivant :

Le CSE dont je fais partie (et dont je suis secrétaire) va proposer d'ici peu de la billetterie à ses salariés, dont je devrais en assurer le suivi :

1/ Le CSE achète ses billets à une centrale à un prix XXX qu'il revend aux salariés, selon leurs demandes (donc un prix inférieur au prix public).

2/ En plus de vendre des billets à tarif préférentiel, le CSE accorde à chaque salarié une remise annuelle de 50€ sur l'ensemble de sa billetterie, selon un barème qui pourrait être le 5€ de remise pour un billet acheté entre 10 et 20€, 10€ de remise pour un billet acheté entre 21 et 50€.... Ces fourchettes ne sont qu'à titre d'exemple, il me faudra donc pouvoir les modifier, une fois le document finalisé.
Il y a donc 2 critères importants dont il faut tenir compte :
- l'ancienneté qui doit être égale à 12 mois mini
- le montant total de remise accordée sur une année : 50€

3/ Pour le bon suivi de ma billetterie, je dois pouvoir faire un support contenant :

• l'ensemble de la billetterie achetée par l'ensemble des salariés comprenant : nom, prénom, date de commande, billet pour...., quantité, prix, total.
• l'ensemble de la billetterie achetée par chaque salarié comprenant nom, prénom, date d'entrée dans l'entreprise, date de commande, billet pour...., quantité, prix, total, total remise obtenue, avec une formule de calcul qui comprenne que le salarié doit avoir 12 mois d'ancienneté mini, et tant que les 50€ de remise du salarié ne sont pas atteints, le salarié bénéficie de la remise, au delà des 50€, il paye le prix du billet au prix que le CSE l'achète à la centrale).
• l'ensemble de la billetterie par thème : Parc Astérix, Patinoire, Contrôle Technique, concerts....

Je vous joins donc le document que j'ai commencé à mettre en forme

Onglet SUIVI ANNUEL COMMANDES SALARIES
Tableau dans lequel je saisis chaque demande.
Avec une colonne "Participation CSE" dans laquelle je dois indique O ou N pour accorder ou non la remise ou M si je dois indiquer le montant "manuellement" pour ne pas dépasser les 50€, car je ne sais pas faire autrement. C'est cette formule qui me pose un réel problème.

Onglet BILLETTERIE
1 Tableau dans lequel je note les billets que nous pouvons revendre, le prix d'achat à la centrale (bien évidemment, j'ai mis des tarifs qui ne reflètent pas la réalité, c'est juste pour la préparation du document), et le prix remisé qui pourra être accordé aux salariés, si les 50€ de remise annuelle ne sont pas atteints.
1 Tableau dans lequel j'indique les fourchettes de prix et le montant de la remise allouée.

Onglet RECAP PAR SALARIE
1 TCD (fonction que j'ai découvert il y a peu de temps et dont je ne connais que les bases), à moins qu'il y ait une autre solution.

Onglet RECAP PAR BILLET
1 TCD, à moins qu'il y ait une autre solution.

Onglet GRAPH PAR BILLET
1 graphique qui permet de voir quelles sont les demandes les plus importantes en matière de billetterie.

Onglet PARTICP CSE - SALARIE
1 TCD, à moins qu'il y ait une autre solution, pour connaitre le montant total des remises accordées à chaque salariés, dans la mesure où c'est 50€ maxi.

Onglet LISTE DU PERSONNEL
1 Tableau dans lequel j'indique le nom des salarié, date d'entrée dans l'entreprise, ainsi qu'une colonne participation annuelle (50€ pour une ancienneté de 12 mois mini dans la société,).

J'espère avoir été suffisamment précise dans la présentation de ma demande.
Avec une dernière précision : Je ne sais pas utiliser le VBA, en dehors d'une macro basique.


Merci d'avance pour toute l'aide que vous voudrez bien m'apporter. Car ce n'est vraiment pas évident quand on a peut de connaissance sur le logiciel. Je passe des heures devant mon écran, alors que pour certains d'entre vous, ce ne sera qu'un jeu d'enfant.

Merci, merci...

Bonne journée.


Laëtitia

 

[herve62]

Bonsoir
Long à lire ton laïus !!!
1er pas : la formule pour 50€ 1 an ancienneté : SI(("01/01/2022" - [@[DATE D''EMBAUCHE]])>365;"50,00 €";"")
le @Xxx se met tout seul si dans l'écriture de la formule tu clique en C

TCD : pas mon truc du tout !!! moi c'est le VBA

 

[LAETI-TOINOU]

Bonjour Hervé62, bonjour tout le monde.

Merci pour ce début d'info.

Effectivement mon laïus est un peu long, mais je voulais apporter le maximum de précisions. Quand je vois certains sujets, certaines personnes sont peu explicites.

A défaut d'utiliser des TCD, penses-tu qu'en utilisant du VBA, pourrais arriver au résultat attendu ?

 

[AtTheOne]

Bonjour Laëtitia (@LAETI-TOINOU), c'est Alain, bonjour à tous
Bon j'ai regardé ton projet, pour les Récap tu as raison les TCD sont la bonne solution.

La formule pour calculer la participation dans la liste des salariés pourrait être

=SI(DATEDIF([@[DATE D''EMBAUCHE]];DATE(ANNEE(AUJOURDHUI());1;1);"Y")>=1;50;0)

DATEDIF est une fonction non répertoriée qui donne des écarts entre une date de début incluse et une date de fin exclue, ici le "Y" indique que l'on veut le nombre d'année entière.

Pour l'attribution de la participation dans le tableau de suivi des commandes c'est un peu plus élaboré , Je détaille ma démarche ci-dessous.
Il faut regarder si le salarié à droit ou non à une participation :

RECHERCHEX([@SALARIE];_Tb_LP[SALARIE];_Tb_LP[PARTICIPATION ANNUELLE];0;0)

Puis le montant potentiel de la participation (en fonction du PU et du Nbre de billet) :

[@[QTE acheté]]*
RECHERCHEX([@[PU achat CSE]];_Tb_Participation[TRANCHE PRIX ACHAT BILLET PAR LE CSE - MINI];_Tb_Participation[PARTICIPATION ALLOUE AUX SALARIES];;-1)

On prend la valeur minimale de ces 2 calculs :

MIN(RECHERCHEX([@SALARIE];_Tb_LP[SALARIE];_Tb_LP[PARTICIPATION ANNUELLE];0;0);
[@[QTE acheté]]*RECHERCHEX([@[PU achat CSE]];_Tb_Participation[TRANCHE PRIX ACHAT BILLET PAR LE CSE - MINI];_Tb_Participation[PARTICIPATION ALLOUE AUX SALARIES];;-1))

Ensuite on calcule les participations déjà versées (dans les lignes du dessus, attention à conserver l'ordre chronologique dans le tableau !) qu'on va retirer à la valeur trouvée précédemment :

-SOMME.SI($B$6:$B6;[@SALARIE];$G$6:$G6)

formule valable en G7 (nom des salarié en colonne B)

Et comme la participation ne peut pas être négative, on prend le maximum entre 0 et le montant trouvé

=MAX(MIN(RECHERCHEX([@SALARIE];_Tb_LP[SALARIE];_Tb_LP[PARTICIPATION ANNUELLE];0;0);[@[QTE acheté]]*RECHERCHEX([@[PU achat CSE]];_Tb_Participation[TRANCHE PRIX ACHAT BILLET PAR LE CSE - MINI];_Tb_Participation[PARTICIPATION ALLOUE AUX SALARIES];;-1))-SOMME.SI($B$6:$B6;[@SALARIE];$G$6:$G6);0)

Voilà
Voir le classeur joint où j'ai également placé 2 "RECAP" dont une avec graphe.

Bon courage, à bientôt
Amicalement
Alain

 

[LAETI-TOINOU]

Bonjour Alain,

Merci beaucoup pour ce tableau.
C'est tout à fait ce que je recherchais, et un grand merci pour tes explications, qui sont très claires comme à chaque fois.
Je vois que j'ai encore beaucoup à apprendre.

Petites questions supplémentaires :
1/ Si la date de référence était le 1er janvier de l'année au lieu de AUJOURD'HUI, comment faudrait-il tourner la formule ?

=SI(DATEDIF([@[DATE D''EMBAUCHE]];DATE(ANNEE(AUJOURDHUI());1;1);"Y")>=1;50;0)

2/ Si le CSE décidait d'accorder 25€ pour les salariés n'ayant que 6 mois d'ancienneté ? Comment faudrait-il tourner la formule ?

Je te souhaite une bonne fin de journée.


Laëtitia

 

[herve62]

Bonjour
Oui le vba ( simple) permet de faire : par salarié, par billet , par CSE
et surtout évite d'effacer les formules ! comme j'ai pu faire ...et donc foutu

Ci joint juste un essai dans l'onglet "RPS" pour Recap Par Salaire : la MAJ se fait en selectionnant l'onglet
Ensuite pour les modif de paramètres comme 25€ pour 6 mois , suffit juste de mettre une petite table dans un coin que l'on peut ajuster comme l'on veut et pas besoin donc de modifier une à une toutes les formules

 

[LAETI-TOINOU]

Bonjour
Oui le vba ( simple) permet de faire : par salarié, par billet , par CSE
et surtout évite d'effacer les formules ! comme j'ai pu faire ...et donc foutu

Ci joint juste un essai dans l'onglet "RPS" pour Recap Par Salaire : la MAJ se fait en selectionnant l'onglet
Ensuite pour les modif de paramètres comme 25€ pour 6 mois , suffit juste de mettre une petite table dans un coin que l'on peut ajuster comme l'on veut et pas besoin donc de modifier une à une toutes les formules

Bonsoir Hervé,

Merci de ton retour.

J'ai toutefois une préférence pour le document proposé par Alain.

Je te souhaite une bonne soirée.


Laëtitia

 

[AtTheOne]

Bonne nuit !

1/ Si la date de référence était le 1er janvier de l'année au lieu de AUJOURD'HUI, comment faudrait-il tourner la formule ?

La formule prend en compte le 1er janvier de l'année en cours :

=SI(DATEDIF([@[DATE D''EMBAUCHE]];DATE(ANNEE(AUJOURDHUI());1;1);"Y")>=1;50;0)

ANNEE(AUJOURDHUI()) renvoie actuellement 2022, et DATE(ANNEE(AUJOURDUI());1;1) renvoie le 1er janvier de cette année...

2/ Si le CSE décidait d'accorder 25€ pour les salariés n'ayant que 6 mois d'ancienneté ? Comment faudrait-il tourner la formule ?

Tu pourrais utiliser cette formule (pour 6 mois d'ancienneté au 1er janvier)

=SI.CONDITIONS(DATEDIF([@[DATE D''EMBAUCHE]];DATE(ANNEE(AUJOURDHUI());1;1);"Y")>=1;50;
DATEDIF([@[DATE D''EMBAUCHE]];DATE(ANNEE(AUJOURDHUI());1;1);"M")>=6;25;
VRAI;0)

Le Paramètre "M" permet de compter les mois entiers entre les 2 dates, VRAI en dernière position permet de renvoyer 0 dans les cas d'ancienneté inférieure à 6 mois (et non pas #N/A) .

Amicalement
Alain

 

[LAETI-TOINOU]

Bonjour Alain, bonjour tout le monde,

Merci beaucoup.

C'est vraiment le fichier qu'il me fallait.

Bonne journée.


Laëtitia

 

[LAETI-TOINOU]

Bonjour Alain, bonjour tout le monde,

J'ai présenté le fichier à mes collègues.

Finalement, il a été décidé que :
- la participation du CSE serait accordée à partir de 6 mois d'ancienneté,
- à une date bien définie (que j'appelle "date repère").

Du coup, j'ai apporté les modifications suivantes sur mon fichier - Onglet LISTE DU PERSONNEL :
- Ajout d'une "date repère", qui sera la référence pour calculer les 6 mois d'ancienneté (cellules F1 et F2)
- Modification de la formule de calcul pour l'attribution de ces 50€ :
=SI(DATEDIF([@[DATE D''EMBAUCHE]];Date_Repere;"m")>=6;Particip_CSE;"")

Formule qui fonctionne, tant que la date d'embauche est < à la "date repère".
Si un salarié est embauché, au lendemain de cette date repère, le résultat #NOMBRE! apparait.

Comment résoudre ce problème, car effectivement, des salariés peuvent être embauchés au delà de cette "date repère3.

Merci de votre retour.


Laëtitia

 

[AtTheOne]

Bonjour à tous, bonjour Laëtitia (@LAETI-TOINOU)
Encadre ta formule par la fonction SIERREUR :

=SIERREUR(SI(DATEDIF([@[DATE D''EMBAUCHE]];Date_Repere;"m")>=6;Particip_CSE;"");"")

A bientôt
Alain

 

[LAETI-TOINOU]

Rebonjour Alain,

Merci pour la formule.


Laëtitia

 

[LAETI-TOINOU]

Bonjour Alain, bonjour tout le monde,

Alors que je croyais mon support de travail enfin abouti, mes collègues viennent de me faire savoir que la remise sur les billets ne se ferait pas en fonction du montant du prix d'achat selon des fourchettes de prix, mais tout simplement par l'application d'un %.
Par exemple 10%.

Sur mon fichier, j'ai donc essayé de remplacer les sommes précédemment indiquées dans l'onglet BILLETTERIE - Colonne G, en gardant malgré tout les fourchettes de prix (on ne sait jamais) par 10%.

Du coup, la formule de calcul (Colonne G) dans l'onglet SUIVI ANNUEL COMMANDES SALARIES n'est plus valable.

Je fais donc une nouvelle fois appel à vous pour m'aider, car si on cumule des formules et des %, ça devient trop compliqué pour moi.

Merci beaucoup.


Laëtitia

 

[AtTheOne]

Bonsoir Laëtitia (@LAETI-TOINOU) bonsoir à tous.

D'abord corriger une coquille en remplaçant
=SIERREUR(SI(DATEDIF([@[DATE D''EMBAUCHE]];Date_Repere;"m")>=6;Particip_CSE;"");"")
par
=SIERREUR(SI(DATEDIF([@[DATE D''EMBAUCHE]];Date_Repere;"m")>=6;Particip_CSE;0);0)
dans la participation annuelle de la liste des salariés. (0 c'est mieux que "" pour faire des calculs !)

Inscris tes pourcentages dans ton tableau de participation par tranche (j'ai mis 10% partout)
Ensuite, dans la colonne "Participation CSE" de l'onglet "SUIVI ANNUEL COMMANDES SALARIES" remplace la formule par :

= MIN(
      RECHERCHEX([@SALARIE];_Tb_LP[SALARIE];_Tb_LP[PARTICIPATION ANNUELLE];0;0) - SOMME.SI($B$6:$B6;[@SALARIE];$G$6:$G6);
      [@[Prix achat Total CSE]]*(RECHERCHEX([@[PU achat CSE]];_Tb_Participation[TRANCHE PRIX ACHAT BILLET PAR LE CSE - MINI];_Tb_Participation[PARTICIPATION ALLOUE AUX SALARIES];;-1))
      )

la participation CSE maximale pour ce salarié (50€ ou 0):
RECHERCHEX([@SALARIE];_Tb_LP[SALARIE];_Tb_LP[PARTICIPATION ANNUELLE];0;0)
De laquelle on retire ce qui lui a déjà eté versé (somme des participations des lignes du dessus):
- SOMME.SI($B$6:$B6;[@SALARIE];$G$6:$G6)

Qu'on compare prix d'achat CSE multiplié par le pourcentage applicable (qu'on lit dans la table) :
[@[Prix achat Total CSE]]
* (RECHERCHEX( [@[PU achat CSE]] ; _Tb_Participation[TRANCHE PRIX ACHAT BILLET PAR LE CSE - MINI]; _Tb_Participation[PARTICIPATION ALLOUE AUX SALARIES] ;; -1))

On prend la valeur minimale de ces 2 valeurs

 

[LAETI-TOINOU]

Bonsoir Alain, bonsoir tout le monde,

Encore un grand merci pour toutes tes explications, et la mise à jour de mon support.

=SIERREUR(SI(DATEDIF([@[DATE D''EMBAUCHE]];Date_Repere;"m")>=6;Particip_CSE;"");"")
par
=SIERREUR(SI(DATEDIF([@[DATE D''EMBAUCHE]];Date_Repere;"m")>=6;Particip_CSE;0);0)
dans la participation annuelle de la liste des salariés. (0 c'est mieux que "" pour faire des calculs !)

Je ne connaissais pas cette subtilité.

Par contre, sur le tableau que tu viens de me retourner, je vois sur l'onglet SUIVI ANNUEL COMMANDES SALARIES, que le salarié 1 achète 250€ de billetterie, mais ne bénéficie pas de la remise supplémentaire.
Je ne sais pas corriger la formule, qui fonctionne pour les autres salariés.


Laëtitia