Google E-Tablolar'da Sonsuz Gerçek Dinamik Aralıkta Ortalama ArrayFormülü
örnek olarak:
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 |
|======|=======|=====|=====|=====|=====|=====|=====
∞ | | | | | | | |
AVERAGE
Dinamik terim anlamında her geçerli satırı elde etmenin en uygun yolu nedir (bilinmeyen satır sayısı ve bilinmeyen sütun sayısı)?
Yanıtlar
SORGU
Seviye 1:
C2: G aralığındaki 5 hücrenin tümünün değerleri varsa:
=QUERY(QUERY(C2:G, "select (C+D+E+F+G)/5"), "offset 1", )

değilse, satırlar atlanır:

boş hücreler sıfır olarak kabul edilirse:
=INDEX(QUERY(QUERY({C2:G*1}, "select (Col1+Col2+Col3+Col4+Col5)/5"), "offset 1", ))

sıfır değerleri kaldırmak için IFERROR(1/(1/...))
sarmayı kullanıyoruz:
=INDEX(IFERROR(1/(1/QUERY(QUERY({C2:G*1},
"select (Col1+Col2+Col3+Col4+Col5)/5"), "offset 1", ))))

Col
referansları dinamik hale getirmek için şunları yapabiliriz:
=INDEX(IFERROR(1/(1/QUERY(QUERY({C2:G*1},
"select "&
"("&JOIN("+", "Col"&ROW(INDIRECT("1:"&COLUMNS(C:G))))&")/"&COLUMNS(C:G)),
"offset 1", ))))

Seviye 2:
boş hücreler sıfır olarak kabul edilmiyorsa ve atlanmaması gerekiyorsa:
=INDEX(TRANSPOSE(QUERY(TRANSPOSE(E2:I),
"select "&TEXTJOIN(",", 1, IF(A2:A="",,
"avg(Col"&ROW(A2:A)-ROW(A2)+1&")")))),, 2)

bunun A sütununa bağımlı olduğuna dikkat edin, bu nedenle A sütunundaki eksik değerler sonuçları dengeleyecektir
eğlenceli gerçek !! Biz takas olabilir avg
için max
ya min
:

A sütununun sınırlandırılmasından kurtarmak ve geçerli herhangi bir satır için çalışmasını sağlamak için:
=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)

Aralıktaki 0'ların ortalaması alınmamalıysa, küçük bir EĞER ifadesi ekleyebiliriz:
=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)

burada , belirli bir aralıktaki tüm değerleri alan ve bunu tek bir sütunda yoğunlaştıran, her satırdaki tüm hücrelerin bir yan ürün olarak boş alanla birleştirildiği sözde "dikey sorgu parçalama" kullandık:
=FLATTEN(QUERY(TRANSPOSE(C2:G),,9^9))

bunun dışında "yatay sorgu çarpması" da vardır :
=QUERY(C2:G,,9^9)

ve ayrıca aralıktaki tüm hücreleri tek bir hücreye yerleştiren "nihai 360 ° çift sorgu parçalama" :
=QUERY(FLATTEN(QUERY(TRANSPOSE(C2:G),,9^9)),,9^9)

ve son olarak , sütunlara satırlara göre öncelik veren "kötü şöhretli negatif 360 ° ters çift sorgu çarpması" :
=QUERY(FLATTEN(QUERY(C2:G,,9^9)),,9^9)

tüm sorgu parçalama adları elbette telif hakkıyla korunmaktadır
konuya geri dön ... Yukarıda belirtildiği gibi aralıktaki her satırdaki tüm hücreler, boş olanlar bile boş alanla birleştirilir, bu nedenle değerler arasında iki veya daha fazla boşluk elde ettiğimiz bir durumla karşılaşırız. Bunu düzeltmek için, belirli bir aralıktaki boş satırlara 0 değerleri atamak TRIM
için basit bir IF
ifade kullanıyoruz ve tanıtıyoruz örn. ofseti karşılamak için:

MMULT
3. seviye:
MMULT
toplama, çıkarma, çarpma, bölme, hatta diziler / matrisler üzerinde toplam çalıştırma yapabilen bir tür ağır sınıf formülüdür ... ancak, veri kümesi ne kadar büyükse = formül hesaplaması daha yavaş (çünkü MMULT
boş satırlarda bile + - × ÷
işlemi gerçekleştirmek zaman alır ) ... her iki yönde de gerçekten dinamik aralık sonsuz kullanmazsak ...
belirli bir aralığın değerlerine sahip son satırı elde etmek için:
=INDEX(MAX(IF(TRIM(FLATTEN(QUERY(TRANSPOSE(
INDIRECT("C2:"&ROWS(A:A))),,9^9)))="",,ROW(A2:A))))

belirli bir aralıktaki değerlere sahip son sütunu elde etmek için:
=INDEX(MAX(IF(TRIM(QUERY(INDIRECT("C2:"&ROWS(A:A)),,9^9))="",,COLUMN(C2:2))))

