如何在不使用VBA的函数中将单个单元格中的字符串用作数组 [英] How to use a string from a single cell as an array in a function without VBA
问题描述
如何将字符串从单元格转换为函数内部的文本数组，该函数应该使用该数组，而不使用VBA，并且不将该数组添加到文档的任何其他部分?它将是超过1000行的这些数组之一.字符串格式为^ [a-zA-Z0-9.，-] * $，以，"作为分隔符.
How do I convert a string from a cell to a text array inside of a function, that should use the array, without using VBA and without adding the array into any other part of the document? It will be one of these arrays on more than 1000 rows. The string format is ^[a-zA-Z0-9.,-]*$ with "," as delimiter.
我有一个带有以下各列的excel表
答:要比较的ID号，以逗号分隔(分隔符可以根据需要更改).至少要支持约100个ID.
B:ID(列中行的每个值都是唯一的，但不会排序，因此无法排序，因为需要根据其他条件进行排序)
C:值(列中的几行可以具有相同的值)
D:输出以逗号分隔的ID的唯一ID，该ID在其行中具有最高的值
I have an excel table with the following columns
A: ID numbers to compare, separated by comma (delimiter can be changed if needed). About 100 ID's would be good to support at least.
B: ID (Each value on the rows in the column are unique but not sorted and can't be sorted because sorting is needed based on other criterias)
C: Value (Several rows in the column can have the same value)
D: Output the one ID of the comma separated ID's that has the highest value on its row
输出的问题部分
到目前为止，我已经创建了一个函数，该函数可以根据C列中的值在B列中找到正确的ID，但前提是必须在函数中手动输入A列中的字符串作为数组常量.我还没有获得从A列创建数组本身的功能，这是必需的.
The problem part of the output
So far I have made a function which find the correct ID in column B based on the values in column C but only if I enter the string from column A as an array constant manually within the function. I have not managed to get the function to create the array itself from column A, which is required.
在此代码中，我手动输入了A列中的值，该代码应能正常工作.
In this code I have entered the values from column A manually and this is working as it should.
=INDEX({"1.01-1","1.01-3","1.08-1","1.01-1-1A"},MATCH(MAX(INDEX(C$10:C$20,N(IF(1,MATCH({"1.01-1","1.01-3","1.08-1","1.01-1-1A"},B$10:B$20,0))))),INDEX(C$10:C$20,N(IF(1,MATCH({"1.01-1","1.01-3","1.08-1","1.01-1-1A"},B$10:B$20,0)))),0))
请注意，起始行不是第一行，并且该数组在函数中使用了3次.
Note that the start row is not the first row and the array is used 3 times in the function.
不起作用，但是如果包装在SUMPRODUCT()中，它将为SUMPRODUCT()函数提供一个数组，当然不可用，因为这样我就无法传递该数组.可以在问题
Not working but if wrapped in SUMPRODUCT() it provide an array to the SUMPRODUCT() function, of course not usable since I then can't pass on the array. The background to this code can be found in question Split a string (cell) in Excel without VBA (e.g. for array formula)!.
=TRIM(MID(SUBSTITUTE(A10,",",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A10)-LEN(SUBSTITUTE(A10,",",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A10)-LEN(SUBSTITUTE(A10,",",""))+1)))=1),99))
第二个代码输出数组的第一项并插入到第一个代码中，不会像在将第二个代码包装在SUMPRODUCT()中时那样更改此结果.
The second code output the first item of the array and inserted in the first code do not change this result as it did when wrapping the second code in SUMPRODUCT().
这是我在Excel中针对这种情况的简化测试设置的图片，与上面描述的相同.
Here is a picture of my simplified test setup in Excel for this case, identical to what is described above.
推荐答案
我不太确定您对公式的处理方式.
但是要将单元格的内容转换为逗号分隔的文本数组以用作INDEX或MATCH函数的数组参数，可以使用FILTERXML函数.您需要对XML和XPATH进行自学，以了解发生了什么，但是有很多网络资源可以解决此问题.
I'm not really sure what you are doing with your formula.
But to convert contents of a cell to a comma separated text array to be used as the array argument to the INDEX or MATCH functions, you can use the FILTERXML function. You'll need to educate yourself about XML and XPATH to understand what's going on, but there are plenty of web resource for this.
例如，使用
A10: "1.01-1","1.01-3","1.08-1","1.01-1-1A"
下面的公式将返回1.08-1.注意INDEX函数的row参数的3.
The formula below will return 1.08-1. Note the 3 for the row argument to the INDEX function.
=INDEX(FILTERXML("<t><s>" & SUBSTITUTE(SUBSTITUTE(A10,"""",""), ",", "</s><s>") & "</s></t>", "//s"),3)
这篇关于如何在不使用VBA的函数中将单个单元格中的字符串用作数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
