이 페이지는 Cloud Translation API를 통해 번역되었습니다.

피벗 테이블

이 가이드에서는 Google Sheets API를 사용하여 피벗을 만드는 방법과 이유를 설명합니다. 만들 수 있습니다.

피벗 테이블이란 무엇인가요?

피벗 테이블을 사용하면 스프레드시트의 데이터를 자동으로 요약할 수 있습니다 데이터를 집계, 정렬, 계산 또는 평균화하는 작업이 새 테이블에 요약된 결과를 생성할 수 있습니다 피벗 테이블은 일종의 쿼리와 비교할 수 있습니다. 이 소스 데이터는 다음의 다른 위치에 있습니다. 피벗 테이블에는 처리된 데이터 보기가 표시됩니다.

예를 들어 다음과 같은 판매 데이터 세트를 생각해 보세요.

	A	B	C	D	E	F	G
1	항목 카테고리	모델 번호	비용	수량	지역	영업 담당자	배송 날짜
2	휠	W-24	20.50달러	4	서부	베스	2016년 3월 1일
3	문	D-01X	15달러	2	남부	아미르	2016년 3월 15일
4	엔진	ENG-0134	100,000원	1	북	카르멘	2016년 3월 20일
5	프레임	FR-0B1	34달러	8	동부	Hannah	2016년 3월 12일
6	패널	P-034	6달러	4	북	Devyn	2016년 4월 2일
7	패널	P-052	11.50달러	7	동부	에리크	2016년 5월 16일
8	휠	W-24	20.50달러	11	남부	Sheldon	2016년 4월 30일
9	엔진	ENG-0161	330달러	2	북	제시	2016년 7월 2일
10	문	D-01Y	29달러	6	서부	Armando	2016년 3월 13일
11	프레임	FR-0B1	34달러	9	남부	율리아나	2016년 2월 27일
12	패널	P-102	$3.00	15	서부	카르멘	2016년 4월 18일
13	패널	P-105	$8.25	13	서부	제시	2016년 6월 20일
14	엔진	ENG-0211	283달러	1	북	아미르	2016년 6월 21일
15	문	D-01X	15달러	2	서부	Armando	2016년 7월 3일
16	프레임	FR-0B1	34달러	6	남부	카르멘	7/15/2016
17	휠	W-25	$20.00	8	남부	Hannah	2016년 5월 2일
18	휠	W-11	29달러	13	동부	에리크	2016년 5월 19일
19	문	D-05	17.7달러	7	서부	베스	2016년 6월 28일
20	프레임	FR-0B1	34달러	8	북	Sheldon	2016년 3월 30일

피벗 테이블을 사용하여 각 지역에서 판매된 각 모델 번호 수를 표시하는 보고서를 만들 수 있습니다.

지역별 모델 번호 수를 보여주는 피벗 테이블의 스크린샷

이 피벗 테이블을 생성하는 데 사용된 소스 코드는 예를 참고하세요. 섹션을 참조하세요.

피벗 테이블을 스프레드시트에 넣으면 사용자가 대화형으로 Sheets UI를 사용하여 요약의 구조와 세부정보를 확인할 수 있습니다.

피벗 테이블을 사용한 작업

피벗 테이블 정의는 시트의 단일 셀과 연결됩니다. 하지만 렌더링된 모양은 프로그래매틱 방식으로 높이와 너비의 많은 셀로 표시됩니다. 단일 셀 좌표에 있습니다. 이 셀은 왼쪽 상단이 됩니다. 가로 및 세로 범위가 있는 렌더링된 피벗 테이블의 모서리 의 정의에 의해 결정됩니다.

피벗 테이블 추가하기

피벗 테이블을 추가하려면 batchUpdate 메서드 호출 Google Kubernetes Engine, updateCells 요청을 수행합니다. 이 요청을 사용하여 PivotTable 정의를 지정할 수 있습니다.

