Nous devons suivre une solution de contournement pour calculer la moyenne pondérée des données filtrées ou visibles dans Google Sheets. Pourquoi cela est-il nécessaire ? Les options populaires SUMPRODUCT et AVERAGE.WEIGHTED ne prennent pas en charge l’exclusion des lignes masquées dans le calcul de la moyenne pondérée.
La solution de contournement consiste à identifier les lignes visibles à l’aide d’une combinaison de formules SUBTOTAL et MAP.
Nous utiliserons cela comme une colonne auxiliaire virtuelle dans les deux options mentionnées.
Remarque: Vous pouvez consulter ces fonctions dans mon guide des fonctions Google Sheets.
Avant de filtrer ou de masquer les lignes, comprenons d’abord le calcul régulier de la moyenne pondérée.
Moyenne pondérée = Somme (Valeur_du_point_de_données * Poids) / Somme (Poids)
Supposons que nous ayons acheté différentes quantités d’un produit à différents prix unitaires.
Voyons comment calculer le coût moyen pondéré du produit par unité.
Selon la formule ci-dessus, le résultat sera le suivant :
Moyenne pondérée = Somme (Valeur_du_point_de_données * Poids) / Somme (Poids), soit 207000/129000 = 1,60 USD.
Dans Google Sheets, nous pouvons utiliser les formules suivantes pour ce calcul.
Formule # 1 [AVERAGE.WEIGHTED]:
Syntaxe: AVERAGE.WEIGHTED(valeurs, poids)
=AVERAGE.WEIGHTED(B2:B8,C2:C8)
Formule # 2 [SUMPRODUCT]:
Syntaxe: SUMPRODUCT(valeurs, poids)/sum(poids)
=SUMPRODUCT(B2:B8,C2:C8)/sum(C2:C8)
Comment calculer la moyenne pondérée des données filtrées (visibles) ensuite ?
Calcul de la moyenne pondérée des données filtrées
Nous avons la table ci-dessus et nous voulons exclure les trois derniers achats lors du calcul du coût moyen pondéré du produit par unité.
Nous ne voulons pas supprimer les lignes 6, 7 et 8, mais les masquer en utilisant l’une des options disponibles : regroupement, filtrage, masquage manuel ou slicer.
Le problème est que les deux formules Google Sheets ci-dessus renverront le même résultat car elles ne peuvent pas différencier les lignes visibles et masquées.
Mais il y a un moyen d’exclure les lignes masquées lors du calcul de la moyenne pondérée dans Google Sheets.
La première étape pour calculer la moyenne pondérée des données filtrées consiste à identifier les lignes visibles dans la plage.
Remarque: Ici, filtré signifie visible. Vous pouvez utiliser des méthodes disponibles pour masquer les lignes et rendre uniquement les données nécessaires visibles.
La combinaison MAP et SUBTOTAL suivante permettra de retourner 1 dans les lignes visibles et 0 dans les lignes masquées.
=map(C2:C8,lambda(r,subtotal(103,r)))
Comment inclure cette technique dans les deux formules précédentes ?
Voici l’approche de formule AVERAGE.WEIGHED pour calculer la moyenne pondérée des données filtrées.
Syntaxe: index(AVERAGE.WEIGHTED(valeurs, poids*combo-sous-total-map))
=index(AVERAGE.WEIGHTED(B2:B8,C2:C8*map(C2:C8,lambda(r,subtotal(103,r)))),)
Faites défiler vers le haut et regardez la formule n°1 et la formule juste ci-dessus, en particulier la partie mise en évidence.
Vous pouvez voir que nous avons multiplié le poids par l’identificateur de ligne masquée (formule combo) pour retourner 0 (zéro) dans les lignes masquées.
Nous pouvons également utiliser cette technique dans SUMPRODUCT pour calculer la moyenne pondérée des données visibles ou filtrées dans Google Sheets.
Syntaxe: SUMPRODUCT(valeurs, poids, map_sous-total_combo)/SUMPRODUCT(poids*map_sous-total_combo)
=sumproduct(B2:B8,C2:C8,map(C2:C8,lambda(r,subtotal(103,r))))/sumproduct(C2:C8*map(C2:C8,lambda(r,subtotal(103,r))))
C’est tout. Merci de votre attention. Profitez-en !
