Match函数动态范围取值

zmm101

F列原数据列的最大非空行行号： =LOOKUP(1,0/(产品信息表!$F:F<>""),ROW(1:1000))
用数组公式1，通过Match函数对F列范围内内容进行去掉重复数据，并将不重复值列到另一列中：

=IFERROR(INDEX(Sheet1!$F:$F,SMALL(IF(MATCH(Sheet1!$F$2:$F$27,Sheet1!$F$2:$F$27,0)=ROW(Sheet1!$F$2:$F$27)-1,ROW(Sheet1!F$2:F$27),4^8),ROW(O2)))&"","")



1、 当：match的范围 Sheet1!$F$2:$F$27 ，从 F2 到 F27 范围中，如果F27是空值，那么 match返回值就会全部变成 是 #N/A 。中间存在空值是不会出错的，只是会增加一个空值行。就是最后行不能为空。

2、假如实际 源数据列是会增加 减少 行数的，也就是 Match范围实际是要动态调整的（否则如果实际数据变多后，还需要去修改这个公式）

所以，我将  F列原数据列的最大非空行行号： =LOOKUP(1,0/(产品信息表!$F:F<>""),ROW(1:1000)) ，放到指定单元格内 [A2]中，再用INDIRECT("Sheet1!$F2"&":$F"&Sheet1!$A$2)代替 Sheet1!$F$2:$F$27 实现了动态间接取数据范围，从而规避Match范围过大导致的错误

上述公式变成：
IFERROR(INDEX(Sheet1!$F:$F,SMALL(IF(MATCH(INDIRECT("Sheet1!$F2"&":$F"&Sheet1!$A$2),INDIRECT("Sheet1!$F2"&":$F"&Sheet1!$A$2),0)=ROW(INDIRECT("Sheet1!$F2"&":$F"&Sheet1!$A$2))-1,ROW(INDIRECT("Sheet1!$F2"&":$F"&Sheet1!$A$2)),4^8),ROW(Y1)))&"","")
------------------------------------------------------------------------
效果如图2, Match范围动态变化。

PS: 数据太多，好像会卡死。有没有别的解决方案，公式层面的。vba除外的方法

白栎

表示看不懂……
不过你找最后一行可以

1. =MAX(IF(F:F<>"",ROW(F:F)))

复制代码

创建这个数据区域也可以

1. OFFSET(F1,,,MAX(IF(F:F<>"",ROW(F:F))))

复制代码

NieC

同样表示看不懂，，另外，，数据源都在同一表格内，为什么单元格都加 sheet1呢？
数组公式这么多，肯定会卡
如果只是筛选一列中的唯一值，，经典的 index+small+if 这个数组已经可以满足了

zmm101

NieC 发表于 2022-8-26 09:04
同样表示看不懂，，另外，，数据源都在同一表格内，为什么单元格都加 sheet1呢？
数组公式这么多，肯定会 ...

我最终是跨页的所以加了SHEET1，我这就是index small if match。是需要Match的。公式千变万化。还是vba好用。因为match的范围内最后行存在空值，返回的全部变成了n/a.是我哪里写错了吗，您帮我看看，捣鼓半天了。
就是把match范围变成可变 区域（比如今天可能就100行数据内去重复，但是后面可能变成1000行，几千行），区域根据需要自动获取。又假如这个文件给别人用，又不知道公式内改哪个位置。

zmm101

白栎 发表于 2022-8-26 08:15
表示看不懂……
不过你找最后一行可以

好的，谢谢。

公式就是index small if match组合的，然后我是想弄成动态match范围，使用者可以根据需要自己调整范围。或者match范围根据数据记录行数自动调整。 你试下 match,如果范围内最后行是空值，（中间有空值不会出现N/A），就会返回全部 是N/a.不知道是不是我哪弄错了。
你有好的方法吗

一剑惊心

感觉反而是简单问题复杂化了，其实对于不确定行导致区域引用不固定的话，一个超级表就可以完全解决。况且你目前的方式还得手工做常量辅助，一旦增删内容，那你常量辅助没有修改的话，照样结果出错的

白栎

zmm101 发表于 2022-8-26 09:51
好的，谢谢。

公式就是index small if match组合的，然后我是想弄成动态match范围，使用者可以根据需 ...

我那两个公式就是自动计算有数据的范围的
然后我用的16版的365，改了你match的范围，也没出现全部是n/a的情况，是不是版本问题

NieC

zmm101 发表于 2022-8-26 09:45
我最终是跨页的所以加了SHEET1，我这就是index small if match。是需要Match的。公式千变万化。还是vba好 ...

这个数组不能有空值，所以具体单元格的范围还要再加个函数确定位置

zmm101

白栎 发表于 2022-8-26 10:33
我那两个公式就是自动计算有数据的范围的
然后我用的16版的365，改了你match的范围，也没出现全部是n/a ...

我的还是07版的，16版的怕太卡，都没敢装。你是改的Match范围大于 存在数据行的范围吗？ 我的就会全部出错了。都变成#n/a。新的版本是比老的更新好多功能