Gérer les subventions par 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 :


Gestion des subventions avec Excel

Dans le cas pratique suivant, nous proposons de mettre en application des notions Excel apprises au cours des différentes formations, afin de dresser un tableau de bord permettant aux gestionnaires d'automatiser l'allocation annuelle de subventions attribuées à des associations.



Chaque année, le montant des subventions est voté par le conseil Municipal. L'administrateur de la commune de St Fos doit pouvoir suivre l'évolution des aides attribuées dans le but de conserver la maîtrise du budget de la commune. Ce tableau de bord doit livrer des résultats automatiques et synthétiques afin de permettre une lecture transversale aisée des résultats.

• Télécharger le classeur Excel subvention-conseil-municipal.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer le contenu si nécessaire,

Dans les trois premières colonnes de ce tableau sont listés respectivement, les noms des associations, leur demande de subvention pour l'année suivante (2017) et le montant des aides dont ils ont bénéficié l'année précédente (2016). Les propositions de subventions pour chaque association doivent être calculées dans les colonnes suivantes, en tenant compte des contraintes indiquées dans le tableau de référence, en ligne 2 et 3 entre les colonnes D et F.

Proposition de subventions - Calcul
En colonne D, nous devons calculer le montant des subventions proposées par le conseil. Selon le tableau de référence, si la demande émanant de l'association est identique au montant de l'année précédente, nous devons majorer les aides de 8%. En revanche, si la demande est supérieure, nous devons appliquer une majoration de 15%. Le calcul doit donc dépendre d'un test à vérifier par la fonction Excel conditionnelle Si.

