2021年以降を中心にExcelに追加された機能・関数の中で特に実務に役立つものについて解説しています。基本的な使い方や他の機能・関数と組み合わせた応用テクニックも紹介しています。
コンサルタントとして様々なプロジェクトに従事してきた著者が、実務において実際に活用したExcelのテクニックを紹介します。著者自身が「あれ？どうすればいいんだろう」と悩んだ部分を中心にピックアップしています。
点数に応じてAランク、Bランク、Cランクというようにランク付けを行う場合、IFS関数（またはIF関数）よりもVLOOKUP関数の方が数式がシンプルになります。
IFS関数を使う方法
たとえば、以下のようなテストの点数に基づき、それぞれのランク分けを行います。
ランク分けのルールとしては以下の通りです。
- 90点以上：Aランク
- 70点以上90点未満：Bランク
- 40点以上70点未満：Cランク
- 0点以上40点未満：Dランク
これをIFS関数を使って表すと以下のようになります。
=IFS(B3>=90,”A”,B3>=70,”B”,B3>=40,”C”,B3>=0,”D”)
そこまで数式が複雑というわけではありませんが、一つ一つの論理式で参照・入力を繰り返していくのは手間になります。
数式を確定すれば、該当のランクが表示されます。
数式を下にコピーすれば、すべてのランクを表示させることができます。
VLOOKUP関数を使う方法
今度はVLOOKUP関数を使ってランク分けを行います。具体的には、VLOOKUP関数の近似一致という検索方法を使用します。
事前の準備として、点数のランク分けを行う基準の点数と該当のランクを表で整理します。
ポイントとしては、以下の2点です。
- 基準となる点数はランクの左に記載
- 基準となる数値を昇順（小さい順）に記載
これはいずれもVLOOKUP関数の性質によるものです。1点目に関しては、VLOOKUP関数では検索値を左端列から検索するため、検索に使用する点数のデータを左端に配置します。2点目に関しては、VLOOKUP関数の近似一致では、参照範囲を上から順番に検索し、検索値より大きい値を最初に見つけた時点で、その手前の行（検索値以下の最大値の行）のデータを返すからです。
| VLOOKUP(検索値,範囲,列番号,[検索方法]) |
|---|
| 検索値で指定した値を、範囲の左端列から検索する。検索値と一致するデータの左から列番号目の値を抽出し表示する。検索方法に「FALSE」を指定すれば、完全に一致するデータを検索する。「TRUE」を指定すれば、「～以上」と数値を区切って該当する区分を特定する。 |
具体的にVLOOKUP関数で数式を記載すると以下のようになります。検索値には、点数を指定し、範囲にはランク分け表を絶対参照で指定します。今回は表の2列目のA、B、C、Dというランクを表示させたいので、2列目の「2」を指定します。最後に検索方法を「TRUE」の近似一致を指定します。
=VLOOKUP(E3,$A$3:$B$6,2,TRUE)
数式を確定すれば、該当のランクが表示されます。
数式を下にコピーすれば、すべてのランクを表示させることができます。
この方法であれば、あらかじめ表を準備するだけでシンプルな数式でランク分けを行うことができます。1つ1つの基準点を参照・入力する必要もないのでミスを減らすこともできます。
コメント