Excel 배열 수식을 사용하여 계산 단순화하기
페이지 정보
작성자 GrapeCity 작성일 2019-02-07 00:00 조회 4,083회 댓글 0건본문
첨부파일
관련링크
배열 수식은 스프레드시트에서 가장 강력하지만, 잘 사용하지 못하는 낮은 계산 기능 중 하나입니다. 사용자는 이를 사용하여 동일한 결과를 제공하면서, 수천 개의 수식을 간단한 계산으로 바꿀 수 있습니다.
예를 들어, 판매 테이블에서 자동으로 계산된 다른 열(Column)을 쉽게 만들거나, 필터링된 데이터를 만드는 데 사용할 수 있습니다. 배열 수식을 사용하면 다음과 같은 이점이 있습니다.
- 복잡한 계산에서 중간 계산의 필요성 제거
- 단일 계산식 대신, 전체 값 배열을 사용해야는 몇몇 계산은 배열 수식이 없으면 계산이 불가능 합니다.
- 단일 결과 또는 여러 결과 제공
- 공식에 사용 된 어레이가 메모리에 저장되므로 일관성과 속도 보장
배열 수식은 훨씬 더 많은 작업을 수행 할 수 있지만, 이 포스팅에서는 간단한 예제에 중점을 두어 SpreadJS에서 배열 수식을 사용해 볼 수 있습니다.
자바 스크립트 스프레드 시트 설정
script/css 참조를 추가하고 Spread 인스턴스를 초기화합니다.
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta charset="utf-8" />
<title>SJS Array Formulas</title>
<link href="http://cdn.grapecity.com/spreadjs/hosted/css/gc.spread.sheets.excel2013white.12.0.5.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="http://cdn.grapecity.com/spreadjs/hosted/scripts/gc.spread.sheets.all.12.0.5.min.js"></script>
<script type="text/javascript" src="ArrayFormulaTest.js"></script>
<script>
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("spreadSheet"), { sheetCount: 1 });
}
</script>
</head>
<body>
<div id="spreadSheet" style="width: 825px; height: 800px; border: 1px solid gray"></div>
</body>
</html>
그런 다음 이미 테이블이 있는, 미리 만들어진 통합 문서를 로드 합니다.
spread.fromJSON(ArrayFormulaTest);
var activeSheet = spread.getActiveSheet();
배열 수식을 사용하여 테이블 열 추가
추가할 첫 번째 배열 수식은 총합(Total) 열을 만드는 것입니다. 일반적으로 수량 열에 가격 열을 곱하기 위해 각 셀에 대한 수식을 만듭니다. 채울 수식을 만드는 대신이 작업을 수행하는 배열 수식을 만들 수 있습니다.
= E3 : E15 * F3 : F15
그러면 단일 열이 아닌, 값의 배열이 반환되어 전체 열에 적용됩니다. SpreadJS에서 배열 수식을 설정하려면 다음과 같이 행, 열, 행 수, 열 수 및 수식을 정의해야합니다.
activeSheet.setArrayFormula(2, 6, 13, 1, "E3:E15*F3:F15");
그런 다음 새로운 열의 서식을 지정할 수 있습니다
spread.options.allowUserEditFormula = true;
activeSheet.getCell(1, 6).value("Total");
activeSheet.setFormula(15, 6, "SUM(G3:G15)");
activeSheet.getRange(1, 6, 15, 1).formatter("$#,#");
이를 위해 배열 수식을 사용하면, 각 행의 총 열을 만드는 데 일반적으로 필요한 수식의 양이 크게 줄어 듭니다.
배열 수식으로 요약
배열 수식의 또 다른 용도는 데이터를보다 쉽게 요약하는 것입니다.
예를 들어, 특정 조건을 충족하는 경우에만 값을 합산 할 수 있습니다 (일반 수식으로는 쉽게 수행 할 수 없음). 일반적으로 IF 문을 사용하면, 셀 범위에 적용할때, 참 및 거짓 값의 배열이 반환됩니다. 단일 셀에서이를 효과적으로 사용하는 유일한 방법은 배열 수식입니다.
이를 위해 "판매자별 판매"와 "각 판매자별로 제품별 판매"라는 두 가지 테이블을 만들 수 있습니다.
activeSheet.getCell(1, 8).value("Sales by Seller");
activeSheet.getCell(2, 8).value("Seller");
activeSheet.getCell(2, 9).value("Total");
activeSheet.getCell(3, 8).value("Bob");
activeSheet.getCell(4, 8).value("Chris");
activeSheet.getCell(5, 8).value("Jill");
activeSheet.getCell(7, 8).value("Sales by Product");
activeSheet.getCell(8, 8).value("Seller");
activeSheet.getCell(8, 9).value("Spread");
activeSheet.getCell(8, 10).value("Wijmo");
activeSheet.getCell(8, 11).value("C1 Studio");
activeSheet.getCell(9, 8).value("Bob");
activeSheet.getCell(10, 8).value("Chris");
activeSheet.getCell(11, 8).value("Jill");
우선 판매자 별 판매 테이블를 가지고 시작해봅시다. 이 경우 배열 수식을 사용하여, 각 특정 판매자에 대한 모든 판매를 추가합니다. 공식을 단순화하기 위해, 사용할 사용자 정의 이름을 추가 할 수 있습니다.
activeSheet.addCustomName("Seller", "$A$3:$A$15", 0, 0);
activeSheet.addCustomName("Total", "$G$3:$G$15", 0, 0);
그런 다음 배열 수식을 다음과 같이 정의합니다.
= SUMIF (판매자, I4 : I6, 전체)
그런 다음 3개의 셀 모두에, 해당 배열 수식을 한 번에 설정할 수 있습니다.
activeSheet.setArrayFormula(3, 9, 3, 1, "SUMIF(Seller, I4:I6,Total)");
제품 별 판매 테이블의 배열 수식은 비슷하지만, 이 경우 판매 한 제품을 기준으로 각 판매자의 총계를 합산합니다.
다음과 같이 배열 수식을 사용하여 수행 할 수 있습니다.
= SUMIFS (총, 판매자, $ I $ 10 : $ I $ 12, 제품, $ J $ 9 : $ L $ 9)
이것은 테이블의 각 셀에 대해 정의됩니다.
activeSheet.addCustomName("Product", "$B$3:$B$15", 0, 0);
activeSheet.setArrayFormula(9, 9, 3, 3, "SUMIFS(Total, Seller, $I$10:$I$12, Product, $J$9:$L$9)");
이것들은 배열 수식에 대한 간단한 예이지만 훨씬 더 많이 사용할 수 있습니다.
이제 SpreadJS에서 배열 수식이 지원되므로 가장 발전된 Excel 파일을 쉽게 가져올 수 있습니다. 스프레드시트에서 더욱 향상된 Excel 기능을 사용할 수있는 가능성은 무한합니다.
댓글목록
등록된 댓글이 없습니다.