Une formule moyenne de tableau à travers les lignes doit renvoyer une moyenne ligne par ligne dans Google Sheets. La fonction AVERAGE n’étend pas ses résultats.
Alors comment faire cela ?
Pour coder une formule moyenne de tableau à travers les lignes, nous devons envisager une ou plusieurs solutions alternatives dans Google Sheets.
Pourquoi la fonction AVERAGE n’étend-elle pas ses résultats ?
Une moyenne (moyenne arithmétique) est un seul nombre 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 à considérer dans AVERAGE, la formule ne renverra qu’un seul nombre pour représenter la plage/tableau utilisé.
| Jan | Feb | Mar | Moyenne |
|---|---|---|---|
Par exemple, la formule =average(A2:C2) dans la cellule D2 renverra 75.
Si vous faites glisser cette formule vers le bas, vous obtiendrez 62 dans D3.
Pouvons-nous utiliser une seule formule AVERAGE de tableau dans la cellule D2, qui s’étendra vers le bas ?
Oui ! C’est ce que je voulais dire par le titre Formule moyenne de tableau à travers les lignes dans Google Sheets.
La bonne façon de trouver la moyenne à travers les lignes est d’utiliser les fonctions MMULT, QUERY ou DAVERAGE, dans Google Sheets.
Mise à jour : Nous pouvons maintenant utiliser BYROW pour étendre AVERAGE à travers les lignes. J’ai également ajouté cette solution ci-dessous.
Formule moyenne de tableau à travers les lignes utilisant MMULT – Méthode régulière
Comme vous le savez peut-être, la moyenne est la somme divisée par le nombre d’éléments. Voici donc la logique que je vais suivre.
J’utiliserai MMULT pour faire la somme à travers les lignes et un autre MMULT pour compter à travers les lignes. Les deux formules renverront des résultats de tableau.
La formule un divisée par la formule deux sera le résultat de la moyenne de tableau à travers les lignes.
Vous trouverez ci-dessous deux formules MMULT pour le calcul de la moyenne à travers les lignes – l’une inclut le zéro, et l’autre ne l’inclut pas.
Pour des données d’exemple, veuillez vous référer à l’animation ci-dessus.
Exclusion du zéro
La formule =ArrayFormula(mmult(N(B2:D5),sequence(columns(B2:D2),1)^0)), dans la cellule F2, renverra la somme de chaque ligne. Mais elle a une limitation.
Note : 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, une plage tableau ouverte.
Dans l’un de mes récents tutoriels sur Google Sheets, j’ai expliqué comment surmonter cela. Voici ce tutoriel – Utilisation appropriée de MMULT dans des lignes infinies dans Google Sheets.
Je suis cette approche ici pour modifier la formule ci-dessus.
Formule_1 (Somme):
=ArrayFormula(mmult(N(array_constrain(B2:D,MATCH(2,1/(A2:A<> » »),1),3)),sequence(columns(B2:D2),1)^0))
Note : 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 le remplacer par n’importe quelle autre colonne.
Maintenant, j’espère qu’il vous sera facile d’obtenir le décompte de chaque ligne.
Pour cela, remplacez B2:D dans la partie de la matrice1 de la formule_1 par if(B2:D>0,1,0). Saisissez le code ci-dessous en G2.
Formule_2 (Décompte):
=ArrayFormula(mmult(N(array_constrain(if(B2:D>0,1,0),MATCH(2,1/(A2:A<> » »),1),3)),sequence(columns(B2:D2),1)^0))
Moyenne de tableau à travers les lignes (Exclusion du zéro) :
Voici la formule générique.
=ArrayFormula(IFERROR({« Moyenne Excluant le Zéro »;sum/count}))
Et la formule finale basée sur celle-ci.
=ArrayFormula(IFERROR({« Moyenne Excluant le Zéro »;mmult(N(array_constrain(B2:D,MATCH(2,1/(A2:A<> » »),1),3)),sequence(columns(B2:D2),1)^0)/mmult(N(array_constrain(if(B2:D>0,1,0),MATCH(2,1/(A2:A<> » »),1),3)),sequence(columns(B2:D2),1)^0)}))
Inclusion du zéro
Pour inclure les zéros dans le calcul de la formule moyenne de tableau à travers les lignes, vous devez effectuer un seul changement dans la solution ci-dessus.
Dans la formule ci-dessus, remplacez if(B2:D>0,1,0) par if(len(B2:D),1,0).
=ArrayFormula(IFERROR({« Moyenne Incluant le Zéro »;mmult(N(array_constrain(B2:D,MATCH(2,1/(A2:A<> » »),1),3)),sequence(columns(B2:D2),1)^0)/mmult(N(array_constrain(if(len(B2:D),1,0),MATCH(2,1/(A2:A<> » »),1),3)),sequence(columns(B2:D2),1)^0)}))
Solutions alternatives QUERY et DAVERAGE – Méthode régulière
La formule QUERY est si simple. Je vais donc omettre l’explication.
=query(A1:D, »Select (B+C+D)/3″)
Cette formule a des limitations. Elle inclura les zéros 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 QUERY pour inclure les cellules vides et inclure/exclure les zéros. Mais cela rendra 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 moyenne de tableau à travers les lignes en utilisant BYROW LHF – Nouvelle méthode
Nous pouvons maintenant étendre une formule AVERAGE en utilisant la fonction BYROW Lambda Helper Function (LHF) dans Google Sheets.
C’est la solution la plus simple pour coder une formule moyenne de tableau en expansion dans Google Sheets.
Utilisez la fonction AVERAGE avec BYROW lorsque vous voulez inclure zéro.
={« Moyenne Incluant le Zéro »;byrow(B2:D,lambda(row,iferror(average(row))))}
Pour exclure le zéro dans la formule moyenne de tableau BYROW, utilisez AVERAGEIF à la place.
={« Moyenne Excluant le Zéro »;byrow(B2:D,lambda(row,iferror(averageif(row, »>0″))))}
C’est tout. Merci de votre attention. Profitez-en !
Source: Crawlan.com
