금융.경제이론

Create custom functions in Excel (Preview)

LBA 효성공인 2018. 7. 31. 17:04

Create custom functions in Excel (Preview:미리보기) :사용자 정의 함수 만들기      

Custom functions (similar to user-defined functions, or UDFs), enable developers to add any JavaScript function to Excel using an add-in. Users can then access custom functions like any other native function(기본함수) in Excel (such as =SUM()). This article explains how to create custom functions in Excel.

The following illustration shows you how an end user would insert a custom function into a cell. The function that adds 42 to a pair of numbers.

custom functions

Here’s the code for the same custom function.

JavaScript 
function ADD42(a, b) {
    return a + b + 42;
}

Custom functions are now available in Developer Preview on Windows, Mac, and Excel online. Follow these steps to try them:

  1. Install Office (build 9325 on Windows or 13.329 on Mac) and join the Office Insider program. (Note that it isn't enough just to get the latest build; the feature will be disabled on any build until you join the Insider program)
  2. Clone the Excel-Custom-Functions repo and follow the instructions in the README.md to start the add-in in Excel, make changes in the code, and debug.(제거하다)
  3. Type =CONTOSO.ADD42(1,2) into any cell, and press Enter to run the custom function.

See the Known Issues section at the end of this article, which includes current limitations of custom functions and will be updated over time.(시간이 지남에 따라)

Learn the basics

In the cloned(복제된) sample repo, you’ll see the following files:

  • customfunctions.js, which contains the custom function code (see the simple code example above for the ADD42 function).(JS or js may refer to: JavaScript, a high-level, dynamic, untyped, and interpreted(해석된) programming language JScript, Microsoft's dialect(사투리) of the ECMAScript standard used in Internet Explorer... )
  • customfunctions.json, which contains the registration JSON that tells Excel about your custom function. Registration makes your custom functions appear in the list of available functions displayed when a user types in a cell.
  • JSON(제이슨[1], JavaScript Object Notation)은 속성-값 쌍( attribute–value pairs and array data types (or any other serializable value))으로 이루어진 데이터 오브젝트를 전달하기 위해 인간이 읽을 수 있는 텍스트를 사용하는 개방형 표준 포맷이다. 비동기 브라우저/서버 통신 (AJAX)을 위해, 넓게는 XML(AJAX가 사용)을 대체하는 주요 데이터 포맷이다. 특히, 인터넷에서 자료를 주고 받을 때 그 자료를 표현하는 방법으로 알려져 있다. 자료의 종류에 큰 제한은 없으며, 특히 컴퓨터 프로그램의 변수값을 표현하는 데 적합하다.

    본래는 자바스크립트 언어로부터 파생되어 자바스크립트의 구문 형식을 따르지만 언어 독립형 데이터 포맷이다. 즉, 프로그래밍 언어나 플랫폼에 독립적이므로, 구문 분석 및 JSON 데이터 생성을 위한 코드는 C, C++, C#, 자바, 자바스크립트, 펄, 파이썬 등 수많은 프로그래밍 언어에서 쉽게 이용할 수 있다.

    JSON 포맷은 본래 더글라스 크록포드가 규정하였다. RFC 7159와 ECMA-404라는 두 개의 경쟁 표준에 의해 기술되고 있다. ECMA 표준은 문법만 정의할 정도로 최소한으로만 정의되어 있는 반면 RFC는 시맨틱, 보안적 고려 사항을 일부 제공하기도 한다.[2] JSON의 공식 인터넷 미디어 타입은 application/json이며, JSON의 파일 확장자는 .json이다.


  • customfunctions.html(Hypertext Markup Language)which provides a <Script> reference to the JS file. This file does not display UI in Excel.(사용자와 컴퓨터가 정보를 주고받기 위해 사용자와 프로그램이 상호 작용하는 프로그램의 일부분. 사용자가 키보드에서 명령을 입력하여 프로그램을 작동시키는 것을 커맨드 라인 인터페이스 (command-line interface;연결))라고 하고, 메뉴 선택에 의한 명령으로 작동시키는 것을 메뉴 방식 인터페이스 (menu-driven interface)라고 하며, 위치 지정 도구(광전 펜, 마우스, 컨트롤 볼, 조이스틱 등)를 사용하며 도형 표시 프로그램을 작동시키는 것을 그래픽 사용자 인터페이스(GUI)라고 한다.)
  • customfunctions.xml, which tells Excel the location of the HTML, JavaScript, and JSON files; and also specifies a namespace for all the custom functions that are installed with the add-in.(Hypertext;정보난을 마음대로 만들거나 연결시키고 기억된 정보를 검색할 수 게 비순차적(non-sequentially)으로 기억된 데이터텍스트)

[Xml:extensible markup language]

요약 인터넷 웹페이지를 만드는 HTML을 획기적으로 개선하여 만든 언어이다. 홈페이지 구축기능, 검색기능 등이 향상되었고, 웹 페이지의 추가와 작성이 편리해졌다.

확장성 생성 언어(擴張性生成言語)로 번역되며, 1996년 W3C(World Wide Web Consortium)에서 제안하였다. HTML보다 홈페이지 구축 기능, 검색 기능 등이 향상되었고 클라이언트 시스템의 복잡한 데이터 처리를 쉽게 한다. 또한 인터넷 사용자가 웹에 추가할 내용을 작성, 관리하기에 쉽게 되어 있다.

이밖에 HTML은 웹 페이지에서 데이터베이스처럼 구조화된 데이터를 지원할 수 없지만 XML은 사용자가 구조화된 데이터베이스를 뜻대로 조작할 수 있다. 구조적으로 XML 문서들은 SGML(standard generalized markup language) 문서 형식을 따르고 있다. XML은 SGML의 부분집합이라고도 할 수 있기 때문에 응용판 또는 축약된 형식의 SGML이라고 볼 수 있다. 1997년부터 마이크로소프트사(社)와 넷스케이프 커뮤니케이션스사(社)가 XML을 지원하는 브라우저 개발을 하고 있다.

[네이버 지식백과] XML [extensible markup language] (두산백과)



JSON file (customfunctions.json:JavaScript Object Notation )

The following code in customfunctions.json specifies the metadata for the same ADD42 function.

메타데이터

meta(메타)는 그리스어로 ‘넘어서, 위에 있는, 초월하는’ 등의 의미를 가진 접두사(prefix)인데, 이 접두사로 만들어진 대표적 단어로는 형이상학을 의미하는 metaphysics가 있다. 글자 그대로 보면 자연(물리계)을 초월하는 그 무엇인데, 이 단어는 기원전 1세기경 그리스 철학자 안드로니코스(Andronicos)가 아리스토텔레스(Aristoteles)의 철학을 정리하면서 만든 용어다.각주1)

