20.excel制作图表，图表跟随数据行数的变化而自动更新

1.需求

在excel中依据某几列数据绘制图表，但是这几列数据的行数不固定。要求图表跟随数据行数的变化而自动更新。

2.制作图表

2.1 插入图表

如下图所示，已插入图表：

2.2 编辑数据源

如上，已经插入了图表，设置A列数据为横坐标轴，且B列数据为主纵向坐标轴，C列数据为次纵向坐标轴；如果需要调整纵向坐标和横向坐标数据源，可右击图表进行编辑：

2.3 图表数据长度不会自动更新

如下图所示，当新增两行数据时，图表的横坐标轴并没有新增，纵向坐标轴数据也没有发生变化。

3.制作数据长度动态更新图表

3.1 背景知识

3.1.1 计数函数

• COUNTA：对不为空的单元格进行计数

• COUNT：对包含数字的单元格进行计数。

• COUNTBLANK：对空白单元格进行计数。

• COUNTIF：对满足指定条件的单元格进行计数。

3.1.1 offset函数

• `OFFSET` 函数是 Excel 中一个强大的引用函数，用于从指定的起始点返回一个单元格或单元格区域的引用。它可以根据给定的行数和列数偏移量，动态地引用不同的单元格或区域。
• 函数语法：OFFSET(reference, rows, cols, [height], [width])

  • -reference: 起始点（参考单元格或区域）。

  • -rows: 从起始点向上或向下偏移的行数。正数表示向下偏移，负数表示向上偏移。

  • -cols: 从起始点向左或向右偏移的列数。正数表示向右偏移，负数表示向左偏移。

  • - [height]: （可选）返回区域的行数。如果省略，默认与起始区域的行数相同。

  • - [width]: （可选）返回区域的列数。如果省略，默认与起始区域的列数相同。

3.2 添加名称管理器

如上，添加了3个名称变量。其中引用位置的公式为：

• xData：=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)  其含义为选择从sheet1中A2位置开始，行数为A列非空单元格的个数，共1列的区域。【即就是A列不包含首行标题的所有非空单元格】
• yData1：=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B),1) 其含义同上。【即就是B列不包含首行标题的所有非空单元格】
• yData2：=OFFSET(Sheet1!$C$2,0,0,COUNTA(Sheet1!$C:$C),1) 其含义同上。【即就是C列不包含首行标题的所有非空单元格】

3.2 设置数据源

添加了名称管理器，就可以编辑数据源引用名称管理器中的变量了。

3.2.1 x轴

（1）右击图表，编辑数据源

（2）设置x轴引用的区域为xData。即=Sheet1!xData

3.2.2 y轴

（1）编辑

（2）设置主坐标y轴引用yData1，次坐标y轴引用yData2；即主坐标y轴=Sheet1!yData1，次坐标y轴=Sheet1!yData2

3.3 图表效果

如下，动态添加两行或删减两行，图表也会跟随着变化

3.4 补充

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B),1)

名称管理器中的引用公式用到的是COUNTA()，它只会统计非空单元格，当B列或C列存在空值时，坐标图就会忽略此行不展示在图表中（即x轴和y轴都略过了此行）。

如果在图表的坐标轴中也要体现空单元格，可以将COUNTA(Sheet1!$B:$B)改为COUNTA(Sheet1!$A:$A)，即改为引用A列数据的长度。至少保证数据中的A列（即X轴）是完整的不会存在空值。