Microsoft Learn Challenge

複数のExcelテーブルのデータを1つのテーブルに統合する – Office Scripts

このサンプルは、複数のExcelテーブルからデータを一つのテーブルに統合する方法を示しています。全テーブルは同じ構造を持つと仮定しています。スクリプトには2つのバリエーションがあります。1つ目はExcelファイル内の全テーブルを統合するもの、2つ目は特定のワークシートからテーブルを選択して統合します。サンプルコードを使用して、統合されたテーブルを作成する手順が示されています。さらに、YouTubeでのトレーニングビデオも提供されています。

複数のExcelテーブルから1つのテーブルにデータを統合する方法

Excelを使用する際、大量のデータを管理することは避けられない課題です。複数のテーブルからデータを1つのテーブルに統合することで、データの分析や管理が容易になります。このサンプルでは、複数のExcelテーブルからすべての行を含む単一のテーブルを作成する方法を紹介します。このスクリプトでは、使用するすべてのテーブルが同じ構造であることを前提としています。

スクリプトのバリエーション

このスクリプトには2つのバリエーションがあります。

  1. 最初のスクリプト: Excelファイル内のすべてのテーブルを統合します。
  2. 2番目のスクリプト: 指定されたワークシート内のテーブルを選択的に取得します。

使い方: サンプルExcelファイルの設定

このワークブックには、スクリプトが期待するデータ、オブジェクト、およびフォーマットが含まれています。

サンプルコード: 複数のExcelテーブルから1つのテーブルにデータを統合する

以下のスクリプトをサンプルワークブックに追加し、実際に試してみてください！

function main(workbook: ExcelScript.Workbook) {
  // "Combined"という名前のワークシートが存在する場合は削除します。
  workbook.getWorksheet('Combined')?.delete();

  // 統合テーブルのために"Combined"という新しいワークシートを作成します。
  const newSheet = workbook.addWorksheet('Combined');

  // ワークブック内の最初のテーブルのヘッダ値を取得します。
  const tables = workbook.getTables();    
  const headerValues = tables[0].getHeaderRowRange().getTexts();
  console.log(headerValues);

  // 新しいワークシートにヘッダーをコピーします。
  const targetRange = newSheet.getRange('A1').getResizedRange(headerValues.length - 1, headerValues[0].length - 1);
  targetRange.setValues(headerValues);

  // ワークブック内の各テーブルからデータを新しいテーブルに追加します。
  const combinedTable = newSheet.addTable(targetRange.getAddress(), true);
  for (let table of tables) {      
    let dataValues = table.getRangeBetweenHeaderAndTotal().getTexts();
    let rowCount = table.getRowCount();

    // テーブルが空でない場合、その行を統合テーブルに追加します。
    if (rowCount > 0) {
      combinedTable.addRows(-1, dataValues);
    }
  }
}

サンプルコード: 複数のExcelテーブルを特定のワークシートから1つのテーブルに統合する

サンプルファイルをこちらからダウンロードして、以下のスクリプトを使用して試してみてください！

function main(workbook: ExcelScript.Workbook) {
  // テーブルを取得するワークシートの名前を設定します。
  const sheetNames = ['Sheet1', 'Sheet2', 'Sheet3'];

  // "Combined"という名前のワークシートが存在する場合は削除します。
  workbook.getWorksheet('Combined')?.delete();

  // 統合テーブルのために"Combined"という新しいワークシートを作成します。
  const newSheet = workbook.addWorksheet('Combined');

  // 他のテーブルと同じヘッダーを持つ新しいテーブルを作成します。
  const headerValues = workbook.getWorksheet(sheetNames[0]).getTables()[0].getHeaderRowRange().getTexts();
  const targetRange = newSheet.getRange('A1').getResizedRange(headerValues.length - 1, headerValues[0].length - 1);
  targetRange.setValues(headerValues);
  const combinedTable = newSheet.addTable(targetRange.getAddress(), true);

  // 各指定されたワークシートを通じてテーブルを取得します。
  sheetNames.forEach((sheet) => {
    const tables = workbook.getWorksheet(sheet).getTables();     
    for (let table of tables) {
      // テーブルから行を取得します。
      let dataValues = table.getRangeBetweenHeaderAndTotal().getTexts();
      let rowCount = table.getRowCount();

      // テーブルにデータがある場合、統合テーブルに追加します。
      if (rowCount > 0) {
        combinedTable.addRows(-1, dataValues);
      }
    }
  });
}

トレーニングビデオ: 複数のExcelテーブルから1つのテーブルにデータを統合する

Sudhi Ramamurthyによるこのサンプルの詳細な解説をYouTubeで視聴する。

この方法を試すことで、Excel内のデータ管理を大幅に効率化できることでしょう。

————-

Combine data from multiple Excel tables into a single table – Office Scripts

Source link

This sample describes two JavaScript-based scripts that combine data from multiple tables within an Excel workbook into a single table while retaining their structure. Both scripts require a properly set up sample Excel file containing the expected data and formatting.

Overview of the Scripts:

  1. First Script: This script consolidates all tables present in the entire workbook into a new worksheet named "Combined."

    • It deletes any existing "Combined" worksheet.
    • A new "Combined" worksheet is created.
    • It retrieves header values from the first table to define the structure of the new table and then populates it with the data from all tables in the workbook.
    • Only non-empty tables are added to the combined table.
  2. Second Script: This version combines tables from specific worksheets ("Sheet1," "Sheet2," "Sheet3") into the "Combined" worksheet.
    • Similarly, it starts by deleting any existing "Combined" worksheet.
    • A new combined table is created using headers from the first specified worksheet.
    • It iterates through the listed worksheets, retrieves tables, and adds their data to the combined table if they have any rows.

Set Up:

  • The user needs to prepare an Excel workbook containing the data and formatting specified for the scripts to function correctly.

Usage:

The user can add the provided scripts to their workbook and execute them to see how data from multiple tables gets compiled into a single, consolidated table.

Additional Resource:

A training video featuring Sudhi Ramamurthy is available on YouTube to visually guide users through the process of using these scripts.

プレスリリース：IPAデジタル基盤センターと産総研G-QuATとの連携協定締結 前の記事 ExcelデータをJSON形式で出力 - Office Scripts 次の記事

関連記事