"updateCells": {
  "rows": {
    "values": [{
      "pivotTable": MyPivotTable
    },
    "start": {
      "sheetId": sheetId,
      "rowIndex": 0,
      "columnIndex": 0
    },
    "fields": "pivotTable"
  }

그러면 MyPivotTable로 설명된 피벗 테이블이 지정된 시트에 배치됩니다. A1 셀에 왼쪽 상단 모서리를 포함합니다. (피벗의 높이와 너비는 테이블은 동적입니다. 출처만 지정하면 됩니다.)

PivotTable 유형을 사용하면 지정할 수 있습니다.

소스 데이터 범위
피벗 테이블의 행이 될 데이터가 포함된 하나 이상의 필드
피벗 테이블의 데이터가 될 하나 이상의 필드
필터링 및 집계 기준
피벗 테이블 레이아웃

피벗 테이블 수정 및 삭제

피벗 테이블을 수정하거나 삭제해 달라는 명시적인 요청은 없습니다. 대신 updateCells 다른 셀 내용이 포함된 요청:

피벗 테이블을 수정하려면 수정된 PivotTable 정의를 만들고 새 피벗 테이블을 추가하는 것과 비슷합니다.
피벗 테이블을 삭제하려면 빈 값으로 셀을 업데이트하세요. 자세한 내용은 피벗 테이블 삭제하기 샘플입니다.

사용 사례

피벗 테이블의 용도는 다양합니다. 여기에는 통계 분석, ERP 애플리케이션, 재무 보고, 기타 등등 기본 피벗 테이블 사용 사례에는 다음과 같은 항목이 포함됩니다.

지역 및 분기별 총 매출
직책 및 지역별 평균 급여
제품 및 시간별 이슈 수

피벗 테이블의 응용 분야는 방대하며, 프로그래매틱 방식으로 생성하는 것이 강력합니다. 피벗을 생성할 수 있습니다. 대화형 탐색을 지원하지만 특정 상황에 맞게 조정된 테이블 다음과 같은 상황이 발생할 수 있습니다.

최근 24시간 동안의 사고 데이터 탐색
현재 선택된 계정에 해당하는 합산 데이터 조회/분석
현재 사용자에게 속한 지역의 판매 데이터 검토

예

이 예에서는 데이터 세트에서 피벗 테이블을 만들어 "모델 번호 리전' 이 페이지의 도입부에 나와 있습니다. 추가 예시를 보려면 피벗 테이블 샘플 페이지를 참고하세요.

Apps Script

sheets/api/스프레드시트_스니펫.gs

GitHub에서 보기

/**
 * Create pivot table
 * @param {string} spreadsheetId spreadsheet ID
 * @returns {*} pivot table's spreadsheet
 */
Snippets.prototype.pivotTable = (spreadsheetId) => {
  try {
    const spreadsheet = SpreadsheetApp.openById(spreadsheetId);

    // Create two sheets for our pivot table, assume we have one.
    let sheet = spreadsheet.getSheets()[0];
    sheet.copyTo(spreadsheet);

    const sourceSheetId = spreadsheet.getSheets()[0].getSheetId();
    const targetSheetId = spreadsheet.getSheets()[1].getSheetId();

    // Create pivot table
    const pivotTable = Sheets.newPivotTable();

    let gridRange = Sheets.newGridRange();
    gridRange.sheetId = sourceSheetId;
    gridRange.startRowIndex = 0;
    gridRange.startColumnIndex = 0;
    gridRange.endRowIndex = 20;
    gridRange.endColumnIndex = 7;
    pivotTable.source = gridRange;

    let pivotRows = Sheets.newPivotGroup();
    pivotRows.sourceColumnOffset = 1;
    pivotRows.showTotals = true;
    pivotRows.sortOrder = 'ASCENDING';
    pivotTable.rows = pivotRows;

    let pivotColumns = Sheets.newPivotGroup();
    pivotColumns.sourceColumnOffset = 4;
    pivotColumns.sortOrder = 'ASCENDING';
    pivotColumns.showTotals = true;
    pivotTable.columns = pivotColumns;

    let pivotValue = Sheets.newPivotValue();
    pivotValue.summarizeFunction = 'COUNTA';
    pivotValue.sourceColumnOffset = 4;
    pivotTable.values = [pivotValue];

    // Create other metadata for the updateCellsRequest
    let cellData = Sheets.newCellData();
    cellData.pivotTable = pivotTable;

    let rows = Sheets.newRowData();
    rows.values = cellData;

    let start = Sheets.newGridCoordinate();
    start.sheetId = targetSheetId;
    start.rowIndex = 0;
    start.columnIndex = 0;

    let updateCellsRequest = Sheets.newUpdateCellsRequest();
    updateCellsRequest.rows = rows;
    updateCellsRequest.start = start;
    updateCellsRequest.fields = 'pivotTable';

    // Batch update our spreadsheet
    let batchUpdate = Sheets.newBatchUpdateSpreadsheetRequest();
    let updateCellsRawRequest = Sheets.newRequest();
    updateCellsRawRequest.updateCells = updateCellsRequest;
    batchUpdate.requests = [updateCellsRawRequest];
    const response = Sheets.Spreadsheets.batchUpdate(batchUpdate,
      spreadsheetId);

    return response;
  } catch (err) {
    // TODO (developer) - Handle exception
    console.log('Failed with error %s', err.message);
  }
};

자바

sheets/snippets/src/main/java/PivotTables.java

GitHub에서 보기

import com.google.api.client.googleapis.json.GoogleJsonError;
import com.google.api.client.googleapis.json.GoogleJsonResponseException;
import com.google.api.client.http.HttpRequestInitializer;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.gson.GsonFactory;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.AddSheetRequest;
import com.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetRequest;
import com.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetResponse;
import com.google.api.services.sheets.v4.model.CellData;
import com.google.api.services.sheets.v4.model.GridCoordinate;
import com.google.api.services.sheets.v4.model.GridRange;
import com.google.api.services.sheets.v4.model.PivotGroup;
import com.google.api.services.sheets.v4.model.PivotTable;
import com.google.api.services.sheets.v4.model.PivotValue;
import com.google.api.services.sheets.v4.model.Request;
import com.google.api.services.sheets.v4.model.RowData;
import com.google.api.services.sheets.v4.model.UpdateCellsRequest;
import com.google.auth.http.HttpCredentialsAdapter;
import com.google.auth.oauth2.GoogleCredentials;
import com.google.common.collect.Lists;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/* Class to demonstrate the use of Spreadsheet Create Pivot Tables API */
public class PivotTables {
  /**
   * Create pivot table.
   *
   * @param spreadsheetId - Id of the spreadsheet.
   * @return pivot table's spreadsheet
   * @throws IOException - if credentials file not found.
   */
  public static BatchUpdateSpreadsheetResponse pivotTables(String spreadsheetId)
      throws IOException {
        /* Load pre-authorized user credentials from the environment.
           TODO(developer) - See https://developers.google.com/identity for
            guides on implementing OAuth2 for your application. */
    GoogleCredentials credentials = GoogleCredentials.getApplicationDefault()
        .createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS));
    HttpRequestInitializer requestInitializer = new HttpCredentialsAdapter(
        credentials);

    // Create the sheets API client
    Sheets service = new Sheets.Builder(new NetHttpTransport(),
        GsonFactory.getDefaultInstance(),
        requestInitializer)
        .setApplicationName("Sheets samples")
        .build();

    // Create two sheets for our pivot table.
    List<Request> sheetsRequests = new ArrayList<>();
    BatchUpdateSpreadsheetResponse result = null;
    try {
      sheetsRequests.add(new Request().setAddSheet(new AddSheetRequest()));
      sheetsRequests.add(new Request().setAddSheet(new AddSheetRequest()));

      BatchUpdateSpreadsheetRequest createSheetsBody = new BatchUpdateSpreadsheetRequest()
          .setRequests(sheetsRequests);
      BatchUpdateSpreadsheetResponse createSheetsResponse = service.spreadsheets()
          .batchUpdate(spreadsheetId, createSheetsBody).execute();
      int sourceSheetId = createSheetsResponse.getReplies().get(0).getAddSheet().getProperties()
          .getSheetId();
      int targetSheetId = createSheetsResponse.getReplies().get(1).getAddSheet().getProperties()
          .getSheetId();

      PivotTable pivotTable = new PivotTable()
          .setSource(
              new GridRange()
                  .setSheetId(sourceSheetId)
                  .setStartRowIndex(0)
                  .setStartColumnIndex(0)
                  .setEndRowIndex(20)
                  .setEndColumnIndex(7)
          )
          .setRows(Collections.singletonList(
              new PivotGroup()
                  .setSourceColumnOffset(1)
                  .setShowTotals(true)
                  .setSortOrder("ASCENDING")
          ))
          .setColumns(Collections.singletonList(
              new PivotGroup()
                  .setSourceColumnOffset(4)
                  .setShowTotals(true)
                  .setSortOrder("ASCENDING")
          ))
          .setValues(Collections.singletonList(
              new PivotValue()
                  .setSummarizeFunction("COUNTA")
                  .setSourceColumnOffset(4)
          ));
      List<Request> requests = Lists.newArrayList();
      Request updateCellsRequest = new Request().setUpdateCells(new UpdateCellsRequest()
          .setFields("*")
          .setRows(Collections.singletonList(
              new RowData().setValues(
                  Collections.singletonList(
                      new CellData().setPivotTable(pivotTable))
              )
          ))
          .setStart(new GridCoordinate()
              .setSheetId(targetSheetId)
              .setRowIndex(0)
              .setColumnIndex(0)

          ));

      requests.add(updateCellsRequest);
      BatchUpdateSpreadsheetRequest updateCellsBody = new BatchUpdateSpreadsheetRequest()
          .setRequests(requests);
      result = service.spreadsheets().batchUpdate(spreadsheetId, updateCellsBody).execute();
    } catch (GoogleJsonResponseException e) {
      // TODO(developer) - handle error appropriately
      GoogleJsonError error = e.getDetails();
      if (error.getCode() == 404) {
        System.out.printf("Spreadsheet not found with id '%s'.\n", spreadsheetId);
      } else {
        throw e;
      }
    }
    return result;
  }
}