meta는 영어에서는 about의 의미로 쓰인다. 가령 “meta model”이라고 말하면 모델 자체가 아니라 “다른 모델들에 관한 하나의 모델(a model ABOUT other models)”을 뜻한다. 영화업계를 다룬 영화가 나온다면 “meta－movie”가 된다. 언어의 meta－message는 ‘다른 메시지에 관한 메시지’이고 meta－communication은 ‘의사 교환에 관한 이야기’라는 뜻이다.


요약 데이터에 관한 구조화된 데이터로, 다른 데이터를 설명해 주는 데이터.


속성정보라고도 한다. 대량의 정보 가운데에서 찾고 있는 정보를 효율적으로 찾아내서 이용하기 위해 일정한 규칙에 따라 콘텐츠에 대하여 부여되는 데이터이다. 여기에는 콘텐츠의 위치와 내용, 작성자에 관한 정보, 권리 조건, 이용 조건, 이용 내력 등이 기록되어 있다. 컴퓨터에서는 보통 메타데이터를 데이터를 표현하기 위한 목적과 데이터를 빨리 찾기 위한 목적으로 사용하고 있다.

데이터를 표현하기 위한 목적으로 사용되는 메타데이터의 가장 좋은 예가 HTML 태그이다. 데이터에 관한 구조화라는 것은 HTML 태그 안에 head나 body가 있으며, body 안에는 table이 올 수 있고, table 안에는 tr이, tr 안에는 td가 올 수 있는 것처럼 데이터가 상위에서 하위로 나무(tree) 형태의 구조를 이루고 있다는 의미이다.
 
메타데이터의 또 다른 목적은 데이터를 빨리 찾기 위한 것으로, 컴퓨터에서 정보의 인덱스(Index) 구실을 한다. 우리가 많이 사용하는 데이타베이스도 이러한 메타데이터가 잘 구성되어 있기 때문에 데이터를 빨리 찾을 수 있다.

사용자는 메타데이터를 이용하여 자기가 원하는 특정 데이터(정보)를 검색엔진 등으로 쉽게 찾아낼 수 있다. 영화의 한 신에서 거기 나오는 배우의 데이터를 추출하거나 축구 시합의 비디오에서 골 인 장면만을 뽑아낼 수 있고, 또 이 자료들을 편집할 수 있는 것도 메타데이터의 기능이다.
 
