如何在没有 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 表格
A:要比较的ID号，用逗号分隔(分隔符可以根据需要更改).至少支持大约 100 个 ID 会很好.
B:ID(列中行上的每个值都是唯一的但未排序，无法排序，因为需要根据其他条件进行排序)
C:值(列中的几行可以有相同的值)
D:输出逗号分隔的 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() 函数提供一个数组，当然不可用，因为我无法传递该数组.此代码的背景可以在问题 在不使用 VBA 的情况下在 Excel 中拆分字符串(单元格)(例如对于数组公式)！.

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 函数的行参数的 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屋！