Ⅰ excel鎬庝箞璺ㄨ〃奼傚拰璺ㄨ〃鏍兼暟鎹奼傚拰鍑犵嶆柟娉
鍦 Excel 涓錛屽彲浠ラ氳繃浣跨敤 SUMIF 鍜 SUMIFS 鍑芥暟鏉ヨ法琛ㄦ眰鍜屻備互涓嬫槸鍏蜂綋鐨勬ラわ細
1. 鎵撳紑瑕佽繘琛岃法琛ㄦ眰鍜岀殑宸ヤ綔綈匡紝騫跺畾浣嶅埌闇瑕佽繘琛屾眰鍜屾搷浣滅殑宸ヤ綔琛ㄣ
2. 鍦ㄩ渶瑕佽繘琛屾眰鍜屾搷浣滅殑鍗曞厓鏍間腑杈撳叆 SUMIF 鎴栬 SUMIFS 鍑芥暟銆
3. 瀵逛簬 SUMIF 鍑芥暟錛岃娉曞備笅錛歋UMIF(range, criteria, sum_range)銆傚叾涓錛宺ange 鏄鎮ㄦ兂瑕佸湪鍏朵腑鎼滅儲鏉′歡鐨勫尯鍩燂紝criteria 鏄鎮ㄥ笇鏈涚敤浜庢悳緔㈢殑鏉′歡錛宻um_range 鏄鎮ㄨ佽繘琛屾眰鍜岀殑鍖哄煙銆
4. 瀵逛簬 SUMIFS 鍑芥暟錛岃娉曞備笅錛歋UMIFS(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2]...)銆傚叾涓錛宻um_range 鏄鎮ㄨ佽繘琛屾眰鍜岀殑鍖哄煙錛宑riteria_range1 鏄鎮ㄦ兂瑕佸湪鍏朵腑鎼滅儲絎涓涓鏉′歡鐨勫尯鍩燂紝criteria1 鏄鎮ㄥ笇鏈涚敤浜庢悳緔㈢殑絎涓涓鏉′歡錛孾criteria_range2] 鍜 [criteria2] 鏄鍙閫夊弬鏁幫紝鐢ㄤ簬娣誨姞鏇村氱殑鏉′歡銆
5. 杈撳叆瀹屽嚱鏁板悗錛屽湪鎷鍙峰唴杈撳叆鐩稿簲鐨勫弬鏁般備緥濡傦紝濡傛灉鎮ㄨ佽法琛ㄦ眰鍜 A 鍒楃殑鏁板礆紝鑰屾悳緔㈡潯浠朵負 B 鍒楃殑鏁板礆紝閭ｄ箞 SUMIF 鍑芥暟搴旇ュ啓鎴 SUMIF(琛ㄦ牸2!B:B, A1, 琛ㄦ牸2!A:A)銆
6. 鎸変笅鍥炶濺閿鎵ц屽嚱鏁幫紝鍗沖彲鍦ㄥ綋鍓嶅崟鍏冩牸涓鐪嬪埌璺ㄨ〃奼傚拰鐨勭粨鏋溿
闇瑕佹敞鎰忕殑鏄錛屽湪璺ㄨ〃奼傚拰鏃訛紝搴旇ョ『淇濅袱涓琛ㄦ牸鍏鋒湁鐩稿悓鐨勫垪鍚嶆垨鑰呭垪緔㈠紩錛岃繖鏍鋒墠鑳芥ｇ『鍦拌繘琛屾眰鍜屾搷浣溿
鍥炵瓟濡備笅錛氫嬌鐢⊿UMIF鍑芥暟鏉ヨ法琛ㄦ眰鍜屻
1.閫変腑涓涓絀哄崟鍏冩牸錛岃緭鍏=SUMIF(
2.鍦ㄦ嫭鍙蜂腑杈撳叆璺ㄨ〃奼傚拰鐨勮寖鍥達紝渚嬪傦細'Sheet2'!A2:A10
3.杈撳叆閫楀彿錛屽啀杈撳叆鏉′歡錛屼緥濡傦細">100"
4.鍐嶆¤緭鍏ラ楀彿錛岃緭鍏ヨ佹眰鍜岀殑鑼冨洿錛屼緥濡傦細'Sheet2'!B2:B10
5.鏈鍚庡湪鎷鍙蜂腑鍔犱笂")"錛屾寜鍥炶濺閿鍗沖彲寰楀嚭璺ㄨ〃奼傚拰鐨勭粨鏋溿
渚嬪傦紝瑕佸湪Sheet1涓奼傚拰Sheet2涓鏁板煎ぇ浜100鐨凚鍒楁暟鎹錛屽垯鍙浠ヤ嬌鐢ㄤ互涓嬪叕寮忥細
=SUMIF('Sheet2'!A2:A10,">100",'Sheet2'!B2:B10)