ArrayFormula of Average on Infinite Truly Dynamic Range in Google Sheets

Question

come da esempio:

     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 |           
  |======|=======|=====|=====|=====|=====|=====|=====
∞ |      |       |     |     |     |     |     |       

qual è il modo più ottimale per ottenere AVERAGEogni riga valida nel senso dinamico dei termini (quantità sconosciuta di righe e quantità sconosciuta di colonne)?

Answer 1

QUERY

livello 1:

se tutte e 5 le celle nell'intervallo C2: G hanno valori:

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

in caso contrario, le righe vengono ignorate:

se le celle vuote sono considerate zeri:

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

per rimuovere i valori zero usiamo il IFERROR(1/(1/...))wrapping:

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

per rendere Coldinamici i riferimenti possiamo fare:

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

---

livello 2:

se le celle vuote non sono considerate come zeri e non devono essere ignorate:

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

si noti che questo dipende dalla colonna A, quindi i valori mancanti nella colonna A compenseranno i risultati

Fatto divertente !! possiamo passare avga maxo min:

per liberarlo dal confinamento della colonna A e farlo funzionare per qualsiasi riga valida:

=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)

se gli 0 presenti nell'intervallo non devono essere mediati, possiamo aggiungere una piccola istruzione 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)

qui abbiamo usato il cosiddetto "smash di query verticale" che prende tutti i valori in un dato intervallo e li concentra in una singola colonna, dove tutte le celle per ogni riga sono unite con lo spazio vuoto come sottoprodotto:

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

oltre a questo, c'è anche "smash orizzontale della query" :

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

e anche "ultimo smash di doppia query a 360 °" che mette tutte le celle dell'intervallo in una singola cella:

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

e infine "il famigerato smash negativo a 360 ° della doppia query inversa" che assegna la priorità alle colonne rispetto alle righe:

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

tutti i nomi di query smash sono ovviamente protetti da copyright

tornando all'argomento ... come accennato sopra tutte le celle per riga nell'intervallo sono unite con spazi vuoti anche quelli vuoti, quindi abbiamo ottenuto una situazione in cui otteniamo spazi doppi o multipli tra i valori. per risolvere questo problema usiamo TRIMe introduciamo una semplice IFistruzione per assegnare valori 0 per righe vuote in un dato intervallo, ad es. per contrastare l'offset:

---

MMULT

livello 3:

MMULTè una sorta di formula di classe pesante che è in grado di eseguire addizioni, sottrazioni, moltiplicazioni, divisioni anche eseguendo il totale su array / matrici ... tuttavia, più grande è il set di dati = più lento il calcolo della formula (perché MMULTanche le righe vuote richiedono tempo per eseguire l' + - × ÷operazione ) ... a meno che non usiamo un intervallo veramente dinamico infinito in entrambe le direzioni ...

per ottenere l'ultima riga con i valori di un determinato intervallo:

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

per ottenere l'ultima colonna con i valori di un determinato intervallo:

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

ora possiamo costruirlo in modo semplice:

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

che è lo stesso di:

=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))))))

o alternativa più breve:

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

pertanto la formula MMULT semplificata sarebbe:

=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)))

nel caso in cui vogliamo escludere valori zero dall'intervallo, la formula sarebbe:

=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)))

livello 4:

mettendo insieme tutto quanto sopra per renderlo infinitamente dinamico e ancora limitato a set di dati validi:

=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)))

di nuovo, escluse le celle con zeri nell'intervallo:

---

menzioni d'onore:

@Erik Tyler livello:

il polo opposto della formula precedente sarebbe per eseguire il MMULTsu

• area totale di invece diC2:? (all rows, all columns)
• area valida che evita calcoli di massa diC2:? (excluding empty rows and columns)0 × 0 = 0

compresi gli zeri:

=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)))

esclusi gli zeri:

=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)))

Livello @kishkin :

per un intervallo fisso C2:G9la MMULTmedia sarebbe:

=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, )))

Livello @MattKing :

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

esclusi gli zeri:

=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)''"))

comprese le celle vuote:

=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)''"))))

Answer 2

Ci metti un sacco di tempo. Spero che le persone lo apprezzino, tanto più che l'hai fatto per tutti gli altri e non per te stesso.

Guardando le tue formule finali, queste dovrebbero produrre gli stessi risultati (dai dati in C2 :? come nei tuoi esempi):

In B2 (includi zeri):

=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))))

In B2 (escludere gli zeri):

=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))))

Answer 3

AGGIORNAMENTO: ho aggiornato la formula dal mio post originale. ROW () dovrebbe sempre venire prima in modo che i valori mancanti nei dati non annullino la divisione.

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

Dovrebbe funzionare a meno che non stia fraintendendo la domanda.

Non c'è bisogno di vlookup, mmults o filtri o altro.

Answer 4

Proverò a fare una piccola aggiunta alla risposta di @ player0. E apprezzerò davvero qualsiasi commento sull'ottimizzazione di questo.

---

Nel caso in cui ci siano molte righe e colonne vuote all'interno dell'intervallo di dati, queste potrebbero anche essere escluse MMULT.

Passaggio 1: filtrare le righe vuote

Abbiamo un intervallo di dati: dal C2basso all'ultima riga e fino all'ultima colonna (che è J:J). Userò C2:K, vedere i dettagli di seguito per la spiegazione.

