ArrayFormula of Average on Infinite Truly Dynamic Range w Arkuszach Google

Dec 24 2020

jak na przykładzie:

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

Jaki jest najbardziej optymalny sposób uzyskania AVERAGEkażdego prawidłowego wiersza w dynamicznym znaczeniu terminów (nieznana liczba wierszy i nieznana liczba kolumn)?

Odpowiedzi

4 player0 Dec 24 2020 at 14:34

PYTANIE

poziom 1:

jeśli wszystkie 5 komórek w zakresie C2: G ma wartości:

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

jeśli nie, to wiersze są pomijane:

jeśli puste komórki są traktowane jako zera:

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

aby usunąć wartości zerowe, używamy IFERROR(1/(1/...))zawijania:

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

aby uczynić Colreferencje dynamicznymi, możemy zrobić:

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


poziom 2:

jeśli puste komórki nie są uważane za zera i nie należy ich pomijać:

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

zwróć uwagę, że jest to zależne od kolumny A, więc brakujące wartości w kolumnie A zrównują wyniki

śmieszny fakt !! możemy zamienić avgna maxlub min:

aby uwolnić go od uwięzienia kolumny A i sprawić, by działał dla dowolnego prawidłowego wiersza:

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

jeśli obecne 0 w zakresie nie powinny być uśredniane, możemy dodać małą instrukcję JEŻELI:

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

tutaj użyliśmy tak zwanego „pionowego rozbicia zapytania”, które pobiera wszystkie wartości z danego zakresu i koncentruje je w jednej kolumnie, gdzie wszystkie komórki w każdym wierszu są połączone z pustą przestrzenią jako produktem ubocznym:

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

oprócz tego istnieje również „poziome rozbicie zapytania” :

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

a także „ostateczne podwójne rozbicie zapytań 360 °”, które umieszcza wszystkie komórki z zakresu w jednej komórce:

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

i wreszcie „niesławne negatywne podwójne zapytanie o 360 ° w odwrotnej kolejności”, które nadaje priorytet kolumnom nad wierszami:

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

wszystkie nazwy zapytań są oczywiście chronione prawem autorskim

wracając do tematu ... jak wspomniano powyżej, wszystkie komórki w wierszu w zakresie są łączone z pustą przestrzenią, nawet te puste, więc mamy sytuację, w której otrzymujemy podwójne lub wielokrotne spacje między wartościami. aby to naprawić używamy TRIMi wprowadzamy prostą IFinstrukcję przypisującą 0 wartości pustym wierszom w danym zakresie np. aby przeciwdziałać przesunięciu:


MMULT

poziom 3:

MMULTjest rodzajem ciężkiej formuły klasowej, która jest w stanie wykonywać dodawanie, odejmowanie, mnożenie, dzielenie, a nawet obliczanie sumy na tablicach / macierzach ... jednak większy zbiór danych = wolniejsze obliczanie formuły (ponieważ MMULTnawet w pustych wierszach wykonanie + - × ÷operacji zajmuje trochę czasu ) ... chyba że użyjemy prawdziwie nieskończonego zakresu dynamiki w obu kierunkach ...

aby uzyskać ostatni wiersz z wartościami z podanego zakresu:

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

aby otrzymać ostatnią kolumnę z wartościami z danego zakresu:

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

teraz możemy to skonstruować w prosty sposób:

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

czyli to samo co:

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

lub krótsza alternatywa:

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

dlatego uproszczona formuła MMULT wyglądałaby następująco:

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

w przypadku gdybyśmy chcieli wykluczyć wartości zerowe z zakresu, wzór wyglądałby tak:

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

poziom 4:

łącząc wszystko powyżej, aby uczynić go nieskończenie dynamicznym i nadal ograniczonym do prawidłowego zbioru danych:

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

ponownie, nie uwzględniając komórek z zerami w zakresie:


wyróżnienia:

Poziom @Erik Tyler :

