Excel JavaScript API を使用してピボットテーブルを操作する

[アーティクル]
04/07/2023

ピボットテーブルは、大規模なデータセットを合理化します。これにより、グループ化されたデータを簡単に操作できます。 Excel JavaScript API を使用すると、アドインでピボットテーブルを作成し、そのコンポーネントと対話できます。この記事では、Office JavaScript API によってピボットテーブルがどのように表されるかについて説明し、主要なシナリオのコードサンプルを提供します。

ピボットテーブルの機能に慣れていない場合は、エンドユーザーとして調査することを検討してください。これらのツールに関する適切な入門情報については、「ピボットテーブルを作成する」を参照してください。

重要

OLAP で作成されたピボットテーブルは現在サポートされていません。 Power Pivot のサポートもありません。

オブジェクトモデル

後続の一覧と関連する参照ドキュメントで説明されているように、ピボットテーブルのオブジェクトの種類間の関係を示す図。

ピボットテーブルは、Office JavaScript API のピボットテーブルの中心的なオブジェクトです。

Workbook.pivotTables および Worksheet.pivotTables は、それぞれブックとワークシートのピボットテーブルを含む PivotTableCollection です。
ピボットテーブルには、複数の PivotHierarchies を持つ PivotHierarchyCollection が含まれています。
これらの PivotHierarchies を特定の階層コレクションに追加して、ピボットテーブルがデータをピボットする方法を定義できます (次のセクションで説明します)。
PivotHierarchy には、ピボットフィールドが 1 つだけ含まれる PivotFieldCollection が含まれています。デザインが展開され、OLAP ピボットテーブルが含まれる場合、これは変更される可能性があります。
ピボットフィールドには、フィールドの PivotHierarchy が階層カテゴリに割り当てられている限り、1 つ以上の PivotFilter を適用できます。
PivotField には、複数の PivotItem を持つ PivotItemCollection が含まれています。
ピボットテーブルには、ワークシートに PivotFields と PivotItems を表示する場所を定義する PivotLayout が含まれています。レイアウトでは、ピボットテーブルの一部の表示設定も制御されます。

これらのリレーションシップがデータの例にどのように適用されるかを見てみましょう。次のデータでは、さまざまな農場からの果物の売上について説明します。この記事全体の例を示します。

異なる農場のさまざまな種類の果物の販売のコレクション。

このフルーツファームの売上データは、ピボットテーブルの作成に使用されます。 Types などの各列はですPivotHierarchy。 Types 階層には、[型] フィールドが含まれています。 [ 種類] フィールドには、 Apple、 Kiwi、 Lemon、 Lime、Orange の項目が含 まれています。

Hierarchies

ピボットテーブルは、行、列、データ、フィルターの 4 つの階層カテゴリに基づいて編成されます。

前に示したファームデータには、 ファーム、 タイプ、分類、 ファームで販売されたクレート、およびク レート販売卸売の 5 つの階層があります。各階層は、4 つのカテゴリのいずれかにのみ存在できます。列階層に Type を追加した場合、行、データ、またはフィルター階層に含めることはできません。その後 、Type が行階層に追加されると、列階層から削除されます。この動作は、階層の割り当てが Excel UI または Excel JavaScript API を介して実行される場合でも同じです。

行階層と列階層は、データのグループ化方法を定義します。たとえば、ファームの行階層は、同じ ファーム のすべてのデータセットをグループ化します。行と列の階層を選択すると、ピボットテーブルの向きが定義されます。

データ階層は、行階層と列階層に基づいて集計される値です。 ファームの行階層とクレート販売卸売のデータ階層を持つピボットテーブルには、各ファームのすべての異なる果物の合計 (既定) が表示されます。

フィルター階層には、そのフィルター処理された型内の値に基づいて、ピボットからデータが含まれるか除外されます。 [ 分類 ] のフィルター階層で、 種類が [有機] が選択されている場合は、有機フルーツのデータのみが表示されます。

