☰

문제

tag-icon • tag-icon microsoft-excel worksheet-function
문제

나는 결국 여러 다른 셀(매개변수)의 입력을 사용하여 단일 셀(출력)을 계산하는 매우 복잡한 Excel 스프레드시트(여러 워크시트에서 다른 수식을 사용하여 셀에 액세스하는 수식)를 가지고 있습니다. 출력 셀에서 시작하여 매개변수 셀 측면에서 직접 수식을 표현할 때까지 계산을 반복적으로 펼칠 수 있는 자동화된 방법(Excel 매크로 또는 독립 실행형 도구)이 있습니까?

설명

댓글에는 수식 평가 도구에 대한 제안이 있었습니다. Mac용 Excel 2008에서 활성화하는 방법을 찾을 수 없었지만설명사용자가 셀 평가를 단계별로 수행할 수 있는 것처럼 들립니다. 그것은 나에게 필요한 것이 아닙니다. 나에게 필요한 것은 수식이 포함된 다른 셀을 참조할 수 있는 특정 셀의 수식을 최종 셀(값은 포함하지만 수식은 포함하지 않는 셀)로 표현된 동등한 수식으로 변환하는 방법입니다.

다음은 간단한 예입니다. 허락하다

  • A1포함하다= B1 + C1
  • B1포함하다= B2 * B2
  • C1포함하다= C2 * C2
  • B2포함하다1
  • C2포함하다2

수식 평가를 통해 의 계산을 단계별로 진행하여 의 A1최종 값을 얻을 수 있습니다 5. 나에게 필요한 것은 실제로 평가하지 않고 A1공식을 전개할 수 있는 도구입니다 .= B2 * B2 + C2 * C2

답변1

문제

이는 함수의 목적이 아니기 때문에 수식 계산으로는 이 작업을 수행할 수 없습니다. 그렇기 때문에 이렇게 불린다.평가하다, 수식을 평가하기 위한 것입니다. 당신이 원하는 것은 일종의 포장 풀기입니다. 이것은 약간 특별한 요구 사항이므로 Excel에서 도구로 구현되지 않지만 일부 Visual Basic 함수/매크로를 만드는 경우 해결 방법이 있습니다.

다음에서 볼 수 있는 것처럼 VBA 코드 모듈(매크로)을 만듭니다.이 튜토리얼.

  1. Alt+를 누르세요F11
  2. Module을 클릭하세요 Insert.
  3. 코드를 붙여넣으세요.
Function CellFormula(Target As Range) As String
   CellFormula = Target.Formula
End Function

그런 다음 셀에 다음을 입력합니다.=CellFormula(A1)

이것은 셀의 공식을 알려줍니다. 이 코드의 유일한 문제점은 한 수준에서만 작동한다는 것입니다. 포함된 셀 수식도 압축을 풀려면 재귀를 사용하는 더 복잡한 코드가 필요합니다.

해결책

긴 여정이었지만 이 기능을 구현하는 VBA 매크로를 만들었습니다. 이 코드가 모든 수식에 대해 작동한다고 명시하지는 않지만 대부분/일부 수식에서는 작동합니다. 또한 이 코드가 원래 입력한 코드와 동일한 수식을 생성하거나 원본과 동일한 결과를 제공한다고 언급하지 않습니다.

소스 코드

Option Explicit

Function isChar(char As String) As Boolean
    Select Case char
        Case "A" To "Z"
            isChar = True
        Case Else
            isChar = False
    End Select
End Function

Function isNumber(char As String, isZero As Boolean) As Boolean
    Select Case char
        Case "0"
            If isZero = True Then
                isNumber = True
            Else
                isNumber = False
            End If
        Case "1" To "9"
            isNumber = True
        Case Else
            isNumber = False
    End Select
End Function