biegunowym przeciwieństwem poprzedniej formuły byłoby uruchomienie MMULTon

  • całkowita powierzchnia zamiastC2:? (all rows, all columns)
  • ważny obszar, który pozwala uniknąć obliczeń masyC2:? (excluding empty rows and columns)0 × 0 = 0

w tym zera:

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

bez zer:

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

Poziom @kishkin :

dla ustalonego zakresu średnia będzie:C2:G9MMULT

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

Poziom @MattKing :

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

bez zer:

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

w tym puste komórki:

=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

Włożyłeś w to mnóstwo czasu. Mam nadzieję, że ludzie to docenią, tym bardziej, że zrobiłeś to dla wszystkich innych, a nie dla siebie.

Patrząc na ostateczne formuły, powinny one dać takie same wyniki (podaj dane w C2 :? jak w twoich przykładach):

W B2 (zawiera zera):

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

W B2 (bez zera):

=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

AKTUALIZACJA: Zaktualizowałem formułę z mojego oryginalnego postu. WIERSZ () powinien zawsze znajdować się na pierwszym miejscu, aby brakujące wartości w danych nie powodowały zmiany podziału.

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

Powinien działać, chyba że źle zrozumiem pytanie.

Nie ma potrzeby stosowania vlookupów, mmultów, filtrów czy czegokolwiek.

1 kishkin Dec 25 2020 at 20:43

Spróbuję dodać trochę do odpowiedzi @ player0. Będę wdzięczny za wszelkie komentarze dotyczące optymalizacji tego.


Jeśli w zakresie danych jest dużo pustych wierszy i kolumn, można je równie dobrze wykluczyć MMULT.

Krok 1 - odfiltruj puste wiersze

Mamy zakres danych: od C2dołu do ostatniego wiersza i od prawej do ostatniej kolumny (czyli J:J). Skorzystam C2:K, zobacz szczegóły poniżej, aby uzyskać wyjaśnienie.

Ta formuła da nam tablicę numerów wierszy, w których jest co najmniej jedna niepusta komórka. Będzie również miał, 0jeśli są puste wiersze, ale nie będzie miało znaczenia przy wyszukiwaniu w tej tablicy, albo odfiltrujemy to, gdy ma to znaczenie:

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

Tak więc, aby odfiltrować puste wiersze z zakresu danych, FILTERktórego używamy, co sprawdzi, czy w naszej tablicy znajduje się wiersz od góry i opuści, jeśli tak jest:

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

Krok 2 - odfiltruj puste kolumny

Aby otrzymać tablicę tylko niepustych numerów kolumn, możemy użyć prawie tej samej formuły:

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

Dlaczego SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))używa się zamiast COLUMN(C2:K)zobaczyć szczegóły na końcu.

Aby odfiltrować pustych kolumnach możemy również skorzystać FILTERz MATCHwarunku, aby wyszukać numery kolumn w naszej tablicy:

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

Aby odfiltrować puste wiersze i puste kolumny, używamy po prostu dwóch 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
    )
  )
)

Pierwotny zakres danych wewnętrznie zmieni się na:

Krok 3 - Wykonaj MMULT

Teraz możemy użyć MMULTtego zestawu danych do obliczenia średniej:

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

Jest to trochę dziwne, jeśli chodzi o oryginalne wiersze danych.

Krok 4 - Wypełnij kolumnę ŚREDNIA

Aby średnie były zgodne z oryginalnymi wierszami danych, możemy użyć w VLOOKUPnastępujący sposób:

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

Gdzie

  • SEQUENCE(MAX((C2:K <> "") * ROW(C2:K)) - 1, 1, ROW(C2))jest tablicą numerów wierszy od drugiego do ostatniego niepustego. Nie będziemy wypełniać wszystkich wierszy pustymi ciągami.
  • QUERY(UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))), "WHERE Col1 <> 0")jest tablicą niepustych numerów wierszy, z których ta 0odfiltrowana jest używana jako klucze do wyszukiwania.
  • IFNA zwróci pusty ciąg do umieszczenia obok pustego wiersza danych.

