数组兴趣小组练习题-037期（金额统计）

lllllllll · 发表于 2008-3-17 15:18

KMorFLMt.rar (2.48 KB, 下载次数: 55)

2008-3-17 15:14 上传

点击文件名下载附件
数组兴趣小组练习题-037期（金额统计） 这是一个会员的求助题感觉在二维数组问题上很有代表性要求不得使用辅助列不得定义名称  呵呵补充两个要求（为了练习区域转换)1 把A9:D12(不含A9)这个4行4列的区域构筑成9行2列的区域再进行其它的运算<table cellspacing="0" cellpadding="0" width="144" border="0" xstr="" style="WIDTH: 108pt; BORDER-COLLAPSE: collapse;"><colgroup><col span="2" width="72" style="WIDTH: 54pt;"></col></colgroup><tbody><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" width="72" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ffcc99;">柑果大</td><td class="xl22" width="72" xnum="" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99;">30</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ffcc99;">柑果小</td><td class="xl22" xnum="" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99;">10</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ffcc99;">柑果中</td><td class="xl22" xnum="" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99;">20</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ffcc99;">芒果大</td><td class="xl22" xnum="" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99;">40</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ffcc99;">芒果小</td><td class="xl22" xnum="" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99;">20</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ffcc99;">芒果中</td><td class="xl22" xnum="" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99;">30</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ffcc99;">桃果大</td><td class="xl22" xnum="" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99;">50</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ffcc99;">桃果小</td><td class="xl22" xnum="" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99;">30</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ffcc99;">桃果中</td><td class="xl22" xnum="" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99;">40</td></tr></tbody></table>2 表中A23:A25的顺序不是固定的公式中要加上处理方法

[此贴子已经被作者于2008-3-17 19:08:13编辑过]

lllllllll · 发表于 2008-3-17 15:20

难度不大但很有代表性望数组爱好者积极参加

linqilele · 发表于 2008-3-17 15:42

我怎么觉得很难

liangwen · 发表于 2008-3-17 17:40

在这个版块中,这个题目应该是比较简单了:=SUM(N(OFFSET($A$9,MATCH(B$2:J$2,A$10:A$12,),MATCH(B$3:J$3,B$9:D$9,)))*$B4:$J4)数组公式或用普通公式的形式:=SUMPRODUCT(N(OFFSET($A$9,MATCH(B$2:J$2,A$10:A$12,),MATCH(B$3:J$3,B$9:D$9,)))*$B4:$J4)

liangwen · 发表于 2008-3-17 18:19

考虑到果树数量统计表和金额补偿统计表的组别排序可能不同,补充一个,公式加个条件=SUMPRODUCT(N(OFFSET(A$9,MATCH(B$2:J$2,A$10:A$12,),MATCH(B$3:J$3,B$9:D$9,)))*(A$4:A$6=A23)*$B$4:$J$6)

[此贴子已经被作者于2008-3-17 18:19:32编辑过]

lllllllll · 发表于 2008-3-17 18:43

四楼五楼的公式非常好学习了~ 偶的本意是想让大家把A9:D12(不含A9)这个4行4列的区域构筑成9行2列的区域再进行其它的运算以达到练习区域转换的目的不想被liangwen用OFFSET给化解了[em01]

willin2000 · 发表于 2008-3-17 20:57

也来凑一个,数组公式(A23:A25的顺序不固定):=SUM(SUMIF(A$10:A$12,B$2:J$2,OFFSET(A$10,,MATCH(B$3:J$3,B$9:D$9,),))*(A$4:A$6=A23)*B$4:J$6)

[此贴子已经被作者于2008-3-17 21:02:10编辑过]

laoyebin · 发表于 2008-3-17 22:00

凑个热闹=SUM((B$2:J$2=A$10:A$12)*MMULT($B$10:$D$12,N((B$3:J$3=TRANSPOSE(B$9:D$9))))*B4:J4)

Luckyguy2008 · 发表于 2008-3-18 09:27

一般的，好像跟大家思路一样 =SUM(N(OFFSET(A$9,MATCH(B$2:J$2,A$10:A$12,),MATCH(B$3:J$3,B$9:D$9,)))*B4:J4) 再来个A23:A25乱序的 =SUM(N(OFFSET(A$9,MATCH(B$2:J$2,A$10:A$12,),MATCH(B$3:J$3,B$9:D$9,)))*OFFSET(B$3:J$3,MATCH(A23,A$4:A$6,),)) 回头有空再弄个区域转换的

Luckyguy2008 · 发表于 2008-3-18 10:48

=SUM(MOD(SMALL(MATCH(B$2:J$2&B$3:J$3,LOOKUP(ROW($3:$11)/3,{1;2;3},A$10:A$12)&LOOKUP(MOD(ROW($3:$11),3),{0,1,2},B$9:D$9),)*100+OFFSET(B$3:J$3,MATCH(A23,A$4:A$6,),),COLUMN(A:I)),100)*RIGHT(SMALL(--({1;2;3}&{1,2,3}&B$10:D$12),COLUMN(A:I)),2)) 不知道这样是否满足不看兄的补充条件。 红色部分是水果加上大中小的数组，蓝色部分是对应的补偿标准的数组，A23:A25乱序也没问题。 公式越整越复杂了。

		自动登录	找回密码
密码			注册