피벗 테이블은 많은 양의 데이터를 빠르게 추출하고 분석할 수 있는 강력한 도구로, 대량의 데이터를 보다 쉽게 분석할 수 있도록 합니다. Excel 피벗 테이블을 사용하면 테이블 내에서 데이터를 “피벗”하여 데이터 테이블을 보다 의미 있는 방식으로 요약할 수 있는데, 예를 들어, 여러 제품 간에 총액을 비교하거나 정렬, 요약, 그룹화 등을 수행할 수 있습니다.

JavaScript Excel 스프레드시트인 SpreadJS v14.1.0 릴리스에는 피벗 테이블 기능이 추가되었습니다. 최신 버전 SpreadJS를 다운로드하여 사용하실 수 있습니다.

이 블로그에서는 SpreadJS의 피벗 테이블 추가 기능, 응용 프로그램, 사용 설정 및 여러 레이아웃 옵션을 사용하여 데이터를 원활하게 보고 분석하는 방법에 대해 살펴봅니다.

SpreadJS 피벗 테이블 사용 방법

• 데이터 추가

• 피벗 테이블 추가

• 피벗 테이블 사이드 패널 추가

• 피벗 테이블 섹션 설정

• 피벗 테이블 필터 및 정렬

• 피벗 테이블 축소 및 그룹화

데이터 추가

피벗 테이블의 기능을 살펴보기 전에 피벗 테이블에서 사용할 데이터 소스를 만듭니다. 피벗 테이블은 테이블 데이터를 제공하거나 기존 테이블 데이터를 사용하여 만들 수 있습니다. 이 예에서는 기존 테이블과 해당 테이블 데이터를 사용하여 새로운 피벗 테이블을 만들어 보겠습니다.

먼저, setArray 메서드를 사용하여 배열에서 SpreadJS 인스턴스까지 데이터를 설정하는 간단한 함수를 만든 다음 설정한 데이터 범위에 테이블을 추가합니다. 시트가 초기화되면 이 함수를 실행합니다. 다음은 이후 단계에서 사용할 샘플 데이터의 배열입니다.

// Table Data
let pivotSales = [
    ["OrderDate", "Region","Rep","Item","Units","UnitCost", "Total"],
    ["1/6/2019", "East", "Jones", "Pencil", 95, 1.99, 189.05],
    ["1/6/2019", "Central", "Kivell", "Desk", 2, 125.00, 250.00],
    ["1/6/2019", "West", "Sorvino", "Desk", 4 ,255.00, 1020.00],
    ["1/6/2019", "East", "Jardine", "Desk", 2 ,100.00, 200.00],
    ["1/23/2019","Central","Kivell","Binder",50,19.99, 999.50],
    ["2/9/2019","Central","Jardine","Pencil", 36,4.99,179.64],
    ["2/26/2019","Central","Gill","Pen", 27,19.99,539.73],
    ["3/15/2019","West","Sorvino","Pencil",56,2.99,167.44],
    ["4/1/2019","East","Jones","Binder",60,4.99,299.40],
];

다음으로, 배열을 시트로 설정하고 데이터 범위에서 테이블을 추가하는 간단한 함수인 getDataSource를 만듭니다(1a). 이 함수를 실행하고, 데이터를 설정한 다음 통합 문서의 두 번째 시트로 설정할 테이블을 추가합니다(1b).

function initSpread(spread) {
  spread.suspendPaint();
  let sheet = spread.getActiveSheet();
  let sheet2 = spread.getSheet(1);
​
// 1b: Get the data sources table name
  let tableName = getDataSource(sheet2, pivotSales);
  spread.resumePaint();
}
​
// 1a: Set data array to sheet and add table and return the table name
function getDataSource(sheet, tableSource){
  sheet.name("DataSource");
  sheet.setRowCount(700);
  sheet.setColumnWidth(5, 150);
  let table = sheet.tables.add('table', 0, 0, 11, 6);
  sheet.setArray(0, 0, tableSource);
  return table.name();
}

이제 데이터가 DataSource 라는 통합 문서의 두 번째 워크시트에 있는 테이블에 표시됩니다.

피벗 테이블 추가