Function CellFormulaExpand(formula As String) As String
    Dim result As String
    Dim previousResult As String
    Dim cell As Range
    Dim stringArray() As String
    Dim arraySize As Integer
    Dim n As Integer
    Dim trimmer As String
    
    Dim c As Integer 'character number
    Dim chr As String 'current character
    Dim tempcell As String 'suspected cell's temporaly result
    Dim state As Integer 'state machine's state:
    Dim stringSize As Integer

    result = formula
    previousResult = result
    state = 0
    stringSize = 0

    For c = 0 To Len(formula) Step 1
        chr = Mid(formula, c + 1, 1)
        Select Case state
            Case 0
                If isChar(chr) Then
                    state = 1
                    tempcell = tempcell & chr
                ElseIf chr = "$" Then
                    state = 5
                    tempcell = tempcell & chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case 1
                If isNumber(chr, False) Then
                    state = 4
                    tempcell = tempcell & chr
                ElseIf isChar(chr) Then
                    state = 2
                    tempcell = tempcell & chr
                ElseIf chr = "$" Then
                    state = 6
                    tempcell = tempcell & chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case 2
                If isNumber(chr, False) Then
                    state = 4
                    tempcell = tempcell + chr
                ElseIf isChar(chr) Then
                    state = 3
                    tempcell = tempcell + chr
                ElseIf chr = "$" Then
                    state = 6
                    tempcell = tempcell + chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case 3
                If isNumber(chr, False) Then
                    state = 4
                    tempcell = tempcell + chr
                ElseIf chr = "$" Then
                    state = 6
                    tempcell = tempcell + chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case 4
                If isNumber(chr, True) Then
                    state = 4
                    tempcell = tempcell + chr
                Else
                    state = 0
                    stringSize = stringSize + 1
                    ReDim Preserve stringArray(stringSize - 1)
                    stringArray(stringSize - 1) = tempcell
                    tempcell = ""
                End If
            Case 5
                If isChar(chr) Then
                    state = 1
                    tempcell = tempcell + chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case 6
                If isNumber(chr, False) Then
                    state = 4
                    tempcell = tempcell + chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case Else
                state = 0
                tempcell = ""
        End Select
    Next c
    If stringSize = 0 Then
        CellFormulaExpand = result
    Else
        arraySize = UBound(stringArray)
        For n = 0 To arraySize Step 1
            Set cell = Range(stringArray(n))
            If Mid(cell.formula, 1, 1) = "=" Then
                trimmer = Mid(cell.formula, 2, Len(cell.formula) - 1)
                If trimmer <> "" Then
                    result = Replace(result, stringArray(n), trimmer)
                End If
            End If
        Next
        If previousResult <> result Then
            result = CellFormulaExpand(result)
        End If
    End If
    CellFormulaExpand = result
End Function

Function CellFormula(rng As Range) As String
    CellFormula = CellFormulaExpand(rng.formula)
End Function

작동하게 하려면 답변 시작 부분에서 설명한 대로 매크로를 만들고 코드를 복사하여 붙여넣으면 됩니다. 그런 다음 =CellFormula(A1)모든 A1종류의 1x1 셀을 사용할 수 있습니다 .

작동하는 사례

실제로 볼 수 있도록 몇 가지 예를 만들었습니다. 이 경우 문자열을 사용하는 방법을 보여줍니다. 완벽하게 작동하는 것을 볼 수 있습니다. 유일한 작은 버그는 알고리즘이 세미콜론을 쉼표로 변경한다는 것입니다. 이 예제에서 했던 것처럼 교체한 후에는 올바른 출력을 얻을 수 있습니다. 문자열 작업

여기에서 숫자와 어떻게 작동하는지 확인할 수 있습니다. 이제 우리는 알고리즘이 수학적 연산 순서를 신경 쓰지 않는다는 첫 번째 문제에 직면합니다. 이것이 바로 10이어야 하는 빨간색 숫자가 6인 이유입니다. 민감한 연산(덧셈 및 뺄셈과 같은)을 괄호 안에 넣으면 다시 입력된 수식은 하단에 10이라고 표시된 녹색 숫자에서 볼 수 있는 것과 동일한 출력을 제공합니다. 숫자 작업

작동하지 않는 경우

이 알고리즘은 완벽하지 않습니다. 가장 일반적인 용도만 구현하려고 했기 때문에 범위와 같은 다른 경우를 처리하는 기능을 더 추가하면 개선될 수 있습니다.
이 예에서 볼 수 있듯이 SUM()범위를 매개변수로 사용했습니다. 알고리즘은 셀 내용을 위에서 아래로 해독하므로 SUM()나중에 매개변수를 다른 것으로 바꾸는 것부터 시작됩니다. 그러므로 :주변의 모든 것이 교체되는 동안 그 자리에 머물게 됩니다. 따라서 새로운 세포가 그 근처에서 교체되고, 누가 그것의 의미를 바꿀 것입니다. 따라서 출력이 잘못됩니다. 따라서 이 경우에는 이 매크로를 사용하여 원래 공식을 연구할 수 있습니다. 범위 작업

답변2

귀하의 질문에서 "추적"이라는 단어를 사용했다면 Excel의 수식 감사 섹션에 있는 Trace Precendents및 기능 에 익숙하다고 가정하겠습니다 .Trace Dependents

다음은 dummies.com의 각 수식 감사 도구에 대한 간략한 설명입니다.

Excel 2010의 수식 감사 도구 이해

답변3

오늘날 우리는 FORMULATEXT()상당히 분명한 방식으로 사용하지만 그 당시에는 놓쳤고 오늘날 이전 버전(2007년의 시댁 식구들처럼)을 사용하는 사람들의 경우 GET()명명된 형식 내에서 다른 Excel 4 매크로 명령인 을 사용하는 것입니다. 범위.

그것이 할 수 있는 많은 일 중 하나는 FORMULATEXT()지금과 똑같이 수식을 텍스트로 읽는 것이었습니다. 그러면 문자열을 결합하기만 하면 됩니다. IF()얻을 것이 있는지, 집에 없는지 테스트하기 위해 약간의 시간을 쏟았습니다.

관련 정보