Допустим, в A1 у меня есть "= ROW()". Ячейка показывает «1», так как это первая строка на листе.
Теперь в A2 я поставил "= A1". К сожалению, это относится к ЗНАЧЕНИЮ А1, а не к формуле. Если бы он ссылался на формулу, то вместо этого A2 дал бы значение '2' (что я и хочу).
Если я затем продолжу изменять формулу в A1, то значение A2 также изменится, поскольку оно опирается на формулу в A1.
Как мне это сделать?
Там нет никакого способа сделать это изначально с функциями листа Excel. Если вам нужна эта функциональность, вам придется использовать VBA.
Вы можете использовать событие Worksheet_Change(). Он будет срабатывать при изменении содержимого ячейки, но не при простом пересчете. У него есть параметр, который указывает ячейки, которые были изменены; Вы можете использовать это, чтобы увидеть, нужно ли вам что-либо делать. Этот код должен находиться в области кода для используемого вами листа. (Откройте VBA с помощью Alt+F11; на левой панели дважды щелкните на листе. Вы должны увидеть что-то вроде «Microsoft Visual Basic - имя файла - [ имя листа (код)]» в заголовке.)
Очевидно, обновите имя листа и диапазоны, как требуется в приведенном ниже коде.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim test As Range, src As Range, dest As Range
Set src = Worksheets("Sheet1").Range("A1")
Set dest = Worksheets("Sheet1").Range("A2")
Set test = Intersect(Target, src)
If Not (test Is Nothing) Then
dest.Formula = src.Formula
End If
End Sub
Приведенный выше код точно скопирует формулу - если вы ссылаетесь на ячейку F3 в ячейке A1, у вас также будет ссылка на ячейку F3 в ячейке A2. Если вы не хотите, чтобы это происходило, измените строку внутри оператора If на dest.FormulaR1C1 = src.FormulaR1C1 . Это соответствующим образом изменит любые относительные ссылки в вашей формуле.
Вы можете изменить dest на больший диапазон, если хотите установить сразу несколько ячеек на основе формулы в ячейке A1.
Следует отметить, что это не приведет к автоматическому восстановлению формул в ячейках, если вы измените эти ячейки - то есть, если вы измените формулу в A2, это автоматически не вернет ее, потому что мы делаем обновление только тогда, когда ячейка A1 изменения. Если вы хотите защитить от этого, измените строку
Set test = Intersect(Target, src)
в
Set test = Intersect(Target, Union(src, dest))
Вы можете достичь этого с помощью таблиц (в Excel 2007: выберите диапазон, затем: Вставка - Таблица; первая строка должна содержать заголовки).
Каждый раз, когда вы изменяете формулу, она будет обновляться во всем столбце (или вас спросят, хотите ли вы обновить ее, если формула в столбце не изменилась до изменения).
Здесь вы найдете подробное описание того, как создать вычисляемый столбец в таблице.
Теперь, если вы измените формулу в любой из ячеек, весь столбец будет обновлен. (Офисная поддержка)
Если это не так, убедитесь, что "заполнить формулы в таблицах для создания вычисляемых столбцов" отмечен на вкладке "Файл" - "Параметры" - "Проверка" - "Автоформат при вводе".