Calculs de primes selon ancienneté avec Excel

Classeur Excel et formation détaillée sur le site Internet :
www.bonbache.f...
Calculer dynamiquement le pourcentage de prime sur salaire selon l'ancienneté du salarié dans l'entreprise en fonction d'une grille évolutive de tranches. La société alloue des primes à ses employés selon des tranches d'ancienneté définies dans un tableau de bord. Il s'agit de faire correspondre la prime du salarié en fonction de sa date d'entrée dans l'entreprise. Nous réalisons un premier calcul complexe grâce à l'imbrication de fonctions Si. Le résultat est un succès mais la formule demeure compliquée et non évolutive. Pour un calcul dynamique et adaptatif, nous mettons en place une rechercheV, dans la grille des primes avec une recherche approximative. Cette recherche se réalise sur un tableau aux bornes non définies grâce aux fonctions decaler et NbVal qui permettent de faire grandir le tableau, en déplaçant sa borne inférieure, tant qu'il y a des cellules non vides. Ainsi nous obtenons des résultats dynamiques capables de prendre en compte les nouvelles primes allouées aux nouvelles tranches créées dans la source de données.
#FormationExcel
#ApprendreExcel
#ExcelExercices
#CalculsExcel
Formation Excel complète :
• Formation Excel
Pensez à vous abonner à la chaîne :
/ formations excel, acce...
N'oubliez pas de cliquer sur la petite cloche pour ne pas manquer les nouvelles sorties.
Toutes les astuces avec Excel :
• Astuces Excel
Toutes les vidéos sur les calculs matriciels avec Excel :
• Formation Calculs Matr...
Retrouvez l'ouvrage complet sur les exercices Excel :
www.bonbache.f...
Retrouvez l'ouvrage complet sur les calculs matriciels avec Excel :
www.bonbache.f...

[@rufinzossou9671]

Vous êtes vraiment un expert et un pédagogue de EXCEL. Tous vos cours sont limpides et faciles à comprendre meme pour les debutant. Merci pour ce cours.

C'est très agréable de votre part Rufin Zossou. Je vous remercie très chaleureusement pour ce témoignage en retour.

[@masvision100]

Bonjour cher professeur pour ce cours bien et très claire

Bonjour et merci à vous pour ce message en retour.

[@hanaesaadi8848]

Salut ca va svp tu peux me repondre cette question comment est fixées la prime d'ancienneté?? Q theorie

[@francishoudayer7328]

Bonsoir Stéphane, comme le propose Rom lef l'insertion du tableau sans lui changer le nom juste en deuxiéme paramétre indiquer tableau1 et ca fonctionne parfaitement. Et bravo pour votre travail remarquable c'est trés intéressant et enrichissant. cordialement.

Bonsoir Francis, merci beaucoup pour votre message. Le nom de ce tableau vous le définissez sur quelle plage de cellules ? S'agit-il d'une plage qui englobe au départ des cellules qui vont au delà des bornes du tableau d'origine ? Car à mon sens, la plage de cellules définie par un nom doit être redimensionnée par la fonction Decaler pour qu'elle intègre les nouvelles données saisies à la suite. Mais dans le doute je vais essayer. En tous cas, merci beaucoup pour votre intervention et contribution.

[@GeorgeBander]

Bonjour cher professeur !! Super, une fois de plus !Toujours une grande imagination..;! Merci !!

Bonjour Gérard, un grand merci à vous comme toujours pour votre soutien.

[@stephanecazaux9475]

Bonjour M. Rossetti. Je suis dans l'ordre toute la playlist consacrée à Excel et comme toujours, vos explications souligne un don pédagogique et vos exemples sont très bien choisis. Ici, je voudrais vous faire part d'une autre solution possible (avec la recherche V comme demandée) : =sierreur(rechercheV(quotient(E7;4)*4; decaler(.....));2;faux);max(L:L)). Comme je m'efforce de réaliser les tableur avant de regarder votre solution, celle ci fonctionne également. Un grand bravo pour la qualité de vos tutos.

Bonjour Stéphane, votre contribution est importante pour la communauté et je vous en remercie grandement ainsi que pour votre intérêt et votre soutien.

[@doncristobal5021]

Excellente formation; comme toujours. On peut aussi utiliser la "mise sous forme de tableau" pour rendre l'abaque des taux dynamique

