Excel에서 사용자 지정 함수 만들기

Excel에는 다양한 기본 제공 워크시트 함수가 포함되어 있지만 사용자가 수행하는 모든 유형의 계산에 대한 함수가 없을 수 있습니다. Excel 디자이너는 모든 사용자의 계산 요구 사항을 예측할 수 없었습니다. 대신 Excel에서는 이 문서에 설명된 사용자 지정 함수를 만들 수 있는 기능을 제공합니다.

간단한 사용자 지정 함수 만들기

매크로와 같은 사용자 지정 함수는 VBA(Visual Basic for Applications) 프로그래밍 언어를 사용합니다. 두 가지 중요한 면에서 매크로와 다릅니다. 먼저 Sub 프로시저 대신 Function 프로시저를 사용합니다. 즉, Sub 문 대신 Function 문으로 시작하고 End Sub 대신 End Function으로 끝납니다. 둘째, 조치를 취하는 대신 계산을 수행합니다. 범위를 선택하고 서식을 지정하는 문과 같은 특정 종류의 문은 사용자 지정 함수에서 제외됩니다. 이 문서에서는 사용자 지정 함수를 만들고 사용하는 방법을 알아봅니다. 함수와 매크로를 만들려면 Excel과 별도의 새 창에서 열리는 VBE(Visual Basic Editor)를 사용합니다.

주문 상품이 100개 이상인 경우 회사에서 제품 판매에 대해 10%의 수량 할인을 제공한다고 가정해 보겠습니다. 다음 단락에서는 이 할인을 계산하는 함수를 보여 드리겠습니다.

아래 예제에서는 각 항목, 수량, 가격, 할인(있는 경우) 및 그에 따른 추가 가격을 나열하는 주문 양식을 보여 줍니다.

이 통합 문서에서 사용자 지정 DISCOUNT 함수를 만들려면 다음 단계를 따릅니다.

1. Alt+F11을 눌러 Visual Basic Editor를 연 다음(Mac에서는 Fn+Alt+F11 누름) 모듈 삽입>을 클릭합니다. Visual Basic Editor의 오른쪽에 새 모듈 창이 나타납니다.

2. 다음 코드를 복사하여 새 모듈에 붙여넣습니다.

   Function DISCOUNT(quantity, price)
    If quantity >=100 Then
    DISCOUNT = quantity * price * 0.1
    Else
    DISCOUNT = 0
    End If
   
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   
   

사용자 지정 함수 사용

이제 새 DISCOUNT 함수를 사용할 준비가 되었습니다. Visual Basic Editor를 닫고 G7 셀을 선택하고 다음을 입력합니다.

=DISCOUNT(D7,E7)

Excel에서는 200대에 대한 10% 할인을 단위당 $47.50로 계산하고 $950.00를 반환합니다.

VBA 코드의 첫 번째 줄인 Function DISCOUNT(quantity, price)에서 DISCOUNT 함수에 quantity 와 price의 두 가지 인수가 필요함을 표시했습니다. 워크시트 셀에서 함수를 호출할 때는 이러한 두 인수를 포함해야 합니다. 수식 =DISCOUNT(D7,E7)에서 D7은 수량 인수이고 E7은 가격 인수입니다. 이제 DISCOUNT 수식을 G8:G13에 복사하여 아래와 같은 결과를 얻을 수 있습니다.

Excel이 이 함수 절차를 해석하는 방법을 살펴보겠습니다. Enter 키를 누르면 Excel은 현재 통합 문서에서 DISCOUNT 이름을 검색하고 이 이름이 VBA 모듈의 사용자 지정 함수임을 찾습니다. 괄호로 묶인 인수 이름인 quantity 및 price는 할인 계산의 기반이 되는 값의 자리 표시자입니다.

다음 코드 블록의 If 문은 수량 인수를 검사하고 판매된 항목 수가 100보다 큰지 같은지 확인합니다.

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

판매된 항목 수가 100보다 크거나 같으면 VBA는 수량 값에가격 값을 곱한 다음 결과에 0.1을 곱하는 다음 다음 문을 실행합니다.

