Excel 2007º¯Êý¹«Ê½ÊµÀý»ã×Ü

{=MMULT(TRANSPOSE(SUBTOTAL(9,OFFSET(B1,ROW(2:11)-1,1,,5))*MMULT((EXACT(B2:B11,TRANSPOSE(µ¥ ¼Û±í!A2:A5)))*TRANSPOSE(µ¥¼Û±í!B2:B5),{1;1;1;1})),1*(A2:A11={\×é\×é\×é\ ÒýÓÃÏúÊÛ½ð¶î¸ßÓÚ200´ÎÊý×î¶à

Õß:{=INDEX(A:A,RIGHT(MAX(MMULT((B2:H9>200)*1,TRANSPOSE(COLUMN(B:H)^0))*10+ROW(2:9))))} ¸ù¾ÝÆÀίÆÀ·ÖºÍȨÖØ·ÖÅäͳ¼Æ×îºóµÃ

·Ö:{=SUM(B2:F8*(A2:A8=B10)*TRANSPOSE(I2:I6))} ÂÞ ÁÐÑ¡Êֵ÷ÖÇ°ÈýÃûµÄÐÕÃû:

{=OFFSET($A1,RIGHT(LARGE(MMULT($B2:$F8*TRANSPOSE($I2:$I6),TRANSPOSE(COLUMN($B:$F)^0))*10^6+ROW(2:8),COLUMN(A1)),2)-1,,)}

¸ù ¾Ý×ÖĸÆÀÓïת»»µÃ·Ö:{=MMULT(TRANSPOSE(ÆÀÓï»»ËãµÃ·Ö!A$2:A$11=TRANSPOSE(E2:E11))*1,ÆÀÓï»»ËãµÃ ·Ö!B$2:B$11)+SUBTOTAL(9,OFFSET(B2,ROW(2:11)-2,,,COLUMNS(B:D)))} ¶àÁС¢¸ôÐÐÊý¾Ý»ã×Ü:{=SUM(MMULT(D2:G11,TRANSPOSE(COLUMN(D:G)^0))*(A2:A11=\ÕÔ»¹Öé\¼ÆËã·¸¹æµÍÓÚ3´ÎµÄÈË Êý:{=SUM(N(MMULT(--(B2:B21=TRANSPOSE(B2:B21)),ROW(2:21)^0)={1,2})/{1,2})} ÌáÈ¡ÐÕÃû:=INDEX(B:B,ROW()*2)&\

´Óµç»°²¾ÖÐÑ¡ÔñÐÔÒýÓÃÊý¾Ý:=INDEX($A:$B,ROW(A1)*3-2,COLUMN(A:A)) Ïû³ý³§ÅÆ´òÓ¡×ÊÁÏÕÕƬ

ÐÐ:{=INDEX(A:A,SMALL(IF(MOD(ROW($1:$12),3)>0,ROW($1:$12),1048576),ROW(A1)))&\ ÂÞÁÐÓÅÐãÔ±¹¤:{=INDEX(A:A,MOD(SMALL(B$2:B$11*100+ROW($2:$11),ROW(8:8)),100))} ²åÈë¿ÕÐзָîÊý¾Ý:=IF(MOD(ROW(),3)>0,INDEX(A:A,ROW(A2)*2/3),\

½ö½öÌáȡͨѶ¼ÖÐËÄ·ÖÖ®ÈýÐÅÏ¢:=INDEX(A:B,ROW(A2)*2/3,(MOD(ROW(A3),3)+1)/3+1) ÂÞ ÁÐ12ÔÂÖвúÁ¿µ¹ÊýµÚÒ»Ãû´ÎÊý×î¶àÕßÃûµ¥:

{=INDEX(B:B,SMALL(IF((COUNTIF(B$2:B$13,B$2:B$13)=MAX(COUNTIF($B$2:$B$13,$B$2:$B$13)))*(MATCH($B$2:$B$13,$B$2:$B$13,0)=ROW($2:$13)-1),ROW($2:$13),1048576),ROW(A1)))&\ °´ ͶËß´ÎÊýÉýÐòÅÅÁпͷþÐÕÃû:

{=INDEX(B:B,MOD(SMALL(IF(MATCH(B$2:B$12,B$2:B$12,)=ROW($2:$12)-1,COUNTIF(B$2:B$12,B$2:B$

12)*10^5+IF(MATCH(B$2:B$12,B$2:B$12,)=ROW($2:$12)-1,ROW($2:$12),9999999),9999999),ROW(A1)),10^5))&\

¼ÆËã60·Öµ½95·ÖÖ®¼äµÄÈËÔ±¸öÊý:=INDEX(FREQUENCY(B2:B11,{60,95}),2) ÂÞ Áе¼Ö²úÆ·²»Á¼µÄÖ÷Òò: {=IFERROR(T(INDEX($A:$A,SMALL(IF($B$2:$B$11=LARGE(IF(FREQUENCY($B$2:$B$11,$B$2:$B$11),$B$2:$B$11),ROW(A1)),ROW($2:$11)),COLUMN(A1)))),\

¶à ¹¤×÷±í²éÕÒ×î´ó

Öµ:{=TEXT(VLOOKUP(MAX(SUBTOTAL(9,INDIRECT(TEXT(ROW(1:6),\Äê ¼¶

!B\

[DBNum1]\

Äê

¼¶!B\

¶Ô´øÓкϲ¢µ¥Ôª¸ñµÄÇøÓò²éÕÒÄê¼ÙÌìÊý:=VLOOKUP(F2,OFFSET(B2,MATCH(E2,A2:A13,0)-1,,4,2),2) ²éÕÒijҵÎñÔ±ÔÚij¼¾¶ÈµÄÏúÁ¿:=HLOOKUP(G2,A1:E9,MATCH(H2,A:A,0),0) ÔÚͬһÐвéÕÒÊý¾Ý:{=HLOOKUP(MAX(A2:H2),IF({1;0},B2:H2,A2:G2),2,FALSE)} ¼Æ

Ëã

Á½

¸ö

²ú

Æ·

²»

ͬ

ʱ

ÆÚ

µÄ

µ¥

¼Û

:=HLOOKUP(MONTH(A2),IF(B2=\

ËÜ

½º»ú

\

¶à Ìõ¼þ¼ÆËã¼Ó°à·Ñ:=TEXT(HOUR(B2)+HLOOKUP(MINUTE(B2),{0,20.0001,50.0001; 0,0.5,1},2),\ ¸ù¾Ý½ø³§ÈÕÆÚ¼ÆËãÓÐн¼ÙÌì

Êý:=HLOOKUP(DATEDIF(B2,TODAY(),\ÖÆ×÷×¼¿¼Ö¤:=HLOOKUP(B2,ѧÉúµµ°¸¿â!$1:$11,ROUNDUP(COLUMN()/5,0)+1+INT(ROW()/7)*2,FALSE)

²»Çø·Ö´óСдÅжÏÁ½ÁÐÏàͬÊý¾Ý¸öÊý:{=COUNT(MATCH(A2:A11,B2:B11,0))} °´ºº×ÖÆÀÓï

:{=INDEX(A:B,MOD(SMALL(MATCH($B$2:$B$12,

ÅÅ

A·û

Ãû

±ê

×¼!$A$2:$A$9,)*100+ROW($B$2:$B$12),ROW(2:12)-1),100),{1,2})} ÌáÈ¡¾Ý×Ö

:{=INDIRECT(\

Ìá

È¡

×Ö

ÁÐ×îºóÒ»¸öÊý´®

ÖÐ

µÄ

ºº°¡

:{=MID(A2,MATCH(1,1/(MID(A2,ROW($1:$99),1)>=\

\°¡\ ½«ÎļþºÅÖеÄÖÐÎÄ´óдתСд:{=\µÚ

\ºÅÎļþ\

¼ÆËã²¹¿Î¿ÆÄ¿×ÜÊý:{=COUNT(0/(MATCH(B2:B8,B2:B8,0)=ROW(2:8)-1))} ²úÉú»ìºÏ±àºÅ:=TEXT(COUNTIF(C$1:C1,\¡©\ ÌáÈ¡³Ùµ½´ÎÊý×î¶àÕßÐÕÃû:=INDEX(B2:B11,MODE(MATCH(B$2:B$11,B$2:B$11,0))) ÂÞÁжà´Î³Ùµ½ÕßÐÕ

Ãû:{=IFERROR(INDEX(B$2:B$11,MODE(IF(COUNTIF(D$1:D1,B$2:B$11)=0,MATCH(B$2:B$11,B$2:B$11,0)))),\

Çø·Ö¡¢²»Çø·Ö´óСдͳ¼Æ×Ö·û¸ö

Êý:{=COUNT(0/(MATCH(MID(A2,ROW($1:$100),1),MID(A2,ROW($1:$100),1),0)=ROW($1:$100)))-1} °´½ð¡¢Òø¡¢Í­ÅÆÅÅÃû

´Î:{=MATCH(B2:B11+C2:C11%+D2:D11%%,LARGE(B2:B11+C2:C11%+D2:D11%%,ROW(2:11)-1),0)} °´ °à¼¶²åÈë·Ö¸ôÐÐ:

{=INDEX(A:B,MOD(SMALL(IF({1,0},ROW(2:11)*1001,IF(ROW(2:11)-1=MATCH(A2:A11,A2:A11,0),((MATCH(A2:A11,A2:A11,)+COUNTIF(A2:A11,A2:A11))*1000+100),1048576)),ROW(1:100)),1000),{1,2})&\ ͳ¼ÆÒ»¡¢¶þ°à¾ÙÖزÎÈüÈËÔ±Êý:{=COUNT(MATCH(B2:B11&C2:C11,{\Ò»°à\¶þ°à\¾ÙÖØ\ ÀÛ ¼ÆÏúÁ¿²¢ÁгöÅÅÐаñ:

{=OFFSET($B$1,MATCH(1,N(MAX(IF(COUNTIF($D$1:D1,B$2:B$12)=0,SUMIF(B$2:B$12,B$2:B$12,C$2:C$12)))=IF(COUNTIF($D$1:D1,B$2:B$12)=0,SUMIF(B$2:B$12,B$2:B$12,C$2:C$12))),),)&\ ÀûÓù«Ê½¶ÔÈë¿â±í½øÐÐÊý¾Ý·Ö

Îö:{=INDEX(B:B,SMALL(IF(MATCH(B$2:B$200,B$2:B$200,0)=ROW($2:$200)-1,ROW($2:$200),65536),ROW(A1)))&\

ÂÞÁÐÿ¸öµØÇøµÄ»ñ½±ÈËÔ±ÐÕ

Ãû:{=IFERROR(INDEX($A:$A,MATCH(1,(COUNTIF(E$1:E1,$A$2:$A$10)=0)*($B$2:$B$10=E$1),)+1),\ ¶ÔºÏ²¢ÇøÓò½øÐÐÊý¾Ý²éѯ:=OFFSET(B1,MATCH(G2,A2:A13,0)-1+MATCH(H2,{\±ùÏä\¿Õµ÷\Ï´Ò»ú\

½« һάÈËÊÂ×ÊÁϱíת¶þά:{=REPLACE(IFERROR(OFFSET($A$1,MATCH(C$1:F$1& amp;\£º

*\ Çø·Ö´óСд²éÕÒµ¥¼Û:{=INDEX(B:B,MATCH(0,0/EXACT(E1,A1:A8),0))} ¸ù¾ÝÐÕÃû²éÕÒ×ó±ßµÄÉí·ÝÖ¤ºÅ:=LOOKUP(E2,B2:B9,A2:A9) ½«ÖÐÎÄ´óд±àºÅת»»³É°¢Î»²®Êý×ÖС

д:=TEXT(LOOKUP(1,0/(B2=TEXT(ROW($1:$1000),\ ½«ÐÕÃû°´Æ´ÒôÉýÐòÅÅ

ÁÐ:{=LOOKUP(0,0/(ROW(A1)=MMULT(N($A$2:$A$11>=TRANSPOSE($A$2:$A$11)),ROW($2:$11)^0)),A$2:A$11)} ½«¾Æµê°´ÐǼ¶½µÐòÅÅ

ÁÐ:{=LOOKUP(ROUND(1/MOD(LARGE(LEN(B$2:B$10)+1/ROW($2:$10),ROW(A1)),1),0),ROW($2:$10),A$2:A$10)}

¼ÆËãij°àÁùÄêÖÐË­»ñµÚÒ»Ãû´ÎÊý×î¶à:{=MAX(COUNTIF(B2:B7,B2:B7))} ÂÞ ÁÐÿ¸öÃû´ÎµÄËùÓÐÐÕÃû:{=IFERROR(INDEX($A:$A,

(SMALL(IF($B$2:$B$11=LARGE(IF(FREQUENCY($B$2:$B$11,$B$2:$B$11),$B$2:$B$11),ROW(A1)),ROW($2:$11)),COLUMN(A2)))),\ ÌáÈ¡ÐÂÊéµÄÓ¡Ë¢Åú

´Î:=LOOKUP(9E+307,--RIGHT(LEFT(A2,FIND(\ÂÞ ÁÐ2008ÄêÿÔµÚÒ»¸ö¼°×îºóÒ»¸öÐÇÆÚÈÕ:

{=MIN(IF(WEEKDAY(DATE(2008,ROW(A1),ROW(INDIRECT(\7,DATE(2008,ROW(A1),ROW(INDIRECT(\ Ìî²¹¿Õ°×Çø:=LOOKUP(1,0/($A$2:A2<>\