Qu'est-ce que la fonction SOMME.XMY2 ?

Définition

La fonction SOMME.XMY2 calcule la somme des carrés des différences entre chaque paire de valeurs de deux matrices de même taille : Σ(xᵢ - yᵢ)². C'est la base du calcul d'erreur quadratique (MSE, RMSE, R²) en statistiques.

SOMME.XMY2 (SUMXMY2 en anglais) te sert de brique de calcul dès que tu dois évaluer l'écart entre deux séries de données, des prévisions face au réel ou deux jeux de mesures, avant de bâtir un indicateur d'erreur plus parlant comme le RMSE.

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

=SOMME.XMY2(matrice_x; matrice_y)

Clique sur un argument pour aller à son explication.

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.

Lucas, la mascotte du Dojo, inspecte les arguments de la fonction à la loupe

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 comparer

Excel 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.

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és

Elle 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.

Le combo gagnant

Divise le résultat par le nombre de valeurs, prends la racine, et tu obtiens le RMSE en une formule : RACINE(SOMME.XMY2(prévisions; réels) / NB(réels)). L'erreur tombe alors dans la même unité que tes données.

Lucas, la mascotte du Dojo, fait une démonstration pas à pas de la fonction dans Excel

Exemples pratiques pas à pas

Exemple 1

Comment calculer l'erreur quadratique totale d'une prévision avec la fonction SOMME.XMY2

Ton modèle de prévision tourne depuis dix-huit mois et le comité veut savoir s'il mérite qu'on continue à le payer. Tu as, mois par mois, le volume prévu et le volume réellement constaté, avec le modèle utilisé noté à côté. La fonction SOMME.XMY2 résume les dix-huit écarts en un seul indicateur d'erreur, celui-là même qui sert de socle au MSE et au RMSE.

Les étapes

