はじめに
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"形式で指定する必要があります。