ピボットテーブルと共に、もう一度ファームデータを次に示します。ピボットテーブルでは、行階層として Farm と Type を使用し、データ階層として [ファームで販売されたクレート] と [ 販売されたクレート ] をデータ階層として使用し (合計の既定の集計関数を使用)、[ 分類 ] をフィルター階層として使用しています ( [有機] が選択されています)。

行、データ、およびフィルター階層を含むピボットテーブルの横にあるフルーツ販売データの選択。

このピボットテーブルは、JavaScript API または Excel UI を使用して生成できます。どちらのオプションでも、アドインを使用してさらに操作できます。

ピボットテーブルを作成する

ピボットテーブルには、名前、ソース、および変換先が必要です。ソースには、範囲アドレスまたはテーブル名 (、string、または Table 型としてRange渡される) を指定できます。宛先は範囲アドレスです (または stringとしてRange指定されます)。次のサンプルは、さまざまなピボットテーブル作成手法を示しています。

範囲アドレスを使用してピボットテーブルを作成する

await Excel.run(async (context) => {
    // Create a PivotTable named "Farm Sales" on the current worksheet at cell
    // A22 with data from the range A1:E21.
    context.workbook.worksheets.getActiveWorksheet().pivotTables.add(
      "Farm Sales", "A1:E21", "A22");

    await context.sync();
});

Range オブジェクトを使用してピボットテーブルを作成する

await Excel.run(async (context) => {
    // Create a PivotTable named "Farm Sales" on a worksheet called "PivotWorksheet" at cell A2
    // the data comes from the worksheet "DataWorksheet" across the range A1:E21.
    let rangeToAnalyze = context.workbook.worksheets.getItem("DataWorksheet").getRange("A1:E21");
    let rangeToPlacePivot = context.workbook.worksheets.getItem("PivotWorksheet").getRange("A2");
    context.workbook.worksheets.getItem("PivotWorksheet").pivotTables.add(
      "Farm Sales", rangeToAnalyze, rangeToPlacePivot);

    await context.sync();
});

ブックレベルでピボットテーブルを作成する

await Excel.run(async (context) => {
    // Create a PivotTable named "Farm Sales" on a worksheet called "PivotWorksheet" at cell A2
    // the data is from the worksheet "DataWorksheet" across the range A1:E21.
    context.workbook.pivotTables.add(
        "Farm Sales", "DataWorksheet!A1:E21", "PivotWorksheet!A2");

    await context.sync();
});

既存のピボットテーブルを使用する

手動で作成されたピボットテーブルには、ブックのピボットテーブルコレクションまたは個々のワークシートからアクセスすることもできます。次のコードでは、ブックから My Pivot という名前のピボット テーブルを取得します。

await Excel.run(async (context) => {
    let pivotTable = context.workbook.pivotTables.getItem("My Pivot");
    await context.sync();
});

ピボットテーブルに行と列を追加する

行と列は、これらのフィールドの値を中心にデータをピボットします。

[ファーム] 列を追加すると、各ファームの売上がすべてピボットされます。 [種類] 行と [分類] 行を追加すると、販売された果物と有機性の有無に基づいてデータがさらに分割されます。

[ファーム] 列と [種類] 行と [分類] 行を含むピボットテーブル。

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");

    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Type"));
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Classification"));

    pivotTable.columnHierarchies.add(pivotTable.hierarchies.getItem("Farm"));

    await context.sync();
});

行または列のみを含むピボットテーブルを作成することもできます。

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Farm"));
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Type"));
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Classification"));

    await context.sync();
});

ピボットテーブルにデータ階層を追加する

データ階層は、ピボットテーブルに行と列に基づいて結合する情報を入力します。 ファームで販売されたクレートとクレート販売卸売のデータ階層を追加すると、行と列ごとにそれらの数値の合計が得られます。

この例では、 Farm と Type の両方が行であり、クレート売上がデータとして使用されています。

