自動化厨のプログラミングメモブログ │ CODE:LIFE

Python/ExcelVBA/JavaScript/Raspberry Piなどで色んなことを自動化

GoogleスプレッドシートのQUERY関数でCONCATが使えない件

Googleスプレッドシートには QUERY関数というSQLライクに指定範囲から情報を取得できる超便利な関数があります。2つのデータテーブルを結合したり、列を入れ替えたり、集計したり、WHERE句でフィルタしたりととにかく便利。

活用方法はいつもお世話になっているブログ「いつも隣にITのお仕事」さんがとてもわかり易いのでこちらを参照ください。

tonari-it.com

今回やりたかったこと

以下のように2つのフィールドの内容を結合して一つのフィールドにしたかったんです。

f:id:maru0014:20200918234707p:plain

SQLっぽく書けるんだしCONCATで一発でしょー。と思いましたがダメでした。

CONCAT は使えないらしい。Google先生できれば実装していただけると嬉しいです。

f:id:maru0014:20200918234716p:plain

ちょっとトリッキーなやり方

「スプレッドシート QUERY CONCAT」で検索してたどり着いたStackOverflowには「CANCAT」はありませんとのコメントとともに transpose 関数で行列入れ替えをして結合処理をしたあとに再度 transpose で元に戻して、余分に入った半角スペースを SUBSTITUTEでもとに戻すという配列数式。

なんかもうちょっとスマートなやり方無いのかなと思いつつもWHERE句なども使いつつとなると仕方ないのかなと思います。

=ARRAYFORMULA(
    SUBSTITUTE(
        trim(
            transpose(
                query(
                    transpose(
                        QUERY(A:C, "select B, C where A>3")
                        ), , 
                        COLUMNS(
                            QUERY(A:C, "select B, C where A>3")
                        )
                )
            )
        ), " ", ""
    )
)

f:id:maru0014:20200918234727p:plain

WHEREとか使わない、ただ配列参照で結合したい場合

ARRAYFORMULA で配列同士を&で結合させるだけ。これなら簡単ですね。

=ARRAYFORMULA(B3:B & C3:C)

f:id:maru0014:20200918234736p:plain

中間テーブルを作ることになっても良い場合

前述の配列同士を結合したものを中間テーブルとして、最終的に中間テーブルを再度QUERY関数で参照するという手もありますね。後々のメンテナンスも考えるとこのやり方がいいのかなぁ。

f:id:maru0014:20200918234748p:plain

参考になったページ

stackoverflow.com

aimstogeek.hatenablog.com

脱線してたまたま見つけたけど便利そうな情報

これいい。GAS使わずになんちゃってCSV化できる。

monoblog.jp