ArrayFormula of Average on Infinite Truly Dynamic Range dans Google Sheets

Dec 24 2020

comme par exemple:

     A       B      C     D     E     F     G     ∞
  |======|=======|=====|=====|=====|=====|=====|=====
1 |      |AVERAGE|     |     |     |     |     |        
  |======|=======|=====|=====|=====|=====|=====|=====
2 | xx 1 |       |   1 |   2 | 0.5 |  10 |     |        
  |======|=======|=====|=====|=====|=====|=====|=====
3 | xx 2 |       |   7 |   1 |     |     |     |       
  |======|=======|=====|=====|=====|=====|=====|=====
4 |      |       |   0 |     |     |     |     |       
  |======|=======|=====|=====|=====|=====|=====|=====
5 | xx 3 |       |   9 |   8 |   7 |   6 |     |       
  |======|=======|=====|=====|=====|=====|=====|=====
6 | xx 4 |       |   0 |   1 |   2 |   1 |     |       
  |======|=======|=====|=====|=====|=====|=====|=====
7 |      |       |   1 |     |   4 |     |     |       
  |======|=======|=====|=====|=====|=====|=====|=====
8 | xx 5 |       |     |     |     |     |     |       
  |======|=======|=====|=====|=====|=====|=====|=====
9 |      |       |     |     |     |     |   5 |           
  |======|=======|=====|=====|=====|=====|=====|=====
∞ |      |       |     |     |     |     |     |       

Quelle est la manière la plus optimale d'obtenir AVERAGEpour chaque ligne valide au sens dynamique des termes (quantité inconnue de lignes et quantité inconnue de colonnes)?

Réponses

4 player0 Dec 24 2020 at 14:34

METTRE EN DOUTE

niveau 1:

si les 5 cellules de la plage C2: G ont des valeurs:

=QUERY(QUERY(C2:G, "select (C+D+E+F+G)/5"), "offset 1", )

sinon, les lignes sont ignorées:

si les cellules vides sont considérées comme des zéros:

=INDEX(QUERY(QUERY({C2:G*1}, "select (Col1+Col2+Col3+Col4+Col5)/5"), "offset 1", ))

pour supprimer les valeurs nulles, nous utilisons le IFERROR(1/(1/...))wrapping:

=INDEX(IFERROR(1/(1/QUERY(QUERY({C2:G*1}, 
 "select (Col1+Col2+Col3+Col4+Col5)/5"), "offset 1", ))))

pour dynamiser les Colréférences, nous pouvons faire:

=INDEX(IFERROR(1/(1/QUERY(QUERY({C2:G*1}, 
 "select "&
 "("&JOIN("+", "Col"&ROW(INDIRECT("1:"&COLUMNS(C:G))))&")/"&COLUMNS(C:G)), 
 "offset 1", ))))


niveau 2:

si les cellules vides ne sont pas considérées comme des zéros et ne doivent pas être ignorées:

=INDEX(TRANSPOSE(QUERY(TRANSPOSE(E2:I), 
 "select "&TEXTJOIN(",", 1, IF(A2:A="",,
 "avg(Col"&ROW(A2:A)-ROW(A2)+1&")")))),, 2)

notez que cela dépend de la colonne A, donc les valeurs manquantes dans la colonne A compenseront les résultats

fait amusant !! nous pouvons échanger avgvers maxou min:

pour le libérer du confinement de la colonne A et le faire fonctionner pour toute ligne valide:

=INDEX(IFERROR(1/(1/TRANSPOSE(QUERY(TRANSPOSE(
 IF(TRIM(TRANSPOSE(QUERY(TRANSPOSE(C2:G),,9^9)))="", C2:G*0, C2:G)), 
 "select "&TEXTJOIN(",", 1, 
 "avg(Col"&ROW(A2:A)-ROW(A2)+1&")"))))),, 2)

si les 0 présents dans la plage ne doivent pas être moyennés, nous pouvons ajouter une petite instruction IF:

