教程：比较 Excel 文件和电子邮件对帐错误

在本教程中，我们将创建一个自动化项目，将包含发票的电子表格中的金额与包含每个供应商摘要的 CSV 文件中的金额进行比较。如果文件之间存在任何对帐错误，我们将创建一个包含详细信息的 CSV 文件，然后通过电子邮件发送该文件。

我们将创建一个包含以下活动的项目：

1. 两个“使用 Excel 文件”活动，指明包含发票的 Excel 文件以及我们要在其中对数据进行核对的文件。

2. 一系列“写入单元格”活动，用于将两个列标题添加到对帐 Excel 文件中，并计算一个供应商的发票总和以及该总和与初始摘要中金额之间的差额。我们将添加一个“复制范围”活动，将我们为一个供应商添加的公式复制到工作表中的所有行中，以便可以对所有供应商执行相同的计算，然后再添加一个“写入单元格”活动，将总差额添加到一个单元格。

3. 一个“IF 条件”活动，我们将在其中添加一个条件，如果存在对帐错误（差值之和大于 0），则应创建一个包含详细信息的 CSV 文件并将其通过电子邮件发送到指定的地址。对于电子邮件任务，我们将在“If”活动中添加一个“使用桌面 Outlook App”活动，以指明我们要从中发送邮件的 Outlook 帐户，并在“使用桌面 Outlook App”中添加“发送电子邮件”活动。

4. 第 1 步：设置项目并获取必要的文件。

   1. Create a new blank project using the default settings.
   2. Download and extract the archive with the automation project in this tutorial using the button at the bottom of this page. Copy the folder data to your project folder.

5. 第 2 步：将 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. 在第二个活动中：

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

      • 在“引用为”字段中，输入 Rec。

        您已指明将使用自动化中称为 Rec 的文件 reconcile.xlsx。

        

6. 第 3 步：将数据复制到对帐文件中。

   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. In the Read CSV activity:

      • 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] Invoices.

        您已指明要将数据从 CSV 文件 summary.csv 复制到摘要工作表中的记录文件。

   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 range 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 range field, and then select Rec > Invoices [Sheet]. Your selection is displayed in the field as [Rec] Invoices.

        您已指明要将复制的范围粘贴到“Rec”文件中的“Invoices”工作表中。

        

7. 步骤 4：进行必要的计算以比较数据。

   1. 在“活动”面板中，选择“Excel”选项卡，然后将“写入单元格”活动拖放到第二个“使用 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.

      • 单击字段右侧的“加号” ，然后选择“Rec” > “在 Excel 中指定” 。电子表格随即在 Excel 中打开。选择单元格C1 ，然后单击“确认” 。该字段将更新为您选择的“[Rec] Summary!C1” 。

        您已指明要让“Rec”文件的“Summary”工作表中 C 列的第一个单元格包含文本 Calculated Amount。

   3. 在前一个“写入单元格”活动下方添加第二个“写入单元格”活动，并在活动中执行以下操作::

      • 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), "").

      • 单击字段右侧的“加号” ，然后选择“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. 在前一个“写入单元格”活动下方添加第三个“写入单元格”活动，并在活动中执行以下操作::

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

      • 单击字段右侧的“加号” ，然后选择“Rec” > “在 Excel 中指定” 。电子表格随即在 Excel 中打开。选择单元格D1 ，然后单击“确认” 。该字段将更新为您选择的“[Rec] Summary!D1” 。

        您已指明要让“Rec”文件的“Summary”工作表中 D 列的第一个单元格包含文本 Difference。

   5. 在前一个“写入单元格”活动下方添加第四个“写入单元格”活动，并在活动中执行以下操作::

      • 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, "").

      • 单击字段右侧的“加号” ，然后选择“Rec” > “在 Excel 中指定” 。电子表格随即在 Excel 中打开。选择单元格D2 ，然后单击“确认” 。该字段将更新为您选择的“[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 range 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 range 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).

      • 单击字段右侧的“加号” ，然后选择“Rec” > “在 Excel 中指定” 。电子表格随即在 Excel 中打开。选择 Invoices 工作表，接着选择单元格F1 ，然后单击“确认” 。该字段将更新为您选择的“[Rec] Invoices!F1” 。

        您已指明要让“Rec”文件的“Invoices”工作表中的 F 列中第一个单元格包含公式 =SUM(Summary!D:D)。该公式会将“Summary”工作表的 D 列中的差异总和添加到“Invoices”工作表中的 F1 单元格中。

        

8. 步骤 5：如果差异总和大于 0，请发送一封电子邮件，其中包含有关对帐的详细信息。

   将发票和摘要数据复制到 reconcile.xlsx 文件，执行计算，并且由于发票中的总额与其中一位供应商的摘要中的金额不匹配，reconcile.xsls 文件的摘要表将会复制到名称包含今天日期的 CSV 文件中，该文件将附加到电子邮件中并发送到指定的地址。在该文件中，您可以通过单元格 D3 中大于 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, click Plus on the right side of the Condition field, and then select 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.

      • From the drop-down menu in the middle, select greater than.

      • 单击右侧字段右侧的“加号”，选择“数字”，输入 0 并单击两次“保存”。

        如果满足此条件，将执行您在 If 活动的 Then 分支中添加的活动。

   4. 在“活动”面板中，选择“CSV”选项卡，然后将“写入 CSV”活动拖放到“If 条件”活动的“Then”分支中。“写入 CSV”活动随即添加到“设计器”面板中。

   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 Project Notebook (Notes) > Date [Sheet] > YYYYMMDD [Cell]. The text in the Text Builder is updated to reconcile Excel Date!YYYYMMDD. Enter the text .csv at the end and click Save. The final text should be reconcile Excel Date!YYYYMMDD.csv.

      • 单击字段右侧的“加号” ，然后选择“Rec” > “Summary [工作表]” 。该字段将更新为您选择的“[Rec] 摘要” 。

        您已指明要在项目文件夹中创建一个 CSV 文件，其名称中要包含单词“reconcile”和今天的日期，然后将“Rec”文件中的“Summary”工作表复制到该文件中。

   6. 在“活动”面板中，选择“邮件”选项卡，然后将“使用桌面 Outlook App”活动拖放到“写入 CSV”活动下方 If 活动的 Then 分支中。

   7. 在活动中，“帐户”字段中已经选择默认的电子邮件帐户。如果您要使用其他帐户，请从下拉菜单中选择。

      在“引用为”字段中，保留默认值 Outlook 作为在自动化中引用该帐户时使用的名称。

   8. 在“活动”面板中，将“发送电子邮件”活动拖放到“使用桌面 Outlook App”活动中。

   9. 在"发送电子邮件"活动中：

      • Click Plus on the right side of the From 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 Is 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 Project Notebook (Notes) > Date [Sheet] > Today [Cell]. The text in the Text Builder is updated to Reconciliation errors for 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 Excel Date!YYYYMMDD.csv。例如，您可以打开“写入 CSV”活动的“写入哪个文件”字段的文本生成器，复制所有文本，然后粘贴在文件附件字段的文本生成器中。

        

   10. 在 StudioX 功能区中单击“保存”，以保存自动化，然后单击“运行”来执行此自动化。