株式会社クイックのWebサービス開発blog

HAPPYなエンジニア&デザイナーのブログです

あなたのスプシがもっとスマートに。ARRAYFORMULA編

こんにちは、ねこです。
ここ半年くらいスプレッドシート(※以下スプシ)で設計書を作っては投げ、データをまとめては投げておりまして、 日々その便利さに感動しもうエクセルには戻れない体になってしまいました。

さてスプシに慣れてくるとあちこちで便利だ便利だと言われているのを見かける伝説の関数、ARRAYFORMULAさん…*1
私もつい最近やっと使い方がわかったので、sanameko先生のINSERT文作成スプシを改良する形で紹介したいと思います!

aimstogeek.hatenablog.com

ARRAYFORMULAさんとは

公式ページには

配列数式から返された値を複数行または複数列に表示したり、非配列関数で配列を使用したりすることができます。
ARRAYFORMULA - ドキュメント エディタ ヘルプ

とありますが正直なんのこっちゃって話なので次に行きましょう。
数式を各行で使いたいときってオートフィルで複製しますよね。
ARRAYFORMULAさんは適用させたい範囲を指定してあげればそれをやってくれる関数です。
しかもこの方だけ入力するためのショートカットキー付きです。*2

・数式の編集中に Ctrl+Shift+Enter を押すと、数式の先頭に ARRAYFORMULA( を追加できます。
ARRAYFORMULA - ドキュメント エディタ ヘルプ

""格""を感じる……

ARRAYFORMULAさんの使い方

ARRAYFORMULAさんの使い方はいろんな人がもう記事にしてるんですが
わかりやすいのは下記の手順だと思います。

  1. いつもどおり一行用の数式を書く
  2. Ctrl+Shift+Enterする(ARRAYFORMULAさんの追加)
  3. 数式内のセル範囲を適用したいとこまで広げる

sanameko先生の式で言うと、作業用のF4セルにいれるこちらの数式

=SUBSTITUTE(VLOOKUP(A$2,$M:$N,2,false),"@@@",A4)

VLOOKUPとSUBSTITUTEの適用したい範囲は下記図になりますね。 f:id:aimstogeek:20181019155806p:plain

というわけでそのとおりに広げてあげるだけ!

A$2 → A$2:E$2
A4 → A4:E6

=ARRAYFORMULA(SUBSTITUTE(VLOOKUP(A$2:E$2,$M:$N,2,false),"@@@",A4:E6))

f:id:aimstogeek:20181019163239g:plain
きゃーかんたん!

これで100行とか1000行とかオートフィルのためにスクロールしなくても良いのです。

応用編

このままINSERT文生成セルも適用しちゃいましょう。
と言いたいところですがここでつまずきポイントがあります。
JOINさん*3です。

何がアレかっていうとこの関数の指定方法、単一セルじゃないんですよね。
既に行または列指定なのです。
当然ですがARRAYFORMULAさんをつけても複数行指定すると単一行にしなさいと怒られます。

f:id:aimstogeek:20181019161120p:plain
ごめんなさい…

こういうときはとりあえずTRUEになるIF文を挟みます。

=IF(A4<>"", "INSERT INTO "&$A$1&" ("&JOIN(",",$A$3:$E$3)&") VALUES ("&JOIN(",",$F4:$J4)&");"), "")

id列が空じゃなかったら、INSERT文を作成」ってしておきました。
これをARRAYFORMULAさんで包んでIF文の範囲を拡大します。

A4 → A4:A

=ARRAYFORMULA( IF(A4:A<>"", "INSERT INTO "&$A$1&" ("&JOIN(",",$A$3:$E$3)&") VALUES ("&JOIN(",",$F4:$J4)&");"), "") )

範囲をA4以下の全行(A4:A)にしたことで、行を追加したときも数式の変更なくINSERT文が生成されます。

f:id:aimstogeek:20181019164844g:plain
これで数式コピーは卒業しよう

まとめ

公式ページを見ても使い方がいまいちわからないARRAYFORMULAさん、
使うコツは「一度いつもどおり数式を作ってからARRAYFORMULA、範囲を拡大」です。
ぜひ覚えて使ってみてくださいね😉✨✨


\\ 『明日のはたらくを創る』仲間を募集中!! // 919.jp

*1:敬意を表して本記事ではさん付けします。

*2:ARRAYFORMULAって打つのめんどいですしね。

*3:すごくお世話になっているのでさん付けしています。