No6_関数技工編（ARRAYFORMULA関数)｜同じ数式は一回しか書かない

こんばんわ。
今日は第6回ということで関数技工編をやります。
技工編多くない？という方もいるかも知れませんが、効率よく勉強するために超重要というか、知っているだけで3倍くらい作業速度があがり、シートのメンテナンスが楽になります。

【今日の課題】
同じ数式を何個も書かない。

0.同じ数式を何個も書かないとは？

以前の記事「No4_LOOKUP関数関連｜「〇〇に一致するデータを探してくる」ができる関数たち」の中でVLOOKUP関数を説明しました。その中でもしも、E列で検索したい文字が一個だけじゃなくて、複数個だったら、何個も何個も同じ数式を書くしかないのか。
下の画像みたいに。。。。

これはおすすめしません。
たとえば、営業リストのように検索したいメンバーの数が、増えたり減ったりする場合は毎回書き直さないと行けないですよね。
ということで便利なARRAYFORMULA関数を勉強しましょう。

1.ARRAYFORMULA関数とは

ARRAYFORMULAは「array=配列」+「formula=数式」という意味で、配列は高校の数学で勉強するやつですね。簡単に言うと一個の数字じゃなくて、複数の数字のまとまりという意味です。
見たほうが早いので、写真貼ります。

わかりますでしょうか？
数式はF2のセルにしか書いていないのに、F8までVLOOKUPした値が入るんですよね。不思議。ちょっと普通の数式とは書き方が違うので説明していきます。

2.VLOOKUP関数と一緒に使ってみる

=ArrayFormula(VLOOKUP(E2:E8,$A$2:$C$8,3,false))

まずVLOOKUPの中身「VLOOKUP(E2:E8,$A$2:$C$8,3,false)」となっていますね。ポイントはいつもならE2とだけする場所がE2:E8となっています。これはARRAYFORMULAを使うときの特有なものなのですが、自分が数式を反映させたい範囲を指定しています。

最初に一個づつ数式を書いたときは、E8まで数式書きましたよね。
なのでVLOOKUPの数式をE2からE8まで書きたいので、E2:E8と書きます。
そして、この数式を最後にARRAYFOEMULA()でくくれば完成となります。
※ちなみにARRAYFOEMULA関数は[Shift+⌘+Enter]のショートカットがあるので、ぜひ使ってください。

少し手を加えましょう。
もし、E列の行数が可変（増えたり減ったりする）な時って範囲（今回でいうとE2:E8）を指定し直すのは大変ですよね。また、A列からC列も可変だともっと大変。
下のスクショみたいに、9行目以下に数式が伸びていません。

こういうときにやること
・検索キーの指定はE2:EでE2から下の行全部を指定
・範囲の指定もA2からCの下まで指定
・IF文と組み合わせて、Eの値があるところまで全行を計算する
の三点セットです。見たほうが早いので、スクショ載せます。
まずは検索キーと範囲を変更します

次にIF文でE列が空白ではない(E2:E<>"")と場合にVLOOKUPをするというふうに変更します。

どうでしょうか。
これで、18行目以下は検索されなくなりました。もちろんE列の長さが変わってもこれで数式を変更することなくほしい行数分だけ数式が自動で反映されています。

あとはできればIFERROR()を含めて、万が一果物以外をE列に書かれた場合の処理をできるともっとGOOD

ちなみに横長だと見にくいので、こんな感じの構造になっています。ちょっといきなり難しくなったかもです。そこまでしなくても業務はできるのでご安心を

=
ArrayFormula(
  IFERROR(
    IF(E2:E<>"",
      VLOOKUP(E2:E,$A$2:$C,3,false),
      ""
    ),
    "果物じゃない"
  )
)

3.ARRAYFORMULA関数でできないこと

ARRAYFORMULA関数は便利ですが、できないこともあります。
やっててよくあるのが、SUM関数関連と一緒に使えません。※SUMIFはできるかも

こんな感じになります。
これはSUMIFS関数自体が〇〇から△△までを足すみたいなことをしており、配列を処理するものになっているからかと思います。※詳しくは今度やりますね。

とはいえこんな感じで、ARRAYFORMULA関数を使うことで、数式を何個も書かなくていいし、検索したい対象が増えたときにも自動で下まで追従してくれるので、メンテナンスがいらなくなります。
ぜひ使っていきましょう。

あとがき
書きたい記事があいっぱいあるので、必ず一日1記事なんてやりますが、こういうのって良くないんですよね。
自分で営業管理とか育成とかしていて思うのは、絶対にやると誓っていいのは「やること自体に外的要因がなくて、自分が必ずコントロールできるもの」だなと思います。

例えば「必ず一文字でもいいからnoteの下書きをすすめる」みたいな。
そうしないと頑張って書いたけど、最後まで書ききれなかったときとかに目標到達せず。。。みたいになって、マインドキルしてしまいます。

目標の立て方って色々なところで、みんな書くけど、大切なのは「目標が達成できること」それ一つしかないので、人がなんと言おうと、自分が一番満足できるやり方いいと思います。

別記事で目標建てるときに重要視していることとか書こうかな。
ちなみに私の仕事の目標はかなり自分にストイックな目標建てるので、ちょっと無理ゲーなところありますが、それくらいしないとワーカーホリックなんで、逆に落ち着かない。暇になる方が怖くなる。
いいか悪いかは別として、満足できる目標へゆっくりでいいので、一歩ずつ進みましょ。