ArrayFormula des Durchschnitts für einen unendlichen, wirklich dynamischen Bereich in Google Sheets

Question

wie im Beispiel:

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

Was ist der optimalste Weg, um AVERAGEfür jede gültige Zeile im dynamischen Sinne einen Begriff zu erhalten (unbekannte Anzahl von Zeilen und unbekannte Anzahl von Spalten)?

Answer 1

ABFRAGE

Level 1:

wenn alle 5 Zellen im Bereich C2: G Werte haben:

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

Wenn nicht, werden die Zeilen übersprungen:

Wenn leere Zellen als Nullen betrachtet werden:

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

Um Nullwerte zu entfernen, verwenden wir IFERROR(1/(1/...))Wrapping:

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

Um ColReferenzen dynamisch zu machen , können wir:

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

---

Level 2:

Wenn leere Zellen nicht als Nullen betrachtet werden und nicht übersprungen werden sollten:

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

Beachten Sie, dass dies von Spalte A abhängig ist. Fehlende Werte in Spalte A versetzen die Ergebnisse

lustige Tatsache !! wir können tauschen avgzu maxoder min:

um es von der Beschränkung von Spalte A zu befreien und es für jede gültige Zeile funktionieren zu lassen:

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

Wenn die vorhandenen Nullen im Bereich nicht gemittelt werden sollen, können wir eine kleine IF-Anweisung hinzufügen:

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

Hier haben wir den sogenannten "vertikalen Abfrage-Smash" verwendet, der alle Werte in einem bestimmten Bereich auf eine einzelne Spalte konzentriert, in der alle Zellen pro Zeile mit dem Leerraum als Nebenprodukt verbunden sind:

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

Abgesehen davon gibt es auch "horizontale Abfrage Smash" :

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

und auch "ultimativer 360 ° -Doppelabfrage-Smash", bei dem alle Zellen aus dem Bereich in einer einzigen Zelle zusammengefasst werden:

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

und schließlich "der berüchtigte negative 360 ​​° Reverse Double Query Smash", bei dem Spalten Vorrang vor Zeilen haben:

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

Alle Abfrage-Smash-Namen sind natürlich urheberrechtlich geschützt

zurück zum Thema ... wie oben erwähnt, werden alle Zellen pro Zeile im Bereich mit leeren Leerzeichen verbunden, auch diese leeren, sodass wir eine Situation haben, in der wir doppelte oder mehrere Leerzeichen zwischen Werten erhalten. Um dies zu beheben, verwenden wir TRIMeine einfache IFAnweisung, um 0 Werte für leere Zeilen in einem bestimmten Bereich zuzuweisen, z. um dem Versatz entgegenzuwirken:

---

MMULT

Stufe 3:

MMULTist eine Art Formel für schwere Klassen, die in der Lage ist, Additionen, Subtraktionen, Multiplikationen, Divisionen und sogar laufende Summen für Arrays / Matrizen auszuführen. Je größer der Datensatz, desto langsamer die Formelberechnung (da MMULTselbst in leeren Zeilen Zeit benötigt wird, um die + - × ÷Operation auszuführen ) ... es sei denn, wir verwenden einen wirklich unendlichen Dynamikbereich in beide Richtungen ...

So erhalten Sie die letzte Zeile mit Werten eines bestimmten Bereichs:

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

So erhalten Sie die letzte Spalte mit Werten eines bestimmten Bereichs:

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

Jetzt können wir es auf einfache Weise konstruieren:

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

Das ist das gleiche wie:

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

oder kürzere Alternative:

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

Daher wäre die vereinfachte MMULT-Formel:

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

Wenn wir Nullwerte aus dem Bereich ausschließen möchten, lautet die Formel:

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

Level 4:

Alles oben zusammenstellen, um es unendlich dynamisch und dennoch auf einen gültigen Datensatz zu beschränken:

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

wieder ohne Zellen mit Nullen im Bereich:

---

Anerkennungen:

@Erik Tyler Level:

das genaue Gegenteil der vorherigen Formel laufen würde das MMULTauf

