엑셀의 유용함, 사용자 지정 함수

1. 엑셀 내장 함수와 사용자 지정 함수

 

가. 엑셀 내장 함수

엑셀에는 많은 내장 함수가 존재합니다.

 

fx라고 쓰여 있는 함수 삽입 아이콘을 누르면

 

최근에 사용한 함수가 선택되어 있고, 그 아래 모두, 재무, 날짜/시간 등 범주가 표시되고, 통계를 누르면

 

두 번째 average, 네 번째와 다섯 번째 averageif, averageifs 함수 등이 보입니다. 아래로 계속 내려가면 자주 사용하는 max, min, sum 등 함수도 보입니다.

 

위와 같이 자주 사용하는 함수를 엑셀을 미리 만들어 놓아서 사용자들이 사용하기 쉬운 환경을 만들어놓고, 계산을 쉽게 할 수 있도록 도와주는 것이 엑셀의 뛰어난 기능입니다.

 

 

나.사용자 지정 함수

사용자 지정 함수는 내장 함수로는 처리하기 어렵거나 복잡해지는 수식을 VBA를 이용하여 편리하게 사용할 수 있도록 사용자가 만든 함수입니다. 종전에는 사용자 정의 함수라고 해서 ,User Defined Funcitons라고  불렀던 것 같은데,

 

마이크로소프트의 엑셀 사용 설명서 사이트를 보니 사용자 지정 함수로 되어 있고, 영어도 custom functions라고 되어 있습니다. 

 

항상 느끼는 것이지만 한글 번역이 너무 엉성합니다.

 

원문은 아래와 같습니다.

 

2. 점수에 따른 등급 구하는 방법

가.  내장 함수 이용하기

 

예를 들어 60미만은 F, 60 이상 70 미만은 D, 70 이상 80 미만은 C, 80 이상 90 미만은 B, 90 이상은 A라고 점수에 따른 등급을 부여한다고 하면 if 함수를 사용할 수 있습니다.

 

완성된 수식은 아래와 같습니다.

=IF(A1>=90,"A",IF(A1>=80,"B",IF(A1>=70,"C",IF(A1>=60,"D","E"))))

 

아닐 경우에 if함수를 넣어서 if함수를 중첩해서 점수에 따른 등급을 구하는 것입니다. 결과는 맞게 구해지는데, 수식이 엄청 복잡합니다.

 

그리고 최근에 긴 수식 입력시 개선된 점이 있는데,

첫째는 Alt + Enter키를 눌러서 2줄로 만들어도 수식 실행에 문제가 없고,

 

스페이스바로 밀어서 수식 사이에 공백을 삽입해도 실행에 전혀 문제없습니다. 물론 엑셀 버전에 따라 안될 수도 있습니다.

 

 

나. 사용자 지정 함수 이용하기

아래는 grade라는 사용자 지정 함수를 만들어 점수를 입력하면 등급을 구하는 화면입니다.

 

if함수를 이용할 때는 무슨 뜻인지 이해하려면 한참을 살펴봐야 하는데, 내부에서 돌아가는 것은 신경 쓸 필요 없이 grade와 점수가 있는 셀만 입력하면 해당하는 등급을 구해줍니다. 만약 A2셀을 93으로 바꾸면 결괏값 A가 구해집니다.

 

이와 같이 자주 사용하는 기능을 함수로 만들어 놓으면 쉽게 원하는 값을 얻을 수 있는 것이 사용자 지정 함수의 장점입니다.

 

 

3. 사용자 지정 함수 작성법

 

가. 기본적인 내용

 

지금까지 엑셀 VBA에 대한 설명은 반복적인 작업을 단순화하기 위해 sub로 시작해서 end sub로 끝나는 서브 프로시저를 주로 설명했는데,

 

사용자 지정 함수는 function으로 시작해서 end function으로 끝나는 점이 다릅니다. 함수이므로 function이 사용되는 것입니다.,

 

사용자 지정 함수는 일반 매크로 작성의 경우와 마찬가지로 개별 엑셀 파일(확장자 xlsm)의 모듈에 추가해서 작성할 수도 있고,

 

별도의 Excel 추가 기능(xlam)  파일 형식으로 저장할 수도 있습니다.

 

나. 상세 내용

함수를 작성하는 방법은 대체로 sub 프로시저를 작성하는 방법과 비슷한데,

아래와 같은 특수한 점이 있습니다.

 

사용자 지정 함수를 작성하는 포맷을 자세히 설명하면 아래와 같습니다.

Function 함수이름(인수명1 as type , 인수명2 as type, Optional 인수명 as type = "/")     처리 루틴     함수이름 = 결과값 End Function

 

① 인수는 필요한 만큼 넣으면 되며, 숫자인지 문자인지 형식을 명확히 하기 위해 type을 지정해 주고,

예, heart_rate as Integer, search_cell as Range, 날짜1 as Date

