La plupart des tutoriels sur la moyenne pondérée dans Excel s’arrêtent à la formule SOMMEPROD/SOMME tapée dans une cellule. Le calcul fonctionne, mais il faut le réécrire à chaque nouveau fichier, adapter les plages, vérifier les parenthèses.
Depuis qu’Excel 365 a introduit la fonction LAMBDA, il existe un moyen de transformer cette formule en fonction personnalisée, réutilisable comme n’importe quelle fonction native. C’est cet angle qui change la donne pour quiconque manipule régulièrement des moyennes pondérées dans des classeurs différents.
Lire également : Protéger ses fichiers bureautiques avec un mot de passe
Fonction LAMBDA Excel : créer une moyenne pondérée comme fonction native
La fonction LAMBDA, disponible dans Excel 365, permet de définir une formule avec des paramètres nommés, puis de l’enregistrer dans le gestionnaire de noms du classeur. Concrètement, vous créez une fonction baptisée, par exemple, MOYENNEPONDEREE, qui accepte deux arguments : une plage de valeurs et une plage de coefficients.
La définition dans le gestionnaire de noms ressemble à ceci :
Lire également : Pourquoi Kelasamdeteom change la façon de gérer vos workflows ?
=LAMBDA(valeurs;poids;SOMMEPROD(valeurs;poids)/SOMME(poids))
Une fois ce nom créé, vous appelez =MOYENNEPONDEREE(B2:B20;C2:C20) dans n’importe quelle cellule du classeur. La formule se lit comme une fonction Excel native, sans avoir à se souvenir de la combinaison SOMMEPROD/SOMME ni à vérifier l’ordre des plages.
Pour réutiliser cette fonction dans un autre fichier, deux options : copier une feuille du classeur source vers le classeur cible (le nom défini suit la feuille), ou recréer le nom dans le gestionnaire de noms du nouveau fichier. Microsoft documente officiellement LAMBDA comme « user-defined function sans macro » depuis les mises à jour 2023-2024 d’Excel 365, ce qui en fait la méthode la plus pérenne pour une formule réutilisable.
SOMMEPROD et SOMME : la base avant d’aller plus loin
Avant de créer une fonction personnalisée, il faut maîtriser le mécanisme sous-jacent. La formule Excel moyenne pondérée repose sur deux fonctions combinées :
=SOMMEPROD(plage_valeurs;plage_coefficients)/SOMME(plage_coefficients)
SOMMEPROD multiplie chaque valeur par son coefficient correspondant, puis additionne les résultats. SOMME totalise les coefficients. La division donne la moyenne pondérée. C’est l’équivalent exact du calcul mathématique classique.
Pourquoi la colonne intermédiaire pose problème
Certains tutoriels proposent d’ajouter une colonne qui multiplie chaque valeur par son poids, puis de faire la somme de cette colonne divisée par la somme des poids. Le résultat est identique, mais cette approche alourdit le fichier. Chaque nouvelle ligne de données exige d’étendre la colonne intermédiaire. SOMMEPROD élimine cette colonne supplémentaire en effectuant la multiplication en mémoire.
En revanche, la colonne intermédiaire reste utile dans un cas précis : quand vous devez auditer le calcul ligne par ligne, par exemple pour vérifier des notes pondérées transmises à un jury. La transparence du calcul l’emporte alors sur la compacité de la formule.
Moyenne pondérée dans un tableau croisé dynamique Excel
Les formules SOMMEPROD et LAMBDA fonctionnent sur des plages statiques ou des tableaux structurés. Elles ne s’adaptent pas automatiquement quand vous filtrez des données par segment (région, produit, période) dans un tableau croisé dynamique.
La méthode recommandée par les formateurs Excel avancés consiste à :
- Ajouter dans la source de données une colonne calculée qui multiplie chaque valeur par son poids (par exemple,
note × coefficient) - Créer un tableau croisé dynamique à partir de cette source enrichie
- Définir un champ calculé dans le TCD qui divise la somme de la colonne « valeur × poids » par la somme de la colonne « poids »
Ce montage produit une moyenne pondérée dynamique par segment, recalculée automatiquement quand vous modifiez les filtres du TCD. Le paradoxe : ici, la colonne intermédiaire jugée superflue dans une feuille plate devient la pièce centrale du dispositif.
Limites du champ calculé dans un TCD
Le champ calculé d’un tableau croisé dynamique ne gère pas les sous-totaux de la même façon qu’une formule classique. La moyenne pondérée affichée pour un groupe est parfois une moyenne des moyennes (et non une vraie pondération globale). Ce comportement, peu documenté, peut fausser l’analyse si le volume de données varie fortement d’un segment à l’autre. La vérification manuelle sur un échantillon reste nécessaire.
Moyenne pondérée Excel avec filtres et lignes masquées
Un problème fréquent et rarement couvert : quand vous appliquez un filtre automatique sur un tableau, SOMMEPROD continue de calculer sur toutes les lignes, y compris celles masquées par le filtre. Le résultat affiché ne correspond plus à la sélection visible.
La parade repose sur la fonction SOUS.TOTAL. SOUS.TOTAL, avec certains codes de fonction, ignore les lignes masquées par un filtre. L’astuce consiste à :
- Créer une colonne auxiliaire utilisant
SOUS.TOTAL(103;cellule)pour détecter si la ligne est visible (renvoie 1) ou masquée (renvoie 0) - Utiliser cette colonne comme critère dans un SOMMEPROD conditionnel, en ne multipliant les valeurs et les poids que lorsque la colonne auxiliaire vaut 1
- Diviser le tout par un SOMMEPROD équivalent appliqué aux seuls poids des lignes visibles
SOMMEPROD seul ne respecte pas les filtres automatiques, ce qui constitue le piège le plus courant pour les utilisateurs intermédiaires. La colonne SOUS.TOTAL corrige ce comportement sans recourir à du VBA.
Combiner LET et LAMBDA pour une formule lisible et maintenable
La fonction LET, également disponible dans Excel 365, permet de déclarer des variables intermédiaires dans une formule. Combinée à LAMBDA, elle rend la définition de la moyenne pondérée beaucoup plus lisible :
=LAMBDA(v;p;LET(produits;SOMMEPROD(v;p);total_poids;SOMME(p);produits/total_poids))
Les variables produits et total_poids nomment explicitement chaque étape du calcul. Un collègue qui ouvre le gestionnaire de noms comprend la logique sans déchiffrer une formule imbriquée. LET améliore la lisibilité sans changer le résultat.
Cette combinaison LET/LAMBDA constitue aujourd’hui la façon la plus propre de packager une moyenne pondérée réutilisable dans Excel 365. Pour les versions antérieures (Excel 2019, 2016), LAMBDA et LET ne sont pas disponibles : la formule SOMMEPROD/SOMME classique reste alors la seule option native, avec la possibilité de la stocker dans un modèle de classeur (.xltx) pour éviter de la retaper.
Le choix entre ces approches dépend de votre version d’Excel et du degré de partage de vos fichiers. Un classeur envoyé à des collaborateurs utilisant une version sans LAMBDA affichera une erreur #NOM? sur la fonction personnalisée. Tester la compatibilité avant de diffuser un modèle reste la précaution la plus efficace.
