想请教EXCEL

规定值或 允许偏差	实测值或实测偏差值
	1	2	3	4	5	6	7	8	9	10	平均值 代表值		合格率 (%)	合格率 (%)
≥96	96	97	95	97	97	96	98	96	96	95	96.3			80
+10，-15	5	-15	12	15	10	-5	-8	5	-10	10	9.5	-9.5	80.0	80	如何在正数前加上+,而不影响公式的计算
50	45	55	50	40	50	50	45	45	52	50	48.2			80	合格率如何算
15	10	12	15	16	13	10	10	12	15	15	12.8			90
±0.3	0.2	0.1	-0.2	0.3	-0.3	-0.2	0.2	0.3	-0.4	0.2	0.2	-0.3		90

一、如何在正数前加上+,而不影响公式的计算:

将该格设置为文本,即可在正数前输入"+",如图:

二、合格率如何算:

请将条件改一下,如图:

"O4"单元格公式为:

=IF(B4=0,COUNTIF(C4:L4,A4)/COUNT(C4:L4)*100,SUMPRODUCT((C4:L4<=A4)*(C4:L4>=B4))/COUNT(C4:L4)*100)

N3输入 =countif(B3:K3,">=96")/10 N4输入 =(countif(B4:K4,"<=10")+countif(B4:K4,">=15"))/10 N5输入 =countif(B5:K5,"<=50")/10 N6输入 =countif(B6:K6,"<=15")/10 N7输入 =(countif(B7:K7,">=-0.3")-countif(B7:K7,">0.3"))/10 请参见我另一答案（及表格区域的例子） http://wenwen.soso.com/z/q166238440.htm