如何将 Excel 的数组公式用于 UDF 以正确读取每个单元格? [英] How to use Excel's array-formulas for a UDF to read each cell correctly?

问题描述

G'Day，

我有一个问题是为了帮助自己理解 Excel 的数组公式 (Control+Shift+Enter) 如何将每个单元格动态读入公式.

I have a question more towards helping myself understand how Excel's array-formulas (Control+Shift+Enter) can read each cell dynamically into the formula.

我做了一个简单的例子来向你展示我的意思.

I made an simplified example to show you what I mean.

我创建了一个虚构的小农场，里面有一些动物，按名称列出，并会提供动物发出的声音.在下一列中，我创建了一个名为 MakesSound 的用户定义函数，它接受它是什么动物的输入并响应它发出的声音.如下图截图所示.

I created a small fictional farm that has some animals, listed by names and will provide the sound of what the animal makes. In the next column I made a User Defined Function called MakesSound which takes an input of what animal it is and respond the sound that animal it makes. As shown in the snapshot picture below.

不幸的是，我认为一个数组公式可以发现我有不同的单元格列出了动物，但它以这个快照结尾.

Unfortunately, I thought an arrayformula could pick up that I have different cells listing the animals and it ends like this snapshot instead.

那么我怎样才能让 arrayformula 识别我在 B 列中有不同的细胞，因为我知道 Quacks 不是其他动物的答案.:-)

so how can I ask the arrayformula to recognise I have different cells in Column B as I know Quacks isn't the answer for other animals. :-)

这是另一个快照，显示了用于比较的 arrayformulas 旁边的公式和我使用的代码.

Here is another snapshot showing the formulas next to arrayformulas for comparsion and code I used as well.

Public Function MakesSound(AnimalName As String) As Variant
    Select Case AnimalName
        Case Is = "Duck"
            MakesSound = "Quack!"
        Case Is = "Cow"
            MakesSound = "Moo!"
        Case Is = "Bird"
            MakesSound = "Tweet!"
        Case Is = "Sheep"
            MakesSound = "Ba-Ba-Ba!"
        Case Is = "Dog"
            MakesSound = "Woof!"
        Case Else
            MakesSound = "Eh?"
    End Select
End Function

我愿意接受建议.

谢谢，彼得.

推荐答案

你需要让你的数组函数将数据读入一个数组，处理它并创建一个输出数组.
然后需要使用ctrl-shift-enter在多单元格数组公式(D3:D7)中输入数组函数.

you need to make you array function read the data into an array, process it and create an output array.
Then the array function nneeds to be entered in a multi-cell array formula (D3:D7) using ctrl-shift-enter.

Public Function MakesSound(AnimalName As Range) As Variant
Dim Ansa() As Variant
Dim vData As Variant
Dim j As Long
vData = AnimalName.Value2
ReDim Ansa(1 To UBound(vData), 1 To 1)
For j = 1 To UBound(vData)
    Select Case vData(j, 1)
    Case Is = "Duck"
        Ansa(j, 1) = "Quack!"
    Case Is = "Cow"
        Ansa(j, 1) = "Moo!"
    Case Is = "Bird"
        Ansa(j, 1) = "Tweet!"
    Case Is = "Sheep"
        Ansa(j, 1) = "Ba-Ba-Ba!"
    Case Is = "Dog"
        Ansa(j, 1) = "Woof!"
    Case Else
        Ansa(j, 1) = "Eh?"
    End Select
Next j
MakesSound = Ansa
End Function

这篇关于如何将 Excel 的数组公式用于 UDF 以正确读取每个单元格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！