我如何按“ |”将此数据集拆分。但是将其放在床单中的一列中吗？

发布于 2025-01-24 11:15:50 字数 923 浏览 2 评论 0原文

我正在尝试在此处使用split（），但是它只能获得第一行：请注意，范围是动态获得的，因此周围的移动列不会丢失colum参考：

=ARRAYFORMULA(
INDIRECT("Sheet1!"&REGEXEXTRACT(ADDRESS(5,MATCH("PO #",Sheet1!$5:$5,0)),"[A-Z]+")&"5:"&REGEXEXTRACT(ADDRESS(5,MATCH("PO #",Sheet1!$5:$5,0)),"[A-Z]+"))&"|"&
INDIRECT("Sheet2!"&REGEXEXTRACT(ADDRESS(5,MATCH("PO #",Sheet2!$5:$5,0)),"[A-Z]+")&"5:"&REGEXEXTRACT(ADDRESS(5,MATCH("PO #",Sheet2!$5:$5,0)),"[A-Z]+")))

这生成，这不是最终结果：

这个想法是由|将其拆分。并堆叠起来，避免空白。

以下是数据示例：

谢谢！

原文

I'm trying to use SPLIT() here, but it only gets the first row:
Notice that the ranges are obtained dynamically, so that moving columns around won't lose the colum reference:

=ARRAYFORMULA(
INDIRECT("Sheet1!"®EXEXTRACT(ADDRESS(5,MATCH("PO #",Sheet1!$5:$5,0)),"[A-Z]+")&"5:"®EXEXTRACT(ADDRESS(5,MATCH("PO #",Sheet1!$5:$5,0)),"[A-Z]+"))&"|"&
INDIRECT("Sheet2!"®EXEXTRACT(ADDRESS(5,MATCH("PO #",Sheet2!$5:$5,0)),"[A-Z]+")&"5:"®EXEXTRACT(ADDRESS(5,MATCH("PO #",Sheet2!$5:$5,0)),"[A-Z]+")))

This generates, which is not the end result:

The idea is to split it by the | and stack them up and avoid blanks.

Here's a sample of the data: https://docs.google.com/spreadsheets/d/1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU/edit?usp=sharing

Thank you!

分享到QQ

分享到微博

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

发布评论

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

晚风撩人 2025-01-31 11:15:50

使用：

=ARRAYFORMULA(QUERY({
INDIRECT("Orders!"®EXEXTRACT(ADDRESS(6,MATCH("PO #",Orders!$5:$5,0)),"[A-Z]+")&"6:"®EXEXTRACT(ADDRESS(6,MATCH("PO #",Orders!$5:$5,0)),"[A-Z]+"));
INDIRECT("Scenarios!"®EXEXTRACT(ADDRESS(6,MATCH("PO #",Scenarios!$5:$5,0)),"[A-Z]+")&"6:"®EXEXTRACT(ADDRESS(6,MATCH("PO #",Scenarios!$5:$5,0)),"[A-Z]+"))}, "where Col1 is not null", ))

更新：

=INDEX(QUERY({
 INDIRECT(   "Orders!"&ADDRESS(6, MATCH("PO #",    Orders!5:5, ))&":"&SUBSTITUTE(ADDRESS(2, MATCH("PO #",    Orders!5:5, ), 4), 2, ));
 INDIRECT("Scenarios!"&ADDRESS(6, MATCH("PO #", Scenarios!5:5, ))&":"&SUBSTITUTE(ADDRESS(2, MATCH("PO #", Scenarios!5:5, ), 4), 2, ))}, 
 "where Col1 is not null", ))

use:

=ARRAYFORMULA(QUERY({
INDIRECT("Orders!"®EXEXTRACT(ADDRESS(6,MATCH("PO #",Orders!$5:$5,0)),"[A-Z]+")&"6:"®EXEXTRACT(ADDRESS(6,MATCH("PO #",Orders!$5:$5,0)),"[A-Z]+"));
INDIRECT("Scenarios!"®EXEXTRACT(ADDRESS(6,MATCH("PO #",Scenarios!$5:$5,0)),"[A-Z]+")&"6:"®EXEXTRACT(ADDRESS(6,MATCH("PO #",Scenarios!$5:$5,0)),"[A-Z]+"))}, "where Col1 is not null", ))

update:

=INDEX(QUERY({
 INDIRECT(   "Orders!"&ADDRESS(6, MATCH("PO #",    Orders!5:5, ))&":"&SUBSTITUTE(ADDRESS(2, MATCH("PO #",    Orders!5:5, ), 4), 2, ));
 INDIRECT("Scenarios!"&ADDRESS(6, MATCH("PO #", Scenarios!5:5, ))&":"&SUBSTITUTE(ADDRESS(2, MATCH("PO #", Scenarios!5:5, ), 4), 2, ))}, 
 "where Col1 is not null", ))