Accords grammaticaux pour expliciter les calculs Excel

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


Sujets que vous pourriez aussi aimer :


Accords grammaticaux pour expliciter les données

Dans cette formation Excel, nous allons voir comment il est possible de réaliser des accords grammaticaux avec différentes techniques, toutes aussi simples les unes que les autres. L'objectif est d'améliorer la clarté des informations dans les tableaux de synthèse. Quand on parle d'accord grammatical, on fait référence au singulier ou au pluriel et au féminin ou au masculin.

Nous allons exercer nos travaux sur une liste de personnes pour lesquelles une indication est fournie sur le nombre d'enfants qu'elles possèdent. Selon ce nombre, des aides sociales à calculer, sont attribuées.



La capture ci-dessus illustre l'application finalisée. Une couleur dynamique permet de repérer rapidement la proportion de femmes. Des accords sont réalisés dans les colonnes Nb. Enfants et Statut.

Source et présentation de la problématique
Pour apprendre à réaliser ces accords de grammaire, nous proposons de récupérer des données, celles illustrées par le tableau ci-dessus.

• Télécharger le classeur accords-grammaticaux.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification du bandeau de sécurité,

Ce classeur est constitué d'une seule feuille nommée liste_salaries. Ces employés sont référencés sur quatre niveaux de détail entre les colonnes B et E. L'information en colonne B renseigne sur leur civilité. Elle sera donc utile pour réaliser les accords au féminin et au masculin. L'information en colonne E indique leur nombre d'enfants. Cette donnée doit être exploitée pour calculer les aides attribuées en colonne F. Un petit tableau situé entre les colonnes J et K liste les aides octroyées par tranches. Par exemple, entre 1 et 3, 50 Euros d'aide sont versés par enfant. Entre 8 et 10, ce sont 115 Euros qui sont accordés. Il s'agira donc de récupérer la bonne donnée pour le calcul.

Une dernière colonne nommée Statut (G) consistera à indiquer si la personne reçoit des aides ou non, par l'inscription d'un texte à accorder (Aidé ou Aidée). Une dernière manipulation visera à repérer dynamiquement et visuellement les femmes afin de connaître leur proportion. C'est une mise en forme conditionnelle raisonnant sur la civilité qui apportera la solution.

Accord Singulier / Pluriel
Dans la colonne E, nous proposons d'enrichir l'information numérique en l'associant à un texte explicatif. Ce texte est le mot Enfant qu'il s'agit d'accorder au pluriel selon le nombre. Comme le rappelle le livre Excel sur les calculs avancés, la concaténation est un bon moyen d'expliciter les données numériques brutes. Par le caractère de concaténation (&), elle consiste à assembler les deux données. Nous allons réaliser un essai dans une cellule arbitraire. En effet, dans le contexte, nous allons vite comprendre qu'il ne s'agit pas de la solution idéale.

• Sélectionner une cellule sur la droite du tableau, I5 par exemple,
• Taper le symbole = pour débuter le calcul,
• Cliquer sur la cellule E5 pour désigner le nombre d'enfants,
• Enfoncer la touche 1 en haut à gauche du clavier pour le caractère de concaténation (&),
• Saisir le texte Enfant entre guillemets pour l'assembler à la donnée numérique, soit : 'Enfant',
• Valider le calcul en enfonçant la touche Entrée.

Plusieurs remarques sont nécessaires. Tout d'abord l'accord n'est pas fait. De plus, il manque un espace entre le nombre et le texte : 3Enfant. Nous aurions certes pu écrire directement le texte au pluriel. Mais dans ce cas, l'accord n'aurait pas été correct avec les chiffres 0 et 1. Donc ce calcul n'est pas dynamique. Concernant l'espace, il suffit de l'ajouter après le guillemet ouvrant.

Nous pourrions donc envisager d'encadrer la concaténation dans une fonction Excel Si. Cette dernière permet de poser une condition. Si elle est vérifiée, elle engage une action, sinon elle engage une autre action. Donc si le nombre est supérieur à 1, elle doit ajouter la lettre S pour l'accord et ne rien faire le cas échéant. Sa syntaxe est la suivante :

=Si(Condition_à_vérifier; Action_alors ; Action_sinon)

En I5, nous pourrions donc adapter la formule comme suit :

=E5 & 'Enfant' & SI(E5>1; 's'; '')