Discount = quantity * price * 0.1

결과는 변수 할인으로 저장됩니다. 변수에 값을 저장하는 VBA 문을 지정 문이라고 합니다. 등호의 오른쪽에 있는 식을 계산하고 그 결과를 왼쪽의 변수 이름에 할당하기 때문입니다. Discount 변수는 함수 프로시저와 이름이 같으므로 변수에 저장된 값이 DISCOUNT 함수를 호출한 워크시트 수식으로 반환됩니다.

수량이 100보다 작으면 VBA는 다음 문을 실행합니다.

Discount = 0

마지막으로 다음 명령문은 Discount 변수에 할당된 값을 소수점 아래 두 자리로 반올림합니다.

Discount = Application.Round(Discount, 2)

VBA에는 ROUND 함수가 없지만 Excel에는 함수가 있습니다. 따라서 이 문에서 ROUND를 사용하려면 VBA에 응용 프로그램 개체(Excel)에서 Round 메서드(함수)를 찾도록 지시합니다. 이렇게 하려면 Round라는 단어 앞에 Application 단어를 추가해야 합니다. VBA 모듈에서 Excel 함수에 액세스해야 할 때마다 이 구문을 사용합니다.

사용자 지정 함수 규칙 이해

사용자 지정 함수는 함수 문으로 시작하고 함수 종료 문으로 끝나야 합니다. 함수 문은 함수 이름 외에 보통 하나 이상의 인수를 지정합니다. 그러나 인수 없이 함수를 만들 수는 있습니다. Excel에는 인수를 사용하지 않는 몇 가지 기본 제공 함수(예: RAND 및 NOW)가 포함되어 있습니다.

함수 프로시저는 함수 문 다음에 함수에 전달된 인수를 사용하여 결정을 내리고 계산을 수행하는 하나 이상의 VBA 문을 포함합니다. 마지막으로 함수 프로시저 어딘가에 함수와 이름이 같은 변수에 값을 할당하는 문이 포함되어야 합니다. 이 값은 함수를 호출하는 수식에 반환됩니다.

사용자 지정 함수에서 VBA 키워드 사용

사용자 지정 함수에서 사용할 수 있는 VBA 키워드 수는 매크로에서 사용할 수 있는 수보다 적습니다. 사용자 지정 함수는 워크시트의 수식이나 다른 VBA 매크로나 함수에서 사용되는 식에 값을 반환하는 것 외에는 어떤 것도 수행할 수 없습니다. 예를 들어 사용자 지정 함수는 창 크기를 조정하거나, 셀에서 수식을 편집하거나, 셀 텍스트의 글꼴, 색 또는 패턴 옵션을 변경할 수 없습니다. 함수 프로시저에 이러한 종류의 "작업" 코드를 포함하면 함수에서 #VALUE!를 반환합니다. 오류가 발생합니다.

계산 수행 외에 함수 프로시저에서 수행할 수 있는 한 가지 작업은 대화 상자를 표시하는 것입니다. 사용자 지정 함수에서 InputBox 문을 사용하여 함수를 실행하는 사용자로부터 입력을 받을 수 있습니다. 사용자에게 정보를 전달하는 수단으로 MsgBox 문을 사용할 수 있습니다. 사용자 지정 대화 상자나 사용자 양식을 사용할 수도 있지만 이는 이 소개의 scope 벗어나는 주제입니다.

매크로 및 사용자 지정 함수 문서화

간단한 매크로와 사용자 지정 함수도 읽기 어려울 수 있습니다. 설명 형식으로 설명 텍스트를 입력하여 이해하기 쉽게 만들 수 있습니다. 설명 텍스트 앞에 아포스트로피를 추가하여 메모를 추가합니다. 예를 들어 다음 예제에서는 메모가 포함된 DISCOUNT 함수를 보여 줍니다. 이와 같은 주석을 추가하면 시간이 지남에 따라 사용자 또는 다른 사용자가 VBA 코드를 더 쉽게 관리할 수 있습니다. 나중에 코드를 변경해야 하는 경우 원래 수행한 작업을 더 쉽게 이해할 수 있습니다.

