При использовании именованного диапазона в Excel (2013), как отобразить весь диапазон, но сместить одну ячейку?

Question

У меня есть диапазон $A$2:$G$2 именем Distances ; позже в листе я хочу заполнить диапазон B11:H11 данными в исходном диапазоне. Я могу начать в ячейке A11 и набрать =Distances и это заполнит значение из A2 . Используя маркер заполнения, я могу заполнить диапазон A11:G11 , но как только я пытаюсь поместить диапазон в B11:H11 , значения переходят напрямую. (Например, только значения из столбца 2 переходят в столбец 2, хотя я хочу, чтобы значение из столбца 1 (начало диапазона) заполняло ссылочную ячейку в столбце 2 (начало ссылки).) Я также не могу запустить ссылку на диапазон в столбце 2 (например, =Distances в B2) и заставить ссылочное значение отличаться от значения соответствующего столбца.

Как я могу ссылаться на диапазон где-то еще на листе, и чтобы первая ссылочная ячейка показывала значение из первой ячейки в именованном диапазоне? Это вообще возможно?

Изменить: Для ясности, я добавляю следующую визуализацию, хотя ответ @ Excell позаботился о проблеме довольно легко ...

Если мой именованный диапазон - это раздел abdf ниже, и я хочу отобразить те же значения abdf, но начиная с X , как мне это сделать?

+-+-+-+-+-+-+-+
|a|b|d|f| | | |
+-+-+-+-+-+-+-+
| | | | | | | |
+-+-+-+-+-+-+-+
| | |X| | | | |
+-+-+-+-+-+-+-+
| | | | | | | |
+-+-+-+-+-+-+-+

Ответ @ excellll указывает, что решение включает использование формулы массива.

Answer 1

Одним из решений является ввод ссылки на именованный диапазон в виде формулы массива. Для этого выберите весь желаемый выходной диапазон, B11:H11 в вашем примере. Не изменяя выбор, введите формулу

=Distances

и нажмите Ctrl+Shift+Enter. Это введет формулу во весь выбранный диапазон и даст вам то, что вы хотите.

Answer 2

Вариант 1. Вы можете использовать INDEX для возврата любого значения из именованного диапазона, по строке и столбцу. Итак, в вашем примере, в B11 (или где угодно) вы можете использовать:

= ИНДЕКС (Расстояния, 1,1)

чтобы получить значение из первого ряда и первого столбца. Но это не будет идеальным для наклеивания. Так что используйте текущий номер COLUMN и отрегулируйте для начальной позиции:

= ИНДЕКС (Расстояния, 1, колонка ()- 10)

Это скопирует довольно счастливо. -10 - это корректировка, поскольку начинается в столбце 11 (в вашем примере).

Вариант 2: Вы пытались использовать метод неявного пересечения, просто используя "= Расстояния", что предполагает, что вы хотите найти, где этот диапазон пересекается со строкой или столбцом текущей ячейки (он будет пересекаться только с одной строкой или одним столбцом, или ни одного), Вместо этого попробуйте использовать явное пересечение, где вы указываете два диапазона, разделенных пробелом (именно поэтому вы не можете использовать пробелы в именах диапазонов). Они оба могут иметь именованные диапазоны или, в вашем случае, просто использовать в B11:

= Расстояния A:A

Это будет пересекать ваш диапазон расстояний в A2:G2 со столбцом A (а не B, где формула). И это, конечно, будет скопировано, или вниз, довольно счастливо.