Bonjour Don Cristobal. Merci beaucoup pour ce message. Oui tout à fait, la mise sous forme de tableau permet de considérer automatiquement l'expansion des plages.

[@ItsISKA]

Excellent formation merci infiniment

Merci beaucoup eycmael.

[@nomanonimous.prenomanonimous]

Bonsoir je profite qu'il n'y a rien à voir à la télé ce soir pour me former grâce à vous. Perso, j'aime bien utiliser le "gestionnaire de noms" avec un formule pour simplifier la lecture. "gestionnaire de noms" > tranche=DECALER('Primes par tranches'!$K$7;0;0;NBVAL('Primes par tranches'!$K$7:$K$15);2) avec une formule du type : =RECHERCHEV(E8;tranche;2;VRAI)

Bonsoir Sergio, oui c'est une très bonne méthode qui permet de largement simplifier la syntaxe de la formule à l'issue. Mais j'émets en revanche une petite critique personnelle, lorsqu'il s'agit de reprendre les travaux d'une autre personne et que les formules sont toutes ainsi conçues, il devient plus compliqué de faire le lien avec les références des cellules et la compréhension des calculs devient plus opaque. Cela dit la méthode est excellente. Et il est en effet possible de rendre une plage de cellules évolutive en remplaçant ses références par un calcul qui déplace dynamiquement ses bornes en fonction du contenu grâce notamment aux fonctions decaler et nbval, depuis le gestionnaire de noms. Très bonne soirée à vous.

[@cordisb7584]

Tres bien expliqué, merci infiniment

Merci beaucoup Cordis B

[@abdelazizchelbi7531]

Tout d'abord, je tiens à vous remercier vivement, cher professeur, pour le rappel de toutes ces fonctions utiles et dont certaines sont peu connues ou utilisées. Seulement, je viens de tirer certaines remarques: 1- Concernant la fonction DATEDIF: Dans cet exemple, où date du jour=01/05/2017, le 10eme salarié (BERNARD) recruté le 02/05/2016 aurait travaillé une année entière (12 mois) alors que le résultat de la formule n'est que 0,92 cela est dû au fait que la fonction DATEDIF compte la différence entre les dates par simple soustraction (exemple: entre les dates 01/01/17 et 31/12/17, la fonction renvoie 11 mois!); la solution que je propose est d'ajouter '1 jour' à la seconde date, ainsi la fonction à utiliser serait : DATEDIF(D7;$C$4+1;"m")/12 2- Concernant l'agrandissement du tableau des taux des primes: Ce tableau a fait l'objet de plusieurs propositions de la part de certains lecteurs (adorant vos magnifiques cours, comme moi) en proposant de nommer ce tableau est de lui attribuer comme référence la formule DECALER, or cette solution ne fait que déplacer la partie DECALER de la formule initiale à la référence du tableau nommée!!! Alors, le vrai problème, à mon avis, est celui de l'ajout d'une remarque ou observation en bas du tableau (et même, quelques lignes plus bas), cela conduit à fausser toute la formule DECALER; la solution que je propose est celle de transformer ce tableau en 'tableau Excel' (onglet: INSERTION, groupe: TABLEAUX, choisir: TABLEAU), alors ce dernier se transforme en un tableau mis en forme, avec des filtres et nommé tableau1 (à renommer à volonté); sa meilleure particularité est que la ligne ajoutée, juste au dessous du tableau, se met en forme automatiquement et s'ajoute aux données du tableau (rien de plus) et les lignes de commentaires (encore plus bas) n'ont aucun effet sur lui; ainsi la fonction à utiliser serait alors : =RECHERCHEV(E8;tableau1;2;VRAI) Enfin, et pour conclure, je tiens à préciser que ces tutos ont pour but essentiel de faire connaitre (et rappeler) des fonctions et des méthodes pour un meilleur perfectionnement sans se soucier à trouver une meilleure solution au problème posé car ce dernier n'est qu'un exemple choisi pour simplifier la compréhension.

Mon cher Abdelaziz, vous apportez toutes les améliorations nécessaires et précieuses à ces mises en application pour mon plus grand bonheur avec qui plus est, un précision chirurgicale. Merci entre autre pour l'amélioration qui consiste à traiter la particularité de la différence entre deux dates, lorsque les jours et mois sont proches. Concernant la solution que vous proposez pour simplifier le dynamisme et l'automatisme de la fonction RechercheV, je vous rejoins tout à fait. Le fait de convertir des plages en tableaux automatiques, les rendent dynamiques dans leur utilisation. En revanche et si vous le permettez, c'est une solution que je ne préconise pas dans les formations, pour bien comprendre le mécanisme et être capable de le reproduire lorsque le cas l'impose.