şimdi bunu basit bir şekilde inşa edebiliriz:
=INDIRECT("C2:"&ADDRESS(9, 7))
şununla aynıdır:
=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))))))

veya daha kısa alternatif:
=INDEX(INDIRECT("C2:"&ADDRESS(
MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*ROW(A2:A)),
MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*COLUMN(C2:2)))))

bu nedenle basitleştirilmiş MMULT formülü şöyle olacaktır:
=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)))

sıfır değerlerini aralıktan çıkarmak istememiz durumunda formül şöyle olacaktır:
=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)))

Seviye 4:
sonsuz derecede dinamik ve hala geçerli veri kümesiyle sınırlı hale getirmek için yukarıdakilerin hepsini bir araya getirmek:
=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)))

yine, aralıkta sıfır olan hücreler dahil değil:

onurlu sözler:
@Erik Tyler seviyesi:
önceki formülün kutupsal zıttı MMULT
,
- yerine toplam alan
C2:?
(all rows, all columns)
- kütle hesaplamalarını engelleyen geçerli alan
C2:?
(excluding empty rows and columns)
0 × 0 = 0
sıfırlar dahil:
=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)))

sıfırlar hariç:
=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 seviyesi:
sabit bir aralık C2:G9
için MMULT
ortalama şöyle olacaktır:
=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 seviyesi:
=INDEX(QUERY(SPLIT(FLATTEN(ROW(C2:C)&"×"&OFFSET(C2,,,9^9, 9^9)), "×"),
"select avg(Col2)
group by Col1
label avg(Col2)''"))

sıfırlar hariç:
=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)''"))
boş hücreler dahil:
=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)''"))))
Buna çok zaman ayırdın. Umarım insanlar bunu daha çok takdir eder, böylece kendiniz için değil, herkes için yaptınız.
Nihai formüllerinize baktığınızda, bunlar aynı sonuçları vermelidir (örneklerinizdeki gibi C2 :?'deki verileri verin):
B2'de (sıfırlar dahil):
=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))))
B2'de (sıfırları hariç tutun):
=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))))
GÜNCELLEME: Formülü orijinal yazımdan güncelledim. SATIR () her zaman önce gelmelidir, böylece verilerdeki eksik değerler bölünmeyi bozmaz.
=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(ROW(C2:C)&"|"&OFFSET(C2,,,9^9,9^9)),"|"),"select AVG(Col2) group by Col1 label AVG(Col2)''"))
Soruyu yanlış anlamazsam işe yaramalı.
Vlookup'lara, mmult'lara veya filtrelere veya herhangi bir şeye gerek yok.
@ Player0'ın cevabına küçük bir ekleme yapmaya çalışacağım. Ve bunu optimize etmekle ilgili yorumlarınızı gerçekten takdir edeceğim.
Veri aralığı içinde çok sayıda boş satır ve sütun olması durumunda bunlar hariç tutulabilir MMULT
.
Adım 1 - Boş satırları filtreleyin
Bir veri aralığımız var: C2
aşağıdan son satıra ve sağdan son sütuna (yani J:J
). Kullanacağım C2:K
, açıklama için aşağıdaki ayrıntılara bakın.
Bu formül bize en az bir boş olmayan hücrenin olduğu bir dizi satır numarası verecektir. Ayrıca 0
boş satırlar varsa bir de olacaktır , ancak bu dizide aramanın önemi olmayacak veya önemli olduğunda onu filtreleyeceğiz:
=ARRAYFORMULA(
UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K)))
)

Bu nedenle, kullandığımız veri aralığındaki boş satırları filtrelemek için FILTER
bu, dizimizde yukarıdan bir satır olup olmadığını kontrol edecek ve bu durumda bırakacak:
=ARRAYFORMULA(
FILTER(
C2:K*1,
MATCH(
ROW(C2:K),
UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
0
)
)
)
Adım 2 - Boş sütunları filtreleyin
Yalnızca boş olmayan sütun numaralarından oluşan bir dizi elde etmek için hemen hemen aynı formülü kullanabiliriz:
=ARRAYFORMULA(
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))))
)

Ayrıntıları sonunda görmek SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))
yerine neden kullanılır COLUMN(C2:K)
.
Boş sütunları filtrelemek için dizimizdeki sütun numaralarını aramak için koşulla FILTER
birlikte kullanıyoruz MATCH
:
=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
)
)
)
Ve boş satırları ve boş sütunları filtrelemek için sadece iki FILTER
s kullanıyoruz:
=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
)
)
)
Orijinal veri aralığı dahili olarak şu hale gelecektir:

Adım 3 - Yapın MMULT
Şimdi MMULT
ortalamayı hesaplamak için bu veri kümesiyle kullanabiliriz:
=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
)
)
)

