Tanagra Data Mining
1 Objectif
ACP (analyse en composantes principales) sous Excel avec la librairie XNUMBERS.
Tout le monde l’a bien compris, le tableur est pour moi avant tout un outil pédagogique pour
l’enseignement de la statistique et du data mining. Les étudiants ne peuvent pas entrer des
commandes ou cliquer frénétiquement au petit bonheur la chance. Ils doivent regarder de près les
formules pour pouvoir les comprendre et les reproduire. Il n’y a pas mieux pour les amener à
décortiquer les différentes étapes du calcul quelle que soit la méthode étudiée.
Nous avions analysé la Régression Linéaire Multiple sous Excel récemment (Régression Linéaire sous
Excel, Mars 2018). Dans ce tutoriel, nous explorons la mécanique d’une autre méthode phare de la
data science (voir Top Data Science and Machine Learning Used in 2017) : l’analyse en composantes
principales (ACP). J’en profiterai pour présenter XNUMBERS, une librairie particulièrement
performante pour le calcul scientifique sous Excel. Elle nous sera utile en particulier pour la
factorisation des matrices à l’aide de la décomposition en valeurs singulières.
2 La librairie XNUMBERS
XNUMBERS est une libraire pour le calcul à très haute précision pour Excel. Il comprend un grand
nombre de fonctions mathématiques et de méthodes numériques. Le projet a été développé à
l’origine par la Foxes Team sous la houlette de Leonardo Volpi. Il a été abandonné en 2008 (version
5.6). Depuis, la librairie a été reprise (version 6.0 et suivantes) par un astronome dont le frère, John
Beyers, a assuré le portage sur les versions les plus récentes d’Excel. Elle est accessible librement et
est plutôt bien documentée, un fichier d’aide au format CHM l’accompagne. J’ai intégré la macro
complémentaire [Link] accessible en ligne ([Link]
dans Excel 2016 - 64 bits, le tout a parfaitement fonctionné1.
3 Données
Le fichier « [Link] » (Feuille « data ») décrit n = 18 véhicules à l’aide de p = 6 variables
(cylindrée, puissance, longueur, largeur, poids et vitesse maximale).
1
Cf. [Link]
30 mars 2018 Page 1/15
Tanagra Data Mining
Modele CYL PUISS LONG LARG POIDS [Link]
Alfasud TI 1350 79 393 161 870 165
Audi 100 1588 85 468 177 1110 160
Simca 1300 1294 68 424 168 1050 152
Citroen GS Club 1222 59 412 161 930 151
Fiat 132 1585 98 439 164 1105 165
Lancia Beta 1297 82 429 169 1080 160
Peugeot 504 1796 79 449 169 1160 154
Renault 16 TL 1565 55 424 163 1010 140
Renault 30 2664 128 452 173 1320 180
Toyota Corolla 1166 55 399 157 815 140
Alfetta 1.66 1570 109 428 162 1060 175
Princess 1800 1798 82 445 172 1160 158
Datsun 200L 1998 115 469 169 1370 160
Taunus 2000 1993 98 438 170 1080 167
Rancho 1442 80 431 166 1129 144
Mazda 9295 1769 83 440 165 1095 165
Opel Rekord 1979 100 459 173 1120 173
Lada 1300 1294 68 404 161 955 140
Figure 1 - Tableau de données - Feuille "data"
Il sert de données d’illustrations dans mon support de cours consacré à l’ACP (RAK, 2013) qui sera
notre principale référencee. Nous pourrons ainsi vérifier nos calculs à chaque stade.
4 ACP sous Excel
L’ACP normée peut être traitée de deux manières : par la diagonalisation de la matrice des
corrélations, ou par la décomposition en valeurs singulières de la matrice des données centrées et
réduites. Nous optons pour cette seconde solution.
4.1 Préparation des données
La première étape passe par le centrage et réduction des variables la matrice des données X, les
valeurs zij de la matrice Z sont calculées comme suit :
𝑥𝑖𝑗 − 𝑥̅𝑗
𝑧𝑖𝑗 =
𝜎𝑗
1 1
Où 𝑥̅𝑗 = ∑𝑛𝑖=1 𝑥𝑖𝑗 est la moyenne de la variable Xj, 𝜎𝑗 = √ ∑𝑛𝑖=1(𝑥𝑖𝑗 − 𝑥̅𝑗 )² son écart-type.
𝑛 𝑛
Nous copions le tableau de données dans une nouvelle feuille « acp-svd » :
30 mars 2018 Page 2/15
Tanagra Data Mining
• A la ligne 21, nous calculons les moyennes. Pour CYL, nous insérons en B21 la formule
=CNUM(xMean(B2:B19)). xMean() est une fonction de XNUMBERS qui effectue les calculs à
haute précision. La fonction renvoie une chaîne de caractères, on la convertit avec CNUM().
• Pour l’écart-type, nous insérons en B22 la formule =CNUM(xStDevP(B2:B19)). xStDevP() calcule
l’écart-type comme ci-dessus.
• Nous complétons les lignes 21 et 22 par copier-coller pour l’ensemble des variables.
• Il nous reste à produire la matrice Z. Pour le premier individu (Alfasud TI) et la première variable
(CYL), nous appliquons en J2 la transformation =(B2-B$21)/B$22. Les références semi-absolues
(voir les positions des $) permettent de compléter le tableau par copier-coller d’une traite.
Figure 2 - Tableau des données centrées et réduites Z (J2:O19) - Feuille "acp-svd"
4.2 Principe de la décomposition en valeurs singulières
La décomposition en valeurs singulières (SVD, singular-value decomposition) est une méthode de
factorisation très populaire en data mining. La matrice Z de dimension (n, p) est décomposée en 3
sous-matrices (RAK, 2013 ; page 20) :
𝑍 = 𝑈𝐷𝑉 𝑇
30 mars 2018 Page 3/15
Tanagra Data Mining
Sous la configuration usuelle où (n > p) : U est de dimension (n, p), elle positionne les individus dans
le nouvel espace de représentation ; V est de dimension (p, p) et permet de situer le rôle des
variables ; D est une matrice diagonale (p, p) et sert à évaluer la qualité de la représentation.
4.3 Qualité de la représentation
Voyons ce qu’il en de la matrice D. Nous utilisons xSVDD(). Comme il s’agit d’une fonction matricielle,
nous devons valider la saisie avec la combinaison de touches CTRL + SHIFT + ENTREE.
En Q3:V8, nous insérons {=CNUM(xSVDD(J2:O19))}. Les accolades { } sont automatiquement ajoutées
par Excel pour signifier que nous avons bien validé une fonction matricielle destinée à compléter
automatiquement une plage de cellules.
Figure 3 - Calcul de la matrice D - Feuille "acp-svd"
En Q10:V10, nous récupérons les valeurs dk sur la diagonale (k = 1, …, 6). Nous en déduisons λk qui
correspond au pouvoir explicatif du facteur, avec (RAK, 2013 ; page 20) :
𝑑𝑘2
𝜆𝑘 =
𝑛
Ainsi, pour le premier facteur nous avons 𝜆1 = 4.4209, etc.
En Q16:V16, nous exprimons la qualité de représentation en proportion d’inertie expliquée (en Q16,
nous avons =Q14/SOMME($Q$14:$V$14) ; la ligne est complétée par copier-coller). Nous observons
30 mars 2018 Page 4/15
Tanagra Data Mining
que les 2 premiers axes permettent de restituer (73.68 + 14.27) = 87.95% de l’information
disponible. Nous nous en tiendrons à ces deux premiers facteurs (K = 2) dans le reste de l’étude.
4.4 Analyse des variables
Pour analyser les variables, nous avons besoin de la sous-matrice V (vjk) issue de la décomposition.
Nous créons une nouvelle feuille « variables ». Nous listons les variables, puis nous insérons en B3:C8
la fonction {=CNUM(xSVDV('acp-svd'!J2:O19))}. Remarques : (1) V possède bien p = 6 lignes, mais elle
est restreinte à K = 2 colonnes dans notre analyse ; (2) la formule prend en entrée la matrice Z des
données centrées réduites (J2:019) située dans la feuille ‘acp-svd’.
Figure 4 - Calcul de la matrice V - Feuille "variables"
Corrélations variables – facteurs. Nous obtenons la corrélation (rjk) des variables (Xj) avec les
facteurs (Fk) via
𝑟𝑗𝑘 = √𝜆𝑘 × 𝑣𝑗𝑘
Dans notre feuille de calcul, après y avoir reporté les valeurs de λk (copier – collage spécial / valeurs)
en B10:C10, nous appliquons =RACINE(B$10)*B3 en D3. Puis nous étendons par copier-coller.
Figure 5 - Corrélations variables-facteurs - Feuille "variables"
30 mars 2018 Page 5/15
Tanagra Data Mining
Qualité de représentation des variables (COS²). La qualité de la représentation d’une variable (COS²)
sur un facteur correspond au carré de la corrélation. Pour chaque variable Xj, la somme des COS² sur
𝑝 2
l’ensemble des p = 6 facteurs potentiels est égale à 1 (∑𝑘=1 𝐶𝑂𝑆𝑗𝑘 = 1).
2 2
𝐶𝑂𝑆𝑗𝑘 = 𝑟𝑗𝑘
Nous passons au format « pourcentage » le contenu des cellules. On note par exemple que PUIS est
parfaitement représentée puisque (78.7% + 14.8%) = 93.5% de l’information qu’elle véhicule (c’est le
cas de le dire) est disponible dans le premier plan factoriel.
Figure 6 - Qualité de représentation des variables (COS²) - Feuille "variables"
Contribution des variables aux axes. La contribution des variables est aussi dérivée de la corrélation,
mais elle est normalisée par l’importance de l’axe :
2
𝑟𝑗𝑘
𝐶𝑇𝑅𝑗𝑘 =
𝜆𝑘
𝑝
Pour chaque axe, la somme des contributions des variables est égale à 1 (∑𝑗=1 𝐶𝑇𝑅𝑗𝑘 = 1).
Figure 7 - Contribution (CTR) des variables aux axes - Feuille "variables"
30 mars 2018 Page 6/15
Tanagra Data Mining
Toutes les variables pèsent peu ou prou de la même manière pour le premier facteur, à l’exception
de [Link] qui, elle, est déterminante pour le second facteur (CTR = 38.4%).
4.5 Analyse des individus
Nous utilisons la matrice U de la décomposition pour obtenir les coordonnées des individus. Nous
créons une nouvelle feuille « individus » et nous y reportons les labels des véhicules. Dans notre cas,
elle est de dimension (n, K) avec K = 2 puisque nous nous en tenons au premier plan factoriel.
Figure 8 - Calcul de la matrice U - Feuille "individus"
En (B3:C20), nous avons inséré {=CNUM(xSVDU('acp-svd'!J2:O19))}, toujours validée par la
combinaison de touche CTRL + SHIFT + ENTREE. Les données centrées-réduites sont récupérées dans
la feuille « acp-svd ».
Coordonnées factorielles des individus. Nous obtenons la coordonnée factorielle Fik de l’individu n°i
sur l’axe n°k par le produit des matrices D et U :
𝐹𝑖𝑘 = 𝑑𝑘 × 𝑢𝑖𝑘
Nous reportons donc les valeurs de dk dans la nouvelle feuille, en B22:C22 (copier – collage spécial /
valeurs). Puis nous créons les deux nouvelles colonnes.
30 mars 2018 Page 7/15
Tanagra Data Mining
Figure 9 - Coordonnées factorielles des individus - Feuille "individus"
Puisque nous sommes sous Excel, nous pouvons construire une graphique nuage de points qui
permet de situer les positions relatives des individus.
Nuage de points des individus
5.0
4.0
3.0
2.0
Peugeot 504 Audi 100
1.0
Renault 16 TL Rancho
Lada 1300 Princess 1800
Simca 1300 Datsun 200L
0.0
Lancia Beta Mazda 9295
-5.0 -4.0 -3.0 -2.0 -1.0 0.0 1.0 2.0 3.0 4.0 5.0
Citroen GS Club Opel Rekord
Toyota Corolla
Taunus 2000
-1.0 Fiat 132
Renault 30
-2.0
Alfasud TI
Alfetta 1.66
-3.0
-4.0
-5.0
Figure 10 - Représentation des individus dans le premier plan factoriel
30 mars 2018 Page 8/15
Tanagra Data Mining
Qualité de représentation des individus (COS²). Pour calculer le COS² de l’individu n°i sur l’axe n°k,
nous devons tout d’abord calculer le carré de la distance à l’origine 𝑜𝑖2 de chaque individu. Il nous
renseigne sur la participation de chaque observation dans l’inertie totale du nuage de points.
𝑜𝑖2 2
= ∑ 𝑧𝑖𝑗
𝑗=1
Nous l’obtenons en effectuant la [Link]() des coordonnées centrées réduites des individus,
lesquelles sont disponibles dans la feuille « acp-svd ».
Figure 11 - Carré des distances à l'origine des individus (𝒐𝟐𝒊 ) - Feuille "individus"
Nous pouvons alors déduire la qualité de représentation des individus :
2
2 𝐹𝑖𝑘
𝐶𝑂𝑆𝑖𝑘 = 2
𝑜𝑖
Que nous mettons en pourcentage. La somme des COS² pour un individu sur l’ensembles des
facteurs est égale à 1.
30 mars 2018 Page 9/15
Tanagra Data Mining
Figure 12 – COS² des individus dans le premier plan factoriel - Feuille "individus"
Contribution des individus aux axes (CTR). La contribution s’appuie toujours sur les coordonnées
2
𝐹𝑖𝑘
factorielles, mais la normalisation est différente : 𝐶𝑇𝑅𝑖𝑘 =
𝑛×𝜆𝑘
Figure 13 - CTR des individus aux facteurs - Feuille "individus"
30 mars 2018 Page 10/15
Tanagra Data Mining
Sans surprise, la Renault 30 et la Toyota Corolla, situés aux deux extrémités, sont déterminants pour
le premier facteur. Le second, lui, repose surtout sur l’Alfetta 1.66 et l’Alfasud TI (ouh là là, c’étaient
des bonnes voitures ça, elles avaient du caractère !).
4.6 Traitement des variables illustratives
Nous souhaitons renforcer l’interprétation des facteurs à l’aide de variables qui n’ont pas participé à
l’étude (RAK, 2013 ; pages 32 et suivantes). Nous avons besoin des coordonnées des individus pour
positionner ces variables dites « illustratives ».
Illustratives quantitatives. Nous créons une nouvelle feuille Excel « [Link] » dans lequel
nous reportons les coordonnées factorielles des individus dans le plan (F1, F2) et les variables
additionnelles PRIX et [Link] (rapport poids-puissance). Nous calculons ensuite les coefficients
de corrélation linéaire entre les facteurs, d’une part, et les variables, d’autre part.
Figure 14 - Positionnement des variables illustratives quantitatives - Feuille "[Link]"
Nous utilisons la fonction [Link]() d’Excel. On peut lire par exemple que le PRIX
est fortement lié au premier axe (corrélation = 0.7725). Ce dernier induit une différenciation des
véhicules selon le prix.
30 mars 2018 Page 11/15
Tanagra Data Mining
Illustratives qualitatives. Les moyennes des facteurs conditionnellement aux modalités des variables
illustratives font l’affaire dans le cas des variables qualitatives. Pour nos données, nous essayons de
qualifier les facteurs à l’aide des finitions (FINITION) des véhicules (Moyen, Bonne, Très Bonne).
Figure 15 - Moyenne des facteurs conditionnellement à FINITION - Feuille "[Link]"
Nous avons utilisé un tableau croisé dynamique pour obtenir les moyennes conditionnelles. A
l’évidence, la différenciation sur les finitions est une lecture possible du premier axe factoriel : de
gauche à droite, les véhicules ont un niveau de finition croissant sur le premier axe, avec des écarts
marqués (plus marqués en tous les cas que sur le second facteur).
4.7 Traitement des individus illustratifs
Nous avons besoin des coordonnées des variables pour positionner les individus illustratifs dans le
plan factoriel (F1, F2), plus précisément des coefficients de la matrice V de la décomposition en
valeurs singulières (Figure 4) (RAK, 2013 ; pages 36 et suivantes). Nous souhaitons situer 2 nouvelles
Peugeot par rapport aux véhicules de notre fichier initial :
Modele CYL PUISS LONG LARG POIDS [Link]
Peugeot 604 2664 136 472 177 1410 180
Peugeot 304 S 1288 74 414 157 915 160
Figure 16 - Caractéristiques des individus illustratifs - Feuille "[Link]"
30 mars 2018 Page 12/15
Tanagra Data Mining
Plusieurs étapes sont nécessaires. Il faut tout d’abord centrer et réduire les descriptions, mais en
utilisant les moyennes et écarts-type calculés sur nos données initiales (Figure 2). Pour un nouvel
𝑥𝑖∗ 𝑗 −𝑥̅ 𝑗
individu i*, nous transformons ses coordonnées (xi*j) à l’aide de : 𝑧𝑖 ∗𝑗 =
𝜎𝑗
Nous récupérons en (B5:G6) les moyennes et écarts-type calculés dans la feuille « acp-svd » (copier –
collage spécial valeurs). Nous appliquons la formule de transformation en (B10:G11) :
Figure 17 - Coordonnées centrées et réduites des individus illustratifs - Feuille "[Link]"
Puis, après avoir copié en (J2:K7) les coefficients de la matrice V en provenance la feuille « variables »
(Figure 4). Nous appliquons la formule :
𝑝=6
𝐹𝑖 ∗ 𝑘 = ∑ 𝑧𝑖 ∗ 𝑗 × 𝑣𝑗𝑘
𝑗=1
Pour la Peugeot 604, elle se traduit en J10 par {=CNUM(xMatMult($B10:$G10;J$2:J$7))}.
Figure 18 - Coordonnées factorielles des individus illustratifs - Feuille "[Link]"
30 mars 2018 Page 13/15
Tanagra Data Mining
xMatMult() de la librairie XNUMBERS est l’équivalent de PRODUITMAT() d’Excel.
Nous pouvons distinguer les Peugeot dans le nuage de points des individus du premier plan factoriel.
Nuage de points des individus
6.0
4.0
2.0
Peugeot 504 Audi 100
Lada 1300 Renault 16 TL Rancho Princess 1800
Simca 1300 Datsun 200L Peugeot 604
0.0 Mazda 9295
-6.0 -4.0 -2.0 Lancia Beta
0.0 2.0 4.0 6.0
Toyota Corolla Citroen GS Club Opel Rekord
Taunus 2000
Peugeot 304 S Fiat 132
Renault 30
-2.0
Alfasud TI Alfetta 1.66
-4.0
-6.0
Figure 19 - Position des individus illustratifs dans le plan factoriel - Feuille "individus - Plan factoriel (2)"
La Peugeot 604 est plutôt un véhicule statutaire, proche de la Renault 30 ; la Peugeot 304 S (le S est
très important) est une compacte sportive, similaire à l’Alfasud TI (c’était vraiment une bonne
voiture). C’est ce que nous dit le graphique factoriel en tous les cas.
30 mars 2018 Page 14/15
Tanagra Data Mining
5 Conclusion
Le but premier de ce tutoriel était pédagogique : décortiquer pas à pas la mécanique de l’ACP en
reproduisant les formules sous le tableur Excel. Les calculs ne sont pas sorciers finalement lorsqu’on
les regarde de plus près. Nous avons pu réaliser une étude complète relativement simplement.
C’était aussi pour moi l’occasion de mettre en avant la librairie XNUMBERS que j’avais découverte en
lisant un ouvrage sur le calcul scientifique sous Excel (de Levie, 2008). Elle est particulièrement
puissante et précise. Nous avons ainsi retrouvé avec un niveau de qualité largement suffisant les
principaux résultats présentés dans mon support dédié à l’ACP (RAK, 2013), où j’avais utilisé les
logiciels phares de la statistique sur les mêmes données.
6 Références
John Beyers, « Xnumbers – Version 6.0 » ([Link]
(RAK, 2013) Ricco Rakotomalala, « Analyse en composantes principales – Diapos », Juillet 2013.
Robert de Levie, « Advanced Excel for scientific data analysis », Oxford University Press, 2008.
Wikipedia, « XNUMBERS ».
30 mars 2018 Page 15/15
