Excel：通过单元格引用将数组参数传递给公式 [英] Excel: pass array argument to formula through cell reference

问题描述

从代码角度来看，这似乎很简单，但是我仍然陷入#VALUE上。

Code-wise this seems so simple, but I still get stuck on a #VALUE.

我制作了一个自定义Excel函数，该函数将数组作为参数，本身就可以正常工作，就像这样使用：= countArray（{3,5}）会得到2（为了说明，实际公式不同）。

I made a custom Excel function taking an array as an argument, which in itself is working fine when used just like that: =countArray({3,5}) would give 2 (for illustration, actual formula is different).

但是，我想用作参数的实际数组包含在另一个单元格中（例如= {3,5}）。因此，我想将函数称为= countArray（A1），使用对包含要用作参数的数组的引用，而不是直接包含该数组，因此我可以使用

However, the actual array I'd like to use as the argument is contained in another cell (e.g. ={3,5}). As such, I'd like to call the function as =countArray(A1), using a reference to the cell containing the array I'd like to use as the argument instead of directly including the array, so I could use one and the same formula.

有没有办法做到这一点？

Is there a way to do this?

Cell formulas:
A1: ={3,5}
B1: =countArray({3,5})
B2: =countArray(A1)

Function countArray(arr()) As Integer
countArr = UBound(arr)
End Function

B1中的公式有效，而B2中的公式无效。
至于A1的内容，当像这样放入Excel时，它只显示3作为单元格值-因此，我认为单元格实际上确实包含一个数组。
那么不是吗？单元格是否可能不包含数组作为它们的值（或从其他单元格引用这些值）而不将其存储为定界字符串并将其转换为数组？

The formula in B1 works, the one in B2 doesn't. As for the contents of A1, when putting it in like this Excel only shows 3 for the cell value -- because of this I assumed the cell therefore does actually contain an array. Is this not the case then? Is it not possible for cells to contain array as their value (or have these referred to from other cells) without storing it as a delimited string and converting it to an Array?

编辑：已经有几年了，所以也许我可以对自发布此问题以来尝试过的道路发表一些评论。最初，我试图朝着对单元内JSON结构进行操作的方向发展，并取得了一定的成功。从那时起，我还发现最近的Power Query（自Excel 2016本地集成以来）确实在其单元格中支持列表结构（以及其他），并且实际上支持相关功能。我不再使用其中的任何一个（现在对Spark更感兴趣），但是希望这可以帮助其他人在这个问题上绊脚石。

Edit: it's been a few years, so maybe I could comment a bit on roads I've tried since I posted this question. Initially I tried to go in the direction of doing operations on in-cell JSON structures, with a certain amount of success. Since then I'd also found that the recent Power Query (since Excel 2016 natively integrated) does support list structures (among others) in its 'cells' though, and does in fact supported related functions. I don't use any of these as much anymore (now more interested in Spark), but hope this might help others stumbling upon this question.

推荐答案

我不认为您有直接的方法...就像您想要将单元格值转换为数组

I don't think so you have a direct way... as you would want to Convert Cell Value to Array

希望它会有所帮助！

这篇关于Excel：通过单元格引用将数组参数传递给公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！