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

问题描述

G'Day，

我有更多的问题来帮助我了解Excel的数组公式（Control + Shift + Enter）如何动态地读取每个单元格式。

我做了一个简单的例子来告诉你我的意思。

我创建了一个小型虚构农场，有一些动物，名字列出，并提供动物所发出的声音。在下一列中，我创建了一个名为MakesSound的用户定义函数，该函数接受一个动物的输入，并回应动物的声音。如下面的快照图片所示。

不幸的是，我认为一个数组形式可以拿起，我有不同的单元格列出动物，而是像这样的快照结束。

那么我怎么可以请求阵列表来识别B列中有不同的单元格，因为我知道Quacks不是其他动物的答案。 ： - ）

这是另一个快照，显示了我所使用的comparsion和代码的数组形式旁边的公式。

公共函数MakesSound（AnimalName As String）As Variant 
选择案例AnimalName 
案例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 =嗯？ 
结束选择
结束功能
  

我接受建议。 p>

谢谢，
Peter。

解决方案

你需要做您的数组函数将数据读入数组，处理并创建一个输出数组。
然后使用ctrl-shift-enter将数组函数nneeds输入到多单元格数组公式（D3：D7）中。

 公共函数MakesSound（AnimalName As Range）As Variant 
 Dim Ansa（）As Variant 
 Dim vData As Variant 
 Dim j As Long 
 vData = AnimalName.Value2 
 ReDim Ansa（1到UBound（vData），1到1）
对于j = 1到UBound（vData）
选择案例vData（j，1）
案例Is =鸭
 Ansa（j，1）=嘎嘎！ 
 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？ 
结束选择
下一步j 
 MakesSound = Ansa 
结束函数
  

G'Day,

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.

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.

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. :-)

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

I'm open to suggestions.

Thanks, Peter.

解决方案

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屋！