2021年以降を中心にExcelに追加された機能・関数の中で特に実務に役立つものについて解説しています。基本的な使い方や他の機能・関数と組み合わせた応用テクニックも紹介しています。
コンサルタントとして様々なプロジェクトに従事してきた著者が、実務において実際に活用したExcelのテクニックを紹介します。著者自身が「あれ？どうすればいいんだろう」と悩んだ部分を中心にピックアップしています。
Excelで商品の注文量などを入力する際、桁数を間違えたり、数字を間違えたりといったミスが発生する場合があります。そのようなミスが発生しないように入力規則でエラーメッセージを設定しておくと便利です。
指定した範囲外の数値の時エラーメッセージを表示
指定した範囲外の数値を入力できなくする
たとえば、このような商品の注文表で個数は100個～300個の間に指定したいと思います。
まず、個数の範囲を選択し、「データ」タブの「データの入力規則」を開きます。
入力値の種類で「整数」を選択し、データで「次の値の間」を指定します。最小値には「100」、最大値には「300」と入力します。
これで、数字を入力する際のルールを設定することができました。
次に「エラーメッセージ」のタブに移動します。スタイルに「停止」を選択すれば、100~300の間の数字以外は入力できなくなります。
エラーメッセージには、「個数は100～300の間の数字で入力してください。」と表示されるようにします。
これで「OK」を押せば、入力規則を設定することができます。
たとえば、商品Aに「150」と入力すると正しく入力することができます。これは100~300の間の数字だからです。
一方で、「400」と入力すると、「個数は100～300の間の数字で入力してください。」というエラーメッセージが表示されます。
商品Bについても範囲外の数字を入力するとエラーメッセージが表示され、入力できません。
このように入力規則を使えば、任意の範囲の数字で入力を制限することができます。
指定した範囲外の数値であってもユーザーが続行を選択すれば入力できるようにする
しかし、通常の範囲から超えているものの正しい数字を入力しているかもしれません。その時だけ、特に注文量が多くなったり、少なくなったりすることもあります。
そのような場合は、入力規則のエラーメッセージのスタイルを「注意」に変更します。これは、警告メッセージを表示しますが、ユーザーが続行を選択すれば入力を許可することを意味します。
エラーメッセージには、「入力された数量は、通常の範囲（100～300）を超えています。この数量で発注しますか？」と入力します。
これで「OK」を押せば、「注意」の入力規則を設定することができます。
商品Aに「150」と入力すれば、先ほどと同じように正しく入力することができます。
今度は「400」と入力すると、先ほど設定したエラーメッセージが表示されます。
ここで「はい」を選択すれば、正しく「400」と入力することができます。
これで入力ミスがないか注意を促すことができ、正しい場合もきちんと数字を入力することができます。
商品ごとに数量の範囲を変更する
なお、今まではすべての商品で同じ数量の範囲を指定していましたが、商品によって注文する数量は変わることが多いと思います。たとえば、このように各商品の発注ルールが定められているとします。
1つ1つ入力規則を設定しても良いのですが、商品の種類が多いと設定するのが大変です。また、発注ルールを変更する場合も都度入力規則を変えるのも非常に手間になります。そこで、数式を使って入力規則を定めたいと思います。
まず、入力する数式について解説します。セルC3に以下のように数式を入力します。
=AND(B3>=VLOOKUP(A3,$A$9:$C$11,2,FALSE),B3<=VLOOKUP(A3,$A$9:$C$11,3,FALSE))
まず、AND関数で「～以上」「～以下」という2つの条件を指定します。
最初に「～以上」ですが、これはセルB3が商品Aの最小以上であることを条件とします。商品Aの最小値を抽出するためVLOOKUP関数を使用します。検索値には「商品A」と書かれたセルを指定し、範囲には発注ルールの表を選択します。列番号には、最小値を抽出するので、表の2列目「2」を指定します。検索方法には、完全一致「FALSE」を指定します。これで「～以上」という条件を指定することができます。
次に「～以下」の条件ですが、基本は同じで、VLOOKUP関数で商品の最大値を抽出します。この際、表の3列目を抽出するので、列番号には「3」を指定します。
これで商品Aの入力範囲の条件を数式で指定することができます。
下に数式をコピーすれば、すべての商品の入力範囲の条件を指定することができます。
今は個数が入力されていないので、すべて「FALSE」になっています。たとえば、商品Aに「500」を入力すると条件を満たすので、「TRUE」になります。
一方で「1000」と入力すると条件を満たさないので、「FALSE」になります。
では、入力規則を設定していきます。先ほど入力した数式をコピーします。
=AND(B3>=VLOOKUP(A3,$A$9:$C$11,2,FALSE),B3<=VLOOKUP(A3,$A$9:$C$11,3,FALSE))
データの入力規則で入力値の種類には「ユーザー設定」を選択し、数式に先ほどコピーした数式を貼り付けます。
エラーメッセージのスタイルを「注意」にして、エラーメッセージには「入力された数量は、通常の範囲を超えています。この数量で発注しますか？」と入力します。
これで「OK」を押せば、入力規則を設定することができます。たとえば、商品Aに「500」と入力すると正しく入力することができます。
一方で、「1000」と入力するとエラーメッセージが表示されます。
商品B、商品Cに関してもそれぞれのルールに基づいて、エラーメッセージが表示されます。
たとえば、商品Aの発注ルールが最小「150個」、最大「300個」に変わったとします。数式を使って入力規則を設定しているので、発注ルールの表を変更すれば、エラーメッセージが表示される数値の範囲を変更することができます。
たとえば、「500」と入力するとエラーメッセージが表示されるようになります。
このように数式を使って入力規則を設定すれば、条件を柔軟に変えることができます。
今回紹介した入力規則のテクニックを活用すれば、想定外のデータが入力されることを未然に防ぐことができます。
コメント