이제 SpreadJS 인스턴스 내 테이블에 데이터가 있으므로, 데이터 테이블의 이름을 피벗 테이블 생성자의 소유자 통합 문서로 사용하여 피벗 테이블을 추가하고(2a), 피벗 테이블 옵션, 테마, 레이아웃을 적용하여 테이블의 모양 스타일을 지정합니다(2b).

• 옵션: SpreadJS 피벗 테이블 스타일 옵션에는 밴드 행 또는 열 설정과 행 또는 열 헤더 표시 여부 활성화가 포함됩니다.
• 테마: 선호도에 따라 다양한 피벗 테이블 테마를 적용하여 테이블의 스타일과 테마를 변경합니다.
• 레이아웃: 피벗 테이블에 적용할 수 있는 3가지 PivotTableLayoutType이 있습니다. 이 예에서는 아웃 라인 레이아웃을 사용합니다.

function initPivotTable(sheet, tableName){
  sheet.name("Basic PivotTable");
  sheet.setRowCount(100);
  sheet.setColumnWidth(0, 20);
  // 2b:Set the pivot tables options, theme and layout
  let options = { bandRows:true, bandColumns:true };
  let theme = GC.Spread.Pivot.PivotTableThemes.medium9;
  let layout = GC.Spread.Pivot.PivotTableLayoutType.outline;
  // 2a: Add a pivot table
  let pivotTable = sheet.pivotTables.add("PivotTable", tableName, 1, 1, layout, theme, options);
}

아직 섹션에 입력할 수 있는 데이터를 만들지 않았기 때문에 SpreadJS 인스턴스에 있는 피벗 테이블은 비어 있습니다.

피벗 테이블 사이드 패널에서 데이터를 선택하여 지정된 섹션으로 드래그 하거나 JavaScript 스프레드시트 API를 사용하여 코드에서 피벗 테이블 섹션을 설정하는 두 가지 방법으로 피벗 테이블에서 데이터를 사용할 수 있습니다.

피벗 테이블 사이드 패널 추가

SpreadJS에서는 피벗 테이블 사이드 패널을 지원하므로 사용자가 드래그앤드롭하여 설정된 또는 비어 있는 피벗 테이블에 필터링을 적용할 수 있습니다. 현재 샘플에 사이드 패널을 적용하려면 DIV 요소를 추가하여 다음과 같이 HTML 파일에 사이드 패널을 포함할 수 있습니다.

<div class="sample-tutorial">
      <div id="ss" class="sample-spreadsheets"></div>
      <div class="sample-panel">
          <div id="panel"></div>
      </div>
</div>

initPivotTable에서 ID가 panel인 DIV에 PivotPanel을 적용합니다.

// 3: PivotTable side panel
var panel = new GC.Spread.Pivot.PivotPanel("PivotTable", pivotTable, document.getElementById("panel"));

이제 SpreadJS 인스턴스에는 PivotPanel이 표시되어 데이터 보기를 조작할 수 있습니다. 이는 SpreadJS 인스턴스 내에서 데이터 보기를 설정할 수 있는 한 가지 방법입니다. 또한 피벗 테이블 섹션을 설정하여 코드를 통해 적용할 수 있습니다.

피벗 테이블 섹션 설정:

빈 피벗 테이블을 사용하면 SpreadJS의 API를 통해 피벗 테이블 섹션인 행 영역, 열 영역, 값 영역, 필터 영역을 설정하여 데이터를 사용하도록 만들 수 있습니다.

이러한 섹션을 설정한 방법에 따라 피벗 테이블이 표시됩니다. 행 또는 열 필드를 설정하면 해당 필드는 테이블의 행 또는 열 머리글이 됩니다. 예를 들어, SpreadJS 피벗 테이블 섹션을 설정하는 initPivotTable이라는 함수를 만들어 이제 선택한 데이터가 피벗 테이블에서 사용할 수 있도록 만듭니다(4).

