不使用VBA宏如何动态设置Excel图表的数据源范围

前置准备 #

• 找两个空白单元格作为参数配置位：
  • 第一个单元格填写有效区间的最小值（例：填入2）
  • 第二个单元格填写有效区间的最大值（例：填入5）
    后续调整区间只需修改这两个单元格的数值即可。

方案1：兼容全Excel版本的定义名称法（适配所有旧版Excel） #

假设你的原始数据结构为：A列存分类标签，B列存对应数值，表头在第1行，数据范围为A2:B100（可根据实际数据量调整引用范围）

1. 打开「公式」选项卡，点击「名称管理器」，新建两个名称：
   • 名称：动态分类，引用位置填写公式：

     =OFFSET($A$1,MATCH(最小值单元格,$B:$B,0)-1,0,MATCH(最大值单元格,$B:$B,0)-MATCH(最小值单元格,$B:$B,0)+1,1)
     

   • 名称：动态数值，引用位置填写公式：

     =OFFSET($B$1,MATCH(最小值单元格,$B:$B,0)-1,0,MATCH(最大值单元格,$B:$B,0)-MATCH(最小值单元格,$B:$B,0)+1,1)
     

2. 插入空白柱状图，右键点击图表选择「选择数据」：
   • 系列值选择你定义的动态数值名称
   • 水平轴标签选择你定义的动态分类名称
3. 确认后图表即可生效，修改两个参数单元格的区间值，图表会自动切换对应的数据范围。

方案2：Excel 365/2021专属简化方案 #

直接用FILTER函数生成动态筛选后的数据源：

1. 在空白列（例：D列）输入公式筛选分类：

   =FILTER(A2:A100,(B2:B100>=最小值单元格)*(B2:B100<=最大值单元格))
   

2. 相邻空白列（例：E列）输入公式筛选对应数值：

   =FILTER(B2:B100,(B2:B100>=最小值单元格)*(B2:B100<=最大值单元格))
   

3. 选中筛选出来的两列数据插入柱状图即可，修改区间参数时筛选结果会自动溢出更新，图表同步变更。

注意事项 #

• 确保你的数值列是按规则排序的，匹配连续区间的需求
• 若数值列有重复值，可调整MATCH函数的第三个参数：升序排列填1，降序排列填-1，精确匹配填0
• 公式内的单元格引用需保留绝对引用符号$，避免引用范围偏移

内容的提问来源于stack exchange，提问作者Kingsley Obeng