Google表格：查询功能无法复制数据 [英] Google Sheets: Query function can't copy data

问题描述

我附上了示例Google表格数据（链接 ）。

I attached a sample Google Sheet data (Link).

它包含2张。 响应工作表包含Google表单的响应。由于Google表单有很多重复问题，因此我必须使用查询函数（工作表转换单元格B2）。查询命令是

It contains 2 sheets. The Response sheet contains the response of the Google Form. Since the Google form has a lot of repeating questions, I have to transform the data using the Query function (sheet Transformed cell B2). The query command is

=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"
)

然而，当您查看 Transformed 表时，Col中的一些数据F（对应于响应表单栏B）没有被传送。如何解决这个问题？

However when you look at the sheet Transformed, some data in Col F (Corresponding to Response sheet Col B) didn't get transferred. How could this problem be fixed?

提前致谢

推荐答案

人们经常注意到，用户很想在列中混合使用数据类型。 query（）函数会产生不希望的输出。如果列是用于数字值，那么只有数字值必须位于该列中。日期栏只能包含日期，而文本栏只能包含文本值。

It is often noted that users are tempted to mix data types within a column. The query() function will give undesirable output. If a column is intended for numeric values then only numerical values must reside in that column. Date columns must only contain dates and text columns only contain text values.

这并不意味着只要文本格式为文本格式，这些数字就不会出现在文本列中。
通常，query（）函数将假定列中的单元格类型数量多于该数据类型。例如，如果在同一列中有100个数字和20个文本值，则会为该列假设一个数字值。文本值很可能会被忽略。

This does not mean that numbers cannot appear in a text column as long as they are in a text format. Generally, the query() function will assume the greater number of cell types in a column to be that data type. For example, if there are 100 numbers and 20 text values in the same column then a numeric value will be assumed for that column. There is a good chance the text values will just be ignored. A slight change in your formula will convert all values to text.

看看这是否有帮助

=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"))

这篇关于Google表格：查询功能无法复制数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！