Deux concaténations sont logiquement nécessaires. La première consiste à assembler le nombre avec le texte Enfant au singulier (E5 & ' Enfant'). Notez la présence cette fois de l'espace après le guillemet ouvrant. La seconde consiste à assembler ce résultat à la lettre S, si et seulement si le nombre est supérieur à 1 (SI(E5>1;'s'...). Dans le cas contraire, rien n'est ajouté (''). Si vous remplacez le chiffre 3 par le chiffre 1 en E5, le résultat en I5 se met à jour, confirmant que l'accord est dynamique.

Mais, car il y a un mais, ce résultat n'est pas satisfaisant. Comme la donnée numérique est assemblée à un texte, le résultat est lui-même un texte. Donc, plus aucun calcul n'est possible. Or, nous devons calculer les aides sur la base de cette donnée numérique.

Format personnalisé pour réaliser les accords
Comme vous le savez, aucune devise ou unité de mesure ne doit être écrite dans la cellule du nombre. Le risque, comme précédemment, est de convertir la donnée en texte non exploitable. C'est un format de cellule qui doit être appliqué. Le plus courant est le format monétaire. Il affiche le symbolede l'Euro (€). Mais il s'agit seulement d'un affichage. La barre de formule montre bien que la donnée numérique d'origine est seule considérée.

Nous savons déjà qu'il est possible d'emprunter un format numérique existant et de l'associer à un texte, en guise d'unité de mesure par exemple. Ce que nous savons moins en revanche, c'est que nous pouvons poser des conditions dans le format personnalisé. Ces conditions permettent de faire varier le texte à associer, pour réaliser un accord par exemple. Elles peuvent s'énumérer en les séparant d'un point-virgule (;) comme dans une fonction Si. Ces critères doivent être entre crochets.

• Supprimer le précédent calcul de concaténation en I5,
• Sélectionner toutes les données numériques de la colonne E, soit la plage E5:E35,
• Dans la section Nombre du ruban Accueil, cliquer sur la flèche de la liste des formats,
• Tout en bas de la liste, choisir Autres formats numériques, comme l'illustre la capture,

• Dans la boîte de dialogue qui suit, sélectionner la catégorie Personnalisée,
• Dans la zone Type au centre, supprimer l'information présente, soit le texte Standard,
• Ouvrir un crochet ([) pour débuter l'écriture de la condition,
• Taper le symbole inférieur suivi du symbole égal, suivi du chiffre 1, soit <=1,
• Fermer le crochet (]) pour terminer le critère,

Lorsque cette condition est vérifiée ([<=1]), comme les enfants ne sont nécessairement pas plusieurs, le texte enfant au singulier doit être associé. Mais avant cela, il s'agit de formater la donnée numérique. Le chiffre 0, comme nous l'avons déjà appris, est idéal pour les quantités, soit les nombres entiers positifs.

• A la suite, saisir le chiffre 0,
• Puis, saisir le texte Enfant précédé d'un espace entre guillemets, soit : ' Enfant',

Attention l'espace doit être inscrit dans les guillemets pour séparer l'information textuelle de la donnée numérique. S'il est inscrit avant le premier guillemet, Excel divise le résultat par 1000. C'est ainsi. A ce stade, nous avons indiquer comment formater la donnée lorsque le nombre est soit zéro, soit un. Il s'agit donc de prévoir la mise en forme dans les cas restants, pour des quantités supérieures donc. Comme nous l'avons dit précédemment, à l'instar de la fonction Excel Si, c'est le point-virgule qui permet d'exprimer le Sinon.

• A la suite, taper un point-virgule (;) pour l'écriture du format correspondant aux autres cas,
• Saisir le chiffre 0 suivi du texte Enfants précédé d'un espace entre guillemets, soit : 0' Enfants',

Nous aurions pu évaluer un nouveau critère pour ce format. Il aurait consisté à vérifier la condition inverse du précédent, soit : [>1]0' Enfants'. Mais il n'est pas nécessaire puisqu'il se déduit seul, dès lors que la première condition n'est pas vérifiée.

• Valider la création de ce format personnalisé en cliquant sur le bouton Ok,

Comme vous le constatez, les accords sont parfaitement réalisés. Les cellules sont toutes correctement formatées. Et pour preuve, si vous consultez la barre de formule de l'une de ces cellules sélectionnées, vous remarquez que seule la donnée numérique apparaît. Le défi est donc relevé. Nos nombres sont préservés et prêts à être exploités pour le calcul des aides sociales. Et en plus, nos résultats numériques sont explicites et clarifient la lecture des informations.

Mais avant cela, nous proposons d'aller encore plus loin. Il est possible d'empiler les conditions dans la construction de ces formats personnalisés. Il s'agit de les énumérer en les séparant d'un point-virgule. Pour prouver l'intérêt et la puissance de ce langage, nous proposons de conserver la règle sur les accords tout en faisant ressortir le texte en rouge, lorsque le nombre d'enfants est supérieur ou égal à 6. Ainsi, nous évaluerons d'un seul coup d'oeil la proportion des familles nombreuses.

