고급기능 JavaScript 스프레드시트 및 동적 배열에서 현금 흐름 보고서 작성
페이지 정보
작성자 GrapeCity 작성일 2022-05-27 15:47 조회 492회 댓글 0건본문
관련링크
현금 흐름 달력은 매일의 현금 흐름을 편리하고 직관적으로 요약해 보여 줍니다. 이 기능은 한 달 중 각 날짜의 보기를 제공하는데, 양수 및 음수 입금 및 출금이 강조 표시되어 있습니다.
이 블로그에서는 Excel과 유사한 JavaScript 스프레드시트 솔루션인 SpreadJS를 사용하여 JavaScript에서 현금 흐름 달력을 만드는 방법을 알아봅니다. 이 달력은 다음과 같은 강력한 기능을 광범위하게 사용합니다.
동적 배열 수식 - 한 수식을 기준으로 광범위한 셀에 여러 결과를 반환합니다. 이 예에서는 SEQUENCE 및 FILTER 기능을 사용합니다.
RANGEBLOCKSPARKLINE(template_range, data_expr) - 이 스파크라인을 사용하면 개발자는 셀 범위 템플릿(template_range)을 단일 셀 유형으로 정의하고 해당 템플릿을 셀 하나에 적용하여 데이터 집합(data_expr)을 템플릿으로 로드할 수 있습니다. 이 템플릿에는 행 및/또는 열을 여러 개 포함할 수 있습니다.
을 다운로드하여 따라 해보세요.
현금 흐름 달력을 만들려면 아래에서 설명하는 시트 3개를 만들어야 합니다.
데이터 소스 시트
이 예의 데이터 소스는 거래 목록입니다.
우리는 보다 동적인 표를 만들었고 셀 범위 대신 데이터가 필요한 경우 Table1을 참조할 수 있습니다.
이 표에는 TransactionID, 거래 유형, 거래 날짜, 회사 이름, 계정 이름, 입금 금액 및 인출 금액에 관한 정보가 포함되어 있습니다.
템플릿 시트
이 페이지에는 현금 흐름 달력에서 발생하는 거래를 나타내는 데 사용할 템플릿 범위가 포함됩니다.
여기서 셀 범위는 현금 흐름 달력에 필요한 정보를 포함하는 셀의 템플릿으로 사용됩니다.
가장 먼저 할 일은 셀을 정렬한 다음 셀에 대한 바인딩 경로를 설정하는 것입니다.
SpreadJS 메서드를 사용하여 Javascript를 통해 이렇게 할 수 있습니다.
templateSheet.setBindingPath(0, 1, "month"); templateSheet.setBindingPath(1, 2, "date"); templateSheet.setBindingPath(2, 2, "start"); templateSheet.setBindingPath(3, 2, "withdrawals"); templateSheet.setBindingPath(4, 2, "deposits"); templateSheet.setBindingPath(5, 2, "end");
를 사용하는 경우 다음 단계를 따라야 합니다. 이 디자이너는 다운로드에 포함되어 있으며 "\SpreadJS.Release.x.x.x\Designer\Designer Runtime" 폴더에서 설치할 수 있습니다.
데이터 탭 에서 템플릿 메뉴를 클릭하면 오른쪽에 필드 목록 패널이 나타납니다.
Start 분기를 마우스로 가리킨 다음 녹색 + 버튼을 클릭하여 필드를 추가합니다. * 필드는 "x" 버튼을 클릭하여 제거할 수 있고 분기 오른쪽에 있는 설정을 사용하여 수정할 수 있습니다.
템플릿 범위의 원하는 셀에 필드를 끌어옵니다.
현금이 부족한 날(마이너스 기말 잔액)은 빨간색으로, 플러스 기말 잔액이 있는 날은 녹색으로, 마이너스도 플러스도 아닌 경우 검은색으로 지정하려면 조건부 서식을 사용할 수 있습니다. 디자이너에서 다음을 수행합니다.
병합될 때 날짜 셀 "A2:D2"를 선택합니다.
조건부 서식 → 새 규칙을 선택합니다.
규칙 유형으로 수식 사용 을 선택하고 서식을 지정할 셀을 결정합니다.
수식을 입력합니다. 이 경우에는 ='Cell Template'!$C$6>0을 입력합니다.
서식 → 채우기 를 클릭하고 글꼴 색상으로 녹색을 선택합니다.
수식 ='Cell Template'!$C$6<0 을 사용하여 동일한 단계를 반복합니다. *잔액이 마이너스인 경우에는 색상을 빨간색 으로 설정해야 합니다.
현금 흐름 달력: 시트 렌더링
1단계: MonthPicker 요소 추가
달력의 첫 번째 요소는 변경 가능한 월 요소입니다. 추가하려면 SpreadJS의 드롭다운 셀 스타일 유형인 MonthPicker를 사용합니다.
JavaScript:
var monthPickerStyle = new GC.Spread.Sheets.Style(); monthPickerStyle.dropDowns = [ { type: GC.Spread.Sheets.DropDownType.monthPicker, option: { startYear: 2019, stopYear: 2021, height: 300, } } ]; sheet.setStyle(2, 5, monthPickerStyle);
디자이너:
셀을 선택합니다. (이 경우에는 B2)
홈 탭 → 셀 드롭다운 → MonthPicker를 클릭합니다.
명령 오른쪽에서 ...를 클릭합니다.
시작 연도, 종료 연도 및 선택기 높이를 설정합니다.
계산 시 월을 포함하는 셀에 이름을 할당합니다.
수식 탭에서 이름 관리자를 선택합니다.
팝업에 새로 만들기 버튼을 클릭합니다.
셀의 이름을 설정합니다. 이 예에서 이름은 다음과 같습니다. : currentMonth
$D$2를 참조합니다. 또한 메모를 추가하고 참조를 변경할 수 있습니다.
2단계: 현금 흐름 달력 만들기
먼저, 다음과 같은 달력 디자인을 만듭니다.
SEQUENCE(rows,columns,start,step) 함수를 사용하여 달력에 날짜를 할당합니다. 그러면 나중에 시 셀 값을 검색할 수 있습니다. B4 셀에 대한 수식은 다음과 같습니다.
=SEQUENCE(6,7,currentMonth-WEEKDAY(currentMonth)+1,1)
JavaScript:
cashflowSheet.setFormula(3, 1, '=SEQUENCE(6,7,currentMonth-WEEKDAY(currentMonth)+1,1)');
아직은 셀에 포맷터를 사용하지 않았습니다.
다음 단계에서는 조건부 서식을 사용하여 다른 달에 속해 있지만 선택한 날짜를 비웁니다.
B4:H9를 선택한 다음 달력의 날짜 → 조건부 서식을 클릭합니다.
드롭다운 목록에서 새 규칙을 선택하고 "서식을 지정할 셀을 결정하는 데 규칙 유형으로 수식 사용"을 선택합니다.
수식을 입력합니다. 이 경우에는 "=MONTH(B4)<>MONTH(currentMonth)"를 입력합니다. 이 서식은 월이 드롭다운에서 선택한 월과 다른 셀에만 해당합니다.
서식을 클릭합니다.
숫자 → 사용자 정의 를 클릭합니다.
";;;"을 포맷터로 설정하여 오른쪽 셀을 모두 비웁니다.
다음 단계에서는 TemplateSheet의 셀 범위를 단일 셀 유형으로 사용할 RANGEBLOCKSPARKLINE과 현금 흐름 달력의 날짜를 나타내는 모든 셀에 이 템플릿을 적용하는 OBJECT 함수를 사용합니다.
SEQUENCE를 사용하여 이러한 셀에 대한 값을 설정했으므로 RANGEBLOCKSPARKLINE을 형식으로 사용합니다.
셀 범위 B4:H9를 선택합니다.
형식 → 기타 숫자 형식 → 사용자 정의를 클릭합니다.
포맷터를 다음과 같이 설정합니다. =RANGEBLOCKSPARKLINE('Cell Template'!$A$2:$D$7,OBJECT("date",@,"start",IFERROR(SUM(FILTER(Table1[Deposit],Table1[Date]<@))-SUM(FILTER(Table1[Withdrawal],Table1[Date]<@)),0),"withdrawals",IFERROR(SUM(FILTER(Table1[Withdrawal],Table1[Date]=@)),0),"deposits",IFERROR(SUM(FILTER(Table1[Deposit],Table1[Date]=@)),0),"month",MONTH($A$2)))
첫 번째 인수로, 셀 범위를 TemplateSheet의 템플릿으로 가져옵니다.
두 번째 인수로, Datasource 시트에 있는 Table1의 데이터를 사용하는 OBJECT를 사용합니다.
[date]: 셀의 현재 값
[start]: 이전의 모든 입금 금액 합계 - 이전의 모든 인출 금액 합계
[withdrawals]: 현재 인출 금액 합계
[deposits]: 현재 입금 금액 합계
[end]: [start] + 현재의 모든 입금 금액 합계 - 현재의 모든 인출 금액 합계
수식을 사용하면 범위 템플릿을 바인딩 및 반환하여 범위 템플릿 작업을 더 쉽게 할 수 있습니다.
최종 출력 결과는 다음과 같습니다.
위 그림에 표시된 것처럼 달력의 날짜가 포함된 셀에는 시작/종료 잔액, 입금 금액 합계 및 인출 금액 합계 정보가 표시됩니다.
3단계: 일일 거래 가져오기
DataSource 페이지에서 모든 거래 목록을 추출하려는 경우 이벤트를 사용하여 추출할 수 있습니다. SpreadJS의 워크시트에는 이벤트 발생 시 특정 작업에 바인딩되는 이벤트가 있습니다.
이 예에서는 유용한 SpreadJS 기능을 사용하여 사용자가 달력에서 날짜를 선택하면 모든 거래 목록을 추출했습니다.
거래에 관한 정보를 포함하는 표와 계산을 더 쉽게 만들 수 있기 때문에 선택한 날짜, 입금 금액 및 인출 금액을 포함할 셀에 이름을 할당합니다. currentMonth에 대한 이름 범위를 만드는 단계는 다음과 같습니다.
수식 탭에서 이름 관리자 를 선택합니다.
팝업에 새로 만들기 버튼을 클릭합니다.
셀의 이름을 설정합니다.
우리가 사용하는 예에서는 다음과 같습니다.
- name: currentSelection; refer to: ='Cash-Flow'!$B$11
- name: currentDeposits; refer to: =FILTER(tblTransactions[Type]:tblTransactions[Withdrawal],(tblTransactions[Date]=CurrentSelection)*(tblTransactions[Deposit]>0))
- name: currentWithdrawals; refer to: =FILTER(tblTransactions[Type]:tblTransactions[Withdrawal],(tblTransactions[Date]=CurrentSelection)*(tblTransactions[Withdrawal]>0))
모든 입금 금액 목록, 모든 인출 금액 목록, 종료 및 시작 잔액을 가져오는 여러 가지 수식을 설정합니다.
시작 잔액(모든 이전 입금 금액 합계 - 모든 이전 인출 금액 합계): =IFERROR((SUM(FILTER(tblTransactions[Deposit],tblTransactions[Date]<$B$11))-SUM(FILTER(tblTransactions[Withdrawal],tblTransactions[Date]<$B$11))),0)
종료 잔액(시작 잔액 + 현재 입금 금액 합계 - 현재 인출 금액 합계): =IFERROR(D13+(SUM(FILTER(tblTransactions[Deposit],tblTransactions[Date]=$B$11))-SUM(FILTER(tblTransactions[Withdrawal],tblTransactions[Date]=$B$11))),0)
여기서 D13은 시작 잔액 입니다.
입금 금액: =IFERROR(FILTER(currentDeposits,{1,0,1,1,0}),"")
인출 금액: =IFERROR(FILTER(currentWithdrawals,{1,0,1,0,1}),"")
이때, currentSelection은 수동으로 삽입됩니다. 사용자의 날짜 선택에 따라 변경되도록 하려면 다음 단계를 수행합니다.
JavaScript에서 이벤트 처리기 함수를 만듭니다(아래 내용 참조).
// on day selection, update a cell used in filtering the data to show detailed transaction list cashflowSheet.bind(GC.Spread.Sheets.Events.SelectionChanged, function (sender, args) { const sheet = args.sheet; const row = args.newSelections[0].row; const col = args.newSelections[0].col; if ((row < 3 || row >= 3 + 6) || (col < 1 || col >= 1 + 7)) return; // set the current date cell so that FILTER would update. sheet.setValue(10, 1, sheet.getValue(row, col)); });
사용자가 셀을 클릭하면 위 코드가 해당 셀이 달력 경계(B4:H9) 내에 포함되어 있는지 확인합니다. 그렇지 않으면 currentSelection을 업데이트합니다. 따라서 변경된 선택 날짜를 가리키면 거래에 관한 정보 및 잔액을 가져오는 데 사용하는 모든 수식이 올바른 결과를 제공합니다.
위 예는 SpreadJS 기능을 사용하여 응용 프로그램을 개선하고, 간단한 데이터 집합에서 매우 유용한 매력적인 Excel과 같은 대시보드로 콘텐츠를 변환하는 많은 방법 중 하나입니다.
이 JavaScript 컴포넌트는 위의 예 외에도 재무 응용 분야에서 다양한 요소를 쉽게 만들 수 있는 수백 가지 통계 및 재무 함수와 수식을 제공합니다.
지금 바로 SpreadJS를 다운로드하여 직접 테스트해보세요!
댓글목록
등록된 댓글이 없습니다.