感謝qlong之分享啟發，針對措施優先級(AP)的Excel函數計算，再分享3種寫法給大家參考。
AIAG & VDA FMEA中AP的公式
https://www.pinzhi.org/thread-90445-1-1.html
措施優先級(AP)表，摘錄自 BSI 對於新版FMEA所應有的認知
https://www.bsigroup.com/localfiles/zh-tw/e-news/no191/knowledge-of-the-new-fmea-randy-chiang.pdf
摘錄自BSI，措施優先級(AP)表
新人發表學習見解，如對各位大大有所幫助，懇請回帖鼓勵，歡迎不吝指導。
第一種 寫法 IFS 列舉法
第二種 寫法 巢狀IF 列舉法
第三種 寫法 SWITCH(TRUE…)＋巢狀 IF
第一種 (利用 X<=AP 且 AP<= Y 方式撰寫，更易理解)
=IFERROR(IFS(
AND(9<=A3,A3<=10,6<=B3,B3<=10,1<=C3,C3<=10),"H",
AND(9<=A3,A3<=10,4<=B3,B3<=5,2<=C3,C3<=10),"H",
AND(9<=A3,A3<=10,4<=B3,B3<=5,1<=C3,C3<=1),"M",
AND(9<=A3,A3<=10,2<=B3,B3<=3,7<=C3,C3<=10),"H",
AND(9<=A3,A3<=10,2<=B3,B3<=3,5<=C3,C3<=6),"M",
AND(9<=A3,A3<=10,2<=B3,B3<=3,1<=C3,C3<=4),"L",
AND(9<=A3,A3<=10,1<=B3,B3<=1,1<=C3,C3<=10),"L",
AND(7<=A3,A3<=8,8<=B3,B3<=10,1<=C3,C3<=10),"H",
AND(7<=A3,A3<=8,6<=B3,B3<=7,2<=C3,C3<=10),"H",
AND(7<=A3,A3<=8,6<=B3,B3<=7,1<=C3,C3<=1),"M",
AND(7<=A3,A3<=8,4<=B3,B3<=5,7<=C3,C3<=10),"M",
AND(7<=A3,A3<=8,4<=B3,B3<=5,1<=C3,C3<=6),"M",
AND(7<=A3,A3<=8,2<=B3,B3<=3,5<=C3,C3<=10),"M",
AND(7<=A3,A3<=8,2<=B3,B3<=3,1<=C3,C3<=4),"L",
AND(7<=A3,A3<=8,1<=B3,B3<=1,1<=C3,C3<=10),"L",
AND(4<=A3,A3<=6,8<=B3,B3<=10,5<=C3,C3<=10),"H",
AND(4<=A3,A3<=6,8<=B3,B3<=10,1<=C3,C3<=4),"M",
AND(4<=A3,A3<=6,6<=B3,B3<=7,2<=C3,C3<=10),"M",
AND(4<=A3,A3<=6,6<=B3,B3<=7,1<=C3,C3<=1),"L",
AND(4<=A3,A3<=6,4<=B3,B3<=5,7<=C3,C3<=10),"M",
AND(4<=A3,A3<=6,4<=B3,B3<=5,1<=C3,C3<=6),"L",
AND(4<=A3,A3<=6,1<=B3,B3<=3,1<=C3,C3<=10),"L",
AND(2<=A3,A3<=3,8<=B3,B3<=10,5<=C3,C3<=10),"M",
AND(2<=A3,A3<=3,8<=B3,B3<=10,1<=C3,C3<=4),"L",
AND(2<=A3,A3<=3,1<=B3,B3<=7,1<=C3,C3<=10),"L",
AND(1<=A3,A3<=1,1<=B3,B3<=10,1<=C3,C3<=10),"L"
),"") |
第二種 利用 巢式IF 逐層判斷
=IFERROR(
IF(A3>=9,
IF(B3>=6,"H",
IF(B3>=4,IF(C3>=2,"H","M"),
IF(B3>=2,IF(C3>=7,"H",IF(C3>=5,"M","L")),"L")
)
),
IF(A3>=7,
IF(B3>=8,"H",
IF(B3>=6,IF(C3>=2,"H","M"),
IF(B3>=4,"M",
IF(B3>=2,IF(C3>=5,"M","L"),"L")
)
)
),
IF(A3>=4,
IF(B3>=8,IF(C3>=5,"H","M"),
IF(B3>=6,IF(C3>=2,"M","L"),
IF(B3>=4,IF(C3>=7,"M","L"),"L")
)
),
IF(A3>=2,
IF(B3>=8,IF(C3>=5,"M","L"),"L"),
"L"
)))),
"")
|
第三種 利用 SWITCH(TRUE…)＋巢狀 IF (簡潔清楚)
=IFERROR(
SWITCH(TRUE,
A3>=9, IF(B3>=6,"H",IF(B3>=4,IF(C3>=2,"H","M"),IF(B3>=2,IF(C3>=7,"H",IF(C3>=5,"M","L")),"L"))),
A3>=7, IF(B3>=8,"H",IF(B3>=6,IF(C3>=2,"H","M"),IF(B3>=4,"M",IF(B3>=2,IF(C3>=5,"M","L"),"L")))),
A3>=4, IF(B3>=8,IF(C3>=5,"H","M"),IF(B3>=6,IF(C3>=2,"M","L"),IF(B3>=4,IF(C3>=7,"M","L"),"L"))),
A3>=2, IF(B3>=8,IF(C3>=5,"M","L"),"L"),
TRUE, "L"
),
"") |
|
发表于 
发表于 
发表于 