=INDEX(IFERROR(1/(1/TRANSPOSE(QUERY(TRANSPOSE(
 IF(TRIM(TRANSPOSE(QUERY(TRANSPOSE(
 IF(C2:G>0, C2:G, )),,9^9)))="", C2:G*0, 
 IF(C2:G>0, C2:G, ))), 
 "select "&TEXTJOIN(",", 1, 
 "avg(Col"&ROW(A2:A)-ROW(A2)+1&")"))))),, 2)

ici, nous avons utilisé ce que l'on appelle le "smash de requête verticale" qui prend toutes les valeurs d'une plage donnée et les concentre sur une seule colonne, où toutes les cellules de chaque ligne sont jointes avec un espace vide en tant que sous-produit:

=FLATTEN(QUERY(TRANSPOSE(C2:G),,9^9))

en dehors de cela, il existe également un "smash de requête horizontal" :

=QUERY(C2:G,,9^9)

et aussi "ultime smash à double requête à 360 °" qui place toutes les cellules de la plage dans une seule cellule:

=QUERY(FLATTEN(QUERY(TRANSPOSE(C2:G),,9^9)),,9^9)

et enfin "le tristement célèbre smash de double requête inverse à 360 ° négatif" qui donne la priorité aux colonnes par rapport aux lignes:

=QUERY(FLATTEN(QUERY(C2:G,,9^9)),,9^9)

tous les noms de requête smash sont bien sûr protégés par le droit d'auteur

retour au sujet ... comme mentionné ci-dessus, toutes les cellules par ligne de la plage sont jointes avec des espaces vides, même les vides, nous avons donc une situation où nous obtenons des espaces doubles ou multiples entre les valeurs. pour résoudre ce problème, nous utilisons TRIMet introduisons une IFinstruction simple pour affecter 0 valeurs pour les lignes vides dans une plage donnée, par exemple. pour contrer le décalage:


MMULT

niveau 3:

MMULTest une sorte de formule de classe lourde qui est capable d'effectuer l' addition, soustraction, multiplication, division , même en cours d' exécution totale sur les tableaux / matrices ... Cependant, plus le jeu de données = plus lente du calcul de la formule (parce que MMULTmême les lignes vides prennent du temps pour effectuer l' + - × ÷opération ) ... à moins que nous n'utilisions vraiment une plage dynamique infinie dans les deux sens ...

pour obtenir la dernière ligne avec les valeurs d'une plage donnée:

=INDEX(MAX(IF(TRIM(FLATTEN(QUERY(TRANSPOSE(
 INDIRECT("C2:"&ROWS(A:A))),,9^9)))="",,ROW(A2:A))))

pour obtenir la dernière colonne avec les valeurs d'une plage donnée:

=INDEX(MAX(IF(TRIM(QUERY(INDIRECT("C2:"&ROWS(A:A)),,9^9))="",,COLUMN(C2:2))))

maintenant nous pouvons le construire de manière simple:

=INDIRECT("C2:"&ADDRESS(9, 7))

qui est le même que:

=INDEX(INDIRECT("C2:"&ADDRESS(MAX(IF(TRIM(FLATTEN(QUERY(TRANSPOSE(
 INDIRECT("C2:"&ROWS(A:A))),,9^9)))="",,ROW(A2:A))), 
 MAX(IF(TRIM(QUERY(INDIRECT("C2:"&ROWS(A:A)),,9^9))="",,COLUMN(C2:2))))))

ou alternative plus courte:

=INDEX(INDIRECT("C2:"&ADDRESS(
 MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*ROW(A2:A)), 
 MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*COLUMN(C2:2)))))

par conséquent, la formule MMULT simplifiée serait:

=ARRAYFORMULA(IFERROR(
 MMULT(N(   C2:G9),           ROW(INDIRECT("C1:C"&COLUMNS(C:G)))^0)/
 MMULT(N(IF(C2:G9<>"", 1, )), ROW(INDIRECT("C1:C"&COLUMNS(C:G)))^0)))