生まれた農場に基づくさまざまな果物の合計売上を示すピボットテーブル。

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");

    // "Farm" and "Type" are the hierarchies on which the aggregation is based.
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Farm"));
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Type"));

    // "Crates Sold at Farm" and "Crates Sold Wholesale" are the hierarchies
    // that will have their data aggregated (summed in this case).
    pivotTable.dataHierarchies.add(pivotTable.hierarchies.getItem("Crates Sold at Farm"));
    pivotTable.dataHierarchies.add(pivotTable.hierarchies.getItem("Crates Sold Wholesale"));

    await context.sync();
});

ピボットテーブルレイアウトとピボットデータの取得

PivotLayout は、階層とそのデータの配置を定義します。レイアウトにアクセスして、データを格納する範囲を決定します。

次の図は、ピボットテーブルの範囲に対応するレイアウト関数呼び出しを示しています。

レイアウトの get 範囲関数によって返されるピボットテーブルのセクションを示す図。

ピボットテーブルからデータを取得する

レイアウトは、ワークシートにピボットテーブルを表示する方法を定義します。つまり、オブジェクトは PivotLayout ピボットテーブル要素に使用される範囲を制御します。レイアウトで提供される範囲を使用して、ピボットテーブルによって収集および集計されたデータを取得します。特に、を使用 PivotLayout.getDataBodyRange して、ピボットテーブルによって生成されたデータにアクセスします。

次のコードは、レイアウトを実行してピボットテーブルデータの最後の行を取得する方法を示しています (前の例の [ファームで販売されたクレートの合計] 列と [販売されたクレートの合計] 列の総計)。その後、これらの値が合計され、最終的な合計が合計され、セル E30 (ピボットテーブルの外部) に表示されます。

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");

    // Get the totals for each data hierarchy from the layout.
    let range = pivotTable.layout.getDataBodyRange();
    let grandTotalRange = range.getLastRow();
    grandTotalRange.load("address");
    await context.sync();

    // Sum the totals from the PivotTable data hierarchies and place them in a new range, outside of the PivotTable.
    let masterTotalRange = context.workbook.worksheets.getActiveWorksheet().getRange("E30");
    masterTotalRange.formulas = [["=SUM(" + grandTotalRange.address + ")"]];
    await context.sync();
});

レイアウトの種類

ピボットテーブルには、コンパクト、アウトライン、表形式の 3 つのレイアウトスタイルがあります。前の例では、コンパクトなスタイルを見てきました。

次の例では、それぞれアウトラインスタイルと表形式スタイルを使用しています。コードサンプルは、異なるレイアウト間を循環する方法を示しています。

アウトラインレイアウト

アウトラインレイアウトを使用したピボットテーブル。

表形式レイアウト

表形式レイアウトを使用したピボットテーブル。

PivotLayout 型スイッチのコードサンプル

await Excel.run(async (context) => {
    // Change the PivotLayout.type to a new type.
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    pivotTable.layout.load("layoutType");
    await context.sync();

    // Cycle between the three layout types.
    if (pivotTable.layout.layoutType === "Compact") {
        pivotTable.layout.layoutType = "Outline";
    } else if (pivotTable.layout.layoutType === "Outline") {
        pivotTable.layout.layoutType = "Tabular";
    } else {
        pivotTable.layout.layoutType = "Compact";
    }

    await context.sync();
});

その他の PivotLayout 関数

既定では、ピボットテーブルは必要に応じて行と列のサイズを調整します。これは、ピボットテーブルが更新されたときに行われます。 PivotLayout.autoFormat は、その動作を指定します。がの場合 autoFormat 、アドインによって行われた行または列のサイズの変更は保持されます false。さらに、ピボットテーブルの既定の設定では、ピボットテーブルにカスタム書式が保持されます (塗りつぶしやフォントの変更など)。をにfalse設定PivotLayout.preserveFormattingして、更新時に既定の形式を適用します。

