|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 五号刺客 于 2020-2-14 16:03 编辑
质量工具的 FMEA 在2019年进行更新,取消了S*O*D=RPN的RPN值,改用组合方式的AP值,改用AP后FMEA表再使用的Excel完成时因为是组合方式(如下图),常规公式不易计算,我使用了一个自定义公式的进行计算AP值,使用的时候直接使用=AP(S,O,D)(SOD为这个自定义函数的三个参数)的方式获得对应的AP值,使用的效果如下图,代码分享如下
搞质量又没学习过VBA的朋友应该用得上,但是这个方法有缺点因为要使用VBA,所以模板表需要保存为带宏的.xlsm 文件,做为使用人员如果不清楚把它保存为普通文件,公式会被丢失掉导致错误
VBA大神来看的话是很简单的,欢迎各大神分享其它更方便的解决方案
2019第五版FMEA手册AP评价表
Function AP(S, O, D) As String
S = Application.WorksheetFunction.Substitute(S, " ", "")
O = Application.WorksheetFunction.Substitute(O, " ", "")
D = Application.WorksheetFunction.Substitute(D, " ", "")
S = Application.WorksheetFunction.Substitute(S, Chr(10), "")
O = Application.WorksheetFunction.Substitute(O, Chr(10), "")
D = Application.WorksheetFunction.Substitute(D, Chr(10), "")
S = S * 1
O = O * 1
D = D * 1
Select Case S
Case Is >= 9
Select Case O
Case Is >= 6: AP = "H"
Case Is >= 4
Select Case D
Case Is >= 2: AP = "H"
Case Is = 1: AP = "M"
End Select
Case Is >= 2
Select Case D
Case Is >= 7: AP = "H"
Case Is >= 5: AP = "M"
Case Is <= 4: AP = "L"
End Select
Case Is = 1: AP = "L"
End Select
Case Is >= 7
Select Case O
Case Is >= 8: AP = "H"
Case Is >= 6
Select Case D
Case Is >= 2: AP = "H"
Case Is = 1: AP = "M"
End Select
Case Is >= 4
Select Case D
Case Is >= 7: AP = "H"
Case Is <= 6: AP = "M"
End Select
Case Is >= 2
Select Case D
Case Is >= 5: AP = "M"
Case Is <= 4: AP = "L"
End Select
Case Is = 1: AP = "L"
End Select
Case Is >= 4
Select Case O
Case Is >= 8
Select Case D
Case Is >= 5: AP = "H"
Case Is <= 4: AP = "M"
End Select
Case Is >= 6
Select Case D
Case Is >= 2: AP = "M"
Case Is = 1: AP = "L"
End Select
Case Is >= 4
Select Case D
Case Is >= 7: AP = "M"
Case Is <= 6: AP = "L"
End Select
Case Is >= 2: AP = "L"
Case Is = 1: AP = "L"
End Select
Case Is >= 2
Select Case O
Case Is >= 8
Select Case D
Case Is >= 5: AP = "M"
Case Is <= 4: AP = "L"
End Select
Case Is <= 7: AP = "L"
End Select
Case Is = 1: AP = "L"
End Select
If Not D Like "[1-9]" And D <> 10 Then AP = "D值错误"
If Not O Like "[1-9]" And O <> 10 Then AP = "O值错误"
If Not S Like "[1-9]" And S <> 10 Then AP = "S值错误"
End Function
|
|