Excel 其他引用函数 #

┌─────────────────────────────────────────────────────────────┐
│                    引用函数一览                              │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  INDIRECT   文本转引用  =INDIRECT("A1")                     │
│  OFFSET     偏移引用    =OFFSET(A1, 1, 2)                   │
│  ADDRESS    创建地址    =ADDRESS(1, 1)                      │
│  COLUMN     列号        =COLUMN(A1)                         │
│  ROW        行号        =ROW(A1)                            │
│  COLUMNS    列数        =COLUMNS(A1:C1)                     │
│  ROWS       行数        =ROWS(A1:A3)                        │
│                                                             │
└─────────────────────────────────────────────────────────────┘

=INDIRECT(ref_text, [a1])

参数：
- ref_text: 引用文本
- a1: 引用样式（TRUE=A1样式，FALSE=R1C1样式）

返回：文本对应的单元格引用

=INDIRECT("A1")              返回A1单元格的值
=INDIRECT("Sheet2!A1")       返回Sheet2的A1值
=INDIRECT(B1 & "!" & "A1")   动态构建工作表引用

根据单元格内容创建引用：

    A          B          C
1   A1         100        
2   B1         200        

=INDIRECT(A1)  → 100（引用A1单元格）
=INDIRECT(A2)  → 200（引用B1单元格）

动态工作表引用：

    A          B
1   Sheet1     =INDIRECT(A1 & "!A1")
2   Sheet2     =INDIRECT(A2 & "!A1")

根据A列的工作表名，引用对应工作表的A1单元格

动态范围引用：
=SUM(INDIRECT("A1:A" & B1))

根据B1的值确定范围：
如果B1=10，则计算A1:A10的和

动态工作表汇总：
=SUMPRODUCT(INDIRECT("'" & 工作表列表 & "'!A1"))

多工作表数据汇总

多工作表汇总：
    A            B
1   工作表       A1值
2   Sheet1       =INDIRECT(A2 & "!A1")
3   Sheet2       =INDIRECT(A3 & "!A1")
4   Sheet3       =INDIRECT(A4 & "!A1")

INDIRECT 是易失性函数：
- 每次工作表计算都会重新计算
- 可能影响性能
- 关闭工作簿时引用会失效

建议：
- 大量数据时谨慎使用
- 考虑使用 INDEX 替代

=OFFSET(reference, rows, cols, [height], [width])

参数：
- reference: 基准单元格
- rows: 行偏移量（正数向下，负数向上）
- cols: 列偏移量（正数向右，负数向左）
- height: 返回范围的高度（可选）
- width: 返回范围的宽度（可选）

返回：偏移后的单元格引用

=OFFSET(A1, 1, 0)            A1向下1行，即A2
=OFFSET(A1, 0, 1)            A1向右1列，即B1
=OFFSET(A1, 2, 3)            A1向下2行向右3列，即D3
=OFFSET(A1, -1, 0)           A1向上1行（可能超出范围）

=OFFSET(A1, 0, 0, 3, 1)      返回A1:A3范围
=OFFSET(A1, 0, 0, 1, 3)      返回A1:C1范围
=OFFSET(A1, 0, 0, 3, 3)      返回A1:C3范围

配合其他函数：
=SUM(OFFSET(A1, 0, 0, 5, 1))  求A1:A5的和
=AVERAGE(OFFSET(A1, 0, 0, 10, 1))  求A1:A10的平均值

创建动态命名范围：

名称：动态数据
引用：=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1)

根据数据量自动调整范围

滚动求和（最近N个值）：
=SUM(OFFSET(A1, COUNTA(A:A)-5, 0, 5, 1))

计算最后5个数据的和

动态图表数据源：
=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 2)

自动适应数据量的图表范围

滚动平均值：
    A          B
1   数据       最近3个平均
2   10         =AVERAGE(OFFSET(A2, 0, 0, MIN(ROW(A2), 3), 1))
3   20         =AVERAGE(OFFSET(A3, MAX(0, ROW(A3)-3), 0, 3, 1))
4   30         → 20（(10+20+30)/3）
5   40         → 30（(20+30+40)/3）

OFFSET 是易失性函数：
- 每次工作表计算都会重新计算
- 可能影响性能

