1.¹¤³ÌÁ¿¼ÆË㹫ʽ¿ì½ÝµØµÃ³ö¼ÆËã½á¹û
ÔںܶàÇé¿öÏ£¬Ôì¼ÛÈËÔ±ÔÚ¼ÆË㹤³ÌÁ¿Ê±£¬ÐèÒªÁгö¼°±£Áô¹¤³ÌÁ¿µÄ¼ÆË㹫ʽºÍ¼ÆË㱸ע£¬ÒÔ·½±ãºóÆڵĶÔÕË¡£ÈçºÎÔÚÊäÈë¼ÆËãʽºÍ¼ÆË㱸עºó£¬¾ÍÄܷܺ½±ãµØµÃ³ö¹¤³ÌÁ¿¼ÆËã½á¹ûÄØ£¿ÊµÓð¸ÀýÁÐÊöÈçÏ£º
Ê×ÏÈ£¬ÐèÑ¡ÖÐÏÔʾ¼ÆËã½á¹ûµÄµ¥Ôª¸ñE2(¿ÉÒÔÀí½âΪ¶¨Î»×÷ÓÃ)£¬È»ºóÔÙ°´ÒÔ϶¯Ì¬Í¼ÑÝʾ£º
ÔÚ´Ë£¬½âÊÍÒ»ÏÂÕâ¸ö¹«Ê½“=EVALUATE(SUBSTITUTE(SUBSTITUTE(Sheet3!D2,"[","*ISTEXT(""["),"]","]"")"))”µÄ×¢Òâµã£º³ý¹«Ê½ÖеĻÆÉ«±êʶ“Sheet3!D2”µÄ²ÎÊýÐèÒª×ÔÐÐÐÞ¸ÄÍ⣬ÆäËû¸´ÖƼ´¿É¡£ÆäÖÐSheet3ÊDZêÇ©Ãû£¬D2ÊǼÆËãÊéËùÔÚµ¥Ôª¸ñλÖá£
ÇмǣºÉÏÊö¼ÆËãÊ鱸עÐëÓÃÓ¢ÎĸñʽÖÐÀ¨ºÅ"[""]"¡£Ê¹Óô˺¯ÊýµÄExcelµÄÎļþÐèÒÔ£¨*.XLSM£©¸ñʽ½øÐб£´æ£¬·ñÔòÏ´ÎÖØдò¿ª±í¸ñ£¬¸Ãº¯ÊýÎÞ·¨ÊµÏÖ£¨¼°ÐèÖØж¨Òå¸Ãº¯Êý£©¡£
2.Datedifº¯Êý¼ÆË㹤ÆÚ¼°×âÁÞÌìÊý
ÔÚ´ó¼ÒÔÚ¼ÆË㹤³Ì¹¤ÆÚ»òÖÜתÐÔ²ÄÁÏ×âÁÞÆÚʱ£¬ÊÇ·ñΪÿ¸öÔµÄ31ÈÕ»ò30ÈÕËùÀ§ÈÅ£¿¿ÉÄÜ»áÓðâÊÖÖ¸»ò·ÈÕÀúµÄ±¿·½·¨¼ÆËãÁ½¸öÈÕÆÚµÄÏà¸ôÌìÊý£¿ÆäʵDatedifº¯Êý¾Í¿ÉÒÔ°ïÄãºÜÇáËɽâ¾ö¡£ÊµÓð¸ÀýÈçÏ£º
±ÊÕßÏȼòµ¥½âÊÍÏÂÒÔϱí¸ñÖеĵ¥Ôª¸ñD2ÊäÈëµÄ¹«Ê½“=DATEDIF£¨B2£¬C2£¬"d"£©“Ç°Á½¸ö²ÎÊý·Ö±ðÊÇ¿ªÊ¼ÈÕÆںͽáÊøÈÕÆÚ£¬µÚÈý¸ö²ÎÊý"d"ÊǼÆËãÌìÊýµÄ²ÎÊý£¬Ò²¿ÉÒԸijÉ"m"(¼ÆËãÔ·Ý)ºÍ"y"(¼ÆËãÄê·Ý)¡£
3.Exactº¯ÊýÔÚ¿ìËÙ²éÕÒÐÞ¸ÄÇ°ºó±í¸ñµÄÄÚÈݲîÒì
ÔÚƽʱµÄÔì¼Û¹¤×÷ÖУ¬´ó¼ÒÒ»¶¨»áÅöµ½ÕâÑùÒ»¸öÎÊÌ⣬ÔÚÄúÒѾ±àÖÆÁ˹¤³ÌÁ¿Çåµ¥µÄ¸ñʽ£¨°üÀ¨Çåµ¥ÃèÊö¡¢µ¥Î»µÈ£©£¬ÐèÒªÆäËûËãÁ¿Í¬ÊÂÌîд¹¤³ÌÁ¿¡£µ«ËûÃÇÍê³ÉÌîд¹¤³ÌÁ¿µÄ±í¸ñÊÇ·ñ»áµ÷Õû»ò²»Ð¡ÐÄÐ޸ĹýÔ±í¸ñÄÚÈÝ£¨ÈçÇåµ¥ÃèÊö»òµ¥Î»µÈ£©£¬ÄãÒªÊÇÒÔÒ»Ò»ºË¶Ô»ò¸´ºËһϣ¬ÄǼòֱ̫·Ñʱ¼äÁË¡£ÆäʵExactº¯Êý»ðÑ۽𾦣¬ºÜ¿ìʶ±ð³öÆäÖеIJîÒì¡£±ÈÈçϱíÖеĵÚ2¡¢3ÐеÄB¡¢CÁеÄÄÚÈÝÒÑÓвîÒìÁË£¬Çë¿´¶¯Ì¬ÑÝʾ£º
4.Vlookupº¯ÊýʵÏÖÇåµ¥Ì×¼ÛÒ»²½µ½Î»
Vlookupº¯ÊýÊÇÒ»¸ö¹¦ÄÜÊ®·ÖÇ¿´óµÄº¯Êý£¬ËûÄÜ°ïÖú´ó¼Ò´ÓÒ»´ó¶Ñ´í×Û¸´ÔÓµÄÊý¾ÝÖвéѯ²¢ÌáÈ¡ÄãËùÐèÒªµÄÊý¾Ý¡£ÏÂÃæ¾ÙÒ»¸öÀý×Ó£¬·½±ã´ó¼ÒÈÏʶ²¢Á˽âËû¡£
ÀýÈ磺ʩ¹¤µ¥Î»ÔÚ½øÐÐͶ±ê±¨¼Ûʱ£¬Óкܶ൥Ì幤³Ì·Ö²»Í¬µÄÇåµ¥±í¸ñ½øÐб¨¼Û£¬¶øÆäʵ´ó²¿·ÖµÄÇåµ¥ÏîÄ¿ÊÇÏàͬµÄ£¬ÔÚÍê³ÉµÚÒ»¸öµ¥Ì幤³ÌµÄͶ±ê±¨¼Ûºó£¬ÔÙÔËÓô˺¯ÊýÔÚÆäËûµ¥ÌåÇåµ¥ÖУ¬¿É´ïµ½ÁËÊ°빦±¶¡£ÓÖÈçij¸ö±ä¸üÇ©Ö¤Òª²ÎÕÕ¹¤³ÌºÏͬ£¨¹¤³ÌÁ¿Ç嵥Ϊ¼Æ¼ÛºÏͬ£©µÄÏàÓ¦Çåµ¥ÏîÄ¿½øÐÐÌ×¼Û£º
ÔÚµ¥Ôª¸ñE15ÖÐÊäÈ빫ʽ“=VLOOKUP(B15,$B$3:$F$11,4,FALSE)”
Õâ¸ö¹«Ê½Óм¸¸öÒªµãÐèÒª¸ú´ó¼Ò½âÊÍһϣ¬ÊÇÏÂÃ涯̬ÑÝʾ²»ÄÜÍêÈ«·´Ó³µÄ£¬Ò²ÊÇ°ïÖú´ó¼ÒÀí½âÕâ¸öº¯Êý£¬ËùÒÔÇë´ó¼ÒÎñ±Ø×ÐϸÔĶÁ¡£
VLOOKUPº¯ÊýÓÐËĸö²ÎÊý£º
µÚ1¸ö²ÎÊýµÄ½âÊÍÊÇËùÐè²éѯµÄÏîÄ¿Ãû³Æ¡£
µÚ2¸ö²ÎÊýµÄ½âÊÍÊDzéѯµÄ·¶Î§£¬ÐèҪעÒâµÄÊÇÑ¡È¡·¶Î§µÄÊ×ÁбØÐëΪµÚÒ»¸ö²ÎÊýËù¶ÔÓ¦µÄÏîÄ¿Ãû³Æ£¬»¹ÓÐÔÚÑ¡È¡·¶Î§µÄʱºòÐèÒªÓõ½“$”·ûºÅ£¬¸Ã·ûºÅÔÚExcelÖÐÊÇËø¶¨µ¥Ôª¸ñÐлòÁеŦÄÜ¡£Õâô×öÊÇΪÁË·½±ãºóÆÚÅúÁ¿¸´ÖƵ¥Ôª¸ñµÄ¹«Ê½¡£
µÚ3¸ö²ÎÊýµÄ½âÊÍÊDzéѯÊý¾ÝÔÚÑ¡È¡·¶Î§µÄµÚ¼¸ÁС£
µÚ4¸ö²ÎÊýµÄ½âÊÍÊÇÑ¡ÔñÄ£ºý²éÕÒ»ò¾«È·²éÕÒ¡£
Çë¿´¶¯Ì¬ÑÝʾ£º
5.Sumifº¯Êý¼òµ¥¹ÔÇÉÍê³É¹¤³ÌÁ¿Ö¸±ê»ã×Ü
´ó¼ÒÔÚͳ¼Æ¹¤³ÌÁ¿µÄ¸÷Ïî¼¼ÊõÖ¸±êʱ£¬ÍùÍùÏÈÐèÒª»ã×ܸ÷Ï³ÌÁ¿£¨Èç»ìÄýÍÁµÄ×ܺͣ©£¬¸ÃÏ×÷ÌرðÂé·³£¬»¹ÈÝÒ׳ö´í£¬ÓÐʲô·½·¨¿ÉÒÔÒ»²½µ½Î»ÄØ£¿³£Óú¯ÊýSumif¿ÉÒÔ°ï´ó¼Ò½â¾öÕâ¸öÎÊÌâ¡£°¸ÀýÈçÏ£º
Çë×¢Ò⣬ÐèÒª½«¸÷Ï³ÌÁ¿½øÐзÖÀà(Èç11¡¢12……£©¡£±ÊÕßÔÙ½âÊÍϵ¥Ôª¸ñD14µÄ¹«Ê½“=SUMIF($C$2:$C$12,C14,$D$2:$D$12)”£ºµÚÒ»¸ö²ÎÊý“$C$2:$C$12”ºÍµÚÈý¸ö²ÎÊý”$D$2:$D$12“±ØÐë±£³Öµ¥Ôª¸ñÐÐÊý¶ÔÓ¦¡£±ÈÈç±¾°¸ÀýÖÐ"·ÖÀà"ÁеÄÆðʼÐÐÊýΪ2£¬"¹¤³ÌÁ¿"ÁеÄÆðʼÐÐÊýÒ²±ØÐëΪ2¡£Í¬Ñù£¬"·ÖÀà"ÁеĽáÊøÐÐÊýΪ12£¬"¹¤³ÌÁ¿"ÁеĽáÊøÐÐÊýÒ²±ØÐëΪ12¡£ÁíÍ⣬ÕâÁ½¸ö²ÎÊý±ØÐëÓÔ$”Ëø¶¨µ¥Ôª¸ñ£¬Õâô×öÊÇΪÁË·½±ãºóÆÚÅúÁ¿¸´ÖƵ¥Ôª¸ñµÄʱºò·ÖÀà»ã×ÜÊý¾Ý²»»áËæÕ³Ìùµ¥Ôª¸ñλÖõIJ»Í¬¶ø±ä»¯¡£
ÏÂÃæÇë¿´¶¯Ì¬ÑÝʾ£º
6.Sumproductº¯ÊýÒ»¼ü»ã×ܶ໧Ð͹¤³ÌÁ¿
´ó¼ÒÔÚ»ã×ܶ໧Ð͹¤³ÌÁ¿µÄʱºòÊÇ·ñ»¹ÔÚÓù«Ê½“=A»§Ð͵¥»§¹¤³ÌÁ¿*A»§ÐÍ»§Êý+ B»§Ð͵¥»§¹¤³ÌÁ¿*B»§ÐÍ»§Êý+ C»§Ð͵¥»§¹¤³ÌÁ¿*C»§ÐÍ»§Êý……”£¬Ò»ÏµÁеIJÙ×÷ÏÂÀ´¹À¼Æ»áʹ´ó¼ÒÑÛ»¨çÔÂÒÁË£¬ÆäʵÓÐÒ»¸ö½Ý¾¶£¬ÓÃSumproductº¯Êý¾Í¿ÉÒÔ¼òµ¥¡¢¸ßЧµØʵÏÖÕâÒ»¹¦ÄÜÁË¡£ÊµÓð¸ÀýµÄ¶¯Ì¬ÑÝʾ£º
½âÊÍÒ»ÏÂÉÏÃæµÄ¹«Ê½“=SUMPRODUCT(C3:E3,F3:H3)”£¬´ó¼Ò¿ÉÒÔÀí½âΪÁ½¸öÊý×é(»§ÐÍ×éºÍÿ»§¹¤³ÌÁ¿×é)µÄ³Ë»ýÇóºÍ¹«Ê½¡£
7.Ìõ¼þ¸ñʽ¿ìËÙ±ê×¢¸÷Ï¼Û×î¸ß(µÍ)¼Û
¶ÔÓÚÒµÖ÷»òÔì¼Û×ÉѯÈËÔ±ÔÚ¶ÔͶ±êÎļþ½øÐлرê·ÖÎöµÄʱºòÒ»¶¨ÊÇʱ¼ä½ô¡¢ÈÎÎñÖØ£¬ÈçºÎ²ÅÄÜ¿ìËٵؽ«¸÷¼Òµ¥Î»Í¶±êµ¥¼ÛÖеÄ×î¸ß¼ÛºÍ×îµÍ¼ÛÒÔ²»Í¬ÑÕÉ«Í»³öÏÔʾ£¬ÒÔ·½±ãÏÂÒ»²½È¥Åжϸ÷Ͷ±êµ¥Î»µÄͶ±ê±¨¼Û¸ßµÍ¡£Ìõ¼þ¸ñʽ¾Í¿ÉÒÔ°ïÎÒÃÇÕâ¸öæ¡£ÏÂÃæÇë¿´°¸Àý£º
µÚ1²½£¬ÐèҪעÒâµÄÊÇÇë´ó¼ÒÏÈ°´Í¼ÖкìÏß·¶Î§Ñ¡Öе¥Ôª¸ñE2£ºG2£¬ÕâºÜÖØÒª£¡
µÚ2²½£¬ÔÚÌõ¼þ¸ñʽÖÐѡȡн¨¹æÔò¡£
µÚ3²½£¬Ñ¡Ôñ“ʹÓù«Ê½È·¶¨ÒªÉèÖøñʽµÄµ¥Ôª¸ñ”¡£
µÚ4²½£¬ÊäÈ빫ʽ£¬±ÊÕß½«Á½¸ö¶¯Ì¬ÑÝʾµÄ¹«Ê½ÌáÈ¡³öÀ´·½±ã´ó¼Ò¸´ÖÆ“=E2=MAX($E2:$G2)”ºÍ“ =E2=MIN($E2:$G2)”ÐèҪעÒâµÄÊÇ£¬”$”·ûºÅ¾ø²»ÄÜÊ¡ÂÔ¡£
µÚ5²½£¬×îºóÅúÁ¿Éú²úµÄ¹ý³ÌÖУ¬Ö»Ðèµã»÷¸ñʽˢ£¬ÔÙÑ¡È¡ÐèÒª¶Ô±ÈµÄ·¶Î§£¬¾Í´ó¹¦¸æ³ÉÁË¡£
ÏÂÃæÇë¿´¶¯Ì¬ÑÝʾ£º
8.Averageº¯ÊýÔÚ¹¤³ÌÔì¼ÛÖеÄʵ¼ÊÓ¦ÓÃ
¶ÔÓÚƽ¾ùÊýº¯ÊýAverage´ó¼ÒÏë±Ø²»»áÄ°Éú£¬µ«Êǹ¤³ÌÔì¼ÛÖÐijЩÌØÊâÇé¿öÐèÒª½«Õâ¸öº¯Êý×÷Ò»¸öССµØ¸Ä¶¯ÒÔ»ñµÃÎÒÃÇËùÐèÊý¾Ý¡£ÏÂÃæÇë¿´¶¯Ì¬ÑÝʾ£º
Á½¸öº¯ÊýµÄÇø±ðÔÚÓÚµÚÒ»¸öº¯Êýͳ¼Æ·¶Î§°üº¬ÊýֵΪ0µÄµ¥Ôª¸ñ£¬µÚ¶þ¸öº¯Êýͳ¼Æ·¶Î§ºöÂÔÊýֵΪ0µÄµ¥Ôª¸ñ¡£
µÚ¶þ¸öº¯Êý¹«Ê½”=AVERAGE(IF(B2:B13<>0,B2:B13))”¡£
Ìرð×¢Ò⣺ÊäÈëÍêÕâ¸ö¹«Ê½ÒÔºó°´Ctrl+Shift+Enter×éºÏ¼ü²ÅÄܵõ½ÕýÈ·½á¹û¡£
9.µ¥Ôª¸ñÄÚ»»ÐÐ
ÔÚwordÖкܺýâ¾öÖ»Òª°´enter¼ü¾Í¿ÉÒÔÁË£¬µ«Êǵ½ÁËexcelÖа´ÏÂenter¼ü¾ÍÌøµ½ÏÂÒ»¸öµ¥Ôª¸ñÁË¡£ÄÇôÎÊÌâÀ´ÁËÈçºÎ±ä³ÉÏñwordÄÇÑùÊäÈëÄØ£¿±ðÔÙ°´¿Õ¸ñÀ²£¡£¡ÆäʵºÜ¼òµ¥Ö»ÒªÊäÈëÍêÒ»¸ö×ö·¨ºó°´×éºÏ¼üAlt+enter¾Í¿ÉÒÔÔÚ±¾µ¥Ôª¸ñÖл»ÐС£ÎÄ×Ö±à¼Ê±£¬µã»÷×ó²àÄ£°å/ͼƬ£¬¾Í¿ÉÒÔ²åÈëµ½ÎÄ×ÖÖм䡣
10.°´F4Öظ´ÉÏÒ»¹¤×÷
ÔÚExcel²Ù×÷ÖÐÊÇ·ñÓÐÐèÒª´ó¼ÒÒ»¸ö¸öµ¥Ôª¸ñÔĶÁ²¢ÇÒ×öµ¥Ò»Ð޸ĵŤ×÷£¬È磺Çå³ýÄÚÈÝ»òÕß±ê×¢ÑÕÉ«µÈ¡£±ÊÕ߸æËß´ó¼Ò¿ÉÒÔÓù¦ÄܼüF4¾Í¿ÉÒÔʵÏÖ¡£
11.¶³½á´°¿ÚµÄÃîÓÃ
Excel±í¸ñ¿íÁË»òÕß³¤ÁËÕÒÊý¾ÝÈÝÒ×´íÐУ¬´ó¼ÒÊÇ·ñÓÐÓùý¶³½á´°¿ÚÄØ£¿Çë¿´²Ù×÷ÑÝʾ£º
12.±í¸ñתÖõÄÃîÓÃ
ÔÚÔì¼Û¹¤×÷ÖÐÓÐЩʱºòΪÁË·½±ã¼ÆËãºÍ¹«Ê½Á´½Ó£¬ÐèÒª½«±í¸ñµÄÐÐÓëÁÐÏ໥תÖá£ÏÂÃæÇë¿´¶¯Ì¬ÑÝʾ£º
ÓÐÈκιØÈκÎÎÊÌâºÍ½¨Ò飬¾´Çë×Éѯ£¡¹Ø×¢ÎÒµÄ΢ÐŹ«Öںż°Ê±»ñÈ¡¸ü¶àÐÅÏ¢¡£¹«ÖںţºÔì¼Û»ï°é£¨zaojiahuoban£©¹Øע΢ÐŹ«ÖÚºÅÓëÎÒ΢ÐÅ»¥¶¯
(ÔðÈα༣º½¨ÖþС°×) |