做报表最烦什么？数据区域会变。这两个函数就是专门解决这个问题的。
先认识一下这两位
INDIRECT：文本变引用
=INDIRECT("A1")
效果：返回A1单元格的值。
你可能会问：直接写 =A1不就行了？
关键是：INDIRECT的参数可以是公式生成的文本！
这就有了无限可能。
OFFSET：基于参照点偏移
=OFFSET(参照点, 向下几行, 向右几列, [高度], [宽度])
例子：
=OFFSET(A1, 2, 3)
从A1出发，往下走2行，往右走3列 → 返回D3的值。
=OFFSET(A1, 0, 0, 5, 3)
从A1出发，取一个5行3列的区域 → 返回A1:C5。
组合技1：动态下拉列表
场景： 你的数据在不断增加，下拉列表要能自动包含新增数据。
假设： A列是产品列表，数据在A2:A100，但只用了前20行。
普通做法： 定义名称时写死 =$A$2:$A$20，新增数据得手动改。
动态做法：
=OFFSET($A$2, 0, 0, COUNTA($A:$A)-1, 1)
解释：
- 从A2开始
- 向下偏移0行
- 高度 = A列非空单元格数 - 1（减去表头）
- 宽度1列
这样数据增加到100行，下拉列表自动包含100行。
组合技2：动态图表数据范围
图表的数据源如果是动态的，就不需要每次重新选了。
定义名称：
# 动态X轴（月份）
月份 = OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A:$A)-1)
# 动态Y轴（销售额）
销售额 = OFFSET(Sheet1!$B$2, 0, 0, COUNTA(Sheet1!$B:$B)-1)
图表数据源指向这两个名称，新增数据图表自动更新。
组合技3：跨表汇总（INDIRECT的骚操作）
场景： 你有12个月的报表，分别叫"1月"、"2月"..."12月"，要汇总到一张总表。
普通做法： 12个VLOOKUP或手动复制粘贴。
INDIRECT做法：
假设总表B1单元格是月份名称（如"3月"），要取该月表的C10单元格：
=INDIRECT(B1&"!C10")
如果月份是数字1-12，可以用：
=INDIRECT(A1&"月!C10")
进阶：动态求和
=SUM(INDIRECT(A1&"月!C2:C100"))
下拉A列的月份，自动汇总各月数据。
组合技4：二级联动下拉列表
这个应该是最经典的INDIRECT应用了。
场景：
- 第一级选择：省份
- 第二级选择：该省份的城市
设置步骤：
-
准备数据源（假设在Sheet2）：
- A列：省份列表（去重）
- 每个省份一个列，下面是该省的城市
-
给每个省份的城市区域定义名称：
- 选中"广东省"下面的城市 → 公式栏左边输入"广东省" → 回车
- 选中"浙江省"下面的城市 → 定义名称为"浙江省"
-
第一级下拉（省份）：
- 直接引用省份列表区域
-
第二级下拉（城市）：
=INDIRECT(A1)假设A1是第一级选择的省份
原理： 第一级选了"广东省"，INDIRECT("广东省")就返回广东省对应的城市列表。
组合技5：动态数据透视表源
数据透视表的数据源如果定义成动态的，新增数据后刷新透视表就行，不用重新选范围。
定义名称：
数据源 = OFFSET($A$1, 0, 0, COUNTA($A:$A), COUNTA($1:$1))
创建透视表时数据源写这个名称。
容易踩的坑
1. INDIRECT是易失性函数
每次Excel重算都会重新计算，数据量大的时候性能会下降。
2. OFFSET也是易失性的
同样存在性能问题，大数据慎用。
3. 引用的工作表不存在会报错
=IFERROR(INDIRECT(A1&"!A1"), "")
养成用IFERROR的好习惯。
4. 定义名称时要注意相对/绝对引用
在名称管理器里定义时，公式里的引用方式决定了它是相对还是绝对。
一个实用的综合案例
动态销售报表：
# 自动获取最近12个月的数据
=OFFSET($A$1, COUNTA($A:$A)-13, 0, 13, 5)
从A1开始，向下偏移到倒数第13行，取13行5列（表头+最近12个月）。
这个范围可以用在：
- 图表数据源
- 条件格式
- 数据验证
一句话总结
INDIRECT让文本变成活的引用，OFFSET让引用变成动态的区域。组合起来，你的报表就活了。
当然，易失性函数的代价是性能。数据量小随便用，数据量大要慎重。
这两个函数我当年研究了好久才明白。但一旦会了，做动态报表简直是降维打击。推荐配合名称管理器一起用，效果更佳。