ExcelTip.Net留存知识帖 ---【注:附件之前被网盘供应商清空后,现已修复-现已修复-现已修复为本地下载!】
现在位置:首页 > 我的测验 > 函数与公式 > 练习7:求最大日差额

练习7:求最大日差额

作者:绿色风 分类: 时间:2022-08-18 浏览:176
楼主
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
三楼的公式可以简化一下:

  1. =TEXT(MID(MAX(10^10+(SUBTOTAL(3,OFFSET(A2,,,ROW(1:51)))=SUBTOTAL(3,OFFSET(A2,,,COLUMN(A:AY))))*(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")



期待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层嵌套,可以用之算出日期:

  1. =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楼
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()也是可以的:

  1. =--MID(MAX(10^10+(MMULT(SUBTOTAL({4,5},OFFSET(C1,ROW(1:51),,FREQUENCY(-ROW(1:51),-(A2:A51>0)*ROW(1:50))+(A2:A52=0))),{1;-1})&A2:A52)),{7,2},5)

16楼
xcd


荣军兄妙笔生花,改的好!
17楼
syz105729913
在社区里看到老乡真是开心,荣军兄我也是广西的,呵呵
18楼
nld366369518
努力向上
19楼
lyglee
学习了   见识了 明白了
20楼
rongjun
小结:
本题目前有三种解法:
一、枚举法:以3、5、6楼为代表;
二、分段法:以9、12、13、15楼为代表;
三、排序法:

  1. =TEXT(MID(MAX(10^10+(MOD(LARGE(C2:C52%-SUBTOTAL(3,OFFSET(A2,,,ROW(1:51))),ROW(1:51)),1)-MOD(SMALL(C2:C52%+SUBTOTAL(3,OFFSET(A2,,,ROW(1:51))),ROW(1:51)),1))*10^7+A2:A52),{7,2},5),"[>1e4]e-mm-dd")


现将以上各个答案汇总如下,请各位指正:

求最大日差额——答题.rar


如还有其他解法,请继续跟帖:)
21楼
xcd

最大日差额还有一解法:
  1. =MAX(MMULT(SMALL(SUBTOTAL(3,OFFSET(A2,,,ROW(1:51)))/1%%+C2:C52,SMALL(IF((A2:A52<>A3:A53)+(B3:B53=""),ROW(1:51)),ROW(1:10)*2-{1,0})),{-1;1}))

数组公式
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
三楼的公式可以简化一下:

  1. =TEXT(MID(MAX(10^10+(SUBTOTAL(3,OFFSET(A2,,,ROW(1:51)))=SUBTOTAL(3,OFFSET(A2,,,COLUMN(A:AY))))*(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")



期待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层嵌套,可以用之算出日期:

  1. =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}))


还有其他解法,大家可以继续……
40楼
l785482583
顶顶

免责声明

有感于原ExcelTip.Net留存知识的价值及部分知识具有的时间限定性因素, 经与ExcelTip.Net站长Apolloh商议并征得其同意, 现将原属ExcelTip.Net的知识帖采集资料于本站点进行展示, 供有需要的人士查询使用,也慰缅曾经的论坛时代。 所示各个帖子的原作者如对版权有异议, 可与本人沟通提出,或于本站点留言,我们会尽快处理。 在此,感谢ExcelTip.Net站长Apolloh的支持,感谢本站点所有人**绿色风(QQ:79664738)**的支持与奉献,特此鸣谢!
------本人网名**KevinChengCW(QQ:1210618015)**原ExcelTip.Net总版主之一

评论列表
sitemap