Невозможно реализовать INDIRECT () в формуле массива

У меня есть лист с именем Finder, на котором я создал инструмент для поиска на других листах (с именами №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,...

это нормально, но я не могу использовать INDIRECT, чтобы сделать формулу динамической:

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

заранее спасибо

ОБНОВЛЕНИЕ:

Finder Sheet

Инструмент Finder содержит множество ячеек. На рисунке показано, почему мне нужно получить имя листа с помощью функции ROW().

ОБНОВЛЕНИЕ 2:

# 1 лист

Поле со списком на листе Finder содержит четыре элемента и использует ячейку $ B $ 4:

    1- Approximate Search
    2- Exact Search
    3- Full Index
    4- Specific Category

Вот полный код формулы:

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

excel array-formulas excel-formula
person Mehdi    schedule 01.12.2013    source источник

Ответы (1)


arrow_upward
3
arrow_downward

Проблема вызвана использованием функций ROW и COLUMN. Эти функции возвращают «массивы», даже если только одно значение, например {1}, а не 1. В некоторых контекстах это не проблема, но в других случаях (например, здесь) в excel возникает проблема с преобразованием {1} в 1, и формулы не работают.

Одно из возможных решений - заключить все функции ROW и COLUMN в функцию типа MAX или SUM, которая будет преобразовывать за вас, например вместо того

ROW() - 5

использовать

SUM(ROW()) - 5

но лучший способ (и тот, который я рекомендую) - использовать функции ROWS или COLUMNS (с "S" в конце), поэтому, если первая формула находится в Z6, используйте

ROWS($Z$6:Z6) or COLUMNS($Z$6:Z6)

они оба дадут вам значение 1 в самом Z6, но по мере того, как вы копируете вниз или поперек, они будут увеличиваться на 1 каждый раз (и они не страдают тем же недостатком, что и ROW и COLUMN). В любом случае это лучшее решение, поскольку оно устраняет ваши -5 и менее подвержено ошибкам, если вы удаляете или добавляете строки или столбцы.

person barry houdini    schedule 01.12.2013
comment
Спасибо @barry houndini. Я добавил изображение, чтобы продемонстрировать, почему мне нужно использовать функцию ROW() для адресации имени листа. Фактически, я пытаюсь сделать банк словарного запаса. Поэтому я поместил много слов в листы №1, №2, .... На этих листах каждое слово имеет уникальный номер и помещается в столбец A, а слова - во второй столбец. (столбец B) Я попытался решить проблему с помощью SUM() и MAX(), но это не помогло. - person Mehdi; 01.12.2013
comment
Я снова обновил вопрос и добавил полный код. Пожалуйста, взгляните на это. Спасибо - person Mehdi; 01.12.2013
comment
Хорошо, я понял, почему вы использовали ROW, но это вызывает проблему, как я уже сказал ... но у вас нет номеров листов, перечисленных в столбце B, почему вы не можете их использовать - что было бы проще, чем ROW, ROWS или любой другой способ? - person barry houdini; 01.12.2013
comment
Я действительно понятия не имею об использовании столбца B. Формула используется в C6, D6, F6, G6, ..., C7, D7, F7, G7 и так далее. Как мне получить текущую строку ?! - person Mehdi; 01.12.2013
comment
Ваша формула использует ROW () - 5 в строке 6, поэтому вы получаете 1 .... но в B6 у вас уже есть 1, поэтому вместо использования ROW () - 5 (что в любом случае вызывает у вас проблемы), почему бы и нет просто обратитесь к B6? В остальной части столбца B у вас есть 2,3,4 и т. Д., Так что это будет работать для каждой строки - person barry houdini; 01.12.2013
comment
Прости! Я тебя еще не понял! Формула должна оставаться постоянной во всех ячейках. Как я могу явно использовать данные в столбце B? - person Mehdi; 01.12.2013
comment
У вас должна быть возможность заменить все экземпляры (ROW () - 5) на $ B6 - person barry houdini; 01.12.2013
comment
Я использовал формулу в строке 6, 7, 8, ... 100. $ B6 всегда получает 6, и, следовательно, мне приходится обновлять формулу для каждой строки, что вызовет боль. Мне нужно, чтобы динамическая формула оставалась неизменной и применялась ко всем ячейкам. - person Mehdi; 01.12.2013
comment
позвольте нам продолжить это обсуждение в чате - person Mehdi; 01.12.2013
comment
Еще раз спасибо, Барри! Я написал настоящую формулу для первой ячейки с обновляемой строкой и столбцом. Поэтому, когда я заполняю его другими ячейками, он обновляется автоматически. - person Mehdi; 01.12.2013