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 AVERAGEkaż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ć 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 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: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)''"))))
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 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 ta0odfiltrowana jest używana jako klucze do wyszukiwania.IFNAzwró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ć zamiastARRAYFORMULAdla zwięzłości (dzięki @ player0, nauczył mnie tego kilka miesięcy temu), ale lubię jednoznacznośćARRAYFORMULA.- Używam
SEQUENCEdo konstruowania kolumny lub wiersza1s, 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 stronieC2i 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:Kto prawie idealnie (wystąpi problem, jeśliZZZna 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 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 jako0s;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żeCol1zSELECTinstrukcji. - Istnieje krótsza wersja (dzięki @MattKing!) Bez
VLOOKUPiWHERE 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 stronieC2i 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:Jto prawie doskonale (wystąpi problem, jeśliZZZna 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?