<p>[公告]10讲上交作业4组</p><p><font color="#ff3300">参考答案:主要提供几种思路,找最简单的最通用的方法!<br/>第一问:<br/>1.=IF(ROUNDUP(MONTH($C9)/3,)&"季度"=D$8,INDEX($K$9:$K$32,MATCH($C9,$J$9:$J$32,)),"")<br/>2.=IF(CEILING(MONTH($C9)/3,1)=--LEFT(D$8),OFFSET($K$9,MATCH($C9,$J$9:$J$32,)-1,),"")<br/>3.=IF(ROUNDUP(MONTH($C9)/3,)=--LEFT(D$8),VLOOKUP($C9,$J$9:$K$32,2,),"")<br/>第二问:<br/>1.=SUMPRODUCT((C9:C32>--"4-30")*((D8:G8="2季度")+(D8:G8="4季度"))*TEXT(D9:G32,"0;;;!0"))<br/>2.=SUMPRODUCT((C9:C32>--"4-30")*((D8:G8="2季度")+(D8:G8="4季度"))*IF(ISNUMBER(D9:G32),D9:G32,0))<br/>3.=SUM(MMULT(--TRANSPOSE(C9:C32>--"4-30"),--TEXT(D9:G32,"0;;;!0")*{0,1,0,1}))<br/>4.=SUM(IF(MONTH(C9:C32)>4,E9:E32),G9:G32)<br/>第三问:<br/>1.=MATCH(MAX(SUBTOTAL(9,OFFSET(D9:D32,,{0,1,2,3}))),SUBTOTAL(9,OFFSET(D9:D32,,{0,1,2,3})),)&"季度"<br/>2.=MATCH(MAX(SUMIF(INDIRECT({"D9:D32","E9:E32","F9:F32","G9:G32"}),"<>")),SUMIF(INDIRECT({"D9:D32","E9:E32","F9:F32","G9:G32"}),"<>"))&"季度"<br/>3.=MATCH(MAX(MMULT(COLUMN(A1:X1)^0,--TEXT(D9:G32,"0;;;!0"))),MMULT(COLUMN(A1:X1)^0,--TEXT(D9:G32,"0;;;!0")))&"季度"</font></p>
[此贴子已经被作者于2008-7-12 8:48:19编辑过] |