Excel数据透视表怎么防止数据源变动出错_Excel数据透视表数据源动态引用设置【攻略】

推荐首选将原始数据转为结构化表格（Ctrl+T），可使数据透视表自动识别新增行；也可用OFFSET或INDEX+COUNTA定义动态名称区域，或通过Power Query自动扩展数据源。

如果您在Excel中创建了数据透视表，但后续向原始数据区域新增行或列后刷新时出现“引用无效”或字段丢失等错误，则很可能是数据源范围未随数据变动自动扩展。以下是防止此类问题的多种设置方法：

一、将原始数据转为结构化表格（推荐首选）

将数据区域转换为Excel内置的“表格”对象（Ctrl+T），可使数据透视表自动识别新增行，并在刷新时包含全部最新数据，无需手动调整源范围。

1、单击原始数据区域任意一个单元格。

2、按 Ctrl + T 快捷键，或在【插入】选项卡中点击【表格】。

3、在弹出的对话框中确认勾选“表包含标题”，点击【确定】。

4、选中该表格任一单元格，插入数据透视表——此时数据源将自动引用整张表格，如 表1[#全部]。

二、使用OFFSET函数定义动态名称区域

通过公式创建可随数据增长自动伸缩的命名区域，再将该名称作为数据透视表的数据源，实现完全动态引用。

1、切换到【公式】选项卡，点击【名称管理器】→【新建】。

2、在“名称”栏输入 动态数据源；在“引用位置”栏输入：
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))（请将Sheet1替换为实际工作表名）。

3、点击【确定】保存名称。

4、插入数据透视表时，在“创建数据透视表”对话框中选择“使用外部数据源”→“选择连接”→“浏览更多”→“新建名称”，输入刚定义的名称“动态数据源”。

标签： excel