전자의 경우에서나 후자의 경우에 메타데이터는 데이터를 사용하는 사람에게는 보이지 않는다. 그러나 기계(컴퓨터)는 메타데이터의 내용을 이해하고 이를 이용한다. 곧, 웹 자료나 다른 것들에 관해 기계가 이해할 수 있는 정보가 메타데이터인 것이다.

[네이버 지식백과] 메타데이터 [metadata] (두산백과)


Note



Detailed reference information for the JSON file, including options not used in this example, is at Custom Functions Registration JSON.

Note that for this example:

  • There's only one custom function, so there's only one member of the functions array.
  • The name property(특성) defines the function name. As you see in the animated(동영상) gif shown previously, a namespace (CONTOSO) is prepended(붙인다) to the function name in the Excel autocomplete menu. This prefix is defined in the add-in manifest(목록), described below. The prefix and the function name are separated using a period, and by convention prefixes and function names are uppercase(대문자). To use your custom function, a user types the namespace followed by the function's name (ADD42) into a cell, in this case =CONTOSO.ADD42. The prefix is intended to be used as an identifier(식별) for your company or the add-in.
  • The description appears in the autocomplete menu in Excel.
  • When the user requests help for a function, Excel opens a task pane(작업창) and displays(전시하다) the web page found at the URL specified in help Url.(컴퓨터 네트워크에서 자신이 원하는 정보 자원을 찾기 위해서는 해당 정보 자원의 위치와 종류를 정확히 파악할 필요가 있는데, 이를 나타내는 일련의 규칙을 URL(Uniform Resource Locator: 자원 위치 지정자)이라고 한다. URL에는 컴퓨터 네트워크 상에 퍼져있는 특정 정보 자원의 종류와 위치가 기록되어 있다)

    [네이버 지식백과] URL [Uniform Resource Locator] - 인터넷 정보의 위치 (용어로 보는 IT)

  • The result property specifies the type of information returned by the function to Excel. The type child property(하부특성) can "string", "number", or "boolean". The dimensionality property can be scalar or matrix (a two-dimensional array of values of the specified type.)
  • boolean이란

①영국의 수학자 조지 불(George Boole)이 창안한 불 대수의 이론과 규칙에 따른 연산에 관한 용어에 붙는 형용사.
②논리값을 다루는 연산에 관한 용어에 붙는 형용사. 불 대수는 X 또는 Y의 수치 계산이 아니라, 참 또는 거짓의 논리값을 다루기 때문에 이 용어가 쓰인다.
③일부 프로그램 언어에서의 기본적 데이터형의 하나. 많은 언어에서 참 또는 거짓의 논리값을 나타내는 불 데이터형을 직접 지원한다. 어떤 언어에서는 불값을 0(거짓)과 1(참)로 나타내는 정수 데이터형을 사용한다.

[네이버 지식백과] 불 [Boolean] (IT용어사전, 한국정보통신기술협회)


  • The parameters array specifies, in order, the type of data in each parameter that is passed to the function. The name and description child properties are used in the Excel intellisense. The type and dimensionality child properties are identical to the child properties of the result property described above.

  • The options property enables you to customize some aspects of how and when Excel executes the function. There is more information about these options later in this article.

    JavaScript 
    {
  "$schema": "https://developer.microsoft.com/en-us/json-schemas/office-js/custom-functions.schema.json",
  "functions": [
      {
          "name": "ADD42", 
          "description":  "adds 42 to the input numbers",
          "helpUrl": "http://dev.office.com",
          "result": {
              "type": "number",
              "dimensionality": "scalar"
          },
          "parameters": [
              {
                  "name": "number 1",
                  "description": "the first number to be added",
                  "type": "number",
                  "dimensionality": "scalar"
              },
              {
                  "name": "number 2",
                  "description": "the second number to be added",
                  "type": "number",
                  "dimensionality": "scalar"
              }
          ],
          "options": {
              "sync": true
          }
      }
  ]
}

Note

The custom functions are registered when a user runs the add-in for the first time. After that, they are available, for that same user, in all workbooks (not only the one where the add-in ran initially.)

Your server settings for the JSON file must have CORS enabled in order for custom functions to work correctly in Excel online.

Manifest file (customfunctions.xml)

The following is an example of the <ExtensionPoint> and <Resources> markup that you include in the add-in's manifest to enable Excel to run your functions. Note the following about this markup:

  • The <Script> element and its corresponding resource ID specifies the location of the JavaScript file with your functions.
  • The <Page> element and its corresponding resource ID specifies the location of the HTML page of your add-in. The HTML page includes a <Script> tag that loads the JavaScript file (customfunctions.js). The HTML page is a hidden page and is never displayed in the UI.
  • The <Metadata> element and its corresponding resource ID specifies the location of the JSON file.
  • A <Namespace> element and its corresponding resource ID specifies the prefix for all custom functions in the add-in.