[@abdelazizchelbi7531]

Votre choix concernant les tableaux dynamiques est tout à fait fondé (d'ailleurs, je ne les utilise presque jamais) et c'est pourquoi j'ai fait ma conclusion ci-haut

[@abdelazizchelbi7531]

Je viens de me rappeler d'une petite astuce (pas très conventionnelle mais efficace) que j'utilisais, il y a longtemps, qui utilise le fait que si on insère une ligne entre les lignes du tableau, celui-ci est pris en compte par toute formule utilisant ce tableau par son nom ou par ses références de cellules (même s'il s'agit de références absolues). La méthode consiste à ajouter d'abord un dernier enregistrement dont les valeurs ne seront jamais atteintes (dans cet exercice, on peut ajouter les valeurs 100 et 100% par exemple) et le tableau sera défini dans les formules l'utilisant (par son nom ou ses références) initialement ainsi. Lors de vouloir ajouter d'autres enregistrements, il suffit d'insérer autant de lignes avant le dernier enregistrement'enregistrement' et le tour est joué. Dans le cas de grands tableaux (surtout ayant des cellules contenant des formules), je résume ces actions dans une petite macro.

Merci mon cher Abdelaziz, nous attendons cette petite macro Excel avec impatience et encore une fois, merci mille fois pour vos efforts et contributions précieuses pour tous.

[@abdelazizchelbi7531]

Il suffit, d'abord d'ajouter une dernière ligne (en K12 et L12, ayant les valeurs 100 et 100%, par exemple) puis: - Sélectionner la cellule K12 - Cliquer sur le bouton 'Enregistrer une macro' de l'onglet 'Développeur' - Cliquer sur le bouton 'Choisir les références relatives' et effectuer les actions suivantes : - Sélectionner la dernière ligne ajoutée (K12:L12) - Cliquer sur le bouton droit de la souris et choisir 'Insérer ...' - Choisir 'Décaler les cellules vers le bas' - Sélectionner la cellule K12 (emplacement de la nouvelle donnée) - Cliquer sur 'Arrêter la macro'

[@thierrywattiaux6035]

Bonjour et merci pour ce tuto qui est toujours aussi complet et bien expliqué. J'aimerais juste vous soumettre un petit mais cependant important problème au sujet de ce tuto. Si un employé entre en service le 01/03/2016 et que nous sommes le 28/02/2017, le tableau calcul moins d'un an d'ancienneté. Auriez vous une astuce pour éviter cela. En vous remerciant. Thierry W

Bonjour Thierry. Merci pour votre intérêt. Oui, il faut certes exploiter la fonction Excel DateDif mais avec des paramètres supplémentaires pour calculer l'écart entre deux dates au jour près. C'est ainsi que nous voyons si l'année est révolue ou non. Cette formation montre, au milieu de beaucoup d'autres notions, comment l'exploiter ainsi : www.bonbache.fr/calculs-sur-les-heures-et-dates-dans-excel-166.html

[@gavaudvanessa5188]

Trop trop bien merci Stéphane :)

Je vous en prie Vanessa, merci à vous pour ce message en retour.

[@emirsadek7093]

merci pour cet video tres claire

Je vous en prie, merci pour votre message

[@tontonaboga3685]

Merci bcp professeur, mais nous avons un souci sur l'image de votre écran n'est pas visible, donc en a mal à voir les formules

Bonjour Tonton ABOGA. La vidéo est pourtant en HD. Je ne comprends pas trop le souci que vous avez pu rencontrer.

[@mahmoudbenmami4131]

merci Mr Stéphane, Retour en force en quantité et en qualité ...... // en suivant vos formations, on va pas tarder a devenir des supers fonctionnaires,......mais moi personnellement, des fois je perd la motivation d'apprendre et d'apprendre des news skills .....je ne sais pas si vous avez lu le livre best sellers " rich dad poor dad de Mr kiyosaki robert......

Bonjour Mahmoud, merci beaucoup pour ce retour. Non je n'ai pas lu ce livre.

[@شجرةالدرالعفري]

Bonjour Mer voilà je veux savoir comment pourrais-je faire sortir une prime de rondement d’un sans la payé sans internet la direction générale a fait monter sur le PC le programme mais je suis nouvelle🙏👍🏻✋

