作者:乔山办公网日期:
返回目录:excel表格制作
A2公式"=abs(A1-average($A$1:$L$1))"
向右拖到e68a84e8a2ade799bee5baa6336L2
最大偏差“=max($A$2:$L$2)”
'以下用代码自定义函数来实现
Function maxdev(arr1 As Range) As Double
Dim xrow As Integer, xcol As Integer
Dim dev() As Double
On Error GoTo err1
Application.Volatile
xrow = arr1.Rows.Count
xcol = arr1.Columns.Count
ReDim dev(xrow * xcol - 1)
xrow1 = 1
xcol1 = 1
For i = 0 To xrow * xcol - 1
dev(i) = Abs(arr1.Cells(xrow1, xcol1).Value - Application.WorksheetFunction.Average(arr1))
xcol1 = xcol1 + 1
If xcol1 > xcol Then
xrow1 = xrow1 + 1
If xrow1 > xrow Then Exit For
xcol1 = 1
End If
Next
maxdev = dev(0)
For i = 1 To xrow * xcol - 1
If dev(i) > maxdev Then maxdev = dev(i)
If i = UBound(dev) Then Exit For
Next
Exit Function
err1:
maxdev = 999999 '错误就显示为999999
End Function
Sub check()
For i = 1 To UsedRange.Rows.Count
Cells(i, 3) = "√"
For j = 1 To Len(Cells(i, 2))
If InStr(1, Cells(i, 1), Mid(Cells(i, 2), j, 1)) Then Cells(i, 3) = "X"
Next j
Next i
End Sub
VBA代码已贴zhidao,版公式可能比权较困难
在C2输入公式 =countif(a1:b2,c1)
c1 输入 f C2就能得出相应的值了
maxValue = application.worksheetfunction.max(1,2)
调用excel max函数
返回 maxValue=2