동적 배열 개요

동적 배열은 단일 셀에 입력한 수식을 바탕으로 값을 자동으로 계산하여 여러 셀에 표시할 수 있는 SpreadJS의 매우 강력한 기능입니다.

SpreadJS는 다음 기본 제공 동적 배열 수식을 지원합니다.

• FILTER - 정의한 조건에 따라 데이터 범위를 필터링합니다.

• RANDARRAY - 난수 배열을 반환합니다.

• SEQUENCE - 일련 번호 목록을 배열로 생성합니다(예: 1, 2, 3, 4).

• SORT - 범위 또는 배열의 콘텐츠를 정렬합니다.

• SORTBY - 해당 범위 또는 배열의 값을 기준으로 범위 또는 배열의 콘텐츠를 정렬합니다.

• UNIQUE - 고유 값 목록을 목록 또는 범위로 반환합니다.

• 암시적 교차 연산자 @: 암시적 교차라고 하는 로직을 사용하여 단일 값을 반환합니다.

유출 범위 연산자 #는 전체 분할 범위를 참조할 수 있습니다.

• 유출된 배열 함수를 처리하는 경우 범위 뒤에 유출된 범위 연산자(#)를 배치하여 전체 유출 범위를 참조할 수 있습니다.

기본 제공되는 동적 배열 수식과 함께 SpreadJS는 사용자가 사용자 정의 함수를 만들어 동적 배열 수식에서 사용할 수 있는 사용자 정의 동적 배열 수식도 지원합니다.

SpreadJS를 사용할 때 플래그 allowDynamicArray = true;를 설정하여 동적 배열 기능을 활성화할 수 있습니다.

다음은 JavaScript 코드입니다.

// 1st way:  
var workbook = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 1, allowDynamicArray: true});  
// 2nd way:  
var workbook = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));  
workbook.options.allowDynamicArray = true;

GrapeCity의 Designer 컴포넌트를 사용하는 경우 아래 단계를 따르세요.

1. 설정으로 이동합니다.

2. 계산을 클릭합니다.

3. 일반을 선택합니다.

4. "동적 배열 허용"을 선택합니다.

5. 확인을 클릭합니다.

여기서 JavaScript 스프레드시트 컴포넌트의 동적 배열을 사용한 데이터 분석 및 추출에 대해 자세히 알아보세요.

동적 배열의 예

다음은 SpreadJS에서 동적 배열을 사용하여 JavaScript 응용 프로그램에 변동성을 가져오는 방법을 보여 주는 몇 가지 예입니다.

자유롭게 SpreadJS ssJson 양식 샘플을 다운로드하여 따라 해보세요. (첨부파일을 통해서도 다운 받을 수 있습니다.)

상환 일정

대출 상환 계산은 다양한 핀테크 응용 프로그램에서 매우 유용합니다. 그러나 기간이 바뀌는 경우 상환 일정을 업데이트하는 것이 까다로울 수 있습니다. 즉, 360개월 대출에 대한 상환 일정을 확인한 다음 180개월 대출에 대한 상환 일정으로 업데이트하고 싶을 수 있습니다. 기간이 달라지면 표시되는 줄 수 역시 달라져야 합니다. 그러므로 다양한 조건부 서식 규칙이 필요할 수 있습니다. 하지만 동적 배열을 사용하면 이야기가 달라집니다. 5개 수식만 사용하여 대출 상환 일정을 만들 수 있습니다.

아래 시나리오를 생각해 보세요.

동적 배열이 없으면 수식을 편집하거나, 행을 숨기거나 숨기기를 취소하거나, 시간 조건에 따라 모든 행에 수식을 채워야 할 수 있습니다. SpreadJS 동적 배열 기능을 사용하면 각 기간 출력을 계산하는 수식 하나만 사용하면 되므로 메모리 용량 및 계산 시간을 절약할 수 있습니다.

• Period - Period 열은 간단하게 1에서 시작하고 입력한 개월 수에서 끝나면 됩니다. 이를 위해 SEQUENCE 함수를 사용합니다. =SEQUENCE(months)

