IF関数とVLOOKUP関数

はじめに

ここでは、Excelの条件検索関数についての使用方法や使用例について説明します。条件検索関数の中で、特にExcelを業務で使用する際に良く利用する関数が、IF関数やVLOOKUP関数です。

  • IF関数…ある条件(論理式)が正しい(真-True)の場合と正しくない(偽-False)の場合、それぞれどちらか一方の結果を返す。
  • VLOOKUP関数…ある検索したい値(検索値)を元に、ある表や範囲から、その値を検索し、表から関連の値を返す。

[本テキスト用データダウンロード]
ex_if_vlookup3.xlsx


IF関数

構文

IF(論理式, 真の場合, 偽の場合)

[論理式]

A > B ... AはBよりも大きい
A >= B ... AはB以上
A < B ... AはBよりも小さい
A <= B ... AはB以下
A = B ... AはBと等しい
A <> B ... AとBは等しくない

IF関数の例1(大小比較)

数値が100より大きい場合は「Big」、それ以外(100以下)の場合は「Small」と表示させてみます。式の中に文字列を記載する場合はその文字列を「”(ダブルクオーテーション)」で囲みます。数値が「100」の場合は、「Big」、「Small」どちらが表示されるでしょうか。

ex-if01

IF関数の例2(数式の結果を返す)

時給が、パート時間によって変化する場合の例です。このように、「真の場合」、「偽の場合」がそれぞれ入る第2引数、第3引数には、前の例のように文字列だけでなく、数値や数式を使用することができます。

ex-if02

IF関数の例3(数式評価の回避処理)

計算書シートなど、対象となる値がセルに入っていなくても、普通あらかじめセルに計算式を入れておくものですが、対象となる値が入っていてもいなくても数式は評価されるので、結果として「0」と表示されたり、図のように、Yの値が入っていない場合、「#DIV/0!(0で除算)」というエラーが表示されてしまいます。それを回避するために、IF関数で、対象となるセルの内容が空(空文字)であれば、空で表示し、何か値が入っていれば式を評価する、というように回避処理をすれば良いです。

ex-if03

IF関数の例4(ネストのIF)

IF関数の引数の中は、数式を入れることができますので、そこにIF関数式を設定することも可能です。IF関数は2つの分岐(2値の比較)しかできませんが、IF関数を入れ子(ネスト)にすると、図の例のように3つの分岐を行うことができます。3つに分岐させたい場合はIF関数を2つ、4つに分岐させたい場合はIF関数を3つ、それぞれネスト処理します。IF関数を4つ以上のネストを要する場合は、VLOOKUP関数やCHOOSE関数など他の方法を取った方が良いです。

ex-if04

※Excel 2016のバージョンより、IFS関数を使うことで、ネストすることなく次のような記述で簡潔な式に置き換えることが可能になりました。

=IFS(C6>=80,"優",C6>=60,"可",TRUE,"不可")

VLOOKUP関数

構文

VLOOKUP(検索値, 範囲, 列番号, 検索方法)

[検索方法]

TRUE(または省略) ... 検索値及び近似値を含めて検索
FALSE ... 検索値と完全一致する値を検索

VLOOKUP関数の例1

検査値(B6)の値を、参照表の範囲(F6:G9)の左の列(商品CD)から探し、該当行の2列目の値を表示する。

ex-vl01

その他、以下のようにMATCH関数と検索関数の組合せによって、VLOOKUP関数と同様の結果を得ることが可能です。

  • =INDIRECT("G"&(5+MATCH(B6,F6:F9)))
  • =INDEX(G6:G9,MATCH(B6,F6:F9),1)
  • =OFFSET(G5,MATCH(B6,F6:F9),0)

VLOOKUP関数の例2

検索方法をTRUEもしくは省略すれば、検査値を任意の範囲で参照することが可能です。ただし、参照表(の一番左の列)は昇順である必要があります。

ex-vl02

VLOOKUP関数の例3

次図のような例では、検査値(くじ番号)を近似値で参照するのは問題がありますので、この場合は、検索方法をFALSEで指定します。

ex-vl03

この場合、該当検査値がないとエラー値「#N/A」を表示します。エラー値「#N/A」を回避させたい場合は、IFNA関数を利用して次のように式を入力します。

  • =IFNA(VLOOKUP(B6,F6:G9,2,FALSE),"はずれです")

練習課題

次図のような、地区ごとに注文された弁当の売上台帳を記録するワークシートを作成してください。ただし、次のような条件(仕様)を満たすものとします。

  1. 設定した計算式はすべての行に入力する。
  2. 金額が入っているセルは、すべて3桁ごとのカンマ区切り表示とする。
  3. 弁当料金表」については、自由に行や列を追加しても良い。
  4. 製品名」(D4)は、「製品ID」から「弁当料金表」より参照する計算式にて表示させる。
  5. 販売単価」(F4)は、数量」が10個以上の場合は「標準単価」の10%割引とする
    可能であれば「数量割引」(M11)を参照し割引率の変化に対応できること。
  6. 税込金額」(H4)は、「消費税率」(M10)込みの金額(端数切捨て)を計算する
  7. 備考」(I4)は、単価10%割引した分について”10%割引”と表示する
  8. 新たなデータ、「中央地区」から「幕の内弁当A」を9個を売り上げた件を追記する。

ex-if-sub01


練習課題の解答例

以下は、なるべく見ないで練習課題に挑戦してください。

まずは、次図のように、「弁当料金表」に「割引単価」の列を追加します。

ex-if-ans01

  • 割引単価」(N4)
    • =M4*(1-$M$11/100)

※あらかじめ10%割引した単価の値を計算しておきます。その際に、セルM11を絶対参照で指定している点に注意。

「地区別弁当売上台帳」の完成例は、次図のとおりです。

ex-if-ans02

各数式解答例は以下のとおり。

  • 製品名」(D4)
    • =IFNA(VLOOKUP(C4,$K$4:$N$7,2,FALSE),"")
  • 販売単価」(F4)
    • =IF(OR(C4="",E4=""),"",VLOOKUP(C4,$K$4:$N$7,IF(E4<10,3,4),FALSE))
    • 製品IDまたは数量が空の場合は、空で表示し、逆に製品IDと数量両方に値が入っている場合は、VLOOKUP関数にて弁当料金表から単価を取得する。この時、数量が10個未満は、料金表の3列目(標準単価)、10個以上の場合は4列目(割引単価)、それぞれ取得する列を分岐する。
  • 金額」(G4)
    • =IF(F4="","",E4*F4)
  • 備考」(I4)
    • =IF(E4>=10,$M$11&"%割引","")
    • 割引率が変わっても対応できるようセル参照を用いる
  • 税込金額」(H4)
    • =IF(F4="","",TRUNC(G4*(1+$M$10/100)))

税込金額」の計算で、金額に「1+8/100」すなわち「1.08」を乗じているのですが、端数(小数点以下)が出ますので、その端数切り捨てに、TRUNC関数を使用しています。

下図のように、セルの表示形式を「桁区切りスタイル」にすることで、整数表示(端数が消されている)になりますが、データ自体は小数部も含んでいますので、きちんと切り捨て処理をしないと合計金額と整合しなくなります。この場合、表示上の税込金額の合計金額は22,794円になります。

ex-if-ans03

IF関数とVLOOKUP関数」への2件のフィードバック

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト /  変更 )

Google フォト

Google アカウントを使ってコメントしています。 ログアウト /  変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト /  変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト /  変更 )

%s と連携中