• Gesamtfläche von stattC2:? (all rows, all columns)
• gültiger Bereich, der Massenberechnungen von vermeidetC2:? (excluding empty rows and columns)0 × 0 = 0

einschließlich Nullen:

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

ohne Nullen:

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

@ Kishkin Level:

für einen festen Bereich wäre C2:G9der MMULTDurchschnitt:

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

@ MattKing Level:

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

ohne Nullen:

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

einschließlich leerer Zellen:

=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

Sie haben eine Menge Zeit in diese Sache gesteckt. Ich hoffe, die Leute wissen das zu schätzen, mehr noch, dass Sie es für alle anderen und nicht für sich selbst getan haben.

Wenn Sie sich Ihre endgültigen Formeln ansehen, sollten diese die gleichen Ergebnisse liefern (geben Sie Daten in C2:? Wie in Ihren Beispielen an):

In B2 (einschließlich Nullen):

=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 (Nullen ausschließen):

=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

UPDATE: Ich habe die Formel aus meinem ursprünglichen Beitrag aktualisiert. ROW () sollte immer an erster Stelle stehen, damit fehlende Werte in den Daten die Aufteilung nicht beeinträchtigen.

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

Sollte funktionieren, es sei denn, ich verstehe die Frage falsch.

Keine Notwendigkeit für Lookups oder MMults oder Filter oder irgendetwas.

Answer 4

Ich werde versuchen, die Antwort von @ player0 ein wenig zu ergänzen. Und ich werde mich über Kommentare zur Optimierung sehr freuen.

---

Falls sich innerhalb des Datenbereichs viele leere Zeilen und Spalten befinden, können diese ebenfalls ausgeschlossen werden MMULT.

Schritt 1 - Leere Zeilen herausfiltern

Wir haben einen Datenbereich: von C2bis zur letzten Zeile und bis zur letzten Spalte (dh J:J). Ich werde verwenden C2:K, siehe Details unten für die Erklärung.

Diese Formel gibt uns ein Array von Zeilennummern, in denen mindestens eine nicht leere Zelle vorhanden ist. Es wird auch eine geben, 0wenn leere Zeilen vorhanden sind, aber es spielt keine Rolle für die Suche in diesem Array, oder wir werden es herausfiltern, wenn es wichtig ist:

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

Um leere Zeilen aus dem von uns verwendeten Datenbereich herauszufiltern FILTER, wird geprüft, ob sich eine Zeile von oben in unserem Array befindet, und es wird in diesem Fall Folgendes gelassen :

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

Schritt 2 - Leere Spalten herausfiltern

Um ein Array von nur nicht leeren Spaltennummern zu erhalten, können wir fast dieselbe Formel verwenden:

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

Warum SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))wird verwendet, anstatt COLUMN(C2:K)Details am Ende zu sehen.

Um leere Spalten herauszufiltern, verwenden wir auch FILTERmit MATCHBedingung, um nach Spaltennummern in unserem Array zu suchen:

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

Und um leere Zeilen und Spalten herauszufiltern, verwenden wir nur zwei 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
    )
  )
)

Der ursprüngliche Datenbereich wird intern zu:

Schritt 3 - Machen Sie das MMULT

Jetzt können wir MMULTmit diesem Datensatz den Durchschnitt berechnen:

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

Es ist ein bisschen anders in Bezug auf Originaldatenzeilen.

Schritt 4 - Füllen Sie die Spalte DURCHSCHNITTLICH

Um Durchschnittswerte mit den ursprünglichen Datenzeilen in Einklang zu bringen, können wir Folgendes verwenden VLOOKUP:

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

Wo

• SEQUENCE(MAX((C2:K <> "") * ROW(C2:K)) - 1, 1, ROW(C2))ist ein Array von Zeilennummern von der zweiten bis zur letzten nicht leeren. Wir werden nicht alle Zeilen mit leeren Zeichenfolgen füllen.
• QUERY(UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))), "WHERE Col1 <> 0")ist ein Array nicht leerer Zeilennummern, wobei diese 0herausgefiltert als Schlüssel für die Suche verwendet werden.
• IFNA gibt eine leere Zeichenfolge zurück, die neben eine leere Datenzeile gestellt wird.

