ArrayFormula of Average on Infinite Truly Dynamic Range w Arkuszach Google
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 AVERAGE
każdego prawidłowego wiersza w dynamicznym znaczeniu terminów (nieznana liczba wierszy i nieznana liczba kolumn)?
Odpowiedzi
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ć Col
referencje 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ć avg
na max
lub 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 TRIM
i wprowadzamy prostą IF
instrukcję przypisującą 0 wartości pustym wierszom w danym zakresie np. aby przeciwdziałać przesunięciu:

MMULT
poziom 3:
MMULT
jest 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ż MMULT
nawet 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 MMULT
on
- całkowita powierzchnia zamiast
C2:?
(all rows, all columns)
- ważny obszar, który pozwala uniknąć obliczeń masy
C2:?
(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:G9
MMULT
=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)''"))))
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))))
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.
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 C2
doł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ł, 0
jeś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, FILTER
któ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ć FILTER
z MATCH
warunku, 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 FILTER
s:
=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ć MMULT
tego 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 VLOOKUP
nastę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 ta0
odfiltrowana 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
INDEX
można użyć zamiastARRAYFORMULA
dla zwięzłości (dzięki @ player0, nauczył mnie tego kilka miesięcy temu), ale lubię jednoznacznośćARRAYFORMULA
.- Używam
SEQUENCE
do konstruowania kolumny lub wiersza1
s, 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:K
który jest o jedną kolumnę więcej niż w rzeczywistości istnieje na arkuszu. Nie tylko podaje zakres wszystkich kolumn po prawej stronieC2
i 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żeC2:K
to prawie idealnie (wystąpi problem, jeśliZZZ
na 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.
Myślę, że istnieje prosta odpowiedź na średnie wierszowane przy użyciu VLOOKUP
i 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
QUERY
instrukcji. - 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 jako0
s;FLATTEN(IFERROR(1/(1/C2:J)))
wykluczyć0
s ze średniej.
- Jeśli nie ma pośrednich pustych wierszy,
VLOOKUP
można je usunąć z formuły, a takżeCol1
zSELECT
instrukcji. - Istnieje krótsza wersja (dzięki @MattKing!) Bez
VLOOKUP
iWHERE 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:J
zakresu z kolumnami do I:I
, kilka szczegółów na ten temat:
- Zakres,
C2:J
który jest o jedną kolumnę większy niż w rzeczywistości istnieje na arkuszu. Nie tylko podaje zakres wszystkich kolumn po prawej stronieC2
i 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żeC2:J
to prawie doskonale (wystąpi problem, jeśliZZZ
na 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 przez0
), więc musimy użyć=SEQUENCE(1, COLUMNS(C2:J),,)
zamiast tego.
@ player0, jakieś przemyślenia na ten temat?