使用 Google 电子表格查询语言获取行号 [英] Get row number with Google spreadsheet query language

问题描述

我想使用查询语言获取所需数据的行号，例如:select A,ROW_NUMBER() where (B=1).

I'd like to get the row number of the data I need with query language, i.g: select A,ROW_NUMBER() where (B=1).

但查询语言似乎没有提供如下函数:ROW_NUMBER()?

But it seems the query language doesn't provide function like: ROW_NUMBER()?

除了查询整个列并在 JavaScript 中进行计数之外，我该怎么做?

How do I do that, other than query the whole column and count it in JavaScript?

推荐答案

您可以使用 { 数组表达式 }，像这样:

You can include a row number in a query() by using an { array expression }, like this:

=arrayformula( query({A2:B, row(A2:B)}, "select Col1, Col3 where Col2 = 1", 0) )

在查询语句中，Col1为A列，Col2为B列，Col3 是虚拟的"包含行号的列.这些ColX"只要数据不是电子表格范围或引用，而是计算数组(例如 arrayformula() 结果或 { 数组表达式 }.

In the query statement, Col1 is column A, Col2 is column B, and Col3 is the "virtual" column that contains row numbers. These "ColX" type column references need to be used whenever the data is not a spreadsheet range or a reference, but a computed array such as an arrayformula() result or an { array expression }.

此处需要 arrayformula() 包装器来评估一系列单元格的 row() 函数，而不仅仅是左上角的单元格.

The arrayformula() wrapper is required here to evaluate the row() function over a range of cells rather than just the top left cell.

这篇关于使用 Google 电子表格查询语言获取行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！