また、 PivotLayout ヘッダーと行の合計の設定、空のデータセルの表示方法、代替テキストオプションも制御します。 PivotLayout リファレンスには、これらの機能の完全な一覧が用意されています。

次のコードサンプルでは、空のデータセルに文字列 "--"が表示され、本文範囲が一貫した水平方向の配置に書式設定され、ピボットテーブルが更新された後も書式設定の変更が確実に維持されます。

await Excel.run(async (context) => {
    let pivotTable = context.workbook.pivotTables.getItem("Farm Sales");
    let pivotLayout = pivotTable.layout;

    // Set a default value for an empty cell in the PivotTable. This doesn't include cells left blank by the layout.
    pivotLayout.emptyCellText = "--";

    // Set the text alignment to match the rest of the PivotTable.
    pivotLayout.getDataBodyRange().format.horizontalAlignment = Excel.HorizontalAlignment.right;

    // Ensure empty cells are filled with a default value.
    pivotLayout.fillEmptyCells = true;

    // Ensure that the format settings persist, even after the PivotTable is refreshed and recalculated.
    pivotLayout.preserveFormatting = true;
    await context.sync();
});

ピボットテーブルを削除する

ピボットテーブルは、その名前を使用して削除されます。

await Excel.run(async (context) => {
    context.workbook.worksheets.getItem("Pivot").pivotTables.getItem("Farm Sales").delete();
    await context.sync();
});

ピボットテーブルをフィルター処理する

ピボットテーブルデータをフィルター処理するための主な方法は、PivotFilters を使用することです。スライサーは、柔軟性の低い代替フィルター方法を提供します。

PivotFilters は、ピボットテーブルの 4 つの階層カテゴリ (フィルター、列、行、および値) に基づいてデータをフィルター処理します。 PivotFilters には 4 種類があり、カレンダーの日付ベースのフィルター処理、文字列解析、数値比較、カスタム入力に基づくフィルター処理が可能です。

スライサーは、ピボットテーブルと通常の Excel テーブルの両方に適用できます。ピボットテーブルに適用すると、スライサーは PivotManualFilter のように機能し、カスタム入力に基づくフィルター処理を許可します。 PivotFilters とは異なり、スライサーには Excel UI コンポーネントがあります。クラスを Slicer 使用して、この UI コンポーネントを作成し、フィルター処理を管理し、その視覚的な外観を制御します。

PivotFilters を使用したフィルター

ピボットフィルターを使用すると、4 つの階層カテゴリ (フィルター、列、行、値) に基づいてピボットテーブルデータをフィルター処理できます。ピボットテーブルオブジェクトモデルでは、 PivotFiltersPivotField に適用され、それぞれが PivotField 1 つ以上割り当てることができます PivotFilters。 PivotFilters を PivotField に適用するには、フィールドの対応する PivotHierarchy を階層カテゴリに割り当てる必要があります。

PivotFilters の種類

フィルターの種類	フィルターの目的	Excel JavaScript API リファレンス
DateFilter	カレンダーの日付ベースのフィルター処理。	PivotDateFilter
LabelFilter	テキスト比較フィルター処理。	PivotLabelFilter
ManualFilter	カスタム入力フィルター処理。	PivotManualFilter
ValueFilter	数値比較フィルター処理。	PivotValueFilter

PivotFilter を作成する

(など) を使用してピボットテーブルデータをPivot*Filterフィルター処理するには、PivotDateFilterピボットフィールドにフィルターを適用します。次の 4 つのコードサンプルは、4 種類の PivotFilters のそれぞれを使用する方法を示しています。

PivotDateFilter

最初のコードサンプルでは、PivotDateFilter を Date Updated PivotField に適用し、 2020-08-01 より前のすべてのデータを非表示にします。

重要

Pivot*Filterフィールドの PivotHierarchy が階層カテゴリに割り当てられている場合を除き、ピボットフィールドにを適用することはできません。次のコードサンプルでは、を dateHierarchy フィルター処理に使用する前に、ピボットテーブルの rowHierarchies カテゴリにを追加する必要があります。

