英文:
Google Sheets: How can I Flatten and Split Data while including multiple columns?
问题
我想从包括复选框的Google表格表单响应中提取数据。这些复选框被输出为逗号分隔的单列单元格。
数据/表单响应表：
在这里输入图像描述
目前和期望的结果
在这里输入图像描述
当前公式
=ARRAYFORMULA(TRIM(QUERY(SPLIT(FLATTEN(IF(IFERROR(SPLIT('Form Responses'!E2:E, ","))="",,
'Form Responses'!B2:B&"×"&SPLIT('Form Responses'!E2:E, ","))), "×"),
"where Col2 is not null")))
英文:
I would like to pull data from Google Sheets form responses that include checkboxes. The checkboxes are output into single column cells by commas.
Data/Form Responses sheet:
enter image description here
Current and Expected Results
enter image description here
Current Formula
=ARRAYFORMULA(TRIM(QUERY(SPLIT(FLATTEN(IF(IFERROR(SPLIT('Form Responses'!E2:E, ","))="",,
'Form Responses'!B2:B&"×"&SPLIT('Form Responses'!E2:E, ","))), "×"),
"where Col2 is not null")))
If anyone could please help I would appreciate it!
答案1
得分: 0
=ARRAYFORMULA(TRIM(QUERY(SPLIT(FLATTEN(IF(IFERROR(SPLIT('Form Responses'!E2:E, ","))="",,TEXT('Form Responses'!A2:A,"MM/DD/YYYY HH:MM:SS")&"×"&'Form Responses'!B2:B&"×"&'Form Responses'!C2:C&"×"&'Form Responses'!D2:D&"×"&SPLIT('Form Responses'!E2:E, ","))),"×"),"where Col2 is not null")))
英文:
SUGGESTION
Perhaps you can try this tweaked formula:
=ARRAYFORMULA(TRIM(QUERY(SPLIT(FLATTEN(IF(IFERROR(SPLIT('Form Responses'!E2:E, ","))="",,TEXT('Form Responses'!A2:A,"MM/DD/YYYY HH:MM:SS")&"×"&'Form Responses'!B2:B&"×"&'Form Responses'!C2:C&"×"&'Form Responses'!D2:D&"×"&SPLIT('Form Responses'!E2:E, ","))),"×"),"where Col2 is not null")))
Demo
答案2
得分: 0
你可以尝试使用这个LAMBDA()公式。只需要调整lambda输入参数中的范围。
=LAMBDA(UnpivotCol, AdjucentCols, QUERY(REDUCE(, REDUCE(, UnpivotCol, LAMBDA(a, x, VSTACK(a, TOCOL(INDEX(JOIN("|", INDEX(AdjucentCols, ROW(x) - (INDEX(ROW(UnpivotCol), 1) - 1))) & "|" & SPLIT(x, ",")))))), LAMBDA(t, g, VSTACK(t, SPLIT(g, "|")))), "offset 2 format Col1 'm/d/yyyy'", 0))(E2:E4, A2:D4)
英文:
You may try this LAMBDA() formula. You just need to adjust ranges in lambda input parameter.
=LAMBDA(UnpivotCol,AdjucentCols,QUERY(REDUCE(,REDUCE(,UnpivotCol,LAMBDA(a,x,VSTACK(a,TOCOL(INDEX(JOIN("|",INDEX(AdjucentCols,ROW(x)-(INDEX(ROW(UnpivotCol),1)-1))) & "|" & SPLIT(x,",")))))),LAMBDA(t,g,VSTACK(t,SPLIT(g,"|")))),"offset 2 format Col1 'm/d/yyyy'",0))(E2:E4,A2:D4)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论