형식은 소문자로 입력해도 알아서 대문자 등으로 변환되므로 입력시 대, 소문자를 신경 쓰지 않아도 됩니다.

 

② 변수명을 반드시 영어로 써야 하는 것은 아니고 한글도 가능하나 숫자로 시작하면 안됩니다.

​

③ optional이란 인수가 꼭 필요하지는 않으나 있으면 그것이 적용되는 선택형 인수를 말하며,

optional 인수의 기본값은 type 뒤에 ="/"식으로 지정하며, 반드시 지정해야 하는 것은 아닙니다.

예, optional 본번자릿수 as integer, Optional 부번자릿수 as integer

optional find_char As String = "/"

Optional 시간구분 As Integer = 1

​

④ 함수 처리 후 결괏값(return값)은 return을 사용하지 않고, 함수이름 = xxxxxx으로 표현합니다.

※ 다른 언어의 경우는 return 방식을 쓰는데 엑셀 VBA의 특이한 점이므로 주의해야 합니다.

Function repl_cr(search_cell, Optional repl_char As String = " ")     ' alt_enter값을 특정캐릭터값으로 바꿔줍니다.     repl_cr = Replace(search_cell, Chr(10), repl_char) End Function

 

​repl_cr 함수는 alt+enter값, ASCII코드로 하면 10번 값을 repl_char로 대체(replace) 해주는 함수입니다.

repl_char 값을 입력하지 않아도 되며, 입력하지 않을 경우 기본값인 " "(공백 한 칸) 이 적용됩니다.

다시 말해 두 줄이 한 줄로 바뀌면서 중간에 공백 한 칸이 추가되는 것입니다.

수식을 =repl_cr(a1, "->")로 옵션 값을 입력하면 그 값으로 바뀌어서

결괏값은

안녕하세요?->홍길동입니다.

가 됩니다.

​

⑤ function안에 들어가는 실행문은 일반적인 sub 프로시저의 경우와 마찬가지로

if문, for ~ next문, while ~ wend, for earch 변수 in 범위 ~ next 등 모든 것이 가능합니다.

​

⑥ function 내 함수는 VBA에서 제공하는 함수는 그대로 쓰면 되는데,

엑셀 워크시트에서만 제공하는 함수는 Application.WorksheetFunciton.RoundDown 식으로 RoundDown 앞에 Application과 WorksheetFunction을 붙여야 하지만, Application은 생략하고 worksheetfunction.rounddown 식으로 사용해도 됩니다.

​

⑦ 프러시저 문이 2줄 이상으로 길어지는 경우는 줄 마지막에 _ 을 써서 연결합니다.

예,

If c3 > 0 Then mu = Trim(Mid(r1, IIf(c2 = 0, IIf(c1 = 0, 0, c1), c2) + 1, _

    c3 - IIf(c2 = 0, c1, c2) - 1))

수식은 복잡하지만 두 줄이상으로 길어지는 경우는 _로 연결한다는 것만 알면 됩니다.

 

다. 추가 기능 파일로 저장하기

위와 같이 작성된 xlsm 파일을

사용자정의함수.xlsm

0.02MB

 

다른 이름으로 저장하는데, 파일 형식을 추가 기능(xlam)으로 지정하면

 

기본적으로 AddIns 폴더로 지정되는데 다른 폴더를 지정해도 됩니다. 그대로 저장 버튼을 누르면

 

AddIns 폴더에 사용자정의함수.xlam 파일이 생기고,

 

개발도구 탭의 Excel 추가 기능 명령을 누르면 사용자정의함수가 목록에 표시되므로 왼쪽에 체크한 후 확인 버튼을 누르면 사용자정의함수.xlsm 파일을 열지 않더라도 어디서든 사용할 수 있습니다.

 

라. 추가 기능 파일 사용하기

사용자정의함수.xlsm 파일을 닫고, 새 통합 문서를 연 다음 A1셀에 89라고 입력한 다음 B1셀에서 =gr까지 입력하면 grade 함수가 표시됩니다.

 

탭 키를 눌러 grade 함수 입력을 마치고, A1셀을 클릭한 다음 괄호를 닫고 엔터키를 누르면 B등급이 구해집니다.

 

간단하지만 사용자 정의 함수의 매력을 느껴보시기 바랍니다.

사용자정의함수.xlam

0.02MB

 

위 파일을 다른 사람에게 전달하면 그 사람은 사용하려면 위와 같이 먼저 엑셀 추가 기능에 추가해야 합니다.

 

마. 주의 사항

사용자 지정 함수가 포함된 엑셀 파일을  다른 사람에게 보내면

='C:\Users\사용자명\AppData\Roaming\Microsoft\AddIns\사용자정의함수.xlam'!grade(A1)

식으로 함수명 앞에 폴더명이 붙는데,

이 때는 홈 탭의 찾기 및 선택 명령을 누른 후 바꾸기를 선택하고

 

 'c:\사용자\....'! 부분을 빈칸으로 대체하면 됩니다.​