SOMME.XMY2 (SUMXMY2 en anglais) mesure l'écart global entre deux séries de données en calculant la somme des carrés des différences. Elle répond à la question « à quel point mes prédictions s'écartent-elles de la réalité ? » ou « ces deux séries de mesures sont-elles vraiment proches ? ».
Que tu travailles en data science pour évaluer ton modèle de prévision des ventes, en contrôle qualité pour quantifier la dérive de fabrication, ou en contrôle de gestion pour mesurer l'écart entre objectifs et réalisations, SOMME.XMY2 te donne en une seule formule la base du calcul d'erreur quadratique : incontournable pour qui compare des séries chiffrées.
Syntaxe de la fonction SOMME.XMY2
=SOMME.XMY2(matrice_x; matrice_y)Les deux matrices doivent avoir exactement le même nombre d'éléments, sinon Excel retourne #N/A. Les cellules vides sont traitées comme des zéros, ce qui peut fausser le résultat si tu as des données manquantes.
Comprendre chaque paramètre de la fonction SOMME.XMY2
matrice_x
: ta première série de données : prédictions, valeurs cibles, mesures de référence, ou toute série numérique que tu veux comparerExcel accepte une plage de cellules (A2:A10), un tableau nommé, ou des valeurs saisies directement entre accolades {1;2;3}.
Les cellules contenant du texte ou des erreurs provoquent #VALEUR!. Les cellules vides valent zéro.
Astuce : Utilise NB(matrice_x) pour compter les valeurs numériques. Si NB(matrice_x)=NB(matrice_y), tes plages ont la même taille et la formule s'exécutera sans erreur #N/A.
matrice_y
: ta deuxième série de données, celle que tu compares à la première : observations réelles, mesures effectuées, résultats réalisésElle doit contenir exactement le même nombre de valeurs que matrice_x.
La fonction calcule (x₁-y₁)² + (x₂-y₂)² + ... + (xₙ-yₙ) : chaque différence est élevée au carré (ce qui la rend toujours positive et accentue les grands écarts), puis toutes les valeurs sont additionnées.
Attention : Si matrice_x et matrice_y n'ont pas la même taille, la formule retourne #N/A sans aucun avertissement préalable. Vérifie toujours que tes deux plages commencent et finissent sur la même ligne.
Exemples pratiques pas à pas
Data analyst : évaluer la précision d'un modèle de prévision
Tu es data analyst dans une entreprise de retail. Tu as créé un modèle de prévision des ventes mensuelles et tu veux mesurer sa précision avant de le présenter à la direction. SOMME.XMY2 te donne l'erreur quadratique totale en une formule.
| A | B | C | |
|---|---|---|---|
| 1 | Mois | Prévision | Réel |
| 2 | Janvier | 1 000 | 980 |
| 3 | Février | 1 200 | 1 250 |
| 4 | Mars | 1 100 | 1 080 |
| 5 | Avril | 1 300 | 1 290 |
=SOMME.XMY2(B2:B5; C2:C5)La fonction calcule (1000-980)² + (1200-1250)² + (1100-1080)² + (1300-1290)² = 400 + 2500 + 400 + 100 = 3400. Pour obtenir le RMSE (erreur en unités réelles), divise par le nombre d'observations et prends la racine : =RACINE(3400/4), soit environ 29 ventes par mois.
Astuce de pro : Divise toujours par le nombre de valeurs pour obtenir une métrique comparable entre modèles de tailles différentes : =RACINE(SOMME.XMY2(prévisions; réels)/NB(réels)). Ce RMSE te donne l'erreur dans la même unité que tes données.
Responsable qualité : quantifier la dérive de fabrication
Tu es responsable qualité dans une usine de pièces mécaniques. Tes pièces doivent mesurer exactement 50,00 mm. Tu prélèves des échantillons et veux quantifier la dérive globale du processus par rapport à la cible.
| A | B | C | |
|---|---|---|---|
| 1 | Pièce | Cible (mm) | Mesuré (mm) |
| 2 | P1 | 50,00 | 50,02 |
| 3 | P2 | 50,00 | 49,98 |
| 4 | P3 | 50,00 | 50,05 |
| 5 | P4 | 50,00 | 49,97 |
| 6 | P5 | 50,00 | 50,01 |
=SOMME.XMY2(B2:B6; C2:C6)La fonction somme les carrés des écarts à la cible : (50,00-50,02)² + ... = 0,0043. Le RMSE =RACINE(0,0043/5) vaut environ 0,029 mm : tes pièces s'écartent en moyenne de moins de 0,03 mm de la cible. Si ce chiffre augmentait sur plusieurs séries, ce serait le signal qu'il faut calibrer la machine.
Contrôleur de gestion : mesurer l'écart objectifs/réalisations
Tu es contrôleur de gestion et tu veux évaluer si les objectifs commerciaux fixés pour le trimestre étaient réalistes. Un RMSE faible indique des objectifs bien calibrés ; un RMSE élevé révèle soit des objectifs trop ambitieux, soit trop faciles.
| A | B | C | |
|---|---|---|---|
| 1 | Vendeur | Objectif (k€) | Réalisé (k€) |
| 2 | Alice | 150 | 165 |
| 3 | Bob | 200 | 185 |
| 4 | Claire | 180 | 190 |
| 5 | David | 160 | 155 |
| 6 | Emma | 175 | 180 |
=SOMME.XMY2(B2:B6; C2:C6)Avec un total de 600, le RMSE =RACINE(600/5) vaut environ 11 k€ par vendeur. C'est raisonnable pour des objectifs autour de 150-200 k€. Si ce chiffre dépassait 40-50 k€, il faudrait revoir la méthode de fixation des objectifs pour le trimestre suivant.
Technicien de laboratoire : valider la cohérence de deux instruments
Tu es technicien de laboratoire. Ton labo vient d'acquérir un nouvel instrument de mesure et tu dois vérifier qu'il est cohérent avec l'appareil existant avant de valider sa mise en service. Tu mesures les mêmes échantillons avec les deux méthodes.
| A | B | C | |
|---|---|---|---|
| 1 | Échantillon | Méthode A | Méthode B |
| 2 | E1 | 25,3 | 25,5 |
| 3 | E2 | 30,1 | 30,0 |
| 4 | E3 | 28,7 | 28,9 |
| 5 | E4 | 32,4 | 32,2 |
| 6 | E5 | 27,9 | 28,1 |
| 7 | E6 | 29,5 | 29,4 |
=SOMME.XMY2(B2:B7; C2:C7)Le total vaut 0,18, d'où un RMSE =RACINE(0,18/6) d'environ 0,17 unité : les deux instruments diffèrent en moyenne de 0,17 unité seulement. Si ce résultat est inférieur à la tolérance de ton protocole d'étalonnage, le nouvel appareil est validé.
Envie de t'entraîner sur de vrais exercices Excel ?
M'entraîner
Les erreurs fréquentes avec la fonction SOMME.XMY2
#N/A : tailles de matrices différentes
Quand matrice_x et matrice_y n'ont pas le même nombre d'éléments, Excel ne peut pas calculer les différences terme à terme et retourne #N/A sans explication.
Solution : Vérifie que tes deux plages commencent et finissent à la même ligne : =SOMME.XMY2(A2:A10; B2:B10) est correct, =SOMME.XMY2(A2:A10; B2:B9) retourne #N/A. Utilise NB(A2:A10) et NB(B2:B10) pour comparer les tailles avant de lancer la formule.
#VALEUR! : données non numériques
Une cellule de matrice_x ou matrice_y contient du texte ou une erreur. SOMME.XMY2 nécessite exclusivement des valeurs numériques.
Solution : Nettoie tes données avec CNUM() pour convertir les nombres stockés en texte. Utilise SI(ESTNUM(A1); A1; 0) pour neutraliser les cellules problématiques sans changer les autres.
Résultat faussé par des cellules vides
Les cellules vides sont traitées comme des zéros. Si ton tableau contient des données manquantes, elles introduisent des différences artificielles (par exemple, 0 - 150 = -150, soit 22 500 ajoutés au total).
Solution : Filtre les lignes incomplètes avant d'utiliser SOMME.XMY2, ou remplace les cellules vides par des valeurs neutres. Pour vérifier : =NB.VIDE(matrice_x)+NB.VIDE(matrice_y) doit retourner 0.
Confusion avec SOMME.X2MY2
SOMME.XMY2 calcule Σ(x-y)² (somme des carrés des différences), tandis que SOMME.X2MY2 calcule Σ(x²-y²) (somme des différences de carrés). Ce sont deux calculs mathématiquement distincts.
Solution : Utilise SOMME.XMY2 quand tu veux mesurer des écarts entre deux séries (erreur quadratique). Utilise SOMME.X2MY2 pour des calculs d'algèbre linéaire ou de physique impliquant des différences de carrés.
SOMME.XMY2 vs SOMME.X2MY2 vs SOMME.X2PY2 vs SOMME.CARRES
Ces quatre fonctions portent des noms proches mais calculent des choses très différentes. SOMME.XMY2 est la seule à mesurer un écart entre deux séries ; les autres sont des outils d'algèbre ou de statistique.
| Critère | SOMME.XMY2 | SOMME.X2MY2 | SOMME.X2PY2 | SOMME.CARRES |
|---|---|---|---|---|
| Formule mathématique | Σ(xᵢ - yᵢ)² | Σ(xᵢ² - yᵢ²) | Σ(xᵢ² + yᵢ²) | Σxᵢ² (une seule série) |
| Usage principal | Erreur quadratique (RMSE, MSE) | Algèbre, identité (a²-b²) | Distances, hypoténuses | Somme des carrés bruts |
| Nombre de séries | 2 | 2 | 2 | 1 (ou plusieurs, non appairées) |
| Résultat toujours ≥ 0 ? | Oui | Non (peut être négatif) | Oui | Oui |
Questions fréquentes sur la fonction SOMME.XMY2
À quoi sert SOMME.XMY2 en analyse de données ?
SOMME.XMY2 calcule la somme des carrés des différences entre deux séries. C'est la base du calcul d'erreur quadratique, qui mesure l'écart entre tes prédictions et les observations réelles, ou entre deux séries de mesures.
En la divisant par le nombre d'observations et en prenant la racine carrée, tu obtiens le RMSE : une métrique largement utilisée en machine learning, contrôle qualité et prévision pour comparer la précision de différents modèles.
Quelle différence entre SOMME.XMY2, SOMME.X2MY2 et SOMME.X2PY2 ?
SOMME.XMY2 calcule Σ(x-y)², SOMME.X2MY2 calcule Σ(x²-y²), et SOMME.X2PY2 calcule Σ(x²+y²). La première mesure les écarts entre deux séries ; les deux autres effectuent des opérations d'algèbre sur les carrés.
Utilise SOMME.XMY2 pour mesurer une erreur quadratique. Utilise SOMME.X2MY2 ou SOMME.X2PY2 pour des calculs d'algèbre linéaire ou de physique.
Pourquoi SOMME.XMY2 retourne #N/A ?
L'erreur #N/A apparaît quand tes deux matrices n'ont pas la même taille. Les deux plages doivent contenir exactement le même nombre d'éléments pour calculer les différences terme à terme.
Vérifie que NB(matrice_x) est égal à NB(matrice_y). Si tes plages contiennent des cellules vides, NB() compte uniquement les valeurs numériques et peut donner des résultats trompeurs.
Comment calculer le RMSE avec SOMME.XMY2 ?
Le RMSE (Root Mean Square Error) se calcule en trois étapes : somme des carrés des écarts, division par le nombre de valeurs, puis racine carrée. Tout en une formule : =RACINE(SOMME.XMY2(prévisions; réels)/NB(prévisions)).
Le RMSE est exprimé dans la même unité que tes données d'origine (euros, millimètres, unités vendues), ce qui facilite son interprétation.
SOMME.XMY2 fonctionne-t-elle avec des valeurs négatives ?
Oui. Comme SOMME.XMY2 élève les différences au carré, le résultat est toujours positif ou nul, quelles que soient les valeurs dans tes matrices. Une différence de -5 contribue autant à la somme qu'une différence de +5 : les deux donnent 25.
C'est précisément pour cela que cette métrique est utile : les écarts positifs et négatifs ne se compensent pas.
Comment calculer le R² avec SOMME.XMY2 ?
Le coefficient de détermination R² mesure la qualité d'ajustement de ton modèle entre 0 et 1. La formule est : =1-SOMME.XMY2(observations; prédictions)/SOMME.XMY2(observations; TABLEAU(MOYENNE(observations))) ou plus simplement =COEFFICIENT.DETERMINATION(observations; prédictions) si tu utilises uniquement une régression linéaire.
Un R² proche de 1 signifie que ton modèle explique bien la variabilité des données.
Pour aller plus loin
Bloqué sur une formule Excel ?
Pose ta question à notre assistant Excel IA, il te sort la bonne formule en quelques secondes.
Essayer l'assistant IAGratuit · 10 questions par mois