Excel VBA 자동화 중 @기호 수식 오류 해결법 (Formula2 활용)

by

엑셀에서 VBA로 SUMPRODUCT와 같은 수식을 자동 입력할 때, 의도치 않게 @ 기호가 삽입되면서 계산 결과가 0이 되는 오류가 발생할 수 있습니다. 이 문제는 특히 Excel 365 또는 2019 이후 버전에서 자주 나타납니다.

문제 상황

아래 수식은 VBA로 Sheet의 특정 셀에 자동 입력된 예시입니다:

=SUMPRODUCT((--(TEXT($A$2:$A$150,"hh:mm")<=TEXT(K4,"hh:mm"))) * 
(IFERROR(SUBSTITUTE(@$E$2:$E$150,"%","")/1,0)))*100

여기서 @$E$2:$E$150 부분에 포함된 @ 기호는 Excel이 암시적 교차 참조(Implicit Intersection)를 시도하며 배열 전체가 아닌 단일 셀로 잘못 인식하게 만듭니다. 그 결과 전체 수식이 0 또는 빈값을 반환하게 됩니다.

해결 방법: Formula2 사용

Excel 365 이상에서는 VBA에서 수식을 넣을 때 .Formula2 속성을 사용하면 @ 기호가 자동 제거되고, 배열 수식도 정상 동작합니다.

아래는 수정된 VBA 코드입니다:

ws.Range("L" & targetRow).Formula2 = _
    "=SUMPRODUCT((--(TEXT($A$2:$A$150,""hh:mm"")<=TEXT(K" & targetRow & ",""hh:mm""))) * " & _
    "(IFERROR(SUBSTITUTE($E$2:$E$150,""%"","""")/1,0)))*100"

추가 팁

  • Formula2는 LET, LAMBDA 같은 동적 배열 함수도 지원
  • Excel 2016 이하 버전에서는 .FormulaArray를 사용해도 일부 해결 가능
  • VBA로 수식 자동화를 구성할 땐 반드시 결과 수식의 형태를 확인하세요 (디버깅 필수)

마무리

VBA 자동화로 수익률, 시간대별 통계 등 다양한 수식 자동화를 구현할 때, @ 기호 문제는 의외로 자주 마주하게 됩니다. 이럴 땐 Formula2를 활용하면 깔끔하게 해결할 수 있으므로, Excel 365 이상 사용자라면 적극 활용해 보시길 권장합니다.

본 포스팅은 AI기반으로 최신 엑셀 환경에서 발생하는 오류를 기준으로 ChatGPT에서 작성되었습니다.

Leave a Comment