µ«ÊÇÎÒ»¹ÏëÇóÒ»ÏÂÕâ¸öС¼ÆµÄ×ܺϣ¬ÎÒÏë¼ÓÈëÒ»¸ö¹«Ê½£¬µ«ÊÇÎÒÓÐSUMµ½ÁË50¶à¸öʱ¾Í¼Ó²»½øÈ¥ÁË£¬ÓüӺÅÁ¬¼ÓÒ²²»ÐУ¬²»ÖªÕâ¸öÎÊÌâÔõô½â¾ö ½â´ð£ºÓÃSubTotal()½â¾ö¼´¿É
ÈçºÎʹÊäÈëµÄÓ¢Îĵ¥´ÊµÚÒ»¸ö×Öĸ±ä³É´óд
½â´ð£ºPrivate Sub Worksheet_Change(ByVal Target As Range) Target.Value = Application.WorksheetFunction.Proper(Target.Text) End Sub
ÔÚÒ»¸ö±íÖÐÓÐÁ½ÁÐÈÕÆÚÐÍÊý×ÖÇëÎÊÈçºÎÔÚµÚÈýÁÐÖеõ½Æä²î(Á½ÈÕÆÚ¼äµÄÌìÊý)
½â´ð£º=DATEDIF(A1,B1,\
ÎÊ£ºÎÒÓÃÁËdatedif·¢ÏÖÒ»¸öÎÊÌâ ¡¢¼´µ±AÁÐBÁÐʱ³ö´íÁË ¡¢ÇëÎÊÓÃʲô°ì·¨½â¾ö
´ð£º=IF(A1>B1,DATEDIF(B1,A1,\
ÖØÒªµÄEXCELÎļþ»µÁË(Îļþ´øÃÜÂë)ÓÐûÓÐEXCELÐÞ¸´¹¤¾ß
½â´ð£ºExcel 2000
Êý¾Ý>>È¡µÃÍⲿÊý¾Ý>>ÐÂÔöÊý¾Ý¿â²éѯ>>Excel File*>>ÕÒµ½µµ°¸>> [Ñ¡Ïî]¹´Ñ¡ËùÓÐÑ¡Ïî>> »áÕÒµ½ËùÓÐδÃüÃû[Çø¿é]ÈçSheet1$ >>ÕÒµ½×Ö¶Î>>ÆäËü¸ú×Ųéѯ¾«Áéµ¼ÒýÒ»²½Ò»²½×÷ ´ËÖмä²ã×é¼þ½Ð×÷Microsoft Query ¿ÉÒÔÓÃÀ´Õü¾È[»ÙËðµµ°¸]
Ö»ÒªÇó¼òµ¥µÄ°ÑÊý¾Ý´Ó·Ö±íÖ±½Ó×·¼Óµ½×ܱí
½â´ð£ºÄã¿ÉÒÔÓÃLookup,vlookupµÈº¯Êý¡£
A1ÖÐÊäÈë11:22:22 ÒªÇóÿ¼ä¸ôÒ»ÃëË¢ÐÂÒ»´Î
½â´ð£º sub mytime range(\
Application.OnTime Now + TimeValue(\end sub
ÔõÑùÓú¯ÊýÀ´ÏÔʾijÔµÄ×îºóÒ»ÌìÊǼ¸ºÅ
´ð£º= DAY(DATE(Äê·Ý,Ô·Ý+1,1)-1)
ÈçºÎÓÃexcelÇó½âÁªÁ¢·½³Ì
x-x(7/y)^z=68 x-x(20/y)^z=61 x-x(30/y)^z=38
½â´ð£ºÕâÊÇÒ»¸öÖ¸Êýº¯ÊýµÄÁªÁз½³Ì¡£²½ÖèÈçÏ 1¡¢ÁîX/Y=W ÔòÓÐ X-(7W)^z=68
17
X-(20W)^Z=61 X-(30W)^Z=38 2¡¢ÏûÈ¥X (20^Z-7^Z)W^Z=7 (30^Z-20^Z)W^Z=23 3¡¢ÏûÈ¥W
(30^Z-20^Z)/(20^Z-7^Z)=23/7 ÓÉ´ËÇóµÃZ=3.542899 x=68.173955 y=781.81960
ÇëÎÊÈçºÎÔÚº¯ÊýÖÐͬʱʹÓÃÁ½¸öÌõ¼þ
Àý:ÔÚIFͬʱʹÓÃÌõ¼þB1>0ºÍB1<10 ½â´ð£ºand(B1>0,B1<10)
ÓÃTRIM°Ñ¡°ÖÐ ÐÄ ÊÇ¡±ÖмäµÄ¿Õ¸ñÈ¥µô
½â´ð£ºÓÃSUBSTITUDE£¨£©º¯Êý£¬¶àÉÙ¿Õ¸ñ¶¼ÄÜÈ¥µô¡£ÈçA1ÖÐÓУºÖÐ ÐÄ ÊÇ ÔòÔÚB1ÖÐʹÓÃ=SUBSTITUTE(A1,\\¾Í¿ÉÒÔÁË¡£×¢Ò⣺¹«Ê½ÖеĵÚÒ»¸ö¡° ¡±ÖмäÒªÓÐÒ»¸ö¿Õ¸ñ£¬¶øµÚ¶þ¸ö¡°¡±ÖÐÊÇÎÞ¿Õ¸ñµÄ¡£
½«EXCELÖеĵ¥Ôª¸ñ¶¨Òå³ÉÊý×é
ÎÒÔÚ±àд³ÌÐòʱÓöµ½ÕâÑùÒ»¸öÎÊÌâ, ÿ´ÎÓÃÑ»·³ÌÐòʱÏòµ¥Ôª¸ñд»ò¶ÁÊý¾Ýʱ×ÜÊÇÃæÏòÒ»¸ö¹Ì¶¨ÇøÓò,ÈçRange(\¡£ÇëÎÊÓÐûÓз½·¨ÄÜ×öµ½ÏñÊý×éÄÇÑù,½«µ¥Ôª¸ñµÄϱêÓɳ£Á¿±ä³É±äÁ¿!¡£ÈçÏÂÃæÕâÖÖÐÎʽ,ÄDZà³Ìʱ»á¼õÉٺܶàµÄ¹¤×÷Á¿!¡£Range(\
½â´ð£ºrange(cells(ÐÐÊý,ÁÐÊý),cells(ÐÐÊý,ÁÐÊý))¡¢ÐÐÊý ÁÐÊý¿ÉÓñäÁ¿´øÈë¡£»ò£ºRange(\&trin(str(value1)) & \
½«µ¥Ôª¸ñÐС¢ÁиßÓë¿íµ¥Î»ÉèÖóɺÁÃ×
½â´ð£ºÆäʵÐиߺÍÁпíµÄ»»Ëã×ÜÌâÊDz»Ì«¸´ÔÓµÄ,Ö»ÒªÓôòÓ¡»ú´òÓ¡Ò»¸öµ¥Ôª¸ñµÄ¿ò¼Ü,ÔÙÓóß×ÓÁ¿³öµ¥Ôª¸ñ¿ò¼ÜµÄ³¤ºÍ¿í,È»ºóÓÃÕâ¸öÖµ¸úµ¥Ôª¸ñµÄ°õÖµ½øÐл»Ëã¼´¿É.
Àý:µ¥Ôª¸ñµÄ¿í¶ÈÊÇ8.38°õ,¸ßÊÇ14.25°õ.´òÓ¡³öÀ´ºó¿í¶ÈÊÇ19.6mm,¸ßÊÇ6mm,ÕâÑù¾ÍµÃ³ö°õºÍºÁÃ×Ö®¼äµÄ»»Ëã¹ØÏµ
.¿í:19.6mm/8.38°õ=2.339mm/°õ; ¸ß:14.25°õ/6mm=2.375°õ/mm
ºÃÁË,ÎÒÏë²»ÓÃÎÒ¶à˵Äã¾Í»áÖÆ³ö¸úʵ¼Ê±í¸ñһģһÑùµÄ±í¸ñÀ´.
һЩµç»°ºÅÂëÈç¹ûÊÇ8½áβ,ºÅÂë¼Ó1;Èç¹û²»ÊÇ,ºÅÂë¼Ó0.
½â´ð£º1¡¢Èç¹ûA1£½1008 ÔÚB1ÖÐÊäÈë £½if(right(a1,1)=8,a1+1,a1),¿ÉÒÔʵÏÖÈç¹û½áβÊÇ8¼Ó1£¬²»ÊÇ8Ôò²»¼Ó¡£2¡¢Èç¹û²»ÊÇÉÏÊöÒâ˼ÔòΪ =if(right(a1,1)=8,concatenate(a1,1),concatenate(a1,0))¡£3¡¢Êµ¼ÊÉÏÓ¦¸ÃÊÇ£º£½if(right(a1,1)=8,a1&\¡£
18
µ±×öÈÕ±¨±íʱ,ÔõÑùÈÃÔÂÀÛ¼ÆÊý×Ô¶¯¼ÓÉÏ
¾ÍÊÇÒªÔÂÀÛ¼Æ×Ô¶¯¼ÓÉϽñÌìµÄµ±ÈÕÊÕÈëÊý,½ñÌìÖ»ÊäÈëµ±ÈÕÊÕÈë,ÎÒÏëÓÃÓÃǰһÌìµÄÔÂÀÛ¼ÆÊý¼ÓÉϽñÌìµÄµ±ÈÕÊÕÈëÊýΪ½ñÌìµÄÔÂÀÛ¼ÆÊý.Çé¿öÊÇÒ»ÔÂÒ»¸ö¹¤×÷±¡,ÿһ¸ö¹¤×÷±¡ÏÂ30¸ö¹¤×÷±í,ÓÃÿһÌìµÄÈÕÆÚΪ±¨±íÃû.ÎÒÏë°ÑÔÂÀۼƵĹ«Ê½Ð´Îª =sheet17!c5 ÖÐµÄ 17 Óà day(now())-1 µÄֵȥȡ´úËû,¸ÃÔõô°ìÄØ? ÒÔÏÂΪ±¨±í¸ñʽ,лл´ó¼Ò°ïÎÒÏëÒ»Ïë. ²¿ÃÅ-------µ±ÈÕÊÕÈë -----------ÔÂÀÛ¼Æ ÍŶÓÊÕÈë 12.12 123.00
д×Ö¼äÊÕÈë 147,258.00 147.147 ·¿ÄÚ°ÉÊÕÈë 147,258,369.00
½â´ð£º1¡¢=SUM('Sheet1:Sheet30'!C5)
ÔÚÄãÐèÒªÔ»ã×ܵĵ¥Ôª¸ñÌîÈëÉÏÊö¹«Ê½£¬Æä×÷ÓÃÊǽ«¹¤×÷±í1µ½¹¤×÷±í30µÄ¡°C5¡±µ¥Ôª¸ñµÄֵȫ²¿ÀÛ¼ÓÆðÀ´£¬¶ø¡°C5¡±µ¥Ôª¸ñÓ¦ÌîÈëµ±ÈÕµÄÊÕÈëÊý¡£
¡°ÎÒÏë°ÑÔÂÀۼƵĹ«Ê½Ð´Îª =sheet17!c5 ÖÐµÄ 17 Óà day(now())-1 µÄֵȥȡ´úËû,¸ÃÔõô°ìÄØ? ¡±---Õâ¿ÉÄÜÐèÒªVBA²ÅÄÜʵÏÖ¡£2¡¢ Èç¹û¹¤×÷ÖØ¸´µÄ»°£¬¿ÉÒÔÖÆ×÷Ò»¸öÄ£°å£¬¹«Ê½ÊÂÏȶ¨ÒåºÃ£¬Ã¿´ÎÓÃʱн¨Ò»¹¤×÷²¾¼´¿É¡£ ¡°°ÑÔÂÀۼƵĹ«Ê½Ð´Îª =sheet17!c5 ÖÐµÄ 17 Óà day(now())-1 µÄֵȥȡ´úËû¡±£¬»¹Ã»ÕÒµ½·½·¨£¬µ÷ÊÔºÃÁËÔÙ¸æËßÄã¡£3¡¢ =INDIRECT(ADDRESS(1,1,1,1,\
µ¥Ôª¸ñA1=£¨a2,a3,a4,a5)ÆäÖÐÒ»¸ö¾Í·µ»ØAA£¬Èç¹ûA1=a6,¾Í·µ»ØBB£¬·ñÔò¾Í·µ»ØCC
½â´ð£º=IF(OR(A2=A1,A3=A1,A4=A1,A5=A1),\
¹ØÓÚÒýÓü°¼ÆËãµÄÎÊÌâ
Sheet1!A1£½Sheet2!A3 /Sheet1!A2£½Sheet3!A3 /Sheet1!A3£½Sheet4!A3/........ /ÄÜÓÐʲô·½·¨¿ÉÒÔ¿ìËٵóöSheet1!A4¼°ÒÔϵÄÊýÖµ£¿£¨±ÈÈç˵ÖÁSheet1!A100£© /»¹ÓоÍÊÇ /Sheet1!A1£½
Sheet2!A3+Sheet2!B7 /Sheet1!A2£½Sheet3!A3+Sheet3!B7 /Sheet1!A3£½Sheet4!A3+Sheet4!B7/........ /ÓÖ¸ÃÈçºÎʵÏÖ£¿
½â´ð£º1¡¢=INDIRECT(\¡£2¡¢ÊýÁ¿1£º =INDIRECT((ROW()-1)&\ÊýÁ¿2£º =INDIRECT((ROW()-1)&\
ÈçºÎͳ¼ÆÄ³¸öÁÐÓÐÊý×ֵĸöÊý
½â´ð£º=COUNT(A:A)
ÈçºÎͳ¼Æ´Ë´Î×Ô¶¯É¸Ñ¡ ³öÀ´¹²ÓеļǼÌõÊý
½â´ð£ºÓà counta ͳ¼Æ
Ôõô°ÑµÈÓÚAÀ¸ÀïµÄÒ»¸öÖµµÄ,ÔÙͳ¼Æ³öBÀ¸ÀﲻͬÊý¾ÝµÄ¸öÊý
[A] [B]
a ±±¾© /a ¹ãÖÝ /a Ìì½ò /b ¹ãÖÝ /b ³¤É³ /a ±±¾©/b ±±¾© /b ³¤É³ ÎÒ»¹ÏëÔÙ¼Ó¸öÌõ¼þÄØ?±ÈÈç: AÀ¸ÊÇaµÄÓÐ3¸ö²»Í¬µÄ³ÇÊÐ,AÀ¸ÊÇbµÄÓÐ3¸ö²»Í¬µÄ³ÇÊÐ,))...
½â´ð£º1¡¢{=sum((a1:a10=\¹ãÖÝ\¡£2¡¢Äã¸ã´íÎÒµÄÒâ˼ÁË,ÎÒÊÇÏëÒª²»Í¬³ÇÊеÄÖµ,Èç:µ±AÀ¸ÖеÈÓÚaµÄ,¾Íͳ¼Æ³öBÀ¸ÖÐÓжàÉÙ¸ö²»Í¬µÄ³ÇÊÐÊýÁ¿,Æä½á¹ûÊÇ3(Èý¸ö²»Í¬µÄ³ÇÊÐ)¡£3¡¢¾ÍÄãÕâµÀÀýÌâÀ´½²£ºÉèÊý¾ÝÔÚA1:B8ÖУº
19
{=COUNT(LARGE(IF($A$1:$A$8=\:$A$5)))-IF(LARGE(IF($A$1:$A$8=\$A$1:$A$5))-LARGE(IF($A$2:$A$9=\
($B$2:$B$9))&CODE(RIGHT($B$2:$B$9)))*1,\¹«Ê½Ò²ÓоÖÏÞÐÔ£¬¾ÍÊdzÇÊÐ×î¶àÁ½¸ö×Ö£¬Èý¸öËĸöÒ²¿ÉÒÔ£¬µ«¿ÏÄÜ»á³ö´í£¬¾ÍÊǻ᩵ô
ÈçºÎÓÃIFº¯Êý´ïµ½ÎÒÏëÒªµÄ½á¹û
¹¤×÷±íÊý¾ÝÈçÏ£º AÁÐ BÁÐ CÁÐ DÁÐ EÁÐ
1 5 9 H /2 6 0 I /3 7 F J /4 8 G K /ÎÒÏëµ±AÁÐ=1»ò2»ò3 ʱ£¬EÁÐ=AÁжÔÓ¦µÄÖµ£¬/·ñÔòCONCATENATE(A2,B2,C2,D2) /½á¹ûÈçÏ£º AÁÐ BÁÐ CÁÐ DÁÐ EÁÐ
1 5 9 H 1 /2 6 0 I 2 /3 7 F J 3 /4 8 G K 48GK /ÇëÎÊÕâ¸ö¹«Ê½Ôõôд£¿ ½â´ð£º=IF(OR(A1=1,A1=2,A1=3),A1,A1&B1&C1&D1)
¹ØÓÚʵÏÖ¡°²éÕÒ²¢Ìæ»»¡±µÄºê
ÎÒÏàÒª±àдһ¸ö×Ô¶¯Ìæ»»µÄºê¡£ ÒªÌæ»»µÄÄÚÈÝÈçÏ£º °ÑÌØÊâ·ûºÅ¡°¨‹¡±Ì滻Ϊ ¡° ¨‹ ¡±
¼´°ÑÔÌØÊâ·ûºÅǰºó¸÷¼ÓÉÏÒ»¸ö»»Ðзû¡£ ½â´ð£ºSub Macro()
Cells.Replace What:=\¨‹\¨‹\:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End Sub
ÓÃÁËһϣ¬ÓÐÎÊÌ⣬°Ñ
¡°, SearchFormat:=False, ReplaceFormat:=False¡±
ɾ³ýºó¾ÍÄÜÓÃÁË£¬²»¹ý£¬ÔÓеÄÎı¾¸ñʽÉèÖö¼Ã»ÓÐÁË£¬Óеã¶ùµÃ²»³¥Ê§ÁË£¬ºÇºÇ¡£
ÎÒÔÚÏ룬ɾ³ýÀïµÄ¡°ReplaceFormat¡±ÊÇÓÃÀ´Ö¸¶¨¸ñʽµÄ°É£¬¿ÉÎÒ¿´ÁË¡°Replace¡±µÄ°ïÖú£¬Ã»ÓÐÕâ¸ö²ÎÊýÉèÖõġ£ÎÒÓõÄÊÇExecl2000£¬²»ÖªµÀÓÐûÓÐÁªÏµ¡£
ÓйØÊäÈëÊý×ÖµÄÌáʾ
¢Ù¡¢¿É×÷ΪÊý×ÖʹÓõÄ×Ö·û ÔÚ Microsoft Excel ÖУ¬Êý×ÖÖ»¿ÉÒÔΪÏÂÁÐ×Ö·û£º 0 1 2 3 4 5 6 7 8 9 + - ( ) , / $ % . E e
¢Ú¡¢Excel ½«ºöÂÔÊý×ÖÇ°ÃæµÄÕýºÅ(+)£¬²¢½«µ¥¸ö¾äµãÊÓ×÷СÊýµã¡£ËùÓÐÆäËüÊý×ÖÓë·ÇÊý×ÖµÄ×éºÏ¾ù×÷Îı¾´¦Àí¡£
¢Û¡¢ÊäÈë·ÖÊý Ϊ±ÜÃ⽫ÊäÈëµÄ·ÖÊýÊÓ×÷ÈÕÆÚ£¬ÇëÔÚ·ÖÊýǰ¼üÈë 0£¨Á㣩£¬Èç¼üÈë 0 1/2¡£ ¢Ü¡¢ÊäÈ븺Êý ÇëÔÚ¸ºÊýǰ¼üÈë¼õºÅ (-)£¬»ò½«ÆäÖÃÓÚÀ¨ºÅ( )ÖС£
¢Ý¡¢¶ÔÆëÊý×Ö ÔÚĬÈÏ״̬Ï£¬ËùÓÐÊý×ÖÔÚµ¥Ôª¸ñÖоùÓÒ¶ÔÆë¡£Èç¹ûÒª¸Ä±äÆä¶ÔÆë·½Ê½£¬Çëµ¥»÷¡°¸ñʽ¡±²Ëµ¥¡°µ¥Ôª¸ñ¡±ÃüÁÔÙµ¥»÷¡°¶ÔÆë¡±Ñ¡Ï£¬²¢´ÓÖÐÑ¡ÔñËùÐèµÄÑ¡Ïî¡£
20