await Excel.run(async (context) => {
    // Get the PivotTable and the date hierarchy.
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    let dateHierarchy = pivotTable.rowHierarchies.getItemOrNullObject("Date Updated");
    await context.sync();

    // PivotFilters can only be applied to PivotHierarchies that are being used for pivoting.
    // If it's not already there, add "Date Updated" to the hierarchies.
    if (dateHierarchy.isNullObject) {
        dateHierarchy = pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Date Updated"));
    }

    // Apply a date filter to filter out anything logged before August.
    let filterField = dateHierarchy.fields.getItem("Date Updated");
    let dateFilter = {
        condition: Excel.DateFilterCondition.afterOrEqualTo,
        comparator: {
        date: "2020-08-01",
        specificity: Excel.FilterDatetimeSpecificity.month
        }
    };
    filterField.applyFilter({ dateFilter: dateFilter });
    
    await context.sync();
});

注:

次の 3 つのコードスニペットでは、完全な Excel.run 呼び出しではなく、フィルター固有の抜粋のみが表示されます。

PivotLabelFilter

2 番目のコードスニペットは、Type PivotField に PivotLabelFilter を適用する方法を示しています。このプロパティをLabelFilterCondition.beginsWith使用して、文字 L で始まるラベルを除外します。

    // Get the "Type" field.
    let filterField = pivotTable.hierarchies.getItem("Type").fields.getItem("Type");

    // Filter out any types that start with "L" ("Lemons" and "Limes" in this case).
    let filter: Excel.PivotLabelFilter = {
      condition: Excel.LabelFilterCondition.beginsWith,
      substring: "L",
      exclusive: true
    };

    // Apply the label filter to the field.
    filterField.applyFilter({ labelFilter: filter });

PivotManualFilter

3 番目のコードスニペットでは、 PivotManualFilter を使用して手動フィルターを [分類 ] フィールドに適用し、分類 オーガニックを含まないデータをフィルター処理します。

    // Apply a manual filter to include only a specific PivotItem (the string "Organic").
    let filterField = classHierarchy.fields.getItem("Classification");
    let manualFilter = { selectedItems: ["Organic"] };
    filterField.applyFilter({ manualFilter: manualFilter });

PivotValueFilter

数値を比較するには、最後のコードスニペットに示すように、 PivotValueFilter で値フィルターを使用します。はPivotValueFilter、Farm PivotField のデータと、販売されたクレートの合計が値 500 を超えるファームのみを含む、クレート販売卸売ピボットフィールド内のデータと比較します。

    // Get the "Farm" field.
    let filterField = pivotTable.hierarchies.getItem("Farm").fields.getItem("Farm");
    
    // Filter to only include rows with more than 500 wholesale crates sold.
    let filter: Excel.PivotValueFilter = {
      condition: Excel.ValueFilterCondition.greaterThan,
      comparator: 500,
      value: "Sum of Crates Sold Wholesale"
    };
    
    // Apply the value filter to the field.
    filterField.applyFilter({ valueFilter: filter });

ピボットフィルターを削除する

すべての PivotFilter を削除するには、次の clearAllFilters コードサンプルに示すように、メソッドを各 PivotField に適用します。

await Excel.run(async (context) => {
    // Get the PivotTable.
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    pivotTable.hierarchies.load("name");
    await context.sync();

    // Clear the filters on each PivotField.
    pivotTable.hierarchies.items.forEach(function (hierarchy) {
        hierarchy.fields.getItem(hierarchy.name).clearAllFilters();
    });
    await context.sync();
});

スライサーを使用したフィルター

スライサーを使用すると、Excel ピボットテーブルまたはテーブルからデータをフィルター処理できます。スライサーは、指定された列または PivotField の値を使用して、対応する行をフィルター処理します。これらの値は、の SlicerSlicerItem オブジェクトとして格納されます。アドインでは、(Excel UI を使用して) ユーザーと同様に、これらのフィルターを調整できます。スライサーは、次のスクリーンショットに示すように、描画レイヤーのワークシートの上に置かれます。

