Google शीट में अनंत सचाई गतिशील रेंज पर औसत का ArrayFormula

Dec 24 2020

उदाहरण के अनुसार:

     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शब्दों के गतिशील अर्थ (अज्ञात मात्रा में पंक्तियों और स्तंभों की अज्ञात मात्रा) में प्रत्येक वैध पंक्ति के लिए सबसे इष्टतम तरीका क्या है ?

जवाब

4 player0 Dec 24 2020 at 14:34

क्वेरी के

स्तर 1:

अगर C2 में सभी 5 सेल हैं : G में मान हैं:

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

यदि नहीं, तो पंक्तियाँ छोड़ दी जाती हैं:

यदि खाली कोशिकाओं को शून्य माना जाता है:

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

शून्य मानों को हटाने के लिए जिनका हम उपयोग करते हैं IFERROR(1/(1/...)):

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

Colसंदर्भों को गतिशील बनाने के लिए हम कर सकते हैं:

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

लेवल 2:

यदि खाली कोशिकाओं को शून्य के रूप में नहीं माना जाता है और उन्हें छोड़ नहीं दिया जाना चाहिए:

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

ध्यान दें कि यह कॉलम ए निर्भर है, इसलिए कॉलम ए में लापता मान परिणामों को ऑफसेट करेगा

मजेदार तथ्य !! हम स्वैप कर सकते हैं avgकरने के लिए maxया min:

कॉलम A के कारावास से इसे मुक्त करने और किसी भी मान्य पंक्ति के लिए यह कार्य करने के लिए:

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

यदि वर्तमान 0 की सीमा औसत नहीं होनी चाहिए तो हम एक छोटा सा IF स्टेटमेंट जोड़ सकते हैं:

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

यहाँ हमने तथाकथित "वर्टिकल क्वेरी स्मैश" का इस्तेमाल किया है, जो किसी दिए गए रेंज में सभी मान लेता है और इसे एक सिंगल कॉलम पर केंद्रित करता है, जहाँ प्रत्येक पंक्ति की सभी कोशिकाएँ एक खाली स्थान के साथ एक प्रतिफल के रूप में जुड़ जाती हैं:

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

इसके अलावा, "क्षैतिज क्वेरी स्मैश" भी है :

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

और "अल्टीमेट 360 ° डबल क्वेरी स्मैश" जो सभी सेल्स को एक सिंगल सेल में रेंज से डालता है:

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

और अंत में "कुख्यात नकारात्मक 360 ° रिवर्स डबल क्वेरी स्मैश" जो पंक्तियों पर कॉलम को प्राथमिकता देता है:

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

सभी क्वेरी स्मैश नामों का कॉपीराइट किया जाता है

इस विषय पर वापस ... जैसा कि ऊपर प्रति पंक्ति में सभी कक्षों के ऊपर उल्लेख किया गया है, रिक्त स्थान के साथ भी रिक्त स्थान शामिल हैं, इसलिए हमें एक ऐसी स्थिति मिली जहां हम मूल्यों के बीच डबल या कई रिक्त स्थान प्राप्त कर रहे हैं। इसे ठीक करने के लिए हम दिए गए रेंज में खाली पंक्तियों के लिए 0 मान निर्दिष्ट करने के लिए TRIMएक सरल IFकथन का उपयोग करते हैं और पेश करते हैं। ऑफ़सेट का सामना करने के लिए:

MMULT

स्तर 3:

MMULTएक प्रकार का भारी वर्ग सूत्र है जो जोड़, घटाव, गुणन, विभाजन को पूरा करने में सक्षम है, यहाँ तक कि सरणियों / मैट्रिक्स पर कुल चल रहा है ... हालाँकि, बड़ा डेटासेट = सूत्र गणना को धीमा करता है (क्योंकि MMULTखाली पंक्तियों में भी + - × ÷ऑपरेशन करने में समय लगता है ) ... जब तक कि हम दोनों दिशाओं में वास्तव में गतिशील रेंज का उपयोग न करें ...

