Augmentations de salaires sous condition avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Augmentations conditionnelles des salaires

Cet exercice Excel est l'occasion d'affirmer les compétences avancées. Il y est question d'augmentations de salaires à allouer automatiquement et dynamiquement, selon un taux défini. Mais ces augmentations sont sujettes à condition. Tout le monde n'est pas concerné.



Comme l'illustre le tableau de la capture ci-dessus, les salaires sont connus pour l'année N. Ils doivent être automatiquement calculés et indexés pour l'année N + 1.

Source et présentation de la problématique
Ce tableau doit être réceptionné. Ainsi nous focaliserons notre attention sur les manipulations et formules.

• Télécharger le classeur augmentations-conditionnelles.xlsx en cliquant sur son lien,
• Double cliquer sur le nom du fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau jaune de sécurité,

Ce classeur Excel est composé d'une seule feuille. Elle est nommée Salaires. Les salariés sont énumérés en colonne B, entre les lignes 8 et 15. Les salaires respectifs en cours sont renseignés dans la colonne C voisine pour les mêmes lignes.

Le salaire indexé doit être calculé dynamiquement en colonne D. Il doit tenir compte d'un pourcentage d'augmentation dynamique inscrit en cellule D4. Mais cette augmentation ne doit être accordée que dans la mesure où la contrainte dynamique inscrite en cellule D5 est respectée. Le salaire, pour être augmenté, doit être inférieur à 2500 Euros.

Enfin, des résultats statistiques sont demandés entre les lignes 17 et 19.

Calculer une augmentation de salaire
Pour décomposer le raisonnement, nous proposons dans un premier temps d'appliquer l'augmentation dynamique à tous les salaires, sans tenir compte donc de la contrainte inscrite en cellule D5. La formule consiste à calculer l'augmentation en Euros : Salaire*D4 pour l'ajouter au montant d'origine : Salaire + Salaire*D4. La syntaxe factorisée serait donc la suivante : Salaire*(1 + D4).

• Sélectionner la cellule du premier salaire indexé à calculer, soit la cellule D8,
• Taper le symbole égal (=) pour débuter la formule,
• Cliquer sur le premier salaire en regard pour intégrer sa référence C8 dans la syntaxe,
• Taper le symbole plus (+) du pave numérique pour enclencher l'addition,
• Désigner de nouveau ce même salaire, soit la cellule C8,
• Taper le symbole de l'étoile (*) pour enclencher la multiplication,
• Désigner alors le pourcentage pour intégrer sa référence D4 dans le calcul,
• Valider la formule par le raccourci clavier CTRL + Entrée pour conserver la cellule active,

Le résultat est cohérent. L'augmentation observée est bien de 7%.

Comme vous le savez, on ne refait jamais deux fois le même calcul dans Excel. La formule doit être répliquée, en l'occurrence ici, sur les lignes du dessous. Et pour cela, nous pouvons exploiter la poignée de la cellule du résultat. Lorsque cette dernière est active, cette poignée est matérialisée par un petit carré noir. Lorsque vous pointez dessus avec la souris, son curseur se transforme en une croix noire indiquant qu'Excel est prêt à reproduire le calcul. La cellule du précédent résultat doit donc toujours être sélectionnée.

• Pointer avec la souris sur la poignée de la cellule du résultat,
• Lorsque le curseur de la croix noire apparaît, double cliquer avec le bouton gauche,

Cette technique est intéressante car elle permet de répliquer un calcul jusqu'à la borne inférieure du tableau, détectée automatiquement par Excel. Cependant et comme vous le constatez, les résultats ne sont pas corrects. Certains résultats sont très conséquents, d'autres conduisent à des erreurs.



Nous sommes désormais des utilisateurs aguerris d'Excel. Le problème vous a sauté aux yeux, à n'en point douter. Les références absolues sont nécessaires dans cette formule pour assurer un calcul dynamique réplicable.

• Double cliquer sur le dernier résultat, soit la cellule D15 pour visualiser sa syntaxe,

Les jeux de couleurs initiés par Excel permettent d'identifier rapidement les cellules impliquées dans le calcul. Et la sentence est sans appel. Pour ce dernier résultat, l'augmentation est bien calculée sur la base du dernier salaire correspondant. Il est situé sur la même ligne. Ses références ont donc naturellement suivi le déplacement vertical imposé par la poignée. En revanche, le taux à appliquer a suivi le même déplacement alors qu'il aurait dû rester figé dans sa cellule D4 d'origine. Le salaire est ici multiplié par un précédent résultat qui lui-même conduit à une erreur. Cette précédente erreur s'explique par la multiplication du salaire par un texte, celui du titre de la colonne.

