エクセル（Excel）複数条件や数値などでデータを抽出する関数

　Excelには、あるデータの中から条件に合うデータを抽出し、セルに表示させるという非常に便利な関数がいくつか用意されています。例えば、商品台帳などをあらかじめ作っておき、商品コードを入れるだけで対応した価格や商品名を自動的に表示させるような関数です。

▼ 図01-01　一覧表のデータから、連絡先を抽出

 

次のページから解説するExcelブックは下記よりダウンロードできます。
なお、本文中サンプルデータに入力する数値や数式は、カギカッコで囲んで表示します。

◎サンプルデータ (zipファイル 11KB） 　>>ダウンロード

VLOOKUP関数

VLOOKUP関数は、指定した条件に対応した値を一覧表から抽出する関数です。
VLOOKUP関数を使って、“氏名”をオーダー表に入力した際、“連絡先”をあらかじめ用意されている“お客様情報”の中から抽出して表示してみましょう。

1. セルB3を選択する。
2. [関数の挿入]ボタンをクリックし、[関数の挿入]ダイアログボックスを表示する。
   ▼ 図02-01　セルB3を選択し、[関数の挿入]ボタンをクリック

   
    

   【Excel2007の場合】

   Excel2007では[数式]タブの[関数の挿入]ボタンをクリックしても[関数の挿入]ダイアログボックスが表示できます。

   ▼ 図02-02　Excel2007の[関数の挿入]ボタン

   
    
3. [関数の分類]ボックスから[検索/行列]を選択する。
4. [関数名]ボックスから[VLOOKUP]を選択する。
5. [OK]ボタンをクリックし、[関数の引数]ダイアログボックスを表示する。
   ▼ 図02-03　[関数の挿入]ダイアログボックス

   
    
6. セルA3をクリックし、[検索値]ボックスに「A3」と表示されていることを確認する。
7. [Tab]キーを押して[範囲]ボックスにカーソルを移動する。
   ※ [範囲]ボックス内をクリックしてカーソルを移動することもできます。
8. セルF3からI8をドラッグし、[F4]キーを押して絶対参照にする。
   ※ 数式をコピーしても参照範囲が変更されないよう[F4]キーを押して絶対参照にします。
9. [列番号]ボックスにカーソルを移動し、「3」と入力する。
   ※ 範囲内の左から何列目のデータを抽出したいかを指定します。
10. [検索の型]ボックスにカーソルを移動し、「false」と入力する。
    

    ※ 検索の型には検索値が完全一致の場合にデータを返すなら「false」、近似値を検索してデータを返すなら「true」を指定します。

    ※ Excel 2007では、[検索方法]ボックスです。

11. [OK]ボタンをクリックする。
    ▼ 図02-04　[関数の引数]ダイアログボックス

    
     

    セルB3に連絡先が表示されます。作成した数式をコピーし、住所も表示させましょう。

    ▼ 図02-05　数式をコピーして住所を表示

    
     

式がコピーされますが、エラー値「＃N/A」が表示されてしまいました。原因は、オートフィル機能により検索値がセルA3からB3に自動的に変わってしまったためです。
もう一度[関数の挿入]ダイアログボックスを表示して、式を修正しましょう。

12. セルC3を選択し、[関数の挿入]ボタンをクリックする。
13. [関数の挿入]ダイアログボックスが表示される。
14. [検索値]ボックスの値をセルB3からA3に修正する。
15. [範囲]が$F$3:$I$8であることを確認し、[列番号]ボックスは「4」に変更する。
16. [検索の型]が「FALSE」になっていることを確認し、[OK]ボタンをクリックする。
    ▼ 図02-06　[関数の挿入]ダイアログボックス

    
     

数式が修正され、住所が正しく表示されます。

▼ 図02-07　住所が正しく表示

DGET関数

次に、複数の条件で合致したデータを抽出するDGET関数を紹介します。

お客様情報リストには、「田中正美」という同じ名前の人が二人存在しています。このような場合、VLOOKUP関数では正しく抽出を行うことができません。DGET関数を使用すれば、「氏名」と「生年月日」の二つの条件から正しいデータを特定できます。

▼ 図03-01　同じ氏名の人が2人存在

 

1. “オーダー表2”シートを表示する。
2. セルC3を選択する。
3. [関数の挿入]ボタンをクリックし、[関数の挿入]ダイアログボックスを表示する。
   ▼ 図03-02　セルC3を選択し、[関数の挿入]ボタンをクリック

   
    

   【Excel2007の場合】

   Excel2007では[数式]タブの[関数の挿入]ボタンをクリックしても[関数の挿入]ダイアログボックスが表示できます。

   ▼ 図03-03　Excel2007の[関数の挿入]ボタン

   
    
4. [関数の分類]ボックスから[データベース]を選択する。
5. [関数名]ボックスから[DGET]を選択する。
6. [OK]ボタンをクリックし、[関数の引数]ダイアログボックスを表示する。
   ▼ 図03-04　[関数の挿入]ダイアログボックス

   
    
7. セルF2からI8をドラッグして[F4]キーを押し、[Database]ボックスに「$F$2:$I$8」と表示されていることを確認する。
   ※ データの参照先は変更されないように絶対参照にしておきます。
   ※ Excel 2007では[データベース]ボックスです。
8. [Tab]キーを押して[フィールド]ボックスにカーソルを移動する。
   ※ [フィールド]ボックス内をクリックしてカーソルを移動することもできます。
9. 抽出したいデータの項目名が入力されているセルH2をクリックする。
10. [Criteria]ボックスにカーソルを移動し、セルA2からB3をドラッグして指定する。
    ※ [Criteria]ボックスには抽出条件となるセル範囲の項目名を含めて選択します。
    ※ Excel 2007では[条件]ボックスです。
11. [OK]ボタンをクリックする。
    ▼ 図03-05　[関数の引数]ダイアログボックス

    
     

氏名と生年月日の2つの条件から連絡先が抽出されました。

▼ 図03-06　連絡先を表示

 

DGET関数の使い方は理解できましたか？
VLOOKUP関数では1つの条件から、DGET関数では複数の条件からデータを取り出すことができます。状況に応じて、使い分けるようにしましょう。

CHOOSE関数

ここまではシートの中にあらかじめ用意されたリストの中からデータを抽出してきましたが、CHOOSE関数を使えば、リストをシート中に作らなくても、式の中でリストを指定できます。
では、“オーダー№”を入力したときに“種別”を表示するようなCHOOSE関数の式を作ってみましょう。

1. “オーダー表”シートに切り替える。
2. セルB6を選択する。
3. [関数の挿入]ボタンをクリックし、[関数の挿入]ダイアログボックスを表示する。
   ▼ 図04-01　セルB6を選択し、[関数の挿入]ボタンをクリック

   
    

   【Excel2007の場合】

   Excel2007では[数式]タブの[関数の挿入]ボタンをクリックしても[関数の挿入]ダイアログボックスが表示できます。

   ▼ 図04-02　Excel2007の[関数の挿入]ボタン

   
    
4. [関数の分類]ボックスから[検索/行列]を選択する。
5. [関数名]ボックスから[CHOOOSE]を選択する。
6. [OK]ボタンをクリックし、[関数の引数]ダイアログボックスを表示する。
   ▼ 図04-03　[関数の挿入]ダイアログボックス

   
    
7. [インデックス]ボックスに「A6-100」と入力する。
   ※ [インデックス]ボックスに指定された数値が「1」ならば値1を、「2」ならば値2を返します。今回はセルA6に入力されているオーダーNoが101から開始しているため、「セルA6から100」を引いた数値がインデックス番号となります。
8. [Tab]キーを押して[値1]ボックスにカーソルを移動する。
   ※ [値1]ボックス内をクリックしてカーソルを移動することもできます
9. 「デスクプリント」と入力し、[Tab]キーを押して[値2]ボックスにカーソルを移動する。
   ※ [値1]ボックスにはインデックス番号が「1」の場合に表示する値を指定します。
10. 「ラベルプリント」と入力し、[Tab]キーを押して[値3]ボックスにカーソルを移動する。
11. 「デザインプリント」と入力し、[OK]ボタンをクリックする。
    ▼ 図04-04　[関数の引数]ダイアログボックス

    
     

“オーダー№”の値に応じて、CHOOSE関数の中で設定された“種別”が表示されます。

▼ 図04-05　種別を表示

 

“オーダーNo.”を「102」や「103」に変更し、“種別”が変更されることを確認しましょう。

SUMPRODUCT関数

最後に、対応する要素同士の積を合計する『SUMPRODUCT関数』を紹介します。
文章での説明は、少し難しいので実際に数式を作成しながら解説をしていきます。

“オーダー表”シートの右下にある「オーダー内容」を参照し、「オーダー表」に入力した“仕上サイズ”と“刷色”から料金を表示させます。

1. セルC8を選択する。
2. [関数の挿入]ボタンをクリックし、[関数の挿入]ダイアログボックスを表示する。
   ▼ 図05-01　セルC8を選択し、[関数の挿入]ボタンをクリック

   
    

   【Excel2007の場合】

   Excel2007では[数式]タブの[関数の挿入]ボタンをクリックしても[関数の挿入]ダイアログボックスが表示できます。

   ▼ 図05-02　Excel2007の[関数の挿入]ボタン

   
    
3. [関数の分類]ボックスから[数学/三角]を選択する。
4. [関数名]ボックスから[SUMPRODUCT]を選択する。
5. [OK]ボタンをクリックし、[関数の引数]ダイアログボックスを表示する。
   ▼ 図05-03　[関数の挿入]ダイアログボックス

   
    
6. [配列1]ボックスに「($F$12:$F$19=A8)*($G$12:$G$19=B8)」と入力する。
7. [配列2]ボックスにセルH12からH19を選択し、[F4]キーを押す。
8. [OK]ボタンをクリックする。
   ▼ 図05-04　[関数の引数]ダイアログボックス

   
    

オートフィル機能を使用して、他のセルにも同じように関数を挿入します。

▼ 図05-05　オートフィル機能を使ってコピー

 

最後に、もう一度SUMPRODUCT関数を使用し、“料金”と“枚数”の値を使用して“オーダー料金”を算出しましょう。

9. セルD13を選択する。
10. [関数の挿入]ボタンをクリックし、[関数の挿入]ダイアログボックスを表示する。
    ▼ 図05-06　セルD13を選択し、[関数の挿入]ボタンをクリック

    
     

    【Excel2007の場合】

    Excel2007では[数式]タブの[関数の挿入]ボタンをクリックしても[関数の挿入]ダイアログボックスが表示できます。

    ▼ 図05-07　Excel2007の[関数の挿入]ボタン

    
     
11. [関数の分類]ボックスから[数学/三角]を選択する。
12. [関数名]ボックスから[SUMPRODUCT]を選択する。
13. [OK]ボタンをクリックし、[関数の引数]ダイアログボックスを表示する。
    ▼ 図05-08　[関数の挿入]ダイアログボックス

    
     
14. [配列1]ボックスにセルC8からC12を選択し、[C8:C12]と表示されたことを確認する。
15. [配列2]ボックスにセルD8からD12を選択し、[D8:D12]と表示されたことを確認する。
16. [OK]ボタンをクリックする。
    ▼ 図05-09　[関数の引数]ダイアログボックス

    
     

これで数式は完成しました。枚数を入力し、オーダー料金が計算されるかどうか確認してみます。
料金×枚数の合計が“オーダー料金”に反映されたでしょうか？

▼ 図05-10　枚数を入力すると、オーダー料金が表示

 

今回は、台帳の管理などデータを扱う場合に活用できる関数をいくつかご紹介しました。用途に応じてこれらの関数を組み合わせて使いこなすことで、Excelでの処理をどんどん楽にできます。そのためには、今回使用したサンプルの引数や条件などを自分なりに変更し、関数の使い方やどの値が表示されるのかなどをしっかりマスターしておきましょう。