ピボットテーブルのデータをフィルター処理するスライサー。

注:

このセクションで説明する手法では、ピボットテーブルに接続されているスライサーを使用する方法について説明します。同じ手法は、テーブルに接続されたスライサーの使用にも適用されます。

スライサーを作成する

メソッドまたはWorksheet.slicers.addメソッドを使用して、ブックまたはワークシートにスライサーをWorkbook.slicers.add作成できます。これにより、指定したオブジェクトまたは Worksheet オブジェクトの SlicerCollection にスライサーが追加されますWorkbook。メソッドには、次の SlicerCollection.add 3 つのパラメーターがあります。

slicerSource: 新しいスライサーの基になるデータソース。またはのPivotTableTable名前または ID を表す、、または文字列をPivotTableTable指定できます。
sourceField: フィルター処理するデータソースのフィールド。またはのPivotFieldTableColumn名前または ID を表す、、または文字列をPivotFieldTableColumn指定できます。
slicerDestination: 新しいスライサーが作成されるワークシート。オブジェクト、またはの名前または ID を指定WorksheetできますWorksheet。を介して Worksheet.slicersアクセスする場合SlicerCollection、このパラメーターは不要です。この場合、コレクションのワークシートがコピー先として使用されます。

次のコードサンプルでは、 ピボット ワークシートに新しいスライサーを追加します。スライサーのソースは 、Farm Sales ピボットテーブルであり、 Type データを使用したフィルターです。スライサーは、将来の参照のために Fruit Slicer とも呼ばれます。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Pivot");
    let slicer = sheet.slicers.add(
        "Farm Sales" /* The slicer data source. For PivotTables, this can be the PivotTable object reference or name. */,
        "Type" /* The field in the data to filter by. For PivotTables, this can be a PivotField object reference or ID. */
    );
    slicer.name = "Fruit Slicer";
    await context.sync();
});

スライサーを使用して項目をフィルター処理する

スライサーは、の項目を使用してピボットテーブルをフィルター処理します sourceField。メソッドは Slicer.selectItems 、スライサーに残っている項目を設定します。これらの項目は、としてメソッド string[]に渡され、項目のキーを表します。これらの項目を含む行はすべて、ピボットテーブルの集計に残ります。これらの呼び出し selectItems で指定されたキーにリストを設定する後続の呼び出し。

注:

データソースにない項目が渡された場合 Slicer.selectItems は、 InvalidArgument エラーがスローされます。内容は、SlicerItemCollection であるプロパティを使用してSlicer.slicerItems確認できます。

次のコードサンプルは、スライサーに対して選択されている 3 つの項目 ( レモン、 ライム、 オレンジ) を示しています。

await Excel.run(async (context) => {
    let slicer = context.workbook.slicers.getItem("Fruit Slicer");
    // Anything other than the following three values will be filtered out of the PivotTable for display and aggregation.
    slicer.selectItems(["Lemon", "Lime", "Orange"]);
    await context.sync();
});

スライサーからすべてのフィルターを削除するには、次の Slicer.clearFilters サンプルに示すように、メソッドを使用します。

await Excel.run(async (context) => {
    let slicer = context.workbook.slicers.getItem("Fruit Slicer");
    slicer.clearFilters();
    await context.sync();
});

スライサーのスタイル設定と書式設定

アドインでは、プロパティを使用してスライサーの表示設定を Slicer 調整できます。次のコードサンプルでは、スタイルを SlicerStyleLight6 に設定し、スライサーの上部にあるテキストを Fruit Types に設定し、スライサーを描画レイヤー上の位置 (395、15) に配置し、スライサーのサイズを 135x150 ピクセルに設定します。

