Excel 배열 수식을 사용하여 계산 단순화하기

추천0

페이지 정보

작성자 GrapeCity

작성일 2019-02-07 00:00 조회 4,066회 댓글 0건

본문

첨부파일

array_formula_test.zip (8.0K)

자바 스크립트 스프레드 시트 설정

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)");