チュートリアル: Excel ブック データをクリーンアップして正規化する

このチュートリアルでは、Office Script for Excel を使用してブックからデータを読み取る方法について説明します。 口座取引明細書の書式設定を行う新しいスクリプトを作成し、明細書のデータを標準化します。 データのクリーンアップの一環として、スクリプトは取引セルの値を読み取り、それぞれの値に簡単な数式を適用し、導き出された回答をブックに書き込みます。 ブックからデータを読み取ることで、スクリプト内の意思決定プロセスの一部を自動化することができます。

ヒント

Office スクリプトを初めて使用する場合は、「 チュートリアル: Excel テーブルを作成して書式設定する」から始めてお勧めします。 Office スクリプトは TypeScript を使用します。このチュートリアルは、JavaScript や TypeScript について初級から中級レベルの知識を持つユーザーを対象としています。 JavaScript を使い慣れていない場合は、「Mozilla の JavaScript チュートリアル」から始めることをお勧めします。

前提条件

このチュートリアルでは、Office スクリプトにアクセスする必要があります。 [自動化] タブが表示されない場合は、 プラットフォームのサポート を確認してください。

セルを読み取る。

操作レコーダーで作成したスクリプトは、ブックに情報を書き込む操作のみを実行できます。 コード エディターを使用すると、ブックのデータを読み取ることも可能なスクリプトの編集と作成ができます。

まず、データを読み取り、読み取られた内容に基づいて動作するスクリプトを作成します。 チュートリアル全体を通して、サンプルの銀行取引明細書を使用します。 この明細書は、支払いと貸方がまとまった明細書です。 残念ながら、銀行は残高の変化を異なる方法で報告します。 支払い明細では、収入を負の貸方として記録し、支出を負の借方として記録しています。 貸方明細ではその逆になっています。

チュートリアルの残りの部分では、スクリプトを使用してこのデータを正規化します。 まず、ブックからデータを読み取る必要があります。

1. チュートリアルの残りの部分で使用したブックに新しいワークシートを作成します。

2. 次のデータをコピーし、新しいワークシートのセル A1 から始まるセル範囲に貼り付けます。

   日付	取引	説明	借方	貸方
   2019/10/10	支払い	Coho Vineyard	-20.05
   2019/10/11	貸方	The Phone Company	99.95
   2019/10/13	貸方	Coho Vineyard	154.43
   2019/10/15	支払い	外部預金		1000
   2019/10/20	貸方	Coho Vineyard - 返金		-35.45
   2019/10/25	支払い	Best For You Organics Company	-85.64
   2019/11/01	支払い	外部預金		1000

3. [ 自動化 ] タブに移動し、[ 新しいスクリプト] を選択します。

4. 書式設定をクリーンアップします。 これは財務ドキュメントであるため、スクリプトで [借方 ] 列と [ クレジット ] 列の数値の書式を変更して、値をドルの金額として表示させます。 また、スクリプトで列の幅をデータに合わせます。

   スクリプトの内容を次のコードで置き換えます。

   function main(workbook: ExcelScript.Workbook) {
       // Get the current worksheet.
       let selectedSheet = workbook.getActiveWorksheet();
   
       // Format the range to display numerical dollar amounts.
       selectedSheet.getRange("D2:E8").setNumberFormat("$#,##0.00");
   
       // Fit the width of all the used columns to the data.
       selectedSheet.getUsedRange().getFormat().autofitColumns();
   }
   

5. 次に、数値列の 1 つから値を読み取ります。 スクリプトの末尾 (終了の前) に次のコードを追加します }。

   // Get the value of cell D2.
   let range = selectedSheet.getRange("D2");
   console.log(range.getValues());
   

6. スクリプトを実行します。

7. コンソールに [Array[1]] が表示されます。 範囲は 2 次元のデータ配列であるため、これは数値ではありません。 この 2 次元の範囲は、コンソールに直接ログ記録されます。 コード エディターを使用すると、この配列の内容を表示できます。

8. 2 次元の配列がコンソールにログ記録すると、各行の下に列の値がグループ化されます。 青い三角形を選択して、配列のログを展開します。