dans le cas où nous voulons exclure les valeurs nulles de la plage, la formule serait:

=ARRAYFORMULA(IFERROR(
 MMULT(N(   C2:G9),         ROW(INDIRECT("C1:C"&COLUMNS(C:G)))^0)/
 MMULT(N(IF(C2:G9>0, 1, )), ROW(INDIRECT("C1:C"&COLUMNS(C:G)))^0)))

niveau 4:

rassembler tout ce qui précède pour le rendre infiniment dynamique et toujours limité à un ensemble de données valide:

=INDEX(IFERROR(
 MMULT(N(   INDIRECT("C2:"&ADDRESS(
 MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*ROW(A2:A)), 
 MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*COLUMN(C2:2))))),           ROW(INDIRECT("C1:C"&
 MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*COLUMN(C2:2))-(COLUMN(C2)-1)))^0)/
 MMULT(N(IF(INDIRECT("C2:"&ADDRESS(
 MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*ROW(A2:A)), 
 MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*COLUMN(C2:2))))<>"", 1, )), ROW(INDIRECT("C1:C"&
 MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*COLUMN(C2:2))-(COLUMN(C2)-1)))^0)))

encore une fois, sans inclure les cellules avec des zéros dans la plage:


mentions honorables:

@Erik Tyler niveau:

l'opposé polaire de la formule précédente serait d'exécuter le MMULTsur

  • superficie totale de au lieu deC2:? (all rows, all columns)
  • zone valide qui évite les calculs de masse deC2:? (excluding empty rows and columns)0 × 0 = 0

y compris les zéros:

=INDEX(IFERROR(
 MMULT(   INDIRECT("C2:"&ROWS(C:C))*1,         SEQUENCE(COLUMNS(C2:2))^0)/ 
 MMULT(IF(INDIRECT("C2:"&ROWS(C:C))<>"", 1)*1, SEQUENCE(COLUMNS(C2:2))^0)))

à l'exclusion des zéros:

=INDEX(IFERROR(
 MMULT(   INDIRECT("C2:"&ROWS(C:C))*1,       SEQUENCE(COLUMNS(C2:2))^0)/ 
 MMULT(IF(INDIRECT("C2:"&ROWS(C:C))>0, 1)*1, SEQUENCE(COLUMNS(C2:2))^0)))

@ niveau kishkin :

pour une plage fixe, C2:G9la MMULTmoyenne serait:

=INDEX(IFERROR(
 MMULT( C2:G9*1,    FLATTEN(COLUMN(C:G))^0)/ 
 MMULT((C2:G9>0)*1, FLATTEN(COLUMN(C:G))^0)))