• Sélectionner de nouveau la précédente plage de cellules, soit E5:E34,
• Cliquer de nouveau sur la flèche des formats dans la section Nombre du ruban Accueil,
• En bas de la liste, comme précédemment, choisir Autres formats numériques,

Notre format numérique est automatiquement restitué dans la zone type du fait que nous soyons placés sur les cellules ainsi formatées.

• Adapter sa syntaxe comme suit :

[<=1]0' Enfant';[<6]0' Enfants';[Rouge]0' Enfants'



La première partie de la syntaxe ne change pas ([<=1]0' Enfant';). Elle consiste toujours à formater la donnée numérique en l'associant à l'information textuelle au singulier. Mais comme deux autres cas sont désormais à prévoir, nous ajoutons une condition ([<6]0' Enfants'). Dans ce cas, nous savons que la quantité est nécessairement supérieure à 1. Dans le même temps, nous nous assurons qu'elle est strictement inférieure à 6. Car dans ce contexte, seul l'accord au pluriel doit être réalisé. Dans le cas restant qui se déduit implicitement (>=6), l'accord au pluriel est effectué quand dans le même temps la cellule est formatée en rouge. Nous empruntons tout simplement une astuce de syntaxe fournie par Excel, notamment pour le format comptabilité. Comme l'illustre la capture ci-dessus, le dernier format encerclé en bas de la liste est l'un de ceux que propose Excel par défaut. La couleur à appliquer doit être écrite entre crochets.

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

De retour sur la feuille, vous constatez que les accords sont toujours parfaitement réalisés. Mais désormais, les familles nombreuses sont automatiquement repérées et identifiées. Le tableau offre une grande clarté pour simplifier les interprétations. Nos valeurs numériques sont toujours préservées pour les calculs. Enfin, cette couleur est bien dynamique, comme s'il elle avait été configurée avec une mise en forme conditionnelle.

En cellule E19 par exemple, si vous remplacez le chiffre 8 par le chiffre 4, vous remarquez que la couleur disparaît. L'accord quant à lui est toujours fonctionnel. C'est la deuxième branche de notre syntaxe qui a été vérifiée et enclenchée ([<6]0' Enfants').

Calculs par tranches
Nous délaissons quelques temps nos travaux sur les accords afin d'exploiter ces données numériques préservées, pour réaliser le calcul des aides sociales. Ces dernières dépendent du nombre d'enfants, selon le référentiel inscrit entre les colonnes J et K. Les aides par enfant sont attribuées par tranches. Entre 3 et 5 enfants par exemple, c'est un versement de 75 Euros par enfant qui est prévu. Il s'agit donc de multiplier le nombre inscrit en colonne E par la valeur correspondante de l'aide inscrite en colonne K.

