Google Sheets- 分割单元格并整理数据?

huangapple go评论74阅读模式
英文:

Google Sheets- Split Cell w/ Data and Organize?

问题

Hello Sheets Experts,

我想要分析我在Google表格中收到的一些时间数据。不幸的是,原始数据的格式不太友好。

示例带有数据的表格:
https://docs.google.com/spreadsheets/d/1-eyyVs67pp4nyL7jboWmOkGnrkNuuwiPP2RVv_SuDz0/edit?usp=sharing

理想情况下,我想要提取这个“源数据”(大约1500个单独的单元格)
源数据

并将最后4天列出的时间(在我的标题中指示)整理成单独的单元格以进行进一步分析(如下所示,我手动完成的):
理想结果

困难的是,每个单元格都是唯一的,因为它包含了数量和日历日期的各种变化。

是否有一种方法可以分解列A中的数据以实现我的期望结果?

我尝试了将文本拆分成多列,但我只能逐个进行,但希望有一种“更智能”的方法来处理1500行数据
拆分列

Ablebits Power Tools似乎可能有所帮助,但我没有订阅,并正在寻找通过公式实现这一目标的“免费”方法。

英文:

Hello Sheets Experts,

I'm looking to analzye some time data that I received in a Google Sheet. Unfortunately, the original data is in a less than friendly format.

Sample Sheet w/ Data:
https://docs.google.com/spreadsheets/d/1-eyyVs67pp4nyL7jboWmOkGnrkNuuwiPP2RVv_SuDz0/edit?usp=sharing

Ideally, I would like to take this "source data" (around 1500 separate cells)
Source Data

and pull the time listed for the last 4 days (indicated in my headers) and organize into separate cells for further analysis (like shown below, which I did manually):
Ideal Result

The tough thing is that each cell is unique as it contains a variety in both the quantity and calendar date.

Is there a way to break down the data in the column A to achieve my desired result?

I tried splitting the text to columns, which I can do 1 by 1- but am hoping there is a "smarter" way to do this with 1500 rows of data
split column

Ablebits powertools seems like it may help, but I don't have a subscription and am looking for a "free" way to do this via a formula.

答案1

得分: 1

=makearray(rows(A2:A),4,lambda(r,c,ifna(regexextract(index(A2:A,r),index(to_text(B1:E1),,c)&" | (.*?)"&CHAR(10))))

Google Sheets- 分割单元格并整理数据?

英文:

Added solution to your sheet here:

=makearray(rows(A2:A),4,lambda(r,c,ifna(regexextract(index(A2:A,r),index(to_text(B1:E1),,c)&" \| (.*?)"&CHAR(10)))))

Google Sheets- 分割单元格并整理数据?

答案2

得分: 0

你可以尝试使用这个公式:

=INDEX(VLOOKUP(B1:E1,INDEX(IFERROR((SPLIT(FLATTEN(SPLIT(A2:A13,CHAR(10))),"" | ")))),2,0)&" min."))

它通过换行符(CHAR(10))将值拆分,使用FLATTEN将其变为列,再次按“|”和FLATTEN拆分;然后使用标题进行VLOOKUP。

要应用到整个范围,你可以使用:

=BYROW(A2:A,LAMBDA(a,IF(a="",{"","","",""},INDEX(IFNA(VLOOKUP(B1:E1,INDEX(IFERROR((SPLIT(FLATTEN(SPLIT(a,CHAR(10))),"" | ")))),2,0)&" min."))))))
英文:

You can try with this formula:

=INDEX(VLOOKUP(B1:E1,INDEX(IFERROR((SPLIT(FLATTEN(SPLIT(A2:A13,CHAR(10)))," | ")))),2,0)&" min.")

It splits the values by the line separator (CHAR(10)), makes it a column with FLATTEN, splits again by "|" and FLATTEN; and then do a VLOOKUP with the headers

Google Sheets- 分割单元格并整理数据?

To the full range you can use:

=BYROW(A2:A,LAMBDA(a,IF(a="",{"","","",""},INDEX(IFNA(VLOOKUP(B1:E1,INDEX(IFERROR((SPLIT(FLATTEN(SPLIT(a,CHAR(10)))," | ")))),2,0)&" min.")))))

huangapple
  • 本文由 发表于 2023年2月14日 00:01:34
  • 转载请务必保留本文链接:https://go.coder-hub.com/75438331.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定