在 Excel 2010 中将字符串解析为数组（不使用 VBscript）

发布于 2024-11-17 21:57:57 字数 660 浏览 3 评论 0原文

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

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

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

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

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

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

在“纯”Excel函数中可能有这样的事情吗？

原文

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:

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 length strings, each contained in their own cell.

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

="{"&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.

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

晨与橙与城 2024-11-24 21:57:57

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

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

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

然后，我将以下公式放入指示的单元格中：

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

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

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

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

=EVALUATE(B14)

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

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

C14: =SUM(eval_left)

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

就像我说的，我认为这只是为了好玩的好奇心。对于这样的事情，使用 VBA 会更好。

编辑：

万一您想要实际执行类似的操作，您会遇到 EVALUATE 可以处理的长度限制。您必须将较长的字符串切成少于 65 个值，而不是使用“虚拟”等，但这仍然是可行的。

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

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

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:

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 C14 is selected when you create the named formula:

=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 B14 has changed to whatever cell is just to the left.

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:

C14: =SUM(eval_left)

Since SUM ignores 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.)

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

EDIT:

In the unlikely event you wanted to actually do something like this, you'd run into the length limit of what EVALUATE can 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.

回复收藏 0 原文

~没有更多了~