=INDEX(IFNA(VLOOKUP(ROW(C2:C), 
 QUERY(SPLIT(FLATTEN(ROW(C2:C)&"×"&C2:J), "×"),
 "select Col1,avg(Col2)
  where Col2 is not null
  group by Col1"), 2, )))

Niveau @MattKing :

=INDEX(QUERY(SPLIT(FLATTEN(ROW(C2:C)&"×"&OFFSET(C2,,,9^9, 9^9)), "×"),
 "select avg(Col2) 
  group by Col1  
  label avg(Col2)''"))

à l'exclusion des zéros:

=INDEX(QUERY(SPLIT(FLATTEN(ROW(C2:C)&"×"&OFFSET(C2,,,9^9, 9^9)), "×"),
 "select avg(Col2)
  where Col2 <> 0 
  group by Col1  
  label avg(Col2)''"))

y compris les cellules vides:

=INDEX(IFERROR(1/(1/QUERY(SPLIT(FLATTEN(ROW(C2:C)&"×"&OFFSET(C2,,,9^9, 9^9)*1), "×"),
 "select avg(Col2)
  group by Col1  
  label avg(Col2)''"))))
2 ErikTyler Dec 24 2020 at 18:27

Vous y consacrez beaucoup de temps. J'espère que les gens l'apprécient, d'autant plus que vous l'avez fait pour tout le monde et pas pour vous-même.

En regardant vos formules finales, celles-ci devraient produire les mêmes résultats (donnez les données en C2 :? comme dans vos exemples):

En B2 (inclure les zéros):

=ArrayFormula(IFERROR(MMULT(INDIRECT("C2:"&ROWS(C:C))*1,SEQUENCE(COLUMNS(C1:1),1,1,0))/ MMULT(IF(INDIRECT("C2:"&ROWS(C:C))<>"",1,0),SEQUENCE(COLUMNS(C1:1),1,1,0))))

En B2 (exclure les zéros):

=ArrayFormula(IFERROR(MMULT(INDIRECT("C2:"&ROWS(C:C))*1,SEQUENCE(COLUMNS(C1:1),1,1,0))/ MMULT(IF(INDIRECT("C2:"&ROWS(C:C))<>0,1,0),SEQUENCE(COLUMNS(C1:1),1,1,0))))

2 MattKing Feb 16 2021 at 23:04

MISE À JOUR: J'ai mis à jour la formule à partir de mon message d'origine. Le ROW () doit toujours venir en premier afin que les valeurs manquantes dans les données ne gâchent pas le fractionnement.

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(ROW(C2:C)&"|"&OFFSET(C2,,,9^9,9^9)),"|"),"select AVG(Col2) group by Col1 label AVG(Col2)''"))

Devrait fonctionner à moins que je ne comprenne mal la question.

Pas besoin de vlookups ou mmults ou de filtres ou quoi que ce soit.

1 kishkin Dec 25 2020 at 20:43

Je vais essayer de faire un petit ajout à la réponse de @ player0. Et j'apprécierai vraiment tous les commentaires sur l'optimisation de cela.


Au cas où il y aurait beaucoup de lignes et de colonnes vides dans la plage de données, celles-ci pourraient tout aussi bien être exclues MMULT.

Étape 1 - Filtrer les lignes vides

Nous avons une plage de données: du C2bas jusqu'à la dernière ligne et jusqu'à la dernière colonne (qui est J:J). Je vais utiliser C2:K, voir les détails ci-dessous pour l'explication.

Cette formule nous donnera un tableau de numéros de ligne où il y a au moins une cellule non vide. De plus, il aura un 0s'il y a des lignes vides, mais cela n'aura pas d'importance pour la recherche dans ce tableau, ou nous le filtrerons quand cela compte:

=ARRAYFORMULA(
  UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K)))
)

Donc, pour filtrer les lignes vides de la plage de données que nous utilisons, nous FILTERallons vérifier si une ligne est dans notre tableau par le haut et laisser si c'est dans ce cas:

=ARRAYFORMULA(
  FILTER(
    C2:K*1,
    MATCH(
      ROW(C2:K),
      UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
      0
    )
  )
)

Étape 2 - Filtrer les colonnes vides

Pour obtenir un tableau contenant uniquement des numéros de colonnes non vides, nous pouvons utiliser presque la même formule:

=ARRAYFORMULA(
  UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))))
)

Pourquoi SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))est utilisé au lieu de COLUMN(C2:K)voir les détails à la fin.

Pour filtrer les colonnes vides, nous utilisons également FILTERwith MATCHcondition pour rechercher des numéros de colonne dans notre tableau:

=ARRAYFORMULA(
  FILTER(
    C2:K*1,
    MATCH(
      SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
      UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
      0
    )
  )
)

Et pour filtrer les lignes vides et les colonnes vides, nous utilisons simplement deux FILTERs:

=ARRAYFORMULA(
  FILTER(
    FILTER(
      C2:K*1,
      MATCH(
        ROW(C2:K),
        UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
        0
      )
    ),
    MATCH(
      SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
      UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
      0
    )
  )
)

La plage de données d'origine deviendra en interne:

Étape 3 - Faites le MMULT

Maintenant, nous pouvons utiliser MMULTavec cet ensemble de données pour calculer la moyenne:

