Une formule de moyenne d’un tableau sur plusieurs lignes doit renvoyer une moyenne ligne par ligne dans Google Sheets. La fonction MOYENNE n’étend pas ses résultats.
Alors comment faire?
Donc, pour coder une formule de moyenne d’un tableau sur plusieurs lignes, nous devons envisager une ou plusieurs solutions alternatives dans Google Sheets.
Pourquoi la fonction Moyenne n’étend-elle pas ses résultats?
Une moyenne (moyenne) est un nombre unique représentant un tableau ou une plage de nombres.
Si vous utilisez A2:C2 ou A2:C3 (veuillez consulter le tableau ci-dessous) comme plage à prendre en compte dans la MOYENNE, la formule ne renverra qu’un seul nombre pour représenter le tableau/plage utilisé.
| Jan | Fév | Mars | Moyenne |
|---|---|---|---|
Cela signifie que la formule =moyenne(A2:C2) dans la cellule D2 renverra 75.
Si vous faites glisser cette formule vers le bas, vous obtiendrez 62 en D3.
Pouvons-nous utiliser une seule formule moyenne de tableau dans la cellule D2 qui s’étendra vers le bas?
Oui! C’est ce que je voulais dire par le titre « Formule de moyenne d’un tableau sur plusieurs lignes dans Google Sheets ».
La bonne façon de trouver la moyenne sur plusieurs lignes est d’utiliser les fonctions MMULT, QUERY ou DAVERAGE dans Google Sheets.
Mise à jour : Nous pouvons maintenant utiliser BYROW pour étendre la MOYENNE sur plusieurs lignes. J’ai également ajouté cette solution ci-dessous.
Formule de moyenne d’un tableau sur plusieurs lignes en utilisant MMULT – Méthode classique
Comme vous le savez peut-être, la moyenne est la somme divisée par le nombre d’éléments. Je suis donc en train d’utiliser la logique suivante.
J’utiliserai un MMULT pour la somme sur plusieurs lignes et un autre MMULT pour le compte sur plusieurs lignes. Les deux formules renverront des tableaux de résultats.
La formule un divisée par la formule deux donnera le résultat du tableau moyen sur plusieurs lignes.
Vous trouverez ci-dessous deux formules MMULT pour le calcul de la moyenne sur plusieurs lignes – l’une inclut zéro et l’autre ne l’inclut pas.
Pour des données d’exemple, veuillez vous référer à l’animation ci-dessus.
Exclure le zéro
La formule =ArrayFormula(prod(B2:D5),sequence(columns(B2:D2),1)^0)), dans la cellule F2, renverra la somme de chaque ligne. Mais elle a une limitation.
Remarque: Vous pouvez utiliser transpose(sign(column(B:D)))) à la place de sequence(columns(B2:D2),1)^0)).
Généralement, dans le MMULT ci-dessus, nous ne pouvons utiliser qu’une plage fixe comme B2:D5 dans la formule, pas B2:D, un tableau/plage ouvert.
Dans l’un de mes tutoriels récents sur Google Sheets, j’ai expliqué comment surmonter cela. Voici ce tutoriel – Bonne utilisation de MMULT dans les lignes infinies de Google Sheets.
Je suis en train de suivre cette approche ici pour modifier la formule ci-dessus.
Formule_1 (Somme) :
=FormuleMatricielle(mmult(N(array_contraindre(B2:D,MATCH(2,1/(A2:A<>""),1),3)),sequence(columns(B2:D2),1)^0))
Remarque: Cette formule utilise des textes dans A2:A pour connaître la dernière ligne de la plage et ainsi fonctionner dans une plage ouverte. Vous pouvez la remplacer par n’importe quelle autre colonne.
Maintenant, j’espère que vous trouverez facile d’obtenir le compte de chaque ligne.
Pour cela, remplacez B2:D dans la partie matrice1 de la formule_1 par si(B2:D>0,1,0). Entrez le code ci-dessous en G2.
Formule_2 (Count) :
=FormuleMatricielle(mmult(N(array_contraindre(if(B2:D>0,1,0),MATCH(2,1/(A2:A<>""),1),3)),sequence(columns(B2:D2),1)^0))
Formule de moyenne d’un tableau sur plusieurs lignes (excluant zéro):
Voici la formule générique.
=FormuleMatricielle(SIERR({ "Moyenne Excluant Zéro" ; somme / dénombrement }))
Et la formule finale basée dessus.
=FormuleMatricielle(SIERR({mmult(N(array_contraindre(B2:D,MATCH(2,1/(A2:A<>""),1),3)),sequence(columns(B2:D2),1)^0)/mmult(N(array_contraindre(if(B2:D>0,1,0),MATCH(2,1/(A2:A<>""),1),3)),sequence(columns(B2:D2),1)^0)}))
Inclure le zéro
Pour inclure les zéros dans le calcul de la formule de moyenne du tableau sur plusieurs lignes, vous devez effectuer un seul changement dans la solution ci-dessus.
Dans la formule ci-dessus, remplacez si(B2:D>0,1,0) par si (len(B2:D),1,0).
=FormuleMatricielle(SIERR({ "Moyenne Incluant Zéro" ; mmult(N(array_contraindre(B2:D,MATCH(2,1/(A2:A<>""),1),3)),sequence(columns(B2:D2),1)^0)/mmult(N(array_contraindre(if(len(B2:D),1,0),MATCH(2,1/(A2:A<>""),1),3)),sequence(columns(B2:D2),1)^0)}))
Alternatives aux solutions de QUERY et DAVERAGE – Méthode classique
La formule QUERY est très simple. Je vais donc sauter l’explication.
=query(A1:D,"Sélectionner (B+C+D)/3")
Cette formule a des limitations. Elle inclura les 0 dans le calcul de la moyenne. De plus, si une cellule est vide, elle ne renverra pas le résultat dans cette ligne.
Nous pouvons modifier cette formule de QUERY pour inclure les cellules vides et inclure/exclure les zéros. Mais cela rendrait cette requête complexe.
J’ai une autre formule à cette fin que je classerais au-dessus de MMULT – Moyenne de chaque ligne dans une plage dynamique dans Google Sheets.
Formule de moyenne d’un tableau sur plusieurs lignes en utilisant BYROW LHF – Nouvelle méthode
Nous pouvons maintenant étendre une formule MOYENNE en utilisant la fonction BYROW Lanmbda Helper Function (LHF) dans Google Sheets.
C’est la solution la plus facile pour coder une formule de tableau de moyenne en expansion dans Google Sheets.
Utilisez la fonction MOYENNE avec BYROW lorsque vous voulez inclure zéro.
=SIERR({"Moyenne Incluant Zéro" ; byrow(B2:D,Lambda(Ligne,sierr(moyenne(Ligne))))})
Pour exclure le zéro dans la formule de tableau de moyenne BYROW, utilisez AVERAGEIF à la place.
=SIERR({"Moyenne Excluant Zéro" ; byrow(B2:D,Lambda(Ligne,sierr(moyenne.si(Ligne,">0"))))})
C’est tout. Merci de rester. Amusez-vous bien!
