FC2ブログ

複数の関数を組み合わせて使う 2 IF+VLOOKUP

今回は、
 ネストの関数を最初から作るケース


セルD8に IF関数とVLOOKUP関数を使った数式を作成する

まずは、確認
 D列で求めるのは、「単価」
 「単価」は、一覧表の3列目にある


1.セルD8をクリック
2. fx (関数の挿入)をクリック
3.IFを選択 [OK]クリック
4.「論理式」欄に、「B8=””」と入力する
5.「真の場合」欄に、「””」と入力する

  201507160011.png

6.「偽の場合」欄をクリック
  
「数式バー」の左側にある「関数ボックス」右端にある▼をクリックする

  201507160012.png

7.一覧に「VLOOKUP」があれば、クリックする

  201507160013.png

 ※ 無かった場合は、一番下の「その他の関数」をクリック
   [関数の挿入]画面が表示されるので
   「関数の分類」:「検索と参照」で選択する

8.[関数の引数]画面が、「VLOOKUP」に変化する
  「検査値」欄に、カーソルがあることを確認
  セルB8をクリックする


  201507160014.png

9.「データ範囲」欄 右端にある 201507160016.png をクリックする

  201507160015.png

10.セル範囲H8:J19をドラッグ選択する

  201507160017.png

11.[関数の引数]右端の 201507160018.png をクリックする

  201507160019.png

12.「データ範囲」にカーソルがあることを確認し、[F4]キーを押す
  H8:J19 が、$H$8:$J$19 に 変化します


  201507160020.png

13.「列番号」 欄に、「3」と入力
  「検索方法」欄に、「0」を入力
  [OK]クリック

  

  201507160021.png
  ※ 関数の場合は、TRUE→1 FALSE→0 で、代用できる
    正式ではない ですが、一般的に使われる裏ワザです

14.D15までオートフィルする

  201507160022.png

製品IDを選択してみる
 セルB9 は、H99A10 ・ セルB10に、H99A21

  201507160023.png

「数量」を入力する
 セルE8 に、30 ・セルE9 に、25 ・ セルE10に、75

  201507160024.png

とりあえず 今回の「関数」は、ここまで
次回からは、「データベースについて」の予定です


 
スポンサーサイト



複数の関数を組み合わせて使う  If+VLOOKUP

関数の引数の中で、さらに関数を指定されていることを
「関数のネスト」といいます
 ※ Kingsoft Spreadsheets 2013では、ネスト出来るレベルは7までです
 ※ Microsoft Excel 2013 では、64レベル


数式が空白セルを参照する可能性がある
など
計算結果にエラー値が表示されることがある場合
IF関数とVLOOKUP関数を組み合わせて
エラー表示を回避します


セルC8に入力されている関数を
エラー値#N/Aが表示されないように
数式を変更する


1.セルC8をクリック
2.数式バーで、以下の部分を選択する
  VLOOKUP(B8,$H$8:$J$19,2,FALSE)

  ※ 「=」は、含めない

  201507160001.png

3.右クリック 「切り取り」をクリック

  201507160002.png

4. fx (関数の挿入)をクリック

  201507160003.png

5.IFを選択 [OK]クリック

  201507160004.png

6.「論理式」欄に、「B8=””」と入力する

  201507160005.png

7.「真の場合」欄に、「””」と入力する

  201507160006.png

8.「偽の場合」欄で、「貼り付け」を行う

  201507160007.png

9.[OK]をクリック

  201507160008.png

10.セルC15までオートフィルする

  201507160009.png

11.セルC9~C15に、#N/Aが表示されないことを確認する

  201507160010.png


 

VLOOKUP関数を使う 2-2

前回の結果

 201507120009.png

 製品ID(セルB8)が未入力なので、エラー値「#N/A」が表示されている

セルB8 に、「H99A16」と入力する
設定されている関数の結果が表示される


   201507120010.png

 注 意

