数据公式

Question

我希望获得一些帮助制定阵列的帮助，以更简单地查看我的数据来自Google表格。这是有人可以帮助我的吗？我有点熟悉，但是恐怕这样做的功能将非常复杂。

在我的Google表格上，我有一个问题，即人们可能会或可能不会多次填写。为了让他们这样做，我不得不在表格上反复复制问题。如果他们需要继续回答这个问题，他们将继续删除复制的版本。如果完成了，他们会选择“否”，并指示完成表格。

在我的进气表上，此数据通过大约30列列出，以说明该问题的每个复制版本。我希望订购填写的列，然后删除列，如果它们留为空白。这确实有助于该电子表格的可读性。

这是表格的副本：

Answer 1

尝试：

=ARRAYFORMULA(QUERY(SUBSTITUTE(SPLIT(FLATTEN(FLATTEN(QUERY(TRANSPOSE(SUBSTITUTE(
 IF('Form Responses 1'!A2:C="",,"¤"&TO_TEXT('Form Responses 1'!A2:C)), " ", "×")),,9^9))&FILTER(SUBSTITUTE(
 IF('Form Responses 1'!D2:AT="","¤×","¤"&'Form Responses 1'!D2:AT), " ", "×"), 
 MOD(COLUMN('Form Responses 1'!D2:AT), 4)=0)&FILTER(
 IF('Form Responses 1'!D2:AT="","¤×","¤"&'Form Responses 1'!D2:AT), 
 MOD(COLUMN('Form Responses 1'!D2:AT)-1, 4)=0)&FILTER(
 IF('Form Responses 1'!D2:AT="","¤×","¤"&'Form Responses 1'!D2:AT), 
 MOD(COLUMN('Form Responses 1'!D2:AT)-2, 4)=0)&FLATTEN(QUERY(TRANSPOSE(SUBSTITUTE(
 IF('Form Responses 1'!AV2:BA="",,"¤"&TO_TEXT('Form Responses 1'!AV2:BA)), 
 " ", "×")),,9^9))), "¤"), "×", " "), "where not Col4 starts with ' '"))

具有条件格式：

=INDEX(COUNTIFS($A:$A, $A1, ROW(A:A), "<="&ROW(A1)))>1

演示表

---

更新：

=ARRAYFORMULA(QUERY(SUBSTITUTE(SPLIT(FLATTEN(FLATTEN(QUERY(TRANSPOSE(SUBSTITUTE(
 IF('Form Responses 1'!A2:C="",,"¤"&TO_TEXT('Form Responses 1'!A2:C)), " ", "×")),,9^9))&FILTER(SUBSTITUTE(
 IF('Form Responses 1'!D2:AT="","¤×","¤"&'Form Responses 1'!D2:AT), " ", "×"), 
 MOD(COLUMN('Form Responses 1'!D2:AT), 4)=0)&FILTER(
 IF('Form Responses 1'!D2:AT="","¤×","¤"&'Form Responses 1'!D2:AT), 
 MOD(COLUMN('Form Responses 1'!D2:AT)-1, 4)=0)&FILTER(
 IF('Form Responses 1'!D2:AT="","¤×","¤"&'Form Responses 1'!D2:AT), 
 MOD(COLUMN('Form Responses 1'!D2:AT)-2, 4)=0)&FLATTEN(QUERY(TRANSPOSE(SUBSTITUTE(
 IF('Form Responses 1'!AV2:BA="","¤ ","¤"&TO_TEXT('Form Responses 1'!AV2:BA)), 
 " ", "×")),,9^9))), "¤"), "×", " "), "where not Col4 starts with ' '"))