Excel | scoring RFM dans le tableur

le scoring est l’action de noter les clients en fonction de critères dont les plus célèbres sont l’association RFM (Récence, Fréquence et Montant des achats). La tendance instaurée par les netpromoters (ces petites étoiles que vous trouvez partout quand vous donnez votre avis) généralisent la norme d’une note de 1 à 5.

le fichier à utiliser dans la progression Télécharger

Vos données importées dans Excel (voir le tuto pour importer des données csv), vous ne pourrez pas exploiter les données sans recourir à des formules de calcul.

Plan de l’article

EQUIV pour traiter les valeurs texte
- Fonctionnement de la formule :
application à la récence
Formule arithmétique pour traiter les valeurs numériques

EQUIV pour traiter les valeurs texte

Dans l’aperçu ci-dessous, on a importé des résultats d’un sondage et le tableur a reporté les choix des sondés pour chaque réponse. Il s’agit de formulations textuelles et aucune formule arithmétique ne peut traiter ce cas.

Fonctionnement de la formule :

=EQUIV(valeur_cherchée;tableau_de_recherche;[type])

Valeur_cherchée : il s’agit de la valeur dont on doit comparer la position dans le tableau de recherche
tableau_de_recherche : c’est la plage qui contient les valeurs (uniques) de la comparaison
Type : c’est un chiffre qui indique le degré de précision de la recherche. 0 pour valeur exacte (affiche une erreur # si la valeur n’est pas trouvée), 1 affichera la position de la valeur la plus proche sans dépasser la valeur référence (ordre croissant pour les nombres et alphabétique pour le texte), -1 la position de la valeur la plus proche au dessus de la valeur cherchée.

application à la récence

les sondés ayant coché « moins d’un mois » pour leur dernière visite recevront la note de 5, entre 1 et 3 mois, la note de 4, etc…

commençons par organiser les valeurs de référence (uniques) et nommons-les pour faciliter la recopie de la formule

a) créer une liste sans doublons des valeurs de référence

sélectionner la colonne de récence (dans notre cas « votre dernière visite dans notre parc »)
Ctrl + C (copier)
créer une nouvelle feuille (Shift + F11)
Sélectionner la colonne A
Ctrl + V (Coller)
Dans le ruban Données, choisir « supprimer les doublons«

la boite de dialogue suivante apparaît :

cliquer sur OK (ou valider par Entrée)

Vous disposez maintenant d’un tableau de valeurs uniques.

glisser les cellules pour obtenir l’ordre ci-contre

en fait, la plage de recherche débutera en A2 (la première position) qui donnera la note de 1 aux sondés n’ayant pas répondu à la question. A3, en seconde position, donnera la note de 2, etc…

b) nommer la plage de référence

Sélectionner la plage A2:A6 (sans l’étiquette A1)
Activer la zone noms (à gauche de la barre de formule)
Donner le nom de son choix (dans notre cas « Ref_R » pour références de récence)
Valider par entrer

on pourra désormais utiliser le nom « Ref_R » dans toutes nos formules. Excel comprendra qu’il s’agit de la plage Aé:A6 de cette feuille.

c) appliquer la formule

Retourner dans la feuille des résultats du sondage
Activer la première cellule à calculer dans la colonne Récence (pour nous, H2)
Ecrire la formule suivante :

recopier la formule sur les autres cellules

On constate que l’équivalence aux cellules vides donne un résultat d’erreur #N/A

Corrigeons l’erreur en encadrant la formule d’une condition SIERREUR :

En indiquant le chiffre 1 dans l’argument « valeur si erreur« , on remplace la valeur d’erreur par la note que l’on voulait appliquer aux sondés n’ayant pas répondu à la question.

Formule arithmétique pour traiter les valeurs numériques

Il s’agit de transformer en chiffes allant de 1 à 5 n’importe quel nombre en fonction des fourchettes de valeur que nous nous sommes fixés.

Dans les fichier exemple, la colonne G indique les montants dépensés par visite.

Un petit coup d’oeil dans la barre d’état après avoir sélectionné la colonne nous indiquera les valeurs MAX et MIN de la plage :

On en déduit une amplitude de 235 € soit 47 € par tranche (arrondissons à 50 € pour faire simple. On peut établir notre scoring comme suit :

de 0 à 49 €, la note de 1
de 50 € à 99 € la note de 2
de 100 € à 149 € la note de 3
de 150 € à 199 € la note de 4
à partir de 200 € la note de 5

il suffit donc de prendre la partie entière du résultat grâce à la fonction ENT puis d’ajouter 1 au résultat obtenu !

Commençons par diviser chaque montant par le pas (la valeur de chaque tranche soit 50).

On constate, si on observe la partie entière des résultats que l’on est exactement 1 en dessous du résultat recherché (ce qui est normal puis que le premier pas est inférieur à 50 – donc 0,xxxx – et que notre notation commence à 1).