如何使用表引用复制Excel公式

If you create Excel formulas that include table references, and then try to copy those formulas to adjacent columns, you might get incorrect results. See why that happens, and how to avoid those problems, when you copy Excel formulas with table references.

如果创建包含表引用的Excel公式，然后尝试将这些公式复制到相邻的列，则可能会得到错误的结果。 查看使用表引用复制Excel公式时发生这种情况的原因以及如何避免这些问题。

使用表引用进行问题复制公式 (Problem Copying Formulas with Table References)

This video shows the problem when copying formulas with table references, and two ways to prevent it. There are written steps below the video.

该视频显示了复制带有表引用的公式时的问题，以及防止该问题的两种方法。 视频下方有书面步骤。

演示地址

Video Timeline

影片时间表

• 00:00 Introduction
  00:00简介
• 00:46 SUBTOTAL Formula to Check Totals
  00:46计算总计的小计
• 01:19 SUMIFS Formula with Table References
  01:19具有表引用的SUMIFS公式
• 01:42 Copy the Formula Down One Row
  01:42将公式向下复制一行
• 02:13 Copy Across
  02:13跨副本
• 03:09 Copy With No Problems
  03:09复制没有问题
• 03:45 Get More Information
  03:45获取更多信息

具有表引用的Excel公式 (Excel Formula with Table References)

In the video, there's a sales summary, with a SUMIFS formula in cell C5. That formula shows the correct total for Bars sales in the East region.

在视频中，有一个销售摘要，在单元格C5中具有SUMIFS公式。 该公式显示了东部地区酒吧销售的正确总额。

Here's the formula in cell C5:

这是单元格C5中的公式：

=SUMIFS(Sales_Data[Quantity], Sales_Data[Region],$B5, Sales_Data[Category],C$4)

= SUMIFS(Sales_Data [Quantity]，Sales_Data [Region]，$ B5，Sales_Data [Category]，C $ 4)

The formula refers to heading cells in the sales summary:

该公式引用销售摘要中的标题单元格：

• Region name - $B5

  区域名称- $ B5

• Category name - C$4

  类别名称-C $ 4

The formula also refers to 3 columns in the Sales_Data table, which is on a different worksheet:

该公式还引用了Sales_Data表中的3列，该表位于不同的工作表上：

• Sales_Data[Quantity]

  Sales_Data [ 数量 ]

• Sales_Data[Region]

  Sales_Data [ 地区 ]

• Sales_Data[Category]

  Sales_Data [ 类别 ]

将公式复制到下一列 (Copy the Formula to Next Column)

The formula in cell C5 is working correctly, but see what happens if you try these steps:

C5单元格中的公式正常工作，但是请尝试以下步骤，看看会发生什么：

• Select cell C5, and point to the fill handle, in the cells bottom right corner
  选择单元格C5，并指向右下角单元格中的填充手柄
• When the pointer changes to a black plus sign, drag right, to put the formula in cell D5 (East – Cookies)
  当指针变为黑色加号时，向右拖动以将公式放入单元格D5中(东– Cookies)

The formula in cell D5 shows an incorrect total of zero. If you check the sales data, the quantity for East Cookie sales is  1425.

D5单元格中的公式显示错误的总数为零。 如果您查看销售数据，则East Cookie的销售数量是1425。

表引用右移 (Table References Shifted Right)

To troubleshoot the problem, click cell D5, at look in the formula bar.

要解决此问题，请在编辑栏中单击外观的单元格D5。

Instead of showing the same formula that was in cell C5, the table references have changed.

表引用已更改，而不是显示与单元格C5中相同的公式。

=SUMIFS(Sales_Data[TotalCost], Sales_Data[Category],$B5, Sales_Data[Product],D$4)

= SUMIFS(Sales_Data [ TotalCost ]，Sales_Data [ Category ]，$ B5，Sales_Data [ Product ]，D $ 4)

• Quantity changed to Total Cost

  数量更改为总成本

• Region changed to Category

  地区更改为类别

• Category changed to Product

  类别更改为产品

East isn't found in the Category column and Cookies isn't found in the Product column, so the SUMIFS formula result is zero.

在“类别”列中找不到“东部”，在“产品”列中找不到“ cookie”，因此SUMIFS公式的结果为零。

防止表引用出现问题 (Prevent Problems with Table References)

To prevent this problem of shifting table references, don't use the fill handle to copy a formula across columns.

为避免出现移动表引用的问题，请不要使用填充手柄跨列复制公式。

Instead, use one of the following methods – Fill Right, or Copy and Paste. The steps are shown below.:

而是使用以下方法之一–填充右或复制和粘贴。 步骤如下所示：

向右填充 (Fill Right)

• Select the cell with the formula, and the cells to the right, where you want to copy the formula – cells C5:F5 in the screen shot below
  选择包含公式的单元格，然后在右侧要复制公式的单元格中进行选择–下方屏幕截图中的单元格C5：F5

• Press Ctrl+R to fill the formula to the right

  按Ctrl + R在右侧填充公式

复制和粘贴 (Copy and Paste)

• Select the cell with the formula, and press Ctrl+C to copy it
  选择带有公式的单元格，然后按Ctrl + C进行复制
• Select all the cells where you want to copy the formula
  选择要复制公式的所有单元格
• Press Ctrl+V to paste the formula
  按Ctrl + V粘贴公式

()

获取样本文件 (Get the Sample File)

To get the sample file used in the video, go to the Excel Sum Function Examples page on my Contextures website.

若要获取视频中使用的示例文件，请转到Contextures网站上的“ Excel Sum函数示例”页面 。

In the Download section, look for the Table References workbook. The zipped file is in xlsx format, and does not contain any macros.

在“下载”部分中，查找“表引用”工作簿。 压缩文件为xlsx格式，不包含任何宏。

翻译自: https://contexturesblog.com/archives/2020/05/21/how-to-copy-excel-formulas-with-table-references/