Comme cette somme est variable, il s'agit de trouver la correspondance. C'est la précieuse fonction Excel RechercheV qui permet de retourner dynamiquement l'information. Comme il s'agit de tranches, nous devons fixer son dernier paramètre, l'argument booléen sur Vrai. Ainsi, lorsque la donnée cherchée n'est pas trouvée (Le nombre d'enfants), elle se calera sur la valeur inférieure la plus proche. L'aide correspondante sera ainsi extraite. Nous avions mis en oeuvre cette technique dans la formation Excel pour calculer les primes selon l'ancienneté des salariés.

Le calcul que nous devons produire est le suivant :

=Nombre_enfants*RechercheV(Nombre_enfants; Référentiel ; 2 ; Vrai)

Le troisième argument de la fonction RechercheV est numérique. Il désigne la colonne de retour. Fixé à 2, il indique que l'aide correspondante à extraire est située dans la deuxième colonne du tableau de recherche (Réérentiel).

• Sélectionner la première cellule du calcul, soit F5,
• Taper le symbole = pour débuter la formule,
• Cliquer sur la première cellule du nombre d'enfants soit E5,
• Taper le symbole de l'étoile (*) pour enclencher la multiplication,
• Saisir le nom de la fonction d'extraction suivi d'une parenthèse, soit RechercheV(,
• Saisir ou cliquer de nouveau la cellule E5 pour indiquer la valeur cherchée,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Sélectionner l'intégralité du référentiel, soit la plage de cellules J4:K10,
• Enfoncer la touche F4 du clavier pour la figer dans le calcul, ce qui donne : $J$4:$K$10,

En effet, la formule est destinée à être reproduite sur toutes les lignes du dessous. L'objectif est de déduire les aides correspondant au nombre d'enfants de chacun. Et la recherche de cette valeur doit toujours s'effectuer entre les mêmes bornes, celles du référentiel. Nous figeons donc ses références, pour qu'elles ne se déplacent pas en même temps que le calcul change de ligne.

• Taper un point-virgule (;) pour passer dans l'argument de la colonne de retour,
• Saisir le chiffre 2 pour indiquer l'emplacement du montant correspondant à extraire,
• Taper un point-virgule suivi du texte Vrai, soit : ;Vrai, pour une correspondance proche,
• Fermer la parenthèse de la fonction RechercheV,
• Valider le calcul par CTRL + Entrée pour conserver la cellule active,
• Puis, double cliquer sur la poignée du résultat pour reproduire la formule sur tout le tableau,

Les aides sont parfaitement calculées. Le quotient correspondant est extrait grâce à cette recherche approximative, dès lors que le palier est franchi. Si le nombre d'enfants n'est pas précisément trouvé dans le tableau de référence, c'est la donnée de la valeur la plus proche, directement inférieure, qui est extraite. Les résultats sont donc cohérents. Pour prétendre à l'aide d'une tranche supérieure, il faut avoir dépassé sa valeur plancher. La formule que nous avons construite est la suivante :

=E5*RECHERCHEV(E5; $J$4:$K$10; 2; VRAI)

Pour certifier la véracité des résultats, nous proposons de réaliser un calcul de confirmation en colonne H. Il s'agit de diviser le montant des aides calculées par le nombre d'enfants. Le résultat conduira à la valeur correspondante, issue du tableau de référence. Mais comme une division par zéro est impossible, nous devons imbriquer la formule dans une fonction de gestion d'erreur :

=SiErreur(Calcul_à_tenter; Action_si_erreur)

La fonction SiErreur permet de tenter la formule qui lui est passée en premier paramètre. Si aucune exception n'est générée, son résultat est retourné et affiché. Dans le cas contraire, l'action définie en second argument est enclenchée. Deux guillemets suffiront pour conserver la cellule vide lorsque l'opération ne peut pas aboutir. Ainsi nous gèrerons la problématique des personnes ne possédant pas d'enfants (0).

• Sélectionner la première cellule du calcul, soit H5,
• Taper le symbole = pour débuter la formule,
• Saisir la fonction pour gérer les exceptions, suivie d'une parenthèse, soit SiErreur(,
• Sélectionner le montant de la première aide, soit la cellule F5,
• Taper le symbole slash (/) pour enclencher la division,
• Sélectionner l'indication sur le nombre d'enfants, soit la cellule E5,
• Taper un point-virgule (;) pour passer dans l'argument de la gestion d'erreur,
• Saisir deux guillemets ('') pour garder la cellule vide en cas de division impossible,
• Fermer la parenthèse de la fonction SiErreur,
• Valider le calcul par le raccourci clavier CTRL + Entrée,
• Répliquer la logique de la formule sur le tableau en double cliquant sur la poignée de sa cellule,

Si nous en doutions, ces résultats confirment sans ambiguïté, la cohérence des données extraites pour le calcul des aides, en fonction du nombre d'enfants. Constatez la présence des cellules gardées vierges, grâce à la gestion d'exception de la fonction Excel SiErreur.



La formule que nous créée est la suivante :

=SIERREUR(F5/E5; '')

Accord Féminin / Masculin
Nous abordons un autre type de règle grammaticale. Dans la colonne Statut (G), il s'agit d'inscrire une indication textuelle confirmant si le salarié reçoit des aides. Nous proposons d'ajouter le texte Aidé. Il doit bien entendu s'accorder selon la civilité du salarié concerné (Aidée / Aidé). Cette colonne est vide. Il ne s'agit donc plus de créer un format permettant de transformer l'affichage de la cellule. L'information doit apparaître accordée grâce à une formule. Il y a plusieurs conditions à vérifier. Le montant de l'aide doit être supérieur à zéro. Dans ce cas, selon que la civilité est Mr ou Mme, l'information textuelle doit s'adapter. Nous pourrions littéralement poser le raisonnement suivant :

Si l'aide est positive, alors si la civilité est Mme, nous devons écrire le texte Aidée, sinon nous devons écrire le texte Aidé, sinon nous ne devons rien inscrire. Le dernier Sinon sous-entend que le premier critère n'est pas satisfait. Si aucune aide n'est attribuée, l'indication ne doit pas être ajoutée.

Nous devons exploiter deux fonctions Excel Si à imbriquer pour tester ces différentes conditions.

• Sélectionner la première cellule du calcul, soit G5,
• Taper le symbole =pour débuter la formule,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse, soit Si(,
• Sélectionner le premier montant pour le critère à bâtir, soit F5,
• Taper le symbole supérieur suivi du chiffre 0, soit : >0 pour l'inégalité à vérifier,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir de nouveau la fonction conditionnelle suivie d'une parenthèse, soit Si(,
• Cliquer sur la civilité, soit B5 pour la seconde condition imbriquée à valider,
• Taper le symbole égal suivi du texte Mme entre guillemets, soit : ='Mme',
• Taper un point-virgule (;) pour passer dans la branche Alors de la seconde fonction Si,
• Saisir le texte Aidée entre guillemets, soit 'Aidée', pour respecter l'accord au féminin,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la seconde fonction Si,
• Saisir le texte Aidé entre guillemets, soit : 'Aidé' pour valider l'accord au masculin le cas échéant,
• Fermer la parenthèse de la seconde fonction Si,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la première fonction Si,
• Saisir deux guillemets ('') pour garder la cellule vide lorsqu'aucune aide n'est allouée,
• Fermer la parenthèse de la première fonction Si,
• Valider le calcul et le répliquer sur la hauteur du tableau,

Les informations textuelles parfaitement accordées au genre, se greffent dans les cellules en regard de chaque salarié. L'imbrication des fonctions conditionnelles a permis de valider les conditions en cascade. Il s'agit d'une autre méthode satisfaisante pour produire des résultats tout en respectant les règles grammaticales. La formule que nous avons construite est la suivante :

=SI(F5> 0; SI(B5='Mme'; 'Aidée'; 'Aidé'); '')

Rapport visuel Hommes / Femmes
Pour parfaire l'application Excel, nous souhaitons repérer visuellement et dynamiquement toutes les femmes. Il s'agit de faire ressortir leurs lignes en couleur. Cette mise en valeur est un moyen d'offrir une idée rapide sur la proportion des femmes par rapport aux hommes. Il s'agit de créer une règle de mise en forme conditionnelle sur le tableau. Le critère consiste à vérifier que la civilité est le texte Mme. Si cette condition est remplie, c'est l'ensemble de la ligne qui doit être impactée par une mise en forme explicitement différente.

La formation Excel pour alerter sur les stocks disponibles nous avait appris à créer ces conditions permettant d'induire les lignes entières.

• Sélectionner tout le tableau, soit la plage de cellules B5:H34,
• Dans le 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 suit, choisir le type : Utiliser une formule pour...,
• Puis, cliquer dans la zone de saisie du critère, située juste en dessous,
• Taper le symbole = pour débuter la syntaxe du critère,
• Sélectionner la première civilité, soit la cellule B5,
• Enfoncer deux fois la touche F4 du clavier pour ne la figer qu'en colonne, soit $B5,

En effet, le critère consiste à vérifier pour chaque ligne que la civilité est le texte Mme. Donc pour chaque cellule d'une même ligne, la condition doit être construite en colonne B. Donc la cellule du critère ne doit pas se déplacer en colonne. En revanche, pour chaque ligne, c'est bien la civilité correspondante qui doit être analysée. Donc la cellule du critère doit se déplacer en ligne.

• Taper le symbole égal suivi du texte Mme entre guillemets, soit : ='Mme', pour l'égalité de la condition à satisfaire,

Si ce critère est satisfait, une mise en valeur dynamique associée doit se déclencher.

• Cliquer sur le bouton Format situé juste en dessous,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un rose pâle,
• Cliquer sur Ok pour confirmer les réglages et revenir sur la première boîte de dialogue,
• Valider la règle dynamique en enfonçant de nouveau le bouton Ok,

Le rose symbolique se déclenche instantanément sur le tableau. Dans cette application destinée à satisfaire les accords grammaticaux, nous améliorons encore la clarté des informations, avec des interprétations simplifiées par les réglages visuels.

En cellule B5, si vous modifiez la civilité Mr par la civilité Mme, la ligne passe en rose et le texte Aidé se transforme en texte Aidée. En cellule E5, si vous inscrivez la valeur 5 à la place du chiffre 3, l'aide est revalorisée instantanément grâce à la rechercheV. En cellule E13, si vous remplacez le chiffre 0 par le nombre 2, le texte Enfant s'accorde automatiquement au pluriel. Il est satisfaisant de constater que toutes ces fonctionnalités, notamment d'accords, ont été conçues pour une application dynamique.