자바스크립트

sheets/snippets/sheets_pivot_tables.js

GitHub에서 보기

function pivotTable(spreadsheetId, callback) {
  // Create two sheets for our pivot table
  const requests = [{
    addSheet: {},
  }, {
    addSheet: {},
  }];
  const batchUpdateRequest = {requests: requests};
  try {
    gapi.client.sheets.spreadsheets.batchUpdate({
      spreadsheetId: spreadsheetId,
      resource: batchUpdateRequest,
    }).then((response) => {
      const sourceSheetId = response.result.replies[0].addSheet.properties.sheetId;
      const targetSheetId = response.result.replies[1].addSheet.properties.sheetId;

      const requests = [{
        updateCells: {
          rows: {
            values: [{
              pivotTable: {
                source: {
                  sheetId: sourceSheetId,
                  startRowIndex: 0,
                  startColumnIndex: 0,
                  endRowIndex: 20,
                  endColumnIndex: 7,
                },
                rows: [{
                  sourceColumnOffset: 1,
                  showTotals: true,
                  sortOrder: 'ASCENDING',
                }],
                columns: [{
                  sourceColumnOffset: 4,
                  sortOrder: 'ASCENDING',
                  showTotals: true,
                }],
                values: [{
                  summarizeFunction: 'COUNTA',
                  sourceColumnOffset: 4,
                }],
                valueLayout: 'HORIZONTAL',
              },
            },
            ],
          },
          start: {
            sheetId: targetSheetId,
            rowIndex: 0,
            columnIndex: 0,
          },
          fields: 'pivotTable',
        },
      }];

      const body = {
        requests,
      };
      gapi.client.sheets.spreadsheets.batchUpdate({
        spreadsheetId: spreadsheetId,
        resource: body,
      }).then((response) => {
        if (callback) callback(response);
      });
    });
  } catch (err) {
    document.getElementById('content').innerText = err.message;
    return;
  }
}

