Excel中的数组常量 [英] Array Constants in Excel

问题描述

我有一个数组常量在单元格A1中定义为{1,2,3}。这显示为1（数组中的第一个值）。

我想使公式SUM（A1）返回6.但是，SUM正在使用A1作为单单元阵列，而不是A1内的数组常量，因此SUM（A1）返回1。

同样，我期望AVERAGE（A1）返回1，而不是2。

所以简单来说，我如何使SUM（A1）返回与SUM（{1,2,3}）相同的值？ / p>

我不想使数组常量为命名引用，因为我为每一行定义了一个不同的数组常量。

感觉就像我被C ++中的某种方式去解除引用！

解决方案

这个简短的VBA UDF应该做这个工作。

 公共功能ToArray（rngCell As Range）As Variant 
 
 Dim sFormString As String 
 sFormString = rngCell.Formula 
 
 Dim adReturn（）As Double 
 ReDim adReturn（1）As Double 
 If不是Len（sFormString） -  3> 0然后
 ToArray = adReturn 
退出函数
 Else 
 sFormString = Mid（sFormString，3，Len（sFormString） -  3）
结束如果
 
 Dim vTest As Variant 
 vTest = Split（sFormString，，）
 
 ReDim adReturn（LBound（vTest）To UBound（vTest））As Double 
 
 Dim iArrayCounter As Integer 
对于iArrayCounter = LBound（vTest）到UBound（vTest）
 adReturn（iArrayCounter）= vTest（iArrayCounter）
下一步iArrayCounter 
 
 ToArray = adReturn 
 
结束函数
  

（如果带大括号的字符串为例如，在单元格b2中，您需要在另一个单元格中写入的是= sum（toarray（b2）））

I have an array constant defined in cell A1 as {1,2,3}. This displays as "1" (the first value in the array).

I would like to have the formula SUM(A1) return 6. However, SUM is using A1 as a single-celled array, rather than the array constant contained inside A1 - and therefore SUM(A1) returns 1.

Likewise, I would expect AVERAGE(A1) returns 1 instead of 2.

So simply speaking, how do I get SUM(A1) to return the same value as SUM({1,2,3})?

I don't want to make the array constant a named reference because i'm defining a different array constant for every row.

It feels like i'm stuck in C++ w/o a way to dereference!

解决方案

This short VBA UDF should do the job.

Public Function ToArray(rngCell As Range) As Variant

    Dim sFormString As String
    sFormString = rngCell.Formula

    Dim adReturn() As Double
    ReDim adReturn(1) As Double
    If Not Len(sFormString) - 3 > 0 Then
        ToArray = adReturn
        Exit Function
    Else
        sFormString = Mid(sFormString, 3, Len(sFormString) - 3)
    End If

    Dim vTest As Variant
    vTest = Split(sFormString, ",")

    ReDim adReturn(LBound(vTest) To UBound(vTest)) As Double

    Dim iArrayCounter As Integer
    For iArrayCounter = LBound(vTest) To UBound(vTest)
        adReturn(iArrayCounter) = vTest(iArrayCounter)
    Next iArrayCounter

    ToArray = adReturn

End Function

(If the string with the curly brackets is in cell b2 for example, all you need to write in another cell is =sum(toarray(b2)) )

这篇关于Excel中的数组常量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！