英文:
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))))
英文:
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)))))
答案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
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.")))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论