=ARRAYFORMULA(
  MMULT(
    FILTER(
      FILTER(
        C2:K*1,
        MATCH(
          ROW(C2:K),
          UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
          0
        )
      ),
      MATCH(
        SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
        UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
        0
      )
    ),
    SEQUENCE(
      ROWS(
        QUERY(
          UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
          "WHERE Col1 <> 0"
        )
      ),
      1,
      1,
      0
    )
  ) /
  MMULT(
    FILTER(
      FILTER(
        (C2:K <> "")*1,
        MATCH(
          ROW(C2:K),
          UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
          0
        )
      ),
      MATCH(
        SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
        UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
        0
      )
    ),
    SEQUENCE(
      ROWS(
        QUERY(
          UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
          "WHERE Col1 <> 0"
        )
      ),
      1,
      1,
      0
    )
  )
)

C'est un peu décalé en ce qui concerne les lignes de données d'origine.

Étape 4 - Remplissez la colonne MOYENNE

Pour rendre les moyennes cohérentes avec les lignes de données d'origine, nous pouvons utiliser VLOOKUPcomme ceci:

=ARRAYFORMULA(
  IFNA(VLOOKUP(
    SEQUENCE(MAX((C2:K <> "") * ROW(C2:K)) - 1, 1, ROW(C2)),
    {
      QUERY(UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))), "WHERE Col1 <> 0"),
      MMULT(
        ...
      ) /
      MMULT(
        ...
      )
    },
    2,
    0
  ))
)

  • SEQUENCE(MAX((C2:K <> "") * ROW(C2:K)) - 1, 1, ROW(C2))est un tableau de numéros de ligne du 2ème au dernier non vide. Nous ne remplirons pas toutes les lignes avec des chaînes vides.
  • QUERY(UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))), "WHERE Col1 <> 0")est un tableau de numéros de ligne non vides avec ceux 0filtrés utilisés comme clés de recherche.
  • IFNA renverra une chaîne vide à placer à côté d'une ligne de données vide.

FORMULE FINALE

Mettre tous ensemble:

=ARRAYFORMULA(
  IFNA(VLOOKUP(
    SEQUENCE(MAX((C2:K <> "") * ROW(C2:K)) - 1, 1, ROW(C2)),
    {
      QUERY(UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))), "WHERE Col1 <> 0"),
      MMULT(
        FILTER(
          FILTER(
            C2:K*1,
            MATCH(
              ROW(C2:K),
              UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
              0
            )
          ),
          MATCH(
            SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
            UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
            0
          )
        ),
        SEQUENCE(
          ROWS(
            QUERY(
              UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
              "WHERE Col1 <> 0"
            )
          ),
          1,
          1,
          0
        )
      ) /
      MMULT(
        FILTER(
          FILTER(
            (C2:K <> "")*1,
            MATCH(
              ROW(C2:K),
              UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
              0
            )
          ),
          MATCH(
            SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
            UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
            0
          )
        ),
        SEQUENCE(
          ROWS(
            QUERY(
              UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
              "WHERE Col1 <> 0"
            )
          ),
          1,
          1,
          0
        )
      )
    },
    2,
    0
  ))
)


