[엑셀] 산식_산출(수식)을 값으로 바꾸는 방법
* 출처 : https://ms-office.tistory.com/23
수량산출서나 각종 계산서에서 산식과 값을 표시하는데 엑셀에서 입맛에 맞게 표현하기가 쉽지는 않습니다.
예전에는 이름정의를 사용하여 산식을 계산하거나 VBA를 조금이라도 아시는 분은 사용자 정의함수를 이용하기도 했습니다.
2013버전 부터는 FORMULATEXT 함수가 추가되어 이름정의를 사용하는 것 보다 더 편리하게 산식과 값을 구분하여 사용할 수 있게 되었는데 산식을 값으로 바꾸는 모든 방법을 알아보겠습니다.
☞ 엑셀에서 산식을 값으로 바꾸는 방법 비교
FORMULATEXT 함수(참고로 2013이후 버전에서 사용가능)
FORMULATEXT 함수는 수식을 문자로 변환하며 수식입력줄에 표시되는 내용을 반환 합니다.
값이 들어가야 할 셀에 수식 =100*2+3^2 (작성 예)
산식이 들어가야 할 셀에 수식 = FORMULATEXT(E6)
E6셀의 수식이 D6셀에 보이시죠.
이름정의 사용 (EVALUATE 함수)
EVALUATE 함수는 매크로 함수이기 때문에 [함수마법사]에서는 찾을 수 없습니다.
다만 이름정의를 통하여 매크로 함수인 EVALUATE 함수를 활용 할 수 있습니다.
☞ 이름정의
1. [수식] - [이름정의]
2. [이름]란에 "계산"이라고 기입 (본인이 알아보기 쉬운 이름으로...)
3. [참조대상]에 수식 = EVALUATE(Sheet1!D8) 입력 (주의 절대참조가 되지 않도록 수식 수정)
4. 확인
☞ 수식입력
1. 산식을 넣을 곳에 산식을 입력
2. 산식 우측셀 E8셀세 수식 = 계산 입력
※ FORMULATEXT 함수가 나오기 전까지 매크로 함수 EVALUATE 함수를 사용했지만 기능이나 결과는 똑같아 굳이 번거롭게 이름정의를 할 필요가 없을 듯 하네요.
3
사용자정의함수
VBA를 이용해서 사용자정의 함수를 만들어 보겠습니다. 코드는 아래와 같습니다.
Function TEVALUATE(ByVal strTemp As String) As Double
Dim strMe As String
Dim strF As String
Dim strG As String
Dim i As Integer, j As Integer
i = Len(strTemp)
For j = 1 To i
strF = Mid(strTemp, j, 1)
If IsNumeric(strF) Then
Else
Select Case strF
Case "*", "/", ".", "+", "-", "^", "(", ")", "%"
Case "×"
strF = "*"
Case "÷"
strF = "/"
Case "＋"
strF = "+"
Case "－"
strF = "-"
Case Else
strF = ""
End Select
End If
strMe = strMe & strF
Next j
If strTemp = "" Then strMe = 0
If strMe = "" Then strMe = 0
TEVALUATE = Evaluate(strMe)
End Function
위 코드는 예전에 어떤 책에서 찾은 것 같은데 시간이 오래되어 작성자가 누군지 확인이 되진 않습니다.
제가 만든건 아님을 먼저 말씀 드립니다.
위 첨부된 파일의 코드를 복사하여 VBA 에디터 [모듈] 입력창에 붙여넣기 합니다.
※ 반드시 [모듈]을 만들어서 [모듈] 코드창에 붙여넣기 해야 합니다.
사용자 정의함수 입력
위 과정이 끝나면 함수를 입력합니다.
입력방법은 함수를 입력하는 것과 동일하며,
아래 그림과 같이 함수마법사 사용자 정의함수에 TEVALUATE 함수가 추가 된 것도 확인 할 수 있습니다.
사용자정의함수(TEVALUATE)의 장점
1. 산식입력시 등호(=) 없이 입력가능
2. 산식에 문자가 포함되어도 됨
3. 산식의 위치와 무관 (이름정의 경우 시트와 셀의 위치에 영향을 받음)
※ 위 이름정의와 사용자 정의 함수는 VBA를 활용하므로 저장시에는 다른이름으로 저장하여 excel 매크로 사용 통합문서(xlsm)로 저장해야 합니다.
댓글 없음:
댓글 쓰기