建议：
- 大量数据时谨慎使用
- 考虑使用 INDEX 替代

=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

参数：
- row_num: 行号
- column_num: 列号
- abs_num: 引用类型（1=$A$1, 2=A$1, 3=$A1, 4=A1）
- a1: 引用样式（TRUE=A1, FALSE=R1C1）
- sheet_text: 工作表名

返回：单元格地址文本

=ADDRESS(1, 1)               → "$A$1"
=ADDRESS(1, 1, 4)            → "A1"
=ADDRESS(1, 1, 2)            → "A$1"
=ADDRESS(1, 1, 3)            → "$A1"
=ADDRESS(5, 3)               → "$C$5"

构建动态引用：
=INDIRECT(ADDRESS(ROW(), COLUMN()))

获取当前单元格的值

查找后返回地址：
=ADDRESS(MATCH("值", A:A, 0), 1)
返回找到值所在的单元格地址

动态引用：
    A          B
1   行号       列号
2   5          3

=ADDRESS(A2, B2)  → "$C$5"
=INDIRECT(ADDRESS(A2, B2))  → 返回C5单元格的值

=COLUMN()                    当前列号
=COLUMN(A1)                  → 1
=COLUMN(C1)                  → 3
=COLUMN(A1:C1)               → {1,2,3}（数组）

=ROW()                       当前行号
=ROW(A1)                     → 1
=ROW(A5)                     → 5
=ROW(A1:A3)                  → {1,2,3}（数组）

自动编号：
=ROW()-ROW($A$1)+1

生成序列：
=ROW(A1:A10)-ROW(A1)+1  → 1,2,3,...,10

动态列号：
=COLUMN()-COLUMN($A$1)+1

VLOOKUP 动态列号：
=VLOOKUP($A1, $B:$E, COLUMN(A1), FALSE)
向右拖动自动调整列号

自动序号：
    A          B
1   序号       数据
2   =ROW()-1   产品A
3   =ROW()-1   产品B
4   =ROW()-1   产品C

结果：
    A          B
1   序号       数据
2   1          产品A
3   2          产品B
4   3          产品C

=COLUMNS(A1:C1)              → 3
=COLUMNS(A1:Z1)              → 26
=COLUMNS(范围)               返回范围的列数

=ROWS(A1:A3)                 → 3
=ROWS(A1:A100)               → 100
=ROWS(范围)                  返回范围的行数

计算范围大小：
=ROWS(A1:A100) * COLUMNS(A1:Z1)

动态范围行数：
=ROWS(INDIRECT("A1:A" & B1))

检查数据完整性：
=IF(ROWS(A:A)=ROWS(B:B), "完整", "不完整")

=SUM(INDIRECT("A1:A" & B1))

或使用 OFFSET：
=SUM(OFFSET(A1, 0, 0, B1, 1))

或使用 INDEX：
=SUM(A1:INDEX(A:A, B1))

=INDIRECT("'" & A1 & "'!B1")

根据A1的工作表名，引用对应工作表的B1单元格

最近N个值的平均：
=AVERAGE(OFFSET(A1, MAX(0, COUNTA(A:A)-N), 0, N, 1))

或使用 INDEX：
=AVERAGE(INDEX(A:A, MAX(1, COUNTA(A:A)-N+1)):INDEX(A:A, COUNTA(A:A)))

定义名称：
数据范围 = OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 2)

图表数据源使用该名称，自动适应数据量变化

工作表列表：
    A            B
1   工作表       A1值
2   Sheet1       =INDIRECT(A2 & "!A1")
3   Sheet2       =INDIRECT(A3 & "!A1")
4   Sheet3       =INDIRECT(A4 & "!A1")

汇总：
=SUMPRODUCT(INDIRECT("'" & A2:A4 & "'!A1"))

数据：
    A          B          C
1   月份       销售额     累计
2   1月        100        =SUM($B$2:B2)
3   2月        150        =SUM($B$2:B3)
4   3月        200        =SUM($B$2:B4)

使用 OFFSET：
=SUM(OFFSET($B$2, 0, 0, ROW()-1, 1))

数据：
    A          B
1   数据       最大值位置
2   10         =ADDRESS(MATCH(MAX(A:A), A:A, 0), 1)
3   30         → "$A$3"
4   20

获取最大值所在单元格的地址