function initPivotTable(sheet, tableName){
  sheet.name("Basic PivotTable");
  sheet.setRowCount(100);
  sheet.setColumnWidth(0, 20);
​
  // 2b:Set the pivot tables options, theme and layout
  let options = { bandRows:true, bandColumns:true };
  let theme = GC.Spread.Pivot.PivotTableThemes.medium9;
  let layout = GC.Spread.Pivot.PivotTableLayoutType.outline;
  // 2a: Add a pivot table
  let pivotTable = sheet.pivotTables.add("PivotTable", tableName, 1, 1, layout, theme, options);
​
​
​
  // 4: Set the PivotTable Sections:
  // rowField
  pivotTable.add("Rep", "Rep", GC.Spread.Pivot.PivotTableFieldType.rowField);
  pivotTable.add("OrderDate", "Order Date", GC.Spread.Pivot.PivotTableFieldType.rowField);
  // columnField
  pivotTable.add("Item", "Item", GC.Spread.Pivot.PivotTableFieldType.columnField);
  // valueField
  pivotTable.add("Total", "Sum of Sales", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
  // filterArea
  pivotTable.add("Region", "Region", GC.Spread.Pivot.PivotTableFieldType.filterField);
}

이 섹션들이 설정되면 테이블이 브라우저에 표시되고 PivotTable과 상호 작용하여 데이터를 드릴다운할 수 있습니다.

데이터 필터 및 정렬 적용

이 섹션에서는 전체 피벗 테이블에 대한 필터 영역을 설정하는 방법에 대해 다루지만, 필터링을 드릴다운 및 적용할 수 있는 여러가지 방법이 있습니다. SpreadJS에서 사용자는 레이블 필터, 값 필터, 수동 필터를 사용하여 여러 필터링 작업을 적용하고 수행할 수 있습니다. 이 섹션에서는 PivotTable Fields 패널을 추가하여 데이터와 상호 작용하는 방법과 여러 필터를 사용하는 방법을 보여줍니다. SpreadJS의 피벗 테이블을 사용할 때 여러 필터를 사용하려면 디자이너에서 “여러 필터 허용”을 선택해야 합니다.

또한 아래 코드 설정을 사용할 수 있습니다.

pivottable.options.allowMultipleFiltersPerField = true;

수동 필터:

수동 필터를 사용하면 여러 행 또는 열 필드를 필터링할 수 있습니다. SpreadJS에서 행 필드 또는 열 필드에서 항목을 하나 이상 숨기려고 할 수 있습니다. 이렇게 하려면 행 또는 열 레이블의 드롭다운 화살표를 클릭한 다음 제거하려는 항목의 체크 표시를 제거하면 됩니다.

레이블 필터:

레이블 필터는 보려는 데이터를 지정할 수 있는 또 다른 방법입니다. 행/열 필드에서 항목을 더 많이 숨기려면 레이블 필터를 적용합니다. "보다 작음" 레이블 필터 연산자를 사용하는 아래 예에서는 1월에서 6월까지의 월만 표시되도록 월 열을 필터링합니다.

값 필터:

값 필터를 사용하면 행 필드의 값에 필터링을 적용하여 특정 데이터를 드릴다운할 수 있습니다. 아래 예에서는 비용을 기준으로 상위 3개 항목만 표시하도록 값 필터를 적용하는 방법을 보여줍니다.

정렬:

SpreadJS 피벗 테이블은 필드에 필터가 이미 적용된 경우에도 레이블과 값을 기준으로 정렬할 수 있습니다. 레이블을 기준으로 정렬하는 경우 SpreadJS의 JavaScript API를 사용하여 정렬을 지정하거나 필터 정렬 대화 상자를 사용할 수 있습니다. SpreadJS의 API를 사용하여 SortType을 오름차순, 내림차순 또는 사용자 정의로 설정할 수 있습니다. 또한 정렬/필터 대화 상자를 선택한 다음 Excel과 마찬가지로 A-Z 또는 Z-A 옵션을 사용하여 오름차순 또는 내림차순을 선택하여 행 영역의 필드에 정렬을 적용할 수 있습니다.

값을 정렬할 때 SpreadJS에서는 오름차순 또는 내림차순으로 정렬하는 옵션을 제공합니다.

피벗 테이블 축소 및 그룹화

SpreadJS에서는 데이터를 더 쉽게 볼 수 있도록 피벗 테이블 그룹화 및 축소를 지원합니다. 사용자는 collapse 메서드를 사용하여 필드 이름별로 피벗 테이블의 지정된 필드에 대한 축소 정보를 설정할 수 있습니다. 또한 날짜, 숫자 및 텍스트 필드를 그룹화할 수 있습니다. 아래에서는 필드를 축소하는 방법과 group 및 ungroup 메서드를 사용하여 SpreadJS에서 지원하는 여러 그룹화 및 그룹 해제 기술을 분류하는 방법을 보여줍니다.

축소:

필드를 축소하려면 SpreadJS의 collapse 메서드를 사용하고 대상 필드의 이름과 축소되는 항목의 이름을 지정합니다. 아래 예에서는 축소된 다양한 영역을 보여줍니다.

spread.suspendPaint();
      let isCollapse = false;
      if(e.target.checked){
          isCollapse = true;
      }
      pivotTable.collapse("region","Africa",isCollapse);
      pivotTable.collapse("region","Asia",isCollapse);
      pivotTable.collapse("region","Australia",isCollapse);
      pivotTable.collapse("region","Europe",isCollapse);
      pivotTable.collapse("region","North America",isCollapse);
      pivotTable.collapse("region","South America",isCollapse);
      spread.resumePaint();
  });

