Excelで配列式を使用すると、複数の条件を使用してデータベースまたはデータテーブルの情報を検索する検索式を作成できます。
配列式には、 MATCH関数をINDEX関数内にネストします。
このチュートリアルでは、複数の基準を使用してサンプルデータベース内のチタンウィジェットのサプライヤを検索するルックアップ式を作成する手順を段階的に示します。
下のチュートリアルのトピックの手順に従って、上記の画像に示す数式を作成して使用します。
01の09
チュートリアルデータの入力
チュートリアルの最初のステップは、Excel ワークシートに データを入力すること です 。
チュートリアルの手順に従うには、上記の画像に示されているデータを次のセルに入力します。
このチュートリアルで作成された配列数式に対応するために、 行 3と4は空白のままです。
このチュートリアルでは画像に表示される書式は含まれていませんが、ルックアップ式の仕組みには影響しません。
上記のような書式設定オプションに関する情報は、この基本的なExcel書式チュートリアルで利用できます。
02の09
INDEX関数の起動
INDEX関数は、Excelで複数のフォームを持つ数少ない関数の1つです。 この関数には、 配列形式と参照形式があります 。
配列フォームはデータベースまたはデータテーブルから実際のデータを返しますが、参照フォームはテーブル内のデータのセル参照または位置を返します。
このチュートリアルでは、データベースのこのサプライヤへのセル参照ではなく、チタンウィジェットのサプライヤの名前を知りたいので、Arrayフォームを使用します。
各フォームには、関数を開始する前に選択しなければならない引数のリストがあります。
チュートリアルの手順
- セルF3をクリックしてアクティブセルにします。 ここで入れ子関数を入力します。
- リボンメニューの式タブをクリックします。
- リボンから参照と参照を選択して、関数ドロップダウンリストを開きます。
- リストのINDEXをクリックすると、 Select Arguments ダイアログボックスが表示されます 。
- ダイアログボックスの配列row_num、col_numオプションを選択します。
- OKをクリックしてINDEX関数ダイアログボックスを開きます。
03の09
INDEX関数の配列引数の入力
必要な最初の引数はArray引数です。 この引数は、目的のデータを検索するセルの範囲を指定します。
このチュートリアルでは、この引数はサンプルデータベースになります 。
チュートリアルの手順
04/09
ネストされたMATCH関数の起動
ある関数を別の関数の中に入れ子にするときは、2番目または入れ子関数のダイアログボックスを開いて必要な引数を入力することはできません。
ネストされた関数は、最初の関数の引数の1つとして型指定されなければなりません。
このチュートリアルでは、ネストされたMATCH関数とその引数は、INDEX関数ダイアログボックスの2行目のRow_num行に入力されます。
関数を手動で入力する場合、関数の引数はカンマ"、"で区切られていることに注意することが重要です。
MATCH関数のLookup_value引数の入力
ネストされたMATCH関数を入力する最初の手順は、 Lookup_value引数を入力することです。
Lookup_valueは、データベース内で照合する検索用語の場所またはセル参照です。
通常、 Lookup_valueは1つの検索基準または用語のみを受け入れます。 複数の条件を検索するには、 Lookup_valueを拡張する必要があります 。
これは、アンパサンド記号 " ＆ "を使用して2つ以上のセル参照を連結または結合することによって行われます 。
チュートリアルの手順
- INDEX関数ダイアログボックスで、 Row_num行をクリックします。
- 関数名に一致するものを入力し、続いて丸い括弧 " （ "
- ダイアログボックスにセル参照を入力するには、セルD3をクリックします。
- 2番目のセル参照を追加するには、セル参照D3の後にアンパサンド「 ＆ 」を入力します。
- この2番目のセル参照をダイアログボックスに入力するには、セルE3をクリックします。
- MATCH関数のLookup_value引数の入力を完了するには、セル参照E3の後にカンマ「、」を入力します。
- INDEX関数ダイアログボックスをチュートリアルの次のステップのために開いたままにしておきます。
チュートリアルの最後のステップで、Lookup_valuesがワークシートのセルD3とE3に入力されます。
05の09
MATCH関数のLookup_Arrayの追加
このステップでは、ネストしたMATCH関数のLookup_array 引数を追加します。
Lookup_arrayは、チュートリアルの前のステップで追加されたLookup_value引数を見つけるためにMATCH関数が検索するセルの範囲です。
Lookup_array引数に2つの検索フィールドを指定しているので、 Lookup_arrayで同じことをする必要があります。 MATCH関数は、指定されたタームごとに1つの配列のみを検索します。
複数の配列を入力するために、再びアンパサンド " ＆ "を使用して配列を連結します。
チュートリアルの手順
これらの手順は、INDEX関数ダイアログボックスの Row_num行に前の手順で入力したカンマの後に入力します 。
- コンマの後のRow_num行をクリックして、現在のエントリの最後にカーソルを置きます。
- 範囲を入力するには、 ワークシート内のセルD6〜D11を強調表示します。 これは、関数が検索する最初の配列です。
- 関数が2つの配列を検索するようにするため、セル参照D6：D11の後にアンパサンド「 ＆ 」を入力します。
- ワークシート内のセルE6〜E11を強調表示して範囲を入力します。 これは、関数が検索する2番目の配列です。
- MATCH関数のLookup_array引数の入力を完了するには、セル参照E3の後にカンマ「、」を入力します。
- INDEX関数ダイアログボックスをチュートリアルの次のステップのために開いたままにしておきます。
06の06
マッチタイプの追加とMATCH関数の完了
MATCH関数の3番目と最後の引数はMatch_type引数です。
この引数は、Lookup_valueとLookup_arrayの値を一致させる方法をExcelに指示します。 選択肢は、1、0、または-1です。
この引数はオプションです。 省略された場合、関数はデフォルト値の1を使用します。
- Match_type = 1または省略された場合：MATCHは、Lookup_value以下の最大値を見つけます。 Lookup_arrayデータは、昇順にソートする必要があります。
- match_type = 0の場合：MATCHは、Lookup_valueと正確に等しい最初の値を見つけます。 Lookup_arrayデータは任意の順序でソートできます。
- Match_type = -1の場合、MATCHはLookup_value以上の最小値を検出します。 Lookup_arrayデータは、降順でソートする必要があります。
チュートリアルの手順
これらの手順は、INDEX関数ダイアログボックスの Row_num行に前の手順で入力したカンマの後に入力します 。
- Row_num行のコンマに続いて、ネストされた関数がセルD3およびE3に入力する項に完全一致を返すようにするため、ゼロを入力します。
- 丸い丸括弧（ " ） "を入力して、MATCH機能を完成させます。
- INDEX関数ダイアログボックスをチュートリアルの次のステップのために開いたままにしておきます。
07の09
INDEX関数に戻る
MATCH関数が終了したので、開いているダイアログボックスの 3行目に移動し、INDEX関数の最後の引数を入力します。
この3番目の最後の引数はColumn_num引数で、D6からF11の範囲の列番号をExcelに返します。ここで、関数から返される情報が見つかります。 この場合、 チタンウィジェットのサプライヤです。
チュートリアルの手順
- ダイアログボックスのColumn_num行をクリックします。
- 範囲D6〜F11の3番目の列のデータを検索しているので、この行に3つの " 3 "（引用符なし）を入力します。
- [OK]をクリックしないか、INDEX関数ダイアログボックスを閉じないでください。 チュートリアルの次のステップ、つまり配列式を作成するために開いたままにしておく必要があります。
08の09
配列式の作成
ダイアログボックスを閉じる前に、ネストされた関数を配列式に変換する必要があります 。
配列式は、データ表の複数の項を検索できるようにするものです。 このチュートリアルでは、列1のウィジェットと列2のチタンという2つの用語を照合しています。
Excelで配列式を作成するには、キーボードのCTRL 、 SHIFT 、およびENTERキーを同時に押します。
これらのキーを同時に押すと、関数が中括弧{}で囲まれ、配列式になります。
チュートリアルの手順
- このチュートリアルの前のステップで完了したダイアログボックスを開いたまま、キーボードのCTRLキーとSHIFTキーを押したまま、 Enterキーを押して離します。
- 正しく実行されると、ダイアログボックスが閉じ、関数に入力したセルのF3セルに＃N / Aエラーが表示されます。
- セルD3とE3は空白なので、 ＃N / Aエラーはセル F3に表示されます。 D3とE3は、チュートリアルの手順5でLookup_valuesを見つける関数に指示したセルです。 これら2つのセルにデータが追加されると、エラーはデータベースの情報に置き換えられます 。
09 09
検索条件の追加
チュートリアルの最後のステップは、検索用語をワークシートに追加することです。
前の手順で説明したように、列1のウィジェットと列2のチタンという用語を一致させることを検討しています。
データベース内の該当する列の両方の条件に一致する式が見つかった場合にのみ、3番目の列の値が返されます。
チュートリアルの手順
- セルD3をクリックします。
- ウィジェットを入力し、キーボードのEnterキーを押します。
- セルE3をクリックします。
- 「 Titanium」と入力し、キーボードのEnterキーを押します。
- サプライヤの名前Widgets Inc.は、Titanium Widgetsを販売している唯一のサプライヤであるため、F3のセルに表示されます。
- セルF3をクリックすると、完全な関数
{= INDEX（D6：F11、MATCH（D3＆E3、D6：D11＆E6：E11,0）、3）}
ワークシートの上の数式バーに表示されます 。
注：この例では、チタンウィジェット用のサプライヤは1つだけです。 複数のサプライヤが存在する場合、データベースに最初にリストされたサプライヤが関数によって返されます。