代码之家 › 专栏 › 技术社区 › Anna

将多列中的ID标准化为一列

spreadsheet google-sheets-formula google-sheets

Anna · 技术社区 · 5 月前

我有与产品名称、工具名称和ID工具相关的数据,数据示例如下:

品名	工具名称	ID工具(标准)	ID工具(备选方案1)	ID工具(备选方案2)
产品A	工具A1	11-A1
产品A	工具A2	12-A2-01	12-A2-02	12-A2-03
产品A	工具A3	13-A3
产品B	工具B1	21-B1-01	21-B1-02
产品B	工具B2	22-B2
产品C	工具C1

我想把它清理成预期的输出,如下所示:

对于输出,我尝试了以下公式:

=LET(
    data; C3:E11;
    row; ROW(C3:E11)-ROW(C3:C11)+1;
    column; COLUMN(C3:E11)-COLUMN(C3:C11)+1;
    sorted_data; FLATTEN(data);
    label; FLATTEN(IF(COLUMN(C3:E11)-COLUMN(C3:C11)=0; "Standard"; "Alternative"));
    result; IFERROR(FILTER(HSTACK(sorted_data; label); LEN(sorted_data)>0); HSTACK(""; ""));
    result
)

但是工具类型信息没有出现,我仍然对如何输入产品和工具名称感到困惑,因为它总是出错。对改进公式有什么建议吗?

以下是测试表链接: https://docs.google.com/spreadsheets/d/11caWeL-PPyioovetrUf6rcy6iTb8vVuHgftAWzffpSM/edit?usp=sharing

4 回复 | 直到 5 月前

z.. 5 月前

您可以使用:

=ARRAYFORMULA(REDUCE(
  {"Product Name"\ "Tool Name"\ "ID Tool"\ "ID Tool Type"};
  SEQUENCE(COUNTA(A3:A));
  LAMBDA(a; i; LET(
    prod; INDEX(A3:A; i);
    tool; INDEX(B3:B; i);
    ids; INDEX(C3:E; i);
    VSTACK(
      a;
      IFERROR(
        SPLIT(
          TOCOL(
            prod & "â" & tool & "â" & TOROW(ids; 1) & "â" & 
            REGEXEXTRACT(FILTER(C2:E2; ids <> ""); "\((.+)\)"));
          "â";;
        );
        {INDEX(A3:B; i)\ ""\ ""}
      )
    )))))

Harun24hr 5 月前

试试下面的公式。看到你的文件 哈伦24小时 纸张。

=VSTACK(QUERY(INDEX(SPLIT(UNIQUE(FLATTEN(INDEX(A3:A8&"|"&B3:B8&"|"&IF(C3:E8<>"";C3:E8&"|"&REGEXEXTRACT(C2:E2;"\((.+)\)");""))));"|"));"where Col3 is not null";0);QUERY(A3:D8;"Where C is null";0))

上述公式的输出:

品名	工具名称	ID工具	ID工具类型
产品A	工具A1	11-A1	标准
产品A	工具A2	12-A2-01	标准
产品A	工具A2	12-A2-02	备选方案1
产品A	工具A2	12-A2-03	备选方案2
产品A	工具A3	13-A3	标准
产品B	工具B1	21-B1-01	标准
产品B	工具B1	21-B1-02	备选方案1
产品B	工具B2	22-B2	标准
产品C	工具C1

rockinfreakshow 5 月前

您可以尝试:

=reduce(tocol(;1);A3:index(A:A;match(;0/(A:A<>"")));lambda(a;c;ifna(vstack(a;let(Î;index(C:E;row(c));Î£;transpose(ifna(filter(vstack(Î;regexextract(C2:E2;"\((.+)\)"));Î<>"")));
        hstack(chooserows(index(A:B;row(c));sequence(rows(Î£);1;1;0));Î£))))))

-2

Mohamed Haroon 5 月前

要将Google表格中多列的ID标准化为一列,您可以使用 ARRAYFORMULA 函数与其他公式相结合,如 FLATTEN , FILTER ,以及 UNIQUE 。以下是实现这一目标的方法:

公式

=ARRAYFORMULA(UNIQUE(FILTER(FLATTEN(A:C), FLATTEN(A:C) <> "")))

解释

FLATTEN(A:C) :将列A、B和C中的所有值组合到一个数组中。
FILTER(FLATTEN(A:C), FLATTEN(A:C) <> "") :从扁平阵列中删除空白单元格。
UNIQUE(...) :确保生成的数组只包含不同的值(没有重复值)。
ARRAYFORMULA(...) :确保公式适用于所有行,而无需手动向下拖动。

更换 A:C 根据您想要标准化的列的实际范围。

Also Visit My blog