函数公式单元格引用小结

lss001

函数公式单元格引用
一，静态引用
相对引用=A1
混合引用=$A1或A$1
绝对引用=$A$1
单行=A1:B1
单列=A1:A10
连续区域=A1:B10
间隔区域=(A1:A2,C1:C2)
联合区域=(A1:A2&C1:C2)
交叉区域=(A1:C2 B1:C2)→中间空格
连接区域=INDIRECT({"a1:a7","c1:c8","e1:e9"})

二，动态引用
单个行变
=INDIRECT("A"&ROW(A1))
=OFFSET($A$1,ROW(A1),)
单个列变
=INDIRECT(CHAR(ROW(A1)+64)&1)
=OFFSET($A$1,,COLUMN(A1))
单个行列都变
=INDIRECT(CHAR(ROW(A1)+64)&ROW(A1))
=OFFSET($A$1,ROW(A1),COLUMN(A1))
单行
=INDIRECT("A1:"&CHAR(COUNTA(1:1)+64)&1)
=OFFSET($A$1,,,,COUNTA(1:1))
=INDEX(A:C,ROW(A1),)
单列
=INDIRECT("A1:A"&COUNTA(A:A))
=OFFSET($A$1,,,COUNTA(A:A))
=INDEX(A:C,,COLUMN(A1))
单区域
=INDIRECT("A1:"&CHAR(COUNTA(1:1)+64)&COUNTA(A:A))
=A1:INDIRECT(CHAR(COUNTA(1:1)+64)&COUNTA(A:A))
=OFFSET($A$1,,,COUNTA(A:A),COUNTA(1:1))
=A1:OFFSET($B$1,,,COUNTA(A:A),COUNTA(1:1))
=A1:INDEX(A:C,COUNTA(A:A),)

三，数组引用
一维数组(单列奇数行)
=OFFSET($A$1,ROW($A$1:$A$5)*2-2,)
一维数组(单列偶数行)
=OFFSET($A$1,ROW($A$1:$A$5)*2-1,)
二维数组(两列奇数行)
=OFFSET($A$1,ROW($A$1:$A$5)*2-2,{0,1})
二维数组(两列偶数行)
=OFFSET($A$1,ROW($A$1:$A$5)*2-1,{0,1})
三维数组
=OFFSET($A$1,{0;1},{0,1},{1;2})
四维数组
=OFFSET($A$1,{0;1},{0,1},{1;2},{1,2})

czar8

就是想学这个

lss001

注意以下区别！→
连接数据区域=INDIRECT({"a1:a7","c1:c8","e1:e9"})
←连接区域→Sum(Countif(可识别区域,x))
连接区域数(组)据=CHOOSE({1,2,3},a1:a7,c1:c8,e1:e9)
←连接数(组)据→Sum(Countif(不识别数(组)据,x))