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

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

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

1. 請求書を含む Excel ファイルを指定するための [ Excel ファイルを使用 ] アクティビティ 2 つと、データを照合するためのファイルを指定します。
2. 一連の [セルに書き込み ] アクティビティで照合 Excel ファイルに 2 つの列見出しを追加し、1 つのサプライヤーの合計請求額、およびその額と初期サマリー額との差額を計算します。1 つのサプライヤーを対象に追加した数式を、シートにあるすべての行にコピーする [ 範囲をコピー ] アクティビティを追加します。これにより、すべてのサプライヤーに対して同じ計算が実行されるようにします。つづいて、[セルに書き込み] アクティビティをもう 1 つ追加し、セルに差額の合計を書き込みます。
3. [ 条件分岐 (if )] アクティビティ: このアクティビティには、照合エラーが発生した場合 (差額の合計が 0 より大きい場合) に、その詳細を記述した CSV ファイルを作成し、指定したアドレスにメールで送信するという条件を追加します。メール タスクのため、[条件分岐 (if)] アクティビティ内に [ デスクトップ版 Outlook アプリを使用 ] アクティビティを追加してメールを送信する Outlook アカウントを指定します。さらに、[デスクトップ版 Outlook アプリを使用] 内に [メールを送信 ] アクティビティを追加します。
4. プロジェクトを設定し、必要なファイルを取得します。
   1. 既定の設定を使用して新しい空のプロジェクトを作成します。
   2. このチュートリアルでは、このページ下部にあるボタンを使用して、オートメーション プロジェクトを収めたアーカイブをダウンロードして展開します。プロジェクト フォルダーに data フォルダーをコピーします。
5. Excel ファイルをプロジェクトに追加します。
   1. デザイナー パネルで [アクティビティを追加] アイコンをクリックし、画面上部の検索ボックスで [Excel ファイルを使用] アクティビティを検索して、それを選択します。[Excel ファイルを使用] アクティビティがデザイナー パネルに追加されます。
   2. アクティビティで次の操作を行います。

      • [Excel ファイル] フィールドの横にある [ を参照] アイコンをクリックし、ファイル ファイルを参照して選択しますinvoices.xlsx

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

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

   3. [Excel ファイルを使用] アクティビティ内の [アクティビティを追加] アイコンをクリックし、もう 1 つ [Excel ファイルを使用] アクティビティを追加します。
   4. 2 番目のアクティビティで、次の操作を行います。

      • [Excel ファイル] フィールドの横にある [ を参照] アイコンをクリックし、ファイル ファイルを参照して選択しますreconcile.xlsx

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

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

        

