【Excel初心者に向けて】IT業界で仕事上でよく使うExcelの関数まとめ

私は現在、sler業界でSEとして働いておりますが、多くの人はすでにご存じの通り、プログラミングの時間よりも資料を作る時間のほうが長いので、仕事上のほとんどはExcelを利用しています。

その中でも関数を使う場面は、テストの段階である場合が多いので、データの整合性チェック（予想した値や前回のログとの差の確認）が主な使用方法となっています。

この記事を書く際に「Excelの関数一覧」のようなもので何があったか確認しながら書いていましたが、484個とかあるそうですね。

さすがにこれだけあると覚えるのも大変そうですし、ほぼ忘れるので時間の無駄ですよね。

関数は一度使うとあまり忘れないものではありますが、最初はプログラミングと同じく知りたくなったら調べる程度でいいとは思っています。

ですが、元から便利な方法を知らないと効率の悪い方法を記事で見つけた時に知らずに使っていると痛い目を見そうなので、実際に職場で使っている関数を書いてみようと思いました。

これが100%効率がいい関数かといわれるとわかりませんが、知っていることが増えればそれぞれの判断で選択できると思うので、Excel関数駆け出しの方に向けてさらっと紹介していこうと思います。

基本

AND

指定した条件がすべて正しかった場合にセルにTRUEを表示します。

OR

指定した条件のどれか一つが正しかった場合にセルにTRUEを表示します。

TEXT

値を文字列に変換します。

整合性チェックの観点で以下のような場面が分かりやすいのですが、

・セル「A2」の「1」は右揃えになっているため、「数値」
・セル「A3」の「1」は左揃えになっているため、「文字列」

上記のAND関数では「表示形式」と呼ばれる「文字列」や「数値」などの形式が違うため、「TRUE」になってくれません。

そうなったときに、「A2」に入っていた「1」の値を「文字列」に変更して、「表示形式」を合わせてあげることで、「TRUE」にすることができます。

LEN

データの桁数を確認します。

「LEN」はこの次に紹介する「LEFT/RIGHT」などで利用したりする場合もあります。

※データの比較をする際に見かけ上、全く同じデータであってもデータが一致しないということがあります。

原因は比較するデータの取得方法がそれぞれ違う場合などに、片方に余計な制御コードが含まれていたりするのですが、

確認したいデータの桁数を確認すると結果に差が出たりするので、そのような確認にも使えます。

LEFT/RIGHT

LEFT=左から指定文字数分だけ抜き出します。

RIGHT=右から指定文字数分だけ抜き出します。

「LEFT」,「RIGHT」は使い方や用途が同じなので、一気に取り上げます。

データ同士の比較の際に、「左から2桁目だけが合っていればいい」という場合や「連結させたデータの分解」などに使います。

左から2桁目だけが合っていればいい場合

※元のデータが数値の場合、「LEFT」を利用すると勝手に文字列に変換されてしまうので、もう片方のデータも「TEXT」で文字列に変換する必要があります。

連結させたデータの分解(桁数が決まっている場合)

※上記の方法で左右に分解する場合はデータ全体の桁数が「10」と決まってなくてはいけないということになるので、

桁数が決まっていない場合は「LEN」関数でセルの桁数を数える必要があります。

連結させたデータの分解(桁数が決まっていない場合)

※実際に計算速度を測ったことがないので、わかりませんが、桁数が決まっていれば、「LEFT」、「RIGHT」のみ方が計算の負荷がかかりずらそうなので、二つ紹介しました。

比較

EXACT

データを比較し、一致すれば「TRUE」を一致しなければ「FALSE」を表示する

正直普通に「セル1=セル2」とやってもいい気はしますが、関数が長くなってしまった時に、比較対象が分かりづらくなってしまうということを防ぐためだと思っています。

なぜか値が一致しなかったときになぜ一致していないのかを中身を見たときにわかるのは便利ですね。

IF

条件が一致した場合、一致しなかった場合それぞれで表示するデータを変える

IFERROR

処理結果がエラーとなる場合の処理を追加する

計算結果やデータの探索結果がエラーとなり「#N/A」などのエラーが表示されそうなとき、それで資料として見づらくなってしまったりする場合にエラーの代わりに表示するデータを変えることができます。

EXCELのデータをそのまま他の処理に利用したりする場合も少なくないので、通常は最終的に空文字("")などに変更したりします。

置換

データを比較する際に片方に余計な文字や空白が入っていた場合に一致させるために利用します。

※ただし、置換はデータを変えてしまっているため、最終手段的な所がありますので、使う際は周りに聞いてから利用したほうがいいと思います。

SUBSTITUTE

指定の文字列を他の文字に置換する

TRIM

先頭と末尾の空白を削除し、文字列中の空白が連続している場合は一つにまとめる

探索

データがEXCEL上のどこにあるのかを明示したり、探索したデータを参照という形で取得する際に使います。

MATCH

指定した文字列が指定した範囲内の何行目にあるかを取得する

※検索は上から下に行うため、上記の例では「"〇"」が二つあればより上にある行数を表示します。

VLOOKUP

指定した文字列を元に指定した範囲を縦方向に検索し、データを参照する

COUNTIF

指定範囲内に指定したデータがいくつあるかを表示する

※画像を見てわかる通り、検索対象は文字列であっても数値であってもカウントしてくれるようです。

その他

INDIRECT

セルを間接参照する

表を作る際などに割と使い方が分かっていると使い方の幅が広がります。

まとめ

本当に必要最低限しか書きませんでしたが、逆にこれらの関数さえわかってれば困ることがないです。

効率面ではVLOOKUPよりもMACH&INDEXを利用したほうが早いとされていますが、見やすさの面でVLOOKUPの方がいいかもしれません。

本当に処理が重く感じるときに試してみてもいいかもしれないですね。