如何在跨Google Sheet查询中返回对应行号？

问题分析 #

你当前的QUERY公式能正常获取目标Sheet的指定列数据，但无法返回原数据在目标Sheet中的行号——直接嵌套ROW()函数无效，因为ROW()默认返回当前工作表的单元格行号，而非目标Sheet中数据对应的原始行号。

解决方案 #

需要先将目标Sheet的原始行号与导入数据合并成一个数组，再用QUERY筛选。推荐使用LET函数优化，避免重复调用IMPORTRANGE（提升效率）：

优化后的公式 #

=LET(
  imp_data, IMPORTRANGE("https://docs.google.com/spreadsheets/d/abunchofnumbersandsuch/","'2018 Data Log'!B2:O"),
  data_with_row, ARRAYFORMULA({SEQUENCE(ROWS(imp_data),1,2), imp_data}),
  QUERY(data_with_row, "select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15 where Col5 contains '"&'Data Entry'!C4&"'", 0)
)

公式说明 #

1. LET函数：定义imp_data变量存储导入的目标Sheet数据，避免重复调用IMPORTRANGE
2. SEQUENCE生成原行号：SEQUENCE(ROWS(imp_data),1,2)生成从2开始的连续数字，对应目标Sheet中B2:O区域的每一行原始行号（因为数据从B2开始）
3. 合并数组：用{行号数组, 导入数据}将行号与原始数据合并，此时原数据的第4列（Col4）变为合并数组的第5列（Col5），因此QUERY条件中需改为Col5 contains ...
4. QUERY筛选：选择合并数组中的行号（Col1）和原数据列（Col2至Col15），并按条件筛选

简化版（无LET，适合旧版Google Sheets） #

如果无法使用LET函数，可直接嵌套，但会重复调用IMPORTRANGE：

=QUERY(
  ARRAYFORMULA(
    {SEQUENCE(ROWS(IMPORTRANGE("https://docs.google.com/spreadsheets/d/abunchofnumbersandsuch/","'2018 Data Log'!B2:O")),1,2),
     IMPORTRANGE("https://docs.google.com/spreadsheets/d/abunchofnumbersandsuch/","'2018 Data Log'!B2:O")}
  ),
  "select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15 where Col5 contains '"&'Data Entry'!C4&"'",
  0
)

注意事项 #

• 首次使用IMPORTRANGE需授权访问目标Sheet，按提示完成授权即可
• 若目标Sheet的B2:O范围有新增行，公式会自动适配行号序列

内容的提问来源于stack exchange，提问作者Sam Nichols