피벗 테이블에는 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의 위치를 가져오거나 설정할 수 있습니다.
사용자는 단일 필드의 부분합 위치(상단/하단)를 가져오거나 설정할 수 있습니다.
개별 필드에 대한 소계를 표시할지 여부를 가져오거나 설정할 수 있습니다.
import { Component, NgModule, enableProdMode } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { SpreadSheetsModule } from '@mescius/spread-sheets-angular';
import GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-shapes";
import "@mescius/spread-sheets-pivot-addon";
import './styles.css';
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"];
@Component({
selector: 'app-component',
templateUrl: 'src/app.component.html'
})
export class AppComponent {
spread: GC.Spread.Sheets.Workbook;
pt: any;
currentFieldName: string = "Sum of total";
positionType: GC.Pivot.DataPosition = 1;
positionIndex = 0;
pt_subtotalPosition= 2;
field_subtotalPosition = 2;
field_subtotalsVisible = 1;
hostStyle = {
width: 'calc(100% - 220px)',
height: '100%',
overflow: 'hidden',
float: 'left'
};
constructor() {
}
initSpread($event: any) {
this.spread = $event.spread;
let spread = this.spread;
this.initSheets(spread);
let pivotLayoutSheet = spread.getSheet(0);
let pt = this.initPivotTable(pivotLayoutSheet);
this.pt = pt;
}
initSheets(spread: GC.Spread.Sheets.Workbook) {
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();
}
initPivotTable(sheet: GC.Spread.Sheets.Worksheet) {
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", this.currentFieldName, GC.Spread.Pivot.PivotTableFieldType.valueField);
myPivotTable.subTotalType(this.currentFieldName, 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;
}
getUniqueName (pt: any, name:string, index = 0):string {
let realName = index === 0 ? name : name + (index + "");
if (pt.getField(realName)) {
return this.getUniqueName(pt, name, index + 1);
} else {
return realName;
}
}
switchSubtotalType(event: any) {
if (this.spread) {
let type = parseInt(event.target.value, 10);
let newFieldName = SubtotalType[type];
let currentFieldName = this.currentFieldName;
let pt = this.pt;
pt.subTotalType(currentFieldName, type);
newFieldName = this.getUniqueName(pt, newFieldName);
pt.updateFieldName(currentFieldName, newFieldName);
pt.autoFitColumn();
this.currentFieldName = newFieldName;
}
}
switchSubtotalType2 (e: any) {
let pt = this.pt;
if (pt) {
let type = parseInt(e.target.value, 10);
pt.subTotalType("Average of total", type);
pt.autoFitColumn();
pt.refresh();
}
}
switchPositionType (e: any) {
let pt = this.pt;
if (pt) {
let positionType = parseInt(e.target.value, 10);
this.positionType = positionType;
pt.dataPosition(positionType, this.positionIndex);
}
}
updatePositionIndex (e: any) {
let pt = this.pt;
if (pt) {
let positionIndex = parseInt(e.target.value, 10);
this.positionIndex = positionIndex;
pt.dataPosition(this.positionType, positionIndex);
}
}
updatePtSubtotalPosition (e: any) {
let pt = this.pt;
if (pt) {
var subtotalPosition = parseInt(e.target.value, 10);
this.pt_subtotalPosition = subtotalPosition;
pt.options.subtotalsPosition = subtotalPosition;
}
};
updateFieldSubtotalPosition (e: any) {
let pt = this.pt;
if (pt) {
var subtotalPosition = parseInt(e.target.value, 10);
this.field_subtotalPosition = subtotalPosition;
pt.subtotalPosition("Cars", subtotalPosition);
}
};
updateFieldSubtotalVisible (e: any) {
let pt = this.pt;
if (pt) {
var subtotalVisible = parseInt(e.target.value, 10);
this.field_subtotalsVisible = subtotalVisible;
pt.subtotalVisible("Cars", !!subtotalVisible);
}
};
}
@NgModule({
imports: [BrowserModule, SpreadSheetsModule],
declarations: [AppComponent],
exports: [AppComponent],
bootstrap: [AppComponent]
})
export class AppModule {}
enableProdMode();
// Bootstrap application with hash style navigation and global services.
platformBrowserDynamic().bootstrapModule(AppModule);
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/ko/angular/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/spread/source/data/pivot-data.js" type="text/javascript"></script>
<!-- Polyfills -->
<script src="$DEMOROOT$/ko/angular/node_modules/core-js/client/shim.min.js"></script>
<script src="$DEMOROOT$/ko/angular/node_modules/zone.js/fesm2015/zone.min.js"></script>
<!-- SystemJS -->
<script src="$DEMOROOT$/ko/angular/node_modules/systemjs/dist/system.js"></script>
<script src="systemjs.config.js"></script>
<script>
// workaround to load 'rxjs/operators' from the rxjs bundle
System.import('rxjs').then(function (m) {
System.import('@angular/compiler');
System.set(SystemJS.resolveSync('rxjs/operators'), System.newModule(m.operators));
System.import('$DEMOROOT$/ko/lib/angular/license.ts');
System.import('./src/app.component');
});
</script>
</head>
<body>
<app-component></app-component>
</body>
</html>
<div class="sample-tutorial">
<gc-spread-sheets [hostStyle]="hostStyle" (workbookInitialized)="initSpread($event)">
<gc-worksheet>
</gc-worksheet>
<gc-worksheet>
</gc-worksheet>
</gc-spread-sheets>
<div class="options-container">
<div class="option-row">
<label><b>Settings</b>
</label></div>
<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 name="" id="field_subtotalsVisible" (change)="updateFieldSubtotalVisible($event)">
<option value="0">none</option>
<option value="1" selected>show</option>
</select>
</div>
</div>
</div>
</div>
.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;
}
(function (global) {
System.config({
transpiler: 'ts',
typescriptOptions: {
tsconfig: true
},
meta: {
'typescript': {
"exports": "ts"
},
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'core-js': 'npm:core-js/client/shim.min.js',
'zone': 'npm:zone.js/fesm2015/zone.min.js',
'rxjs': 'npm:rxjs/dist/bundles/rxjs.umd.min.js',
'@angular/core': 'npm:@angular/core/fesm2022',
'@angular/common': 'npm:@angular/common/fesm2022/common.mjs',
'@angular/compiler': 'npm:@angular/compiler/fesm2022/compiler.mjs',
'@angular/platform-browser': 'npm:@angular/platform-browser/fesm2022/platform-browser.mjs',
'@angular/platform-browser-dynamic': 'npm:@angular/platform-browser-dynamic/fesm2022/platform-browser-dynamic.mjs',
'@angular/common/http': 'npm:@angular/common/fesm2022/http.mjs',
'@angular/router': 'npm:@angular/router/fesm2022/router.mjs',
'@angular/forms': 'npm:@angular/forms/fesm2022/forms.mjs',
'jszip': 'npm:jszip/dist/jszip.min.js',
'typescript': 'npm:typescript/lib/typescript.js',
'ts': './plugin.js',
'tslib':'npm:tslib/tslib.js',
'css': 'npm:systemjs-plugin-css/css.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-shapes': 'npm:@mescius/spread-sheets-shapes/index.js',
'@mescius/spread-sheets-pivot-addon': 'npm:@mescius/spread-sheets-pivot-addon/index.js',
'@mescius/spread-sheets-angular': 'npm:@mescius/spread-sheets-angular/fesm2020/mescius-spread-sheets-angular.mjs',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'ts'
},
rxjs: {
defaultExtension: 'js'
},
"node_modules": {
defaultExtension: 'js'
},
"node_modules/@angular": {
defaultExtension: 'mjs'
},
"@mescius/spread-sheets-angular": {
defaultExtension: 'mjs'
},
'@angular/core': {
defaultExtension: 'mjs',
main: 'core.mjs'
}
}
});
})(this);
