피벗 테이블에는 11가지 종류의 부분합 유형이 있습니다. 각 유형은 데이터 영역에서 값이 계산되는 방식을 정의합니다.
열거 값
설명
average (Average)
값의 평균
count (Count)
데이터 값의 개수. Count 통합 함수는 COUNTA 워크시트 함수와 동일하게 작동합니다.
countNums (CountNums)
숫자 데이터 값의 개수. Count Nums 통합 함수는 COUNT 워크시트 함수와 동일하게 작동합니다.
max (Maximum)
가장 큰 값
min (Minimum)
가장 작은 값.
product (Product)
여러 값의 곱
stdDev (StdDev)
샘플이 전체 모집단의 하위 집합인 모집단의 표준 편차에 대한 예측값
stdDevp (StdDevP)
모집단이 요약되는 모든 데이터인 모집단의 표준 편차
sum (Sum)
값의 합계
var (Variance)
샘플이 전체 모집단의 하위 집합인 모집단의 분산에 대한 예측값
varp (VarP)
모집단이 요약되는 모든 데이터인 모집단의 분산
여러 값 필드에서 ∑Value 필드가 열 영역에 자동으로 추가됩니다.
∑Value 필드는 피벗 테이블의 행/열 헤더에서 데이터 분포를 정의하는 행/열 필드로 작동합니다.
사용자가 아래 API로 피벗 테이블 필드의 SubTotalType을 가져오거나 설정할 수 있습니다.
subTotalType을 설정하기 전에 사용자는 현재 피벗 테이블에 이 필드를 추가해야 합니다. 예를 들어 다음을 수행합니다.
지원되는 모든 부분합 유형이 아래와 같이 열거됩니다.
사용자가 ∑Value의 위치를 가져오거나 설정할 수 있습니다.
사용자는 단일 필드의 부분합 위치(상단/하단)를 가져오거나 설정할 수 있습니다.
개별 필드에 대한 소계를 표시할지 여부를 가져오거나 설정할 수 있습니다.
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
<gc-worksheet> </gc-worksheet>
<gc-worksheet> </gc-worksheet>
</gc-spread-sheets>
<div class="options-container">
<label><b>Settings</b> </label>
<hr />
<div class="option-row">
Change the subtotal type for the <b>Sum of Total</b> fields (col D-G):
<select id="subtotalType" @change="switchSubtotalType($event)">
<option value="0">average (Average)</option>
<option value="1">count (Count)</option>
<option value="2">countNums (CountNums)</option>
<option value="3">max (Maximum)</option>
<option value="4">min (Minimum)</option>
<option value="5">product (Product)</option>
<option value="6">stdDev (StdDev)</option>
<option value="7">stdDevp (StdDevP)</option>
<option value="8" selected>sum (Sum)</option>
<option value="9">var (Variance)</option>
<option value="10">varp (VarP)</option>
</select>
</div>
<div class="option-row">
Changes the subtotal type for the <b>Average of total</b> fields (col H-M):
<select id="subtotalType2" @change="switchSubtotalType2($event)">
<option value="0" selected>average (Average)</option>
<option value="1">count (Count)</option>
<option value="2">countNums (CountNums)</option>
<option value="3">max (Maximum)</option>
<option value="4">min (Minimum)</option>
<option value="5">product (Product)</option>
<option value="6">stdDev (StdDev)</option>
<option value="7">stdDevp (StdDevP)</option>
<option value="8">sum (Sum)</option>
<option value="9">var (Variance)</option>
<option value="10">varp (VarP)</option>
</select>
</div>
<div class="option-row">
Changes the data position.
<div class="option-item">
Position Type:
<select id="positionType" @change="switchPositionType($event)">
<option value="0">row</option>
<option value="1" selected>col</option>
</select>
</div>
<div class="option-item">
Position Index:
<input type="number" value="0" id="positionIndex" min="0" max="2" @change="updatePositionIndex($event)" />
</div>
</div>
<div class="option-row">
Change pivotTable subtotals position:
<div class="option-item">
Subtotals Position:
<select id="pt_subtotalsPosition" @change="updatePtSubtotalPosition($event)">
<option value="0">none</option>
<option value="1">top</option>
<option value="2" selected>bottom</option>
</select>
</div>
</div>
<div class="option-row">
Change subtotals position for the <b>Cars</b> fields:
<div class="option-item">
Subtotals Position:
<select id="field_subtotalsPosition" @change="updateFieldSubtotalPosition($event)">
<option value="1">top</option>
<option value="2" selected>bottom</option>
</select>
</div>
</div>
<div class="option-row">
Sets whether the <b>Cars</b> field displays subtotals:
<div class="option-item">
Subtotal Visible:
<select id="field_subtotalsVisible" @change="updateFieldSubtotalVisible($event)">
<option value="0">none</option>
<option value="1" selected>show</option>
</select>
</div>
</div>
</div>
</div>
</template>
<script setup>
import GC from "@mescius/spread-sheets";
import "@mescius/spread-sheets-shapes";
import "@mescius/spread-sheets-vue";
import { shallowRef } from "vue";
import "@mescius/spread-sheets-pivot-addon";
import '@mescius/spread-sheets-resources-ko';
GC.Spread.Common.CultureManager.culture("ko-kr");
const SubtotalType = [
"Average of total",
"Count of total",
"Count numbers of total",
"Max of total",
"Min of total",
"Product of total",
"StdDev of total",
"StdDevp of total",
"Sum of total",
"Varr of total",
"Varp of total",
];
let spreadRef = shallowRef(null);
let ptRef = shallowRef(null);
let currentFieldNameRef = shallowRef("Sum of total");
let positionTypeRef = shallowRef(1);
let positionIndexRef = shallowRef(0);
let pt_subtotalPositionRef = shallowRef(2);
let field_subtotalPositionRef = shallowRef(2);
let field_subtotalsVisibleRef = shallowRef(1);
function initSpread (spread) {
spreadRef.value = spread;
initSheets(spread);
let pivotLayoutSheet = spread.getSheet(0);
let pt = initPivotTable(pivotLayoutSheet);
ptRef.value = pt;
}
function initSheets (spread) {
spread.suspendPaint();
let sheet = spread.getSheet(1);
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("tableSales", 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, pivotSales);
let sheet0 = spread.getSheet(0);
sheet0.name("PivotLayout");
sheet0.setColumnCount(50);
spread.resumePaint();
}
function initPivotTable (sheet) {
let myPivotTable = sheet.pivotTables.add(
"myPivotTable",
"tableSales",
1,
1,
GC.Spread.Pivot.PivotTableLayoutType.outline,
GC.Spread.Pivot.PivotTableThemes.light8
);
myPivotTable.suspendLayout();
myPivotTable.options.showRowHeader = true;
myPivotTable.options.showColumnHeader = true;
myPivotTable.add(
"salesperson",
"Salesperson",
GC.Spread.Pivot.PivotTableFieldType.rowField
);
myPivotTable.add(
"car",
"Cars",
GC.Spread.Pivot.PivotTableFieldType.rowField
);
myPivotTable.add("quantity", "Quantity", GC.Spread.Pivot.PivotTableFieldType.rowField);
let groupInfo = {
originFieldName: "date",
dateGroups: [{
by: GC.Pivot.DateGroupType.quarters
}],
};
myPivotTable.group(groupInfo);
myPivotTable.add(
"분기 (date)",
"분기 (date)",
GC.Spread.Pivot.PivotTableFieldType.columnField
);
myPivotTable.add(
"total",
currentFieldNameRef.value,
GC.Spread.Pivot.PivotTableFieldType.valueField
);
myPivotTable.subTotalType(
currentFieldNameRef.value,
GC.Pivot.SubtotalType.sum
);
myPivotTable.add(
"total",
"Average of total",
GC.Spread.Pivot.PivotTableFieldType.valueField,
GC.Pivot.SubtotalType.average
);
myPivotTable.resumeLayout();
myPivotTable.autoFitColumn();
return myPivotTable;
}
function getUniqueName(pt, name, index = 0) {
let realName = index === 0 ? name : name + (index + "");
if (pt.getField(realName)) {
return getUniqueName(pt, name, index + 1);
} else {
return realName;
}
}
function switchSubtotalType (e) {
let pt = ptRef.value;
if (pt) {
let type = parseInt(e.target.value, 10);
let newFieldName = SubtotalType[type];
let currentFieldName = currentFieldNameRef.value;
pt.subTotalType(currentFieldName, type);
newFieldName = getUniqueName(pt, newFieldName);
pt.updateFieldName(currentFieldName, newFieldName);
pt.autoFitColumn();
currentFieldNameRef.value = newFieldName;
}
}
function switchSubtotalType2(e) {
let pt = ptRef.value;
if (pt) {
let type = parseInt(e.target.value, 10);
pt.subTotalType("Average of total", type);
pt.autoFitColumn();
pt.refresh();
}
}
function switchPositionType(e) {
let pt = ptRef.value;
if (pt) {
let positionType = parseInt(e.target.value, 10);
positionTypeRef.value = positionType;
pt.dataPosition(positionType, positionIndexRef.value);
}
}
function updatePositionIndex(e) {
let pt = ptRef.value;
if (pt) {
let positionIndex = parseInt(e.target.value, 10);
positionIndexRef.value = positionIndex;
pt.dataPosition(positionTypeRef.value, positionIndex);
}
}
function updatePtSubtotalPosition(e) {
let pt = ptRef.value;
if (pt) {
var subtotalPosition = parseInt(e.target.value, 10);
pt_subtotalPositionRef.value = subtotalPosition;
pt.options.subtotalsPosition = subtotalPosition;
}
}
function updateFieldSubtotalPosition(e) {
let pt = ptRef.value;
if (pt) {
var subtotalPosition = parseInt(e.target.value, 10);
field_subtotalPositionRef.value = subtotalPosition;
pt.subtotalPosition("Cars", subtotalPosition);
}
}
function updateFieldSubtotalVisible(e) {
let pt = ptRef.value;
if (pt) {
var subtotalVisible = parseInt(e.target.value, 10);
field_subtotalsVisibleRef.value = subtotalVisible;
pt.subtotalVisible("Cars", !!subtotalVisible);
}
}
</script>
<style scoped>
.sample-tutorial {
position: relative;
height: 100%;
}
.sample-spreadsheets {
width: calc(100% - 220px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 220px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
scrollbar-width: none;
-ms-overflow-style: none;
}
.options-container::-webkit-scrollbar {
display: none;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
.option-item {
margin-top: 5px;
}
.option-item #positionType {
float: right;
width: 80px;
}
.option-item #positionIndex {
float: right;
width: 72px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
#app {
height: 100%;
}
</style>
<!DOCTYPE html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>SpreadJS VUE</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/ko/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/spread/source/data/pivot-data.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ko/vue3/node_modules/systemjs/dist/system.src.js"></script>
<script src="./systemjs.config.js"></script>
<script src="./compiler.js" type="module"></script>
<script>
var System = SystemJS;
System.import("./src/app.js");
System.import('$DEMOROOT$/ko/lib/vue3/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
(function (global) {
SystemJS.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
packageConfigPaths: [
'../node_modules/*/package.json',
"../node_modules/@mescius/*/package.json",
"../node_modules/@babel/*/package.json",
"../node_modules/@vue/*/package.json"
],
map: {
'vue': "npm:vue/dist/vue.esm-browser.js",
'tiny-emitter': 'npm:tiny-emitter/index.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
"systemjs-babel-build": "npm:systemjs-plugin-babel/systemjs-babel-browser.js",
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-resources-ko': 'npm:@mescius/spread-sheets-resources-ko/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js',
'@mescius/spread-sheets-pivot-addon': 'npm:@mescius/spread-sheets-pivot-addon/index.js',
'@mescius/spread-sheets-shapes': 'npm:@mescius/spread-sheets-shapes/index.js'
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);
