| 楼主 rongjun |
题目：求A1:C52区域内每日最大销售额与每日最小销售额之差的最大值，并列出其日期。 说明：1、日差额=日最大销售额-日最小销售额； 2、A列日期按升序排列；不考虑存在相同的日差额； 3、参看如下辅助说明（仅作为说明题意，不得用于公式中）。 要求：1、用函数完成，可以用两个公式完成； 2、不用VBA，不用辅助区域，不定义名称； 3、表格格式固定，不能更改，结果如下图所示。   求最大日差额_题目.rar已经公布答案，在20楼。 |
| 2楼 小敏敏 |
学习学习。 |
| 3楼 棉花糖 |
=TEXT(MID(MAX(10^10+(LOOKUP(ROW(2:52),IF(A2:A52<>"",ROW(2:52)),A2:A52)=TRANSPOSE(LOOKUP(ROW(2:52),IF(A2:A52<>"",ROW(2:52)),A2:A52)))*(C2:C52-TRANSPOSE(C2:C52))*10^5+(LOOKUP(ROW(2:52),IF(A2:A52<>"",ROW(2:52)),A2:A52))),{7,2},5),"[>1e4]e-mm-dd") 公式太长了，243个字符。一个公式 |
| 4楼 syz105729913 |
rongjun 版主出的题目越来越难了，做了个300多字符的，先不贴出来，继续优化中。 |
| 5楼 rongjun |
三楼的公式可以简化一下：
期待syz105729913 兄的好公式：） |
| 6楼 toby08 |
既然题目已说明A列日期按升序排列，可不可作这样的简化呢？ =TEXT(MID(MAX(10^10+IF(SUBTOTAL(4,OFFSET(A2,,,ROW(1:51)))=SUBTOTAL(4,OFFSET(A2,,,COLUMN(A:AY))),C2:C52-TRANSPOSE(C2:C52))*10^5+ SUBTOTAL(4,OFFSET(A2,,,ROW(1:51)))),{7,2},5),"[>1e4]e-mm-dd") |
| 7楼 syz105729913 |
最大日差额=MAX(MMULT(LARGE((9-ROW(1:10))/1%+IF(COLUMN(A:I)<=FREQUENCY(ROW(2:52),IF(A3:A53,ROW(2:52))),LOOKUP(COLUMN(A:I)+MATCH(A2+ROW(1:10)-1,A1:A51,)-2,ROW(1:56),C2:C52)),ROW(1:10)*9+COLUMN(A:I)-9)*((COLUMN(A:I)=1)-(COLUMN(A:I)=FREQUENCY(ROW(2:52),IF(A3:A53,ROW(2:52))))),ROW(1:9)^0)) SUBTOTAL函数用得太巧妙了，学习学习，我的公式就显得太肥了。 |
| 8楼 gouweicao78 |
上面解法中，核心部分用是解决A列空单元格问题，主要有几个解法： 1、SUBTOTAL(3,OFFSET(A2,,,ROW(1:51)))——利用SUBTOTAL对offset多维引用的支持，得到A列非空单元格个数的数组，1;1;1……2;2;…… 2、SUBTOTAL(4,OFFSET(A2,,,ROW(1:51)))——利用SUBTOTAL对offset多维引用的支持，以及A列日期升序排列，得到类似填充A列日期的效果（A2:A3，日期的最大值是A2的……；A2:A10日期的最大值是A10的…… 3、COUNTIF(OFFSET(A2,,,ROW(1:51)),"<>")——利用COUNTIF对offset多维引用的支持，取得与解法1类似的数组。 4、LOOKUP(ROW(2:52),IF(A2:A52,ROW(2:52)))——以前经典的LOOKUP得到内存数组的解法。 而解决日最大值与最小值之差，上面的解法都给了C2:C52-TRANSPOSE(C2:C52)的枚举法，是目前比较简单的解法。还有一个复杂一些的解法： =MAX(MMULT(SUBTOTAL({4,5},OFFSET(C1,SMALL(IF(A2:A52,ROW(1:51)),ROW(OFFSET(A1,,,COUNT(A:A)))),,FREQUENCY(ROW(2:52),IF(A3:A53,ROW(2:52))))),{1;-1})) 即用OFFSET+small取得偏移量+Frequency取得分段高度，得到每一个日期的C列的数值，再用SUBTOTAL({4,5}分别求取最大、最小值，再用MMULT求差。 当然，这个解法在2003中受到7层嵌套的限制，就不能再算出日期了，而且公式长度也是长了许多。 |
| 9楼 rongjun |
用mmult()的话可以用下面的公式，不超过7层嵌套，可以用之算出日期：
还有其他解法，大家可以继续…… |
| 10楼 l785482583 |
顶顶 |
| 11楼 xcd |
荣军兄的好题,进来学习下 |
| 12楼 syz105729913 |
如果不考虑03版嵌套限制的话可以用以下的公式（07可以用）=TEXT(MID(MAX(MMULT(SUBTOTAL({4,5},OFFSET(C1,ROW(1:51),,-TEXT(FREQUENCY(ROW(1:51),(A3:A52>0)*ROW(1:50)),"0;;1"))),{1;-1})*10^5+SUBTOTAL(4,OFFSET(A2,,,ROW(1:51)))),{3,1},{5,2}),"[>1e4]e-mm-dd") =TEXT(MID(MAX(MMULT(SUBTOTAL({4,5},OFFSET(C1,MATCH(A2+ROW(1:10)-1,A2:A52,),,FREQUENCY(ROW(2:52),IF(A3:A53,ROW(2:52))))),{1;-1})*10^5+A2+ROW(1:10)-1),{3,1},{5,2}),"[>1e4]e-mm-dd") |
| 13楼 xcd |
E2:F2多单元格数组公式,但E2要设置为日期格式 |
| 14楼 178456568 |
学习中········ |
| 15楼 rongjun |
不用iferror()也是可以的：
|
| 16楼 xcd |
荣军兄妙笔生花,改的好! |
| 17楼 syz105729913 |
在社区里看到老乡真是开心，荣军兄我也是广西的，呵呵 |
| 18楼 nld366369518 |
努力向上 |
| 19楼 lyglee |
学习了 见识了 明白了 |
| 20楼 rongjun |
小结： 本题目前有三种解法： 一、枚举法：以3、5、6楼为代表； 二、分段法：以9、12、13、15楼为代表； 三、排序法：
现将以上各个答案汇总如下，请各位指正： 求最大日差额——答题.rar如还有其他解法，请继续跟帖：） |
| 21楼 xcd |
最大日差额还有一解法:
数组公式 |
| 22楼 西山晴雪 |
都是这些强人在做 |
| 23楼 donghan |
学习学习。。 |
| 24楼 一米阳光-洋洋 |
灰常佩服的说,楼主的函数脑袋 |
| 25楼 阿卢 |
如果A列不合并单元格，一个日期对应一个值，那就容易办了用=MAX(IF(A2:A52=E2,C2:C52))-MIN(IF(A2:A52=E2,C2:C52))，数组公式轻松搞定，一合并还真没有思路 |
| 26楼 bluexuemei |
学习，学习 |
| 27楼 玉丫头 |
看一下答案 |
| 28楼 日月经天 |
学习 |
| 29楼 Pak |
学习学习，试解下 |
| 30楼 熊事典 |
好贴就是好贴。 |
| 31楼 rongjun |
题目：求A1:C52区域内每日最大销售额与每日最小销售额之差的最大值，并列出其日期。 说明：1、日差额=日最大销售额-日最小销售额； 2、A列日期按升序排列；不考虑存在相同的日差额； 3、参看如下辅助说明（仅作为说明题意，不得用于公式中）。 要求：1、用函数完成，可以用两个公式完成； 2、不用VBA，不用辅助区域，不定义名称； 3、表格格式固定，不能更改，结果如下图所示。 求最大日差额_题目.rar已经公布答案，在20楼。 |
| 32楼 小敏敏 |
学习学习。 |
| 33楼 棉花糖 |
=TEXT(MID(MAX(10^10+(LOOKUP(ROW(2:52),IF(A2:A52<>"",ROW(2:52)),A2:A52)=TRANSPOSE(LOOKUP(ROW(2:52),IF(A2:A52<>"",ROW(2:52)),A2:A52)))*(C2:C52-TRANSPOSE(C2:C52))*10^5+(LOOKUP(ROW(2:52),IF(A2:A52<>"",ROW(2:52)),A2:A52))),{7,2},5),"[>1e4]e-mm-dd") 公式太长了，243个字符。一个公式 |
| 34楼 syz105729913 |
rongjun 版主出的题目越来越难了，做了个300多字符的，先不贴出来，继续优化中。 |
| 35楼 rongjun |
三楼的公式可以简化一下：
期待syz105729913 兄的好公式：） |
| 36楼 toby08 |
既然题目已说明A列日期按升序排列，可不可作这样的简化呢？ =TEXT(MID(MAX(10^10+IF(SUBTOTAL(4,OFFSET(A2,,,ROW(1:51)))=SUBTOTAL(4,OFFSET(A2,,,COLUMN(A:AY))),C2:C52-TRANSPOSE(C2:C52))*10^5+ SUBTOTAL(4,OFFSET(A2,,,ROW(1:51)))),{7,2},5),"[>1e4]e-mm-dd") |
| 37楼 syz105729913 |
最大日差额=MAX(MMULT(LARGE((9-ROW(1:10))/1%+IF(COLUMN(A:I)<=FREQUENCY(ROW(2:52),IF(A3:A53,ROW(2:52))),LOOKUP(COLUMN(A:I)+MATCH(A2+ROW(1:10)-1,A1:A51,)-2,ROW(1:56),C2:C52)),ROW(1:10)*9+COLUMN(A:I)-9)*((COLUMN(A:I)=1)-(COLUMN(A:I)=FREQUENCY(ROW(2:52),IF(A3:A53,ROW(2:52))))),ROW(1:9)^0)) SUBTOTAL函数用得太巧妙了，学习学习，我的公式就显得太肥了。 |
| 38楼 gouweicao78 |
上面解法中，核心部分用是解决A列空单元格问题，主要有几个解法： 1、SUBTOTAL(3,OFFSET(A2,,,ROW(1:51)))——利用SUBTOTAL对offset多维引用的支持，得到A列非空单元格个数的数组，1;1;1……2;2;…… 2、SUBTOTAL(4,OFFSET(A2,,,ROW(1:51)))——利用SUBTOTAL对offset多维引用的支持，以及A列日期升序排列，得到类似填充A列日期的效果（A2:A3，日期的最大值是A2的……；A2:A10日期的最大值是A10的…… 3、COUNTIF(OFFSET(A2,,,ROW(1:51)),"<>")——利用COUNTIF对offset多维引用的支持，取得与解法1类似的数组。 4、LOOKUP(ROW(2:52),IF(A2:A52,ROW(2:52)))——以前经典的LOOKUP得到内存数组的解法。 而解决日最大值与最小值之差，上面的解法都给了C2:C52-TRANSPOSE(C2:C52)的枚举法，是目前比较简单的解法。还有一个复杂一些的解法： =MAX(MMULT(SUBTOTAL({4,5},OFFSET(C1,SMALL(IF(A2:A52,ROW(1:51)),ROW(OFFSET(A1,,,COUNT(A:A)))),,FREQUENCY(ROW(2:52),IF(A3:A53,ROW(2:52))))),{1;-1})) 即用OFFSET+small取得偏移量+Frequency取得分段高度，得到每一个日期的C列的数值，再用SUBTOTAL({4,5}分别求取最大、最小值，再用MMULT求差。 当然，这个解法在2003中受到7层嵌套的限制，就不能再算出日期了，而且公式长度也是长了许多。 |
| 39楼 rongjun |
用mmult()的话可以用下面的公式，不超过7层嵌套，可以用之算出日期：
还有其他解法，大家可以继续…… |
| 40楼 l785482583 |
顶顶 |