Suivi et fidélisation clients automatisée 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 :


Gestion et fidélisation Clients avec Excel

Nous proposons ici de réaliser un tableau Excel de suivi et de fidélisation des clients d'une entreprise. Il s'agit d'accorder aux clients, des cadeaux en fonction des chiffres annuels qu'ils apportent. Cette mise en pratique sera l'occasion de mettre en oeuvre des formules apprises au travers de différents supports de formation.



Bien sûr cette gestion clients doit être complètement automatisée, à l'aide de calculs dynamiques. Ainsi d'une année sur l'autre, en fonction des nouveaux chiffres d'affaires apportés, tous les calculs, notamment sur les valeurs des cadeaux, se mettront à jour automatiquement.

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

Comme l'illustre la capture ci-dessus, les cadeaux accordés aux clients se calculeront par l'addition d'une partie fixe et d'une partie variable.


C'est la raison pour laquelle un petit tableau de bord est proposé en haut de la feuille. La partie fixe est définie à 1000 Euros. Elle est accordée à partir du moment où le client a passé commande, ce qui se traduit par un chiffre d'affaires en colonne D. La partie variable est définie à 1% du chiffre d'affaires apporté par le client. L'addition de ces deux parties définira la valeur du cadeau qui sera ramenée au seuil de 4200 Euros en cas de dépassement. Tous les calculs doivent donc se référer aux contraintes de ce tableau de bord, afin que la gestion des clients puisse être automatisée.

Codes clients - Combinaison de textes
Il s'agit de créer tout d'abord les références clients qui consistent en une combinaison de chiffres et de lettres prélevées dans le nom du client. Plus précisément cette référence doit débuter par la partie fixe X615 suivi des trois premières lettres puis de la dernière lettre du nom du client. Les fonctions Excel Gauche et Droite permettent de prélever une partie d'une chaîne de caractères, respectivement en partant du début et de la fin, sur une longueur à définir en paramètre. Ces informations doivent être concaténées pour former la combinaison finale de la référence client. La formation Excel pour assembler des portions de texte apprend l'utilité de la concaténation.

• Sélectionner toutes les cellules de références à créer, soit la plage A8:A21,
• Taper le symbole = pour débuter le calcul,
• Saisir la partie fixe entre guillemets, soit 'X615',
• Enfoncer ensuite la touche 1 en haut du clavier pour inscrire le Et commercial (&),

Ce caractère est utilisé dans les formules Excel pour assembler des bouts de textes les uns avec les autres.

