作者:乔山办公网日期:
返回目录:excel表格制作
用数组公式:=SUM(MMULT(A1:A10,TRANSPOSE(A1:A10)))-SUM(A1:A10*A1:A10)
用VBA自定义函数吧,使用方便
按ALT+F11 打开代码窗口636f7079e799bee5baa6e79fa5e98193339,
在左边的格(工程资源管理器)内,右键,
插入,模块,
将下面的代码复制到右边(模块1的代码窗口),
然后关闭代码窗口,返回sheet表
-----------
Public Function iHe(ByRef c As Range) As String
iHe = (c(1, 1).Value + c(1, 2).Value) & "," & _
(c(1, 1).Value + c(1, 3).Value) & "," & _
(c(1, 1).Value + c(1, 4).Value) & "," & _
(c(1, 2).Value + c(1, 3).Value) & "," & _
(c(1, 2).Value + c(1, 4).Value) & "," & _
(c(1, 3).Value + c(1, 4).Value)
End Function
----------------
现在就可以将这个iHe()函数当做普通函数使用了
在E1内输入公式
=iHe(A1:D1)
显示结果:
1 2 3 4 3,4,5,5,6,7
128 256 721 541 384,849,669,977,797,1262
--------------
改进代码:
上面的代码只能计算出4个横向单元格,下面的代码不限制横向单元格的数量
---
Public Function iHe(ByRef c As Range) As String
Dim n As Long: n = c.Columns.Count
If n = 1 Then iHe = c.Value & "": Exit Function
Dim x As String
For i = 1 To n - 1
For j = i + 1 To n
x = x & "," & (c(1, i).Value + c(1, j).Value)
Next j, i
iHe = Right(x, Len(x) - 1)
End Function
-----------
使用方法同上