Формулы таблиц Excel

Question

Я хотел бы иметь возможность иметь ячейку на 2-й вкладке, равную той, которая была на предыдущей вкладке, затем создать новую вкладку, скопировать и вставить и обновить формулу электронной таблицы на предыдущей вкладке вместо ссылки на первую вкладку.

Т.е. формула для предыдущей работы на вкладке 2 равна значению на вкладке 1, выполненному до вкладки.

Когда я копирую и вставляю в новую вкладку (3), чтобы создать следующий лист прогресса, ссылка на формулу листа по-прежнему относится к вкладке 1 вместо обновления на вкладку 2. Это можно обойти?

Answer 1

Excel может только ссылаться на листы абсолютно. В отличие от ссылок на ячейки, которые могут быть относительными и изменяться при копировании, ссылки на листы всегда являются абсолютными.

Относительные ссылки на листы запрашивались как новая функция Excel несколько раз в прошлом, но до сих пор не реализованы.

Answer 2

Относительная ссылка на лист довольно проста, если вы хотите использовать немного VBA.  Создайте следующую функцию VBA:

Function SHEET_OFFSET(Offset, Ref)
'   Returns cell contents at Ref, in sheet Offset
    Application.Volatile
    With Application.Caller.Parent
        SHEET_OFFSET = .Parent.Sheets(.Index + Offset) _
         .Range(Ref.Address).Value
    End With
End Function

См. Как добавить VBA в MS Office? для общей информации об использовании VBA.

Затем, если вы хотите, чтобы ячейка в Sheet2 доступ к Sheet1!Q42 , но затем, когда вы копируете с Sheet2 на Sheet3 , вы хотите, чтобы соответствующая ячейка получила доступ к Sheet2!Q42 , используйте формулу

SHEET_OFFSET(-1, Q42)

Первый аргумент - это номер листа относительно текущего, а второй аргумент - ссылка на ячейку.  Когда вы копируете эту формулу в Sheet(n) , она автоматически будет ссылаться на Sheet(n-1) .