Détecter les changements d'adresse des clients

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


Sujets que vous pourriez aussi aimer :


Détecter les changements entre deux listes

Nous avions déjà apporté la solution pour mettre en lumière les différences entre deux tableaux de données. Pour cela, nous avions bâti une règle de mise en forme conditionnelle tout à fait particulière. Ici, nous souhaitons résoudre le cas par calcul matriciel.



Dans l'exemple illustré par la capture ci-dessus, nous comparons deux sources de données de clients. Celle de gauche est considérée comme la nouvelle liste tandis que celle de droite représente l'ancienne. Entre les deux périodes, des clients peuvent avoir déménagé. En conséquence, l'ancienne liste, encore en cours d'utilisation, offre des informations potentiellement obsolètes. Nous proposons de les déceler par calcul matriciel dans une colonne indépendante, sur la droite de la feuille.

Source et présentation de la problématique
Pour réaliser ce cas, nous suggérons tout d'abord de réceptionner un fichier offrant les sources à comparer.

• Télécharger le classeur identifier-changements.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Si vous consultez attentivement les deux sources de données, vous pouvez remarquer que certains clients existent dans une source et pas dans l'autre. Mais la majorité figure dans les deux tableaux. Parfois, ils sont situés sur les mêmes lignes et souvent sur des lignes différentes. Et c'est bien la complexité de la problématique qu'il s'agit de résoudre. La comparaison des deux sources ne peut être linéaire. Un raisonnement chronologique considèrerait qu'une même ligne portant des informations différentes se traduit par un client différent.

Ici, l'enjeu consiste donc à déceler la présence d'un client issu de la première source de données dans la seconde et ce, quelle que soit sa position. Dès lors, les adresses doivent être comparées. Si elles diffèrent, nous devons en conclure qu'un changement a été opéré entre les deux périodes. Une adresse est la combinaison du code postal et de la ville. En d'autres termes, un double critère doit être analysé.

De plus, si vous déployez la zone Nom, en haut à gauche de la feuille Excel, vous notez que les colonnes du second tableau ont été nommées en fonction de leurs titres. Nous utiliserons ces noms dans les formules matricielles.



Ainsi, nous pourrons aisément comparer chaque client issu du premier tableau avec tous les clients du second.

Comparer deux tableaux
Nous l'avons évoqué précédemment, les techniques classiques de calcul sont à exclure. Un raisonnement chronologique n'est pas capable de scruter les recoins de chaque tableau dans une même formule. La fonction SommeProd quant à elle raisonne sur des matrices. Elle en scrute les lignes respectives. Par défaut, cette fonction réalise la multiplication des lignes respectives et additionne les totaux à l'issue. Mais nous l'avons déjà appris, nous pouvons poser des contraintes sur ces matrices. Si elles sont recoupées, la fonction SommeProd répond par le chiffre 1et par le chiffre 0 le cas échéant. Ces contraintes consistent à vérifier la similitude sur la ville et le code postal dans leurs champs respectifs. Pour enclencher une action en cas de succès, ce raisonnement doit être imbriqué dans la fonction conditionnelle Si.

• Sélectionner la première correspondance à trouver, soit la cellule L6,
• Taper le symbole égal (=) pour initier la formule matricielle,
• Saisir la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Saisir la fonction matricielle suivie d'une parenthèse, soit : SommeProd(,
• Ouvrir une nouvelle parenthèse pour accueillir la première matrice conditionnelle,
• Désigner les codes postaux de la seconde liste par le nom de plage, soit : CP,
• Taper le symbole égal (=) pour annoncer le critère à honorer,
• Désigner le premier code postal du premier tableau, soit la cellule D6,

De cette manière, nous cherchons à trouver la correspondance du code postal dans l'intégralité de la plage de la seconde liste. Et comme ce calcul est destiné à être répliqué sur les lignes du dessous, ce sont tous les codes postaux de la première liste qui seront comparés avec ceux de la seconde. Mais dans le même temps, nous devons observer l'égalité sur la ville.

