反復計算
SpreadJSは、Excelと同様に、反復計算（循環参照とも呼ばれる）をサポートします。反復計算は、特定の数値の条件が満たされるまで、繰り返し行われるワークシートの再計算です。これは、セル内の数式がスプレッドシート内の自身のセルを直接的または間接的に参照して、特定の結果が見つかるまで数式を再計算する場合に使用されます。
iterativeCalculation プロパティを使用して、ワークシートで反復計算を有効にできます。 iterativeCalculationMaximumIterations プロパティを設定して、数式を再計算する回数を指定することができます。また、 iterativeCalculationMaximumChange プロパティを設定することで、2つの計算値間の最大変更量を指定することができます。
反復計算を有効にすると、循環参照は、すべての値の変更がIterativeCalculationMaximumChangeより小さくなるか、反復カウントがIterativeCalculationMaximumIterationsになるまで反復計算されます。ただし、反復計算を無効にすると、すべての循環参照のセル値がゼロになり、それによって参照される他のセルの値もゼロになります。
使用例
反復計算は、次のように使用できます。
投資の将来価値を計算する
例えば、顧客は5000円を現金預金（CD）口座に投資して、月利4.75％の利益を受け取りたいとします。2年後の総現金を計算するために、毎月の元本額を計算し、前月に利息を追加します。ここで、iterativeCalculationプロパティをTrueに設定して反復計算を有効にし、最大反復回数を24に設定することで、24か月（2年）後の利息、つまり25か月目の現金の合計値を計算します。
次のサンプルコードは、上記の使用例を示します。
<script>
$(document).ready(function () {
// Spreadを初期化します。
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
// アクティブシートを取得します。
var sheet = spread.getSheet(0);
// 列幅を設定します。
sheet.setColumnWidth(0, 30);
sheet.setColumnWidth(1, 110);
sheet.setColumnWidth(2, 130);
sheet.setColumnWidth(3, 30);
sheet.setColumnWidth(4, 30);
sheet.setColumnWidth(5, 230);
sheet.setColumnWidth(6, 130);
// iterativeCalculationを有効にします。
spread.options.iterativeCalculation = true;
spread.options.iterativeCalculationMaximumIterations = 24;
spread.options.iterativeCalculationMaximumChange = 0.1;
// テキストと値を設定します。
sheet.setText(1, 1, "初期現金");
sheet.setValue(1, 2, 5000);
sheet.setText(2, 1, "利子");
sheet.setValue(2, 2, 0.0475);
sheet.setText(4, 1, "年／月");
sheet.setText(4, 2, "総現金");
// 配列を作成します。
var items = [
"2019年1月", "2019年2月", "2019年3月", "2019年4月", "2019年5月", "2019年6月", "2019年7月", "2019年8月", "2019年9月", "2019年10月", "2019年11月", "2019年12月",
"2020年1月", "2020年2月", "2020年3月", "2020年4月", "2020年5月", "2020年6月", "2020年7月", "2020年8月", "2020年9月", "2020年10月", "2020年11月", "2020年12月", "2021年1月"
];
// 配列を設定します。
sheet.setArray(5, 1, items);
// 数式を設定します。
sheet.setFormula(5, 2, '=C2*(1+C3)');
sheet.setFormula(6, 2, '=C6*(1+C3)');
for (var i = 6; i < 30; i++) {
sheet.setFormula(i, 2, '=C' + i + '*(1+C3)');
}
// セル範囲にスタイルを追加するためにテーブルを作成します。
sheet.tables.add("Table1", 4, 1, 26, 2, GC.Spread.Sheets.Tables.TableThemes.medium6);
// 値と数式を設定します。
sheet.setText(1, 5, "初期現金");
sheet.setValue(1, 6, 5000);
sheet.setText(2, 5, "利子");
sheet.setValue(2, 6, 0.0475);
sheet.setText(3, 5, "年後の総現金（2021年1月）");
sheet.setFormula(3, 6, '=G2*(1+G3)');
// この数式は循環参照を生成します。
sheet.setFormula(1, 6, '=G4');
// フォーマッタを設定します。
sheet.setFormatter(1, 2, '¥#,##0.00');
sheet.setFormatter(2, 2, '0.00%');
sheet.setFormatter(1, 6, '¥#,##0.00');
sheet.setFormatter(2, 6, '0.00%');
sheet.setFormatter(3, 6, '¥#,##0.00');
sheet.setFormatter(5, 2, '¥#,##0.00');
for (var i = 6; i < 30; i++) {
sheet.setFormatter(i, 2, '¥#,##0.00');
}
// セル範囲の境界線を設定します。
sheet.getRange("B2:C3", GC.Spread.Sheets.SheetArea.viewport).setBorder(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin), { all: true }, 3);
sheet.getRange("F2:G4", GC.Spread.Sheets.SheetArea.viewport).setBorder(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin), { all: true }, 3);
sheet.getRange("B5:C30", GC.Spread.Sheets.SheetArea.viewport).setBorder(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin), { all: true }, 3);
// セル範囲の背景色を設定します。
sheet.getRange("C2:C3").backColor("#D9E3F2");
sheet.getRange("G2:G4").backColor("#D9E3F2");
});
</script>セルにタイムスタンプを追加する
反復計算を使用して、ワークシートにタイムスタンプを設定できます。たとえば、タスクのリストを管理していて、各タスクの完了時に隣接するセルに日付と時刻を追加したいとします。 iterativeCalculation プロパティをTrueに設定して、反復計算を有効にします。また、 iterativeCalculationMaximumIterations プロパティを24に設定し、数式を実行する最大回数を指定します。反復計算を使用することで、「従業員名」列の新しい行に新しい項目を入力するたびに、タイムスタンプが23行目に達するまで「タイムスタンプ」列で自動的に更新されます。
次のサンプルコードは、上記の使用例を示します。
<script>
$(document).ready(function () {
// Spreadを初期化します。
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
// アクティブシートを取得します。
var sheet = spread.getSheet(0);
sheet.suspendPaint();
// 列幅を設定します。
sheet.setColumnWidth(0, 200);
sheet.setColumnWidth(1, 200);
// iterativeCalculationを有効にします。
spread.options.iterativeCalculation = true;
spread.options.iterativeCalculationMaximumIterations = 24;
spread.options.iterativeCalculationMaximumChange = 0.1;
// セル範囲の背景色を設定します。
sheet.getRange("A1:B1").backColor("#D9E3F2");
// セルにテキストを設定します。
sheet.setText(0, 0, "従業員名");
sheet.setText(0, 1, "タイムスタンプ");
for (var i = 2; i < spread.options.iterativeCalculationMaximumIterations; i++) {
sheet.setFormula(i - 1, 1, '=IF(A' + i + '<>"",IF(B' + i + '<>"",B' + i + ',NOW()),"")');
sheet.setFormatter(i - 1, 1, 'yyyy年mm月dd日 hh:mm:ss');
}
// サンプルデータ
sheet.setText(1, 0, "Andy");
var style = new GC.Spread.Sheets.Style();
style.font = "bold 12px Arial";
style.foreColor = "white";
style.backColor = "#5B9BD5";
style.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
style.vAlign = GC.Spread.Sheets.VerticalAlign.center;
for (var i = 0; i < 2; i++)
sheet.setStyle(0, i, style, GC.Spread.Sheets.SheetArea.viewport);
sheet.resumePaint();
});
</script>