「検査値」の「製品ID」に入力する内容が少しでも違うと
VLOOKUP関数では、エラー値(#N/A)が表示される
例えば
先ほどの「H99A16」のケースで
 「H19A16」のように全角・半角混合で入力した時
 「H99A16 」のようにスペースを入れてしまった時


  201507120011.png

  201507120012.png

下の例の場合 一見入力が間違っていないように思える

このような入力ミスによるエラーが起きないように
「製品ID」の入力欄(セル範囲 B8:B15)に、入力規則を設定する


1.セル範囲B8:B15を選択する

  201507120013.png

2.[データ]タブ 「入力規則」をクリック

  201507120014.png

3.[入力規則]画面
  [設定]タブ 「条件の設定」 「許可」で
  「すべての値」を 「リスト」に変更


   201507120015.png

4.ソースで、右端のアイコン 201507120016.png をクリック

  201507120017.png

5.一覧表で、「製品ID」のセル範囲(H8:H19)を選択

  201507120018.png

6.入力規則の右端のアイコンをクリック

  201507120019.png

7.【 □ セル内のドロップダウン】に、チェックを入れる

  201507120020.png

8.[OK]クリック

  201507120021.png

9.セルB8の横に ▼ が表示されている

  201507120022.png

10.[▼]をクリックすると、「製品ID」のリストが表示される

  201507120023.png

11.この一覧の「H99A16」をクリックする

  201507120024.png

12.C8に「製品名」が表示される

  201507120025.png

今回は、ここまで
「関数04.et」として保存する   



 

VLOOKUP関数を使う 2-1

完全一致検索のケース

使用ファイル: 関数03.et 

 201507120001.png


シート「請求書2」の表に
製品ID番号を入力し、製品名が表示されるように設定する


1.セルC8をクリック
  fx (関数の挿入)クリック

   201507120002.png

2.[関数の挿入]画面
  「関数の分類」「最近使用した関数」
  「関数の選択」欄に、VLOOKUP があれば選択

  無かった場合 「検索と参照」に変えて選択
  [OK]クリック

   201507120003.png

3.[関数の引数]画面
  「検査値」欄に、カーソルがあることを確認
  セルB8をクリックする


   201507120004.png

4.「データ範囲」欄をクリック

   201507120005.png

5.一覧表のデータ部分(セル範囲 H8:J19)をドラッグ選択する

   201507120006.png

6.[F4]キーを押す
  $H48:$J$16 に変化する ※ 絶対参照にする


   201507120007.png

7.「列番号」欄に、「2」と入力
  「検索方法」欄に、「FALSE」と入力 ※ 「0」(ゼロ)でも


注意
 結果が、#N/A ですが、これは「検索値」の B8セルが空欄であるため


  [OK]クリック

   201507120008.png

つづく


 

VLOOKUP関数を使う 1 近似値検索

VLOOKUP関数

引数の「検索方法」が、TRUE または省略するケース

どのようなケースで使うか
 * 荷物の重さに応じて 料金を決める
 * 距離に応じて 料金をきめるなど
 検索値で検索するものが、数値などで
 すべてを一覧表にすると表が大きくなりすぎる



 荷物の重さに応じた料金を求める

 重さが 「0kg以上0.5kg未満」は、Aランクで、300円
 重さが 「0.5kg以上1kg未満」は、Bランクで、500円
 重さが 「1kg以上3kg未満」は、Cランクで、800円
 重さが、「3kg以上5kg未満」は、Dランクで、1,000円
 重さが 「5kg以上10kg未満」は、Eランクで、1,500円
 重さが、「10kg以上」は、Fランクで、2,000円
 以上のような条件の場合
 次のような一覧表になります


   201507110000.png

調べたい重さを セルF4に入力すると
料金を セルG4に自動的に表示する

 
  本来は セルF4が空欄の場合 空欄というIF関数も組み合わせるのですが...
 
1.セルF4に「2.5」と入力する
2.セルG4をクリック
fx  「関数の挿入」をクリック

   201507110001.png

3.[関数の挿入]画面 「関数の分類」で▼をクリック
  「検索と参照」を選択する


   201507110002.png

4.一番下にある「VLOOKUP」を選択
  [OK]クリックする


   201507110003.png

5.[関数の引数]画面で、「検索値」欄にカーソルがあることを確認
  検索したいセルF4をクリックする


   201507110004.png

6.「データ範囲」欄をクリック
  カーソルが移動したことを確認


   201507110005.png

7.一覧表のデータの部分で、最初のセルB4をクリック
  データ部分最後(右下)のセルD9まで、ドラッグする
  選択されたセル範囲が点線で表示され
  6行*3列の意味の[ 6R × 3C ]も表示される


   201507110006.png

8.「データ範囲」欄に、B4:D9 と表示される
  今回はこのままでもよいのですが セル範囲を固定する場合
  カーソルが後ろにあることを確認 [F4]キーを押す


   201507110007.png

9.B4:D9 が $B$4:$D$9 になるセル範囲の絶対参照
  数式の入ったセルをオートフィルしてもデータ範囲が変動しない

   201507110008.png

10.「列番号」欄には、
   表示したい「料金」の列が3列目なので、「3」と入力する
  「検索方法」欄には、近似値検索の「TRUE」を入力する


   201507110009.png


検索方法を変えた場合

1.近似値検索なので、「検索方法」欄を省略しても OK

  201507110010.png

2.よく使う方法ですが 
  Excel で数式の場合 TRUE は、1 でも同じ意味になる

  ※ FALSEは、 0 です

  201507110011.png

3.間違えて FALSE(=完全一致検索) と入力した場合は
  完全一致する検索値(2.5)がないので #N/A というエラーになる


  201507110012.png