RPAでExcelの数式を最終行まで自動コピーする方法|JobAuto

RPA × EXCEL FORMULA × LAST ROW

Excelの先頭行に入力した数式を、データがある最終行まで自動でコピー・貼り付けする方法を紹介します。JobAutoでは、売上CSVの取り込み、月別シートの作成、SUM関数の入力に続けて、「コピー」と「ペースト」の「最終行まで貼り付ける」を組み合わせます。

毎回データの件数が変わる集計表にも対応しやすい操作例です。操作画像22点を使い、設定手順を順に説明します。

RPAでExcelの数式を最終行まで反映する処理の流れ

この操作例では、売上のCSVファイルと集計用Excelブックを用意し、次の6段階で処理します。

  1. 売上集計用Excelファイルと売上CSVファイルを開く
  2. 前月の年月をシート名に使い、集計用Excelにシートを追加する
  3. CSVファイルのデータをコピーする
  4. 追加したExcelシートへCSVデータを貼り付ける
  5. 計算用のセルへSUM関数を入力する
  6. 数式セルをコピーし、「最終行まで貼り付ける」で各行へ展開する

このうち数式の一括展開は最後の2工程です。CSVの取り込みから実行することで、データ件数が変わる場合にも使える構成になります。

手順1|売上集計用ExcelとCSVファイルを開く

STEP 01 / OPEN WORKBOOKS

売上集計用Excelブックを開く

まず「Excelを開く」アクションを作成します。既存の売上集計用Excelファイルを指定し、アクションを実行します。

JobAutoで売上集計用のExcelファイルを開くアクション作成画面
エクセルを開くアクションを作成。
Excelを開くアクションで売上集計用Excelファイルを指定する画面

売上CSVファイルを開く

同じ「Excelを開く」アクションを利用して、売上データが入ったCSVファイルを開きます。CSVはExcel上で操作対象として扱います。

JobAutoでCSVを開くためにExcelを開くアクションを作成する画面
エクセルを開くアクションを作成。
Excelを開くアクションで売上CSVファイルのパスを指定する画面

ブック指定の確認:これ以降は集計用ExcelとCSVファイルの両方を操作します。「コピー」はCSV側、「シートの追加」「ペースト」「数式の入力」は集計用Excel側となるよう、ブック指定を確認してください。

手順2|前月の名前で新しいExcelシートを追加する

STEP 02 / CREATE MONTH SHEET

「変数の値を加工」でシート名を作成する

コントロールパネルから「変数の値を加工」アクションを作成します。今回の例では、ロボット実行時点の前月をシート名に利用します。

JobAutoの変数の値を加工アクションを作成する画面
変数の値を加工アクションを作成する。

「日付のセット」で前月月初を指定する

「日付操作」から「日付のセット」を追加し、「セットする日付」に「前月月初」を指定します。

変数の値を加工で日付操作の日付のセットを選ぶ画面
日付操作から日付のセットを選択する
日付のセットで前月月初を指定する設定画面
前月月初を選択する

「日付の書式」で年月表記に変換する

続けて「日付操作」から「日付の書式」を追加します。作成するシート名はこの操作例に合わせて「YYYY年MM月」形式とし、実際の加工結果を確認します。

変数の値を加工で日付の書式を指定する設定画面
日付操作から日付の書式を選択する
追加するシート名の年月書式を確認する画面
結果を見て、YYYY年MM月となっていることを確認しよう

作成したシート名を変数へ格納する

日付を加工した結果の格納先となる変数を指定します。この変数は後続の「シートの追加」で使います。

前月の年月を加工したシート名の格納先変数の設定画面
加工した値の格納先へ作成したシート名を格納する

「シートの追加」で月別シートを作る

集計用Excelブックを対象に「シートの追加」アクションを作成します。「追加するシート名」に、前月の年月を格納した変数を指定します。

JobAutoのExcel操作でシートの追加アクションを作成する画面
シートの追加アクションを作成する
シートの追加アクションに前月年月を格納した変数を設定する画面
変数を設定する

同名シートに注意:前月の月別シートが既にある場合、追加操作が想定どおり実行できないことがあります。月内に再実行する運用では既存シートの有無を確認してください。日付のセットと日付の書式変更も参照してください。

手順3|CSVの売上データをコピーする

STEP 03 / COPY CSV DATA

「コピー」アクションでCSVのセル範囲を指定する

コピー元のCSVファイルで必要なセル範囲を選択し、「コピー」アクションを作成します。

CSVファイルのデータをコピーするアクションを作成する画面
コピーアクションを作成する

アクションの設定画面で「対象となるセル」が必要なコピー範囲になっていることを確認します。CSVの行数が変動する場合は、毎回必要な行までコピーできるかをテストしてください。

CSVデータのコピー対象セル範囲を確認する設定画面
対象となるセルはアクションを作成するときに選択した範囲が自動的に設定される。

手順4|追加したExcelシートへCSVデータを貼り付ける

STEP 04 / PASTE CSV DATA

