excel计算达标率 达标率怎么算
Excel\u600e\u4e48\u8ba1\u8fbe\u6807\u7387\uff1fExcel\u8ba1\u8fbe\u6807\u7387\u7684\u5177\u4f53\u64cd\u4f5c\u6b65\u9aa4\u5982\u4e0b\uff1a
1\u3001\u9996\u5148\u6211\u4eec\u6253\u5f00\u7535\u8111\u91cc\u7684excel\u8868\u683c\uff0c\u5728\u7535\u5b50\u8868\u683c\u4e2d\uff0c1-6\u6708\u4efd\u7684\u9500\u552e\uff0c\u8fbe\u6807\u8d85\u8fc760000\u5143\u7684\u4eba\u6570\u6240\u5360\u7684\u6bd4\u4f8b\u3002
2\u3001\u5728\u5355\u5143\u683c\u4e2d\u8f93\u5165\u201c=COUNTIFS()\u201d\u3002
3\u3001\u8c03\u51fa\u63d2\u5165\u51fd\u6570\u516c\u5f0f\uff0c\u8f93\u5165\u7b2c\u4e00\u4e2a\u53c2\u6570\u201c\u88681[\u4e00\u6708\u4efd]\u201d\uff0c\u53731\u6708\u4efd\u9500\u552e\u6240\u6709\u6570\u636e\u3002
4\u3001\u7b2c\u4e8c\u4e2a\u53c2\u6570\uff0c\u662f\u6761\u4ef6\u5927\u4e8e60000\u5143\u7684\u4eba\u6570\uff0c\u5373\u8f93\u5165\u201c>60000\u201d\uff0c\u70b9\u51fb\u786e\u5b9a\u3002
5\u3001\u6c42\u51fa\u5927\u4e8e60000\u5143\u7684\u9500\u552e\uff0c\u518d\u9664\u4ee51\u6708\u4efd\u603b\u9500\u552e\uff0c\u5373\u5728\u8f93\u5165\u6846\u4e2d\u7ee7\u7eed\u8f93\u5165\u201c/COUNT()\u201d\u3002
6\u3001\u51fd\u6570\u516c\u5f0f\u4e2d\uff0c\u53d6\u51fa\u603b\u7684\u9500\u552e\uff0c\u5373\u53c2\u6570\u8f93\u5165\u201c\u88681[\u4e00\u6708\u4efd\u201d\u3002
7\u3001\u7136\u540e\u6211\u4eec\u5373\u53ef\u5f97\u51fa\u8fbe\u6807\u7387\u3002
\u8fbe\u6807\u7387\u7b97\u5f0f\uff1a\u8fbe\u6807\u7387 =(\u8fbe\u6807\u4eba\u6570/\u603b\u4eba\u6570)\u00d7100%\u3001\u8fbe\u6807\u7387=\uff08\u8fbe\u5230\u6807\u51c6\u7684\u6570\u91cf\u00f7\u603b\u6570\uff09\u3002
\u8fbe\u6807\u7387\u4e5f\u53ef\u7406\u89e3\u4e3a\u6709\u6548\u5ea6\u3001\u6709\u6548\u7387\uff0c\u5728\u67d0\u79cd\u7a0b\u5ea6\u4e0a\u4e5f\u662f\u5408\u683c\u7387\u3002\u8fbe\u6807\u7387\u662f\u6307\u8fbe\u5230\u67d0\u4e2a\u6807\u51c6\u7684\u6570\u91cf\u6bd4\u4e0a\u672a\u8fbe\u5230\u8fd9\u4e2a\u6807\u51c6\u7684\u6bd4\u7387\u3002
\u8fbe\u6807\uff0c\u5373\u8fbe\u5230\u6807\u51c6\u7684\u610f\u601d\u3002
\u6269\u5c55\u8d44\u6599\uff1a
1\u3001\u4e3e\u4f8b\uff1a\u67d0\u6821\u5bf9\u4e5d\u5e74\u7ea7\u5973\u751f\u8fdb\u884c\u767e\u7c73\u6d4b\u8bd5\uff0c\u8fbe\u6807\u6210\u7ee9\u4e3a18\u79d2\uff0c\u7b2c\u4e00\u7ec4\u76848\u540d\u5973\u751f\u7684\u6210\u7ee9\u5206\u522b\u662f\u3008\u5176\u4e2d\u6b63\u53f7\u8868\u793a\u5927\u4e8e18\u79d2\u3009\uff1a-1\u3001+0.8\u30010\u3001-1.2\u3001-0.1\u30010\u3001+0.5\u3001-0.6 \u6c42\u8fbe\u6807\u7387\u3002
\u6210\u7ee9\u4e3a\u8d1f\u7684\u548c\u4e3a0\u8bf4\u660e\u6210\u7ee9\u6bd413\u79d2\u5c11\u6216\u76f8\u7b49\uff0c\u4e5f\u5c31\u662f\u8fbe\u6807\u67096\u4eba
\u6240\u4ee5\u8fbe\u6807\u7387=6\u9664\u4ee58\u4e58\u4ee5100%=75%
2\u3001\u7b49\u7ea7\u8fbe\u6807\uff1a
\u8003\u8bd5\u6210\u7ee9\u7684\u7b49\u7ea7\u4e00\u822c\u5206\u4e3a4\u79cd\uff0c\u5206\u522b\u662f\uff1a\u4f18\u79c0\u3001\u826f\u597d\u3001\u53ca\u683c\u3001\u4e0d\u53ca\u683c\u3002 \u6bcf\u4e2a\u7b49\u7ea7\u90fd\u6709\u8fbe\u6807\u3002
\u5728\u767e\u5206\u5236\u8003\u8bd5\u4e2d\uff0c\u7b49\u7ea7\u8fbe\u6807\u60c5\u51b5\u5982\u4e0b\uff1a
\u4e0d\u53ca\u683c\uff1a\u4f4e\u4e8e60\u5206\u7684\u6210\u7ee9\uff1b\u53ca\u683c\uff1a60\u5206\u4ee5\u4e0a\u7684\u6210\u7ee9\uff1b\u826f\u597d\uff1a75\u5206\u4ee5\u4e0a\u7684\u6210\u7ee9\uff1b\u4f18\u79c0\uff1a85\u5206\u4ee5\u4e0a\u7684\u6210\u7ee9\u3002
\u53c2\u8003\u8d44\u6599\u6765\u6e90\uff1a\u767e\u5ea6\u767e\u79d1\u2014\u2014\u8fbe\u6807\u7387
=text(SUMPRODUCT((A1:A100>0.39)*(A1:A100<0.46))/count(a:a）,"0.00%")其他类推
若是两个范围加在一起算达标率则
=TEXT(SUMPRODUCT(((A1:A100>0.39)*(A1:A100<0.46)+(A1:A100>1.29)*(A1:A100<1.36)))/COUNT(A:A),"0.00%")
这些数据在A1里面？还是在A列
先把这些数据放在A列，每格一个数据
以下公式
=SUMPRODUCT(($A$1:$A$2000>0.39)*($A$1:$A$2000<0.46)*ROW(1:1))/COUNT(A:A)
=SUMPRODUCT(($A$1:$A$2000>1.29)*($A$1:$A$2000<1.36)*ROW(1:1))/COUNT(A:A)
换算成百分比
B1输入
=if（or（and(A1>0.39,A1<0.46),and(A1>1.29,A1<1.36)),"达标","未达标")
若每个数据分别在a列的不同单元格，则大于0.39小于0.46
=text(SUMPRODUCT((A1:A100>0.39)*(A1:A100<0.46))/count(a:a）,"0.00%")其他类推
若是两个范围加在一起算达标率则
=TEXT(SUMPRODUCT(((A1:A100>0.39)*(A1:A100<0.46)+(A1:A100>1.29)*(A1:A100<1.36)))/COUNT(A:A),"0.00%")
绛旓細Excel璁杈炬爣鐜鐨勫叿浣撴搷浣滄楠ゅ涓嬶細1銆侀鍏堟垜浠墦寮鐢佃剳閲岀殑excel琛ㄦ牸锛屽湪鐢靛瓙琛ㄦ牸涓紝1-6鏈堜唤鐨勯攢鍞紝杈炬爣瓒呰繃60000鍏冪殑浜烘暟鎵鍗犵殑姣斾緥銆2銆佸湪鍗曞厓鏍间腑杈撳叆鈥=COUNTIFS()鈥濄3銆佽皟鍑烘彃鍏ュ嚱鏁板叕寮忥紝杈撳叆绗竴涓弬鏁扳滆〃1[涓鏈堜唤]鈥濓紝鍗1鏈堜唤閿鍞墍鏈夋暟鎹4銆佺浜屼釜鍙傛暟锛屾槸鏉′欢澶т簬60000鍏冪殑浜烘暟锛屽嵆...
绛旓細1銆侀鍏堝湪excel琛ㄦ牸涓緭鍏ユ垚缁╋紝骞剁偣鍑婚変腑鍙婃牸鐜囦笅鐨勭┖鐧藉崟鍏冩牸銆2銆佸湪鍗曞厓鏍间腑杈撳叆璁＄畻鍏紡锛=COUNTIF(A2:A6,">=60")/COUNT(A2:A6)銆3銆佺敓鎴愮粨鏋滃悗鍐嶅湪浼樼鐜囦笅杈撳叆鍏紡锛=COUNTIF(A2:A6,">=85")/COUNT(A2:A6)銆4銆佸嵆鍙敓鎴愬強鏍肩巼涓庝紭绉鐜囩殑缁撴灉锛屽皢涓や釜璁＄畻缁撴灉璋冩暣涓虹櫨鍒嗘瘮鏄剧ず鍗冲彲銆5...
绛旓細=text(SUMPRODUCT((A1:A100>0.39)*(A1:A100<0.46))/count(a:a锛,"0.00%")鍏朵粬绫绘帹 鑻ユ槸涓や釜鑼冨洿鍔犲湪涓璧绠楄揪鏍囩巼鍒 =TEXT(SUMPRODUCT(((A1:A100>0.39)*(A1:A100<0.46)+(A1:A100>1.29)*(A1:A100<1.36)))/COUNT(A:A),"0.00%")...
绛旓細=IF(A2>0.97,MIN((A2-0.97)/0.97/1%*0.2,0.5),-MIN((0.97-A2)/0.97/1%*0.4,5))
绛旓細=鏍囧噯鍒-if(鍚堟牸鐜囪揪鏍鍊>=96%,0,min(int((96%-鍚堟牸鐜囪揪鏍囧)/1%)*0.1,2))
绛旓細=IF(A2>0.97,MIN((A2-0.97)/0.97/1%*0.2,0.5),-MIN((0.97-A2)/0.97/1%*0.4,5))
绛旓細杈炬爣鐜鐨勫钩鍧囧 =AVERAGEIFS(C:C,A:A,"2014-11-11",B:B,"鍚嶅瓧")闇鍦ㄥ彟涓寮犲伐浣滆〃涓紝鍙互杩欐牱鍋(鍋囪鏁版嵁鍦⊿heet1)锛氬悓涓鏃ユ湡鍚屼竴涓悕瀛楀嚭鐜扮殑娆℃暟 =COUNTIFS(Sheet1!A:A,"2014-11-11",Sheet1!B:B,"鍚嶅瓧")杈炬爣鐜囩殑骞冲潎鍊 =AVERAGEIFS(Sheet1!C:C,Sheet1!A:A,"2014-11-11",...
绛旓細鍏紡涓猴細=IF(A1<0.6,0,MIN((A1-0.6)*150,30))濡傚浘鎵绀猴細
绛旓細=if(杈炬爣鐜>80%,婊″垎,if(杈炬爣鐜>=50%,绾挎у緱鍒,涓嶅緱鍒))
绛旓細=AVERAGE(LEFT(G17,3),MID(G17,5,3),RIGHT(G17,3))/F17鐢ㄨ繖涓叕寮忓惂,涓嬫媺灏卞彲浠ヤ簡