Nous devons donc modifier la formule en enclenchant les références absolues sur le taux d'augmentation. Dès lors, nous pourrons répliquer le calcul pour assurer un modèle dynamique.

• Enfoncer la touche Echap du clavier pour abandonner la modification de formule,
• Sélectionner le tout premier résultat, soit la cellule D8,
• Dans la barre de formule, cliquer entre le D et le 4 de la référence D4,
• Enfoncer la touche F4 du clavier pour figer le taux d'augmentation dans le calcul,

Des dollars encadrent automatiquement les coordonnées de la cellule : $D$4. Elle ne peut désormais ni se déplacer en ligne, ni se déplacer en colonne. Nous pourrions nous contenter de ne conserver que celui situé devant l'indice de ligne : D$4. En effet, le calcul est amené à être répliqué uniquement sur la hauteur du tableau et non sur la largeur. Il n'est pas gênant donc la conserver pleinement figée dans ce contexte.

• Valider la modification par le raccourci CTRL + Entrée pour garder le résultat actif,
• Puis, double cliquer sur la poignée de sa cellule pour répercuter le changement,

En effet, tous les résultats livrés semblent cette fois cohérents.



Une augmentation de 7% est aobservée sur chaque salaire correspondant.

• Double cliquer sur le dernier résultat pour afficher la syntaxe de la cellule D15,

L'augmentation est bien calculée sur la base du salaire correspondant, dont les références de la cellule ont suivi le déplacement sur les lignes du dessous. Mais le taux qui lui est appliqué est cette fois bien resté dans sa cellule, grâce aux références absolues. De fait, notre modèle est dynamique et nous allons le prouver.

• Enfoncer la touche Echap du clavier pour abandonner la modification de formule,
• En cellule D4, saisir 10 à la place de 7 et valider avec la touche Entrée,

Tous les salaires indexés se mettent effectivement à jour au changement de taux accordé. Cette zone de contrainte devient donc précieuse pour faire varier les résultats, selon le contexte et la politique de la société.

• Réinitialiser les 7% d'origine,

Calcul d'augmentation conditionnel
Le problème est donc plus corsé que l'angle sous lequel nous l'avons abordé. Nous souhaitions décomposer le raisonnement pour faciliter la compréhension. De nombreuses notions fondamentales d'Excel entrent en jeu et se mêlent en effet. Cette augmentation ne doit pas être calculée pour tous les salariés. L'opération doit tenir compte de la contrainte, elle aussi dynamique, en cellule D5. Si le salaire de l'employé est inférieur à ce montant, l'augmentation lui est accordée, dans le cas contraire, sa valeur ne change pas. C'est la fonction Excel Si qui permet d'analyser des conditions et apporte la solution pour ce type de raisonnement. Sa syntaxe est la suivante :

=Si(Critère_à_vérifier; Action_alors ; Action_sinon)

Elle requière donc trois arguments.

Le critère consiste à comparer le salaire avec la contrainte : C8<=D5. S'il est vérifié, alors une action est engagée. Ici, il doit s'agir du calcul de l'augmentation, sans oublier les références absolues : C8+C8*$D$4. Sinon, si le critère n'est pas vérifié, le salaire ne doit pas être indexé. En conséquence, il doit être retranscrit fidèlement : C8.