XML 
<VersionOverrides xmlns="http://schemas.microsoft.com/office/taskpaneappversionoverrides" xsi:type="VersionOverridesV1\_0">
    <Hosts>
        <Host xsi:type="Workbook">
            <AllFormFactors>
                <ExtensionPoint xsi:type="CustomFunctions">
                    <Script>
                        <SourceLocation resid="residjs" />
                    </Script>
                    <Page>
                        <SourceLocation resid="residhtml"/>
                    </Page>
                    <Metadata>
                        <SourceLocation resid="residjson" />
                    </Metadata>
                    <Namespace resid="residNS" />
                </ExtensionPoint>
            </AllFormFactors>
        </Host>
    </Hosts>
    <Resources>
        <bt:Urls>
            <bt:Url id="residjson" DefaultValue="http://127.0.0.1:8080/customfunctions.json" />
            <bt:Url id="residjs" DefaultValue="http://127.0.0.1:8080/customfunctions.js" />
            <bt:Url id="residhtml" DefaultValue="http://127.0.0.1:8080/customfunctions.html" />
        </bt:Urls>
        <bt:ShortStrings>
            <bt:String id="residNS" DefaultValue="CONTOSO" />
        </bt:ShortStrings>
    </Resources>
</VersionOverrides>

Initializing custom functions

Your code must initialize the custom functions feature before using it. You can do this either in a <Script> tag in the HTML file (customfunctions.html) or at the top of the JavaScript file (customfunctions.js). During the preview of custom functions, you have your choice of two syntaxes for intializing. The HTML file in the repo uses the following syntax:

JavaScript 
Office.initialize = function (reason) {
    return Excel.CustomFunctions.initialize();
};

You can also use the following syntax:

JavaScript 
Office.Preview.StartCustomFunctions();

Synchronous and asynchronous functions

The function ADD42 above is synchronous with respect to Excel (designated by setting the option "sync": true in the JSON file). Synchronous functions offer fast performance because they run in the same process as Excel and they run in parallel during multithreaded calculation.

On the other hand, if your custom function retrieves data from the web, it must be asynchronous with respect to Excel. Asynchronous functions must:

  1. Return a JavaScript Promise to Excel.
  2. Resolve the Promise with the final value using the callback function.

The following code shows an example of an asynchronous custom function that retrieves the temperature of a thermometer. Note that sendWebRequest is a hypothetical function, not specified here, that uses XHR to call a temperature web service.

JavaScript 
function getTemperature(thermometerID){
    return new OfficeExtension.Promise(function(setResult){
        sendWebRequest(thermometerID, function(data){
            setResult(data.temperature);
        });
    });
}

Asynchronous functions display a GETTING_DATA temporary error in the cell while Excel waits for the final result. Users can interact normally with the rest of the spreadsheet while they wait for the result.

Note

Custom functions are asynchronous by default. To designate functions as synchronous set the option "sync": true in the options property for the custom function in the registration JSON file.

Streamed functions

An asynchronous function can be streamed. Streamed custom functions let you output data to cells repeatedly over time, without waiting for Excel or users to request recalculations. The following example is a custom function that adds a number to the result every second. Note the following about this code:

  • Excel displays each new value automatically using the setResult callback.
  • The final parameter, caller, is never specified in your registration code, and it does not display in the autocomplete menu to Excel users when they enter the function. It’s an object that contains a setResult callback function that’s used to pass data from the function to Excel to update the value of a cell.
  • In order for Excel to pass the setResult function in the caller object, you must declare support for streaming during your function registration by setting the option "stream": true in the options property for the custom function in the registration JSON file.
JavaScript 
function incrementValue(increment, caller){
    var result = 0;
    setInterval(function(){
         result += increment;
         caller.setResult(result);
    }, 1000);
}

Cancellation

You can cancel streamed functions and asynchronous functions. Canceling your function calls is important to reduce their bandwith consumption, working memory, and CPU load. Excel cancels function calls in the following situations:

  • The user edits or deletes a cell that references the function.
  • One of the arguments (inputs) for the function changes. In this case, a new function call is triggered in addition to the cancelation.
  • The user triggers recalculation manually. As with the above case, a new function call is triggered in addition to the cancelation.

You must implement a cancellation handler for every streaming function. Asynchronous, non-streaming functions may or may not be cancelable; it's up to you. Synchronous functions cannot be canceled.

To make a function cancelable, set the option "cancelable": true in the options property for the custom function in the registration JSON file.