Node.js

sheets/스니펫/sheets_pivot_table.js

GitHub에서 보기

/**
 * Adds a pivot table to a spreadsheet.
 * @param {string} spreadsheetId The Spreadsheet to add the pivot table to.
 * @return {obj} spreadsheet information
 */
async function pivotTable(spreadsheetId) {
  const {GoogleAuth} = require('google-auth-library');
  const {google} = require('googleapis');

  const auth = new GoogleAuth({
    scopes: 'https://www.googleapis.com/auth/spreadsheets',
  });

  const service = google.sheets({version: 'v4', auth});
  try {
    // Create two sheets for our pivot table
    let requests = [
      {
        addSheet: {},
      },
      {
        addSheet: {},
      },
    ];
    let resource = {requests};
    let response = await service.spreadsheets.batchUpdate({
      spreadsheetId,
      resource,
    });
    const sourceSheetId = response.data.replies[0].addSheet.properties.sheetId;
    const targetSheetId = response.data.replies[1].addSheet.properties.sheetId;

    requests = [
      {
        updateCells: {
          rows: {
            values: [
              {
                pivotTable: {
                  source: {
                    sheetId: sourceSheetId,
                    startRowIndex: 0,
                    startColumnIndex: 0,
                    endRowIndex: 20,
                    endColumnIndex: 7,
                  },
                  rows: [
                    {
                      sourceColumnOffset: 1,
                      showTotals: true,
                      sortOrder: 'ASCENDING',
                    },
                  ],
                  columns: [
                    {
                      sourceColumnOffset: 4,
                      sortOrder: 'ASCENDING',
                      showTotals: true,
                    },
                  ],
                  values: [
                    {
                      summarizeFunction: 'COUNTA',
                      sourceColumnOffset: 4,
                    },
                  ],
                  valueLayout: 'HORIZONTAL',
                },
              },
            ],
          },
          start: {
            sheetId: targetSheetId,
            rowIndex: 0,
            columnIndex: 0,
          },
          fields: 'pivotTable',
        },
      },
    ];
    resource = {
      requests,
    };
    response = service.spreadsheets.batchUpdate({
      spreadsheetId,
      resource,
    });
    return response;
  } catch (err) {
    // TODO (developer) - Handle exception
    throw err;
  }
}

