HYPERLINK函数中嵌套match函数时[match_type]参数的注意事项

acewel

今天早些时候发了个HYPERLINK函数的问题贴子，居然无人解答。这里我将问题的解答及match函数中[match_type]参数的格式做下说明。

MATCH(lookup_value, lookup_array, [match_type])
match_type 参数：
1 或被省略 MATCH 函数会查找小于或等于 lookup_value 的最大值。lookup_array 参数中的值必须按升序排列，例如：...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE。
0 MATCH 函数会查找等于 lookup_value 的第一个值。lookup_array 参数中的值可以按任何顺序排列。
-1 MATCH 函数会查找大于或等于 lookup_value 的最小值。lookup_array 参数中的值必须按降序排列，例如：TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ... 等等。

附件中因为明细录入中的日期是升序排列的，所以：
match_type 参数使用1的时候可以定位，但不是想要的结果！
match_type 参数使用 -1，查找列不是降序排列当然得不到结果！

曲折求解的话只能将match函数(match_type 参数使用1)结果加上“1”试试了。
=IF($B1<>"",HYPERLINK("#"&IF(ISNA(MATCH($B1,明细录入!A:A,1)),"明细录入!$A$"&"2","明细录入!$A$"&MATCH($B1,明细录入!$A:$A,1)+1),"…"),"")

论坛中有很多人说“match函数的结果‘偏离’1“实际上也是没有了解match_type 参数1、0、-1时对目标查找列到底是升序还是降序的要求。

yongbin

看看附件是否符合要求?

acewel

更行的结果应该是可以早到完全匹配的日期时就完全匹配，无法找到的时候再加1

这样就可以始终得到正确链接地址了。

acewel

原帖由 acewel 于 2009-6-23 23:44 发表
更行的结果应该是可以早到完全匹配的日期时就完全匹配，无法找到的时候再加1

这样就可以始终得到正确链接地址了。

=IF($B1<>"",HYPERLINK(IF(ISNA(MATCH($B1,明细录入!$A:$A,0)),"#明细录入!$A$"&MATCH($B1,明细录入!$A:$A,1)+1,"#明细录入!$A$"&MATCH($B1,明细录入!$A:$A,1)),"…"),"")

这样就可以始终链接到正确的单元格了。

acewel

-------------------------------------------------------------
=IF(ISNUMBER($J18),HYPERLINK(IF(ISNA(MATCH($J18,每日记账!$A:$A,0)),IF(ISNA(MATCH($J18,每日记账!$A:$A,1)),"#每日记账!$A$"&"2","#每日记账!$A$"&MATCH($J18,每日记账!$A:$A,1)+1),"#每日记账!$A$"&MATCH($J18,每日记账!$A:$A,0)),"…"),"")
-------------------------------------------------------------

谢谢大家的回复和关注，以上是我的最终方案目前工作十分正常，目前我使用的最终效果如下图“…”所示的样式。match函数是用于定位“每日记账”中的交易日期（银行等机构的记账日期在另外一列此处不考虑，在该表中的初始行我还设置了一个交易时间是1900-01-01的隐藏行，这样超出的日期会被定位至A3任何时候都不会显示错误）。上面版主说的“日期加1 ”不是这样的，实际是将match匹配到的行数值加1。因为交易日期的时间是不连续的升序列，所以我要的定位方案是“每个'账期的第一次交易的时间'”，比如2009-05-02当天没有记录临近的只有2009-04-26与2009-05-09，就被定位到2009-05-09.我在上面的公式中使mactch函数可以精确匹配的就精确匹配（[match_type]=0，可以按任何顺序排列），不能精确匹配的绝对不能匹配到上一账期（[match_type]=1，升序查找小于或等于 lookup_value 的最大值所在的行号再将行号加1），就是这样。我想分享的是match函数match_type参数与目的完全匹配的一点想法。当然不在我这个表格文件的环境中来讨论仅凭1楼的精简附件的确有点难以理解，现在gouweicao78版主应该知道我的意思了吧，谢谢!



顺便说一下，这个表格文件"该卡相关信息"中的每一个关键字都是函数构造出来的。如果有朋友也想做这样的表请注意：“…”两边的账期区间和之前的账期说明，对于某些“账单日<还款日”的卡片容易发生“同一行月份相同或计算错误的”情况，要使计算正确请加入“账单日<还款日”和“账单日>还款日”的判断条件，或简单一点请将“账单日”或“还款日”中的一个用当月的时间格式如2009-08-01表示，另一个在前者基础上+间隔天数也表示为2009-08-20形式，计算时用MID函数来调用就可以了。

[ 本帖最后由 acewel 于 2009-8-11 21:18 编辑 ]

飞云楼主

谢谢，学习了。

gouweicao78

看来半天，还不是很明白楼主在1楼想表达的是要分享什么。
MATCH的第3个参数为1、0、-1的描述并没有错误。
1楼附件中，要定位2009-2-21的位置，MATCH正确地返回2009-2-7所在的位置，为什么楼主却说“HYPERLINK函数如果正确工作的话应该被定位在”2009-2-23呢？
公式使用了MATCH($B1,明细录入!A:A,1)的第3个参数是1，要求明细录入日期升序排列（确实也是升序），并返回小于等于查找的B1（即2009-2-21）的最大值的位置，也就是2009-2-7的位置，怎么会是2009-2-23的位置呢？


因此，问题是楼主的实际需要返回>=B1的第一个位置，而与HYPERLINK嵌套、与MATCH函数参数问题根本无关。
改为数组公式：

1. 
   
2. =IF(B1="","",HYPERLINK("#明细录入!A"&MATCH(TRUE,明细录入!A2:A23>=B1,0)+1,"…"))
   

复制代码

注意按CTRL+SHIFT+ENTER结束。

[ 本帖最后由 gouweicao78 于 2009-8-10 22:35 编辑 ]

gouweicao78

日期加1并非正确办法。要是加了1仍然没有该日期呢？
比如查2009-1-28，那么按楼主要求，应该对应2009-2-7。加1的话，查找2009-1-29也找不到。

6楼公式中，MATCH的第2参数是对明细录入表A2:A23的日期>=B1进行判断（注意，A1是文本，判断>=B1会返回TRUE，因此去掉A1，并在后面MATCH结果加1）

_s_bri

=IF(B1<>"",HYPERLINK("#明细录入!a"&MATCH(B1,明细录入!A:A,1)+(COUNTIF(明细录入!A:A,汇总!B1)=0),"…"),"")

gouweicao78

明白了。
其实，楼主对MATCH的理解没有错，呵呵。
问题在于用比较多的文字表达一个比较简单的意思：定位>=B1的第1个记录，因为MATCH的升序定位是<=B1的第一个记录，所以，用判断条件并在MATCH后面+1修正。

解法上来说，本帖8楼的最简单。