Bonjour les amis ! Aujourd’hui, je vais vous montrer comment retourner la moyenne de chaque n cellules dans une colonne sur Google Sheets. Vous allez adorer cette astuce !
Introduction
Récemment, l’un de mes lecteurs m’a posé une question intéressante dans les commentaires d’un de mes articles. Il voulait savoir s’il était possible d’écrire une formule matricielle pour retourner la moyenne de chaque n cellules dans une colonne adjacente, sans avoir à utiliser un script Google Apps pour améliorer les performances.
Au lieu de simplement lui donner une formule complexe en réponse à son commentaire, j’ai décidé d’écrire un tutoriel complet pour lui et pour vous, mes chers lecteurs.
Crédibilité
En tant qu’expert en Google Sheets et propriétaire du site Crawlan.com, je peux vous assurer que cette astuce fonctionne parfaitement. Vous pouvez me faire confiance, j’ai l’expérience et l’expertise nécessaires pour vous fournir des informations fiables.
L’astuce en action
Voici un aperçu de ce que nous allons accomplir : image 1
Dans la cellule F2, vous allez trouver une formule matricielle qui retourne la moyenne de 1, 2, 3, 4, 5… cellules, en fonction de la valeur dans la cellule H2. En d’autres termes, la valeur dans la cellule H2 contrôle le nombre de cellules utilisées pour calculer la moyenne.
Étape 1 : Réorganiser une seule colonne en plusieurs colonnes
Dans la cellule I2, nous allons utiliser la formule suivante pour ajuster une colonne (E2:E) en n (H2) colonnes :
=ArrayFormula(IFERROR(hlookup("test",E1:E,SEQUENCE(roundup(COUNTA(E1:E)/H2),H2,2),0)))
L’image ci-dessous vous montre le résultat de cette étape : image 2
Étape 2 : Utiliser MMULT pour calculer la moyenne de chaque n cellules
Nous voulons trouver la moyenne de chaque 3 cellules, comme indiqué dans l’image #2 ci-dessus. La formule précédente réorganise les valeurs de la colonne E dans les colonnes I à K (1 colonne à 3 colonnes).
Si nous utilisons la fonction AVERAGE dans la cellule L2 avec la formule =average(I2:K2), nous aurons bien la moyenne de chaque 3 cellules de la colonne E, mais ce n’est pas ce que nous voulons. Nous voulons une formule matricielle qui retourne la moyenne de chaque n cellules.
Pour cela, nous allons utiliser la fonction MMULT, qui effectue une multiplication matricielle. La formule suivante utilise la formule HLOOKUP (étape #1) comme matrice1 et une formule SEQUENCE comme matrice2 pour obtenir la somme de chaque n cellules :
=ArrayFormula(mmult(N(IFERROR(hlookup("test",E1:E,SEQUENCE(roundup(COUNTA(E1:E)/H2),H2,2),0))),sequence(H2,1)^0))
Maintenant, pour obtenir la moyenne de chaque n cellules, vous devez simplement diviser le résultat de la formule MMULT par n (valeur dans la cellule H2). Et voilà ! Vous avez la moyenne de chaque n cellules dans Google Sheets.
Étape 3 : Utiliser une séquence de nombres en fonction du nombre de cellules n dans la colonne E
Nous allons utiliser la formule suivante pour insérer une séquence de nombres dans la colonne J en fonction du nombre de cellules n dans la colonne E :
=SEQUENCE(mround(count(E2:E),H2)/H2,1)
Maintenant, dans la cellule L2, nous allons utiliser la formule MOD pour obtenir une colonne avec des nombres, où chaque cellule tous les n nombres aura la valeur 0 :
=ArrayFormula(mod(sequence(mround(count(E2:E),H2),1),H2))
Étape 4 : Répartir la moyenne dans chaque n cellules
Dans la cellule M2, nous allons insérer la formule de comptage de la valeur 0 dans la colonne L. Cette formule utilisera la fonction COUNTIFS pour obtenir le compteur de tous les nombres en cours d’exécution. Ne manquez pas de jeter un coup d’œil à la colonne M dans l’image ci-dessous :
=ARRAYFORMULA(if(indirect("L2:L"&mround(count(E2:E),H2)+1)=0,COUNTIFS(L2:L,L2:L,ROW(L2:L),"<="&ROW(L2:L)),))
Le résultat obtenu dans la colonne M servira de clé de recherche pour la fonction VLOOKUP dans la cellule F2. La plage de recherche sera le résultat MMULT.
Voici la formule finale en une seule formule matricielle dans la cellule F2 qui retourne la moyenne de chaque n cellules dans Google Sheets :
=ArrayFormula(IFNA(vlookup(if(mod(sequence(mround(count(E2:E),H2),1),H2)=0,COUNTIFS(mod(sequence(mround(count(E2:E),H2),1),H2),mod(sequence(mround(count(E2:E),H2),1),H2),sequence(mround(count(E2:E),H2),1),"<="&sequence(mround(count(E2:E),H2),1)),),{sequence(mround(count(E2:E),H2)/H2,1),array_constrain(mmult(N(IFERROR(hlookup("test",E1:E,SEQUENCE(roundup(COUNTA(E1:E)/H2),H2,2),0))),sequence(H2,1)^0)/H2,count(sequence(mround(count(E2:E),H2)/H2,1)),1)},2,0)))
Voilà ! Vous avez maintenant une formule matricielle qui retourne la moyenne de chaque n cellules dans Google Sheets.
N’oubliez pas de garder quelques cellules supplémentaires (n cellules supplémentaires) à la fin des valeurs dans la colonne E. Vous pouvez copier ma feuille de calcul d’exemple ici, pour voir la formule en action.
J’espère que cette astuce vous sera utile ! Si vous avez des questions, n’hésitez pas à les poser dans les commentaires ci-dessous. Bonne utilisation de Google Sheets !
Articles liés
- Group and Average Unique Column in Google Sheets
- Average IF: Find Average Based on Condition in Google Sheets
- Averageifs Multiple Criteria Function in Google Sheets
- Find the Average of Visible Rows in Google Sheets
Note pour l’utilisation :
- Gardez toujours quelques lignes supplémentaires (n lignes supplémentaires) à la fin des valeurs dans la colonne E.
- Copiez simplement ma feuille d’exemple pour voir la formule en action.
J’espère que cette astuce vous sera utile ! Si vous avez des questions, n’hésitez pas à les poser dans les commentaires ci-dessous. Bonne utilisation de Google Sheets !