「ペースト」で貼り付け先を設定する

手順2で追加した月別シートへ移動し、CSVのデータを貼り付ける開始セルを選択して「ペースト」アクションを作成します。

追加したExcelシートにペーストアクションを作成する画面
ペーストアクションを作成する

設定画面では「ブック」「シート名」「基点となるセル」が意図した貼り付け先になっているか確認します。月別シート名は、作成時に利用した年月の変数を参照できます。

ペーストアクションでデータ貼り付けの基点セルを指定する画面
基点となるセルはアクションを作成するときに選択したセルが自動的に設定される。

貼り付け対象を確認:CSVのデータを貼り付ける段階と、数式を最終行まで貼り付ける段階は別のアクションです。CSVの貼り付けでは、取り込みたいデータ範囲・シート・開始セルを確認します。Excelのペーストアクションの公式マニュアルも参考になります。

手順5|集計用のセルにSUM関数を入力する

STEP 05 / ENTER FORMULA

「セルに値入力」で数式を指定する

CSVのデータを貼り付けた後、計算結果を表示する先頭セルを選択し、「セルに値入力」アクションを作成します。

JobAutoでExcelのセルに数式を入力するアクションを作成する画面
セルに値入力アクションを使って数式を入力する

アクションの入力値へ計算用の数式を設定します。この操作例ではExcelのSUM関数を利用しています。合計対象の列やセル範囲は、実際に扱う売上データに合わせて指定します。

セルに値入力アクションへSUM関数を入力する設定画面
合計を行いたいので、エクセルのSUM関数を入力

数式の確認:数式を入力したセルで計算結果が正しいことを確認してから、以降のコピーに進みます。相対参照・絶対参照($記号)の設定によって、貼り付けた各行で参照されるセルが変わります。目的の計算になるよう、数行で結果を検証してください。

手順6|入力した数式を最終行までコピー・ペーストする

STEP 06 / FILL FORMULA TO LAST ROW

数式を入力したセルをコピーする

手順5でSUM関数を入力したセルを選択して、「コピー」アクションを作成します。設定画面でコピー対象が数式の入ったセルになっているか確認してください。

SUM関数を設定したExcelセルのコピーアクションを作成する画面
コピーアクションを作成する
数式のコピー元セルを指定するコピーアクションの設定画面
対象となるセルはアクションを作成するときに選択した範囲が自動的に設定される。

数式セルの1行下からペーストする

次に、数式を入力したセルの1行下のセルを選択し、「ペースト」アクションを作成します。

数式を入力したセルの直下にペーストアクションを作成する画面
数式を入力したセルの1行下のセルを選択して、ペーストアクションを作成する

「最終行まで貼り付ける」にチェックを入れる

設定画面で「基点となるセル」を確認し、「最終行まで貼り付ける」にチェックを入れます。これにより、コピーした数式を、貼り付け対象となる列の最終行まで展開します。

Excelのペーストアクションで最終行まで貼り付けるを有効にする画面
最終行まで貼り付けるへチェックを入れると、自動的に最終行までコピーしたセルの内容がペーストされる

最終行の判定に注意:「最終行まで貼り付ける」は、貼り付け先の列の最終行までコピー内容を展開する設定です。データの位置や空白行によって想定した範囲にならない場合もあるため、実際にどこまで数式が入力されたかを確認してください。セル範囲を直接指定して貼り付ける方法との違いはペーストの公式マニュアルで確認できます。

数式が各行に設定され、SUM関数の参照範囲が行に応じて変わっているかを確認します。ファイルとして結果を残したい場合は、必要に応じて後続に「Excelを保存」アクションを追加してください。

最終行まで数式を自動入力する際の確認ポイント

  1. 集計用ExcelとCSVのブック指定が正しく分かれているか
  2. 前月の年月を使ったシート名が意図どおりで、同名シートが重複していないか
  3. CSVのデータ件数が変わっても必要なセル範囲を貼り付けられるか
  4. SUM関数の計算対象や相対参照・絶対参照の指定が正しいか
  5. 数式セルの直下をペースト開始位置に設定しているか
  6. 「最終行まで貼り付ける」の実行結果が、実際のデータ最終行と一致しているか
  7. 転記・計算したExcelブックを保存する必要があるか

まとめ|Excelの計算式をデータ最終行まで自動反映

JobAutoでは、売上CSVファイルをExcelへ取り込み、月別シートを作成し、最初の行に入力した数式を「コピー」「ペースト」で最終行まで展開できます。行数が変わるデータでも、毎回数式を手でドラッグしてコピーする作業を自動化できます。

CSVの取り込みやExcelの集計業務を自動化する際は、データ範囲、数式の参照先、貼り付け結果を確認しながら設定してください。

Excelの数式コピー・集計作業をJobAutoで自動化

Excelの数式入力やCSVの転記、レポート更新を自動化したい方は、JobAutoの30日間無料トライアルをご利用ください。実際のExcelファイルに合わせたロボット作成についてもご相談いただけます。

コメントを残す