|
本期总结： 在编写这道题目之前，我在论坛中发了一个有关TEXT函数应用的新帖：[原创]你也许未曾见过这样的Text函数用法，在这个帖子中曾经提到，使用TEXT函数来进行条件判断，在某些情况下可以大大简化公式。但是苦于一时半会儿找不到印证这一说法的实例，于是就想根据这个思路来编一道竞赛题，通过题目来展示TEXT函数的作用。通过大家竞答的结果也可以发现，最长的公式有780个字符，而最高效的公式只有163个字符，这其中的差距虽然并非完全是TEXT函数的功劳，但至少在很大程度上帮助大家更深刻地体会到了这个函数的用法。 本题目的本意是让对函数公式不太熟悉的用户也能有机会得分，并且通过一点所谓的小技巧来得到一定的提升。这里提到的小技巧，也是题目中预设的几个考点。先看一下我预设的180个字符的公式： =TEXT(MAX(--TEXT(SUBSTITUTE(B2&"秒","点","时")/60&"","[="&QUARTILE(IF(A$2:A$16=A2,--SUBSTITUTE(B$2:B$16&"秒","点","时")),{0,4})/60&""&"]!"&{3,1}&"G/通用格式;4")),"[>4]!"&A2&"第aaa次-m点s.00分;") 此公式可再节省3个字符：177字符： =TEXT(MAX(--TEXT(SUBSTITUTE(B2&"秒","点","时")/60&"","[="&QUARTILE(IF(A$2:A$16=A2,--SUBSTITUTE(B$2:B$16&"秒","点","时")),{0,4})/60&"]!"&{3,1}&"G/通用格式;4")),"[>4]!"&A2&"第aaa次-m点s.00分;") 虽然本题的解题思路可以有很多，我这个公式也并非最佳解答，但所涉及的考点基本上已经在这个公式里面包含了： 1，认祖归宗： “12点36分23”这种形式的字符串并不是可以直接被系统所识别的时间值，需要通过公式转换才能形成时间数值。通过公式“SUBSTITUTE(B2&"秒","点","时")”应该是最简单的方法之一，通过这样转换得到的字符串可以被系统识别为时间，前面加上--就可以转换为数值进行最值的比较。此类做法在论坛中已经很常见了。 2，李代桃僵： 要将“12时36分23秒”这种形式的时间中秒数部分进位到十进制的分钟形式，成为下面这种形式“12点36.38分”的时间，方法其实有很多，不少初级用户都选择使用字符串分段组合的形式来解决。其实使用“=TEXT(("12时36分23秒")/60,"m点s.00分")”就可以很方便地得到结果，先除以60，将“时-分-秒”的结构退一位变成“分-秒-1/60秒”的结构，然后再使用表示分的格式代码m和表示秒的格式代码s来代替“时-分”的格式显示。因为“秒”有s.00的这种百分数显示格式，因此不需要进行其他运算，很方便就可以将60进制转换为十进制的显示了。 这次答题当中不少朋友都想到了这种方法，其实我在出题前几天也在回帖当中写到过：请问高手，在excel里如何将经纬度只以度分来表示，希望以此为大家留点提示。 以上两个小技巧我觉得都属于一般性的技巧，只要在论坛中多看一些帖子一般都能看到或想到。因此如果用到了这两个技巧，即使公式结构很普通，一般也能完成在290个字符以下，这就是我第一道分数线的设置原由。 例如此公式为289字符： =IF(--SUBSTITUTE(B2&"秒","点","时")=MIN(IF(A$2:A$16=A2,--SUBSTITUTE(B$2:B$16&"秒","点","时"))),A2&"第一次-"&TEXT(SUBSTITUTE(B2&"秒","点","时")/60,"m点s.00分"),IF(--SUBSTITUTE(B2&"秒","点","时")=MAX((A$2:A$16=A2)*SUBSTITUTE(B$2:B$16&"秒","点","时")),A2&"第二次-"&TEXT(SUBSTITUTE(B2&"秒","点","时")/60,"m点s.00分"),"")) 3，鱼和熊掌： 题目要对最大值和最小值进行判断，少不了要求取最值。分开求取最值的代价很大，包括使用计数方式来分别求取的方法。而要两者兼得又排除其他数据的干扰，使用QUARTILE函数就是一个不错的选择。“QUARTILE(....{0,4})”就可以得到一个同时包含最大值和最小值的数组，至于这个数组的后续处理，那就需要其他的技巧了。看一下这期在200个字符以下的公式（包括我在评分说明中所优化的几个公式），几乎都使用到了这个函数。说句不太谦虚的话，所谓“英雄所见略同”，这些高手们果然都毫不客气地祭出了这一法宝。 4，移花接木： 因为题目要求显示“第一次”、“第二次”，很多朋友得到了数值1、2，想要转换为中文字符“一”和“二”还是动用了不少脑筋。如果用[dbnum1]吧，后续的时间值不太好处理，把两个分开写成两段条件格式吧，又有些不太甘心。这里就要有些小聪明，借用格式代码中的aaa显示星期几的方法来代替这里中文字符的显示。看上去“星期几”这个格式与我们的“一”和“二”毫不相关，但在这里却是很好的一个移花接木的方法。要在时间前面加日期才能构成星期的显示，加日期的方法有很多，各位高手也都各显神通了。gouweicao版主一开始使用了这个技巧，但后来由于思路的改变而放弃了，而willin2000版主则借助这个技巧最终得到了本期的最短公式。 以上四个技巧就是本期竞赛题主要想跟大家分享的内容，我的180个字符的公式也是由此而来，不过由于太想把条件判断放入TEXT函数当中，反而错失了更简单使用IF函数进行条件判断的做法，其他大致思路均与willin2000版主的答案比较相似。当我看到willin2000版主195个字符的解答时真的是很惊讶，题目的几个考点几乎完完全全都被用到了。看来大家脑袋里面的东西真的是相差不多，关键就是看如何去调配组合了。 昨天写的比较急，还有一些想再补充一下： 除了上面提到的四点内容，其他有关在TEXT函数中使用的一些小技巧可参考前面所提到的那个“Text函数用法”帖中的相关说明。 此外有关在TEXT函数中进行数值的相等比较还有一点需要特别说明一下，如果比较对象是无穷小数，那么由于Excel数值运算保留位数的问题，直接使用=号进行比较会出现错误，例如： =TEXT(1/3,"[="&1/3&"]!a;!b") 这个公式的本意是当第一参数等于1/3时显示字符a，其他情况显示字符b，可是公式的结果却得到了b。 要使公式能够真正起作用，需要将数值转换为文本再进行比较： =TEXT(1/3&"","[="&1/3&"]!a;!b") 这个公式的返回结果才是a。需要注意的是，这个公式并不是将两个文本字符串进行比较，比较的还是1/3这个运算结果的数值，只不过将第一参数和第二参数中的运算结果统一了保留位数的标准，可以进行真正的数值比较了。例如下面这个公式也可以得到结果a： =TEXT(1/3&"","[="&2/6&"]!a;!b") 我此帖一开始所提到的180字符的公式，以及下面红色字体的几个公式中都使用到了这种条件判断方式，其中“SUBSTITUTE(B2&"秒","点","时")/60&""”的作用即在于此。由于后来我发现，第一参数用过&""后，第二参数中就不再需要&""，因此这几个公式都可以比原来再减少3个字符，因此再做了修改。 下面整理一下本期中比较好的思路解法，包括我设想和修改的一些公式： 195字符：=TRIM(LEFT(TEXT(MAX((B2=TEXT(QUARTILE(IF(A$2:A$16=A2,--SUBSTITUTE(B$2:B$16&"秒","点","时")),{0,4}),"h点m分s"))*{9,3}),"!"&A2&"第aaa次-;;"&REPT(" ",9))&TEXT(SUBSTITUTE(B2&"秒","点","时")/60,"m点s.00分"),14)) 189字符：=TEXT(MAX(--TEXT(SUBSTITUTE(B2&"秒","点","时")/60&"","[="&QUARTILE(IF(A$2:A$16=A2,--SUBSTITUTE(B$2:B$16&"秒","点","时")),{0,4})/60&"]""08-7-"&{7,1}&" ""h:m:s.00;!0")),"[>0]!"&A2&"第aaa次-m点s.00分;") 188字符：=TEXT(TEXT(SUM((-SUBSTITUTE(B$2:B$16&"秒","点","时")>=-SUBSTITUTE(B2&"秒","点","时"))*(A$2:A$16=A2)),"[=1]2;[="&COUNTIF(A:A,A2)&"]3;4")+SUBSTITUTE(B2&"秒","点","时")/60,"[<4]!"&A2&"第aaa次-m点s.00分;") 187字符：=TEXT(SUM((-SUBSTITUTE(B$2:B$16&"秒","点","时")>=-SUBSTITUTE(B2&"秒","点","时"))*(A$2:A$16=A2))+SUBSTITUTE(B2&"秒","点","时")/60,"[<2]!"&A2&"第一次-m点s.00分;[>"&COUNTIF(A:A,A2)&"]!"&A2&"第二次-m点s.00分;") 184字符：=TEXT(MIN(--TEXT(SUBSTITUTE(B2&"秒","点","时")/60&"","[="&QUARTILE(IF(A$2:A$16=A2,--SUBSTITUTE(B$2:B$16&"秒","点","时")),{0,4})/60&"]"&{2,3}&"."&REPT(0,9)&";4")),"[<4]!"&A2&"第aaa次-m点s.00分;") 180字符：=LOOKUP("々",IF({0,1,1},IF(B2=TEXT(QUARTILE(IF(A$2:A$16=A2,--SUBSTITUTE(B$2:B$16&"秒","点","时")),{0,4,0}),"h点m分s"),A2&TEXT({0,3,2}+SUBSTITUTE(B2&"秒","点","时")/60,"第aaa次-m点s.00分")),"")) 177字符：=TEXT(MAX(--TEXT(SUBSTITUTE(B2&"秒","点","时")/60&"","[="&QUARTILE(IF(A$2:A$16=A2,--SUBSTITUTE(B$2:B$16&"秒","点","时")),{0,4})/60&"]!"&{3,1}&"G/通用格式;4")),"[>4]!"&A2&"第aaa次-m点s.00分;") 163字符：=TEXT(MIN(IF(TEXT(QUARTILE(IF(A$2:A$16=A2,1*SUBSTITUTE(B$2:B$16&"秒","点","时")),{0,4}),"h点m分s")=B2,{2,3}))+SUBSTITUTE(B2&"秒","点","时")/60,"[>1]!"&A2&"第aaa次-m点s.00分;")
好了，就讲到这里吧。有事跟帖，无事退朝。
[此贴子已经被作者于2008-7-24 10:05:41编辑过] | 
楼主: chrisfang
沪公网安备 31011702000001号 沪ICP备11019229号-2
楼主
发表于 2008-7-22 15:59
发表于 2008-7-23 11:20