Quelques détails

  • INDEXpourrait être utilisé à la place de ARRAYFORMULApar souci de brièveté (merci @ player0, m'a appris cela il y a quelques mois), mais j'aime la non-ambiguïté de ARRAYFORMULA.
  • J'utilise SEQUENCEpour construire une colonne ou une ligne de 1s pour être explicite, pour plus de clarté. Par exemple, celui-ci
SEQUENCE(
  ROWS(
    QUERY(
      UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
      "WHERE Col1 <> 0"
    )
  ),
  1,
  1,
  0
)

pourrait être remplacé par

SIGN(
  QUERY(
    UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
    "WHERE Col1 <> 0"
  )
)

ce qui est un peu plus court. Il y a aussi un moyen démontré ici par @ player0 d'élever à la puissance de 0:

QUERY(
  UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
  "WHERE Col1 <> 0"
)^0

mais (ce n'est que ma spéculation) je pense que SEQUENCEla mise en œuvre interne de devrait être plus simple que l'opération d'élever à une puissance.

  • J'utilise range C2:Kqui est une colonne de plus qu'il n'y en a réellement sur la feuille. Non seulement il donne une plage de toutes les colonnes à droite de C2et toutes les lignes en dessous, mais il se met également à jour en cas d'ajout d'une autre colonne à droite de la feuille: une démo . Bien que cela ne soit pas mis en évidence. Cela C2:Kpeut presque parfaitement (il y aura un problème au cas où il y aurait effectivement une ZZZcolonne présente sur une feuille) remplacer ces approches:
INDIRECT("C2:" & ROWS(C:C))

OFFSET(C2,,, ROWS(C2:C), COLUMNS(C2:2))
  • Il y a un petit inconvénient à utiliser C2:K: =ARRAYFORMULA(COLUMN(C2:K))retournera un tableau de numéros de colonne même pour ceux qui n'existent pas, nous devons donc utiliser à la =SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))place.
1 kishkin Jan 04 2021 at 19:17

Je pense qu'il existe une réponse simple pour la moyenne par ligne en utilisant VLOOKUPet QUERY.

Celui-ci est en B2:

=ARRAYFORMULA(
  IFNA(
    VLOOKUP(
      ROW(B2:B),
      QUERY(
        {
          FLATTEN(ROW(C2:J) + SEQUENCE(1, COLUMNS(C2:J),,)),
          FLATTEN(C2:J)
        },
        "SELECT Col1, AVG(Col2)
         WHERE Col2 IS NOT NULL
         GROUP BY Col1"
      ),
      2,
      0
    )
  )
)

  • Cela pourrait être facilement changé pour max, min, sum, count - il suffit de changer la fonction d'agrégation à l'intérieur de l' QUERYinstruction.
  • La même approche pourrait être utilisée pour l'agrégation par colonne.
  • FLATTEN(C2:J) pourrait être changé en:
    • FLATTEN(--C2:J)traiter les cellules vides comme des 0s;
    • FLATTEN(IFERROR(1/(1/C2:J)))pour exclure 0s de la moyenne.
  • S'il n'y a pas de lignes vides intermédiaires, VLOOKUPpourrait être supprimé de la formule, ainsi que Col1de l' SELECTinstruction.
  • Il existe une version plus courte (merci @MattKing!) Sans VLOOKUPet WHERE Col...:
=ARRAYFORMULA(
  QUERY(
    {
      FLATTEN(ROW(C2:J) + SEQUENCE(1, COLUMNS(C2:J),,)),
      FLATTEN(IFERROR(1/(1/C2:J)))
    },
    "SELECT AVG(Col2)
     GROUP BY Col1
     LABEL AVG(Col2) ''"
  )
)

J'utilise une C2:Jplage ayant des colonnes jusqu'à I:I, quelques détails à ce sujet:

  • Plage C2:Jqui est une colonne de plus qu'il n'y en a réellement sur la feuille. Non seulement il donne une plage de toutes les colonnes à droite C2et toutes les lignes en dessous, mais il se met également à jour en cas d'ajout d'une autre colonne à droite de la feuille: une démo . Bien que cela ne soit pas mis en évidence. Cela C2:Jpeut presque parfaitement (il y aura un problème au cas où il y aurait effectivement une ZZZcolonne présente sur une feuille) remplacer ces approches:
INDIRECT("C2:" & ROWS(C:C))

OFFSET(C2,,, ROWS(C2:C), COLUMNS(C2:2))
  • Il y a un petit inconvénient à utiliser C2:J: =ARRAYFORMULA(0 * COLUMN(C2:J))retournera un tableau de numéros de colonnes même pour ceux qui n'existent pas (multipliés par 0), nous devons donc utiliser à la =SEQUENCE(1, COLUMNS(C2:J),,)place.

@ player0, avez-vous des idées à ce sujet?