MATCH一个数组，而不是一行或一列！

leslie0705

A   B   C   D
1   2   3   4
5   3   7   8
9  10 11 12
3   7  14 15
我想找出最后一行的值3（A4）的最近的行的相同值，结果应该是B2，而不是C1.
同理，我想找B4 的最近的行的相同值，即是 C2。
请问，我应该怎么写出公公式呢？MATCH只能是数组的一列或一行，我要的是匹配整个数组。
谢谢各位大大！

pureshadow

以A4为例，查找结果B2：
匹配整个数组要用MIN(IF(A4=B1:D4,ROW(1:4)*10+COLUMN(B:D)*101))的方式，如果要返回单元格地址，公式要写成：
=ADDRESS(MOD(MIN(IF(A4=B1:D4,ROW(1:4)+COLUMN(B:D)*10)),10),MIN(IF(A4=B1:D4,COLUMN(B:D))),4)
CTRL+SHIFT+ENTER结束

roych

太复杂了，比尔盖要是知道Excel被蹂躏成这样，不知道作何感想。换了我，我宁愿多做两个辅助列也懒得去搞那么复杂的公式。

pureshadow

回楼上的：我也鄙视小比同学的，EXCEL有N多不符合中国国情的地方

leslie0705

我以大大的公式MIN(IF(A4=B1： D4,ROW(1:4)*10+COLUMN(B： D)*101))试了试，还是不行啊。
我的理解是ROW(1:4)*10是恒定值10，而COLUMN(B： D)*101是恒定值202，所以加法是212。。。
而且为什么不是ROW(1:3)和COLUMN(A： D)呢？这样的话就可以选A1到D3的所有值了。。。
最后我的excel显示出的值是0，我也不知道为什么，希望大大赐教！

roych

leslie0705 发表于 2011-1-22 19:45
我以大大的公式MIN(IF(A4=B1： D4,ROW(1:4)*10+COLUMN(B： D)*101))试了试，还是不行啊。
我的理解是ROW(1 ...

这是数组公式，怎么可能是恒定的呢？
ROW(1:4)*10表示的是一个数组，集合元素为：10,20,30，40
同样地，COLUMN(B： D)*101表示的是含有三个元素的集合。。
——嗯，要是不懂什么叫做元素和什么叫做集合的话，请回去翻翻高一的数学教科书。

leslie0705

哦哦，不好意思，高一读得不好。。
那为什么不是COLUMN(A： D)*101呢？为什么是101？
而且，我试了一下，改变了数组的排列，但还是结果0啊？
希望大大赐教！

pureshadow

ROW与COLUMN与被引用的单元格的值无关
还是按ROY说的那样，加几个辅助列来得简单，这一个函数，要从头开始解释，那得写好几张纸……

roych

回复 leslie0705 的帖子

再做最后一次解释吧，选中一个4×3的单元格区域，输入：
=row(1:4)*10+column(B：D)*101，
按下Ctrl+shift+enter结束输入生成数组，你就会发现有什么不同了：


小妖姐姐这个公式：
=ADDRESS(MOD(MIN(IF(A4=B1:D4,ROW(1:4)+COLUMN(B:D)*10)),10),MIN(IF(A4=B1：D4,COLUMN(B:D))),4)
我就勉为其难地解释一下吧，尽管没有小妖姐姐讲课讲得那么好，——毕竟俺不是培训师出身的嘛，有误的地方请多多指正。

我们先从最内层开始：
①IF(A4=B1:D4,ROW(1:4)+COLUMN(B:D)*10)
这表示什么意思呢？表示的是，如果在B1:D4的区域找到与A4相等的值，就返回其列标的10倍与其行标的和，例如，A4在这个区域里共有2个值，B2和C1，那么返回值为：22和31，如果没有找到，当然返回0（即False）了。

根据上面的意思，两个值都是符合条件的，那么我们需要的是哪个值呢？显然，我们需要列标最小的值，也就是B2。B2对应的是22，C1对应的是31……我们是不是可以得到这么一个规律，最小值才是我们想要的？

因此用Min函数来获取最小值：Min(元素集合），获得集合中最小值。接下来，我们再对这两个数值做进一步观察，很显然，十位数为列标，个位数为行标。那么就有了下面的操作了：

如果行、列标都是一位数的话，我们甚至可以直接用left和right来分离开行标和列标。这里小妖姐姐用Mod函数。Mod的只用在于取模，这个“模”听起来很专业，通俗点说就是取余数。例如Mod(A1,A2)就是取A1除以A2后的余数。——顺便提一下，如果有十几行，则应该列标该乘以100，再按100取模，这样公式要严谨些。

通过对If获得的数值取模，我们获得了所有的行标。当然，对于列标，我们就不用这么复杂了，直接根据公式①，稍作改造就可以获取列标了。这里不再解释：MIN(IF(A4=B1:D4,COLUMN(B:D))),4)

至此，行标和列标都获取到了，那么再用Address（行标，列标）即可返回单元格位置。小妖姐姐这里加了一个参数值4，表示的是获取的地址为相对引用。默认情况下为绝对引用，即带美元符号的地址。

leslie0705

哇！谢谢2位大大！！！
实在是太感激了！终于把问题弄清楚了！！！
要不是2位指导，我真的不知道怎么弄呢！
非常感谢！感激涕零！