スプレッドシート ARRAYFORMULAでの一括QUERY、さらに文字連結

Question

環境

スプレッドシート

以下2テーブル

色

品番	型番	Color
A123-R	A123	R
A123-W	A123	W
A123-B	A123	B
A124-R	A124	R
A245-W	A245	W
A245-B	A245	B
...

マスター

型番	種類
A123	=join(",",QUERY('色'!B2:C,"Select C Where B ='"&$A2&"'")
B124	R
C245	W,B
...

やりたいこと

色テーブルとマスターテーブルの2種類のテーブル構成です。
現在、マスターテーブルのB2の数式をオートフィルでB2以降に適応してB3,B4のような指定文字で区切られた形で取得しています。

型番、Color共に可変で数量もバラバラのため、どこかで変更があると全て(5万行ほど)再計算されるため苦しいです。

ARRAYFORMULAなどで一括で取得しようと
=ARRAYFORMULA(join(",",QUERY('色'!B2:C,"Select C Where B ='"&A2:A&"'"))
でやってみたのですがA2:Aの部分に配列が渡せていないみたいで一括で取得してくれません。

何か改善案、代替案等ありましたらご教授いただけると幸いです。
可能であれば新たな参照用のテーブルやセルを追加することなく実現したいです。
よろしくお願いします。

Comments

[sawa]

集約したマスタのテーブルで5万行ですか？結構大きいですね。元データとなる色の方はどれくらいの行数でしょうか？

[cali]

ありがとうございます。
色テーブルは40万くらいです。
中には色を持たないもの(現時点で色の区別がないもの)、50色ほどあるものと差が大きいのでベストな管理方法がわからずいます。(大半は2か3です)
とりあえずこちらで回答がつかないので参照用のテーブルを新たに作って対応しようかなと考えておりました。
常に500万セル制限に怯えながらですが。。。非常に不格好で情けないです。

Answer 1

40万行くらいのデータとのことで、Googleスプレッドシートの関数で処理するにはなかなか厳しそうですね。
とくに

さらに文字連結

この部分が厳しいです。
行毎に結合するような関数が無いので、ARRAYFORMULAと組み合わせは難しそうです。

回答の手前段階ですが、QueryとARRAYFORMULAを組み合わせた以下の式、今の色シートのリスト形式をマトリックス表示にスムーズに変換できるかを、作業用シートを追加して試していただけますか？

=ARRAYFORMULA(IF(QUERY('色'!B1:C,"select B,count(B) where B is not null group by B pivot C",1)="",,IF(ISNUMBER(QUERY('色'!B1:C,"select B,count(B) where B is not null group by B pivot C",1)),INDEX(QUERY('色'!B1:C,"select B,count(B) where B is not null group by B pivot C",1),1),QUERY('色'!B1:C,"select B,count(B) where B is not null group by B pivot C",1))))