谷歌工作表：查询功能无法复制数据

Question

我附加了一个示例谷歌表格数据（链接）。

它包含两张纸。响应表包含谷歌表单的响应。由于Google表单有很多重复的问题，我必须使用查询功能（sheetTransformedcell B2）转换数据。查询命令是

=QUERY(
  {
    Response!E2:H,Response!B2:D;
    Response!I2:L,Response!B2:D;
    Response!M2:P,Response!B2:D;
    Response!Q2:T,Response!B2:D;
    Response!U2:X,Response!B2:D;
    Response!Z2:AC,Response!B2:D;
    Response!AD2:AG,Response!B2:D;
    Response!AH2:AK,Response!B2:D;
    Response!AL2:AO,Response!B2:D;
    Response!AP2:AS,Response!B2:D;
    Response!AU2:AX,Response!B2:D;
    Response!AY2:BB,Response!B2:D;
    Response!BC2:BF,Response!B2:D;
    Response!BG2:BJ,Response!B2:D;
    Response!BK2:BN,Response!B2:D;
    Response!BP2:BS,Response!B2:D;
    Response!BT2:BW,Response!B2:D;
    Response!BX2:CA,Response!B2:D;
    Response!CB2:CE,Response!B2:D;
    Response!CF2:CI,Response!B2:D;
    Response!CK2:CN,Response!B2:D;
    Response!CO2:CR,Response!B2:D;
    Response!CS2:CV,Response!B2:D;
    Response!CW2:CZ,Response!B2:D;
    Response!DA2:DD,Response!B2:D
  },
  "select * where Col1 <> '' Order By Col6"
)

但是，当您查看转换后的工作表时，F列中的一些数据（对应于响应工作表B列）没有被传输。这个问题怎么解决？

提前感谢

Answer 1

经常注意到，用户试图在一个列中混合数据类型。query（）函数将给出不需要的输出。如果一列用于数值，则该列中只能包含数值。日期列只能包含日期，文本列只能包含文本值。

这并不意味着数字只要是文本格式就不能出现在文本列中。通常，query（）函数将假定列中的单元格类型数越多，则该数据类型越多。例如，如果同一列中有100个数字和20个文本值，则该列将假定一个数值。文本值很有可能被忽略。公式中的细微更改将把所有值转换为文本。

看看这有没有帮助

=ArrayFormula(QUERY(to_text({
Response!E2:H,Response!B2:D;
Response!I2:L,Response!B2:D;
Response!M2:P,Response!B2:D;
Response!Q2:T,Response!B2:D;
Response!U2:X,Response!B2:D;
Response!Z2:AC,Response!B2:D;
Response!AD2:AG,Response!B2:D;
Response!AH2:AK,Response!B2:D;
Response!AL2:AO,Response!B2:D;
Response!AP2:AS,Response!B2:D;
Response!AU2:AX,Response!B2:D;
Response!AY2:BB,Response!B2:D;
Response!BC2:BF,Response!B2:D;
Response!BG2:BJ,Response!B2:D;
Response!BK2:BN,Response!B2:D;
Response!BP2:BS,Response!B2:D;
Response!BT2:BW,Response!B2:D;
Response!BX2:CA,Response!B2:D;
Response!CB2:CE,Response!B2:D;
Response!CF2:CI,Response!B2:D;
Response!CK2:CN,Response!B2:D;
Response!CO2:CR,Response!B2:D;
Response!CS2:CV,Response!B2:D;
Response!CW2:CZ,Response!B2:D;
Response!DA2:DD,Response!B2:D}),"select * where Col1 <> '' Order By Col6"))