SpreadSheet OFFSET関数を使って複数行抽出したい

前提

SpreadSheetを勉強しているものです。
VBAなどを使わず、SpreadSheetの関数のみで実現したいです。

###実現したいこと

下記項目の「検索フォーム」のD列３〜５行に<タイプ>を入力すると、
「商品表」より該当する<タイプ>を検索し、
「検索フォーム」のE列に、該当する<品番>が全てカンマつなぎで出力される関数を組んでます。

=TEXTJOIN(",",TRUE,TRANSPOSE(OFFSET(A1,MATCH(D3,A:A,0)-1,1,COUNTIF(A:A,D3),1)))

ただ、現状では「検索フォーム」のE列３〜５行に、上記の関数をそれぞれ入力して実現している状態です。
この関数を、「検索フォーム」のE列３行目だけに入力し、残りの４〜５行にも反映させられるように組み直したいです。

わかりづらい文章で大変申し訳ございませんが、
もし、代わりになる関数や、抽出できる別の方法をお分かりになる方がおられましたら、
ご教示のほど、よろしくお願い申し上げます。

検索フォーム

[D列] 　　 [E列]
3 コーヒー　TBR-30,TBR-50,ADA-20R,ADA-30R,ADA-50R,YDA-50R
4 ドリップ KDB-20N,KDB-25N,KDB-30N,KDB-40N,KDB-50N
5 ミルクM KCI-20N,KCI-30N,KCI-35N,KCI-40N

商品表

[A列]　 [B列]
<タイプ>　　　　 <品番>
1 コーヒー TBR-30
2 コーヒー TBR-50
3 コーヒー ADA-20R
4 コーヒー ADA-30R
5 コーヒー ADA-50R
6 コーヒー YDA-50R
7 ドリップ KDB-20N
8 ドリップ KDB-25N
9 ドリップ KDB-30N
10 ドリップ KDB-40N
11 ドリップ KDB-50N
12 ミルクM KCI-20N
13 ミルクM KCI-30N
14 ミルクM KCI-35N
15 ミルクM KCI-40N

試したこと

[D列] 　 [E列]　 [F列]
3 コーヒー 1　 TBR-30,TBR-50,ADA-20R,ADA-30R,ADA-50R,YDA-50R
4 ドリップ 7 KDB-20N,KDB-25N,KDB-30N,KDB-40N,KDB-50N
5 ミルクM 12 KCI-20N,KCI-30N,KCI-35N,KCI-40N

「検索フォーム」E列に下記関数を組み、行数を取得した上で

=ARRAYFORMULA(MATCH(D3:D5,A:A,0)-1)

F列の関数にARRAYFORMULA関数を加えてみました。

=TEXTJOIN(",",TRUE,TRANSPOSE(ARRAYFORMULA(OFFSET(A1,E3:E5,1,COUNTIF(A:A,D3),1))))

ただ、OFFSET関数にARRAYFORMULA関数が使えないことを知り、断念いたしました。

行動規範の内容に同意します

回答1件

ベストアンサー

ARRAYFORMULA関数を使いたいとのことなので、Excelでなく Google スプレッドシートの質問と思われます。
なので質問のタグが不適切で、Google スプレッドシートのタグを付けるべきです。

質問の内容ですが、OFFSETもそうですが、JOINやTEXTJOINもARRAYFORMULAで思うように動かないので、断念することをお勧めします。

こういう条件に一致するものを全て抽出というケースでは、スプレッドシートならFILTER関数がベストなので E3に入れる式としては

=JOIN(",",FILTER($B$1:$B,$A$1:$A=D3))

がスッキリしてて良いかと。あとは、これをE4以下にコピペが一番簡単です。
FILTERも残念ながら ARRAYFORMULA 使えません。

どーしても、無理やりにでも ARRAYFORMULAでいきたいなら、

前提条件
・商品表のA列が昇順になっている

としたうえで、

=ARRAYFORMULA(MID(TEXTJOIN(",",TRUE,$B$1:$B),IF(MATCH(D3:D5,SORT($A$1:$A,1,TRUE),0)=1,0,FIND("_",SUBSTITUTE(TEXTJOIN(",",TRUE,$B$1:$B),",","_",MATCH(D3:D5,SORT($A$1:$A,1,TRUE),0)),1))+1,IF(COUNTA($A$1:$A)-MATCH(D3:D5,SORT($A$1:$A,1,FALSE),0)+1=COUNTA($A$1:$A),LEN(TEXTJOIN(",",TRUE,$B$1:$B))+1,FIND("_",SUBSTITUTE(TEXTJOIN(",",TRUE,$B$1:$B),",","_",COUNTA($A$1:$A)-MATCH(D3:D5,SORT($A$1:$A,1,FALSE),0)+1),1))-IF(MATCH(D3:D5,SORT($A$1:$A,1,TRUE),0)=1,0,FIND("_",SUBSTITUTE(TEXTJOIN(",",TRUE,$B$1:$B),",","_",MATCH(D3:D5,SORT($A$1:$A,1,TRUE),0)),1))-1))

という式でいけましたが、実用的じゃないですね。頭の体操というか時間つぶしに作るような式です。

簡単に中身を説明すると
・先にTEXTJOINを「,」で結合させたものを用意して、
・商品表の A列を昇順、降順に切り替えることで検索ヒットする最初と最後の行を見つけ
・SUBSSTITUTEで〇番目の「,」を「_(アンダーバー)」に置き換えた上で、FINDで位置を見つけ
・IFで1番目と最後の場合を調整、+-αで位置を調整した上で、
・MIDで該当部分を抽出
・これを ARRAYFORMULA でとりあえず D3～D5 の検索キーに適用

動きは悪そうなんで、やはり素直にFILTER使うのをお勧めします。

もしくは、この手の処理はスプレッドシートなら GoogleAppsScript（ExcelでいうとこのVBAみたいなもの）でやった方が、かなり簡単に処理できるので、敷居が高そうに感じるかもですが試してみても良いかと。

投稿2020/04/18 00:50

編集2020/04/18 01:06