vba 在 Excel 2010 中将字符串解析为数组（无 VBscript）

Parsing a string as an array in Excel 2010 (without VBscript)

提问by Steven Ruppert

I've got a whole bunch of cells containing arbitrary length arrays stored as semicolon-delimited strings, ranging in length from 1 to 65 entries, like:

我有一大堆包含任意长度数组的单元格，这些数组存储为分号分隔的字符串，长度从 1 到 65 个条目，例如：

• pcmsh15(232);pcmsh16(232);pcmsh17(136);
• pcmsh12(40);
• pcmsh12(40);
• pcmsh12(5);pcmsh15(20);

• pcmsh15(232);pcmsh16(232);pcmsh17(136);
• pcmsh12(40);
• pcmsh12(40);
• pcmsh12(5);pcmsh15(20);

I want a way to sum up the numbers in the parenthesis in Excel 2010 without using VBA, keeping in mind that they are arbitrary lengthstrings, each contained in their own cell.

我想要一种在不使用 VBA 的情况下对 Excel 2010 中括号中的数字求和的方法，请记住它们是任意长度的字符串，每个字符串都包含在自己的单元格中。

I've currently got a VBA function that I wrote that sums the numbers in parenthesis, but it's slowing my spreadsheet down. I know I can use Excel's SUBSTITUTE function to turn the semi-colon delimited arrays into something resembling Excel's internal array format, like

我目前有一个我编写的 VBA 函数，它对括号中的数字求和，但它减慢了我的电子表格速度。我知道我可以使用 Excel 的 SUBSTITUTE 函数将分号分隔的数组转换为类似于 Excel 内部数组格式的内容，例如

="{"&SUBSTITUTE([@[data]],";",",")&"}"

But unfortunately, Excel won't parse that as an array in SUM or COUNTIF, only as a string. One workaround I found makes a named range that references the cell with a string-formatted array, but since I also have an arbitrary number of these arrays in cells, I can't go naming every single cell.

但不幸的是，Excel 不会将其解析为 SUM 或 COUNTIF 中的数组，只会解析为字符串。我发现的一种解决方法是创建一个命名范围，该范围使用字符串格式的数组引用单元格，但由于我在单元格中也有任意数量的这些数组，因此我无法命名每个单元格。

Is something like this possible in "pure" excel functions?

在“纯”excel函数中是否有可能出现这种情况？

回答by jtolle

You can actually do this with no VBA involved. The method is kind of ridiculous, though. But, just for fun...

您实际上可以在不涉及 VBA 的情况下执行此操作。不过这个方法有点可笑。但是，只是为了好玩……

In cell B4I put one of the strings you want to "parse":

在单元格中，B4我放置了您要“解析”的字符串之一：

B4:  pcmsh15(232);pcmsh16(232);pcmsh17(136);

Then I put the following formulas in the cells indicated:

然后我在指定的单元格中输入以下公式：

B6:  =SUBSTITUTE(B4,"pcmsh", """dummy")
B8:  =SUBSTITUTE(B6,"(",""";")
B10: =SUBSTITUTE(B8,")","")
B12: =MID(B10, 1, LEN(B10)-1)
B14: ="{"&B12&"}"

That transforms the starting text into a text representation of a legal Excel array:

这会将起始文本转换为合法 Excel 数组的文本表示：

B14: {"dummy15";232;"dummy16";232;"dummy17";136}

You can make Excel evaluate that string and return an actual array with the following trick. Create a named formula that we'll call eval_left. Make sure cell C14is selected when you create the named formula:

您可以使用以下技巧让 Excel 评估该字符串并返回一个实际数组。创建一个命名公式，我们将称之为eval_left。确保C14在创建命名公式时选择单元格：

=EVALUATE(B14)

Note that the formula uses a relative reference, so if you use that name from a cell other than C14, you'll see that the B14has changed to whatever cell is just to the left.

请注意，该公式使用相对引用，因此如果您在 以外的单元格中使用该名称C14，您将看到B14已更改为左侧的任何单元格。

This uses the old Excel 4 macro function EVALUATE, which takes a string and returns the value that that string represents. Now we can put a final formula in cell C14:

这使用了旧的 Excel 4 宏函数EVALUATE，它接受一个字符串并返回该字符串表示的值。现在我们可以在单元格中放置一个最终公式C14：

C14: =SUM(eval_left)

Since SUMignores non-numeric strings, you get the answer you want, with no VBA. (You do still get the macro warning though, because of the use of EVALUATE.)

由于SUM忽略非数字字符串，因此无需 VBA 即可获得所需的答案。（不过，您仍然会收到宏警告，因为使用了EVALUATE.）

Like I said, I consider this a just-for-fun curiousity. You're way better off using VBA for stuff like this.

就像我说的，我认为这只是为了好玩的好奇心。你最好使用 VBA 来处理这样的事情。

EDIT:

编辑：

In the unlikely event you wanted to actually do something like this, you'd run into the length limit of what EVALUATEcan handle. You'd have to chop up your longer strings into less than 65 values, not use "dummy", etc., but it would still be doable.

在不太可能发生的情况下，您真的想要做这样的事情，您会遇到EVALUATE可以处理的长度限制。您必须将较长的字符串切成少于 65 个值，而不是使用“虚拟”等，但它仍然是可行的。