☰

Ссылаться на формулу ячейки, а не на значение в Excel?

tag-icon • tag-icon microsoft-excel
Ссылаться на формулу ячейки, а не на значение в Excel?

Допустим, в ячейке A1 у меня есть "=СТРОКА()". Ячейка показывает "1", так как это первая строка в листе.

Теперь в A2 я ставлю "=A1". К сожалению, это ссылается на ЗНАЧЕНИЕ A1, а не на формулу. Если бы это ссылалось на формулу, то A2 вместо этого выдало бы значение '2' (что мне и нужно).

Если затем я изменю формулу в ячейке A1, то значение в ячейке A2 также изменится, поскольку оно основано на формуле в ячейке A1.

Как мне это сделать?

решение1

Нет возможности сделать это нативно с функциями листа 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))

решение2

Этого можно добиться с помощью таблиц (в Excel 2007: выберите диапазон, затем: Вставить - Таблица;первая строка должна содержать заголовки).
Каждый раз, когда вы изменяете формулу, она будет обновлена ​​во всем столбце (или вам будет предложено обновить ее, если формула в столбце не была той же самой до изменения).

Обновлять

Здесьвы найдете подробное описание того, как создать вычисляемый столбец в таблице.
Теперь, если вы измените формулу в любой из ячеек, весь столбец будет обновлен.(Офисная поддержка)
Если это не ваш случай, убедитесь, что в Файл - Параметры - Правописание - вкладка «Автоформат при вводе» отмечен флажок «Заполнять формулы в таблицах для создания вычисляемых столбцов».

Связанный контент