csv - 如何在不同列中分隔逗号分隔值，同时在 Google 表格的其余行中保留值？

Question

您如何调整逗号分隔值，以分隔用逗号分隔的值并为此值创建一个新行并且其他值与该值来自的行中的相同？看起来像这样：

由此..

..到这个。

我实际上正在寻找一个尽可能不使用谷歌脚本并且不使用巨大的长而复杂的公式的答案。可以使用谷歌表格中的数据透视表，但也不是我的偏好。但如果不能只使用公式，那么我也愿意接受其他答案。

我已经有这个问题一年多了，经过几个小时的搜索，我无法在网上找到严肃的答案。会有使用谷歌脚本的答案，但这并不真正属于我的问题范围。如果当前问题仍未得到解答，我愿意调整或改写我的问题。

我自己不知道如何回答这个问题，我所做的尝试不值得认真对待。

标签： csvgoogle-sheetsgoogle-sheets-formula

Answer 1

可拖动公式解决方案

让我们看看我能不能让这个球滚起来。

乍看上去

不幸的是，该解决方案不稳定，因为它依赖于Flatten未记录的函数（将任何范围转换为列数组），并且需要两个公式才能工作。虽然我确信您可以在没有 的情况下执行相同的操作Flatten()，但这至少可以为我们节省一些打字时间，因为我们非常依赖它。如果不使用 flatten，我们可以使用 实现相同的效果TRANSPOSE(SPLIT(TEXTJOIN(...))，这几乎没有那么优雅。

核心公式，虽然它确实有一个线性增长因子，并且可能会因为更多的列而变得混乱，但它确实有一个易于遵循的设置模式。它也可以被拖动，这是对单个ArrayFormula.

第 1 阶段：序列化行

正如您可能已经预料到的，我们将使用一些字符串序列化技巧来获得我们想要的东西。这是核心公式：

=TEXTJOIN(",",,
ArrayFormula(
    Flatten(Flatten(Flatten(Flatten(Flatten(
      SPLIT(A1,",")&",")&
      SPLIT(B1,",")&",")&
      SPLIT(C1,",")&",")&
      SPLIT(D1,",")&",")&
      SPLIT(E1,",")&";")
))&","

如您所见，它说明了行中每个单元格内的任何逗号。要添加更多列，只需添加另一个Flatten(列并将您的列添加到列表中。只要确保最后一个使用 a;而不是 a ,。

我们利用了这样一个事实：通常，当ArrayFormula应用于列向量和行向量时，我们可以对两者混合在一起的每个排列进行操作。

例子：

• =ArrayFormula({0;1}&{2,3})相当于={"02","12";"03","13"}
• =ArrayFormula(SEQUENCE(10)*SEQUENCE(1,10))给了我们一个 10x10 的乘法表。

在我们的例子中，我们使用它来根据每个单元格中的逗号生成所有可能的行排列，将行序列化为 CSV 字符串，以分号结尾，然后将所有行连接成一个长字符串。额外的","是，我们可以在下一阶段将多个表连接在一起。

设置正确的列数后，将其向下拖动到表格的高度。（注意：如果您的某些值可以为空，您还必须对每个值进行一些错误检查SPLIT。）

第 2 阶段：反序列化

这个公式要简单得多。（假设序列化数据在 F 列中。）

=ArrayFormula(
  SPLIT(
    TRANSPOSE(
      SPLIT(
        JOIN(,F:F),
        ";,",
      )
    ),
    ","
  )
)

首先，使用 . 将所有字符串粘合在一起JOIN。由于我们知道每一行都以“;”结尾，因此我们将其拆分以获取我们的行。之后，我们可以通过拆分“，”将每一行拆分为单元格，从而生成我们的表格。

结论

• 当然，这不是一个单一的ArrayFormula，但这些公式都没有那么复杂，这很好。ArrayFormulas 会很快变得混乱和混乱。
• 我们也设法避免编写脚本，这是一个优点。
• 如果觉得不美观也可以隐藏序列化栏！

希望这至少有点用处。