☰

Формула подстановки excel

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
Формула подстановки excel

можно ли легко выполнить замену формулы в Excel?

У меня есть рабочий лист, похожий на приведенный ниже пример (хотя и более сложный, поэтому нужна какая-то функция в Excel, если таковая существует)

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

F6+I6+L6+O6 = F9 + L9
F4+F5+I4+I5+L4+L5+O4+O5 = F6+I6+L6+O6

that is 
F9 + L9 becomes F4+F5+I4+I5+L4+L5+O4+O5

введите описание изображения здесь

Надеюсь, вы понимаете, что я имею в виду.

решение1

Я написал быстрый код, который должен сделать то, что вам нужно.

По сути, он проверяет, содержит ли ячейка формулу, и если содержит, то заменяет все случаи, когда эта ячейка ссылается на эту формулу.

После того, как он прошел по листу, если он нашел какие-либо замены, он снова зацикливается (на самом деле не уверен, нужно ли это, но так было проще сделать). Я также не имею представления, насколько быстро это будет работать на сложных электронных таблицах.

Обратите внимание, что он рассматривает $A$1,$A1,A$1 и A1 как одно и то же и не имеет возможности определить, должны ли быть в ссылке какие-либо замороженные ячейки.

Sub replace_formulas()
Dim cell_count As Long, flag As Boolean

Do

flag = False

For Each c In ActiveSheet.UsedRange
    If c.HasFormula Then

        'count number of replacements
        cell_count = Application.WorksheetFunction.CountIf(Cells, c.Address) + _
            Application.WorksheetFunction.CountIf(Cells, Replace(c.Address, "$", "")) + _
            Application.WorksheetFunction.CountIf(Cells, Replace(c.Address, "$", "", 1, 1)) + _
            Application.WorksheetFunction.CountIf(Cells, "$" & Replace(c.Address, "$", ""))

        'If there is at least one replacement loop through all the cells after this one
        If cell_count > 0 Then flag = True

        'Replace cell references with and without $ ($A$1,$A1,A$1,A1)
        Cells.Replace What:=c.Address, Replacement:="c.formula", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Cells.Replace What:=Replace(c.Address, "$", ""), Replacement:=Right(c.Formula, Len(c.Formula) - 1), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Cells.Replace What:=Replace(c.Address, "$", "", 1, 1), Replacement:=Right(c.Formula, Len(c.Formula) - 1), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Cells.Replace What:="$" & Replace(c.Address, "$", ""), Replacement:=Right(c.Formula, Len(c.Formula) - 1), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    End If
Next

Loop While flag = True

End Sub

решение2

Возможно, вам придется сделать это вручную, но есть функция, которая вам поможет: отслеживание прецедентов (на вкладке «Формулы» в Excel 2013).

Это добавит стрелки, показывающие, откуда берется выбранная вами формула. Так, если вы выбрали общий объем продаж в столбце J в вашем примере и нажали «Отследить прецеденты», то к нему будут нарисованы стрелки от F9 и L9.

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