excelº¯Êý¹«Ê½´óÈ«

ʱ¼ä£º2020.01.11 ·¢²¼ÈË£ºyc5058

excelº¯Êý¹«Ê½´óÈ«

Òѽâ¾öÎÊÌâ

¹È¸èyc5058Óû§ÔÚ2020.01.11Ìá½»Á˹ØÓÚ¡°Ã÷ÍõÊ׸¨excelº¯Êý¹«Ê½´óÈ«¡±µÄÌáÎÊ£¬»¶Ó­´ó¼ÒÓ¿Ô¾·¢±í×Ô¼ºµÄ¹Ûµã¡£Ä¿Ç°¹²ÓÐ1¸ö»Ø´ð£¬×îºó¸üÐÂÓÚ2024-09-18T21:40:38¡£Ï£Íû´ó¼ÒÄܹ»°ïÖúËý¡£

ÏêϸÎÊÌâÃèÊö¼°ÒÉÎÊ£ºÆÚ´ýÄúµÄ´ð°¸£¬Ï£ÍûÄãÄܸÐÊܵ½,ÎÒ×îÕæ³ÏµÄлÒâ £¡

Ï£ÍûÒÔϵĻشð£¬Äܹ»°ïÖúÄã¡£

µÚ1¸ö»Ø´ð

Óû§Ãû£º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)²ÉÓõÄÊǾø¶ÔÒýÓúÍÏà¶ÔÒýÓÃÏà½áºÏµÄ·½Ê½£¬Ä¿µÄÔÚÓÚ¶Ô²ÎÊý½øÐж¯Ì¬±ä»¯¡£½áºÏ¾ßÌåµÄÖµ±ãÓÚÀí½â¡£