Google E-Tablolar'da Sonsuz Gerçek Dinamik Aralıkta Ortalama ArrayFormülü

Dec 24 2020

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

AVERAGEDinamik 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

4 player0 Dec 24 2020 at 14:34

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

Colreferansları 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 avgiçin maxya 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 TRIMiçin basit bir IFifade kullanıyoruz ve tanıtıyoruz örn. ofseti karşılamak için:


MMULT

3. seviye:

MMULTtoplama, çı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ü MMULTboş 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 alanC2:? (all rows, all columns)
  • kütle hesaplamalarını engelleyen geçerli alanC2:? (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:G9için MMULTortalama şö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)''"))))
2 ErikTyler Dec 24 2020 at 18:27

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

2 MattKing Feb 16 2021 at 23:04

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.

1 kishkin Dec 25 2020 at 20:43

@ 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: C2aş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 0boş 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 FILTERbu, 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 FILTERbirlikte 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 FILTERs 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 MMULTortalamayı 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"), 0arama 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ı

  • INDEXARRAYFORMULAkısalık yerine kullanılabilir (teşekkürler @ player0, bunu bana birkaç ay önce öğretti), ancak belirsizliğini seviyorum ARRAYFORMULA.
  • Kullandığım SEQUENCEbir sütun ya da bir satır oluşturmak için 1açı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 SEQUENCEiç uygulamasının bir iktidara yükseltmekten daha basit olması gerekir.

  • C2:KSayfada 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 sonraki C2tü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. Bu C2:K, neredeyse mükemmel bir şekilde ( ZZZbir 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 .
1 kishkin Jan 04 2021 at 19:17

Ben sıra sıra ortalama kullanmak için basit bir cevabı olduğunu düşünüyorum VLOOKUPve 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 QUERYifade 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ücreleri 0s olarak tedavi etmek ;
    • FLATTEN(IFERROR(1/(1/C2:J)))0s ortalamadan hariç tutmak için .
  • Ara boş satır yoksa VLOOKUP, formülden olduğu Col1kadar SELECTifadeden de çıkarılabilir .
  • Daha kısa bir versiyonu (! Teşekkürler @MattKing) olmadan var VLOOKUPve 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) ''"
  )
)

C2:JSütunlara sahip aralık kullanıyorum I:I, bununla ilgili bazı ayrıntılar:

  • Sayfada C2:Jgerçekte var olandan bir sütun daha fazla olan aralık . Sadece sağındaki tüm sütunların bir aralığını ve ondan sonraki C2tü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. Bu C2:Jneredeyse mükemmel bir şekilde ( ZZZbir 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ılan 0) 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ı?