計算の種類 | 機能紹介 | SpreadJS（スプレッドJS）チュートリアルデモアプリケーション

ピボットテーブルは、さまざまな計算の種類を提供して、計算の値をすばやく表示します。 14種類の計算の種類を利用できます。計算の種類列挙型の値必要なプロパティ計算なし normal showDataAs 総計に対する比率 percentOfTotal showDataAs 列集計に対する比率 percentOfRow showDataAs 行集計に対する比率 percentOfCol showDataAs 基準値に対する比率 percent showDataAs, baseFieldName, baseFieldItemType, baseFieldItem? 親行集計に対する比率 percentOfParentRow showDataAs 親列集計に対する比率 percentOfParentCol showDataAs 親集計に対する比率 percentOfParent showDataAs, baseFieldName 基準値との差分 difference showDataAs, baseFieldName, baseFieldItemType, baseFieldItem? 基準値との差分の比率 percentDiff showDataAs, baseFieldName, baseFieldItemType, baseFieldItem? 累計 runTotal showDataAs, baseFieldName 比率の累計 percentOfRunningTotal showDataAs, baseFieldName 昇順での順位 rankAscending showDataAs, baseFieldName 降順での順位 rankDescending showDataAs, baseFieldName 指数 (インデックス) index showDataAs サンプル：