6. データを照合ファイルにコピーする
   1. 2 番目の [Excel ファイルを使用] 内の [ アクティビティ を追加] アイコンをクリックし、画面上部の検索ボックスで [ CSV を読み込み] アクティビティを検索して、それを選択します。このアクティビティが [Excel ファイルを使用] アクティビティに追加されます。
   2. [CSV を読み込み] アクティビティで、以下の手順を実行します。

      • [読み込み元ファイル] フィールドの横にある [ 参照] アイコン をクリックし、ファイルsummary.csv を参照して選択します。

      • [ 出力先] フィールドの右側にある プラス 記号 をクリックし、[ Rec ] > Summary [シート]] を選択します。フィールドの内容が、選択した [Rec] Summary で更新されます。

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

   3. 2 番目の [Excel ファイルを使用] 内の [CSV を読み込み] アクティビティの下の [ アクティビティ を追加] アイコンをクリックします。つづいて、画面上部にある検索ボックスで [ 範囲をコピー ] アクティビティ を探して選択します。[Excel ファイルを使用] アクティビティに [範囲をコピー] アクティビティが追加されます。
   4. [範囲をコピー] アクティビティで、次の操作を行います。

      • [ソース] フィールドの右側にある プラス 記号 をクリックし、[ Invoices] > [ Invoices [シート]] を選択します。フィールドに、選択した [Invoices] Invoices (請求書) が表示されます。

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

      • [ターゲット] フィールドの右側にある プラス 記号 をクリックし、[ Rec] > Invoices [シート]] を選択します。フィールドに、選択した範囲 [Rec] Invoices が表示されます。

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

        

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

      • [ 書き込む内容] フィールドの右側にある プラス 記号 をクリックし、[ テキスト] を選択します。テキスト ビルダーにテキスト Calculated Amountを入力します。

      • [ 書き込む場所] フィールドの右側にある プラス 記号 をクリックし、[ Rec] > [ Excel 内で示す] を選択します。スプレッドシートが Excel で開きます。セル C1 を選択して [ 確定] をクリックします。フィールドの内容が、選択した [Rec] Summary で更新されます。C1.

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

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

      • [ 書き込む内容] フィールドの右側にある プラス 記号 をクリックし、[ テキスト] を選択します。テキスト ビルダーにテキスト =IF(A2 <> "", SUMIF(Invoices!B:B, A2, Invoices!C:C), "")を入力します。

      • [ 書き込む場所] フィールドの右側にある プラス 記号 をクリックし、[ Rec] > [ Excel 内で示す] を選択します。スプレッドシートが Excel で開きます。セル C2 を選択して [ 確定] をクリックします。フィールドの内容が、選択した [Rec] Summary で更新されます。C2.

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

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

      • [ 書き込む内容] フィールドの右側にある プラス 記号 をクリックし、[ テキスト] を選択します。テキスト ビルダーにテキスト Differenceを入力します。

      • [ 書き込む場所] フィールドの右側にある プラス 記号 をクリックし、[ Rec] > [ Excel 内で示す] を選択します。スプレッドシートが Excel で開きます。セル D1 を選択して [ 確定] をクリックします。フィールドの内容が、選択した [Rec] Summary で更新されます。D1.

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

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

      • [ 書き込む内容] フィールドの右側にある プラス 記号 をクリックし、[ テキスト] を選択します。テキスト ビルダーにテキスト =IF(A2 <> "", C2-B2, "")を入力します。

      • [ 書き込む場所] フィールドの右側にある プラス 記号 をクリックし、[ Rec] > [ Excel 内で示す] を選択します。スプレッドシートが Excel で開きます。セル D2 を選択し、[ 確定] をクリックします。フィールドの内容が、選択した [Rec] Summary で更新されます。D2.

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

   6. 最後の [セルに書き込み] アクティビティの下の [アクティビティを追加] アイコンをクリックし、画面上部にある検索ボックスで [範囲をコピー] アクティビティを探して選択します。[範囲をコピー] アクティビティがデザイナー パネルに追加されます。
   7. [範囲をコピー] アクティビティで、次の操作を行います。

      • [ソース] フィールドの右側にある プラス 記号 をクリックし、[ Rec] > [ Excel 内で示す] を選択します。スプレッドシートが Excel で開きます。範囲 C2:D2 を選択して [ Confirm (確定)] をクリックします。フィールドの内容が、選択した [Rec] Summary で更新されます。C2:D2です。

      • [ ターゲット] フィールドの右側にある プラス 記号 をクリックし、[ Rec] > [ Excel 内で示す] を選択します。スプレッドシートが Excel で開きます。範囲 C3-D20 を選択して [ 確定] をクリックします。フィールドの内容が、選択した [Rec] Summary で更新されます。C3-D20。

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

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

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

      • [ 書き込む内容] フィールドの右側にある プラス 記号 をクリックし、[ テキスト] を選択します。テキスト ビルダーに数式 =SUM(Summary!D:D)を入力します。

      • [ 書き込む場所] フィールドの右側にある プラス 記号 をクリックし、[ Rec] > [ Excel 内で示す] を選択します。スプレッドシートが Excel で開きます。Invoices シートを選択してから、セル F1 を選択して [ Confirm (確定)] をクリックします。フィールドの内容が、選択した [Rec] Invoices (請求書) で更新されます。F1 です。

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

        

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

   1. 最後の[セルに書き込み] アクティビティの下の [ アクティビティを追加] アイコンをクリックし、画面上部にある検索ボックスで [ 条件分岐 (if)] を探して選択します。[条件分岐 (if)] アクティビティがデザイナー パネルに追加されます。

   2. [条件分岐 (if)] アクティビティで、[ 条件] フィールドの横にある [ 詳細エディターで開く を選択して条件ビルダーを開きます。

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

      • 左側のフィールドの右側にあるプラス記号 をクリックし、[Rec] > [Excel 内で示す] を選択します。スプレッドシートが Excel で開きます。Invoices シートを選択してから、セル F1 を選択して [Confirm (確定)] をクリックします。フィールドの内容が、選択した [Rec] Invoices!F1 で更新されます。

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

      • 右側のフィールドの右側にあるプラス記号 をクリックし、[数値] を選択します。0 を入力して [保存] を 2 回クリックします。

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

   4. [アクティビティ] パネルの [ CSV ] タブを選択し、[ CSV に書き込み ] アクティビティを [条件分岐 (if)] アクティビティの [Then] 分岐にドラッグ アンド ドロップします。[CSV に書き込み] アクティビティがデザイナー パネルに追加されます。

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

      • [書き込み先ファイル] フィールドの右側にある プラス 記号 をクリックし、[ テキスト] を選択します。テキスト ビルダーに「 reconcile-」と入力し、テキスト ビルダーの右側にある プラス メニューから、[ ノートブック > Date [シート] ] > [YYYYMMDD [セル]] を選択します。テキスト ビルダーのテキストが「 reconcile-[Notes] Date!YYYYMMDD」に更新されます。テキスト .csv を末尾に入力し、[ 保存] をクリックします。最終的なテキストは reconcile-[Notes] Date!YYYYMMDD.csvになります。

      • [書き込み元] フィールドの右側にある プラス 記号 をクリックし、[ Rec >の Summary[シート]] を選択します。フィールドの内容が、選択した [Rec] Summary で更新されます。

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

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

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

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

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

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

      • [ アカウント] フィールドの右側にある プラス 記号 をクリックし、[ Outlook] を選択します。

      • [ 宛先] フィールドの右側にある プラス 記号 をクリックし、[ テキスト] を選択します。[テキスト ビルダー] ウィンドウに、メールの宛先とするメール アドレスを入力します。たとえば、自分宛てにメールが送信されるように、自分のアドレスを入力することもできます。[ 下書きとして保存 ] オプションを選択したままにすると、メールは送信されず Outlook の [下書き] フォルダーに保存されます。

      • [ 件名] フィールドの右側にある プラス 記号 をクリックし、[ テキスト] を選択します。[テキスト ビルダー] ウィンドウに「 Reconciliation errors for」と入力し、テキスト ビルダーの右側にある プラス記号 メニューから、[ ノートブック > Date [シート] ] > [Today [セル]] を選択します。テキスト ビルダーのテキストが「 Reconciliation errors for [Notes] Date!Today」に更新されます。

      • [ 本文] フィールドの右側にある プラス 記号 をクリックし、[ テキスト] を選択します。[テキスト ビルダー] ウィンドウに、メールの本文となるテキストを入力します。たとえば、「 One or more suppliers have reconciliation errors. See attached CSV file for details.です。

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

        

   10. StudioX のリボンで [ 保存 ] をクリックしてオートメーションを保存し、[ 実行 ] をクリックしてオートメーションを実行します。請求書と集計データが reconcile.xlsx ファイルにコピーされ、計算が実行され、請求書の合計金額がいずれかのサプライヤーの集計金額と一致しないため、reconcile.xsls ファイルの Summary シートが、名前に今日の日付を含む CSV ファイルにコピーされ、ファイルが電子メールに添付され、指定されたアドレスに送信されます。ファイルでは、照合エラーのあるサプライヤーを、0より大きいセルD3の値で識別できます。 サンプルをダウンロード