The following code shows the previous example with cancellation implemented. In the code, the caller object contains an onCanceled function must be defined for each cancelable custom function.

JavaScript 
function incrementValue(increment, caller){ 
    var result = 0;
    var timer = setInterval(function(){
         result += increment;
         caller.setResult(result);
    }, 1000);
    caller.onCanceled = function(){
        clearInterval(timer);
    }
}

Saving and sharing state

Asynchronous custom functions can save data in global JavaScript variables. In subsequent calls, your custom function may use the values saved in these variables. Saved state is useful when users add the same custom function to more than one cell, because all the instances of the function can share the state. For example, you may save the data returned from a call to a web resource to avoid making additional calls to the same web resource.

The following code shows an implementation of the previous temperature-streaming function that saves state globally. Note the following about this code:

  • refreshTemperature is a streamed function that reads the temperature of a particular thermometer every second. New temperatures are saved in the savedTemperatures variable, but does not directly update the cell value. It should not be directly called from a worksheet cell, so it is not registered in the JSON file.
  • streamTemperature updates the temperature values displayed in the cell every second and it uses savedTemperatures variable as its data source. It must be registered in the JSON file, and named with all upper-case letters, STREAMTEMPERATURE.
  • Users may call streamTemperature from several cells in the Excel UI. Each call reads data from the same savedTemperatures variable.
JavaScript 
var savedTemperatures;
function streamTemperature(thermometerID, caller){ 
     if(!savedTemperatures[thermometerID]){
         refreshTemperatures(thermometerID); // starts fetching temperatures if the thermometer hasn't been read yet
     }
     function getNextTemperature(){
         caller.setResult(savedTemperatures[thermometerID]); // setResult sends the saved temperature value to Excel.
         setTimeout(getNextTemperature, 1000); // Wait 1 second before updating Excel again.
     }
     getNextTemperature();
}
function refreshTemperature(thermometerID){
     sendWebRequest(thermometerID, function(data){
         savedTemperatures[thermometerID] = data.temperature;
     });
     setTimeout(function(){
         refreshTemperature(thermometerID);
     }, 1000); // Wait 1 second before reading the thermometer again, and then update the saved temperature of thermometerID.
}

Note

Synchronous functions (designated by setting the option "sync": true in the JSON file) cannot share state because Excel parallelizes them during multithreaded calculation. only asynchronous functions may share state because an add-in's synchronous functions share the same JavaScript context in each session.

Working with ranges of data

Your custom function can take a range of data as a parameter, or you can return a range of data from a custom function.

For example, suppose that your function returns the second highest value from a range of numbers stored in Excel. The following function takes the parameter values, which is an Excel.CustomFunctionDimensionality.matrix parameter type. Note that in the registration JSON for this function, you would set the parameter's type property to matrix.

JavaScript 
function secondHighest(values){ 
     var highest = values[0][0], secondHighest = values[0][0];
     for(var i = 0; i < values.length; i++){
         for(var j = 1; j < values[i].length; j++){
             if(values[i][j] >= highest){
                 secondHighest = highest;
                 highest = values[i][j];
             }
             else if(values[i][j] >= secondHighest){
                 secondHighest = values[i][j];
             }
         }
     }
     return secondHighest;
 }

As you can see, ranges are handled in JavaScript as arrays of row arrays (like a 2-dimensional array).

Known issues

  • Help URLs and parameter descriptions are not yet used by Excel.
  • Custom functions are not currently available on Excel for mobile clients.
  • Currently, add-ins rely on a hidden browser process to run asynchronous custom functions. In the future, JavaScript will run directly on some platforms to ensure custom functions are faster and use less memory. Additionally, the HTML page referenced by the <Page> element in the manifest won’t be needed for most platforms because Excel will run the JavaScript directly. To prepare for this change, ensure your custom functions do not use the web page DOM. The supported host APIs for accessing the web will be WebSocket and XHR using GET or POST.
  • Volatile functions (those which recalculate automatically whenever unrelated data changes in the spreadsheet) are not yet supported.
  • Debugging is only enabled for asynchronous functions on Excel for Windows.
  • Deployment via the Office 365 Admin Portal and AppSource are not yet enabled.
  • Custom functions in Excel online may stop working during a session after a period of inactivity. Refresh the browser page (F5) and re-enter a custom function to restore the feature.

Changelog

  • Nov 7, 2017: Shipped the custom functions preview and samples
  • Nov 20, 2017: Fixed compatibility bug for those using builds 8801 and later
  • Nov 28, 2017: Shipped support for cancellation on asynchronous functions (requires change for streaming functions)
  • May 7, 2018: Shipped support for Mac, Excel online, and synchronous functions running in-process