9. 新しく表示された青い三角形を選択して、配列の 2 番目のレベルを展開します。 次のように表示されるはずです。

   

セルの値を変更する

スクリプトでデータを読み取ることができるので、そのデータを使用してブックを変更します。 関数を使用してセル D2 の値を正にします Math.abs 。 Math オブジェクトには、スクリプトでアクセスできる多くの関数が含まれています。 Math および他の組み込みオブジェクトの詳細については、「Office スクリプトでの組み込みの JavaScript オブジェクトの使用」を参照してください。

1. および setValue メソッドを使用してgetValue、セルの値を変更します。 これらの方法は、1 つのセルで使用できます。 複数のセル範囲を処理する場合は、getValues と setValues を使用します。 スクリプトの末尾に次のコードを追加します。

   // Run the `Math.abs` method with the value at D2 and apply that value back to D2.
   let positiveValue = Math.abs(range.getValue() as number);
   range.setValue(positiveValue);
   

   注:

   as のキーワードを使用して range.getValue() の返された値を number に キャスト しています。 範囲は、文字列、数値、ブール値の可能性があるため、これは必須です。 この例では、明らかに番号が必要です。

2. セル D2 の値が正の値になります。

列の値を変更する

1 つのセルの読み取りと書き込みの方法を理解したら、 デビット 列と クレジット 列全体で動作するようにスクリプトを一般化できます。

1. 1 つのセルにのみ影響するコード (前述の絶対値コード) を削除します。すると、スクリプトは次のようになります。

   function main(workbook: ExcelScript.Workbook) {
       // Get the current worksheet.
       let selectedSheet = workbook.getActiveWorksheet();
   
       // Format the range to display numerical dollar amounts.
       selectedSheet.getRange("D2:E8").setNumberFormat("$#,##0.00");
   
       // Fit the width of all the used columns to the data.
       selectedSheet.getUsedRange().getFormat().autofitColumns();
   }
   

2. 最後の 2 つの列の行を反復処理するループをスクリプトの最後に追加します。 スクリプトにより、各セルの値が現在の値の絶対値に設定されます。

   セルの位置を定義する配列は 0 から始まることにご注意ください。 したがって、セル A1 は range[0][0] になります。

   // Get the values of the used range.
   let range = selectedSheet.getUsedRange();
   let rangeValues = range.getValues();
   
   // Iterate over the fourth and fifth columns and set their values to their absolute value.
   let rowCount = range.getRowCount();
   for (let i = 1; i < rowCount; i++) {
       // The column at index 3 is column "4" in the worksheet.
       if (rangeValues[i][3] != 0) {
           let positiveValue = Math.abs(rangeValues[i][3] as number);
           selectedSheet.getCell(i, 3).setValue(positiveValue);
       }
   
       // The column at index 4 is column "5" in the worksheet.
       if (rangeValues[i][4] != 0) {
           let positiveValue = Math.abs(rangeValues[i][4] as number);
           selectedSheet.getCell(i, 4).setValue(positiveValue);
       }
   }
   

   スクリプトのこの部分は、いくつかの重要なタスクを実行します。 まず、指定された範囲の値と行数を取得します。 これにより、スクリプトは値を確認し、停止するタイミングを把握できます。 次に、指定された範囲を反復処理し、[借方] 列と [貸方] 列の各セルをチェックします。 最後に、セルの値が 0 ではない場合、その値が絶対値で置き換えられます。 スクリプトはゼロを無視するため、空白のセルをそのまま使用できます。

3. スクリプトを実行します。

   これで、銀行取引明細書に正の数値が適切に書式設定されているはずです。

   

次の手順

コード エディターを開き、「Excel での Office スクリプトのサンプル スクリプト」をいくつか試してみます。 Office スクリプトの作成の詳細については、「 Excel の Office スクリプトの基礎 」を参照してください。

次の一連の Office スクリプトのチュートリアルでは、Power Automate を使用した Office スクリプトの使用法について説明します。 「 Office スクリプトを Power Automate で実行 する」または 「チュートリアル: Power Automate フローからスプレッドシートを更新 して Office スクリプトを使用する Power Automate フローを作成する」の 2 つのプラットフォームを組み合わせる利点について説明します。