Supprimer des espaces avec la fonction SUPPRESPACE

Quand on importe ou extrait des données dans Excel depuis une source externe (internet, logiciel, PDF, base de données…), il arrive souvent que les cellules contiennent des espaces indésirables : avant, après ou même entre les mots.

Ces espaces peuvent poser problème dans les formules, les tris, les filtres ou les recherches.

Les espaces en début de cellules sont faciles à repérer, mais ceux situés en fin de cellule sont indétectables visuellement.

Heureusement, Excel propose une fonction très simple pour les supprimer automatiquement : SUPPRESPACE.

SUPPRESPACE ne supprime pas tous les espaces, il garde un espace entre chaque mot de la cellule.

SUPPRESPACE s’utilise souvent pour nettoyer des données avant un calcul, un tri, une recherche, une mise en forme et se combine avec de nombreuses fonctions.

SUPPRESPACE

Syntaxe :

 =SUPPRESPACE(texte)

Détail de l’argument :

• texte

C’est la cellule qui contient le texte dont on veut supprimer les espaces inutiles.

Exemple 1 : Nettoyer une base de données suite à une extraction ou un import.

Imaginons une liste de codes produits importés d’un logiciel.

Suite à l'extraction, des espaces parasites ont été importés dans le fichier Excel.

Astuce  :

Pour savoir si vous avez des espaces en trop, vous pouvez utiliser la fonction NBCAR qui compte le nombre de caractères dans une cellule.

Si votre code, par exemple, fait 13 caractères, vous pouvez facilement repérer ceux qui ont des espaces en trop, en effet, les espaces sur Excel sont comptés comme un caractère.

On ajoute une colonne et on insère la fonction NBCAR, ce qui nous permet de voir que des cellules ont plus de 13 caractères.

Cela permet aussi de vérifier, après avoir utilisé la fonction SUPPRESPACE que les espaces ont bien été supprimés.

Note : 

Vous pouvez créer une colonne supplémentaire pour insérer la fonction SUPPRESPACE à côté de la colonne contenant les cellules avec espaces.

Vous pouvez garder cette colonne, mais le mieux est de la supprimer une fois les espaces supprimés.

Pour cela faites un copier-coller spécial Valeurs sur l'ancienne colonne et vous supprimerez la colonne temporaire.

La fonction SUPPRESPACE sera supprimée, cela permet de garder une base propre, sans formule supplémentaire.

On insère la fonction SUPPRESPACE dans la première cellule, puis on incrémente à l'ensemble du tableau, l'incrémentation est automatique si les cellules sont sous forme de tableau.

 Après SUPPESPACE

Les espaces superflus ont bien été supprimés, maintenant toutes les cellules ont bien 13 caractères.

Lire l'article sur la fonction NBCAR.

Combiner SUPPRESPACE avec d’autres fonctions

Il existe de nombreuses possibilités de combinaisons de SUPPRESPACE avec d'autres fonctions de texte.

Nous avons déjà vu NBCAR, vous pouvez aussi combiner SUPPRESPACE avec d'autres fonctions de texte : GAUCHE, DROITE, STXT, MAJUSCULE, MINUSCULE, NOM.PROPRE, SUBSTITUE, REMPLACER, CONCAT...pour nettoyer vos données et améliorer vos fichiers.

Elle est aussi très utile avant d'utiliser des fonctions comme RECHERCHEV/X, elle évite les erreurs du type #N/A souvent causées par un espace en trop.

Nous voyons tout ceci en détails dans la formation Excel :

Utilisez les fonctions de texte pour préparer et nettoyer vos données

Une formation complète sur les fonctions de texte avec des exemples et des problématiques concrets rencontrés en entreprises.

Elle est disponible ici à tarif préférentiel.

En anglais :

La fonction en anglais est TRIM.

Du verbe To Trim qui signifie, rogner, couper les bords.

Voilà, cet article sur la fonction SUPPRESPACE est terminé.

Vous pouvez le commenter et vous abonner au blog si ce n’est pas encore fait, pour recevoir du contenu exclusif réservé aux membres et progresser sur Excel.