await Excel.run(async (context) => {
    let slicer = context.workbook.slicers.getItem("Fruit Slicer");
    slicer.caption = "Fruit Types";
    slicer.left = 395;
    slicer.top = 15;
    slicer.height = 135;
    slicer.width = 150;
    slicer.style = "SlicerStyleLight6";
    await context.sync();
});

スライサーを削除する

スライサーを削除するには、メソッドを Slicer.delete 呼び出します。次のコードサンプルでは、現在のワークシートから最初のスライサーを削除します。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.slicers.getItemAt(0).delete();
    await context.sync();
});

集計関数の変更

データ階層の値は集計されます。数値のデータセットの場合、これは既定で合計です。プロパティは summarizeBy 、 AggregationFunction 型に基づいてこの動作を定義します。

現在サポートされている集計関数の種類はSum、、Count、、Average、Max、、StandardDeviationStandardDeviationPMinCountNumbersVarianceProductVariancePおよび Automatic (既定値) です。

次のコードサンプルでは、集計がデータの平均に変更されます。

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    pivotTable.dataHierarchies.load("no-properties-needed");
    await context.sync();

    // Change the aggregation from the default sum to an average of all the values in the hierarchy.
    pivotTable.dataHierarchies.items[0].summarizeBy = Excel.AggregationFunction.average;
    pivotTable.dataHierarchies.items[1].summarizeBy = Excel.AggregationFunction.average;
    await context.sync();
});

ShowAsRule を使用して計算を変更する

ピボットテーブルは、既定では、行階層と列階層のデータを個別に集計します。 ShowAsRule は、ピボットテーブル内の他の項目に基づいてデータ階層を出力値に変更します。

オブジェクトには ShowAsRule 、次の 3 つのプロパティがあります。

calculation: データ階層に適用する相対計算の種類 (既定値は noneです)。
baseField: 計算が適用される前の基本データを含む階層内の PivotField 。 Excel ピボットテーブルには階層とフィールドの 1 対 1 のマッピングがあるため、同じ名前を使用して階層とフィールドの両方にアクセスします。
baseItem: 計算の種類に基づいて基本フィールドの値と比較された個々の PivotItem 。すべての計算でこのフィールドが必要なわけではありません。

次の例では、Farm データ階層 で販売されたクレートの合計 に対する計算を、列の合計に対する割合に設定します。細分性は引き続きフルーツ型レベルに拡張する必要があるため、 Type 行階層とその基になるフィールドを使用します。この例では、最初の行階層として Farm も含まれているため、ファームの合計エントリには、各ファームが生成を担当する割合が表示されます。

個々の農場と各農場内の個々の果物の種類の総計に対する果物の売上の割合を示すピボットテーブル。

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    let farmDataHierarchy = pivotTable.dataHierarchies.getItem("Sum of Crates Sold at Farm");

    farmDataHierarchy.load("showAs");
    await context.sync();

    // Show the crates of each fruit type sold at the farm as a percentage of the column's total.
    let farmShowAs = farmDataHierarchy.showAs;
    farmShowAs.calculation = Excel.ShowAsCalculation.percentOfColumnTotal;
    farmShowAs.baseField = pivotTable.rowHierarchies.getItem("Type").fields.getItem("Type");
    farmDataHierarchy.showAs = farmShowAs;
    farmDataHierarchy.name = "Percentage of Total Farm Sales";
});

前の例では、個々の行階層のフィールドを基準にして、計算を列に設定します。計算が個々のアイテムに関連する場合は、プロパティを baseItem 使用します。

