Я пробовал несколько формул и поиск в Google, но не смог найти ничего, что помогло бы моей проблеме. Для простоты, как объясняется на прилагаемом рисунке, как перетащить формулу вниз по вертикали, продолжая горизонтальную ссылку на ячейку в формуле
?
Как на картинке - формула вертикального направления - зеленая стрелка, а за горизонтальными ячейками - оранжевая стрелка.
Спасибо миллион!
Вы можете использовать функцию OFFSET , чтобы указать смещение ячейки от ячейки A1 . Даже если формула копируется вниз, это может быть горизонтальное смещение. Например, следующая формула, вставленная в ячейку B2 на снимке экрана, будет смещена вправо при копировании вниз (в качестве значений смещения используются числа в столбце A):
=OFFSET($A$1, 0, A2)
Энергонезависимое предложение с Индексом:
=A2-INDEX($1:$1,ROW(A2))
Смещение является изменчивым и приведет к пересчету всей рабочей книги при любой замене любой ячейки. Это может привести к замедлению в больших файлах со многими формулами.
Пояснение (после запроса разъяснения):
Первая строка содержит данные, начинающиеся со столбца B, который является вторым столбцом. Вышеприведенная функция Index() просматривает всю строку 1 и затем указывает на второй столбец, поскольку функция Row(A2) возвращает значение 2 .
Если формула перетаскивается вниз, бит Row(A2) изменяется на Row(A3) и возвращается число 3 , поэтому Index вернет третий столбец строки 1.
Чтобы настроить этот сценарий на другую настройку, вам необходимо проверить настройки. Какой столбец в строке 1 имеет первое значение данных? Второй, третий или n-й столбец?
Номер столбца с первым значением - это то, что вам нужно для числовой части в ссылке для функции Row(). Если ваши данные начинаются со столбца 2, то это будет Row(A2) . Если ваши данные начинаются в столбце 7, то это должна быть Row(A7) . Затем перетащите вниз, чтобы позволить Excel сделать свое дело с настройкой относительных ссылок.
Важным моментом является то, что вы правильно поняли стартовый номер.
То, что вы описываете, делается с помощью привязки части адреса, которую вы хотите исправить (строка и / или столбец), с префиксом $ . Поэтому, если вы хотите перетащить свою формулу вниз и иметь следующую ячейку, равную =A3-B1 , вам нужно исправить ссылку на строку 1 в столбце B. Вы бы сделали формулу ячейки B2 =A2-B$1 . Если вы перетащите его вниз в тот же столбец, ссылка A2 будет увеличиваться, но не ссылка B1.
Если вы хотите иметь возможность перетаскивания, то следующая ячейка справа будет содержать =A2-C1 , это ссылка на столбец, которую вы хотите заблокировать. Формула в вашей первой ячейке будет =$A2-B1 .
Если вы хотите перетащить как горизонтально, так и вертикально, чтобы заполнить всю таблицу таким образом, первая ячейка будет =$A2-B$1 . При горизонтальном перемещении единственное, что изменится, - это ссылка на столбец B. Перемещение по вертикали, единственное, что изменится, это ссылка на строку 2.