用VBA編程可以實現。
第壹步:輸入壹個數據清單如圖:
第二步:點擊開發工具-visual basic-插入模塊
第三步:輸入自定義函數代碼如下:
Function ac(a)
Dim arr(1 To 100)
Dim zharr(1 To 1000, 1 To 3)
Dim zhgs As Integer
Dim gs As Integer '數據個數
gs = 0 '統計數據的個數 即R值
For Each myc In a
gs = gs + 1
arr(gs) = myc.Value
Next myc
zszZS = 0
For i = 1 To gs - 1
For j = i + 1 To gs
zhgs = zhgs + 1
zharr(zhgs, 1) = arr(i)
zharr(zhgs, 2) = arr(j)
zharr(zhgs, 3) = Abs(arr(i) - arr(j))
Next j
Next i
Set zczzd = CreateObject("scripting.dictionary")
For i = 1 To zhgs
If zharr(i, 3) > 0 Then
If Not zczzd.exists(zharr(i, 3)) Then
zczzd.Add zharr(i, 3), 1
End If
End If
Next i
ac = zczzd.Count - gs + 1
End Function
第四步:在C1單元格輸入公式:=ac(A1:A$7)。結果如圖:
第五步:將C1的公式下拉至C7.結果如圖: