Excel 2011-如何将textJoin的输出从单元格的值更改为单元格名称，因此我可以将其放在Sum()中以添加这些值 [英] Excel 2011 - How to change output of textJoin from value of cell, to the cell name, so I can put it in Sum() to add those values

问题描述

我正在使用下面列出的textJoin UDF将来自不同行的值组合到单个单元格中；它显示每个值.但是，我想知道是否可以操纵此UDF的输出，以便不仅可以简单地显示值，还可以添加值并获得值的总和.或理想情况下，如果我可以修改变量以指示其添加值.有谁知道是否可以指示该UDF(我没有创建)输出单元名称(A2，B2，C2)，如果可以，我可以将该输出放入Sum()函数中，以便将其添加A2 + B2 + C2?

I'm using the textJoin UDF listed below to combine values from various rows into a single cell; it displays each value. However, I want to know if I can manipulate the output of this UDF so that instead of simply displaying the values I can add the values and get the sum of the values. Or ideally, if I can just modify variables to instruct it to ADD the values. Does anyone know if this UDF (which I didn't create) can be instructed to output the Cell names (A2, B2, C2), and if so can I put that output within the Sum() function so that it will add A2+B2+C2?

Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
    Dim d As Long
    Dim c As Long
    Dim arr2()
    Dim t As Long, y As Long
    t = -1
    y = -1
    If TypeName(arr) = "Range" Then
        arr2 = arr.Value
    Else
        arr2 = arr
    End If
    On Error Resume Next
    t = UBound(arr2, 2)
    y = UBound(arr2, 1)
    On Error GoTo 0

    If t >= 0 And y >= 0 Then
        For c = LBound(arr2, 1) To UBound(arr2, 1)
            For d = LBound(arr2, 1) To UBound(arr2, 2)
                If arr2(c, d) <> "" Or Not skipblank Then
                    TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
                End If
            Next d
        Next c
    Else
        For c = LBound(arr2) To UBound(arr2)
            If arr2(c) <> "" Or Not skipblank Then
                TEXTJOIN = TEXTJOIN & arr2(c) & delim
            End If
        Next c
    End If
        TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
    End Function

似乎应该有一种方法可以将输出从单元格的值转换为单元格名称(即C2，C3，C4等)，然后将整个内容放在Sum()函数中，以便只需将单元格加在一起.另外，是否有一个函数可以广告放置在函数中的值，而不使用单元格名称?

It seems like there should be a way to convert the output from the value of the cell to the cell name (i.e. C2, C3, C4, etc), then put the whole thing within the Sum() function so that it simply adds the cells together. Alternatively, is there a function that ads the values placed within the function instead of using cell names?

我要添加突出显示的单元格(G2).我的值分别为10和20.如果我可以让textJoin输出单元格名称(例如C2，C3)，则似乎应该可以使用= Sum(textJoin(...)).

I want to add the highlighted cell (G2). I have the value of 10 and 20. It seems like I should be able to use =Sum(textJoin(...)) if I can get textJoin to output the cell names (i.e. C2, C3).

推荐答案

如果您希望它仅对值求和，则可以在UDF的末尾添加这样的内容

if you want it to just total the values then you can add something like this to the end of the UDF

    Dim total As Long
    Dim txtPart
    For Each txtPart In Split(TEXTJOIN, delim)
        total = total + CLng(txtPart)       
    Next txtPart
    TEXTJOIN = total

示例

Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
Dim d As Long
Dim c As Long
Dim arr2()
Dim t As Long, y As Long
t = -1
y = -1
If TypeName(arr) = "Range" Then
    arr2 = arr.Value
Else
    arr2 = arr
End If
On Error Resume Next
t = UBound(arr2, 2)
y = UBound(arr2, 1)
On Error GoTo 0

If t >= 0 And y >= 0 Then
    For c = LBound(arr2, 1) To UBound(arr2, 1)
        For d = LBound(arr2, 1) To UBound(arr2, 2)
            If arr2(c, d) <> "" Or Not skipblank Then
                TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
            End If
        Next d
    Next c
Else
    For c = LBound(arr2) To UBound(arr2)
        If arr2(c) <> "" Or Not skipblank Then
            TEXTJOIN = TEXTJOIN & arr2(c) & delim
        End If
    Next c
End If
    TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
    'add the below loop to add each number together
    Dim total As Long
    Dim txtPart
    For Each txtPart In Split(TEXTJOIN, delim)
        total = total + CLng(txtPart)

    Next txtPart
    TEXTJOIN = total
End Function

这篇关于Excel 2011-如何将textJoin的输出从单元格的值更改为单元格名称，因此我可以将其放在Sum()中以添加这些值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！