Il vous suffit de renseigner votre prénom et votre adresse mail dans le formulaire ci-dessous ou dans la pop-up qui s’affiche parfois.

À bientôt sur le blog Maîtrisez Excel.

Steeve

Maîtrisez SOMMEPROD : de la multiplication simple au filtre multi-critères

SOMMEPROD est une fonction qui permet de réaliser des calculs en multipliant des valeurs de différentes plages et en les additionnant simultanément.

On calcule la somme des produits, d'où le nom SOMMEPROD.

Mais il existe une autre utilisation de la fonction SOMMEPROD, souvent méconnue, qui permet de faire des calculs conditionnels, des statistiques, ou des analyses multicritères comme le feraient les fonctions SOMME.SI.ENS ou NB.SI.ENS mais de manière encore plus flexible.

Découvrons ensemble tout ce que SOMMEPROD peut faire, et pourquoi elle mérite une place de choix dans votre boîte à outils Excel.

SOMMEPROD

Syntaxe :

= SOMMEPROD(plage1; [plage2]; [plage3]; …)

La fonction SOMMEPROD multiplie les éléments de plusieurs plages entre eux, puis additionne les résultats.

Autrement dit, elle fait la somme des produits.

Exemple simple

Vous avez un tableau avec le prix unitaire des produits (P.U HT) et la quantité vendue.

Vous voulez connaitre le CA total.

Formule :

Insérer la fonction SOMMEPROD : 

=SOMMEPROD(A2:A4;B2:B4)

Sélectionnez les valeurs de la colonne Prix unitaire puis les valeurs de la colonne Quantité, séparées par un point-virgule.

Résultat :

Excel multiplie chaque quantité par son prix et fait la somme de tous les montants.

Le calcul est : (19×126) + (69x24) + (57×31) + (119x214) + (97×19)= 9326

C’est la méthode la plus rapide pour calculer un total pondéré sans colonne intermédiaire.

En ajoutant une colonne pour calculer la multiplication sur chaque ligne et en faisant le total général, on retrouve bien le même résultat.

SOMMEPROD pour calculer une moyenne pondérée

Une moyenne pondérée est une moyenne où chaque valeur à un coefficient.

L'exemple le plus classique de moyenne pondérée est le résultat d'un examen.

Par exemple on affecte un coefficient 7 à la note obtenue en maths, 3 à l'anglais, 4 au français etc.

Curieusement, il n'existe pas dans Excel de fonction qui calcule directement la moyenne pondérée.

Mais SOMMEPROD permet de calculer une moyenne pondérée en la combinant avec la fonction SOMME.

Exemple 2 : Calcul de la moyenne pondérée

Le tableau ci-dessous montre les notes obtenues par un élève à un examen.

A chaque note est affecté un coefficient.

Pour calculer la moyenne pondérée, on va faire la somme des produits avec la fonction SOMMEPROD, puis on divisera le résultat par la somme des coefficients avec la fonction SOMME.

Formule :

=SOMMEPROD(B6:B12;C6:C12)/SOMME(C6:C12)

Résultat : 13,79

Bien sûr il existe beaucoup d'autres exemples où l'on doit calculer la moyenne pondérée, par exemple le CUMP (Cout Unitaire Moyen Pondéré) d'un stock.

SOMMEPROD avec conditions

L’une des grandes forces de SOMMEPROD, souvent méconnue, c’est qu’elle peut intégrer des critères logiques (comme les fonctions NB.SI.ENS ou SOMME.SI.ENS), mais de manière plus flexible et plus puissante.

Exemple : condition simple

Le tableau ci-dessous montre les ventes de 6 vendeurs sur 4 régions.

On veut connaitre le CA par région.

Nous allons créer un second tableau pour insérer les fonctions SOMMEPROD.

On commencera avec la région "Nord" qui sera notre premier filtre.

Formule :

J'insère la fonction SOMMEPROD : =SOMMEPROD(

Je sélectionne la colonne des régions : (Tableau1[Région]

J'insère le symbole = et je mets le nom de la région entre guillemets :  ="Nord"

Je ferme la parenthèse puis je mets le symbole * , 

Enfin je sélectionne  la colonne CA : Tableau1[CA])

