æ¢ç¶æ¯å ³äºå½æ°çï¼é£ä¹æ¤å¤ä»¥IFå½æ°ãSUMIFå½æ°ãSUMIFSå½æ°ãCOUNTIFå½æ°ãvlookupå½æ°ä¸¾ä¾
2
主IFå½æ°ï¼IF(æ¡ä»¶å¤æ, ç»æ为çè¿åå¼, ç»æ为åè¿åå¼) IF( logical_test , [value_if_true] , [value_if_false])
ä¾IF(A2>29,"å¤æç","å¤æå") A2ï¼æ¥æ¾å¼ä½ç½® >29ï¼è¡¨ç¤ºå¤§äº29 "å¤æç"ï¼è¡¨ç¤ºæ¾ç¤ºå¤æ为ç
3
主SUMIFå½æ°ï¼ï¼æ¥æ¾èå´ï¼å¤ææ¡ä»¶ï¼æ±åèå´ï¼ sumif(range,criteria,[sum_range])
érangeå¿ é¡»é¡¹ï¼è¡¨ç¤ºæ¡ä»¶èå´ criteriaå¿ é¡»é¡¹ï¼è¡¨ç¤ºæ¡ä»¶ sum_rangeå¯é项ï¼è¡¨ç¤ºæ±åèå´
ä¾SUMIF(D2:D8,">=95") D2å°D8åºåå ï¼å¤§äºçäº95çæ°å¼ä¹å SUMIF(D2:D8,">=95",F2:F8) D2å°D8åå æ ¼ä¸æ°å¼è¶ è¿95对åºçF2å°F8çæ°å¼ä¹å
4
主SUMIFSå½æ°ï¼ï¼æ±åèå´ï¼æ¡ä»¶èå´ï¼æ¡ä»¶ï¼ï¼æ¡ä»¶èå´ï¼æ¡ä»¶ãããï¼ sumifs(sum_range,criteria_range1,criteria1,[riteria_range2,criteria2]...)
ésum_rangeæ¯æ们è¦æ±åçèå´ criteria_range1æ¯æ¡ä»¶çèå´ criteria1æ¯æ¡ä»¶ åé¢çæ¡ä»¶èå´åæ¡ä»¶å¯ä»¥å¢å ã
ä¾SUMIFS(Q36:Q52,P36:P52,"ç·") Q36å°Q52èå´ï¼æ¡ä»¶èå´P36å°P52ï¼æ¡ä»¶ç· SUMIFS(T36:T52,Q36:Q52,">=90",R36:R52,">=90") T36å°T52ï¼æ¡ä»¶èå´Q36å°Q52ï¼æ¡ä»¶1大äºçäº90ï¼æ¡ä»¶èå´2R36å°R52ï¼æ¡ä»¶2大äºçäº90
5
主COUNTIFå½æ°ï¼ï¼èå´ï¼æ¡ä»¶ï¼ countifï¼rangeï¼criteriaï¼
éå ¬å¼ï¼è®¡ç®æ个åºåä¸æ»¡è¶³ç»å®æ¡ä»¶çåå æ ¼æ°ç®
ä¾COUNTIF(P56:P72,">20000") èå´P56å°P72ï¼æ¡ä»¶å¤§äº20000æ8个
6
主vlookupå½æ°:VLOOKUP(æ¥æ¾å¼ï¼æ¥æ¾èå´ï¼æ¥æ¾åæ°ï¼ç²¾ç¡®å¹é æè è¿ä¼¼å¹é ï¼FALSE or trueï¼ï¼ VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
éfalseä½ä¸ºç²¾ç¡®å¹é ï¼å $ï¼ç»å¯¹å¼ç¨
ä¾vlookupï¼(H3,$A$3:$F$19,5,FALSE) H3ï¼æ¥æ¾å¼ä½ç½® A3ï¼F19ï¼æ¥æ¾èå´ 5ï¼æ¥æ¾æ°å FALSEï¼ç²¾ç¡®å¹é