사용자 지정 함수가 Excel에서 예상 값을 계산할 수 없음

  • 적용 대상: Microsoft Excel

증상

워크시트를 계산할 때 일부 셀이 잘못된 값을 계산한 것처럼 보일 수 있습니다.

원인

이 문제는 다음과 같은 경우 발생합니다.

  • 워크시트의 셀에는 사용자 지정 함수가 포함되어 있습니다.

  • 사용자 지정 함수는 워크시트의 셀 범위를 참조하는 하나 이상의 인수를 포함합니다.

  • 사용자 지정 함수의 결과는 직접 참조하는 것보다 많은 셀에 따라 달라집니다.

이 동작은 Microsoft Excel의 디자인입니다. Excel에서는 사용자 지정 함수가 포함된 셀을 계산할 때 사용자 지정 함수에 인수로 전달되는 모든 셀 범위를 다시 계산합니다. 함수의 결과가 함수에 의해 명시적으로 참조되지 않은 셀에 따라 달라지면 해당 셀은 다시 계산되지 않을 수 있습니다.

해결 방법

이 문제를 해결하려면 다음 방법 중 일부를 사용합니다.

방법 1: 모든 관련 셀 범위가 전달되도록 함수 수정

함수 결과를 계산하는 데 필요한 모든 셀을 인수로 허용하도록 함수를 수정합니다.

방법 2: 사용자 지정 함수를 휘발성으로 만들기

Microsoft는 예시 목적으로만 프로그래밍 예제를 제공하며, 명시적이든 묵시적이든 어떠한 보증도 하지 않습니다. 이는 상품성이나 특정 목적에 대한 적합성의 묵시적인 보증을 포함하며 이에 제한되지 않습니다. 이 문서에서는 예제에 사용되고 있는 프로그래밍 언어와 프로시저를 만들고 디버깅하는 데 사용되는 도구를 사용자가 잘 알고 있는 것으로 가정합니다. Microsoft 지원 엔지니어는 특정 프로시저의 기능을 설명하는 데 도움을 줄 수 있지만 특정 요구 사항을 충족하기 위해 추가된 기능 또는 생성 프로시저를 제공하도록 이러한 예제를 수정하지는 않습니다. 사용자 지정 함수를 일시적으로 만드는 경우 이 문제가 발생하지 않습니다. 사용자 지정 함수를 휘발성으로 만들려면 함수에 다음 코드 줄을 추가합니다.

Application.Volatile

비고

사용자 지정 함수를 변동성으로 설정하면, 값을 변경하거나 열려 있는 통합 문서를 다시 계산할 때마다 함수가 다시 계산됩니다. 워크시트 모델의 성능에 영향을 미칠 수 있습니다.

방법 3: 열려 있는 모든 통합 문서를 다시 계산하도록 Excel 강제 적용

Ctrl+Alt+F9를 눌러 열려 있는 모든 통합 문서의 값을 다시 계산합니다. Microsoft Office Excel 2007에서 Ctrl+Alt+Shift+F9를 눌러 열려 있는 모든 통합 문서의 값을 다시 계산합니다.

추가 정보

문제의 예

이 문제를 설명하려면 다음 단계를 수행합니다.

  1. 열려 있는 통합 문서를 닫고 저장한 다음 새 통합 문서를 엽니다.

  2. Visual Basic Editor를 시작합니다(Alt+F11 키를 누릅니다).

  3. 삽입 메뉴에서 모듈을 클릭합니다.

  4. 모듈 시트에 다음 코드를 입력합니다.

    ' This function counts the number of blank cells by starting from the cell 
' referred to by the rngStartCell argument and moving up the column.

Function FindTextUp(rngStartCell As Range) As Single
    Dim iIndex As Integer

For iIndex = 0 To 100
        If rngStartCell.Value <> "" Then
            FindTextUp = iIndex
            Exit Function
        Else
            Set rngStartCell = rngStartCell.Offset(-1, 0)
        End If
    Next iIndex
End Function

  5. Alt+F11을 눌러 Excel로 돌아갑니다.

  6. 셀 A2에서 테스트를 입력한 다음 Enter 키를 누릅니다.

  7. 셀 A10에 다음 수식을 입력한 다음 Enter 키를 누릅니다.

    =FindTextUp(A9)

    수식은 7 값을 반환합니다.

  8. 셀 A5에 다른 테스트를 입력한 다음 Enter 키를 누릅니다.

기대 값이 4임에도 불구하고 수식은 여전히 7의 값을 반환합니다. 이 예제에서 FindTextUp 함수는 A9 셀을 명시적으로 참조합니다. 그러나 함수는 워크시트에 입력된 데이터에 따라 A1:A8 셀에 따라 달라질 수 있습니다.

이 문서의 "해결 방법" 섹션에서 메서드 1을 구현하는 경우 함수는 예상 결과를 계산합니다. 다음 코드 줄에서는 예상 결과가 계산되도록 이 예제의 함수를 수정하는 방법을 보여 줍니다.

Function FindTextUp(rngStartCell As Range, rngOtherCells As Range) As Single

이제 7단계의 함수 호출을 다음 함수 호출로 바꾸면 함수는 항상 예상 결과를 반환합니다.

=FindTextUp(A9,A1:A8)
  • Last updated on