При использовании именованного диапазона в Excel (2013), как отобразить весь диапазон, но сместить одну ячейку?
У меня есть диапазон $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 указывает, что решение включает использование формулы массива.
2 ответа
Одним из решений является ввод ссылки на именованный диапазон в виде формулы массива. Для этого выберите весь желаемый выходной диапазон, B11:H11 в вашем примере. Не изменяя выбор, введите формулу
=Distances
и нажмите Ctrl+Shift+Enter. Это введет формулу во весь выбранный диапазон и даст вам то, что вы хотите.
Вариант 1. Вы можете использовать INDEX для возврата любого значения из именованного диапазона, по строке и столбцу. Итак, в вашем примере, в B11 (или где угодно) вы можете использовать:
= ИНДЕКС (Расстояния,1,1)
чтобы получить значение из первого ряда и первого столбца. Но это не будет идеальным для наклеивания. Так что используйте текущий номер COLUMN и отрегулируйте для начальной позиции:
= ИНДЕКС (Расстояния, 1, колонка ()-10)
Это скопирует довольно счастливо. -10 - это корректировка, поскольку начинается в столбце 11 (в вашем примере).
Вариант 2: вы пытались использовать метод неявного пересечения, просто используя "= Расстояния", который предполагает, что вы хотите найти, где этот диапазон пересекается со строкой или столбцом текущей ячейки (он будет пересекаться только с одной строкой или одним столбцом, или ни одного), Вместо этого попробуйте использовать явное пересечение, где вы указываете два диапазона, разделенных пробелом (поэтому вы не можете использовать пробелы в именах диапазонов). Они оба могут быть именованными диапазонами или, в вашем случае, просто использовать в B11:
= Расстояния A:A
Это будет пересекать ваш диапазон расстояний в A2:G2 со столбцом A (а не B, где формула). И это, конечно, будет скопировано, или вниз, довольно счастливо.