아포스트로피는 Excel에서 같은 줄의 오른쪽 항목은 모두 무시하도록 지시하므로 줄 자체에 메모를 작성하거나 VBA 코드가 포함된 줄의 오른쪽에 메모를 작성할 수 있습니다. 비교적 긴 코드 블록을 전반적인 용도를 설명하는 주석으로 시작한 다음 인라인 주석을 사용하여 개별 문을 문서화할 수 있습니다.

매크로와 사용자 지정 함수를 문서화하는 또 다른 방법은 설명이 포함된 이름을 지정하는 것입니다. 예를 들어 매크로 이름을 Labels로 지정하는 대신 MonthLabels 로 이름을 지정하여 매크로가 수행하는 목적을 보다 구체적으로 설명할 수 있습니다. 매크로 및 사용자 지정 함수에 설명이 포함된 이름을 사용하는 것은 많은 프로시저를 작성한 경우, 특히 목적이 유사하지만 동일하지는 않은 프로시저를 작성하는 경우에 특히 유용합니다.

매크로와 사용자 지정 함수를 문서화하는 방법은 개인 취향의 문제입니다. 중요한 것은 문서화 방법을 채택하고 일관되게 사용하는 것입니다.

어디서나 사용자 지정 함수 사용 가능

사용자 지정 함수를 사용하려면 함수를 만든 모듈이 포함된 통합 문서가 열려 있어야 합니다. 해당 통합 문서가 열려 있지 않으면 #NAME가 표시되나요? 오류가 발생할 수 있습니다. 다른 통합 문서에서 함수를 참조하는 경우 함수 이름 앞에 함수가 있는 통합 문서의 이름을 붙여야 합니다. 예를 들어 Personal.xlsb라는 통합 문서에서 DISCOUNT라는 함수를 만들고 다른 통합 문서에서 이 함수를 호출하는 경우 단순히 =discount()가 아니라 =personal.xlsb!discount()를 입력해야 합니다.

함수 삽입 대화 상자에서 사용자 지정 함수를 선택하여 일부 키 입력 및 발생할 수 있는 입력 오류를 방지할 수 있습니다. 사용자 지정 함수가 사용자 정의 범주에 표시됩니다.

사용자 지정 함수를 항상 사용할 수 있도록 하는 더 쉬운 방법은 해당 함수를 별도의 통합 문서에 저장한 다음 해당 통합 문서를 추가 기능으로 저장하는 것입니다. 그런 다음 Excel을 실행할 때마다 추가 기능을 사용할 수 있도록 만들 수 있습니다. 방법은 다음과 같습니다.

이러한 단계를 수행하면 Excel을 실행할 때마다 사용자 지정 함수를 사용할 수 있습니다. 함수 라이브러리에 추가하려면 Visual Basic Editor로 돌아갑니다. Visual Basic Editor Project Explorer에서 VBAProject 제목 아래를 살펴보면 추가 기능 파일의 이름을 따서 명명된 모듈을 볼 수 있습니다. 추가 기능의 확장명은 .xlam입니다.

Project Explorer에서 해당 모듈을 두 번 클릭하면 Visual Basic Editor에 함수 코드가 표시됩니다. 새 함수를 추가하려면 코드 창에서 마지막 함수를 종료하는 End Function 문 뒤에 삽입 지점을 놓고 입력을 시작합니다. 이 방법으로 필요한 만큼 함수를 만들 수 있으며 함수 삽입 대화 상자의 사용자 정의 범주에서 항상 사용할 수 있습니다.

저자 소개

이 콘텐츠는 원래 Mark Dodge와 Craig Stinson이 저서 Microsoft Office Excel 2007 Inside Out의 일부로 작성했습니다. 이후 최신 버전의 Excel에도 적용되도록 업데이트되었습니다.

추가 지원

언제든지 Excel 기술 커뮤니티의 전문가에게 문의하거나 커뮤니티에서 지원을 받을 수 있습니다.