Я согласен, INDEX, безусловно, является лучшим ответом на это, чем INDIRECT, на самом деле он действительно ближе всего к селектору элементов массива, существующему в Excel. (Примечание: элемент нуля отсутствует, как во многих языках программирования, Excel работает со строками и столбцами, поэтому первый всегда равен 1 в любом направлении)
Обратите внимание, что вы можете использовать простую версию для выбора n-го элемента в массиве из одного столбца:
= INDEX(A1:A100,27) даст 27-й элемент A1:A100, то есть A27 или массив из одной строки:
= INDEX(A1:G1, 5) дает содержимое пятого столбца в массиве, поэтому в этом случае E1 (обратите внимание на двойную запятую, чтобы показать, что значение для строки не указано)
Вы можете сделать двумерные массивы:
= INDEX(A1:D100,27,4) будет содержимым D27 (строка 27, столбец 4 массива) и
= ИНДЕКС (C19:X43,5,7) дает I23
Теперь, если у вас есть двумерный массив и указан только один параметр, результатом будет целый столбец или строка. Хотя это не имеет смысла само по себе, оно прекрасно встроено в другую функцию.
Итак:= INDEX(A1:C5,1) - возвращает ошибку #REF, поскольку он даже не понимает, относится ли 1 к строке или столбцу.
= INDEX(A1:C5,1,) - возвращает #VALUE, потому что не может отобразить результирующий массив в ячейке, но учтите, что дополнительная запятая теперь явно определяет, что 1 означает первую строку.
= SUM(INDEX(A1:C5,1,)) - возвращает сумму всех значений в A1:C1, первой строке массива.
Однако, сказав все это, вам может вообще не понадобиться явно выбирать элемент массива. Из вашего примера видно, что вы пытаетесь проверить значение в известном столбце для той же строки, в которой была введена формула. Вы можете сделать это, используя неявное пересечение диапазона и текущей строки, например, если вы введете эту формулу в ячейку B13:
= IF(A:A = "X", "Да, это работает", "X не найден"), то вы можете скопировать это в любую другую строку, просто перетаскивая ее, и в каждой строке это неявно означает "элемент столбца A". который находится на том же номере строки, что и эта формула ".
Примечание: диапазон не обязательно должен быть целым столбцом, это может быть A4:A104 или даже на другом листе, но в каждом случае он будет использовать ячейку в той же фактической строке Excel, что и формула.
Я еще раз скажу последний бит: если в ячейке B5 используется формула с диапазоном A4:A104, вы получите не пятый элемент этого диапазона (A8), а ячейку A5. Если вы используете формулу в ячейке B3 вместо A4:A104, вы получите ошибку, потому что в строке 3 ничего нет (пересечения нет).
Итак, если ваши строки выстроены в ряд, эта неявная ячейка из диапазона действительно полезна (и даже более чиста, когда вы используете именованный диапазон для столбца), но если это не так, вам нужен INDEX, чтобы получить истинную репликацию [ n] для любого элемента массива.