• Saisir le nom de la fonction pour prélever par le début et ouvrir la parenthèse, soit Gauche(,
• Sélectionner la cellule du nom du client ou saisir ses références, soit B8,
• Taper un point-virgule pour passer à l'argument permettant de préciser la longueur à prélever,
• Saisir le chiffre 3 pour prélever les trois premières lettres du nom du client,
• Fermer la parenthèse de la fonction Gauche et ajouter le caractère de concaténation (&),
• Saisir le nom de la fonction pour prélever par la fin et ouvrir sa parenthèse, soit Droite(,
• Désigner de nouveau le nom du client, soit B8,
• Taper un point-virgule pour passer à l'argument de la longueur à prélever,
• Saisir le chiffre 1 pour extraire seulement le dernier caractère de la chaîne,
• Fermer la parenthèse et valider le calcul par le raccourci CTRL + Entrée,

Comme la plage des cellules était présélectionnée avant le calcul, le raccourci clavier a permis en même temps, de valider la formule et de la répliquer sur l'ensemble des cellules, sans devoir tirer sa poignée. Tous les codes clients sont ainsi formés par concaténation. Les fonctions Excel Gauche et Droite ont permis de prélever seulement une partie de la chaîne, sur une longueur définie en second paramètre de chaque fonction.

Calculs des bonus Clients selon CA
Selon les conditions définies par le panneau de contrôle en H2, une partie fixe du cadeau allouée au client est définie à 1000 Euros. Bien sûr, ce fixe doit être calculé si et seulement si le client a effectivement passé commande au cours de l'année. Il s'agit donc de poser un critère sur la cellule du chiffre d'affaires annuel du client. Et c'est comme souvent la fameuse fonction Si d'Excel, qui va nous permettre d'envisager différents cas.

• Sélectionner l'ensemble des cellules du fixe à calculer, soit la plage E8:E21,
• 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 du chiffre d'affaires annuel pour poser le critère, soit D8,
• Saisir alors le symbole inférieur (<) suivi du symbole supérieur (>), soit <>,
• Taper deux guillemets (''),
• Puis, ajouter un point-virgule pour passer dans la branche Alors de la fonction Si,

Pour l'instant nous avons écrit ceci : =SI(ET(D8<>'';

Bien que la formule ne soit pas terminée, nous allons émettre quelques commentaires. Le symbole Supérieur suivi du symbole Inférieur permet, en langage Excel, de signifier l'opérateur de comparaison différent de (<>). Nous cherchons donc à vérifier que le chiffre annuel du client en D8 existe bien, pour lui offrir un cadeau en retour, à calculer sur une partie fixe dans un premier temps. Si ce critère est vérifié, nous devons inscrire la valeur mentionnée en H2 dans la colonne du fixe du client. Comme ce calcul devra être répercuté sur les lignes du dessous pour les autres clients, nous devrons figer cette cellule de référence (Touche F4 du clavier), pour qu'elle ne se déplace pas avec la formule.

• Cliquer sur la contrainte de la partie fixe dans le panneau de contrôle, soit H2,
• Enfoncer la touche F4 du clavier pour figer cette dernière,
• Saisir un nouveau point-virgule pour passer dans la branche Sinon de la fonction SI,
• Saisir alors deux guillemets ('') pour indiquer à Excel de laisser la cellule vide lorsque le CA est lui-même vide,
• Puis, fermer la parenthèse de la fonction Si et valider le calcul par CTRL + Entrée,

Comme vous le constatez, des montants fixes sont attribués à tous les clients, étant donné que dans ce cas de figure, chacun a bien passé commande au cours de l'année. Mais si vous supprimiez l'un des CA, le critère ne serait plus validé par la fonction SI. En conséquence la cellule du fixe serait laissée vide, signifiant qu'aucun cadeau en retour ne serait offert au client.

Il s'agit maintenant de calculer la partie variable du cadeau promis au client, selon le pourcentage défini dans le panneau de contrôle en cellule H3. Le principe est le même. Le calcul de la multiplication doit être réalisé si et seulement si le CA n'est pas nul. Le critère peut indifféremment être posé sur la cellule du CA ou du fixe précédemment calculé, qui s'en déduit.

• Sélectionner l'ensemble des cellules à calculer soit F8:F21,
• Débuter le calcul avec la fonction Si comme précédemment, soit =SI(E8<>'';,
• Après le point-virgule, sélectionner la cellule du CA à multiplier, soit D8,
• Taper l'étoile du pavé numérique (*) afin d'enclencher la multiplication,
• Puis sélectionner la cellule du pourcentage, soit H3 et la figer avec la touche F4 du clavier,
• Taper un second point-virgule pour passer dans la branche Sinon de la fonction Si,
• Puis, comme précédemment, ouvrir et fermer les guillemets (''),
• Enfin, fermer la parenthèse et valider le calcul par CTRL + Entrée,

Cette partie variable du cadeau concédé au client est d'autant plus importante que le chiffre d'affaires apporté par ce dernier, est important. Pour calculer la valeur totale du cadeau consenti pour le client, il suffit désormais de sommer la partie fixe et la partie variable, en colonne G.

• Sélectionner l'ensemble des cellules sur lesquelles calculer les totaux, soit G8:G21,
• Saisir le calcul suivant : =SOMME(E8:F8),
• Puis, le valider par CTRL + Entrée pour le répercuter sur toute la colonne,

Certaines valeurs de cadeaux sont considérées comme trop importantes. C'est la raison pour laquelle l'entreprise fixe une dernière contrainte, par le biais du panneau de contrôle, en H4. Si la valeur totale calculée dépasse ce montant, alors le cadeau sera plafonné à cette valeur définie en H4. Dans le cas contraire, le montant total calculé correspondra bien à la valeur du cadeau offert au client, en retour des commandes passées. Il s'agit donc d'exploiter de nouveau une fonction Si pour valider le critère qui consiste à vérifier si le total calculé est supérieur ou non à ce montant défini en H4. Comme H4 est une cellule de référence pour tous les calculs de la colonne, la cellule devra être figée par les références absolues.

• Sélectionner la plage de cellules H8:H21,
• 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 cellule du total à comparer, soit G8,
• Taper l'opérateur de comparaison Supérieur suivi du symbole = (>=),
• Sélectionner la cellule de référence H4 et la figer ($H$4),
• Taper un point-virgule pour passer dans la branche Alors de la fonction Si,
• Sélectionner et figer de nouveau la cellule H4,

En effet, comme nous l'avons dit, si le critère est vérifié, cela signifie que le montant total dépasse le plafond autorisé dans le panneau de contrôle. Et dans ce cas, la valeur du cadeau est révisée à la baisse pour correspondre à ce plafond. Dans le cas contraire en revanche, le total calculé est conservé, ce que nous allons maintenant traduire :

• Taper un second point-virgule pour passer dans la branche Sinon de la fonction Si,
• Sélectionner la cellule du total, soit G8,
• Puis, fermer la parenthèse et valider le calcul par CTRL + Entrée pour le reproduire,

La formule s'adapte parfaitement pour afficher le montant du cadeau client et le plafonner lorsque le panneau de contrôle l'ordonne. L'intérêt du modèle que nous sommes en train de bâtir est la portabilité qu'il offre pour s'adapter à toute entreprise selon ses propres conditions. En effet, l'administrateur, pour fidéliser les clients de la société, n'aura plus qu'à adapter les contraintes dans le panneau de contrôle en H2, H3 et H4 afin que tous les résultats se mettent à jour automatiquement.

Pour les sommes qui sont proposées en lignes 22, il suffit d'utiliser la somme automatique ou son raccourci clavier ALT + =.

Synthèses statistiques - Somme conditionnelle
La dernière partie du tableau, à partir de la ligne 23, consiste à faire ressortir des données statistiques sur les montants des cadeaux offerts aux clients. Ces derniers sont recensés en départements en colonne C. La société aimerait savoir quel est le département le plus favorable à développer sa clientèle, en calculant les sommes des montants des cadeaux par département. Bien entendu ce calcul doit rester dynamique. Il n'est pas question de pointer les départements à la souris pour sélectionner manuellement les cellules à sommer. Si d'aventure les clients venaient à changer, avec les départements, les calculs deviendraient erronés.

Nous proposons d'exploiter la somme conditionnelle d'Excel (SOMME.SI) qui permet en fonction d'un critère vérifié sur une plage de cellules, de sommer sur une autre plage correspondante. Le critère ici étant le numéro du département. Sa syntaxe est donc la suivante : Somme.si(plage_du_critere ;Critere ; Plage_ou_sommer)

Dans notre cas, la plage de cellules pour vérifier le numéro du département est C8:C21. Le critère correspondant au département est identifié en colonne G à partir de la cellule G24. Avec un critère lui aussi dynamique, ce sont tous les calculs qui resteront automatisés et auto-adaptables. Enfin, la plage de cellules correspondante où la somme doit se faire lorsque le critère est trouvé, est la plage H8:H21.

• Sélectionner les cellules à calculer, soit la plage H24:H26,
• Taper le symbole = pour initialiser le calcul,
• Saisir le nom de la somme conditionnelle suivi d'une parenthèse ouvrante, soit Somme.Si(,
• Sélectionner la plage de cellules où le critère doit être cherché, soit C8:C21,
• Enfoncer la touche F4 du clavier pour figer les bornes de cette plage,

Le calcul doit en effet être reproduit sur les deux lignes du dessous. Mais la recherche doit se faire dans la même plage de cellules. Ses bornes ne doivent donc pas bouger.

• Taper un point-virgule pour passer à l'argument du critère,
• Désigner la cellule G24 en la saisissant par exemple,

En G24 figure le premier département à rechercher pour calculer la somme correspondante des cadeaux offerts, sur la plage de cellules des cadeaux.

• Taper un point-virgule pour passer à l'argument de la plage de cellules où sommer,
• Sélectionner de H8 à H21 puis enfoncer la touche F4 pour figer cette plage,
• Fermer la parenthèse de la fonction Somme.si,
• Valider et reproduire le calcul par le raccourci clavier CTRL + Entrée,

Grâce aux références absolues, le calcul a pu être répercuté sur les autres cellules, en déplaçant le critère vers le bas, pour considérer les deux autres départements. L'entreprise pourra apprécier de constater que le département 78 semble le plus propice aux affaires. Il s'agit en effet du département pour lequel la valeur des cadeaux est la plus importante. Cette valeur est en partie calculée sur un pourcentage du chiffre d'affaire apporté par les clients.

Le modèle de suivi et de fidélisation des clients est terminé et peut être exploité par toute entreprise. Il suffit de manipuler les contraintes du tableau de bord pour modifier les conditions de fidélisation. Par exemple, si vous modifiez en H3 le pourcentage de 1 à 2%, vous notez instantanément, que les clients dépassant le plafond de la valeur du cadeau, sont plus nombreux.