Óû§Ãû£ºek93t
excel³£Óù«Ê½º¯ÊýÓУºIÀ´×ÔFº¯Êý¡¢SUMIFSº¯Êý¡¢COUNÎÊ´ðTIF¡¢VLOOKUPº¯Êý£¬LOOKUPº¯Êý¡£
1¡¢IFº¯Êý
Iºò½ïÒýµ×²»ËÉÐ̽ÐFº¯ÊýÒ»°ãÊÇÖ¸³ÌÐòÉèÌõ°ëÒ½°¡ÆƲ¹ÏìÐøÉù¼Æ»òExcelµÈÈí¼þÖеÄÌõ¼þº¯´ïÊý£¬¸ù¾ÝÖ¸¶¨µÄÌõ¼þÀ´ÅжÏÆä¡°Õ桱£¨TRUE£©¡¢¡°¼Ù¡±£¨FAL**£©£¬¸ù¾ÝÂß¼¶Ê±ÈºËЧ¼ÆËãµÄÕæ¼ÙÖµ£¬´Ó¶ø·µ»ØÏàÓ¦µÄÄÚÈÝ¡£¿ÉÒÔʹÓú¯ÊýIF¶ÔÊýÖµºÍ¹«Ê½½øÐÐÌõ¼þ¼ì²â¡£
Óï·¨
IF(logical_test,value_if_true,value_if_false)
¹¦ÄÜ
IFº¯ÊýÊÇÌõ¼þÅжϺ¯Êý£ºÈç¹ûÖ¸¶¨Ìõ¼þµÄ¼ÆËã½á¹ûΪTRUE£¬IFº¯Êý½«·µ»Øij¸öÖµ£»Èç¹û¸ÃÌõ¼þµÄ¼ÆËã½á¹ûΪFALËð´¨Âð**£¬Ôò·µ»ØÁíÒ»¸öÖµ¡£
ÀýÈçIF(²âÊÔÌõ¼þ,½á¹û½ñÓà±±¼ÈɽÍÁ×ÖÓ͵Ø̸1,½á¹û2)£¬¼´Èç¹ûÂú×ã¡°²âÊÔÌõ¼þ¡±ÔòÏÔʾ¡°½á¹û1¡±£¬Èç¹û²»Âú×ã¡°²âÊÔÌõ¼þ¡±ÔòÏÔʾ¡°½á¹û2¡±¡£
²ÎÊý
£¨1£©Logical_test±íʾ¼ÆËã½á¹ûΪTRUE»òFAL**µÄÈÎÒâÖµ»ò±í´ïʽË**¬ÒýÂð¶«ÊÒÕ÷ûÀî¡£
ÀýÈ磬A10=100¾ÍÊÇÒ»¸öÂß¼±í´ïʽ£¬Èç¹ûµ¥Ôªµç¸ñA10ÖеÄÖµµÈÓÚ100£¬±í´ïʽ¼´ÎªTRUE£¬·ñÔòΪFAL**¡£±¾²ÎÊý¿ÉʹÓÃм¼ÈκαȽÏÔËËã·û£¨=£¨µÈÓÚ£©Ê¦²®Âè¡¢>£¨´óÓÚ£©¡¢>=Á¢£¨´óÓÚµÈÓÚ£©¡¢<=£¨Ð¡ÓÚµÈÓÚµÈÔËËã·û£©£©¡£
£¨°ûÖÖÀ¼ÎÞͼת×豶2£©Value_if_true±íʾlogical_testΪTRUEʱ·µ»ØµÄÖµ¡£
ÀýÈ磬Èç¹û±¾²ÎÊýΪÎı¾×Ö·û´®ÃÞ´¦ËÍ·ÊÌøÕâ¹û½ï¸ß»®¡°Ô¤ËãÄÚ¡±¶øÇÒlogical_test²ÎÊýֵΪTRUE£¬ÔòIFº¯Êý½«ÏÔʾÎı¾¡°Ô¤ËãÄÚ¡±¡£Èç¹ûlogical_testΪTRUE¶øvalue_if_trueΪ¿Õ£¬Ôò±¾²ÎÊý·µ»Ø0¡£Èç¹ûÒªÏÔʾTRUE£¬ÔòÇëΪ±¾²ÎÊýʹÓÃÂß¼ÖµTRUE¡£vÍòÀÏÃÀ¸ÏÑÓËùÓ°¾²ÃܶÁalue_if_trueÒ²¿ÉÒÔÊÇÆäËû¹«Ê½¡£
£¨3£©Value_if_false±íʾlogical_testΪFAL**ʱ·µ»ØµÄÖµ¡£
ÀýÈ磬Èç¹û±¾²ÎÊýΪÎı¾×Ö·û´®¡°³¬³öÔ¤Ëãô¡±¶øÇÒlogical_test²ÎÊýֵΪFAL**£¬ÔòIFº¯Êý½«ÏÔʾÎı¾¡°³¬³öÔ¤Ë㡱¡£Èç¹ûlogical_testΪFAL**ÇÒºöÂÔÁËvÊý±»ÈÕÓ¢¸ºÑîalue_if_false£¨¼´value_if_trueºóûÓжººÅ£©£¬Ôò»á·µ»ØÂß¼ÖµFAL**¡£
Èç¹ûlogical_testΪFAL**ÇÒvalue_if_falseΪ¿Õ£¨¼´value_if_trueºóÓжººÅ£¬²¢½ô¸ú×ÅÓÒÀ¨ºÅ£©£¬Ôò±¾²ÎÊý·µ»Ø0£¨Á㣩¡£VALUE_if_falseÒ²¿ÉÒÔÊÇÆäËû¹«Ê½¡£
2¡¢SUMIFº¯Êý
SUMIFº¯ÊýÊÇExcel³£Óú¯Êý¡£Ê¹ÓÃSUMIFº¯Êý¿ÉÒÔ¶Ô±¨±í·¶Î§ÖзûºÏÖ¸¶¨Ìõ¼þµÄÖµÇóºÍ¡£ExcelÖÐsumifº¯ÊýµÄÓ÷¨ÊǸù¾ÝÖ¸¶¨Ìõ¼þ¶ÔÈô¸Éµ¥Ôª¸ñ¡¢ÇøÓò»òÒýÓÃÇóºÍ¡£
Óï·¨
SUMIF(range,criteria,sum_range)
1£©rangeΪÓÃÓÚÌõ¼þÅжϵĵ¥Ôª¸ñÇøÓò¡£
2£©criteriaΪȷ¶¨ÄÄЩµ¥Ôª¸ñ½«±»Ïà¼ÓÇóºÍµÄÌõ¼þ£¬ÆäÐÎʽ¿ÉÒÔΪÊý×Ö¡¢Îı¾¡¢±í´ïʽ»òµ¥Ôª¸ñÄÚÈÝ¡£ÀýÈ磬Ìõ¼þ¿ÉÒÔ±íʾΪ32¡¢"32"¡¢">32"¡¢"apples"»òA1¡£Ìõ¼þ»¹¿ÉÒÔʹÓÃͨÅä·û£ºÎʺÅ(?)ºÍÐǺÅ(*)£¬ÈçÐèÒªÇóºÍµÄÌõ¼þΪµÚ¶þ¸öÊý×ÖΪ2µÄ£¬¿É±íʾΪ"?2*",´Ó¶ø¼ò»¯¹«Ê½ÉèÖá£
3£©sum_rangeÊÇÐèÒªÇóºÍµÄʵ¼Êµ¥Ôª¸ñ¡£
3¡¢Countifº¯Êý
Countifº¯ÊýÊÇMicrosoftExcelÖжÔÖ¸¶¨ÇøÓòÖзûºÏÖ¸¶¨Ìõ¼þµÄµ¥Ôª¸ñ¼ÆÊýµÄÒ»¸öº¯Êý£¬ÔÚWPS£¬Excel2003ºÍExcel2007µÈ°æ±¾Öоù¿ÉʹÓá£
¸Ãº¯ÊýµÄÓï·¨¹æÔòÈçÏ£º
countif£¨range£¬criteria£©
²ÎÊý£ºrangeÒª¼ÆËãÆäÖзǿյ¥Ôª¸ñÊýÄ¿µÄÇøÓò
²ÎÊý£ºcriteriaÒÔÊý×Ö¡¢±í´ïʽ»òÎı¾ÐÎʽ¶¨ÒåµÄÌõ¼þ
4¡¢VLOOKUPº¯Êý
VLOOKUPº¯ÊýÊÇExcelÖеÄÒ»¸ö×ÝÏò²éÕÒº¯Êý£¬ËüÓëLOOKUPº¯ÊýºÍh**LOOKUPº¯ÊýÊôÓÚÒ»ÀຯÊý£¬ÔÚ¹¤×÷Öж¼Óй㷺ӦÓã¬ÀýÈç¿ÉÒÔÓÃÀ´ºË¶Ô**£¬¶à¸ö±í¸ñÖ®¼ä¿ìËÙµ¼Èë**µÈº¯Êý¹¦ÄÜ¡£¹¦ÄÜÊÇ°´ÁвéÕÒ£¬×îÖÕ·µ»Ø¸ÃÁÐËùÐè²éѯÐòÁÐËù¶ÔÓ¦µÄÖµ£»ÓëÖ®¶ÔÓ¦µÄh**LOOKUPÊÇ°´ÐвéÕҵġ£
²ÎÊý˵Ã÷
Lookup_valueΪÐèÒªÔÚ**±íµÚÒ»ÁÐÖнøÐвéÕÒµÄÊýÖµ¡£Lookup_value¿ÉÒÔΪÊýÖµ¡¢ÒýÓûòÎı¾×Ö·û´®¡£µ±vlookupº¯ÊýµÚÒ»²ÎÊýÊ¡ÂÔ²éÕÒֵʱ£¬±íʾÓÃ0²éÕÒ¡£
Table_arrayΪÐèÒªÔÚÆäÖвéÕÒ**µÄ**±í¡£Ê¹ÓöÔÇøÓò»òÇøÓòÃû³ÆµÄÒýÓá£
col_index_**mΪtable_arrayÖвéÕÒ**µÄ**ÁÐÐòºÅ¡£col_index_**mΪ1ʱ£¬·µ»Øtable_arrayµÚÒ»ÁеÄÊýÖµ£¬col_index_**mΪ2ʱ£¬·µ»Øtable_arrayµÚ¶þÁеÄÊýÖµ£¬ÒÔ´ËÀàÍÆ¡£Èç¹ûcol_index_**mСÓÚ1£¬º¯ÊýVLOOKUP·µ»Ø´íÎóÖµ#VALUE!£»Èç¹ûcol_index_**m´óÓÚtable_arrayµÄÁÐÊý£¬º¯ÊýVLOOKUP·µ»Ø´íÎóÖµ#REF!¡£
Range_lookupΪһÂß¼Öµ£¬Ö¸Ã÷º¯ÊýVLOOKUP²éÕÒʱÊǾ«È·Æ¥Å䣬»¹ÊǽüËÆÆ¥Åä¡£Èç¹ûΪFAL**»ò0£¬Ôò·µ»Ø¾«È·Æ¥Å䣬Èç¹ûÕÒ²»µ½£¬Ôò·µ»Ø´íÎóÖµ#N/A¡£
Èç¹ûrange_lookupΪTRUE»ò1£¬º¯ÊýVLOOKUP½«²éÕÒ½üËÆÆ¥ÅäÖµ£¬Ò²¾ÍÊÇ˵£¬Èç¹ûÕÒ²»µ½¾«È·Æ¥ÅäÖµ£¬Ôò·µ»ØСÓÚlookup_valueµÄ×î´óÊýÖµ¡£Èç¹ûrange_lookupÊ¡ÂÔ£¬ÔòĬÈÏΪ1¡£
5¡¢LOOKUPº¯Êý
LOOKUPº¯ÊýÊÇExcelÖеÄÒ»ÖÖÔËË㺯Êý£¬ÊµÖÊÊÇ·µ»ØÏòÁ¿»òÊý×éÖеÄÊýÖµ£¬ÒªÇóÊýÖµ±ØÐë°´ÉýÐòÅÅÐò¡£
ʹÓ÷½·¨
£¨1£©ÏòÁ¿ÐÎʽ£º¹«Ê½Îª=LOOKUP(lookup_value,lookup_vector,result_vector)
ʽÖÐlookup_value¡ªº¯ÊýLOOKUPÔÚµÚÒ»¸öÏòÁ¿ÖÐËùÒª²éÕÒµÄÊýÖµ£¬Ëü¿ÉÒÔΪÊý×Ö¡¢Îı¾¡¢Âß¼Öµ»ò°üº¬ÊýÖµµÄÃû³Æ»òÒýÓã»
lookup_vector¡ªÖ»°üº¬Ò»ÐлòÒ»ÁеÄÇøÓòlookup_vectorµÄÊýÖµ¿ÉÒÔΪÎı¾¡¢Êý×Ö»òÂß¼Öµ£»
result_vector¡ªÖ»°üº¬Ò»ÐлòÒ»ÁеÄÇøÓòÆä´óС±ØÐëÓëlookup_vectorÏàͬ¡£
£¨2£©Êý×éÐÎʽ£º¹«Ê½Îª
=LOOKUP(lookup_value,array)
ʽÖÐarray¡ª°üº¬Îı¾¡¢Êý×Ö»òÂß¼ÖµµÄµ¥Ôª¸ñÇøÓò»òÊý×éËüµÄÖµÓÃÓÚÓëlookup_value½øÐбȽϡ£
ÀýÈ磺LOOKUP(5.2,{4.2,5,7,9,10})=5¡£
×¢Ò⣺arrayºÍlookup_vectorµÄ**±ØÐë°´ÉýÐòÅÅÁУ¬·ñÔòº¯ÊýLOOKUP²»ÄÜ·µ»ØÕýÈ·µÄ½á¹û¡£Îı¾²»Çø·Ö´óСд¡£Èç¹ûº¯ÊýLOOKUPÕÒ²»µ½lookup_value£¬Ôò²éÕÒarrayºÍlookup_vectorÖÐСÓÚlookup_valueµÄ×î´óÊýÖµ¡£
Èç¹ûlookup_valueСÓÚarrayºÍlookup_vectorÖеÄ×îСֵ£¬º¯ÊýLOOKUP·µ»Ø´íÎóÖµ#N/A¡£ÁíÍ⻹ҪעÒ⣺º¯ÊýLOOKUPÔÚ²éÕÒ×Ö·û·½ÃæÊDz»Ö§³ÖͨÅä·ûµÄ£¬µ«¿ÉÒÔʹÓÃFINDº¯ÊýµÄÐÎʽÀ´´úÌæ¡£
À©Õ¹**£º
Excelº¯Êý¹«Ê½£º4¸ö±ØÐëÕÆÎÕµÄʵÓòéѯ»ã×ܼ¼ÇÉ
Ò»¡¢¶àÁвéÕÒ¡£
Ä¿µÄ£º²éѯ¶ÔÓ¦µÄ¶à¿Æ³É¼¨¡£
·½·¨£º
1¡¢ÔÚÄ¿±êµ¥Ôª¸ñÖÐÊäÈ빫ʽ£º=VLOOKUP($h**$3,$B$3:$F$9,COLUMN(B3),0)¡£
2¡¢ÔÚÄ¿±êµ¥Ôª¸ñÖÐÊäÈ빫ʽ£º=VLOOKUP($h**$3,$B$3:$F$9,MATCh**(I$2,$B$2:$E$2,0),0)¡£
½â¶Á£º
1¡¢Vlookupº¯ÊýµÄÓï·¨½á¹¹Ê½£º=Vlookup£¨²éѯֵ£¬²éѯ·¶Î§£¬²éѯֵÔÚ²éѯ·¶Î§ÖеÄÁÐÊý£¬Æ¥Åäģʽ£©¡£
2¡¢¹«Ê½=VLOOKUP($h**$3,$B$3:$F$9,COLUMN(B3),0)¡£ÓÃCOLUMN(B3)À´**µ±Ç°²éѯֵÔÚ²éѯ·¶Î§ÖеÄλÖã¬Æä²ÎÊýB3Ϊ¿É±äÖµ¡£
3¡¢¹«Ê½=VLOOKUP($h**$3,$B$3:$F$9,MATCh**(I$2,$B$2:$E$2,0),0)ÓÃMATCh**(I$2,$B$2:$E$2,0)À´**¿ÆÄ¿ÔÚ²éѯ·¶Î§ÖеÄÏà¶ÔλÖã¬Ó¦ÎªÆä³õʼֵ´Ó0¿ªÊ¼¼ÆË㣬¹Ê=MATCh**(I$2,$B$2:$E$2,0)µÄ·¶Î§´Ó$b$2¿ªÊ¼¼ÆËã¡£
¶þ¡¢°´Ö¸¶¨µÄÌõ¼þ»ã×Ü**¡£
Ä¿µÄ£º²éѯָ¶¨**Æ·µÄÏúÁ¿×ÜÊý»òij**Æ·ÔÚÖ¸¶¨Ô·ݵÄÏúÊ۶
·½·¨£º
1¡¢ÔÚÄ¿±êµ¥Ôª¸ñÊäÈ빫ʽ£º=SUMPRODUCT(($C$3:$C$9="A1")*D3:D9)¡£
2¡¢ÔÚÄ¿±êµ¥Ôª¸ñÖÐÊäÈ빫ʽ£º=SUMPRODUCT((($C$3:$C$9="A1")*(MONTh**($E$3:$E$9)=5))*D3:D9)¡£
½â¶Á£º
1¡¢SUMPROCUTº¯ÊýµÄ»ù±¾¹¦ÄÜÊÇ£º·µ»ØÊý×é¼ä¶ÔÓ¦ÔªËصij˻ýÖ®ºÍ¡£
2¡¢¹«Ê½£º=SUMPRODUCT(($C$3:$C$9="A1")*D3:D9)¾ÍÊÇÊý×é{1,0,1,0£¬1,0,1}ºÍ{90,98,12£¬45,98£¬67,100}¶ÔÓ¦³Ë»ýµÄºÍ¡£ôº1*90+0*98+1*12+0*45+1*98+0*67+1*100=300¡£
2¡¢=SUMPRODUCT((($C$3:$C$9="A1")*(MONTh**($E$3:$E$9)=5))*D3:D9)Ö»ÊǶàÁËÒ»¸öÊý×飬¶ÔÓ¦µÄÈý¸öÊýÏà³Ë²¢ÇóºÍ¡£
Èý¡¢¶àÌõ¼þÇóºÍ»ã×Ü¡£
Ä¿µÄ£ºÇó¡°Íõ¶«¡±¶Ô**Æ·¡°A1¡±µÄÏúÁ¿¡£
·½·¨£º
1¡¢ÔÚÄ¿±êµ¥Ôª¸ñÖÐÊäÈ빫ʽ£º=SUMIFS(D3:D9,B3:B9,"Íõ¶«",C3:C9,"A1")¡£
2¡¢ÔÚÄ¿±êµ¥Ôª¸ñÖÐÊäÈ빫ʽ£º=SUMIFS(D3:D9,B3:B9,"Íõ¶«",C3:C9,"A1",D3:D9,">50")¡£
½â¶Á£º
1¡¢SUMIFSº¯ÊýÊǶàÌõ¼þÇóºÍº¯Êý¡£ÆäÓï·¨½á¹¹Îª£º=SUMIFS(ÇóºÍ·¶Î§£¬Ìõ¼þ·¶Î§1£¬Ìõ¼þ1£¬Ìõ¼þ·¶Î§2£¬Ìõ¼þ2¡¡Ìõ¼þ·¶Î§N£¬Ìõ¼þN£©¡£
ËÄ¡¢¸ôÁзÖÀà»ã×Ü¡£
Ä¿µÄ£º¶Ô¡°¼Æ»®¡±ºÍ¡°Êµ¼Ê¡±½øÐлã×Ü¡£
·½·¨£º
ÔÚÄ¿±êµ¥Ôª¸ñÊäÈ빫ʽ£º=SUMIF($C$3:$F$10,h**$3,$**:$F4)¡£
½â¶Á£º
1¡¢º¯ÊýSUMIFÊǵ¥Ìõ¼þÇóºÍº¯Êý£¬ÆäÓï·¨½á¹¹Îª=SUMIF(ÇóºÍ·¶Î§£¬Ìõ¼þ·¶Î§£¬Ìõ¼þ£©¡£
2¡¢¹«Ê½£º=SUMIF($C$3:$F$10,h**$3,$**:$F4)²ÉÓõÄÊǾø¶ÔÒýÓúÍÏà¶ÔÒýÓÃÏà½áºÏµÄ·½Ê½£¬Ä¿µÄÔÚÓÚ¶Ô²ÎÊý½øÐж¯Ì¬±ä»¯¡£½áºÏ¾ßÌåµÄÖµ±ãÓÚÀí½â¡£