Bonjour Madame. Tout dépend des règles internes de l'entreprise, des objectifs et quotas fixés. Excel offre toutes les possibilités mais à adapter aux contraintes de l'entreprise.

[@شجرةالدرالعفري]

@ Le programme qui est sur le PC sur le quel je travaille ça existe mais je n’ai pas pue le sortir dommage que vous soyez loin de moi de toute façon merci pour votre amabilité et bonne réussite pour votre chaîne KZbin 😊🇩🇿👍🏻

@@شجرةالدرالعفري C'est très gentil à vous. Merci beaucoup.

[@yaminaelmouhib9195]

Bonjour je suis en accident de travail je vous savoir si la prime d expérience est payée par mon employeur tous les mois ou non merci beaucoup professeur

Bonjour Yamina. Je ne suis malheureusement pas un professionnel du domaine et tout dépend de la convention collective. En ce sens, je ne saurais vous répondre et j'en suis désolé.

[@cliffborman4522]

La voix on dirait à la radio et c’est bon à écouter . Merci pour votre contenu cependant dans mon Excel j’aimerais bien utiliser la fonction Datedif mais je ne l’ai pas dans mon Excel actuel et je dois rendre mon devoir avant le 5 Mai 2024. Je ne sais pas quoi faire

Bonjour et merci beaucoup pour ce message. Si bien sûr, la fonction DateDif est disponible avec toutes les versions d'Excel mais comme il s'agit d'une fonction volatile, elle n'est pas naturellement proposée. Elle est étudiée sous toutes les coutures avec d'autres sujets, dans ce cas pratique : www.bonbache.fr/calculs-sur-les-heures-et-dates-dans-excel-166.html

[@wafaaazzouni3959]

bonsoir Stéphane, encore un superbe Tuto, on ne cesse d'apprendre avec vous, mais voilà, j'ai beau suivre le tutoriel sur liste déroulante et recherche dynamique qui aborde la fonction décaler et même sur cette vidéo mais je n'ai pas encore saisie le bon fonctionnement de cette fonction, si vous pouvez m'en donner d'avantage explication ou un lien expliquant au mieux cette fonction. Encore merci pour le partage.

Bonsoir Wafaa Azzouni, la fonction Decaler est effectivement très précieuse. Elle permet de rendre évolutives et dynamiques des plages de cellules. Ainsi les formules et listes déroulantes peuvent considérer les nouvelles saisies réalisées a posteriori. Elle demande 5 arguments : =decaler(ref;decalage_ligne;decalage_colonne;hauteur;largeur). Ref correspond généralement à la cellule ou la plage de départ pour un tableau. Les décalages en ligne et colonne permettent de préciser le point de départ par rapport à cette référence. A zéro, on part de la cellule indiquée par la référence. La hauteur et la largeur permettent précisément de rendre évolutives ces plages grâce à la fonction NbVal qui peut les faire varier en comptant les éléments à considérer. Cette formation sur les listes dynamiques l'explique très bien : www.bonbache.fr/listes-deroulantes-et-recherches-dynamiques-dans-excel-128.html

[@wafaaazzouni3959]

Re bonsoir Stéphane, en fait j'ai déjà visualisé la vidéo sur les listes déroulantes et recherche dynamique, mais cette fois ci dans le calcul, vous n'avez pas mis de nbval-1 et même par rapport a ce que vous avez employé sur les listes déroulantes, je sais que c'est le même principe mais différent, c'est pour cela que je vous ai demandé s'il vous auriez un lien ou une ressource expliquant en détail cette fonction.

Bonjour Wafaa, je n'ai pas de lien précis traitant seulement de la fonction Decaler. En revanche, elle est aussi exploitée pertinemment dans cet exemple : www.bonbache.fr/extraire-les-donnees-de-doublons-avec-excel-185.html, pour déplacer les bornes de recherche dans un tableau, afin d'extraire les données de doublons sans retraiter le même.

[@wafaaazzouni3959]

Bonsoir Stéphane, tout d'abord merci pour l'effort que vous faites, vous n'êtes pas obligé de me répondre et pourtant à chaque fois que je vous envoies un message vous répondez alors je vous dis un grand merci, je vais essayer de suivre ce tuto, et je vous dirai, encore une fois merci.