दी गई सीमा के मानों के साथ अंतिम पंक्ति प्राप्त करने के लिए:

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

किसी दिए गए रेंज के मानों के साथ अंतिम कॉलम पाने के लिए:

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

अब हम इसे सरल तरीके से बना सकते हैं:

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

जो इस प्रकार है:

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

या कम विकल्प:

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

इसलिए सरलीकृत MMULT फॉर्मूला होगा:

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

यदि हम शून्य मानों को श्रेणी से बाहर करना चाहते हैं, तो सूत्र यह होगा:

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

स्तर 4:

इसे अनंत गतिशील बनाने के लिए और फिर भी वैध डेटासेट तक सीमित रखने के लिए सभी को एक साथ रखना:

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

फिर से, श्रेणी में शून्य वाले सेल शामिल नहीं हैं:

सम्मानपूर्वक उल्लेख:

@ एरिक टायलर स्तर:

पिछले सूत्र के ध्रुवीय विपरीत को चलाने के लिए MMULTहोगा

के बजाय कुल क्षेत्रफलC2:? (all rows, all columns)
वैध क्षेत्र जो की बड़े पैमाने पर गणना से बचा जाता हैC2:? (excluding empty rows and columns)0 × 0 = 0

शून्य सहित:

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

शून्य को छोड़कर:

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

@ किस्किन स्तर:

एक निश्चित सीमा के 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, )))

@ मैटिंग स्तर:

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

शून्य को छोड़कर:

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

खाली सेल सहित:

=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

आपने इसमें एक टन समय लगाया। मुझे उम्मीद है कि लोग इसकी सराहना करेंगे, और अधिक ताकि आप इसे बाकी सभी के लिए करें और अपने लिए नहीं।

अपने अंतिम फ़ार्मुलों को देखते हुए, ये समान परिणाम उत्पन्न करने चाहिए (C2 में डेटा दें: अपने उदाहरणों में):

B2 में (शून्य शामिल करें):

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

बी 2 (शून्य को छोड़कर) में:

=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

अद्यतन: मैंने अपने मूल पोस्ट से सूत्र अपडेट किया है। आरओडब्ल्यू () को हमेशा पहले आना चाहिए ताकि डेटा में लापता मान विभाजन को फेंक न दें।

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

तब तक काम करना चाहिए जब तक मैं प्रश्न को गलत नहीं समझ लेता।

Vlookups या mmults या फिल्टर या कुछ भी करने के लिए कोई ज़रूरत नहीं है।

1 kishkin Dec 25 2020 at 20:43

मैं @ player0 के उत्तर के लिए थोड़ा अतिरिक्त बनाने की कोशिश करूंगा। और मैं वास्तव में इस के अनुकूलन पर किसी भी टिप्पणी की सराहना करूंगा।

मामले में डेटा श्रेणी के अंदर बहुत सी खाली पंक्तियाँ और स्तंभ होते हैं, जिन्हें बाहर रखा जा सकता है MMULT।

चरण 1 - खाली पंक्तियों को फ़िल्टर करें

हमें एक डेटा श्रेणी मिली है: C2नीचे से अंतिम पंक्ति और दाईं ओर अंतिम कॉलम (जो है J:J)। मैं C2:Kस्पष्टीकरण के लिए नीचे दिए गए विवरणों का उपयोग करूंगा ।

यह सूत्र हमें पंक्ति संख्याओं की एक सरणी देगा जहां कम से कम एक गैर रिक्त सेल है। इसके अलावा, 0अगर कोई खाली पंक्तियाँ होंगी, लेकिन यह इस सरणी में खोज के लिए कोई मायने नहीं रखेगा, या हम इसे तब फ़िल्टर करेंगे जब यह काम करेगा:

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

इसलिए, हमारे द्वारा उपयोग की जाने वाली डेटा रेंज से खाली पंक्तियों को फ़िल्टर करने के लिए, FILTERजो यह जाँच करेगा कि क्या कोई पंक्ति ऊपर से हमारे सरणी में है और यदि उस स्थिति में है तो छोड़ दें:

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

