チュートリアル: Excel ファイルを比較し、照合エラーのメールを送信する

このチュートリアルでは、請求書が保存されたスプレッドシートにある金額と、サプライヤーごとのサマリーが保存された CSV ファイルにある金額を比較するうかプロジェクトを作成します。これらのファイル間に何らかの照合エラーが発生した場合、その詳細情報を記述した CSV ファイルを作成して、メールで送信します。

以下のアクティビティから成るプロジェクトを作成します。

1. Two Use Excel File activities to indicate the Excel file with the invoices and the file where we want to reconcile the data.
2. A series of Write Cell activities to add two column headers to the reconciliation Excel file and to calculate the sum of invoices for one supplier and the difference between that sum and the amount in the initial summary. We will add a Copy Range activity to copy the formulas we added for one supplier to all the rows in the sheet so that we can perform the same calculations for all the suppliers, and then add one more Write Cell activity to add the total difference to a cell.
3. An If activity where we will add a condition that if there are reconciliation errors (the sum of differences is greater than 0), a CSV file with details should be created and emailed to a specified address. For the email tasks, inside the If activity we will add a Use Desktop Outlook App activity to indicate the Outlook account we want to send the mail from and a Send Email activity inside Use Desktop Outlook App.
4. プロジェクトを設定し、必要なファイルを取得します。
   1. Create a new blank project using the default settings.
   2. このチュートリアルでは、このページ下部にあるボタンを使用して、オートメーション プロジェクトを収めたアーカイブをダウンロードして展開します。プロジェクト フォルダーに data フォルダーをコピーします。
5. Excel ファイルをプロジェクトに追加します。
   1. Click Add Activity in the Designer panel, and then find the Use Excel File activity in the search box at the top of the screen and select it. A Use Excel File activity is added to the Designer panel.
   2. アクティビティで次の操作を行います。

      • Click Browse next to the Excel file field, and then browse to and select the file invoices.xlsx

      • [参照名] フィールドに「Invoices」と入力します。

        これで、このオートメーションでは名前を Invoices としたファイル invoices.xlsx を操作対象として指定したことになります。

   3. Click Add Activity inside the Use Excel File activity, and then add a another Use Excel File activity.
   4. 2 番目のアクティビティで、次の操作を行います。

      • Click Browse next to the Excel file field, and then browse to and select the file reconcile.xlsx

      • [参照名] フィールドに「Rec」と入力します。

        これで、このオートメーションでは名前を Rec としたファイル reconcile.xlsx を操作対象として指定したことになります。

        

6. データを照合ファイルにコピーする
   1. Click Add Activity inside the second Use Excel File, and then find the Read CSV activity in the search box at the top of the screen and select it. The activity is added inside the Use Excel File activity.
   2. [CSV を読み込み] アクティビティで、以下の手順を実行します。

      • Click Browse next to the Read from file field, and then browse to and select the file summary.csv.

      • Click Plus on the right side of the Output to field, and then select Rec > Summary [Sheet]. The field is updated with your selection [Rec] Summary.

        これで、CSV ファイル summary.csv のデータを、Rec ファイルの Summary シートにコピーするように指定したことになります。

   3. Click Add Activity inside the second Use Excel File below the Read CSV activity, and then find the Copy Range activity in the search box at the top of the screen and select it. A Copy Range activity is added inside the Use Excel File activity.
   4. [範囲をコピー] アクティビティで、次の操作を行います。

      • Click Plus on the right side of the Source field, and then select Invoices > Invoices [Sheet]. Your selection is displayed in the field as [Invoices] Invoices.

        これで、Invoices ファイルから Invoices シートをコピーするように指定したことになります。

      • Click Plus on the right side of the Destination field, and then select Rec > Invoices [Sheet]. Your selection is displayed in the field as [Rec] Invoices.

        これで、コピーした範囲を Rec ファイルの Invoices シートに貼り付けるように指定したことになります。

        