선택하면 지정된 필드가 모두 축소되어 데이터를 더 쉽게 볼 수 있습니다.

날짜 그룹화:

SpreadJS에서 날짜를 그룹화할 때 시간, 일, 분기, 연도 등을 기준으로 DateGroupType을 지정할 수 있습니다. 예를 들어, 분기 및 연도별로 날짜를 그룹화하려는 경우 dateGroups를 사용하여 새 groupInfo를 다음과 같이 초기화할 수 있습니다.

let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }, { by: GC.Pivot.DateGroupType.years }] };
pivotTable.group(groupInfo);
pivotTable.add("Years", "Years", GC.Spread.Pivot.PivotTableFieldType.columnField);

날짜 그룹을 해제할 때 원본 필드가 같은 모든 날짜가 제거되고 원본 필드는 일반 날짜로 돌아갑니다. 

텍스트 그룹화:

나머지 데이터를 구분하기 위해 특정 텍스트 항목을 그룹화할 수 있습니다. 이 작업을 수행하기 위해 사용자는 텍스트 값 그룹을 만들 수 있습니다. 텍스트 그룹을 추가하면 새 필드가 생성됩니다. 이 필드의 이름은 groupInfo.textGroup에서 지정된 fieldName입니다. 새 필드를 그룹 필드로 사용합니다. 이 예에서는 열 필드로 추가되는 새 그룹을 보여줍니다.

let groupInfo = { originFieldName: "country",textGroup: {
  fieldName:"country1",
  groupItems:{
      group1:["Spain","China","Australia","Egypt"],
      group2:["Germany","Republic of Korea","South Africa"]
  }
} };
pivotTable.group(groupInfo);
pivotTable.add("country1", "country1", GC.Spread.Pivot.PivotTableFieldType.columnField);

텍스트 그룹을 해제하면 해제할 때 필드가 제거됩니다.

숫자 그룹화: 

SpreadJS에서 숫자를 그룹화하면 숫자 그룹이 추가됩니다. groupInfo에 지정된 원본 필드는 사용할 그룹 필드로 변경됩니다. 숫자 그룹화는 다음과 같이 나타납니다.

let groupInfo = { originFieldName: "id",numberGroup: { by: 100 } };
pivotTable.group(groupInfo);

숫자 그룹을 해제하면 필드가 일반 숫자 필드로 돌아갑니다.

피벗 테이블은 v14.1.0 릴리스에 포함된 SpreadJS의 편리한 대화형 추가 기능입니다. 피벗 테이블은 로우 데이터를 계산, 요약 및 시각화하는 최종 사용자의 역량을 개선하여 비교, 패턴 및 추세를 파악하는 데 도움이 됩니다.

SpreadJS 평가판에 피벗 테이블 추가 기능 컴포넌트가 포함되어 있으므로 지금 바로 최신 버전의 SpreadJS를 다운로드 하여 기능을 사용해 보세요!