Questa formula ci darà una matrice di numeri di riga in cui è presente almeno una cella non vuota. Inoltre avrà un 0se ci sono righe vuote, ma non importa per la ricerca in questo array, o lo filtreremo quando è importante:

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

Quindi, per filtrare le righe vuote dall'intervallo di dati che usiamo FILTERche controllerà se una riga è nel nostro array dall'alto e lascerà se è in quel caso:

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

Passaggio 2: filtrare le colonne vuote

Per ottenere un array di soli numeri di colonna non vuoti possiamo usare quasi la stessa formula:

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

Perché SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))viene utilizzato invece di COLUMN(C2:K)vedere i dettagli alla fine.

Per filtrare le colonne vuote usiamo anche FILTERcon MATCHcondition per cercare i numeri di colonna nel nostro array:

=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
    )
  )
)

E per filtrare righe vuote e colonne vuote usiamo solo due 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
    )
  )
)

L'intervallo di dati originale diventerà internamente:

Passaggio 3: eseguire il file MMULT

Ora possiamo usare MMULTcon quel set di dati per calcolare la media:

=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
    )
  )
)

È un po 'fuori posto per quanto riguarda le righe di dati originali.

Passaggio 4: riempire la colonna MEDIA

Per rendere le medie coerenti con le righe di dati originali possiamo usare in VLOOKUPquesto modo:

=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
  ))
)

Dove

• SEQUENCE(MAX((C2:K <> "") * ROW(C2:K)) - 1, 1, ROW(C2))è un array di numeri di riga dalla seconda all'ultima non vuota. Non riempiremo tutte le righe con stringhe vuote.
• QUERY(UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))), "WHERE Col1 <> 0")è un array di numeri di riga non vuoti con quello 0filtrato utilizzato come chiavi per la ricerca.
• IFNA restituirà una stringa vuota da mettere accanto a una riga di dati vuota.

FORMULA FINALE

Mettere tutto insieme:

=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
  ))
)

---

Alcuni dettagli

• INDEXpotrebbe essere usato al posto di ARRAYFORMULAper brevità (grazie @ player0, me lo ha insegnato qualche mese fa), ma mi piace l'univocità di ARRAYFORMULA.
• Uso SEQUENCEper costruire una colonna o una riga di 1s per essere esplicito, per chiarezza. Ad esempio, questo

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

potrebbe essere sostituito con

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

che è un po 'più corto. C'è anche un modo dimostrato qui da @ player0 di elevare al potere di 0:

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

ma (è solo una mia speculazione) penso SEQUENCEche l'implementazione interna dovrebbe essere più semplice dell'operazione di elevazione al potere.

• Uso l'intervallo C2:Kche è una colonna in più di quanto effettivamente esiste sul foglio. Non solo fornisce un intervallo di tutte le colonne a destra di C2e tutte le righe in basso, ma si aggiorna anche in caso di aggiunta di un'altra colonna a destra del foglio: una demo . Anche se non viene evidenziato. Questo C2:Kpuò quasi perfettamente (ci sarà un problema nel caso in cui ci sia effettivamente una ZZZcolonna presente su un foglio) sostituire questi approcci:

INDIRECT("C2:" & ROWS(C:C))

OFFSET(C2,,, ROWS(C2:C), COLUMNS(C2:2))

• C'è un piccolo inconveniente nell'uso C2:K: =ARRAYFORMULA(COLUMN(C2:K))restituirà un array di numeri di colonna anche per quelli non esistenti, quindi dobbiamo usare =SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))invece.

Answer 5

Penso che ci sia una risposta semplice per l'utilizzo medio di righe VLOOKUPe QUERY.

Questo è in 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
    )
  )
)

• Questo potrebbe essere facilmente modificato per max, min, sum, count - basta cambiare la funzione di aggregazione all'interno QUERYdell'istruzione.
• Lo stesso approccio potrebbe essere utilizzato per l'aggregazione per colonna.
• FLATTEN(C2:J) potrebbe essere cambiato in:
  • FLATTEN(--C2:J)trattare le celle vuote come 0s;
  • FLATTEN(IFERROR(1/(1/C2:J)))per escludere 0i messaggi dalla media.
• Se non ci sono righe vuote intermedi, VLOOKUPpuò essere rimosso dalla formula, così come Col1da SELECTdichiarazione.
• C'è una versione più breve (grazie @MattKing!) Senza VLOOKUPe 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) ''"
  )
)

Uso l' C2:Jintervallo con colonne fino a I:I, alcuni dettagli su questo:

• Intervallo C2:Jche è una colonna in più di quanto effettivamente esiste sul foglio. Non solo fornisce un intervallo di tutte le colonne a destra di C2e tutte le righe in basso, ma si aggiorna anche in caso di aggiunta di un'altra colonna a destra del foglio: una demo . Anche se non viene evidenziato. Questo C2:Jpuò quasi perfettamente (ci sarà un problema nel caso in cui ci sia effettivamente una ZZZcolonna presente su un foglio) sostituire questi approcci:

INDIRECT("C2:" & ROWS(C:C))

OFFSET(C2,,, ROWS(C2:C), COLUMNS(C2:2))

• C'è un piccolo inconveniente nell'uso C2:J: =ARRAYFORMULA(0 * COLUMN(C2:J))restituirà un array di numeri di colonna anche per quelli inesistenti (moltiplicato per 0), quindi dobbiamo usare =SEQUENCE(1, COLUMNS(C2:J),,)invece.

---

@ player0, qualche idea su questo?