Excel:在单个单元格中创建数据集或数据字符串

Question

我遇到了一些解决方案，但我想知道这是否只是 excel 的限制，还是没有办法创建多个值以从同一个单元格进行评估。 C3 根据相邻单元格中的所有值创建平均值。

我会解释为什么我想这样做。我正在为游戏中的元素价格制作电子表格，并希望从列的一个单元格中创建一个伪数据库。我想这样做的一个例子只是在一个单元格中:

2、40、300、200、340

没有C2(单元名称)总和所有条目除以条目总数之类的东西吗？用逗号分隔的所有值的平均值？

我知道这很容易用多个单元格完成，但我想减少水平困惑，所以想知道是否有任何方法可以用语法分隔值，而不使用 VBA 或过于复杂的代码。

Answer 1

公式是熊但是:

=SUMPRODUCT(--MID(SUBSTITUTE(C2,",",REPT(" ",99)),(ROW(INDIRECT("1:" & LEN(C2)-LEN(SUBSTITUTE(C2,",",""))+1))-1)*99+1,99))/(LEN(C2)-LEN(SUBSTITUTE(C2,",",""))+1)

这里发生了很多事情，所以让我们分解一下:
LEN(C2)-LEN(SUBSTITUTE(C2,",",""))+1 : 获取由 , 分隔的项目数, 在这种情况下 5 .在这个公式中使用了两次。第二个是获得平均值的分母。

第二个用于 (ROW(INDIRECT("1:" & LEN(C2)-LEN(SUBSTITUTE(C2,",",""))+1))-1) .这是迭代的部分。 SUMPRODUCT 将导致迭代并对各部分求和。

该行将返回 1,2,3,... 到 LEN(...) - LEN(...) 函数返回的值。我们从 0 开始减一。

每次迭代都乘以 99，我们加一，因为 MID 不喜欢 0 作为开始。
SUBSTITUTE(C2,",",REPT(" ",99))创建一个有 99 个空格来代替每个 ","的字符串。

MID 获取这个由现在 100 个空格分隔的数字字符串并对其进行解析。第二个标准是从 1,100,199,298,... 等单元格次数中的数字数量进行迭代。它总是落在一个空间上，因为我们给了它 100 个空间来着陆。

第三个标准设置每次迭代时要返回的字符串的长度。我们使用 99 是因为它总是会在数字之后出现一个空格。
--在 MID 前面将导致返回的已解析字符串恢复为数字。这会强制 Excel 删除多余的空格并仅查看数字。

正是这个数字 SUMPRODUCT() 维护并添加到每次迭代的总数中。