鍝庯紝浣犺瘯杩囩敤Excel绠楀競鍦洪儴鍗庡寳鍖?0鍚庡憳宸ョ殑骞冲潎缁╂晥鍚楋紵杩欏彲涓嶆槸绠€鍗曟媺涓钩鍧囧€煎氨鑳芥悶瀹氱殑锛佷粖澶╁氨璺熶綘鍞犲敔杩欎釜璁╂棤鏁颁汉鎶撶媯鐨勫鏉′欢姹傚钩鍧囷紝淇濆噯鐪嬪畬浣犱細鎷嶅ぇ鑵匡細"鍘熸潵杩欎箞绠€鍗曪紵锛?

馃殌涓夌鍏ラ棬AVERAGEIFS鍏紡

璁颁綇杩欎釜涓囪兘缁撴瀯锛氣€?strong>鈥?AVERAGEIFS(瑕佺畻骞冲潎鐨勫垪,鏉′欢鍒?,鏉′欢1,鏉′欢鍒?,鏉′欢2,...)鈥?/strong>鈥?br/> 涓句釜鏍楀瓙锛氭兂绠楅攢鍞儴鍗庝笢鍖篏MV瓒呰繃10涓囩殑骞冲潎鍒╂鼎锛?/p>

1. 骞冲潎鍒楅€夊埄娑︽爮锛堟瘮濡侱鍒楋級
2. 閮ㄩ棬鍒楅€堿鍒楋紝鏉′欢鏄?閿€鍞儴"
3. 鍖哄煙鍒楅€塀鍒楋紝鏉′欢鏄?鍗庝笢"
4. 涓氱哗鍒楅€塁鍒楋紝鏉′欢鏄?>100000"
   鏈€缁堝叕寮忛暱杩欐牱锛氣€?strong>鈥?AVERAGEIFS(D:D,A:A,"閿€鍞儴",B:B,"鍗庝笢",C:C,">100000")鈥?/strong>鈥?/li>

馃挕閲嶇偣鏉ヤ簡锛佹墍鏈夋潯浠跺垪蹇呴』鍜屽钩鍧囧垪琛屾暟涓€鑷达紝鍚﹀垯绛夌潃鐪?N/A閿欒鍚э紒

馃尓锔忓姩鎬佽寖鍥撮粦绉戞妧

杩樺湪鎵嬪姩鏀规暟鎹寖鍥达紵璇曡瘯杩欐嫑锛氬厛鐢ㄢ€?strong>鈥婥trl+T鈥?/strong>鈥嬫妸鏁版嵁杞垚鏅鸿兘琛ㄦ牸锛岃繖鏃跺€欏叕寮忎細鑷姩鍙樻垚缁撴瀯鍖栧紩鐢ㄣ€傛瘮濡傚師鏉ョ殑鈥?strong>鈥?AVERAGEIFS(D:D,A:A,"鍗庝笢")鈥?/strong>鈥嬩細鍙樻垚鈥?strong>鈥?AVERAGEIFS(琛?[鍒╂鼎],琛?[鍖哄煙],"鍗庝笢")鈥?/strong>鈥?br/> 濂藉鏈変笁锛?/p>

1. 鏂板鏁版嵁鑷姩璁″叆璁＄畻
2. 鍒楀悕浠ｆ浛瀛楁瘝鏇寸洿瑙?/li>
3. 璺ㄨ〃寮曠敤涓嶆€曞垪椤哄簭鍙樺姩

鈿狅笍娉ㄦ剰锛佽繖鎷涙湁涓壇浣滅敤鈥斺€旇窡VLOOKUP鍑芥暟娣风敤鏃跺鏄撴姤閿欙紝寤鸿鏂版墜鍏堢敤浼犵粺鍖哄煙寮曠敤缁冩墜銆?/p>

馃敟鏁扮粍鍏紡鏍告鍣?/h3>