Note : On remarque qu'on utilise pas de point-virgule ici mais le symbole de multiplication *.

Résultat :

Explication :

Excel évalue la condition ("Nord"), renvoie 1 si vrai, 0 sinon,

puis multiplie par la valeur de la colonne CA ,

et additionne tous les résultats.

Ce fonctionnement matriciel avec un critère de filtre rend SOMMEPROD capable de remplacer SOMME.SI

Cela fonctionne aussi avec plusieurs critères, par exemple la région et le vendeur.

Dans ce cas SOMMEPROD à la même fonction que SOMME.SI.ENS.

Il suffit  d'ajouter le symbole * devant la parenthèse, de sélectionner la colonne correspondant à mon second critère , d'ajouter = et le critère entre guillemets .

Par exemple si je veux connaitre le CA de la vendeuse "Alice" dans la région "Nord", la fonction sera : 

C’est la même logique qu’un SOMME.SI.ENS, mais plus souple : on peut y ajouter des formules ou des fonctions à l’intérieur des critères.

Autres utilisations de SOMMEPROD

La force de SOMMEPROD est les nombreuses utilisations et combinaisons que l'on peut en faire : 

SOMMEPROD pour compter

Même si elle s’appelle SOMMEPROD, cette fonction peut aussi compter des valeurs comme NB.SI ou NB.SI.ENS.

SOMMEPROD pour comparer deux listes

Autre usage pratique : vérifier si deux colonnes contiennent les mêmes valeurs.

SOMMEPROD et logique booléenne

Les critères de SOMMEPROD fonctionnent aussi avec les opérateurs logiques :

• > ou < pour les comparaisons : Par exemple filtrer sur les ventes supérieures à 1000€
• * pour “ET” : Quand on veut faire la somme des produits selon un critère, ce que l'on a vu dans les exemples précédents
• + pour “OU” : Si on veut l'un ou l'autre des critères, on remplace le symbole * par +, par exemple les ventes de la région "Nord" ou "Sud".

SOMMEPROD et combinaisons avec d'autres fonctions

SOMMEPROD peut être encore plus puissante combinée avec d'autres fonctions.

SOMMEPROD peut être combinée avec :

• MOYENNE.SI pour calculer une moyenne pondérée sur un critère, exemple le coût moyen pondéré d'un article en stock,
• GAUCHE, DROITE, TEXTE.AVANT, CHERCHE … pour filtrer selon des conditions textuelles,
• MOIS, ANNEE, DATE pour filtrer sur des critères de temps, par exemple le CA total sur l'année 2025
• ARRONDI, ARRONDI.INF...
• SI, SI.CONDITIONS, SI.MULTIPLE pour des analyses précises basées sur des conditions.
• Et beaucoup d'autres fonctions...

 Tout ceci est vu en détails dans la fonction : Utilisation avancée des fonctions Excel.

Pourquoi SOMMEPROD est si puissante ? 

• Elle combine plusieurs critères librement sans limites de syntaxe.
• Elle évite d’utiliser des colonnes intermédiaires.
• Elle peut remplacer plusieurs fonctions conditionnelles.
• Elle est compatible avec toutes les versions d’Excel.
• Elle peut être combinée avec un grand nombre de fonctions.

En anglais :

La fonction SOMMEPROD s’appelle SUMPRODUCT en anglais.

Conclusion :

La fonction SOMMEPROD permet bien plus que de simplement calculer le produit de 2 colonnes comme son nom l’indique.

On peut calculer en filtrant sur des critères, compter des valeurs etc

C’est une des rares fonctions à pouvoir “penser en matrice”, c’est-à-dire traiter plusieurs conditions et colonnes simultanément.

Voilà, cet article sur la fonction SOMMEPROD est terminé.

Si vous voulez vous entrainer et maitriser la fonction SOMMEPROD, abonnez-vous au blog si ce n’est pas encore fait, pour recevoir du contenu exclusif réservé aux membres et progresser sur Excel.

Il vous suffit de renseigner votre prénom et votre adresse mail dans le formulaire ci-dessous ou dans la pop-up qui s’affiche parfois.

À bientôt sur le blog Maîtrisez Excel.

Steeve