=IF(A2=\
Èç¹û¸ô¼¸ÐÐÅÅÃû£¬ÈçÏÂ±í£¬µÚÎåÐС¢µÚ¾ÅÐк͵ÚÊ®¶þÐв»²ÎÓëÅÅÃû¡£ µ¥Î» Êý¾Ý ÅÅÃû A 1 8 A 5 7 A 6 6 С¼Æ 12 B 8 4 B 9 3 B 7 5 С¼Æ 24 C 18 1 C 11 2 С¼Æ 29
=IF(A2=\С¼Æ\ ÏÂÀ
¸ù¾Ý·ÖÊý½øÐе¹ÅÅÃû
=RANK($E3,$E$3:$E$22,1) =RANK(K60,$K$60:$K$83,1)
=COUNTIF($K$60:$K$83,\
µ¹ÊýÅÅÃûº¯ÊýÊÇʲô
1ΪÕýÅÅÐò£¬0ΪÄæÅÅÐò¡£ µ¹ÊýÅÅÃû=RANK(A2,$A$2:$A$5,0) ÕýÊýÅÅÃû=RANK(A2,$A$2:$A$5,1)
ÈçºÎʵÏÖÿÈÕ¸÷³µ¼ä²úÁ¿µÄÅÅÃû
=RANK(C2,OFFSET($C$2:$C$5,MATCH(A2,$A$2:$A$1000,0)-1,,,)) =RANK(C2,OFFSET($C$2:$C$5,MATCH(A2,$A$2:$A$33,0)-1,,,))
·ÖÊýÏàͬʱ°´ÕÕÒ»¿ÆµÄ·ÖÊý½øÐÐÅÅÃû
£û=MATCH(K308*100+D308,LARGE($K$308:$K$331*100+$D$308:$D$331,ROW($K$308:$K$331)-307),)£ý
¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«¡«
ɸѡºó×Ô¶¯²úÉúÐòÁкŲ¢»ã×Ü
×Ô¶¯²úÉúÐòÁкţºÔÚA1ÊäÈëÒÔϹ«Ê½£¬ÍùÏÂÍÏ¡£ =SUBTOTAL(3,$B$2:B2)*1
×Ô¶¯»ã×Ü£¬ÓÃÒÔϹ«Ê½£º =SUBTOTAL(9,$B$2:B2)
˵Ã÷£º»ã×Üʱ£¬²»ÒªÔÚ¡°È«Ñ¡¡±×´Ì¬Ï½øÐУ¬ÏÈ¡°É¸Ñ¡¡±³öijһµ¥Î»£¬×Ô¶¯ÇóºÍ¡Æ¡£È»ºóÔÙ»Ö¸´µ½¡°È«Ñ¡¡±»òÕßÑ¡ÔñÈκε¥Î»£¬¾ÍÄÜ×Ô¶¯»ã×ÜÁË£¨ÔÚ¡°É¸Ñ¡¡±³öijһµ¥Î»½øÐÐÇóºÍʱ£¬Ò»°ã±í¸ñ»á×Ô¶¯²úÉúÒÔÉÏ»ã×ܹ«Ê½£©¡£
ÆäËü£ºÈçͬʱҪÔÚÆäËüµ¥Ôª¸ñÏÔʾÈËÊý£¬ÔÚ¡°È«Ñ¡¡±×´Ì¬Ï£¬Ñ¡¶¨µ¥Ôª¸ñ£¬µã¡°fx¡±£¨ÓÃ
16
¡°sum¡±º¯Êý£©ÔÙµã»÷ÐòÁкÅ×îĩβÊý£¬¼´¿É¡£
ÈçºÎɸѡÆæÊýÐÐ
¹«Ê½=MOD(A1,2)=1
º¯ÊýɸѡÐÕÃû
ÈçºÎ°ÑÁ½ÁÐÖÐÖ»Òª°üº¬AºÍA+µÄÈËԱɸѡ³öÀ´
=IF(ISNUMBER(FIND(\
Ãû´Îɸѡ
Ãû´Î=RANK(K5,K$2:K$435)
°àÃû´Î=RANK(K6,OFFSET(K$2,MATCH(A6,A:A,)-2,,COUNTIF(A$1:A$500,A6)))
ÈçºÎʵÏÖ¿ìËÙ¶¨Î»£¨É¸Ñ¡³ö²»Öظ´Öµ£©
=IF(COUNTIF($A$2:A2,A2)=1,A2,\
=IF((COUNTIF($A$2:A2,A2)=1)=TRUE,A2,\
=INDEX(A:A,SMALL(IF(MATCH(A$1:A$20,A$1:A$20,)=ROW($1:$20),ROW(A$1:A$20),65536),ROW()))&\£¨Êý×鹫ʽ£©
ÈçºÎÇëÔÚNÁÐÖÐÁгöA1:L9ÖÐÿÁж¼´æÔÚµÄÊýÖµ
£û=IF(ROW()>SUM(--x),\£ý
×Ô¶¯ÎªÐÔ±ð±àºÅµÄÎÊÌâ
ÓÐÒ»¸ö±àÂë,5룬µÚ1λ,1ΪÄÐ,2ΪŮ£¬ºóÃæ4λ,´ú±íËûµÄ±àºÅ,´Ó0001-9999£¬ÈçºÎ´ïµ½ÏÂ±í£º
ÐÔ±ð ±àÂë ÄÐ 10001 ÄÐ 10002 Å® 20001 ÄÐ 10003 Å® 20002
ÄеÄÒ²ÊÇ´Ó0001-9999 Å®µÄÒ²ÊÇ´Ó0001-9999
Èç¹ûÄãÊÇÒѾÊäÈëÁËÆäËüÐÅÏ¢£¬½ö½öΪ¿ìËÙÊäÈë±àÂëµÄ»°¡£ÓÃɸѡ¿ÉÒÔʵÏÖ°É¡£
ÏÈÒÔ¡°ÄС±Îª¹Ø¼ü×Ö½øÐÐÅÅÐò£¬È»ºóÔÚµÚÒ»¸öÄеıàÂëÊäÈë10001£¬ÏÂÀ¸´ÖƵ½×îºóÒ»µ¥¼´¿É¡£Í¬ÀíÔÙÒÔ¡°Å®¡±ÅÅÐò¡£Íê³ÉÄ¿±ê¡£
Óù«Ê½£º
=IF(A2=\ÏòÏÂÍÏ
©L©L©L©L©L©L©L©L©L©L©L©L©L©L©L©L©L©L©L©L©L©L©L©L©L ¡¾Îı¾ÓëÒ³ÃæÉèÖá¿
17
EXCELÖÐÈçºÎɾ³ý*ºÅ
ÔÚ¼ÈëÕʺÅÊǼÈëÁË*ºÅ£¬ÈçºÎɾ³ý¡£ ¿ÉÒÔÓú¯Êý SUBSTITUTE(a1,\²éÕÒ~*£¬Ì滻Ϊ¿Õ¡£
½«×Ö·û´®ÖеÄÐǺš°*¡±Ì滻ΪÆäËü×Ö·û
ÔÚ²éÕÒÀ¸ÊäÈë~* Ì滻Ϊ¡°-¡±¼´¿É¡£
È¥¿Õ¸ñº¯Êý
ÈçºÎɾȥµ¥Ôª¸ñÖеĿոñ£¬ÈçÐÕÃûÇ°£¬ÖУ¬ºóµÄ¿Õ¸ñ£¬¼´µ¥Ôª¸ñÖÐÊÇÁ½¸ö×ÖµÄÈËÃûÖмäÓÐÒ»¸ö¿Õ¸ñ£¬ÏëɾȥÓкη½·¨¡£È磺ÖÐ ¹ú£¬¸ÄΪ£ºÖйú¡£
1¡¢Óù«Ê½£º=SUBSTITUTE(A2,\ ×¢£ºµÚÒ»¶ÔË«ÒýºÅÖÐÓÐÒ»¿Õ¸ñ¡£¶øµÚ¶þ¸ö¡°¡±ÖÐÊÇÎÞ¿Õ¸ñµÄ¡£
2¡¢ÀûÓòéÕÒ£Ìæ»»£¬Ò»´ÎÐÔÈ«²¿½â¾ö¡£ ¡°±à¼¡±£¡°Ìæ»»¡±£¨»òCtrl+H£©£¬ÔÚ¡°²éÕÒ¡±À¸ÄÚÊäÈëÒ»¿Õ¸ñ£¬¡°Ìæ»»¡±Ê²Ã´Ò²²»ÊäÈ루¿Õ°×£©¡£È»ºó¡°È«²¿Ìæ»»¡±¼´¿É¡£
3¡¢ÓÐÒ»¸öרÃÅɾ³ý¿Õ¸ñµÄº¯Êý: TRIM()
ÔÚEXCEL±à¼À¸À²»¹ÜÊäÖÐÎÄ»¹ÊÇÓ¢ÎÄÖ»ÄÜÊäÒ»¸ö×ֽڵĿոñ£¬µ«Èç¹û×ÖÓë×ÖÖмäÊÇÁ½¸ö×ֽڵĿոñ£¬ÄÇôTRIM£¨£©¾Í²»Æð×÷ÓÃÁË£¬Ëü¾Í²»ÈÏΪÊÇÒ»¸ö¿Õ¸ñ£¬¶øÊÇÒ»¸öºº×Ö£¬Ôõôȥ¡°TRIM¡±Ò²Ã»Óá£È磺µ¥Ôª¸ñA1ÖÐÓС°ÖÐ ÐÄ ÊÇ¡±£¬Èç¹ûÓÃTRIMÔò±ä³É¡°ÖÐ ÐÄ ÊÇ¡±£¬ Ï뽫¿Õ¸ñÈ«È¥µô£¬Ö»ÄÜÓÃSUBSTITUDE£¨£©º¯Êý£¬¶àÉÙ¿Õ¸ñ¶¼ÄÜÈ¥µô¡£
ÈçºÎÈ¥µô×Ö·ûºÍµ¥Ôª¸ñÀïµÄ¿Õ¸ñ
8900079501 8900079501~ 1900078801 1900078802~ =SUBSTITUTE(B2,\
ÔõÑù¿ìËÙÈ¥³ý±íÖв»Í¬ÐкÍÁеĿոñ
±à¼-¶¨Î»-¶¨Î»Ìõ¼þ-¿ÕÖµ,¿ÉÑ¡ÖÐËùÓпյ¥Ôª¸ñ, ÔÙɾ³ý¡£
ÈçºÎ½ûÖ¹ÊäÈë¿Õ¸ñ
ÔÚExcelÖÐÈçºÎͨ¹ý±à¼¡°ÓÐЧÊý¾Ý¡±À´½ûֹ¼Èë¿Õ¸ñ£¿·³Çë´óÏÀÃÇ·ÑÐĽâ´ð¡£ ½â´ð£ºÓÐЧÐÔ¹«Ê½¡£=COUNTIF(A1,\
(×¢£ºCOUNTIF(A1,\ÔÚµ¥Ôª¸ñÓпոñʱ½á¹ûΪ1,ûÓпոñʱ½á¹ûΪ0 ÈçÏ£ÍûµÚһλ²»ÄÜÊäÈë¿Õ¸ñ:countif(a1,\ÈçÏ£Íû×îºóһλ²»ÄÜÊäÈë¿Õ¸ñ:countif(a1,\
´úÌæµ¥Ôª¸ñÖÐ×Ö·û´®
µ¥Ôª¸ñ±àºÅ£¬¿ªÊ¼Î»Êý£¬´Ó¿ªÊ¼Î»ÊýËãÆðµÚ¼¸Î»Êý£¬ÒªÓÃÓÚ´úÌæµÄµÄ×Ö·û´®¡£ windows2000±ä³Éwindows2K =REPLACE(B2,8,3,\
µ¥Ôª¸ñ±àºÅ£¬Òª´úÌæµôµÄ×Ö·û£¬ÒªÓÃ×÷´úÌæµÄ×Ö·û£¬µÚ¼¸¸ö¡£
18
´úÌæµ¥Ôª¸ñB391ÖеÄÈ«²¿TT£¬¸ÄΪUU¡£ EETTCCTTFF±ä³ÉEEUUCCUUFF =SUBSTITUTE(B394,\
Ö»´úÌæµ¥Ôª¸ñB391ÖеĵÚÒ»´Î³öÏÖµÄTT£¬¸ÄΪUU¡£ EETTCCTTFF±ä³ÉEEUUCCTTFF =SUBSTITUTE(B397,\
°Ñµ¥Ôª¸ñÖеÄÊý×Öת±ä³ÉΪÌض¨µÄ×Ö·û¸ñʽ
º¯ÊýÖеĵڶþ¸ö²ÎÊýµÄË«ÒýºÅÒ»¶¨²»ÄÜÊÇÖÐÎĸñʽµÄ£¨²»ÄÜÓÃÈÎÒâÖÐÎÄÊäÈë·¨ÊäÈëµÄË«ÒýºÅ¡££©
ʵÀý£º 20000 Ä¿µÄ£º ±ä³É´øÓÐÃÀÔª·ûºÅµÄ×Ö·û 10000 ±ä³É´øÓÐÈËÃñ±Ò·ûºÅµÄ×Ö·û 151581 ±ä³É´øÓÐÅ·Ôª·ûºÅµÄ×Ö·û 1451451 ±ä³ÉÖÐÎÄ·±ÌåµÄ×Ö·û 15748415 ±ä³ÉÖÐÎļòÌåµÄ×Ö·û ²Ù×÷²½Ö裺 =TEXT(B72,\ ½á¹û£º $20000.00 =TEXT(B73,\0.00\ £¤10000.00 =TEXT(B74,\) €151581.00 =TEXT(B75,\ͨÓøñʽ\ Ò¼°ÛËÁÊ°ÎéÍòҼǪËÁ°ÛÎéÊ°Ò¼ =TEXT(B76,\ͨÓøñʽ\ һǧÎå°ÙÆßÊ®ËÄÍò°ËǧËÄ°ÙһʮÎå
°ÑÓÐÁù°Ù¶à¸öµ¥Ôª¸ñµÄÒ»ÁУ¬±ä³ÉÒ»Ò³µÄ¶àÁÐ
ÓÐÒ»ÕÅ±í£¬¹²ÓÐ14Ò³£¬µ«Ã¿Ò³Ö»ÓÐÒ»ÁУ¬ÈçºÎ°ÑËûÃÇÕûºÏÔÚÒ»Æ𣬱ä³ÉÒ»Ò³£¨°´Ã¿Ò³µÄ˳Ðò£©£¬Èç¹ûʹÓüôÇкÍÕ³ÌùµÄ·½Ê½£¬ÄÇÑùÌ«Âé·³¡£
=INDIRECT(\¸´ÖƵ½ÆäËûµ¥Ôª¸ñ
½«NÁбäMÁй«Ê½¹éÄÉΪ
=OFFSET($A$1,INT(((ROW(A1)-12)*m+COLUMN(A1)-1)/n),MOD((ROW(A1)-1)*m+COLUMN(A1)-1,n))
=OFFSET($A$1,INT(((ROW(A1)-1)*7+COLUMN(A1)-1)/4),MOD((ROW(A1)-1)*7+COLUMN(A1)-1,4)) ËÄÁбäÆßÁÐ
=OFFSET($A$1,INT(((ROW()-20)*10+COLUMN()-1)/7),MOD((ROW()-20)*10+COLUMN()-1,7)) ÆßÁбäÊ®ÁÐ
Ò»ÁбäËÄÁÐ
=OFFSET($A$1,ROW($A1)*4-COLUMNS(C:$F),) =OFFSET($A$1,(ROW()-3)*4+MOD(COLUMN()-8,4),) =OFFSET($A$1,ROW(A1)*4-4+MOD(COLUMN()-13,4),)
ËÄÁбäÒ»ÁÐ
=OFFSET($F$1,INT(ROW(1:1)/4+3/4)-1,MOD(ROW()-1,4)) =OFFSET($F$1,INT((ROW(1:1)-1)/4),MOD(ROW()-1,4))
=OFFSET($F$1,ROUNDUP((ROW(1:1)/4),0)-1,MOD(ROW()-1,4)) =OFFSET($F$1,(ROW()-1)/4,MOD(ROW()-1,4))
19
Öظ´ËÄ´ÎÌî³ä
=TEXT(INT(ROW()/4+3/4),\
=IF(TRUNC((ROW()-1)/4,0)<9,\1)
=TEXT(ROUNDUP(ROW()/4,),\ =TEXT(ROW(2:2)/4,\
¶àÐÐÊý¾ÝÅųÉÒ»ÁÐ
a1 b1 c1 d1 e1 f1 g1 h1 i1 a1 a2 b2 c2 d2 e2 g2 h2 i2 a2 a3 c3 d3 g3 h3 i3 a3 a4 c4 g4 h4 i4 a4 A5 c5 g5 h5 A5 g6 b1
£û=IF(ROW()>COUNTA($A$1:$I$10),\<>\$A$1:$I$10<>\£ý
½«µ¥Ôª¸ñÒ»ÁзÖΪ¶àÁÐ
Èç¹ûÓÐÒ»ÁÐ×ÊÁÏÐèÒª·ÖΪ¶àÁУ¬Ö»ÒªÏȽ«´ËÁÐÑ¡ÖУ¬È»ºóÔÙÑ¡Ôñ¡°Êý¾Ý¡±¡ú¡°·ÖÁС±£¬´Ëʱ»á³öÏÖÒ»¸ö¶Ô»°¿ò£¬Ñ¡¡°¹Ì¶¨¿í¶È¡±»ò¡°·Ö¸ô·ûºÅ¡±¡£ÈçΪǰÕßÔòÏÂÒ»²½ºóÖ»ÒªÓÃÊó±êÇáµã×ÊÁϼ´¿ÉÒÔ°´ÈÎÒâ¿í¶È½øÐзָîÁË£¬ÈçΪºóÕßÔòÖ»ÒªÓÐÃ÷ÏԵķָô·ûºÅ¼´¿É£¬ÏÂÒ»²½ºó¾Í¿ÉÒÔ×Ô¶¨Òå¸Õ·ÖµÄÁеĸñʽÁË£¬¶¨ºÃºó¾ÍËãÍê³ÉÁË¡£
²½Ö裺
1¡¢ÏÈÈ·¶¨1ÁеÄ×îÊʺϵÄÁпí,ÔÙ½«Æä¿í¶È³ËÒÔ·Ö³ÉÁÐÊý,¼´ ·ÖÁÐÇ°µÄÁпí=×îÊʺϵÄÁпí¡ÁÐè·Ö³ÉµÄÁÐÊý. 2¡¢±à¼¡ªÌî³ä¡ªÄÚÈÝÖØÅÅ¡£ 3¡¢Êý¾Ý¡ª·ÖÁС£
Ê×д×Öĸ´óд
°Ñµ¥Ôª¸ñ±àºÅÖеĵ¥´ÊÊ×д×Öĸ±ä³É´óд×Öĸ£¬ÆäÓà×Öĸ±ä³ÉСд¡£ Èçchina - China =PROPER(B160)
°Ñµ¥Ôª¸ñ±àºÅÖеÄСд×Öĸ±ä³É´óд×Öĸ
lafayette148 LAFAYETTE148 =UPPER(B1)
=LOWER(B1) £¨´óд×Öĸ±ä³ÉСд×Öĸ¹«Ê½£©
ÈÃÐÕÃû×óÓÒ¶ÔÆë
ÐÕÃûÓÃ×Ö£¬ÓеÄÊÇÈý¸öºº×Ö£¬ÓеÄÊÇÁ½¸öºº×Ö£¬´òÓ¡³öÀ´ºÜ²»ÃÀ¹Û£¬ÒªÊ¹ÐÕÃûÓÃ×ÖÊÇÁ½¸ö×ÖµÄÓëÈý¸ö×ÖµÄ×óÓÒ¶ÔÆëÒ²ÓÐÁ½ÖÖ·½·¨:
20
2011-ѧϰ×ÊÁÏ´óÈ«£ºexcel³£Óú¯Êý¹«Ê½¼°¼¼ÇÉËѼ¯
![](/skin/haowen/images/icon_star.png)
![](/skin/haowen/images/icon_star.png)
![](/skin/haowen/images/icon_star.png)
![](/skin/haowen/images/icon_star.png)