window.onload = function () {
let spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 });
initSpread(spread);
};
function initSpread(spread) {
spread.suspendPaint();
let sheet1 = spread.getSheet(0);
let sheet2 = spread.getSheet(1);
let tableName = getDataSource(sheet2, pivotSales);
let pivotTable = initPivotTable(sheet1, tableName);
bindEvent(pivotTable, spread);
spread.resumePaint();
}
function getDataSource(sheet, tableSource) {
sheet.name("DataSource");
sheet.setRowCount(117);
sheet.setColumnWidth(0, 120);
sheet.getCell(-1, 0).formatter("YYYY-mm-DD");
sheet.getRange(-1,4,0,2).formatter("$ #,##0");
let table = sheet.tables.add('table', 0, 0, 117, 6);
for(let i=2;i<=117;i++)
{
sheet.setFormula(i-1,5,'=D'+i+'*E'+i)
}
table.style(GC.Spread.Sheets.Tables.TableThemes["none"]);
sheet.setArray(0, 0, tableSource);
return table.name();
}
function initPivotTable(sheet, source) {
sheet.name("ShowDataAs");
sheet.setRowCount(1000);
let option = {
showRowHeader:true,
showColumnHeader:true,
bandRows:true,
bandColumns:true
};
let pivotTable = sheet.pivotTables.add("pivotTable", source, 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium8,option);
pivotTable.suspendLayout();
pivotTable.add("salesperson", "Salesperson", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("car", "Cars", GC.Spread.Pivot.PivotTableFieldType.rowField);
let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }] };
pivotTable.group(groupInfo);
pivotTable.add("四半期 (date)", "四半期 (date)",GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("total", "Totals", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.resumeLayout();
pivotTable.autoFitColumn();
return pivotTable;
}
function applyShowDataAsStyle(pivotTable, fieldName, showValueAsType) {
let style, valueFieldArea = {
dataOnly: true,
references: [{
fieldName: "値",
items: [fieldName]
}]
};
style = pivotTable.getStyle(valueFieldArea);
if (!style) {
style = new GC.Spread.Sheets.Style();
}
let needApplyStyle = [GC.Pivot.PivotShowDataAs.percentOfTotal, GC.Pivot.PivotShowDataAs.percentOfRow, GC.Pivot.PivotShowDataAs.percentOfCol, GC.Pivot.PivotShowDataAs.percent, GC.Pivot.PivotShowDataAs.percentOfParentRow,
GC.Pivot.PivotShowDataAs.percentOfParentCol, GC.Pivot.PivotShowDataAs.percentOfParent, GC.Pivot.PivotShowDataAs.percentDiff, GC.Pivot.PivotShowDataAs.percentOfRunningTotal].indexOf(showValueAsType) > -1;
if (needApplyStyle) {
style.formatter = "0.00%";
} else {
delete style.formatter;
}
pivotTable.setStyle(valueFieldArea, style);
}
function bindEvent(pivotTable, spread) {
document.getElementById("showValueAs").addEventListener("change", function (event) {
let selectIndex = event.target.value;
let showValueAsBaseFieldPanel = document.getElementById("showValueAsBaseFieldPanel"),
showValueAsDialogLabel = document.getElementById("showValueAsDialogLabel"),
showValueAsBaseItemPanel = document.getElementById("showValueAsBaseItemPanel");
showValueAsBaseFieldPanel.style.display = "none";
showValueAsBaseItemPanel.style.display = "none";
showValueAsDialogLabel.innerText = "";
let showValueAsBaseItem = document.getElementById("showValueAsBaseItem");
showValueAsBaseItem.innerHTML = "";
let showValueAsBaseField = document.getElementById("showValueAsBaseField");
showValueAsBaseField.innerHTML = "";
if (["4", "7", "8", "9", "10", "11", "12", "13"].indexOf(selectIndex) > -1) {
showValueAsBaseFieldPanel.style.display = null;
let baseField = [...pivotTable.getFieldsByArea(1), ...pivotTable.getFieldsByArea(2)];
showValueAsDialogLabel.innerText = "計算: " + event.target.selectedOptions[0].text;
baseField.forEach((item, index) => {
let option = document.createElement('option');
option.value = index;
option.innerText = item.fieldName;
showValueAsBaseField.appendChild(option);
});
if (["4", "8", "9"].indexOf(selectIndex) > -1) {
showValueAsBaseItemPanel.style.display = null;
let text = showValueAsBaseField.selectedOptions[0].text;
let baseItems = pivotTable.getItemsByField(text);
baseItems.unshift("next"); baseItems.unshift("previous");
baseItems.forEach((item, index) => {
let option = document.createElement('option');
option.value = index;
option.innerText = item;
showValueAsBaseItem.appendChild(option);
});
}
}
});
document.getElementById("showValueAsBaseField").addEventListener("change", function (event) {
let text = event.target.selectedOptions[0].text;
let showValueAsBaseItem = document.getElementById("showValueAsBaseItem");
showValueAsBaseItem.innerHTML = "";
let baseItems = pivotTable.getItemsByField(text);
baseItems.unshift("next");
baseItems.unshift("previous");
baseItems.forEach((item, index) => {
let option = document.createElement('option');
option.value = index;
option.innerText = item;
showValueAsBaseItem.appendChild(option);
});
});
document.getElementById("applySetting").addEventListener('click', () => {
let showValueAsIndex = parseInt(document.getElementById("showValueAs").value, 10);
let valueFieldName = pivotTable.getFieldsByArea(3)[0].fieldName;
if ([4, 8, 9].indexOf(showValueAsIndex) > -1) {
let fileName = document.getElementById("showValueAsBaseField").selectedOptions[0].text;
let showValueAsBaseItem = document.getElementById("showValueAsBaseItem");
let baseIndex = showValueAsBaseItem.value;
let baseFieldItem = showValueAsBaseItem.selectedOptions[0].text;
if (baseIndex === "0") {
pivotTable.showDataAs(valueFieldName, { showDataAs: showValueAsIndex, baseFieldName: fileName, baseFieldItemType: 2 });
} else if (baseIndex === "1") {
pivotTable.showDataAs(valueFieldName, { showDataAs: showValueAsIndex, baseFieldName: fileName, baseFieldItemType: 1 });
} else {
pivotTable.showDataAs(valueFieldName, { showDataAs: showValueAsIndex, baseFieldName: fileName, baseFieldItemType: 0, baseFieldItem: baseFieldItem });
}
} else if ([7, 10, 11, 12, 13].indexOf(showValueAsIndex) > -1) {
let fileName = document.getElementById("showValueAsBaseField").selectedOptions[0].text;
pivotTable.showDataAs(valueFieldName, { showDataAs: showValueAsIndex, baseFieldName: fileName });
} else {
pivotTable.showDataAs(valueFieldName, { showDataAs: showValueAsIndex });
}
applyShowDataAsStyle(pivotTable, valueFieldName, showValueAsIndex);
pivotTable.refresh();
});
}

<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta name="spreadjs culture" content="ja-jp" />
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js"
type="text/javascript"></script>
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script>
<script
src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets-pivot-addon/dist/gc.spread.pivot.pivottables.min.js"
type="text/javascript"></script>
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets-resources-ja/dist/gc.spread.sheets.resources.ja.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/data.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/pivot-data.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
<div class="options-container">
<div class="option-row">
<label>計算の種類：</label>
<select id="showValueAs">
<option value='0' selected>No Calculation</option>
<option value='1'>% of Grand Total</option>
<option value='2'>% of Column Total</option>
<option value='3'>% of Row Total</option>
<option value='4'>% Of ...</option>
<option value='5'>% of Parent Row Total</option>
<option value='6'>% of Parent Column Total</option>
<option value='7'>% of Parent Total ...</option>
<option value='8'>Difference From ...</option>
<option value='9'>% Of Difference From ...</option>
<option value='10'>Running Total In ...</option>
<option value='11'>% Running Total In ...</option>
<option value='12'>Rank Smallest to Largest ...</option>
<option value='13'>Rank Largest to Smallest ...</option>
<option value='14'>Index</option>
</select>
</div>
<label style="padding: 5px; margin-top: 10px;" id="showValueAsDialogLabel"></label>
<div class="option-row" id="showValueAsBaseFieldPanel" style="display: none;">
<label id="showValueAsBaseFieldLabel">基準フィールド:</label>
<select id="showValueAsBaseField">
</select>
</div>
<div class="option-row" id="showValueAsBaseItemPanel" style="display: none;">
<label id="showValueAsBaseItemLabel">基準アイテム:</label>
<select id="showValueAsBaseItem">
</select>
</div>
<div class="option-row">
<input type="button" value="設定を適用する" id="applySetting" />
</div>
</div>
</div>
</body>
</html>

.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
p{
padding:2px 10px;
background-color:#F4F8EB;
}
input, select {
width: 100%;
padding: 4px 6px;
box-sizing: border-box;
}
label {
display:block;
margin-bottom: 6px;
}
input[type="checkbox"], input[type="radio"] {
display: inline-block;
width: auto;
}
input[type="checkbox"]+label, input[type="radio"]+label {
display: inline-block;
}
input[type="button"] {
display: block;
margin: 0 0 6px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}