• Interest - IPMT 함수를 사용하여 각 기간에 대한 이자를 계산합니다. 동적 배열을 입력으로 사용하면 각 행에 대해 수식을 쓰는 대신 유출 범위 #B7을 전달합니다. =-IPMT(rate/12,B7#,months,amount)

• Principal - 이자 계산과 논리는 동일하지만 PPMT 함수를 사용해야 합니다. =-PPMT(rate/12,B7#,months,amount)

• End Balance - 주어진 모든 기간에 대한 종료 잔액을 계산하려면 SEQUENCE 함수를 인수로 사용하여 주어진 모든 기간에 대해 납부한 누적 원금을 계산하는 CUMPRINC 함수를 사용합니다. =amount+CUMPRINC(rate/12,months,amount,1,SEQUENCE(months),0)

• Start Balance - 유출 범위를 사용하면 아래 수식만 써서 시작 잔액을 찾을 수 있습니다. =E7#+F7#

바로 상환 일정이 준비됩니다.

금액, 이자율 또는 기간(월)을 변경하면 아무것도 업데이트하지 않아도 이에 따라 결과가 바뀝니다.

상위 N개 보고서

이 예는 일반적인 경우입니다. 특정 조건에 따라 데이터 집합에서 상위 N개 레코드를 검색하고 싶습니다. 이는 대화형 보고서 및 대시보드를 작성할 때 매우 유용합니다. 동적 배열을 사용하면 빠르고 쉽게 이와 같이 할 수 있습니다.

데이터 집합으로 tblSales라는 표가 있습니다. 연간 매출을 기준으로 영업 직원 N명의 목록을 추출하려고 합니다. 사용자가 F5에 숫자 N을 입력합니다.

• 셀 F9를 선택하고 다음 수식을 입력합니다. =IFERROR(SORT(FILTER(tblSales,tblSales[[#Data], [ Sales ]]>=LARGE(tblSales[[#Data], [ Sales ]],F5)),2,-1),"")

이 수식은 Sales 열에서 N번째로 가장 큰 항목을 찾은 다음 Sales 열의 값이 이 항목보다 크거나 같은 항목을 표에서 전부 가져옵니다. 마지막으로, 모든 항목이 내림차순(-1)으로 정렬되고 두 번째 열을 정렬 색인으로 사용합니다.

또한 COUNT 함수를 사용하는 확인을 포함하여 사용자가 목록에서 제공할 수 있는 항목 수보다 더 선택했는지 확인할 것입니다.

• F6을 선택하고 다음 수식을 입력합니다. =IF(F5>COUNT(tblSales[[#Data], [ Sales ]]),"Incorrect. Type a smaller number","")

오류 메시지를 더 눈에 잘 띄도록 하려면 텍스트 색상으로 "빨간색"을 사용합니다.

대화형 차트

다음으로, 동적 배열을 사용하여 수동 필터링 및 행 숨기기 없이 사용자가 보고 싶어하는 항목만 표시하는 동적 차트를 만드는 방법을 보여 주는 예가 있습니다.

이는 선택에 따라 대화형 차트를 포함하는 대시보드를 만드는 경우 특히 유용합니다.

다음은 개인 비용 예산이 포함된 표입니다.

M4에 모든 범주를 포함하는 드롭다운을 만듭니다. 사용자가 특정 범주를 선택하면 셀 F4에 있는, Expenses 및 Amounts 값을 포함한 표도 바뀝니다.

날짜 유효성 검사 기능을 사용하여 드롭다운을 만듭니다.

1. M4를 선택합니다.

2. 데이터 -> 데이터 유효성 검사를 클릭합니다.

3. 허용 목록 범주를 선택합니다.

4. 소스를 정의합니다. 범주를 하나씩 쓰거나 UNIQUE 수식을 사용하여 정의할 수 있습니다.

   =UNIQUE(tblExpenses[Category])

특정 범주에 속한 비용 목록을 가져오려면 FILTER 함수를 사용할 수 있습니다.

• F5를 선택하고 다음 수식을 입력합니다. =IF($M$4="All",SORTBY(tblExpenses[Expense],tblExpenses[Amount],-1),SORTBY(FILTER(tblExpenses[Expense],tblExpenses[Category]=$M$4),FILTER(tblExpenses[Amount],tblExpenses[Category]=$M$4),-1))

이 수식은 FILTER 함수를 사용하여 비용을 필터링하여 모든 비용을 가져오고(모두를 선택한 경우에는 비용을 모두 가져옴) 금액별로 배열을 정렬합니다.

VLOOKUP을 사용하여 열 F에 있는 Expense의 금액 값을 가져옵니다.

• G5를 선택하고 다음 수식을 입력합니다. =VLOOKUP(F5#,tblExpenses[[Expense]:[Amount]],2,FALSE)

그런 다음 계산식을 만들 때 쉽게 사용할 수 있도록 이름 및 Expenses의 금액을 포함하는 배열에 이름을 할당합니다. 이렇게 하려면 위 단계를 수행하세요.

1. _Formulas _Tab에서 이름 관리자를 선택합니다.

2. 팝업에서 새로 만들기 버튼을 클릭합니다.

3. 셀의 이름을 설정합니다.

이 예에서는:

name: exNames;  

Refer To: _=ANCHORARRAY('Interactive Chart'!$F$5) - _ ANCHORARRAY.name: exAmounts;

Refer To: =ANCHORARRAY('Interactive Chart'!$G$5) 대신 $F$5# 사용

이러한 이름 범위가 차트의 데이터 소스일 수 있습니다(수식을 직접 사용할 수도 있음).

이제 표시하고 싶은 Expenses의 범주에 따라 바뀌는 대화형 차트가 생성되었습니다.

검색 가능한 드롭다운 목록

값이 여러 개 있는 드롭다운 목록이 있는 경우 특정 값을 찾기 어려울 수 있습니다. 검색 가능한 드롭다운 목록은 값 찾기 효율성을 개선하는 좋은 방법일 수 있습니다.

사용 사례로, 제품 및 해당 가격 목록을 포함하는 표가 있습니다. E4에 내용을 변경할 때마다 업데이트되어 해당 문자가 제품 이름의 어딘가에 포함된 항목 목록을 표시하는 검색 가능한 드롭다운 목록이 필요합니다.

검색 가능한 드롭다운 목록을 만들려면 아래 단계를 수행하세요.

• E4에 쓴 문자가 포함된 항목 목록을 포함하는 유효성 검사 표를 만듭니다.

이렇게 하기 위해 H3를 선택하고 다음 수식을 입력합니다.

=IFERROR(SORT(FILTER(B3:B33,ISNUMBER(SEARCH(E4,B3:B33)),"not found")),"not found")

이 수식에서는 SEARCH를 사용하여 제품 목록에서 단어를 검색합니다. SEARCH 함수는 단어 및 !VALUE를 찾으면 인덱스를 반환하고 그러지 않으면 인덱스가 있는 항목만 가져오는 ISNUMBER 함수로 필터링되는 목록이 있습니다.

• 계산식을 보다 쉽고 깔끔하게 만들기 위해 드롭다운에 허용되는 값을 포함하는 목록에 이름을 할당합니다. Name: ddValid; Refer To: _=ANCHORARRAY('Searchable Drop Down List'!$H$3) _

셀 E4의 내용을 변경하면 데이터 유효성 검사 목록이 변경되고 기본적으로 제품을 선택하는 목록의 내용이 바뀝니다.

• 마지막으로 제품 가격을 가져오려면 셀 F4를 선택하고 다음 수식을 입력합니다. =FILTER(TableCustomer[[#Data], [Price]],TableCustomer[[#Data], [Product]]=$E$4,"")

이것이 추가 JavaScript 코드 없이 동적 배열 수식을 사용하여 검색 가능한 목록을 만드는 방법입니다.

시작-종료 날짜 차트

다음 샘플은 간단합니다. 날짜와 USD에서 EUR로 환산율이 포함된 표가 있습니다. 여기서 목표는 사용자가 "시작" 날짜(셀 F5) 및 "종료" 날짜(셀 G5)를 선택하면 해당 날짜가 통화 변환 차트를 위한 올바른 데이터 집합을 검색하도록 하는 것입니다.

두 개의 드롭다운에서 사용자는 fromDate(F5) 및 toDate(G5), 이렇게 두 개의 날짜를 선택합니다. 드롭다운 두 개를 채우는 날짜를 계산하려면 다음 단계를 수행하세요.

• O3를 선택하고 수식을 입력합니다. =UNIQUE(tblRates[[#Data], [Date]]) - 데이터 집합에 모든 날짜를 포함합니다.

• P3를 선택하고 수식을 입력합니다. =FILTER(N3#,N3#>F5,"") - 선택한 fromDate(셀 F5)에서 끝까지 모든 날짜를 포함합니다.

손쉽게 사용할 수 있도록 아래와 같은 이름 범위 두 개를 추가할 수 있습니다.

• name: fromDate ; Refer To: =ANCHORARRAY('From-To Date Chart'!$O$3)

• name: toDate ; Refer To: =ANCHORARRAY('From-To Date Chart'!$P$3)

이러한 이름은 각각 fromDate 및 toDate 드롭다운을 위한 소스 역할을 합니다.

FILTER 함수를 사용하여 사용자가 선택한 날짜를 바탕으로 날짜와 비율을 계산합니다.

• Date의 경우 - K3를 선택하고 다음 수식을 입력합니다. =FILTER(B5:B50,(B5:B50>=F5)*(B5:B50<=G5),"No records found")

• Rates - L3를 선택하고 다음 수식을 입력합니다. =FILTER(C5:C50,(B5:B50>=F5)*(B5:B50<=G5),"No records found")

날짜 배열에 대해 했던 것처럼 Date 및 Rates가 포함된 배열에 이름을 할당한 후에는 이러한 이름 범위를 차트의 데이터 소스로 사용할 수 있습니다. 먼저, 이름 관리자를 선택하고 아래와 같이 이름을 설정합니다.

name: chartDates ;

Refer To: =ANCHORARRAY('From-To Date Chart'!$K$3)

name: chartRates ;

Refer To: =ANCHORARRAY('From-To Date Chart'!$L$3) 

이러한 이름 범위가 차트의 데이터 소스일 수 있습니다(수식을 직접 사용할 수도 있음).

출력은 _from -to _date 선택에 따라 비율을 보여 주는 차트입니다.

다음은 스크린샷입니다.

요약

요약하면 다음과 같습니다.

• 동적 배열은 특정 수식을 작성하기 훨씬 쉽게 만듭니다. 덕분에 대출 상황 일정 등과 같은 다양한 사용 사례를 간소화할 수 있습니다.

• 대화형 및 동적 차트 만들기를 매우 쉽게 합니다.

• 동적 배열 수식은 무엇보다 필터링 및 정렬하기 위해 연결(중첩)할 수 있습니다.

• 이 기능은 데이터 캐시 JavaScript 스프레드시트를 훨씬 더 쉽게 효율적으로 활용하도록 합니다.

• 사용자가 낮은 메모리 공간(컴팩트)을 사용하면서 임의 액세스를 용이하게 만드는 캐시 친화적인 데이터 구조를 구축하고 싶어 합니다. 이러한 경우 동적 배열은 크기 및 용량에 관한 정보를 저장하기 위한 고정된 작은 추가 오버헤드만 있기 때문에 매우 유용할 수 있습니다.

지금 바로 SpreadJS를 다운로드하여 직접 테스트해보세요!

• SpreadJS 공식 홈페이지
• SpreadJS 튜토리얼 및 샘플
• SpreadJS Q&A
• SpreadJS FAQ