• Sélectionner tous les précédents résultats, soit la plage de cellules D8:D15,
• Enfoncer la touche Suppr du clavier pour éliminer les calculs,
• Sélectionner le premier résultat à trouver, soit la cellule D8,
• Taper le symbole égal (=) pour débuter la formule,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse, soit Si(,

Une info-bulle apparaît instantanément pour vous aider à renseigner ses trois arguments. Il s'agit de ceux que nous venons d'expliquer. Le premier concerne le critère qui consiste à comparer le salaire avec la contrainte.

• Cliquer sur la cellule du salaire pour intégrer sa référence C8 dans la syntaxe,
• Taper le symbole inférieur suivi du symbole égal, soit <=, pour la comparaison,
• Désigner la contrainte pour intégrer sa référence D5 dans la formule,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $D$5,

En effet, à l'instar du raisonnement précédent, lorsque nous répliquerons la logique sur les lignes du dessous, chaque salaire ayant suivi le déplacement devra être comparé à cette contrainte située dans la cellule de référence D5, qui ne doit donc pas bouger.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Sélectionner le salaire pour intégrer la référence C8 dans le calcul,
• Taper le symbole plus (+) du pavé numérique pour enclencher l'addition,
• Désigner ce même salaire, soit C8 pour le calcul de la majoration,
• Taper le symbole de l'étoile (*) du pavé numérique pour la multiplication,
• Désigner alors le taux pour intégrer sa référence D4 dans la syntaxe,
• Enfoncer la touche F4 du clavier pour la figer comme précédemment,
• Taper un nouveau point-virgule (;) pour passer dans la branche Sinon de la fonction,
• Désigner de nouveau le salaire ou à défaut écrire la référence de la cellule, soit C8,

Il se peut que la formule déborde empêchant la sélection de la cellule à la souris. Dans le cas donc où le critère n'est pas vérifié, nous savons que le salaire est déjà supérieur à 2500 Euros. En conséquence, nous le retranscrivons sans majoration.

• Fermer la parenthèse de la fonction Si,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

Le premier résultat tombe et le salaire est fort logiquement majoré. Il répond à la condition imposée.

La formule que nous avons construite est la suivante :

=SI(C8<=$D$5;C8+C8*$D$4;C8)

• Double cliquer sur la poignée du résultat pour répliquer la logique sur la hauteur du tableau,

Les résultats sont tout à fait satisfaisants. Les augmentations ne sont appliquées qu'aux salaires inférieurs à 2500 Euros.



Et nous n'insisterons jamais assez sur ces notions, nous avons bâti une formule totalement dynamique.

• Enfoncer la touche Echap du clavier pour abandonner la modification de la cellule,
• En D5, saisir 2000 à la place de 2500,

Instantanément, tous les calculs de la rangée s'actualisent. Et comme, vous le remarquez, les augmentations sont beaucoup nombreuses, puisque le pallier a été redescendu.

• Annuler le dernier changement ou saisir de nouveau 2500 en D5,

Synthèse et statistiques
Les calculs de synthèse demandés entre les lignes 17 et 19 sont triviaux. Ils n'en sont pas moins importants. En les consultant en premier, ils donneront une idée globale des actions entreprises par les calculs. En somme et comme toujours, ils doivent simplifier l'interprétation des données à la première lecture.

En D17, nous devons comptabiliser le nombre d'augmentations. Ce dénombrement est conditionnel. Le raisonnement consiste à compter tous les salaires inférieurs au seuil en D5, avant indexation. La fonction dédiée, que nous avons exploitée dans l'exercice précédent, se nomme Nb.Si. Sa syntaxe est la suivante :

=Nb.Si(Plage_où_compter; Critère_pour_compter)

La plage de cellules des salaires à comptabiliser est donc la plage C8:C15. Ce décompte doit être réalisé lorsque le critère, à passer en second paramètre, est satisfait. Ce critère consiste à vérifier que le salaire en cours d'analyse est inférieur ou égal à la contrainte inscrite en D5. L'opérateur de comparaison doit être inscrit entre guillemets et concaténé à la variable de cellule.

• Sélectionner la cellule D17 et taper le symbole égal (=) pour débuter la formule,
• Saisir le nom de la fonction de dénombrement suivi d'une parenthèse, soit Nb.Si(,

Attention, il s'agit bien d'un point (.) séparant les deux termes et non d'une virgule. A l'ouverture de la parenthèse, vous notez l'apparition d'une info-bulle. Elle guide le concepteur afin de renseigner la fonction au plus juste (Plage ; Critère). Le point-virgule est essentiel dans la syntaxe. Il sert à séparer et identifier explicitement les deux arguments.

• Sélectionner la plage de cellules des salaires non indexés, soit C8:C15,
• Taper un point-virgule (;) pour passer dans l'argument du critère,
• Taper le symbole inférieur suivi du symbole égal entre guillemets, soit '<=',
• Ajouter le caractère de concaténation en enfonçant la touche 1 en haut à gauche du clavier,
• Sélectionner la contrainte en cellule D5,
• Fermer la parenthèse de la fonction Nb.Si,
• Puis, valider le calcul à l'aide du raccourci clavier CTRL + Entrée,

En l'état, le résultat statistique est livré en Euro. Nous allons corriger ce défaut de format. La fonction Nb.Si a comptabilisé quatre salaires inférieurs à la valeur de référence. Elle indique donc que quatre augmentations sont accordées. La formule que nous avons construite est la suivante :

=NB.SI(C8:C15;'<='&D5)

• Veiller à ce que la cellule D17 soit bien sélectionnée,
• Avec la liste déroulante de la section Nombre dans le ruban Accueil, choisir Standard,

La quantité dénombrée est désormais affichée sans décimales et sans le symbole de l'Euro.

En D18, nous devons calculer la somme des salaires versés. La somme automatique est donc dédiée. En revanche, comme ce résultat ne touche pas les données à additionner, nous ne pouvons pas exploiter la précieuse méthode en un clic. Celle-ci est entre autres enseignée dans le livre Excel gratuit pour débuter avec les calculs. Nous allons devoir décomposer le mouvement.

• Cliquer sur la cellule D18 pour la sélectionner,
• Tout à fait à droite du ruban Accueil, cliquer directement sur le bouton Somme automatique,

Comme vous le constatez, la proposition d'Excel n'est pas correcte. C'est pourquoi la fonction est en attente de validation. Elle se base sur les cellules numériques adjacentes.

• A la souris, désigner tous les salaires de la dernière colonne, soit la plage D8:D15,
• Puis, valider le calcul par le raccourci clavier CTRL + Entrée,

La syntaxe de la fonction ainsi créée est donc la suivante : =SOMME(D8:D15).

Le résultat fourni est de 23 587,35 €. Il donne instantanément l'idée de la représentation des salaires dans l'entreprise.

Le dernier calcul en D19 consiste à soustraire les salaires de l'année précédente aux salaires actualisés dans la dernière colonne. Comme nous venons de réaliser ce calcul, nous pouvons l'exploiter directement.

• Sélectionner la cellule D19,
• Taper le symbole égal (=) pour débuter la formule,
• Désigner le total précédemment calculé pour intégrer sa référence D18 dans la syntaxe,
• Taper le symbole moins (-) du pavé numérique pour déclencher la soustraction,
• Saisir le nom de la fonction pour additionner suivi d'une parenthèse, soit Somme(,

En effet, rien n'empêche de taper directement le nom des fonctions automatiques proposées par Excel. Nous pouvons ainsi les intégrer dans des calculs plus complexes.

• Sélectionner tous les salaires de la colonne C, soit la plage de cellules C8:C15,
• Fermer la parenthèse de la fonction Somme,
• Puis, valider le calcul par CTRL + Entrée,

Ce résultat est précieux pour la maîtrise des budgets. Instantanément, l'entreprise constate qu'elle verse 567 Euros de plus par mois que l'année précédente, pour honorer les salaires.



Indicateurs visuels dynamique
Pour parfaire le modèle, nous souhaitons bâtir une règle de mise en forme conditionnelle particulière. Elle doit repérer sur un fond de couleur dynamique, tous les salariés ayant bénéficié d'une augmentation. Ainsi, elle rendra compte rapidement des proportions. Sa vocation est bien sûr de simplifier l'interprétation des données à la première lecture.

La ligne entière doit être mise en valeur. Le critère consiste pour chacune, à comparer le salaire de l'année N avec la valeur seuille accordant l'augmentation. Les références absolues sont nécessaires pour construire cette règle.

• Sélectionner la plage de cellules B8:D15,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste, choisir Nouvelle règle,
• Dans la boîte de dialogue qui apparaît, choisir le type Utiliser une formule pour...,
• Dans la zone de saisir juste en dessous, taper le symbole égal (=) pour initier la syntaxe,
• Sélectionner le premier des salaires non indexés, soit la cellule C8,

Une règle de mise en forme conditionnelle raisonne effectivement chronologiquement. Vous remarquez que la cellule désignée est complètement figée. Or, il faut analyser et comparer le salaire de chaque ligne. Donc, cette cellule doit être défigée en ligne. En revanche, le critère est valable pour toute la ligne. Donc, elle doit rester figée en colonne.

• Enfoncer deux fois la touche F4 du clavier de manière à ne conserver que le dollar devant l'indice de colonne, ce qui donne : $C8,
• Taper le symbole inférieur suivi du symbole égal, soit : <=, pour la comparaison,
• Désigner la contrainte pour intégrer sa référence D5 dans la syntaxe,

Là encore, la cellule est complètement figée. C'est exactement ce que nous souhaitons afin de comparer chaque salaire avec cette valeur seuille, bien que l'analyse se propage en ligne et en colonne.

Le critère est finalisé. Nous devons désormais lui associer un format. Il s'agit d'attributs qui sedéclencheront dès lors qu'il est vérifié, sur la plage désignée.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un fond vert clair et valider par Ok,

Nous sommes de retour sur la première boîte de dialogue. La règle est explicite. Elle explique que tout salaire inférieur ou égal à la valeur de la contrainte entrainera la mise en valeur, sur fond vert, de toute la ligne présélectionnée.

• Valider ces réglages en cliquant de nouveau sur le bouton Ok,

Instantanément, quatre des huit salariés ressortent.

• En D5, changer la valeur 2500 par 2000, puis valider,

Aussitôt, les résultats de calculs et alertes visuelles se mettent à jour. Dès la première lecture, nous comprenons que seuls deux salariés sont concernés par les augmentations. De même, les résultats de synthèse s'actualisent en bas de tableau, offrant un bilan facile et rapide à interpréter. L'augmentation accordée chaque mois a été réduite de moitié en influant sur l'un des deux paramètres du tableau.