2,399필리핀

sheets/snippets/src/SpreadsheetPivotTables.php

GitHub에서 보기

use Google\Client;
use Google\Service\Drive;
use Google\Service\Sheets\BatchUpdateSpreadsheetRequest;


function pivotTables($spreadsheetId)
    {
        /* Load pre-authorized user credentials from the environment.
           TODO(developer) - See https://developers.google.com/identity for
            guides on implementing OAuth2 for your application. */
        $client = new Google\Client();
        $client->useApplicationDefaultCredentials();
        $client->addScope(Google\Service\Drive::DRIVE);
        $service = new Google_Service_Sheets($client);
        try{
            $requests = [
                new Google_Service_Sheets_Request([
                    'addSheet' => [
                        'properties' => [
                            'title' => 'Sheet 1'
                            ]
                ]
            ]),
            new Google_Service_Sheets_Request([
                'addSheet' => [
                    'properties' => [
                        'title' => 'Sheet 2'
                        ]
                        ]
                        ])
                    ];
                    // Create two sheets for our pivot table
                    $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
                        'requests' => $requests
                    ]);
        $batchUpdateResponse = $service->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);
        $sourceSheetId = $batchUpdateResponse->replies[0]->addSheet->properties->sheetId;
        $targetSheetId = $batchUpdateResponse->replies[1]->addSheet->properties->sheetId;
        $requests = [
            'updateCells' => [
                'rows' => [
                    'values' => [
                        [
                            'pivotTable' => [
                                'source' => [
                                    'sheetId' => $sourceSheetId,
                                    'startRowIndex' => 0,
                                    'startColumnIndex' => 0,
                                    'endRowIndex' => 20,
                                    'endColumnIndex' => 7
                                ],
                                'rows' => [
                                    [
                                        'sourceColumnOffset' => 1,
                                        'showTotals' => true,
                                        'sortOrder' => 'ASCENDING',
                                    ],
                                ],
                                'columns' => [
                                    [
                                        'sourceColumnOffset' => 4,
                                        'sortOrder' => 'ASCENDING',
                                        'showTotals' => true,
                                    ]
                                ],
                                'values' => [
                                    [
                                        'summarizeFunction' => 'COUNTA',
                                        'sourceColumnOffset' => 4
                                        ]
                                    ],
                                'valueLayout' => 'HORIZONTAL'
                            ]
                        ]
                        ]
                ],
                'start' => [
                    'sheetId' => $targetSheetId,
                    'rowIndex' => 0,
                    'columnIndex' => 0
                ],
                'fields' => 'pivotTable'
            ]
        ];
        return $batchUpdateResponse;
    }
        catch(Exception $e) {
            // TODO(developer) - handle error appropriately
            echo 'Message: ' .$e->getMessage();
        }
    }

