☰

수식 대체 엑셀

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
수식 대체 엑셀

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에서 화살표가 그려집니다.

관련 정보