• Fermer la parenthèse de la première matrice conditionnelle,
• Taper le symbole de l'étoile (*) pour annoncer la condition à recouper,
• Ouvrir une nouvelle parenthèse pour accueillir la deuxième matrice conditionnelle,
• Désigner les villes de la seconde liste par son nom de plage, soit : Ville,
• Taper le symbole égal (=) pour annoncer la seconde condition à recouper,
• Désigner la première ville de la première liste en cliquant sur sa cellule E6,
• Fermer la parenthèse de cette seconde matrice conditionnelle,
• Fermer la parenthèse de la fonction SommeProd,
• Taper le symbole égal (=) pour annoncer le critère de la fonction Si à vérifier,
• Puis, saisir le chiffre zéro (0),

Par ce critère, nous décelons ainsi chaque enregistrement pour lesquels le couple CP/Ville n'est pas identifié strictement à l'identique. Dans ces conditions, nous souhaitons les marquer par la mention Nok.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir la mention Nok entre guillemets, soit : 'Nok',
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir la mention Ok entre guillemets, soit : 'Ok',
• Fermer la parenthèse de la fonction Si,
• Puis, valider le calcul par CTRL + Entrée pour garder active la cellule du résultat,
• Cliquer et glisser la poignée de la cellule vers le bas jusqu'en ligne 23,

Instantanément, tous les résultats tombent. Chaque mention Ok identifie la ligne de l'enregistrement issu de la première liste pour lequel la correspondance exacte a été trouvée. Mais cette définition n'est pas tout à fait exacte. Par exemple, en ligne 12, le client Sansasse Lionel est considéré comme non concordant. Et pour cause, son adresse est différente dans la seconde source de donnée. Mais si vous copiez son code postal et sa ville sur un autre client du deuxième tableau, la mention Nok se transforme en mention Ok. Et pourtant, il ne s'agit pas du même client. Cela signifie que notre actuelle syntaxe :

=SI(SOMMEPROD((CP=D6)*(Ville=E6))=0; 'Nok'; 'Ok')

Ne recoupe pas toutes les conditions requises. Quatre critères doivent être croises. Il faut ajouter celui sur le nom et celui sur le prénom selon la syntaxe suivante :

=SI(SOMMEPROD((CP=D6)*(Ville=E6)*(Nom=B6)*(Prénom=C6))=0; 'Nok'; 'Ok')

Désormais, chaque client pour lequel la moindre différence est observée, est repéré.

Pour parachever le cas, nous proposons de faire ressortir visuellement les changements détectés entre les deux sources de données. L'essentiel du travail est fait. La règle de mise en forme conditionnelle doit s'appuyer sur les résultats livrés par la formule matricielle.

• Sélectionner toutes les données du premier tableau, soit la plage de cellules B6:E23,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Dans la zone de saisie juste en dessous, taper la syntaxe suivante : =$L6='Nok',

Nous cherchons à repérer chaque enregistrement non concordant, d'où la recherche sur la mention Nok. La cellule L6 est laissée libre de se déplacer en ligne. Ainsi, tous les enregistrements pourront être passés en revue. En revanche, sa colonne reste fixe, d'où la présence du dollar devant l'indice L. Un enregistrement est composé de plusieurs colonnes. Elles vont toutes être analysées tour à tour par la règle. Ce critère ne doit suivre le mouvement qu'à la verticale et non à l'horizontale.

• Cliquer ensuite 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 orange clair par exemple,
• Activer ensuite l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir un rouge foncé pour le texte,
• Valider ces attributs de format par le bouton Ok,
• De retour sur la première boîte de dialogue, valider la création de la règle par le bouton Ok,

Tous les changements étant intervenus entre les deux sources de données sont désormais clairement et dynamiquement identifiés.