Bonsoir Wafaa Azzouni, tant que je le peux et que je pense avoir la réponse, je trouve cela on ne peut plus normal. Et c'est à mon tour de vous remercier pour votre reconnaissance.

[@nacimtebbal7065]

Bonjour Monsieur, tout d'abord je vous remercie pour votre vidéo, j'ai une question à vous poser si vous me le permettez. Notre convention collective attribue des indemnités d'expérience professionnelles d'une façon progressiste par tranche, comme suit : de 1 à 5 ans : 2% chaque année de 6 à 10 : 3% de 11 à 15 : 4% de 16 à 20 % : 5% plus de 20 : 6% et on calcul de la façon suivante : par exemple un salarié qui a 16 ans au sein de notre organisme a le droit de : 2%*5 + 3%*5 +4%*5 +5%*1= 50%. votre formule n'est pas adéquate avec la façon dont on calcul l'IEP, est-ce qu'il y a une possibilité de la calculer avec Excel ? Merci infiniment

Bonjour Nacim. Ma solution ou formule est un exemple Lambda pour ouvrir la voie. Mon objectif est de permettre, sur cette base, que chacun puisse adapter le cas en fonction de sa convention collective. Tout est possible avec Excel. Dans le cas que vous m'exposez, il faut gérer les résultats avec des instructions conditionnelles (Si), selon la tranche atteinte.

[@delphine9584]

Bonjour, Merci pour cette video très claire. Comment calculer une moyenne d'âge en mentionnant le nombre d'années et le nombre de mois ? Une précision, je souhaite calculer une moyenne d'ancienneté Merci beaucoup par avance

Bonjour Delphine et merci pour votre retour. Vous devez exploiter la fonction Excel Datedif. En lui passant les paramètres, vous pouvez obtenir la différence au jour près, comme expliqué entre autres, dans cette formation : www.bonbache.fr/calculs-sur-les-heures-et-dates-dans-excel-166.html

[@romlef526]

Bonsoir stephane, comme d'habitude, je vais m'exprimer :) n'était-il pas possible au lieu d'ajouter une fonction décalée, de faire ( une insertion tableau ) ainsi de K6 à L11 on aurait bien un tableau !!! et ainsi le fait d'ajouter une entrée en K12 et L12, de plus en nommant le tableau avec un nom, et dans la rechercheV , dans la partie sélection de la table matrice, on donne le nom du tableau que l'on a donné précédemment et le tour est joué, non ????? Quoi qu'il en soit, sa fonctionne très bien avec la fonction décalée, merci encore pour tout ce travail

Rom Lef quel plaisir de nouveau ! Tout à fait mais à mon sens, une fois le nom du tableau attribué, il fait référence à une plage de cellules fixes et pour que la référence à cette matrice soit évolutive, il me semble qu'il est nécessaire de modifier la plage de cellules à laquelle ce nom fait référence avec la fonction Decaler pour que les nouvelles saisies puissent être considérées dynamiquement par les calculs. C'est d'ailleurs ce que je démontre dans cette formation pour les listes déroulantes et recherches dynamiques : www.bonbache.fr/listes-deroulantes-et-recherches-dynamiques-dans-excel-128.html, basées sur des noms de tableaux et plages, retravaillés avec cette fonction. Néanmoins votre proposition reste à essayer. Très bonne soirée à vous Cher Ami.

[@romlef526]

merci pour cette précision, passez également une excellent soirée :)

[@rabahghouti7070]

رائع

Merci beaucoup Rabah Ghouti

[@josealfonsi3915]

Bonjour Merci pour ce cours, mais je suis encore perdu, je fais comme vous dites et il me sort un calcul à 0€ alors qu'il doit avoir 30€ ces la galère

Bonjour José, toute la procédure détaillée est donnée à cette adresse avec les formules : www.bonbache.fr/calculs-de-primes-selon-anciennete-avec-excel-184.html En la suivant scrupuleusement, vous ne pouvez pas vous tromper.

[@hanaesaadi8848]

Slt cv svp mrc pr cette video vous pouvez me repondre pour cette question : comment est fixées la prime d'ancienneté???

Bonjour, ici elle est fixée arbitrairement à titre d'exemple pour le calcul. Mais même si la règle change, le calcul lui, reste le même. Il suffit d'adapter les tranches. Ensuite, tout dépend des conventions collectives je pense.

[@hanaesaadi8848]

@ groooo merci mr stephane 👍👍👍👍

Je vous en prie