1
Dans une cellule, écris =SOMME.XMY2(.
2
En 1er argument : sélectionne la colonne Prévu, B2:B19. La fonction SOMME.XMY2 étant construite sur une différence élevée au carré, l'ordre des deux plages ne change pas le résultat, car une différence et son opposée ont le même carré.
3
En 2ᵉ argument : sélectionne la colonne Réel, C2:C19. Les deux plages doivent couvrir les mêmes dix-huit lignes, car chaque prévision doit rencontrer le réel de son propre mois, sinon la fonction SOMME.XMY2 mesure des écarts qui n'ont jamais existé.
4
Ferme la parenthèse et appuie sur Entrée.

Au final, ta formule devrait ressembler à ça :=SOMME.XMY2(B2:B19; C2:C19)

Explication

La fonction retranche le réel au prévu ligne par ligne, élève chaque écart au carré, puis additionne les dix-huit carrés. Tout se joue dans la position du carré : il enferme la différence entière, ce qui neutralise les signes (un mois sous-estimé ne compense jamais un mois surestimé) et pénalise lourdement les gros ratés. Le total de 122 sur des volumes qui montent jusqu'à 238 traduit un modèle qui colle de près.

Exemple 2

Comment calculer un RMSE avec les fonctions RACINE, SOMME.XMY2 et NB

Tu présentes la qualité de ton modèle à des gens qui ne feront pas la conversion mentale, et « l'erreur totale vaut 100 » ne veut rien dire pour eux : 100 quoi, sur combien de semaines ? Tu as quatre semaines de prévisions et de réalisations. La fonction SOMME.XMY2 fournit l'erreur brute, et deux opérations suffisent à la transformer en RMSE, l'indicateur qui se lit dans la même unité que tes ventes.

Les étapes

1
Dans une cellule, écris =RACINE(. Cette fonction n'attend qu'un seul nombre, celui qu'elle passera à la racine, et c'est elle qui rendra le résultat lisible dans l'unité d'origine.
2
À l'intérieur, écris SOMME.XMY2(, puis :
1. aEn 1er argument : sélectionne la colonne Prévu, B2:B5. La fonction SOMME.XMY2 retranche le réel au prévu et élève chaque écart au carré.
2. bEn 2ᵉ argument : sélectionne la colonne Réel, C2:C5. Une fois les deux plages en place, la fonction renvoie 100, la somme des quatre écarts au carré.
3
Ferme la parenthèse de SOMME.XMY2, complète par /NB(B2:B5) pour diviser par les 4 semaines et obtenir l'erreur quadratique moyenne, ferme la parenthèse de RACINE et appuie sur Entrée. La racine annule le carré posé au départ et ramène l'erreur dans l'unité des ventes.

Voici la formule que tu obtiens à la fin :=RACINE(SOMME.XMY2(B2:B5; C2:C5)/NB(B2:B5))

Explication

La fonction SOMME.XMY2 renvoie 100, un nombre exprimé en unités au carré que personne ne sait interpréter en réunion. Divisé par les 4 semaines il devient l'erreur quadratique moyenne, et la racine le ramène enfin dans l'unité des ventes : le modèle se trompe typiquement de 5 unités par semaine, un chiffre qu'on peut comparer directement aux volumes prévus.

Exemple 3

Comment comparer deux modèles avec les fonctions SOMME.XMY2 et FILTRE

Deux modèles de prévision cohabitent dans ton tableau, une ligne sur deux, et tu dois en supprimer un avant la fin du trimestre. L'erreur globale ne t'aide pas, puisqu'elle mélange les deux, et découper ton tableau en deux onglets pour un arbitrage serait une perte de temps. La fonction FILTRE isole les lignes d'un modèle à la volée, et la fonction SOMME.XMY2 chiffre l'erreur de ce modèle seul.

Les étapes

1
Dans une cellule, écris =SOMME.XMY2(.
2
En 1er argument : écris la fonction FILTRE appliquée à la colonne Prévu et referme-la, FILTRE(B2:B7; A2:A7="ARIMA"). Elle ne retient que les trois lignes du modèle ARIMA.
3
En 2ᵉ argument : écris la même fonction FILTRE sur la colonne Réel, FILTRE(C2:C7; A2:A7="ARIMA"), pour récupérer les réels des mêmes lignes. La condition doit être rigoureusement identique à celle du premier argument, sinon les deux séries sortent dans un ordre différent et la fonction SOMME.XMY2 apparie des prévisions avec les réels d'autres mois.
4
Ferme la parenthèse de SOMME.XMY2 et appuie sur Entrée. Duplique ensuite la formule en remplaçant "ARIMA" par "ETS" pour obtenir l'erreur du second modèle, lisible sur la même échelle puisqu'elle porte sur le même nombre de mois.

Une fois les morceaux assemblés, ta formule donne ça :=SOMME.XMY2(FILTRE(B2:B7; A2:A7="ARIMA"); FILTRE(C2:C7; A2:A7="ARIMA"))

Explication

Chaque fonction FILTRE ne laisse passer que les trois lignes du modèle ARIMA, et la fonction SOMME.XMY2 travaille sur ces deux séries réduites : 17 d'erreur pour ARIMA contre 22 pour ETS en remplaçant le critère. Les deux modèles ayant ici le même nombre de lignes, la comparaison des sommes brutes est légitime ; sur des effectifs différents, il faudrait passer par le RMSE avant de désigner un gagnant.

Lucas, la mascotte du Dojo, l'air gêné face à une erreur Excel

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.

L’erreur fatale

Contrairement à ses cousines, SOMME.XMY2 ne saute pas les cellules vides : elle les compte comme des zéros. Un blanc face à 150 crée une différence de -150, soit 22 500 ajoutés au total. Filtre tes lignes incomplètes avant de lancer le calcul.

Lucas, la mascotte du Dojo, compare deux fonctions Excel

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

FAQ

Questions fréquentes

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.

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.

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.

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.

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.

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.

Ressources

Pour aller plus loin

Continue sur ta lancée après la fonction SOMME.XMY2 : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.

Tout voir

La leçon

Les formules de base dans Excel

Explorer

Lucas, la mascotte du Dojo, présente la leçon Excel associée

Le guide

Le guide complet pour bien démarrer sur Excel

Explorer

Lucas, la mascotte du Dojo, décolle en fusée pour progresser sur Excel

Nos meilleurs outils IA dédiés à Excel