SCHLUSSFORMEL

Alles zusammen:

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

---

Ein paar Details

• INDEXkönnte anstelle der ARRAYFORMULAKürze verwendet werden (danke @ player0, hat mir das vor ein paar Monaten beigebracht), aber ich mag die Eindeutigkeit von ARRAYFORMULA.
• Ich benutze SEQUENCE, um eine Spalte oder eine Zeile von 1s so zu konstruieren , dass sie der Klarheit halber explizit sind. Zum Beispiel dieser

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

könnte durch ersetzt werden

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

das ist etwas kürzer. Es gibt auch eine Möglichkeit, die @ player0 hier demonstriert, um die Macht von 0:

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

aber (es ist nur meine Spekulation) Ich denke SEQUENCE, die interne Implementierung sollte einfacher sein als die Operation, sich zu einer Macht zu erheben.

• Ich verwende einen Bereich, C2:Kder eine Spalte mehr ist, als tatsächlich auf dem Blatt vorhanden ist. Es gibt nicht nur einen Bereich aller Spalten rechts davon C2und alle Zeilen darunter an, sondern es wird auch aktualisiert, wenn rechts neben dem Blatt eine weitere Spalte hinzugefügt wird: eine Demo . Es wird jedoch nicht hervorgehoben. Dies C2:Kkann fast perfekt (es wird ein Problem geben, falls tatsächlich eine ZZZSpalte auf einem Blatt vorhanden ist) diese Ansätze ersetzen:

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

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

• Es gibt einen kleinen Nachteil bei der Verwendung von C2:K: =ARRAYFORMULA(COLUMN(C2:K))Gibt ein Array von Spaltennummern zurück, auch für nicht vorhandene, daher müssen wir =SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))stattdessen verwenden.

Answer 5

Ich denke, es gibt eine einfache Antwort für den zeilenweisen Durchschnitt mit VLOOKUPund QUERY.

Dieser ist 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
    )
  )
)

• Dies kann leicht für max, min, sum, count geändert werden - ändern Sie einfach die Aggregationsfunktion innerhalb der QUERYAnweisung.
• Der gleiche Ansatz könnte für die spaltenweise Aggregation verwendet werden.
• FLATTEN(C2:J) könnte geändert werden zu:
  • FLATTEN(--C2:J)leere Zellen als 0s zu behandeln ;
  • FLATTEN(IFERROR(1/(1/C2:J)))auszuschließen 0s aus dem Durchschnitt.
• Wenn keine leeren Zwischenzeilen vorhanden sind, VLOOKUPkönnen diese sowohl aus der Formel als auch Col1aus der SELECTAnweisung entfernt werden.
• Es gibt eine kürzere Version (danke @MattKing!) Ohne VLOOKUPund 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) ''"
  )
)

Ich verwende einen C2:JBereich mit Spalten bis zu I:Ieinigen Details dazu:

• Bereich, C2:Jder eine Spalte mehr ist, als tatsächlich auf dem Blatt vorhanden ist. Es gibt nicht nur einen Bereich aller Spalten rechts davon C2und alle Zeilen darunter an, sondern es wird auch aktualisiert, wenn rechts neben dem Blatt eine weitere Spalte hinzugefügt wird: eine Demo . Es wird jedoch nicht hervorgehoben. Dies C2:Jkann fast perfekt (es wird ein Problem geben, falls tatsächlich eine ZZZSpalte auf einem Blatt vorhanden ist) diese Ansätze ersetzen:

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

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

• Die Verwendung hat einen kleinen Nachteil C2:J: =ARRAYFORMULA(0 * COLUMN(C2:J))Gibt ein Array von Spaltennummern zurück, auch für nicht vorhandene (multipliziert mit 0). Daher müssen wir =SEQUENCE(1, COLUMNS(C2:J),,)stattdessen verwenden.

---

@ player0, irgendwelche Gedanken dazu?