7. データを比較するために必要な計算を行います。
   1. [アクティビティ] パネルで [Excel] タブを選択し、[セルに書き込み] アクティビティを 2 番目の [Excel ファイルを使用] 内の [範囲をコピー] の下にドラッグ アンド ドロップします。[セルに書き込み] アクティビティがデザイナー パネルに追加されます。
   2. [セルに書き込み] アクティビティで、以下の手順を実行します。

      • Click Plus on the right side of the What to write field, and then select Text. In the Text Builder, enter the text Calculated Amount.

      • Click Plus on the right side of the Where to write field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the cell C1, and then click Confirm. The field is updated with your selection [Rec] Summary!C1.

        これで、Rec ファイルにある Summary シートの C 列の一番上のセルに、テキスト「Calculated Amount」を書き込むように指定したことになります。

   3. 前の [セルに書き込み] アクティビティの下に、もう 1 つ同じアクティビティを追加します。

      • Click Plus on the right side of the What to write field, and then select Text. In the Text Builder, enter the text =IF(A2 <> "", SUMIF(Invoices!B:B, A2, Invoices!C:C), "").

      • Click Plus on the right side of the Where to write field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the cell C2, and then click Confirm. The field is updated with your selection [Rec] Summary!C2.

        これで、Rec ファイルにある Summary シートの C2 セルに、数式「=IF(A2 <> "", SUMIF(Invoices!B:B, A2, Invoices!C:C), "")」を書き込むように指定したことになります。この数式は、サプライヤーのセル (A2) が空でないことを確認し、Summary シートの A2 セルに記述されたサプライヤーについて、Invoices シートの C 列の値を合計します。

   4. 前の [セルに書き込み] アクティビティの下に、3 番目の同じアクティビティを追加して、そこで以下の手順を実行します。

      • Click Plus on the right side of the What to write field, and then select Text. In the Text Builder, enter the text Difference.

      • Click Plus on the right side of the Where to write field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the cell D1, and then click Confirm. The field is updated with your selection [Rec] Summary!D1.

        これで、Rec ファイルにある Summary シートの D 列の一番上のセルに、テキスト「Difference」を書き込むように指定したことになります。

   5. 前の [セルに書き込み] アクティビティの下に、4 番目の同じアクティビティを追加して、そこで以下の手順を実行します。

      • Click Plus on the right side of the What to write field, and then select Text. In the Text Builder, enter the text =IF(A2 <> "", C2-B2, "").

      • Click Plus on the right side of the Where to write field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the cell D2, and then click Confirm. The field is updated with your selection [Rec] Summary!D2.

        これで、Rec ファイルにある Summary シートの D2 セルに、数式「=IF(A2 <> "", C2-B2, "")」を書き込むように指定したことになります。この数式は、サプライヤーのセル (A2) が空でないことを確認し、Summary シートのセル C2 と B2 の値の差を計算します。

   6. Click Add Activity below the last Write Cell activity, and then find the Copy Range activity in the search box at the top of the screen and select it. A Copy Range activity is added to the Designer panel.
   7. [範囲をコピー] アクティビティで、次の操作を行います。

      • Click Plus on the right side of the Source field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the range C2:D2, and then click Confirm. The field is updated with your selection [Rec] Summary!C2:D2.

      • Click Plus on the right side of the Destination field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the range C3-D20, and then click Confirm. The field is updated with your selection [Rec] Summary!C3-D20.

      • [コピーする内容] ドロップダウン メニューの [数式] を選択します。

        これにより、コピーしたセル範囲 C2:D2 の数式を、シートにある同じ列の下の行にコピーするように指定したことになります。

   8. [アクティビティ] パネルの [Excel] タブを選択し、[セルに書き込み] アクティビティを最後の [範囲をコピー] アクティビティの下にドラッグ アンド ドロップします。[セルに書き込み] アクティビティがデザイナー パネルに追加されます。
   9. [セルに書き込み] アクティビティで、以下の手順を実行します。

      • Click Plus on the right side of the What to write field, and then select Text. In the Text Builder, enter the formula =SUM(Summary!D:D).

      • Click Plus on the right side of the Where to write field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the sheet Invoices, then the cell F1, and then click Confirm. The field is updated with your selection [Rec] Invoices!F1.

        これで、Rec ファイルにある Invoices シートの F 列の一番上のセルに、数式「=SUM(Summary!D:D)」を書き込むように指定したことになります。この数式は、Summary シートの D 列に表示された差の合計であり、Invoices シートの F1 セルに追加されます。

        

