運用エンジニアの道具箱【Excel & Google Spreadsheet 関数】

株式会社オズビジョンのユッコ (@terra_yucco) です。

一社目はともかく、二社目で自社サービスの開発・運用エンジニアになった身として、思った以上に使うなって思っているツールは断トツで Excel & Google Spreadsheet です。
ただ、プレゼン資料を作ったり、複雑な集計・分析を必要としない運用業務においても「最低限これは必要だろう」というレベルで道具として使えていないエンジニアが多いので、独断と偏見で、これだけ覚えておけばいいだろうっていう関数を列挙しました。何かの参考になれば！

※だいたいどれも Excel, Google Spreadsheet 両方で使えると思いますが、片方でしか使えないものもあるかと思います。

データ処理系

if

• 条件に合うものを抜き出すときのお約束
• e.g.) =if(条件, trueの場合の処理, falseの場合の処理)

and, or

• 上記の if を利用するときに、複数の条件を使うときのお供
• e.g.1) =if(and(条件A,条件B), 条件Aも条件Bも両方trueの場合の処理, どちらかもしくは両方がfalseの場合の処理)
• e.g.2) =if(or(条件A,条件B), 条件Aと条件Bいずれかもしくは両方trueの場合の処理, 両方がfalseの場合の処理)

count, counta, countif

• データ件数を数える関数。何かが入っていたら数えたり、条件に合致するものだけ数えたりできる
• e.g.1) =count(A:A)
  • A列で値が入力されているセルの数
• e.g.2) =countif(A:A, ">=20")
  • A列で20以上の値が入力されているセルの数

sum, sumif

• データ内容を合計する関数。条件に合致するものの合計を取ったりできる、ここからさらに割合出したりとかにも使う
• e.g.1 ) =sum(A:A)
  • A列の値合計を表示
• e.g.2) =sumif(A:A, ">=20", B:B)
  • A列が20以上のものについて、対応する(同じ行数の)のB列の値を合計して表示

vlookup

• 別シートからデータを引っ張ってくるのに利用
• これだけは絶対形式も覚えてしまうべき、さっと使えるかどうかでかなり効率違う
• e.g.) =vlookup(A1, B:D, 3, false)
  • B列からA1と同じ値を持つセルを探し、対応するD列の値を表示
  • 値が見つからない場合は #N/A などのエラー値になる
• ちなみに姉妹関数の hlookup は、通信系組み込み6年+LAMP Web系6年、で学習以外で利用したことがありません

isna

• vlookup と isna の組み合わせはよく使う、検索して見つからなかった場合などという形で if と組み合わせる
• e.g) =if(isna(vlookup(A1, B:D, 3, false)), "NOT FOUND", vlookup(A1, B:D, 3, false))
  • B列からA1と同じ値を持つセルを探し、対応するD列の値を表示
  • 値が見つからない場合は "NOT FOUND" と表示

unique

• Google Spreadsheet 限定かも
• 重複データを除いて、それぞれの件数・合計値を取得したい場合などに使う
• e.g.) =unique(A:A)
  • A列の値をユニークにして出力

right, left, mid

• 文字列の切り出し
• e.g.) =right(A1,5)
  • A1セルの最後5文字を表示する

見た目を整える系

trunc

• 結果を小数点以下切り捨てる
• e.g.) =trunc(A1)
  • A1セルの値を小数点以下で切り捨てて表示

text

• 日付のフォーマット整えてラベル付けするときなどに
• e.g.) =text(A1,"YYYYmm")
  • A1セルに入っている日付をYYYYmm形式で表示する、A1に2020/09/30が入ってたら202009

番外 (知っておいてほしい細かいこと)

& 文字列結合

セルの値同士を結合したり、vlookup のためにユニークなキーを複数カラムの組み合わせで作ったりするときによく使う。

セルの相対参照と絶対参照

これを理解しないと、コピペで数式がずれたり、使いまわせるものが使いまわせなくなったりすることも。
A1, $A1, A$1, $A$1 はそれぞれ違う。

セルの書式

日付が変な数字になる！などの大半はこれ。
桁数の大きい数字に E が入って壊れてる！なども大半これ。

まとめ

運用業務でよく使うなと感じた Excel & Google Spreadsheet の関数を列挙しました。
※勿論他にも便利な関数はたくさんありますし、知っていれば知っているほど選択肢が広がることは否定しません。
ですが、多分これだけ覚えておけば、入社してすぐに渡された CSV からでも、(定義さえわかれば) 条件に合わせた抽出がサッと出来るかと思います('ω')

エンジニアにとって Excel & Google Spreadsheet はツールなので、よく使う関数あたりはサッと覚えてしまって楽しましょう！