Orijinal veri satırlarıyla ilgili biraz yanlış.
Adım 4 - ORTALAMA sütununu doldurun
Ortalamaları orijinal veri satırlarıyla tutarlı hale getirmek için şu şekilde kullanabiliriz 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
))
)
Nerede
SEQUENCE(MAX((C2:K <> "") * ROW(C2:K)) - 1, 1, ROW(C2))
2. olandan boş olmayan sonuncuya kadar satır numaraları dizisidir. Tüm satırları boş dizelerle doldurmayacağız.QUERY(UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))), "WHERE Col1 <> 0")
,0
arama için anahtar olarak kullanılan filtrelenmiş boş olmayan satır numaraları dizisidir .IFNA
boş bir veri satırının yanına koymak için boş bir dize döndürecektir.
SON FORMÜL
Hepsini bir araya koy:
=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
))
)

Birkaç ayrıntı
INDEX
ARRAYFORMULA
kısalık yerine kullanılabilir (teşekkürler @ player0, bunu bana birkaç ay önce öğretti), ancak belirsizliğini seviyorumARRAYFORMULA
.- Kullandığım
SEQUENCE
bir sütun ya da bir satır oluşturmak için1
açıklık için, açık olması s. Örneğin, bu
SEQUENCE(
ROWS(
QUERY(
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
"WHERE Col1 <> 0"
)
),
1,
1,
0
)
ile değiştirilebilir
SIGN(
QUERY(
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
"WHERE Col1 <> 0"
)
)
bu biraz daha kısadır. Burada @ player0 tarafından aşağıdakilerin gücüne yükseltmenin bir yolu da var 0
:
QUERY(
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
"WHERE Col1 <> 0"
)^0
ancak (bu sadece benim spekülasyonumdur) Bence SEQUENCE
iç uygulamasının bir iktidara yükseltmekten daha basit olması gerekir.
C2:K
Sayfada gerçekte var olandan bir sütun daha fazla olan aralık kullanıyorum . Sadece sağındaki tüm sütunların bir aralığını ve ondan sonrakiC2
tüm satırları vermekle kalmaz, aynı zamanda sayfanın sağına başka bir sütun eklenmesi durumunda da güncellenir: bir demo . Vurgulanmamasına rağmen. BuC2:K
, neredeyse mükemmel bir şekilde (ZZZ
bir sayfada gerçekten sütun bulunması durumunda bir sorun olacaktır ) bu yaklaşımların yerini alabilir:
INDIRECT("C2:" & ROWS(C:C))
OFFSET(C2,,, ROWS(C2:C), COLUMNS(C2:2))
- Kullanmanın küçük bir dezavantajı var
C2:K
:=ARRAYFORMULA(COLUMN(C2:K))
var olmayanlar için bile bir sütun numarası dizisi döndürecektir, bu yüzden=SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))
onun yerine kullanmamız gerekir .
Ben sıra sıra ortalama kullanmak için basit bir cevabı olduğunu düşünüyorum VLOOKUP
ve QUERY
.
Bu, içinde 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
)
)
)

- Bu, max, min, sum, count için kolayca değiştirilebilir - sadece
QUERY
ifade içindeki toplama işlevini değiştirin . - Sütun bazında toplama için aynı yaklaşım kullanılabilir.
FLATTEN(C2:J)
şu şekilde değiştirilebilir:FLATTEN(--C2:J)
boş hücreleri0
s olarak tedavi etmek ;FLATTEN(IFERROR(1/(1/C2:J)))
0
s ortalamadan hariç tutmak için .
- Ara boş satır yoksa
VLOOKUP
, formülden olduğuCol1
kadarSELECT
ifadeden de çıkarılabilir . - Daha kısa bir versiyonu (! Teşekkürler @MattKing) olmadan var
VLOOKUP
veWHERE 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) ''"
)
)

C2:J
Sütunlara sahip aralık kullanıyorum I:I
, bununla ilgili bazı ayrıntılar:
- Sayfada
C2:J
gerçekte var olandan bir sütun daha fazla olan aralık . Sadece sağındaki tüm sütunların bir aralığını ve ondan sonrakiC2
tüm satırları vermekle kalmaz, aynı zamanda sayfanın sağına başka bir sütun eklenmesi durumunda da güncellenir: bir demo . Vurgulanmamasına rağmen. BuC2:J
neredeyse mükemmel bir şekilde (ZZZ
bir sayfada gerçekten sütun bulunması durumunda bir sorun olacaktır ) bu yaklaşımların yerini alabilir:
INDIRECT("C2:" & ROWS(C:C))
OFFSET(C2,,, ROWS(C2:C), COLUMNS(C2:2))
- Kullanmanın küçük bir dezavantajı var
C2:J
:=ARRAYFORMULA(0 * COLUMN(C2:J))
mevcut olmayanlar için bile (ile çarpılan0
) bir sütun numarası dizisi döndürecektir , bu yüzden=SEQUENCE(1, COLUMNS(C2:J),,)
bunun yerine kullanmamız gerekir .
@ player0, bu konuda herhangi bir düşünceniz var mı?