Googleスプレッドシート index関数の引数(行数)指定が上手くいかない

Question

query関数で取得したリストH列の商品間に、空白行を2行挿入して、
K列のようなゴールを目指しているのですが、
現状L列のように、商品Aが続いてしまいます。

L2セル
=ArrayFormula(if(I2:I=2,index($H$2:$H,J$2:J),""))

どこが問題なのでしょうか。

なお、参考までに
I2セル
=ArrayFormula(mod(row(I2:I),3))

J2セル
=ArrayFormula(quotient(row(J2:J),3)+1)

です。

加えて、

今回のような、query関数で取得したリストの行間に、空白行を◯行挿入するようなことをしたい場合、
query関数内で処理できたり、もっとスマートな方法はありますでしょうか？

Comments

[sawa]

>query関数で取得したリストH列
このquery式の情報をいただければ他の解決策も提示できる可能性があります。

[SKIYO]

検討くださりありがとうございます！

=query(importrange(〜), "where Col1 is not null", 1)
というような、シンプルなものです

Answer 1

質問に掲載したシートの作業列をいかすなら、qnoirさんが既に回答されている VLOOKUPをArrayformulaと組み合わせる方法が良いです。

別の方法も探されているようなので、スマートかどうかわかりませんが違う式を回答しておきます。

○個飛ばしを出力する際 FLATTENという関数を応用する方法があります。
質問のH列を使った場合、2個飛ばしならL2セルに以下の式を入れます。
たぶんこれが一番短い式です。

=ARRAYFORMULA(FLATTEN({H2:H,IF(H2:H="",,{"",""})}))

Query内で実現する方法はなさそうなんで、Hの作業列無でやる場合は、結局上記の式のH2:H の部分に タイトル行を除いた Query式をはめ込むしかないです。
※タイトル行無しは importrangeの範囲開始位置からタイトル行除いて queryの最後の1不要。

Queryが2回出てくるの嫌な場合は、以下のような書き方もできますが微妙な気がします。。

=FLATTEN(ARRAYFORMULA(SPLIT(query(importrange(〜), "where Col1 is not null")&REPT(",",2),",",,FALSE)))

Comments

[SKIYO]

FLATTEN　また一つ、私にとって有意義な関数を知ることができました！
別の式まで考えてくださりありがとうございます！

Answer 2

Sequence関数で行をインデックスとした表を作りvlookupで値を参照します。

=ArrayFormula(if(I2:I=2,vlookup(J2:J, {sequence(7), $H$2:$H$8 }, 2, 0),""))

Comments

[SKIYO]

早速回答ありがとうございます！
ちなみに、もしご存知であればでいいのですが、
・query呼び出しと同時に、query関数内の記述にて、空白行を挿入することはできないでしょうか？
・今回の、index関数での行数指定が上手くいかなかった理由は何でしょうか？

[退会済みユーザー]

> query呼び出しと同時に、query関数内の記述にて、空白行を挿入することはできないでしょうか？
→ 不可能ではないかもしれませんが、申し訳ないですが、私の頭ではぱっと良いやり方が思いつかないです。
stackoverflow等を調べてみましたが、該当するものを見つけることはできませんでした。
元データと一緒に空白データも結果として取得してきて、元データと空白データそれぞれにインデックスをつけ、MOD(剰余)で織り交ぜる、とかでしょうか。
そもそもQUERY関数は、データの表示形式を整えるためのものではなく、主にデータの抽出・集計・並べ替え等に使うものという認識ですので、データを抽出等を行った上でさらに一定間隔で行を空けるという用途に使おうとすると、かなりトリッキーなやり方になりそうです。

> 今回の、index関数での行数指定が上手くいかなかった理由は何でしょうか？
→INDEX関数は、IFやVLOOKUP等と異なり、ARRAYFORMULAの中で使用したときの挙動に制限があります。
今回「=ArrayFormula(if(I2:I=2,index($H$2:$H,J$2:J),""))」の場合、
１．ARRAYFORMULA中に配置したINDEX関数の第二引数「J2:J」は、展開されず、どの行でも「J2」としか解釈されません。
２．結果、H2:Hの範囲(=H2行目以降）から「J2セルの値」行目すなわち「1」行目が常に選ばれることになります。
したがって、INDEX($H$2:H,1)となり、この計算式の結果は「A」という単一値になります。
３．IFの引数の方は展開されるため、各行でI列が「２」の場合、K列に「A」という値が表示されることになります。

簡単に言えば、「INDEX関数は、ARRAYFORMULA関数の中ではIFやVLOOKUPと同じようには扱えません」ということになります。

[SKIYO]

ご丁寧な回答ありがとうございます！

2つの回答とも、とても参考になりました！！