चरण 2 - खाली कॉलमों को फ़िल्टर करें

केवल गैर-रिक्त कॉलम संख्याओं की एक सरणी प्राप्त करने के लिए हम लगभग एक ही सूत्र का उपयोग कर सकते हैं:

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

अंत में विवरण देखने के SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))बजाय क्यों उपयोग किया जाता है COLUMN(C2:K)।

खाली कॉलम को फ़िल्टर करने के लिए हम अपने एरे में कॉलम नंबर खोजने के लिए शर्त के FILTERसाथ भी उपयोग करते 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
    )
  )
)

और खाली पंक्तियों और खाली कॉलमों को छानने के लिए हम सिर्फ दो FILTERएस का उपयोग करते हैं :

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

मूल डेटा रेंज आंतरिक रूप से बन जाएगी:

चरण 3 - करो `MMULT`

अब हम MMULTऔसत की गणना करने के लिए उस डेटा सेट का उपयोग कर सकते हैं :

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

यह मूल डेटा पंक्तियों के संबंध में थोड़ा हटकर है।

चरण 4 - AVERAGE कॉलम भरें

मूल डेटा पंक्तियों के अनुरूप औसत बनाने के लिए हम 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
  ))
)

कहा पे

SEQUENCE(MAX((C2:K <> "") * ROW(C2:K)) - 1, 1, ROW(C2))पंक्ति संख्या की एक सरणी है जो 2 से एक से अंतिम-खाली एक नहीं है। हम खाली तारों के साथ सभी पंक्तियों को नहीं भरेंगे।
QUERY(UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))), "WHERE Col1 <> 0")0खोज के लिए कुंजी के रूप में उपयोग किए गए फ़िल्टर किए गए गैर-रिक्त पंक्ति संख्याओं की एक सरणी है ।
IFNA एक खाली स्ट्रिंग को एक खाली डेटा पंक्ति के साथ डालने के लिए वापस करेगा।

अंतिम फार्मूला

यह सब एक साथ डालें:

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

कुछ विवरण

INDEXके बजाय इस्तेमाल किया जा सकता ARRAYFORMULAसंक्षिप्तता के लिए (धन्यवाद @ player0, मुझे सिखाया है कि कुछ महीने पहले), लेकिन मुझे के unambiguity की तरह ARRAYFORMULA।
मैं स्पष्टता के लिए, SEQUENCEकॉलम या पंक्ति की पंक्ति का 1उपयोग स्पष्ट करने के लिए करता हूं । उदाहरण के लिए, यह एक

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

के साथ प्रतिस्थापित किया जा सकता है

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

जो थोड़ा छोटा है। एक तरीका यह भी है कि यहां @ खिलाडियों को शक्ति प्रदान करने के लिए 0:

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

लेकिन (यह सिर्फ मेरी अटकलें हैं) मुझे लगता है कि SEQUENCEआंतरिक कार्यान्वयन सरल होना चाहिए फिर एक शक्ति को बढ़ाने का संचालन।

मैं रेंज का उपयोग करता हूं C2:Kजो कि शीट पर वास्तव में मौजूद होने की तुलना में एक कॉलम अधिक है। न केवल यह सभी स्तंभों के दाईं ओर C2और सभी पंक्तियों की एक सीमा देता है , बल्कि यह शीट के दाईं ओर एक और कॉलम जोड़ने के मामले में भी अपडेट करता है: एक डेमो । हालांकि इसे उजागर नहीं किया जाता है। यह C2:Kलगभग पूरी तरह से हो सकता है (अगर ZZZशीट पर वास्तव में कॉलम मौजूद है, तो उन समस्याओं की जगह लेने में समस्या होगी :

INDIRECT("C2:" & ROWS(C:C))

OFFSET(C2,,, ROWS(C2:C), COLUMNS(C2:2))

उपयोग करने में एक छोटी खामी है C2:K: =ARRAYFORMULA(COLUMN(C2:K))गैर-मौजूदा वाले के लिए भी कॉलम संख्याओं की एक सरणी लौटाएगा, इसलिए हमें =SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))इसके बजाय उपयोग करने की आवश्यकता है ।

