Excel 2007º¯Êý¹«Ê½ÊµÀý»ã×Ü
Excel2007º¯Êý¹«Ê½ÊÕ¼¯ÁË688¸öʵÀý£¬Éæ¼°µ½137¸öº¯Êý¡¢7¸öÐÐÒµ¡¢41ÀàÓÃ;£¬Îª´ó¼ÒÌṩһ¸ö²Î¿¼£¬ÍØÕ¹Ë¼Â·µÄ»ú»á¡£¹«Ê½ÓÉ{}°üÀ¨µÄΪÊý×鹫ʽ£¬ÔÚ¸´ÖÆÕ³Ìùµ½µ¥ÔªºóÏÈÈ¥µô{}È»ºó°´×¡Shift¼ü+Ctrl¼üÔÙ°´Enter¼ü£¬×Ô¶¯Éú³ÉÊý×鹫ʽ¡£
¶ÔÈý×éÉú²úÊý¾ÝÇóºÍ:=SUM(B2:B7,D2:D7,F2:F7)
¶ÔÉú²ú±íÖдóÓÚ100µÄ²úÁ¿½øÐÐÇóºÍ:{=SUM((B2:B11>100) *B2:B11)}
¶ÔÉú²ú±í´óÓÚ110»òÕßСÓÚ100µÄÊý¾ÝÇóºÍ:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)} ¶ÔÒ»³µ¼äÄÐÐÔÖ°¹¤µÄ¹¤×ÊÇóºÍ:{=SUM((B2:B10=\Ò»³µ¼ä\ÄÐ\ ¶ÔÐÕÕÔµÄŮְ¹¤¹¤×ÊÇóºÍ:{=SUM((LEFT(A2:A10)=\ÕÔ\Å®\ ÇóǰÈýÃû²úÁ¿Ö®ºÍ:=SUM(LARGE(B2:B10,{1,2,3})) ÇóËùÓй¤×÷±íÏàÍ¬ÇøÓòÊý¾ÝÖ®ºÍ:=SUM(A×é:E×é!B2:B9)
ÇóͼÊé¶©¹º¼Û¸ñ×ܺÍ:{=SUM((B2:E2=²Î¿¼¼Û¸ñ!A$2:A$7)*²Î¿¼¼Û¸ñ!B$2:B$7)} Çóµ±Ç°±íÒÔÍâµÄËùÓй¤×÷±íÏàÍ¬ÇøÓòµÄ×ܺÍ:=SUM(Ò»ÔÂ:ÎåÔÂ!B2) ÓÃSUMº¯Êý¼ÆÊý:{=SUM((B2:B9=\ÄÐ\Çó1ÀÛ¼Óµ½100Ö®ºÍ:{=SUM(ROW(1:100))}
¶à¸ö¹¤×÷±í²»Í¬ÇøÓòÇóǰÈýÃû²úÁ¿ºÍ:{=SUM(LARGE(CHOOSE({1,2,3,4,5},A×é!B2:B9,B×é!B2:B9,C×é!B2:B9,D×é!B2:B9,E×é!B2:B9),ROW(1:3)))}
¼ÆËã²Ö¿â½ø¿âÊýÁ¿Ö®ºÍ:=SUMIF(B2:B10,\½ø¿â\ ¼ÆËã²Ö¿â´ó¶î½ø¿âÊýÁ¿Ö®ºÍ:=SUMIF(B2:B8,\
¶Ô1400µ½1600Ö®¼äµÄ¹¤×ÊÇóºÍ:{=SUM(SUMIF(B2:B10,\Çó
ǰ
Èý
Ãû
ºÍ
ºó
Èý
Ãû
µÄ
Êý
¾Ý
Ö®
ºÍ:=SUMIF(B2:B10,\
¶ÔËùÓгµ¼äÈËÔ±µÄ¹¤×ÊÇóºÍ:=SUMIF(A2:A10,\³µ¼ä\¶Ô¶à¸ö³µ¼äÈËÔ±µÄ¹¤×ÊÇóºÍ:=SUMIF(A2:A10,\³µ¼ä*\
»ã×ÜÐÕÕÔ¡¢Áõ¡¢ÀîµÄÒµÎñÔ±Ìá³É½ð¶î:=SUM(SUMIF(A2:A10,{\ÕÔ\Áõ\Àî\
»ã×ÜÊó±êËùÔÚÁÐÖдóÓÚ600µÄÊý
¾Ý:=SUMIF(INDIRECT(\ Ö»»ã×Ü60¡«80·ÖµÄ³É¼¨:=SUMIFS(B2:B10,B2:B10,\ »ã×ÜÈýÄê¼¶¶þ°àÈËÔ±³Ùµ½´ÎÊý:=SUMIFS(D2:D10,B2:B10,\ÈýÄê¼¶\¶þ°à\
»ã×ܳµ¼äÅ®ÐÔÈËÊý:=SUMIFS(C2:C11,A2:A11,\³µ¼ä\Å®\¼ÆËã³µ¼äÄÐÐÔÓëÅ®ÐÔÈËÔ±µÄ²î:=SUM(SUMIFS(C2:C11,B2:B11,{\Å®\ÄÐ\³µ¼ä\ ¼ÆËã²Î±£ÈËÊý:=SUMPRODUCT((C2:C11=\ÊÇ\
Çó25ËêÒÔÉÏÄÐÐÔÈËÊý:=SUMPRODUCT((B2:B10=\ÄÐ\»ã×ÜÒ»°àÈËÔ±»ñ½±´ÎÊý:=SUMPRODUCT((B2:B11=\Ò»°à\
»ã×ÜÒ»³µ¼äÄÐÐԲα£ÈËÊý:=SUMPRODUCT((A2:A10&B2:B10&C2:C10=\Ò»³µ¼äÄÐÊÇ\ »ã×ÜËùÓгµ¼äÈËÔ±¹¤×Ê:=SUMPRODUCT(--NOT(ISERROR(FIND(\³µ¼ä\ »ã×ÜÒµÎñÔ±Òµ¼¨:=SUMPRODUCT((B2:B11={\½Î÷\¹ã¶«\ÄÐ\ ¸ù¾ÝÖ±½ÇÈý½ÇÐÎÖ®¹´¡¢¹ÉÇóÆäÏÒ³¤:=POWER(SUMSQ(B1,B2),1/2) ¼ÆËãA1£ºA10ÇøÓòÕýÊýµÄƽ·½ºÍ:{=SUMSQ(IF(A1:A10>0,A1:A10))} ¸ù
¾Ý
¶þ
±ß
³¤
ÅÐ
¶Ï
Èý
½Ç
ÐÎ
ÊÇ
·ñ
Ϊ
Ö±
½Ç
Èý
½Ç
ÐÎ:=CHOOSE((SUMSQ(MAX(B1:B3))=SUMSQ(LARGE(B1:B3,{2,3})))+1,\·ÇÖ±½Ç\Ö±½Ç\ ¼ÆËã1µ½10µÄ×ÔÈ»ÊýµÄ»ý:=FACT(10)
¼ÆËã50µ½60Ö®¼äµÄÕûÊýÏà³ËµÄ½á¹û:=FACT(60)/FACT(49) ¼ÆËã1µ½15Ö®¼äÆæÊýÏà³ËµÄ½á¹û:=FACTDOUBLE(15) ¼ÆËãÿСʱÉú²ú²úÖµ:=PRODUCT(C2:E2)
¸ù¾ÝÈý±ßÇóÆÕͨÈý½ÇÐÎÃæ»ý:=(PRODUCT(SUM(B1:B3)/2,SUM(B1:B3)/2-LARGE(B1:B3,{1,2,3})))^0.5 ¸ù¾ÝÖ±½ÇÈý½ÇÐÎÈý±ßÇóÈý½ÇÐÎÃæ»ý:=PRODUCT(LARGE(B1:B3,{2,3}))/2 ¿ç±íÇó»ý:=PRODUCT(²úÁ¿±í:µ¥¼Û±í!B2)
Çó²»Í¬µ¥¼ÛϵÄÀûÈó:{=MMULT(B2:B10,G2:H2)*25%}
ÖÆ×÷ÖÐÎľžų˷¨±í:=COLUMN()&\
¼ÆËã³µ¼äÓ¯¿÷:=SUM(MMULT((B3:E5>0)*B3:E5,{1;1;1;1}),MMULT((B3:E5<0)*B3:E5,{1;1;1;1})) ¼ÆËã¸÷×é±ðµÚÈýÃû²úÁ¿ÊǶàÉÙ:{=MAX(MMULT(COLUMN(A:E)^0,B2:G6))}
¼ÆËãC²úÆ·×î´óÈë¿âÁ¿:{=MAX(MMULT(N(A2:A11=\ ÇóÈë¿â×î¶àµÄ²úÆ·Êý
Á¿:{=MAX(MMULT(TRANSPOSE((B2:B11)*(A2:A11={\ ¼ÆËãÀÛ¼ÆÈë¿âÊý:{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11)} ¼ÆËãÿÈÕ¿â´æÊý:{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11-C2:C11)} ¼Æ
Ëã
A
²ú
Æ·
ÿ
ÈÕ
¿â
´æ
Êý:{=MMULT(N(ROW(2:17)>=TRANSPOSE(ROW(2:17))),(B2:B17=\ÇóµÚÒ»ÃûÈËÔ±×î¶àÓм¸´Î:{=MAX(MMULT(N(B2:B7=TRANSPOSE(B2:B7)),ROW(2:7)^0))} Ç󼸺ÅÑ¡ÊÖѡƱ×î
¶à:{=RIGHT(MAX(MMULT(N(B2:B10=TRANSPOSE(B2:B10)),ROW(2:10)^0)*100+B2:B10))} ×ܹ²Óм¸¸öÑ¡ÊÖ²ÎÑ¡:{=SUM(1/(MMULT(N(B2:B10=TRANSPOSE(B2:B10)),ROW(2:10)^0)))} ÔÚ²»Í¬°à¼¶ÓÐͬÃûǰÌáϼÆËãѧÉúÈËÊý:
{=SUM(1/MMULT(N(A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17)),ROW(2:17)^0))} ¼Æ
Ëã
ǰ
½ø
ÖÐ
ѧ
²Î
Èü
ÈË
Êý
:{=SUM(IFERROR(1/MMULT(N((A2:A17&B2:B17&
C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17))*(A2:A17=\ǰ½øÖРѧ\
´®Áªµ¥Ôª¸ñÖеÄÊý×Ö:{=MMULT(10^(COLUMNS(B:K)-COLUMN(C:L)),TRANSPOSE(B2:K2))}»ò=SUMPRODUCT(B2:K2,10^(COLUMNS(B:K)-COLUMN(B:K)-1))
¼ÆËã´ï±êÂÊ:{=MMULT(TRANSPOSE(N(A2:A11<=(B2:B11))),ROW(2:11)^0)/ROWS(2:11)} ¼Æ Ëã³É¼¨ÔÚ60-80·ÖÖ®¼äºÏ¼ÆÊýÓë¸öÊý:
ÇóºÍ{=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11< 80)*B2:B11),ROW(2:11)^0)}£¬ Çó¸öÊý{=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11& lt;80)),ROW(2:11)^0)}
»ã×ÜA×éÄÐÖ°¹¤µÄ¹¤×Ê:{=MMULT(TRANSPOSE(N(B2:B11&C2:C11=\ÄÐA×é\
¼ÆËãÏóÆå±ÈÈü¶Ô¾Ö´ÎÊýl:=COMBIN(B1,B2)
¼ÆËãÎåÏî±ÈÈü¶Ô¾Ö×Ü´ÎÊý:{=SUM(COMBIN(B2:B5,2))} Ô¤¼ÆËùÓÐÈüÊÂÍê³ÉµÄʱ¼ä:=COMBIN(B1,B2)*B3/B4/60 ¼ÆËãÓ¢ÎÄ×ÖÄ¸Çø·Ö´óСд×öÃÜÂëµÄ×éÊý:=PERMUT(B1*2,B2) ¼ÆËãÖн±ÂÊ:=TEXT(1/PERMUT(B1,B2),\ ¼ÆËã×î´ó¹«Ô¼Êý:=GCD(B1:B5) ¼ÆËã×îС¹«±¶Êý:=LCM(B1:B5) ¼ÆËãÓàÊý:=MOD(A2,B2)
»ã×ÜÆæÊýÐÐÊý¾Ý:=SUMPRODUCT(MOD(ROW(2:13),2)*C2:C13) ¸ù¾Ýµ¥¼ÛÊýÁ¿»ã×ܽð
¶î:=SUMPRODUCT(MOD(COLUMN(A:I),2)*A2:I2,(MOD(COLUMN(B:J),2)=0)*B2:J2)
Éè¼Æ¹¤×ÊÌõ:=IF(MOD(ROW(),3)=1,µ¥ÐбíÍ·¹¤×ÊÃ÷ϸ!A$1,IF(MOD(ROW(),3)=2,OFFSET(µ¥ÐбíÍ·¹¤×ÊÃ÷ϸ!A$1,ROW()/3+1,0),\
¸ù¾ÝÉí·ÝÖ¤ºÅ¼ÆËãÐÔ±ð:=IF(MOD(MID(B2,15,3),2),\ÄÐ\Å®\
ÿ¸ô4ÐкϼƲúÖµ:=IF(MOD(ROW(),5)=1,SUM(OFFSET(F2,-4,,4,)),D2*E2) ¹¤×ʽØÎ²È¡Õû:=B2+MOD(Ò»ÔÂ!B2,10)-MOD(B2+MOD(Ò»ÔÂ!B2,10),10) »ã×Ü3µÄ±¶ÊýÁеÄÊý¾Ý:{=SUM(IF(MOD(COLUMN(A:I),3)=0,A2:I10))} ½«ÊýÖµÖðλÏà¼Ó³ÉһλÊý:=IF(A2=0,0,MOD(A2-1,9)+1)
¼ÆËãÁã³®:5½Ç=INT(MOD(SUM(B2:B10),1)/0.5);2½Ç=INT(MOD(MOD(SUM(B2:B10),1),0.5)/0.2);1½Ç=MOD(MOD(MOD(SUM(B2:B10),1),0.5),0.2)/0.1 ÃëÓëСʱ¡¢·ÖÖӵĻ»
Ëã:=QUOTIENT(MOD($A2,IF(COLUMN()=2,A2+1,60^(3-COLUMN(A:A)+1))),60^(3-COLUMN(A:A))) Éú³É¸ôÐÐÀÛ¼ÓµÄÐòÁÐ:=QUOTIENT(ROW()+1,2)
¸ù¾ÝÒµ¼¨¼ÆËãÒµÎñÔ±½±½ð:=CHOOSE(MIN(QUOTIENT(B2,10000)+1,6),0,3%,5%,7%,9%,11%)*B2 ¼ÆËãÔ¤±¨Î¶ÈÓëʵ¼ÊζȵÄ×î´óÎó²îÖµ:{=MAX(ABS(C2:C8-B2:B8))}