8. 差額の合計が 0 より大きい場合に、照合に関する詳細をメールで送信する

   1. Click Add Activity below the last Write Cell activity, and then, in the search box at the top of the screen, locate and select If. An If activity is added to the Designer panel.

   2. In the If activity, select Open in Advanced Editor next to the Condition field to open the Condition Builder.

   3. 次の手順に従い、[条件ビルダー] で「差額の合計が 0 より大きい場合」という条件を追加します。

      • Click Plus on the right side of the field on the left, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the sheet Invoices, then the cell F1, and then click Confirm. The field is updated with your selection [Rec] Invoices!F1.

      • 中央のドロップダウン メニューから [次の値より大きい] を選択します。

      • Click Plus on the right side of the field on the right, select Number, enter 0 and click Save twice.

        この条件が満たされた場合、[条件分岐 (if)] アクティビティの [Then] 分岐に追加したアクティビティが実行されます。

   4. In the Activities panel, select the CSV tab, and then drag the Write CSV activity and drop it in the Then branch of the If activity. A Write CSV activity is added to the Designer panel.

   5. [CSV に書き込み] アクティビティで、以下の手順を実行します。

      • Click Plus on the right side of the Write to what file field, and then select Text. In the Text Builder, enter reconcile-, and then from the Plus menu on the right side of the Text Builder select Notebook > Date [Sheet] > YYYYMMDD [Cell]. The text in the Text Builder is updated to reconcile-[Notes] Date!YYYYMMDD. Enter the text .csv at the end and click Save. The final text should be reconcile-[Notes] Date!YYYYMMDD.csv.

      • Click Plus on the right side of the Write from field, and then select Rec > Summary [Sheet]. The field is updated with your selection [Rec] Summary.

        これで、reconcile という語と今日の日付を名前に持つ CSV ファイルをプロジェクト フォルダーに作成し、そのファイルにRec ファイルの Summary シートをコピーするように指定したことになります。

   6. [アクティビティ] パネルで [メール] タブを選択し、[デスクトップ版 Outlook アプリを使用] アクティビティを、[条件分岐 (if)] アクティビティの [Then] 分岐内の [CSV に書き込み] アクティビティの下にドラッグ アンド ドロップします。

   7. このアクティビティの [アカウント] フィールドには、あらかじめ既定のメール アカウントが入力されています。別のアカウントを使用する場合は、ドロップダウン メニューから選択します。

      [参照名] フィールドは既定値 Outlook のままとして、オートメーションでアカウントを参照するときは、この名前を使用します。

   8. [アクティビティ] パネルで [メールを送信] アクティビティをドラッグし、[デスクトップ版 Outlook アプリを使用] アクティビティ内にドロップします。

   9. [メールを送信] アクティビティで、以下の手順を実行します。

      • Click Plus on the right side of the Account field, and then select Outlook.

      • Click Plus on the right side of the To field, and then select Text. In the Text Builder window, enter an email address where to send the email. For example, you can enter your own email address to send the email to yourself. If you leave the Save as draft option selected, the automation does not send the email, it instead saves the email to the Outlook Drafts folder.

      • Click Plus on the right side of the Subject field, and then select Text. In the Text Builder window, enter Reconciliation errors for, and then from the Plus menu on the right side of the Text Builder select Notebook > Date [Sheet] > Today [Cell]. The text in the Text Builder is updated to Reconciliation errors for [Notes] Date!Today.

      • Click Plus on the right side of the Body field, and then select Text. In the Text Builder window, enter text for the body of the email, for example One or more suppliers have reconciliation errors. See attached CSV file for details..

      • [添付ファイル]で [ファイル] を選択し、手順 5 で [CSV に書き込み] アクティビティに使用したものと同じファイル名「reconcile-[Notes] Date!YYYYMMDD.csv」を入力します。たとえば、[CSV に書き込み] アクティビティの [書き込み先ファイル] フィールドでテキスト ビルダーを開き、すべてのテキストをコピーし、ファイル添付フィールドのテキスト ビルダーに貼り付けることができます。

        

   10. Click Save in the StudioX ribbon to save the automation, then click Run to execute the automation.The invoices and summary data are copied to the reconcile.xlsx file, the calculations are performed and, because the total amount in the invoices does not match the one in the summary for one of the suppliers, the Summary sheet of the reconcile.xsls file is copied to a CSV file whose name includes today's date, the file is attached to an email and sent to the specified address. In the file, you can identify the supplier with reconciliation errors by the value in cell D3 that is greater than 0. Download example