Python

sheets/snippets/sheets_pivot_tables.py

GitHub에서 보기

import google.auth
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError


def pivot_tables(spreadsheet_id):
  """
  Creates the batch_update the user has access to.
  Load pre-authorized user credentials from the environment.
  TODO(developer) - See https://developers.google.com/identity
  for guides on implementing OAuth2 for the application.
  """
  creds, _ = google.auth.default()
  # pylint: disable=maybe-no-member
  try:
    service = build("sheets", "v4", credentials=creds)
    # Create two sheets for our pivot table.
    body = {"requests": [{"addSheet": {}}, {"addSheet": {}}]}
    batch_update_response = (
        service.spreadsheets()
        .batchUpdate(spreadsheetId=spreadsheet_id, body=body)
        .execute()
    )
    source_sheet_id = (
        batch_update_response.get("replies")[0]
        .get("addSheet")
        .get("properties")
        .get("sheetId")
    )
    target_sheet_id = (
        batch_update_response.get("replies")[1]
        .get("addSheet")
        .get("properties")
        .get("sheetId")
    )
    requests = []
    requests.append(
        {
            "updateCells": {
                "rows": {
                    "values": [
                        {
                            "pivotTable": {
                                "source": {
                                    "sheetId": source_sheet_id,
                                    "startRowIndex": 0,
                                    "startColumnIndex": 0,
                                    "endRowIndex": 20,
                                    "endColumnIndex": 7,
                                },
                                "rows": [
                                    {
                                        "sourceColumnOffset": 1,
                                        "showTotals": True,
                                        "sortOrder": "ASCENDING",
                                    },
                                ],
                                "columns": [{
                                    "sourceColumnOffset": 4,
                                    "sortOrder": "ASCENDING",
                                    "showTotals": True,
                                }],
                                "values": [{
                                    "summarizeFunction": "COUNTA",
                                    "sourceColumnOffset": 4,
                                }],
                                "valueLayout": "HORIZONTAL",
                            }
                        }
                    ]
                },
                "start": {
                    "sheetId": target_sheet_id,
                    "rowIndex": 0,
                    "columnIndex": 0,
                },
                "fields": "pivotTable",
            }
        }
    )
    body = {"requests": requests}
    response = (
        service.spreadsheets()
        .batchUpdate(spreadsheetId=spreadsheet_id, body=body)
        .execute()
    )
    return response

  except HttpError as error:
    print(f"An error occurred: {error}")
    return error


if __name__ == "__main__":
  # Pass: spreadsheet_id
  pivot_tables("1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k")

Ruby

sheets/snippets/lib/spreadsheet_snippets.rb

GitHub에서 보기

requests = [{
  update_cells: {
    rows:   {
      values: [
        {
          pivot_table: {
            source:       {
              sheet_id:           source_sheet_id,
              start_row_index:    0,
              start_column_index: 0,
              end_row_index:      20,
              end_column_index:   7
            },
            rows:         [
              {
                source_column_offset: 1,
                show_totals:          true,
                sort_order:           'ASCENDING',
              },
            ],
            columns:      [
              {
                source_column_offset: 4,
                sort_order:           'ASCENDING',
                show_totals:          true,
              }
            ],
            values:       [
              {
                summarize_function:   'COUNTA',
                source_column_offset: 4
              }
            ],
            value_layout: 'HORIZONTAL'
          }
        }
      ]
    },
    start:  {
      sheet_id:     target_sheet_id,
      row_index:    0,
      column_index: 0
    },
    fields: 'pivotTable'
  }
}]
result = service.batch_update_spreadsheet(spreadsheet_id, body, {})