googleスプレッドシート query関数で抽出したデータの元の行数を取得する

4001 ワード
GoogleSpreadSheet query関数 GoogleSpreadSheet テキストリンク

はじめに

query関数でデータを抽出した際、抽出されたデータは「元データの何行目に存在するのか」を知りたい時があります。
例)GASを実行する際、query関数で処理対象のデータだけを抽出してから処理を行い、処理結果を元データに書き込むために元の行数が知りたいなど

元となる表に行数を表示する列を追加出来ればいいんですが、元の表は変更してはいけないということもあるため、query関数で抽出したデータの右列に元データの行数を書き出す数式を作りました。

元データと抽出イメージ

■シート1、元データ

A B C
1 A_1 B_1
2 A_2 B_2 対象
3 A_3 B_3
4 A_4 B_4 対象
5 A_5 B_5

■シート2、抽出データ
query関数で「C列が"対象"」のデータを抽出、元データの行数を最右列に書き出し

A B C D
1 A_2 B_2 対象 2
2 A_4 B_4 対象 4

数式

シート2のA1セルに以下の数式を入れているだけです。

=QUERY({'シート1'!A:C,ARRAYFORMULA(ROW('シート1'!A:A))},"WHERE Col3 = '対象'")

解説

query関数は1つの範囲のみならず、複数の範囲を結合して扱うことが出来ます。
なので、元データの範囲 'シート1'!A:Cと
行数を表示する範囲 ARRAYFORMULA(ROW('シート1'!A:A))を結合し、結合された範囲に対してquery処理をかけています。

■{'シート1'!A:C,ARRAYFORMULA(ROW('シート1'!A:A))}で結合された範囲

Col1 Col2 Col3 Col4
1 A_1 B_1 1
2 A_2 B_2 対象 2
3 A_3 B_3 3
4 A_4 B_4 対象 4
5 A_5 B_5 5

注意として範囲を結合すると、WHEREの列指定が"A","B"などが利用できず"Col1", "Col2"形式で指定する必要があります。