主页 / coding / 问题 / 79098702
Accepted
Anna
Asked: 2024-10-17 23:02:13 +0800 CST

IMPORTRANGE 与 REGEXREPLACE/REGEXMATCH 函数

  • 772

我有与书籍产品代码相关的虚拟数据：

XXX AAA 鲍勃 文档代码 测试类型
P01 嘘 01 BK-001-CR 1. 内容质量测试
   - 校对
   - 事实核查
   - 最终编辑
2. 设计和布局测试
P01 嘘 02 BK-002-CR 1. 物理生产测试
2. 分发测试
P02 欧克 01 BK-001-CR 1. 内容质量测试
   - 校对
   - 事实核查
   - 最终编辑
2. 设计和布局测试
P03 酷 01 BK-001-RT 1. 内容质量测试
   - 校对
   - 事实核查
   - 最终编辑
2. 设计和布局测试

如此链接所列。

我想将其清理为预期的输出，如下所示：

产品代码 文档代码 测试类型
P01.BOO.01
P02.OOK.01		 BK-001-CR 内容质量测试
   - 校对
   - 事实核查
   - 最终编辑
P01.BOO.01
P02.OOK.01		 BK-001-CR 设计和布局测试
P01.BOO.02 BK-002-CR 物理生产测试
P01.BOO.02 BK-002-CR 分布测试
P03.KOO.01 BK-001-RT 内容质量测试
   - 校对
   - 事实核查
   - 最终编辑
P03.KOO.01 BK-001-RT 设计和布局测试

如此链接所列。

对于输出，我尝试在产品代码列中使用公式：

=ARRAYFORMULA( 
  TEXTJOIN(" "; TRUE; 
    FILTER(
      IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dIVsBszYwQnELHwu8C7BaamZnQTz2zogzKlrkq72IZQ/edit?gid=0#gid=0"; "Test!B2:B5") & "." & 
      IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dIVsBszYwQnELHwu8C7BaamZnQTz2zogzKlrkq72IZQ/edit?gid=0#gid=0"; "Test!C2:C5") & "." &
      IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dIVsBszYwQnELHwu8C7BaamZnQTz2zogzKlrkq72IZQ/edit?gid=0#gid=0"; "Test!D2:D5"); 
      (REGEXMATCH(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dIVsBszYwQnELHwu8C7BaamZnQTz2zogzKlrkq72IZQ/edit?gid=0#gid=0"; "Test!F2:F5"); I2) + 
      REGEXMATCH(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dIVsBszYwQnELHwu8C7BaamZnQTz2zogzKlrkq72IZQ/edit?gid=0#gid=0"; "Test!B2:B5"); H2)) > 0
    )
  )
)

在文档代码列中：

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dIVsBszYwQnELHwu8C7BaamZnQTz2zogzKlrkq72IZQ/edit?gid=0#gid=0"; "Test!E2:F5"); "SELECT Col1 WHERE Col2 CONTAINS '" & I2 & "'")

在测试类型列中：

=ARRAYFORMULA(
  UNIQUE(
    FILTER(
      TRIM(
        FLATTEN(
          SPLIT(
            REGEXREPLACE(
              IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dIVsBszYwQnELHwu8C7BaamZnQTz2zogzKlrkq72IZQ/edit?gid=0#gid=0"; "Test!F2:F5"); 
              "\d+\."; 
              "~"
            ); 
            "~"
          )
        )
      ); 
      (TRIM(FLATTEN(SPLIT(REGEXREPLACE(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dIVsBszYwQnELHwu8C7BaamZnQTz2zogzKlrkq72IZQ/edit?gid=0#gid=0"; "Test!F2:F5"); "\d+\."; "~"); "~"))) <> "")
    )
  )
)

但是，仍然有错误，并且仍然与预期的输出不匹配。 在此处输入图片描述

还有其他公式吗？

google-sheets

2 个回答

  1. Best Answer

    这是一个通用的方法，您可以相应地进行调整：

    =let(Λ;importrange("1dIVsBszYwQnELHwu8C7BaamZnQTz2zogzKlrkq72IZQ";"B2:F7");Σ;lambda(x;choosecols(Λ;x));a;Σ(1);b;Σ(2);c;Σ(3);d;Σ(4);e;Σ(5);
 reduce(tocol(;1);unique(tocol(d;1));lambda(x;y;vstack(x;reduce(tocol(;1);sequence(max(index(ifna(--regexextract(split(xlookup(y;d;e);char(10));"^\d+")))));lambda(f;q;vstack(f;
 hstack(join(char(10);filter(a&"."&b&"."&c;d=y));y;regexextract(xlookup(y;d;e);"(?s).*"&q&". (.*?)(?:\n"&q+1&". |$)")))))))))

    在此处输入图片描述

    • 2

  2. 您也可以尝试这个数组公式：

    =ArrayFormula(LET(url,"https://docs.google.com/spreadsheets/d/1mNDf04c51ucs5NOSQG3CRVRdlyQnJ2AvmmIvuIO0FU0/",
              refProdCode,"'Test'!B2:D5",
              refDocCode,"'Test'!E2:E5",
              refTestType,"'Test'!F2:F5",
              prodCode_data,IMPORTRANGE(url,refProdCode),
              docCode_data,IMPORTRANGE(url,refDocCode),
              testType_data,IMPORTRANGE(url,refTestType),
              prodCode,TRANSPOSE(REGEXREPLACE(TRIM(QUERY(TRANSPOSE(prodCode_data),,9^9))," ",".")),
              testType_split,SPLIT(REGEXREPLACE(testType_data,"[0-9]*\.","#"),"#"),
              concatAll,prodCode&"@"&docCode_data&"#"&testType_split,
              breakdown,SPLIT(FLATTEN(concatAll),"@"),
              initialSummary,QUERY(breakdown,"SELECT Col1,MIN(Col1) GROUP BY Col1 PIVOT Col2 LABEL MIN(Col1) ''",0),
              getHeader,CHOOSEROWS(initialSummary,1),
              getBody,QUERY(initialSummary,"OFFSET 1"),
              concatBody,REGEXREPLACE(TRIM(QUERY(getBody,,9^9))," ",CHAR(10)),
              finalProdDocCodes,SPLIT(TRANSPOSE(getHeader),"#"),
              final,QUERY(HSTACK(TRANSPOSE(concatBody),finalProdDocCodes),"ORDER BY Col1,Col2 OFFSET 1",0),
              final)

    ）

    在此处输入图片描述

    确保替换 URL 和参考资料。

    • 1

相关问题