一年前くらいに書いた記事 かっこいいシートを作ろう！Googleスプレッドシートの便利技 - 初級編 - - How to make datas our friends の続き、中級編です。（もう書いてから1年経つのか...）
本記事では、シートがスマートになる便利な技をいくつか紹介します。
その①：条件に合致した行のみカウント
下記の表で、優先度ごとにToDoの数をカウントしてみましょう。
COUNTIF関数を使用し条件にマッチする行のみをカウントすることが可能です。COUNTIF関数の構文は以下の通りです。
=COUNTIF(範囲, 条件)
上記画像表C2の計算を行う場合はこのように記載します。
=COUNTIF(A1:A6,"A")
条件が複数ある場合はCOUNTIFS関数を使用しましょう。
その②：条件に合致したデータ同士を紐付ける
下画像内の表では、受注管理シートと商品金額が別々に管理されています。
受注管理シート内の金額列が空なのでVLOOKUP関数を使用し商品データ表から対応する商品の金額を引っ張ってみましょう。
VLOOKUP関数の構文は以下です。
=VLOOKUP(検索キー, 範囲, 指数, [並べ替え済み]) *[]は任意
検索キーには検索対象の商品名が記載されているセルを指定、範囲は商品データの範囲をまるっと指定、指数は表の何列目の値を返すか指定、今回のケースでは金額が二列目にあるので指数は 2 になります。
今回はシート内D3に下記のように記載し、D9までコピーします。
=VLOOKUP(C3,F$2:G$4,2) ※コピーするとき値がスライドしちゃうので$で値固定を忘れずに
無事に金額表示されました。
その③：条件に合致した行の値のみSUMする
最初に紹介したCOUNTIF関数のSUM版です。上で使用した受注管理シートから状況が発送済みになっている商品Aの合計金額を売上として計算しましょう。
SUMIFS関数の構文は以下です。
=SUMIFS(合計範囲, 条件範囲1, 条件1, [条件範囲2, ...], [条件2, ...])
合計範囲に合計する値が入っている列を、条件範囲には条件にあたる「状況」「商品名」が入っている列を指定し、更にそれぞれの列ごとに条件を指定します。
今回は以下のように関数を記載します。
=SUMIFS(D2:D9,B2:B9,"発送済み",C2:C9,"A")
Pivot機能を使うのもありですが、自由度が高いので行列が多い複雑なデータを扱う際に便利です。
その④：関数エラー時の表示を整えよう
受注管理シートに先程組み込んだVLOOKUP関数のオプション[並べ替え済み]をFALSEに変更し、商品データの商品名Bの行を削除するとD列にエラーが発生します（#N/Aとなっている部分）。
これはVLOOKUP関数が商品Bの金額を商品データ側から見つけられないためにエラーを返している状態です。（[並べ替え済み]でFALSEを指定すると対応するデータがないとエラーを返す、TRUEを指定すると最も近い値を返す、何も指定しないとデフォルトでTRUEが設定される）
今回はIFERROR関数を使用し、もしエラーが出た場合「商品データに値がありません」と表示されるようにしましょう。
IFERROR関数の構文は以下です。
=IFERROR(値, [エラー値])
値に関数をまるっと入れて、[エラー値]にエラー時の挙動を記載するので、表示したい文字列を記載しましょう。
今回はこんな感じにしました。
=IFERROR(VLOOKUP(C3,F$2:G$4,2,FALSE),"商品データに値がありません")
見た目はさておき、エラーが文字列に置き換わりました。
その⑤：ARRAYFORMULA関数で無駄なコピペを減らそう
例えば上の受注管理シートが常にデータが追加されていくような類ものだとします。その場合、データが追加される度にD列に記載した関数を追加した行までコピーしなくてはいけなくなります。
ARRAYFORMULA関数を使用し、行が追加された際に自動でD列の関数が適用されるようにしましょう。
ARRAYFORMULA関数の構文が以下です。
=ARRAYFORMULA(配列数式)
配列数式...なんのこっちゃですよね笑
C3:C9みたいな複数の値を含むものが配列ですね。
とにかく関数を書いてみましょう。
=ARRAYFORMULA(IFERROR(VLOOKUP(C3:C,F$2:G$4,2,FALSE),"商品データに値がありません"))
上で書いたD列の関数をARRAYFORMULA()で囲ってあげて、VLOOKUP関数の検索キーをC3から配列であるC3:Cに書き換えてあげます。
これで、ARRAYFORMULA関数がC3:CまでひたすらにVLOOK関数の計算を繰り返してくれます。
D3にしか関数を書いていないのに、下まで関数を適用し計算してくれていますね。
しかし、困ったことに範囲をC3:Cで指定したので、下の方まで計算をおこないエラーになってしまったため、IFERROR関数が「商品データに値がありません」と表示する挙動をしてしまっています。
IF関数を使って関数の暴走を沈めましょう。
=ARRAYFORMULA(IF(C3:C="","",IFERROR(VLOOKUP(C3:C,F$2:G$4,2,FALSE),"商品データに値がありません")))
IF文でC3:Cにデータがない(空文字"")なら空文字を返すように記載してあげます。簡単にIF分の要素だけ書くとこんな感じ。
=IF(C列が空文字,TRUE=空文字を返す,FALSE=関数を実行)
無事、暴走が収まりました。
力尽きたので終了
力尽きてきたので、今回はここで終了します。笑
今回紹介させていただいた関数は普段僕も業務で頻繁に使うものです。
スプレッドシート上で大量のデータを扱うことが多い人は覚えておいて損はない関数だと思います。
スプレッドシートの中級編は第二弾をいずれ書こうかなと思っています。ぷらす、上級編としてGASを使った自動化等記載予定でいます。