1 kishkin Jan 04 2021 at 19:17

मुझे लगता है कि पंक्ति-वार औसत उपयोग करने के लिए एक सरल उत्तर है VLOOKUPऔर QUERY।

यह एक में है 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
    )
  )
)

इसे अधिकतम, न्यूनतम, सम, काउंट के लिए आसानी से बदला जा सकता है - केवल QUERYकथन के अंदर एकत्रीकरण फ़ंक्शन को बदलें ।
स्तंभ-वार एकत्रीकरण के लिए समान दृष्टिकोण का उपयोग किया जा सकता है।
FLATTEN(C2:J) इसे बदला जा सकता है:
- FLATTEN(--C2:J)खाली कोशिकाओं के इलाज के लिए 0;
- FLATTEN(IFERROR(1/(1/C2:J)))0औसत से बाहर करने के लिए ।
यदि कोई मध्यवर्ती रिक्त पंक्तियाँ नहीं हैं, VLOOKUPतो सूत्र से, साथ ही कथन Col1से हटाया जा सकता है SELECT।
एक छोटा संस्करण है (धन्यवाद @MattKing!) बिना VLOOKUPऔर 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:Jसीमा तक कॉलम का उपयोग करता हूं I:I, जिस पर कुछ विवरण:

रेंज C2:Jजो कि एक से अधिक स्तंभ है वास्तव में शीट पर मौजूद है। न केवल यह सभी स्तंभों के दाईं ओर C2और सभी पंक्तियों की एक सीमा देता है , बल्कि यह शीट के दाईं ओर एक और कॉलम जोड़ने के मामले में भी अपडेट करता है: एक डेमो । हालांकि इसे उजागर नहीं किया जाता है। यह C2:Jलगभग पूरी तरह से हो सकता है (अगर ZZZशीट पर वास्तव में कॉलम मौजूद है, तो उन समस्याओं की जगह लेने में समस्या होगी :

INDIRECT("C2:" & ROWS(C:C))

OFFSET(C2,,, ROWS(C2:C), COLUMNS(C2:2))

उपयोग करने में एक छोटी सी खामी है C2:J: =ARRAYFORMULA(0 * COLUMN(C2:J))गैर-मौजूदा वाले (द्वारा गुणा 0) के लिए भी कॉलम संख्या की एक सरणी लौटाएगा , इसलिए हमें =SEQUENCE(1, COLUMNS(C2:J),,)इसके बजाय उपयोग करने की आवश्यकता है ।

@ खिलाड़ी 0, इस पर कोई विचार?

"प्रतीक नहीं मिल सकता है" या "प्रतीक का समाधान नहीं कर सकता" त्रुटि का मतलब है?

मुझे PHP में उपयोगी त्रुटि संदेश कैसे मिल सकते हैं?

ऑब्जेक्ट स्लाइसिंग क्या है?

एक नियमित अभिव्यक्ति का उपयोग करके ईमेल पते को कैसे मान्य किया जाए?

C ++ पहचानकर्ता में अंडरस्कोर का उपयोग करने के बारे में क्या नियम हैं?

मुझे क्यों नहीं # निकलना चाहिए <बिट्स / stdc ++। H>?

क्या Are एरो फंक्शंस ’और ions फंक्शन्स’ समतुल्य / विनिमेय हैं?

सीखना नियमित भाव [बंद]

मेरे जावास्क्रिप्ट कोड को "नहीं 'एक्सेस-कंट्रोल-अलाउंस-ओरिजिन' हेडर अनुरोधित संसाधन पर मौजूद है।"

मैं संख्याओं के रूप में इनपुट कैसे पढ़ सकता हूं?

मैं PHP से जावास्क्रिप्ट में चर और डेटा कैसे पास करूं?