• Sélectionner toutes les cellules à calculer, soit la plage D7:D16,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse ouvrante, soit SI(,
• Sélectionner la première cellule de la demande 2017, soit B7,
• Taper le symbole = pour initier la comparaison du critère,
• Sélectionner la première cellule de la subvention 2016, soit C7,
• Taper un point-virgule pour passer dans la branche Alors de la fonction Si,

La formule n'est pas terminée, mais à ce stade la syntaxe du calcul est la suivante :

=si(B7=C7;

Selon les indications du tableau de référence, deux cas sont à envisager. Le cas où la demande est identique à la précédente et le cas où elle est supérieure. Dans la zone de critère de la fonction SI, nous réalisons donc la comparaison entre la demande (B7) et le précédent accord (C7). Si ces deux valeurs sont égales, alors nous devons appliquer une majoration de 8% (Branche Alors de la fonction Si), sinon nous devons appliquer une majoration de 15% (Branche Sinon de la fonction Si). Reprenons l'écriture de la formule :

• Sélectionner de nouveau la cellule de la subvention 2016, soit C7,
• Taper le symbole * du pavé numérique pour la multiplication,
• Ouvrir la parenthèse pour factoriser le calcul,
• Taper le chiffre 1 suivi du symbole + pour l'addition, soit 1 +,
• Sélectionner la majoration correspondante du tableau de référence, soit la cellule F2,
• Enfoncer la touche F4 du clavier pour figer cette dernière dans le calcul,
• Fermer la parenthèse de la factorisation,
• Taper un point-virgule pour passer dans la branche Sinon de la fonction SI,

Nous venons d'indiquer le calcul à réaliser lorsque le critère est vérifié par la fonction Si, soit quand la demande de subvention est identique à celle de l'année précédente. La syntaxede la formule à ce stade est la suivante :

=SI(B7=C7; C7*(1+$F$2);

Si nous appliquons une majoration de 8%, cela signifie que nous devons multiplier la subvention par 108%, soit 100% + 8% ce qui équivaut à 1 + 8%, soit 1 + F2. Comme il s'agit d'une cellule qui doit être utilisée comme une référence pour toutes les autres cellules de la plage, elle ne doit pas bouger au moment de reproduire le calcul sur les lignes du dessous. Donc nous figeons les références de cette cellule en enfonçant la touche F4 du clavier. Enfin, comme la multiplication est prioritaire sur l'addition en mathématiques, nous encadrons l'addition dans les parenthèses afin que la multiplication puisse se faire sur son résultat. Il reste à reproduire exactement le même raisonnement dans la branche Sinon de la fonction Si, pour calculer la majoration de 15% avec l'autre cellule de référence.

• Sélectionner de nouveau la cellule C7 de la subvention 2016,
• Taper le symbole * du pavé numérique pour enclencher la multiplication,
• Ouvrir la parenthèse pour factoriser le calcul,
• Taper le chiffre 1 suivi du symbole +, soit 1 +,
• Sélectionner la cellule de référence pour la majoration à 15%, soit F3,
• Enfoncer la touche F4 du clavier pour figer ses références dans le calcul,
• Fermer la parenthèse de la factorisation,
• Puis, fermer la parenthèse de la fonction Si,
• Réaliser le raccourci clavier CTRL + Entrée pour valider le calcul et le répliquer.

Comme les cellules du calcul étaient présélectionnées, le raccourci clavier CTRL + Entrée a permis de répliquer le calcul sur toute la plage sans devoir tirer la poignée. Comme vous le remarquez, toutes les propositions 2017 se calculent en effet en considérant une majoration conforme aux contraintes du tableau de référence. Les références absolues (Touche F4) ont permis de figer les cellules (F2 et F3) qui ne devaient pas se déplacer avec la réplication vers le bas du calcul.


Si vous modifiez les pourcentages de majoration dans le tableau de référence, tous les calculs des subventions 2017 se mettent instantanément à jour. C'est tout l'intérêt du modèle que nous sommes en train de bâtir. En revanche, les subventions se négocient à l'euro prêt et non au centime d'euro. Nous allons donc arrondir ces résultats pour ne pas afficher les décimales. Nous devons inclure le calcul précédent dans la fonction Excel Arrondi dont la syntaxe est la suivante :

=Arrondi(Montant ; Nombre_decimales)

Le premier argument à passer est le montant de la subvention à arrondir. Dans notre cas, il s'agira donc de la fonction Si qui livre ce calcul à l'issue. Le deuxième argument consiste à préciser le nombre de décimales à conserver. Dans notre cas, nous indiquerons Zéro (0) pour arrondir à l'euro prêt.

• Sélectionner de nouveau les cellules du calcul, soit la plage D7:D16,
• Enfoncer la touche F2 du clavier pour accéder à la saisie de la formule,
• Cliquer après le symbole = pour y placer le point d'insertion,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit ARRONDI(,
• Cliquer à la fin de la formule après la dernière parenthèse fermante pour y placer le point d'insertion (Vous pouvez le faire dans la barre de formule),
• Taper un point-virgule pour passer dans l'argument du nombre de décimales de la fonction,
• Taper le chiffre 0 et fermer la parenthèse de la fonction Arrondi,
• Valider le calcul par CTRL + Entrée pour répliquer les modifications sur toute la plage,

Tous les calculs de subventions sont effectivement mis à jour instantanément dans la colonne. Les résultats sont à la fois plus propres et plus logiques. La syntaxe complète de la formule que nous avons construite est la suivante :

=ARRONDI(SI(B7=C7; C7*(1+$F$2); C7*(1+$F$3));0)

La fonction Si est imbriquée dans la fonction Arrondi qui attend son résultat pour l'arrondir à l'euro.


Dans la colonne suivante, nous souhaitons fournir un résultat synthétique intéressant. Il consiste à afficher la part de pourcentage que représente la subvention allouée à chaque association, par rapport à l'ensemble des subventions accordées. Ce ratio permettra de contrôler si certaines subventions prennent une part trop importante par rapport aux autres. Le calcul consiste donc à diviser le montant de la subvention accordée par la somme de toutes les subventions proposées. Il s'agit donc dans un premier temps de calculer cette somme.

• Sélectionner toutes les subventions 2017 avec la cellule du résultat, soit la plage D7:D17,
• Réaliser la raccourci clavier ALT + = ou cliquer sur le bouton Somme automatique du ruban,

Le résultat apparaît instantanément dans la cellule D17. C'est tout l'intérêt de cette technique par présélection qui permet le calcul sans étape intermédiaire. ALT + = est le raccourci clavier qui permet d'enclencher une somme automatique.

• Sélectionner toutes les cellules des pourcentages à calculer, soit la plage E7:E16,
• Taper le symbole = pour débuter le calcul,
• Sélectionner la première subvention, soit la cellule D7,
• Taper le Slash (/) du pavé numérique pour la division,
• Sélectionner la cellule de la somme des subventions, soit D17,
• Enfoncer la touche F4 du clavier pour figer cette cellule dans le calcul,
• Valider la formule par CTRL + Entrée pour la répliquer sur toute la plage,

Tous les ratios sont ainsi instantanément répercutés sur l'ensemble de la colonne mettant en évidence des différences de traitements importants entre les associations. Nous avons figé la cellule de la division dans ce calcul à l'aide de la touche F4 pour qu'elle soit toujours utilisée comme la cellule de référence pour la division. Si nous ne l'avions pas fait, ses références se seraient déplacées vers le bas en même temps que nous avons répliqué le calcul sur les lignes du dessous.

Nous souhaitons obtenir des ratios avec une précision à une décimale. Par exemple, au lieu de 2,37%, nous souhaitons afficher 2,40%. Donc nous devons de nouveau exploiter la fonction Excel Arondi. Mais pour ne conserver qu'une décimale dans les pourcentages, nous devons arrondir à trois chiffres. En effet, 2,37% est l'équivalent de 0,0237. Le 3 de 0237 est en troisième position dans les décimales. C'est à cette position donc, que nous devons tronquer le résultat.

• Sélectionner tous les résultats des pourcentages, soit la plage de cellules E7:E16,
• Enfoncer la touche F2 du clavier pour accéder à la saisie de la formule,
• Cliquer juste après le symbole = pour y placer le point d'insertion,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit Arrondi(,
• Cliquer à la toute fin de la formule pour y placer le point d'insertion,
• Taper un point-virgule pour passer à l'argument du nombre de décimales,
• Saisir le chiffre 3 et fermer la parenthèse de la fonction Arrondi,
• Valider le calcul par CTRL + Entrée pour répliquer les modifications sur toute la plage,

Les résultats sont plus présentables et plus simples à lire étant donné qu'il s'agit de ratios, donc d'ordres de grandeur. La syntaxe finale de la fonction que nous avons bâtie est la suivante :

=ARRONDI(D7/$D$17;3)

Calculs des évolutions entre deux dates
Afin de maîtriser les dépenses et contrôler que les augmentations ne sont pas démesurées, nous devons calculer en colonne F, l'évolution en pourcentage de la subvention proposée en 2017 par rapport à la subvention accordée en 2016. Le calcul d'une évolution est le suivant : (Valeur_fin - Valeur_depart) / Valeur_depart. Dans notre cas, cela se traduit par : (Proposition 2017 - Subvention 2016) / Subvention 2016.

• Sélectionner toutes les cellules des évolutions à calculer, soit la plage F7:F16,
• Taper le symbole = pour débuter le calcul,
• Ouvrir la parenthèse pour factoriser la formule,
• Sélectionner la première cellule de la proposition 2017, soit la cellule D7,
• Taper le symbole moins (-) du pavé numérique pour la soustraction,
• Sélectionner la première cellule de la subvention 2016, soit la cellule C7,
• Fermer la parenthèse puis taper le slash (/) du pavé numérique pour la division,
• Sélectionner de nouveau la cellule de la subvention 2016, soit la cellule C7,
• Valider le calcul par CTRL + Entrée pour le répliquer sur toute la plage,

Nous constatons fort logiquement que les évolutions sont conformes aux majorations imposées par le tableau de référence. Néanmoins, certaines variations sont enregistrées, tantôt enpositif, tantôt en négatif, selon que l'arrondi que nous avons appliqué a majoré ou minoré la valeur du calcul.

Rang et classement des valeurs numériques
Dans la dernière colonne du tableau, nous souhaitons réaliser un classement des subventions attribuées, de la plus grande à la plus petite, soit selon un ordre décroissant. En d'autres termes, l'association qui touche le plus d'aides doit être classée en première position. Inversement, celle qui touche le plus faible montant, doit être classée en dernière position. La fonction Excel permettant de réaliser un classement des valeurs entre elles se nomme Rang. La syntaxe de la fonction Rang est la suivante :

=Rang(valeur_a_classer ;tableau_des_valeurs ; odre)

La valeur à classer est le montant de la subvention. Le tableau des valeurs représente l'ensemble des subventions par rapport auxquelles ce classement doit être établi. Enfin, l'ordre est une valeur booléenne pour la hiérarchie du classement, 0 pour décroissant.

• Sélectionner toutes les cellules pour calculer le rang, soit la plage G7:G16,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit Rang(,
• Sélectionner la première subvention à classer, soit la cellule D7,
• Taper un point-virgule pour passer à l'argument du tableau de référence,
• Sélectionner toutes les subventions, soit la plage D7:D16,
• Enfoncer la touche F4 du clavier pour figer la plage de cellules,
• Taper un point-virgule pour passer à l'argument de l'ordre du classement,
• Saisir le chiffre 0 et fermer la parenthèse,
• Valider le calcul par CTRL + Entrée pour le répliquer sur toute la plage,

Nous obtenons instantanément tous les classements. Les rangs sont cohérents puisque le plus grand est bien classé premier tandis que le dernier est classé dixième, soit dernier. Nous avons judicieusement figé la plage de cellules du tableau, afin que ses bornes ne se déplacent pas en même temps que la formule est répliquée sur les lignes du dessous.


Mise en évidence dynamique des valeurs remarquables
Pour finir et faire en sorte que le tableau soit rapide à lire et facile à interpréter, nous souhaitons faire ressortir en couleur, la ligne entière de l'association la plus subventionnée ainsi que la ligne entière de l'association la moins subventionnée. Bien sûr, si les subventions changent, les couleurs doivent s'adapter pour s'appliquer aux nouvelles valeurs remarquables. Il s'agit d'appliquer une mise en forme conditionnelle de manière à ce que l'aspect des cellules change en fonction des valeurs dans la colonne G.

• Sélectionner l'ensemble des cellules concernées par le format, soit la plage A7:G16,
• Cliquer sur le bouton Mise en forme conditionnelle du ruban Accueil,
• Dans la liste, choisir Nouvelle Règle,
• Dans la boîte de dialogue qui suit, choisir le dernier type : Utiliser une formule...,
• Cliquer dans la zone de saisie juste en dessous pour bâtir la formule du format dynamique,
• Taper le symbole = et sélectionner la première cellule du classement, soit G7,
• Enfoncer deux fois la touche F4 de manière à ne figer que la colonne de cette cellule,
• Taper le symbole = pour la comparaison,
• Saisir ensuite le nom de la fonction pour extraire le plus grand, suivi d'une parenthèse ouvrante, soit Max(,
• Sélectionner toutes les cellules du classement, soit la plage G7:G16,
• Fermer la parenthèse de la fonction Max,
• Cliquer sur le bouton Format juste en dessous pour définir une mise en valeur associée,
• Dans la boîte de dialogue qui suit, choisir une police grasse avec un rouge rubis,
• Cliquer sur l'onglet Remplissage et choisir un fond orange pâle,
• Valider ce réglage en cliquant sur le bouton Ok,
• De retour sur la première boîte de dialogue, valider de nouveau par Ok,

Comme vous le remarquez, toute la ligne concernée par la subvention la plus faible, correspondant au classement le plus élevé est dynamiquement mise en forme. Il s'agit de l'association Amicale Rugby.


Pour bâtir ce format conditionnel, nous avons construit la formule suivante :

=$G7 = MAX($G$7:$G$16)

Lorsque le classement (G7) est le plus élevé de tous (MAX($G$7:$G$16)), alors nous appliquons la mise en forme définie, sur toute la ligne. Comme la cellule G7 n'est figée qu'en colonne ($G7), la comparaison s'applique bien sur toute la colonne tandis que la mise en forme résultante s'applique bien sur toute la ligne. Les mises en forme conditionnelles peuvent se cumuler sur une même plage de cellules. En conséquence, nous devons ajouter la mise en forme permettant de faire ressortir le premier, correspondant à la subvention la plus élevée. La formule est quasi-identique :

=$G7 = min($G$7:$G$16)