offset 取非空行动态数据

总有刁民想骗朕

想自定义一个名称作为动态数据源。
比如A1~A6为待选动态数据区域，如果其中的cell为空（假设A2），就不要作为数据源 跳过，也就是A1+A3~A6。这样的公式该如何写。

喵那个咪

试了一下，直接在名称中使用较复杂的公式没有创建成功，下面方法需要添加辅助区域：（1）在 E列 提取要引用的内容，将空单元格排除（假设数据源为100行）
=IFERROR(OFFSET($A$1,SMALL(IF($A$1:$A$100<>"",ROW($1:$100)),ROW(A1))-1,0),"々")

（2）创建名称
=OFFSET(Sheet1!$E$1,0,0,COUNTIFS(Sheet1!$E$1:$E$100,"<>々",Sheet1!$E$1:$E$100,"<>"))

（3）使用数据验证测试

OFFSET动态非空数据源.rar (42.44 KB, 下载次数: 140)

2021-11-10 19:53 上传

点击文件名下载附件
OFFSET动态非空数据源.rar

詛儜詆調詆

万金油，你说的是，，=index(a:a,MODE.MULT(IF(A1:A99<>"",ROW(1:99)+{0,0})))

小麻椒

公式名称管理器
名称填写→
A_1
引用位置填写→
=N(Offset($A$1,MODE.MULT(IF($A$1:$A$99<>"",ROW($A$1:$A$99)+{0,0}))-1,))
单元格B列或其他列求和时引用→
＝SUM(A_1)

可笑爱脸

刚开始我也是用的 N(OFFSET(INDIRECT在单元格可以，但是放到名称里面后，数据验证无法使用，你这个成功了吗？我试了一下不行

杨洪金

试试才知,我这边试了可以！

用户心中有你

超出认知了。
这个公式里 {0,0} 是什么意思？是将非空的行数 格式化？变成{1,1} {2,2}{FALSE，FALSE}的意思么？
因为我在excel里点公式看结果是这么个结果。
如果是的话，再用MODE.MULT函数 取出出现次数最多的行 是什么意思？ 是因为{FALSE，FALSE}最多？
那再用offset函数 偏移 怎么就过滤了那些空行。。。。
麻烦大神讲解下思路，不想一直做伸手党。。。

不明所以丢了个账号

在excel里分解了下公式，理解了{0,0}的意思了。
应该就是格式化下，让有数据的变成{1,1}这样的数组
然后通过MODE.MULT 取得重复的数字，也就是{1,1}，{2,2}这种。
结果得到非空的行号。
但offset（A1,非空行号数组，）这样的公式在我这没成功啊 定义了名称 用sum看下结果 和预想的不一致啊。
如图，黄色cell那sum下提供的公式结果，是6 但想要的不应该是1,3,5,7,8 也就是和17吗

牙医张亚岗

注意：→-1
=SUM(N(Offset($A$1,MODE.MULT(IF($A$1:$A$99<>"",ROW($A$1:$A$99)+{0,0}))-1,)))
数组公式！请按Shift+Ctrl+Enter结束输入！

山桐花开

厉害。offset的参数居然还能用数组。。。
有了-1 完美解决。