excel - सरणी सूत्र में अप्रत्यक्ष() को लागू नहीं किया जा सकता



excel-formula array-formulas (1)

मेरे पास खोजकर्ता नामक एक शीट है जिस पर मैंने अन्य शीट्स (नाम # 1, # 2, आदि) में खोज करने के लिए एक उपकरण बनाया है। जिस समस्या का मुझे सामना करना पड़ा है वह है कि मैं कुछ मामलों में अप्रत्यक्ष कार्य का उपयोग नहीं कर सकता जैसा कि नीचे वर्णित है:

 { = IF(Finder!$B$4 = 1,
        INDEX(INDIRECT("'#" & (ROW() - 5) & IF(MOD(COLUMN(), 3) = 0, "'!$A$2:$A$100", "'!$B$2:$B$100")),
              SMALL(IF(ISERROR(SEARCH(Finder!$F$4, '#1'!$B$2:$B$100)),
                       "",
                       ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1),
                    INT(COLUMN() / 3)),
              1),
        IF(Finder!$B$4 = 2,
           INDEX(INDIRECT("'#" & (ROW() - 5) & "'!" & IF(MOD(COLUMN(), 3) = 0, "$A$2:$A$100", "$B$2:$B$100")),
                 SMALL(IF(Finder!$F$4 = '#1'!$B$2:$B$100,
                          ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1,
                          ""),
                       INT(COLUMN() / 3)),
                 1),... }

संकल्पनात्मक रूप:

 { = IF(Finder!$B$4 = 1,
        INDEX(using INDIRECT to address a range in another sheet successfully,
              SMALL(IF(ISERROR(SEARCH(cannot use INDIRECT here!)),
                       "",
                       Desired value),
                    Desired item),
              1),
        IF(Finder!$B$4 = 2,
           INDEX(using INDIRECT to address a range in another sheet successfully,
                 SMALL(IF(cannot use INDIRECT here!,
                          Desired value,
                          ""),
                       Desired item),
                 1),... }

मुझे नीचे की तरह एक और शीट में एक श्रेणी को संबोधित करना होगा:

SMALL(IF(ISERROR(SEARCH(Finder!$F$4, '#1'!$B$2:$B$100)),...

SMALL(IF(Finder!$F$4 = '#1'!$B$2:$B$100,...

यह ठीक है, लेकिन मैं सूत्र को डायनामिक बनाने के लिए अप्रत्यक्ष का उपयोग नहीं कर सकता:

SMALL(IF(ISERROR(SEARCH(Finder!$F$4, INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))),...

SMALL(IF(Finder!$F$4 = INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"),...

अग्रिम में धन्यवाद

अद्यतन करें:

खोजक उपकरण में कई कक्ष हैं चित्र दिखाता है कि ROW() फ़ंक्शन का उपयोग करके मुझे शीट नाम प्राप्त करने की आवश्यकता क्यों है I

अद्यतन 2:

फाइंडर शीट में कंबॉबॉक्स में चार आइटम हैं और $ B $ 4 सेल का उपयोग करता है:

    1- अनुमानित खोज
    2- सटीक खोज
    3 - पूर्ण सूचकांक
    4- विशिष्ट श्रेणी

यहां सूत्र का पूरा कोड है:

= IF(ISERROR(IF(Finder!$B$4 = 1,
                INDEX(INDIRECT("'#" & (ROW() - 5) & IF(MOD(COLUMN(), 3) = 0, "'!$A$2:$A$100", "'!$B$2:$B$100")),
                      SMALL(IF(ISERROR(SEARCH(Finder!$F$4, '#1'!$B$2:$B$100)),
                               "",
                               ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1),
                            INT(COLUMN() / 3)),
                      1),
                IF(Finder!$B$4 = 2,
                   INDEX(INDIRECT("'#" & (ROW() - 5) & "'!" & IF(MOD(COLUMN(), 3) = 0, "$A$2:$A$100", "$B$2:$B$100")),
                         SMALL(IF(Finder!$F$4 = '#1'!$B$2:$B$100,
                                  ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1,
                                  ""),
                               INT(COLUMN() / 3)),
                         1),
                   IF(Finder!$B$4 = 3,
                      IF(MOD(COLUMN(), 3) = 0,
                         20 * (ROW() - 6) + COLUMN() / 3,
                         INDIRECT(ADDRESS(INT(COLUMN() / 3) + 1, 2, 1, , "#" & (ROW() - 5)))),
                      INDEX(INDIRECT("'#" & (ROW() - 5) & "'!" & IF(MOD(COLUMN(), 3) = 0, "$A$2:$A$100", "$B$2:$B$100")),
                            SMALL(IF(Finder!$F$4 = '#1'!$I$2:$I$100,
                                     ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1,
                                     IF(ISERROR(SEARCH(Finder!$F$4 & ",", '#1'!$I$2:$I$100)),
                                        IF(ISERROR(SEARCH("," & Finder!$F$4, '#1'!$I$2:$I$100)),
                                           IF(ISERROR(SEARCH(", " & Finder!$F$4, '#1'!$I$2:$I$100)),
                                              "",
                                              ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1),
                                           ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1),
                                        ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1)),
                                  INT(COLUMN() / 3)),
                            1))))),
     " ",
     IF(Finder!$B$4 = 1,
        INDEX(INDIRECT("'#" & (ROW() - 5) & IF(MOD(COLUMN(), 3) = 0, "'!$A$2:$A$100", "'!$B$2:$B$100")),
              SMALL(IF(ISERROR(SEARCH(Finder!$F$4, '#1'!$B$2:$B$100)),
                       "",
                       ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1),
                    INT(COLUMN() / 3)),
              1),
        IF(Finder!$B$4 = 2,
           INDEX(INDIRECT("'#" & (ROW() - 5) & "'!" & IF(MOD(COLUMN(), 3) = 0, "$A$2:$A$100", "$B$2:$B$100")),
                 SMALL(IF(Finder!$F$4 = '#1'!$B$2:$B$100,
                          ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1,
                          ""),
                       INT(COLUMN() / 3)),
                 1),
           IF(Finder!$B$4 = 3,
              IF(MOD(COLUMN(), 3) = 0,
                 20 * (ROW() - 6) + COLUMN() / 3,
                 INDIRECT(ADDRESS(INT(COLUMN() / 3) + 1, 2, 1, , "#" & (ROW() - 5)))),
              INDEX(INDIRECT("'#" & (ROW() - 5) & "'!" & IF(MOD(COLUMN(), 3) = 0, "$A$2:$A$100", "$B$2:$B$100")),
                    SMALL(IF(Finder!$F$4 = '#1'!$I$2:$I$100,
                             ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1,
                             IF(ISERROR(SEARCH(Finder!$F$4 & ",", '#1'!$I$2:$I$100)),
                                IF(ISERROR(SEARCH("," & Finder!$F$4, '#1'!$I$2:$I$100)),
                                   IF(ISERROR(SEARCH(", " & Finder!$F$4, '#1'!$I$2:$I$100)),
                                      "",
                                      ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1),
                                   ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1),
                                ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100")) - MIN(ROW(INDIRECT("'#" & (ROW() - 5) & "'!$B$2:$B$100"))) + 1)),
                          INT(COLUMN() / 3)),
                    1)))))

समस्या ROW और COLUMN फ़ंक्शंस के उपयोग के कारण होती है। ये फ़ंक्शन "सरणियों" को वापस देता है, भले ही केवल एक एकल मान, जैसे 1. 1 के बजाय {1}। कुछ संदर्भों में कोई मुद्दा नहीं है लेकिन अन्य मामलों में (जैसे यहां), एक्सेल को {1} से 1 और सूत्रों को परिवर्तित करने में समस्या है काम नहीं करते।

एक संभव समाधान यह है कि MAX या SUM जैसे फ़ंक्शन में सभी ROW और COLUMN फ़ंक्शन को SUM जो आपके लिए परिवर्तित हो जाएगा, उदाहरण के बजाय

ROW() - 5

उपयोग

SUM(ROW()) - 5

लेकिन एक बेहतर तरीका (और मैं ROWS हूं) ROWS या COLUMNS फ़ंक्शंस (अंत में "S" के साथ) का उपयोग करना है, इसलिए यदि पहले सूत्र Z6 उपयोग में है

ROWS($Z$6:Z6) या COLUMNS($Z$6:Z6)

वे दोनों आपको Z6 में 1 के मान देंगे, लेकिन जैसा कि आप प्रतिलिपि या नीचे की ओर करते हैं, वे हर बार 1 से बढ़ेंगे (और वे ROW और COLUMN रूप में एक ही दोष नहीं COLUMN ) यह वैसे भी एक बेहतर समाधान है क्योंकि यह आपकी -5 एस के साथ करता है और त्रुटि को कम संवेदनशील होता है, आपको पंक्तियों या स्तंभों को हटा देना चाहिए या जोड़ना चाहिए