FORMUŁA KOŃCOWA

Kładąc wszystko razem:

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


Kilka szczegółów

  • INDEXmożna użyć zamiast ARRAYFORMULAdla zwięzłości (dzięki @ player0, nauczył mnie tego kilka miesięcy temu), ale lubię jednoznaczność ARRAYFORMULA.
  • Używam SEQUENCEdo konstruowania kolumny lub wiersza 1s, aby było jasne, dla jasności. Na przykład ten
SEQUENCE(
  ROWS(
    QUERY(
      UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
      "WHERE Col1 <> 0"
    )
  ),
  1,
  1,
  0
)

można zastąpić

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

który jest nieco krótszy. Jest też sposób pokazany tutaj przez @ player0 na podbicie do potęgi 0:

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

ale (to tylko moje spekulacje) myślę SEQUENCE, że wewnętrzna implementacja powinna być prostsza niż operacja podniesienia do potęgi.

  • Używam zakresu, C2:Kktóry jest o jedną kolumnę więcej niż w rzeczywistości istnieje na arkuszu. Nie tylko podaje zakres wszystkich kolumn po prawej stronie C2i wszystkich wierszy od niego, ale także aktualizuje w przypadku dodania kolejnej kolumny po prawej stronie arkusza: demo . Chociaż nie można tego podkreślać. Może C2:Kto prawie idealnie (wystąpi problem, jeśli ZZZna arkuszu faktycznie występuje kolumna), aby zastąpić te podejścia:
INDIRECT("C2:" & ROWS(C:C))

OFFSET(C2,,, ROWS(C2:C), COLUMNS(C2:2))
  • Istnieje mała wada w użyciu C2:K: =ARRAYFORMULA(COLUMN(C2:K))zwróci tablicę numerów kolumn nawet dla nieistniejących, więc musimy użyć =SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))zamiast tego.
1 kishkin Jan 04 2021 at 19:17

Myślę, że istnieje prosta odpowiedź na średnie wierszowane przy użyciu VLOOKUPi QUERY.

Ten jest w 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
    )
  )
)

  • Można to łatwo zmienić dla max, min, sum, count - po prostu zmień funkcję agregacji w QUERYinstrukcji.
  • To samo podejście można zastosować do agregacji według kolumn.
  • FLATTEN(C2:J) można zmienić na:
    • FLATTEN(--C2:J)traktować puste komórki jako 0s;
    • FLATTEN(IFERROR(1/(1/C2:J)))wykluczyć 0s ze średniej.
  • Jeśli nie ma pośrednich pustych wierszy, VLOOKUPmożna je usunąć z formuły, a także Col1z SELECTinstrukcji.
  • Istnieje krótsza wersja (dzięki @MattKing!) Bez VLOOKUPi 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) ''"
  )
)

Używam C2:Jzakresu z kolumnami do I:I, kilka szczegółów na ten temat:

  • Zakres, C2:Jktóry jest o jedną kolumnę większy niż w rzeczywistości istnieje na arkuszu. Nie tylko podaje zakres wszystkich kolumn po prawej stronie C2i wszystkich wierszy od niego, ale także aktualizuje w przypadku dodania kolejnej kolumny po prawej stronie arkusza: demo . Chociaż nie można tego podkreślać. Może C2:Jto prawie doskonale (wystąpi problem, jeśli ZZZna arkuszu faktycznie występuje kolumna), aby zastąpić te podejścia:
INDIRECT("C2:" & ROWS(C:C))

OFFSET(C2,,, ROWS(C2:C), COLUMNS(C2:2))
  • Istnieje mała wada w użyciu C2:J: =ARRAYFORMULA(0 * COLUMN(C2:J))zwróci tablicę numerów kolumn nawet dla nieistniejących (pomnożonych przez 0), więc musimy użyć =SEQUENCE(1, COLUMNS(C2:J),,)zamiast tego.

@ player0, jakieś przemyślenia na ten temat?