ご訪問ありがとうございます。
Googleシートマスターのひろしです。
前回
QUERY関数 行を追加されても範囲を固定しておく方法(Gスプレッドシート)
technical.verybestcbp.com
今回は、とっても価値の高い方法をお伝えします。
これを知ることであなたは、
- QUERY関数でデータが追加されても範囲を自動的に広げられます。
- OFFSET,COUNTAの効果的な使い方がわかります。
なので、例えば。。
と言って上げてください。
ご質問
こんにちは。こちらの動画シリーズでQueryがあればVLOOKはいらないを理解している最中です。 VLOOKやQueryを使うと、抽出したい元データの範囲を指定しますが、誰かがその元データを更新すると1行増えて対象範囲が変わってしまうことがよくあります。 あらかじめ空白行含め多めのバッファをとっておく案もありますが、数行ならいいのですが膨大なデータだとあまり多すぎる範囲指定だとデータ処理が重くなるので、できれば自動でデータが入っている最後の行を判断して指定範囲が更新されるといいなと思っています。何かいい数式はありますでしょうか？ 例） シートと、別シート①がある。シート①のデータをひっぱってきたい。 =Query(`シート①`!$A$2:$K$10000, "SELECT A WHERE B='&C1&') C1セルのID番号に一致するデータを別シート①からとってくるため、上記の数式を使いvlook的にとってきている。 このとき、別シートの表データの範囲はA2:K10000 だが、誰かが更新すると、A2:K10001になってしまうが、数式は10001行目が入っていないため、検索結果N/Aとなるものが発生してしまう。 誰かが行を追加する場合に備えて、この対象範囲A2:K10000が自動でいい感じに必要行数まで増えるような設定としておきたい。（対象範囲A:K（全量）とすると、スプレッドシートが重いのかかたまってしまうためそれは避けたい）
まとめると
QUERYの範囲を自動的に拡大したい
動画はこちら
シートはこちら
※クリックするとシートが開きます。
ポイント
QUERYの範囲を自動的に拡大するためにCOUNTAとOFFSETを
使用します。
COUNTAで項目の数を取得する
COUNTA(値1, [値2, …])
例)
=COUNTA(‘1万データ’!$A$2:$A)
OFFSET関数でQUERYの範囲を作成する
OFFSET(セル参照, オフセット行, オフセット列, [高さ], [幅])
高さにCOUNTAで取得した値を設定します。
例)
=OFFSET(‘1万データ’!$A$2,0,0,COUNTA(‘1万データ’!$A$2:$A),11)
後は、上記をQUERYの「データ」に指定すればOKです。
QUERY(データ, クエリ, [見出し])
例)
=Query(OFFSET(‘1万データ’!$A$2,0,0,COUNTA(‘1万データ’!$A$2:$A),11),“SELECT A WHERE B='”&C2&“‘”)
以上です。
式は長いのですが、やっていることは単純です。
つぎ
QUERY関数 pivotの使用方法(Gスプレッドシート)
QUERY関数でpivotを使う方法がわかります。
ピボットテーブルを使用しなくても商品ごとの集計ができるようになります。
technical.verybestcbp.com