閬囧埌瑕佺畻"鍗庡寳鎴栧崕涓滃尯鍩燂紝涓旈攢鍞鍓?0%鐨勫钩鍧囧€?杩欑鍙樻€侀渶姹傦紵涓娾€?strong>鈥?AVERAGE(IF((B:B="鍗庡寳")+(B:B="鍗庝笢"))*(C:C>PERCENTILE(C:C,0.5)),D:D))鈥?/strong>鈥?br/> 杈撳叆鍚庤寰楁寜鈥?strong>鈥婥trl+Shift+Enter鈥?/strong>鈥嬩笁閿縺娲绘暟缁勫叕寮忥紒杩欐椂鍊欏叕寮忓墠鍚庝細鑷姩鍑虹幇澶ф嫭鍙穥}

鏂规硶瀵规瘮	浼樺娍	鑷村懡浼?/th>
AVERAGEIFS	鏉′欢鏁伴噺涓嶉檺	涓嶆敮鎸佸鏉傞€昏緫鍒ゆ柇
鏅鸿兘琛ㄦ牸	鑷姩鎵╁睍鏁版嵁鑼冨洿	淇敼鍒楀悕浼氭姤閿?/td>
鏁扮粍鍏紡	鑳藉鐞嗗鍚堥€昏緫	璁＄畻閫熷害鎱?/td>

---

璇翠釜鐪熷疄妗堜緥锛氬幓骞村弻鍗佷竴澶т績锛岃繍钀ラ儴瑕佺畻"鎵嬫満鍝佺被銆佽瘎浠?鏄熶互涓娿€侀€€璐х巼浣庝簬5%"鐨勫晢鍝佸钩鍧囨姌鎵ｇ巼銆傚皬濮戝鐢ˋVERAGEIFS濂椾簡涓変釜鏉′欢锛岀粨鏋滄紡浜嗛€€璐х巼瑕佸皬浜庣瓑浜?%鐨勭瓑鍙凤紝瀵艰嚧200澶氫釜SKU鏁版嵁鍏ㄩ敊銆傚悗鏉ユ敼鎴愨€?strong>鈥?AVERAGEIFS(D:D,C:C,"鎵嬫満",E:E,">=4",F:F,"<=5%")鈥?/strong>鈥嬫墠鎼炲畾锛屼綘璇翠竴涓鍙锋槸涓嶆槸鑳借浜哄懡锛?/p>

---

馃挕椴滀负浜虹煡鐨勫喎鐭ヨ瘑

寰蒋瀹樻柟鏁版嵁鏄剧ず锛?2%鐨凙VERAGEIFS鍏紡閿欒鏄洜涓哄尯鍩熷紩鐢ㄦ病閿佸畾銆傛瘮濡傛嫋鍔ㄥ叕寮忔椂鏉′欢鍖哄煙璺戝亸浜嗭紝璁板緱鐢ㄢ€?strong>鈥?span>$B:$B:B鈥?/strong>鈥嬬粷瀵瑰紩鐢紒杩樻湁涓獨鎿嶄綔锛氬湪鏉′欢閲岀洿鎺ュ紩鐢ㄥ崟鍏冩牸锛屾瘮濡?>"&G1锛屽氨鑳藉疄鐜板姩鎬侀槇鍊艰皟鏁淬€?/p>

鏈€鍚庣敥涓帇鐐告妧宸э細鐢ㄢ€?strong>鈥?AGGREGATE(1,6,鏁版嵁鍖哄煙)鈥?/strong>鈥嬪彲浠ュ拷鐣ラ敊璇€兼眰骞冲潎锛岀壒鍒€傚悎澶勭悊浠庣郴缁熷鍑虹殑娈嬬己鏁版嵁銆備笉杩囪瀹炶瘽锛岃繖涓嚱鏁扮殑绗簩涓弬鏁版垜姣忔閮藉緱鏌ヨ鏄庢枃妗ｂ€斺€旇皝璁板緱浣?鏄眰骞冲潎銆?鏄拷鐣ラ敊璇€煎晩锛?/p>