辦公表格常用公式大全.doc

1=if(countif(a:a,a2)>1,)2=trunc(days360(h6,2009/8/30,false)/360,0)318=concatenate(mid(e2,7,4),/,mid(e2,11,2),/,mid(e2,13,2)4=if(len(c2)=15,if(mod(mid(c2,15,1),2)=1,),if(mod(mid(c2,17,1),2)=1,)c21 =sum(k2:k56) k2k56;2 =average(k2:k56) k2 k56;3 =rank(k2k$2:k$56) 55;4 =if(k2>=85,if(k2>=74,if(k2>=60,)5 =k2*0.3+m2*0.3+n2*0.4 kmn;6 =max(k2:k56) k2k56(55);7 =min(k2:k56) k2k56(55);8(1) =countif(k2:k56,100) k2k56100;k57;(2) =countif(k2:k56,>=95)-k57 k2k569599.5;k58;(3)=countif(k2:k56,>=90)-sum(k57:k58) k2k569094.5;k59;(4)=countif(k2:k56,>=85)-sum(k57:k59) k2k568589.5;k60;(5)=countif(k2:k56,>=70)-sum(k57:k60) k2k567084.5;k61;(6)=countif(k2:k56,>=60)-sum(k57:k61) k2k566069.5;k62;(7) =countif(k2:k56,<60) k2k5660;k63;countif=countif(c2:c351,) c2c351(350);9 =sum(k57:k60)/55*10010 =sum(k57:k62)/55*10011 =stdev(k2:k56) k2k56(55)();12 =sumif(b2:b56,k2:k56) bk;13 =sum(if(c3:c322=,if(g3:g322=1,1,0) c(c3:c322)g(g3:g322)(12345) ;ctrl+shift+enter(?)14=trunc(days360(d3,now( )/360,0)de15word-=excela11c1a1<0c11c1>1 =a1=12->-> 2 =and(a1>0,a1<1)3->-> 3 =a1<0->->4excel1->->5()2a->=countif($a:$a,$a1)>1->->excelba?()aba2->: =b2->-> a2b()ab()a2->:=countif($b:$b,$a2)=0->-> a2bababbaexcel()?,1aef1 =large($a1:$e1,column(a1)fj/1(),:=small($a1:$e1,column(a1)abcd104=sum(if(b2:b9999=)*(c2:c9999>=104)*(d2:d9999=),1,0)ctrl+shift+enter,?a1,: =if(countif(a1,*&&*)=1,) =if(iserror(find(,a1,1),) a1:a100=sum(1/countif(a1:a100,a1:a100)ctrl+shift+enter,=sumproduct(1/countif(a1:a100,a1:a100)13.0ctrl+f3x=mid(get.workbook(1),find(,get.workboo