JavaScript 스프레드시트에서 동적 배열을 사용하여 데이터 분석 및 추출하기

추천0

페이지 정보

작성자 GrapeCity

작성일 2020-03-09 00:00 조회 3,279회 댓글 0건

본문

관련링크

Microsoft Excel은 동적 배열이라고 하는 강력하고 새로운 계산 기능을 추가했으며 SpreadJS 에서도 이러한 기능을 지원합니다. 이전에는 하나의 수식을 사용하면 하나의 결과를 반환했지만, 새로운 동적 배열 지원을 통해 하나의 수식에서 여러 결과를 반환할 수 있습니다. 이러한 결과는 인접한 셀로 확장될 수 있으며, Spilled Array Formula라고 알려져있습니다.

이 포스팅에서는 SpreadJS Designer와 FILTER, SORT, UNIQUE Spilled Array(배열) 그리고 Spilled Operator(연산자)를 포함하는 SpreadJS의 기본 API를 이용하여 동적 배열 함수를 구현해 보겠습니다.

JavaScript 스프레드시트 설정

이 프로젝트의 테이블과 데이터가 포함된 기본 SSJSON 파일(첨부파일 확인)을 SpreadJS Desinger를 이용하여 미리 생성하였습니다. 우리는 이 파일을 나머지 프로젝트를 로드하기 위한 기초로 사용할 것입니다.

다음과 같이 HTML 파일에 완료되지 않은 JSON을 JavaScript와 함께 로드합니다.

<script type="text/javascript" src="dynamicArrays.js"></script>

<script>
    window.onload = function() {
       // Initializing Spread         
      var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });         

      // Load JSON template saved as JS         
      spread.fromJSON(dynamicArrays);     
};
</script>

동적 배열 함수 시작하기

이제 데이터가 로드되었습니다. 이제 각기 다른 동적 배열 함수를 사용하여 몇 가지 함수만으로 데이터를 분석할 수 있습니다. 동적 배열을 활성화하기 위해, 코드에서 가장 먼저 해야 할 일은 allowDynamicArray 옵션을 true로 설정하는 것입니다.

spread.options.allowDynamicArray = true;

동적 배열을 활성화하면, 일부 수식이 다르게 계산될 수 있으므로, 위의 옵션 설정이 필요합니다. 이 기능을 사용하면, 여러 결과를 반환할 수 있는 함수에서 단일 값을 반환하려는 경우, SINGLE 함수 또는 '@' 연산자를 사용해야 합니다.

첫번째로 시도하는 기능은 FILTER입니다. 10,000 달러가 넘는 모든 주문을 필터링하는 데 사용할 것입니다. 이를 위해, 셀을 선택하고 FILTER 함수에 다음의 세 가지 매개 변수를 제공할 수 있습니다.

필터링 할 전체 범위 (The entire range to filter)
특정 셀의 범위와 비교 대상 (The range of cells and the comparison)
모든 값이 비어있는 경우 반환 할 값 (The value to return if all the values are empty)

코드에서 이것을 설정하려면, 셀에서 아래와 같은 수식을 설정해야 합니다.

sheet.setFormula(23, 2, 'FILTER(SalesData[[#Data], [Sales Rep]:[Amount ($)]],SalesData[[#Data], [Amount ($)]]>10000,"")');

다른 중요한 동적 배열 기능에는 SORT, SORTBY 및 UNIQUE가 있습니다. UNIQUE 및 SORT 함수를 하나로 결합하여 다음을 수행 할 수 있습니다.

중복을 무시하고 표에서 모든 영업 담당자를 찾습니다.

해당 담당자를 알파벳순으로 정렬합니다.

SORTBY 함수를 사용하여 전체 주문을 달러 금액으로 정렬할 수 있습니다.

이 수식을 설정하는 코드는 FILTER 함수 만큼 간단합니다.

//Set SORT and UNIQUE functions sheet.setFormula(23, 6, 'FILTER(SalesData[[#Data], [Sales Rep]:[Amount ($)]],SalesData[[#Data], [Amount ($)]]>10000,"")');

//Set SORTBY function sheet.setFormula(33, 2, 'SORTBY(SalesData[#Data],SalesData[[#Data], [Amount ($)]],-1)');

Spilled Array 시작하기

지금까지 이 포스팅에서 보았던 모든 수식은 여러 결과를 반환하고 있으며 이 결과는 다른 셀로 확장되며, "Spilled Array"라고 불립니다. Spilled Array는 공간이 있는 한 배열이 데이터에 맞게 자동으로 확장됨을 나타냅니다. 이 경우 다른 명령을 추가 할 수 있으며, Spilled Array의 크기가 자동으로 증가하여 해당 항목을 목록에 추가합니다.

경우에 따라 배열이 확장될 공간이 부족하기도 합니다. 이 경우,, 배열이 확장하고자 하는 범위 영역에 보여주는 "#SPILL!" 오류가 발생합니다.

Spilled 연산자 소개

Spilled Array를 사용하면 결과를 참조하는 방법을 찾는 복잡성이 가중됩니다. 동적 배열 수식을 입력했는데 결과가 A3 : A8로 끝나면 다른 수식에서도 사용할 수 있습니다. 그러나 사용자가 다른 곳에 데이터를 추가하여 결과를 변경하면 어떻게 될까요? 이제 해당 참조는 사용하지 못하게 됩니다. 이를 간단하게 수정할 수 있는 방법은 "A1 #" 표기법을 사용하는 것입니다. "#"은 기본적으로 동적 배열 수식의 spilled array 결과를 지정합니다. 마지막 예에서는 A3 : A8을 사용하는 대신 "A3 #"을 사용하여 spilled array를 참조합니다.

우리는 이것을 샘플에서 활용할 수 있습니다. 판매된 모든 제품의 총 수량을 얻고 싶다고 가정하겠습니다. 먼저 UNIQUE를 사용하여 모든 제품을 찾습니다.