次の例は、計算を differenceFrom 示しています。ファームのクレート売上データ階層エントリの差が 、A Farms のエントリに対して相対的に表示されます。は baseFieldFarm であるため、他のファーム間の違いと、フルーツなどの種類ごとの内訳が表示されます (この例では、Type は行階層でもあります)。

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    let farmDataHierarchy = pivotTable.dataHierarchies.getItem("Sum of Crates Sold at Farm");

    farmDataHierarchy.load("showAs");
    await context.sync();
        
    // Show the difference between crate sales of the "A Farms" and the other farms.
    // This difference is both aggregated and shown for individual fruit types (where applicable).
    let farmShowAs = farmDataHierarchy.showAs;
    farmShowAs.calculation = Excel.ShowAsCalculation.differenceFrom;
    farmShowAs.baseField = pivotTable.rowHierarchies.getItem("Farm").fields.getItem("Farm");
    farmShowAs.baseItem = pivotTable.rowHierarchies.getItem("Farm").fields.getItem("Farm").items.getItem("A Farms");
    farmDataHierarchy.showAs = farmShowAs;
    farmDataHierarchy.name = "Difference from A Farms";
});

階層名を変更する

階層フィールドは編集可能です。次のコードは、2 つのデータ階層の表示名を変更する方法を示しています。

await Excel.run(async (context) => {
    let dataHierarchies = context.workbook.worksheets.getActiveWorksheet()
        .pivotTables.getItem("Farm Sales").dataHierarchies;
    dataHierarchies.load("no-properties-needed");
    await context.sync();

    // Changing the displayed names of these entries.
    dataHierarchies.items[0].name = "Farm Sales";
    dataHierarchies.items[1].name = "Wholesale";
});

次の方法で共有

Excel JavaScript API を使用してピボットテーブルを操作する

オブジェクトモデル

Hierarchies

ピボットテーブルを作成する

範囲アドレスを使用してピボットテーブルを作成する

Range オブジェクトを使用してピボットテーブルを作成する

ブックレベルでピボットテーブルを作成する

既存のピボットテーブルを使用する

ピボットテーブルに行と列を追加する

ピボットテーブルにデータ階層を追加する

ピボットテーブルレイアウトとピボットデータの取得

ピボットテーブルからデータを取得する

レイアウトの種類

アウトラインレイアウト

表形式レイアウト

PivotLayout 型スイッチのコードサンプル

その他の PivotLayout 関数

ピボットテーブルを削除する

ピボットテーブルをフィルター処理する

PivotFilters を使用したフィルター

PivotFilters の種類

PivotFilter を作成する

PivotDateFilter

PivotLabelFilter

PivotManualFilter

PivotValueFilter

ピボットフィルターを削除する

スライサーを使用したフィルター

スライサーを作成する

スライサーを使用して項目をフィルター処理する

スライサーのスタイル設定と書式設定

スライサーを削除する

集計関数の変更

ShowAsRule を使用して計算を変更する

階層名を変更する

関連項目

フィードバック

その他のリソース

次の方法で共有

Excel JavaScript API を使用してピボットテーブルを操作する

オブジェクト モデル

Hierarchies

ピボットテーブルを作成する

範囲アドレスを使用してピボットテーブルを作成する

Range オブジェクトを使用してピボットテーブルを作成する

ブック レベルでピボットテーブルを作成する

既存のピボットテーブルを使用する

ピボットテーブルに行と列を追加する

ピボットテーブルにデータ階層を追加する

ピボットテーブル レイアウトとピボットデータの取得

ピボットテーブルからデータを取得する

レイアウトの種類

アウトライン レイアウト

表形式レイアウト

PivotLayout 型スイッチのコード サンプル

その他の PivotLayout 関数

ピボットテーブルを削除する

ピボットテーブルをフィルター処理する

PivotFilters を使用したフィルター

PivotFilters の種類

PivotFilter を作成する

PivotDateFilter

PivotLabelFilter

PivotManualFilter

PivotValueFilter

ピボットフィルターを削除する

スライサーを使用したフィルター

スライサーを作成する

スライサーを使用して項目をフィルター処理する

スライサーのスタイル設定と書式設定

スライサーを削除する

集計関数の変更

ShowAsRule を使用して計算を変更する

階層名を変更する

関連項目

フィードバック

その他のリソース

オブジェクトモデル

ブックレベルでピボットテーブルを作成する

ピボットテーブルレイアウトとピボットデータの取得

アウトラインレイアウト

PivotLayout 型スイッチのコードサンプル