excel - Excel：如何减去两个不同的单元格，每个单元格都包含一组值

Question

我有包含 2 列的 Excel 数据：

FirstColumn        SecondColumn
1.0,3.0,4.5,2.5    0.5,0.75,2.25,1.25
2.0,0.5,2.3        1.75,1.5,2.0
1.5,2.0,3.0,4.0    0.75,5.0,2.0,3.75

有什么方法可以减去这两列并获得每一行的减法结果？预期的结果是这样的：

row1: 0.5,2.25,2.25,1.25

如果有人可以提供帮助将不胜感激。用python编程也可以。谢谢你。

标签： excel

Answer 1

我的解释在代码注释中。

Option Explicit

Function mutliDifference(str1 As String, str2 As String, _
                         Optional delim As String = ",")

    Dim tmp1 As Variant, tmp2 As Variant, i As Long

    'cteate arrays from the strings passed into the function
    tmp1 = Split(str1, delim)
    tmp2 = Split(str2, delim)

    'ensure that the arrays of are of equal size
    ReDim Preserve tmp2(UBound(tmp1))

    'Debug.Print LBound(tmp1) & ":" & UBound(tmp1)
    'Debug.Print LBound(tmp2) & ":" & UBound(tmp2)

    'loop through arrays and perform subtraction
    For i = LBound(tmp1) To UBound(tmp1)
        tmp1(i) = Format(Abs(tmp2(i) - tmp1(i)), "0.00")
    Next i

    'create string from subtraction results
    mutliDifference = Join(tmp1, delim)

End Function