Excelで配列式を使用すると、複数の条件を使用してデータベースまたはデータテーブルの情報を検索する検索式を作成できます。配列式には、MATCH関数をINDEX関数内にネストします。
このチュートリアルでは、複数の条件を使用してサプライヤを検索するルックアップ式を作成する手順を段階的に示します。チタンウィジェット サンプルデータベースに保存します。自分のカスタム検索式を作成するのがどれほど簡単かを学ぶために続けます。
Excelワークブックにデータを準備する
このチュートリアルの手順を実行するには、まず、上の図に示すように、サンプルデータを次のセルに入力する必要があります。このチュートリアルで作成された配列数式に対応するために、行3と4は空白のままです。
- データの上位範囲をセルに入力する D1 に F2
- 2番目の範囲をセルに入力する D5 に F11
このチュートリアルでは画像に表示される書式は含まれていませんが、ルックアップ式の仕組みには影響しません。上記のような書式設定オプションに関する情報は、この基本的なExcel書式チュートリアルで利用できます。
08の02ExcelでINDEX関数を作成する
ザ INDEX 関数は、複数のフォームを持つExcelのほんの一部です。この関数は 配列形式 と 参照フォーム。配列フォームはデータベースまたはデータテーブルから実際のデータを返しますが、参照フォームはテーブル内のデータのセル参照または位置を返します。
このチュートリアルでは、データベースのこのサプライヤへのセル参照ではなく、チタンウィジェットのサプライヤの名前を知りたいので、Arrayフォームを使用します。 INDEX関数を作成するには、次の手順を実行します。
- セルをクリック F3 これをアクティブなセルにする - これがネストされた関数に入る場所です。
- クリックしてください 数式 タブをクリックします。
- 選択する 参照と参照 リボンバーから機能をドロップダウンして開きます。
- クリック INDEX リストを表示して 選択引数 ダイアログボックス。
- 選択する 配列、row_num、col_num ダイアログボックスのオプションをクリックします。
- クリック OK INDEX関数ダイアログボックスを開きます。 Excelで数式ビルダーが開きます。
- の中に 数式ビルダー、 クリックしてください アレイ ライン。
- セルを強調表示する D6 に F11ワークシート内をクリックしてドラッグすると、ビルダーに範囲を入力できます。
旧バージョンのExcelでは、 数式ビルダー は、 関数の引数 窓。このチュートリアルで説明したのと同じ手順をウィンドウに適用します。
03/08ネストされたMATCH関数を開始する
ある関数を別の関数の中に入れ子にするときは、2番目または入れ子関数の式ビルダーを開くことができません。o必要な引数を入力します。ネストされた関数は、最初の関数のソートされた関数の1つとして型指定されなければなりません。
関数を手動で入力する場合、関数の引数はカンマで区切られます。
入れ子にされたMATCH関数を入力する最初のステップは、 参照値 引数。 Lookup_valueは、データベース内で照合する検索用語の場所またはセル参照です。
通常、Lookup_valueは1つの検索基準または用語のみを受け入れます。複数の基準を検索するには、Lookup_value; t彼はアンパサンド記号を使って2つ以上のセル参照を連結または結合することによって行われます。 &.
- の中に 数式ビルダー、 クリックしてください Row_num ライン。
- 関数名を入力 一致 オープンラウンドブラケットが続きます。
- セルをクリック D3 そのセル参照をダイアログボックスに入力します。
- セル参照の後にアンパサンドを入力 D3 第2のセル参照を追加する。
- セルをクリック E3 この第2のセル参照を入力する。
- セル参照の後にカンマを入力します。 E3 MATCH関数の入力を完了する 参照値 引数。
このチュートリアルの最後のステップでは、 Lookup_values ワークシートのセルD3およびE3に入力されます。
04/08ネストされたMATCH関数を完了する
このステップでは、 Lookup_array ネストされた引数 一致 関数。 Lookup_arrayは、チュートリアルの前のステップで追加されたLookup_value引数を見つけるためにMATCH関数が検索するセルの範囲です。
Lookup_array引数に2つの検索フィールドを指定しているので、Lookup_arrayで同じことをする必要があります。 MATCH関数は、指定された各用語について1つの配列のみを検索し、複数の配列を入力するために、再び配列を連結するためにアンパサンドを使用します。
これらのステップは、前のステップで入力したカンマの後に入力する必要があります。 Row_num の INDEX 関数。
- クリックしてください Row_num コンマの後の行で、現在のエントリの最後にカーソルを置きます。
- セルを強調表示する D6 に D11 範囲を入力するワークシートで - これは関数が検索する最初の配列です。
- セル参照の後にアンパサンドを入力 D6：D11 関数が2つの配列を検索する必要があるからです。
- セルを強調表示する E6 に E11 範囲を入力するワークシートで - これは関数が検索する2番目の配列です。
- セル参照の後にカンマを入力します。 E3 のエントリを完了するために 一致 関数 Lookup_array 引数。
- 出て INDEX 関数ダイアログボックスがチュートリアルの次のステップのために開きます。
MATCH型引数を追加する
MATCH関数の3番目と最後の引数は、 Match_type 引数;それはどのように一致するかExcelに指示します 参照値 Lookup_arrayの値を使用します。使用可能な選択肢は、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データは、降順でソートする必要があります。
これらのステップは、前のステップで入力したカンマの後に入力する必要があります。 Row_num の INDEX 関数。
- コンマの後に Row_num 行には、 0 - ネストされた関数がセルに入力した用語と正確に一致するものを返すようにするため D3 そして E3.
- 閉じた丸いブラケットをタイプする - ) - 一致 関数。
- 出て 数式ビルダー チュートリアルの次のステップのために開いてください。
INDEX関数の終了
今、 一致 関数が実行されると、式ビルダーの3行目に移動し、最後の引数を入力します INDEX 関数。この3番目の最後の議論は、 Column_num 範囲内の列番号をExcelに伝える引数 D6 に F11;これは関数が返す情報を見つける場所です。この場合、 チタンウィジェット.
- クリックしてください Column_num ライン。
- 3番を入力してください - 3 - この行では、範囲の3番目の列のデータを探しているので D6 に F11.
- 繰り返しますが、 数式ビルダー チュートリアルの次のステップのために開いてください。
配列式の作成
閉じる前に 数式ビルダー入れ子関数を配列式に変換する必要があります。これは、私たちの関数がデータテーブル内の複数の項を検索できるようにするものです。このチュートリアルでは、次の2つの用語に一致するようにしています。 ウィジェット 列1から チタン 列2から。
Excelで配列式を作成するには、 CTRL, シフト、および 入る 同時にキーボードのキーを押します。一度押すと、関数は中括弧で囲まれ、関数が配列になったことを示します。
- 閉じます 数式ビルダー をクリックすると 完了 ボタン。
- 次に、セルを選択します。 F4 を押して 入る キーを押して数式を表示します。
- フォーミュラを配列に変換するには、同時に CTRL + SHIFT + ENTER あなたのキーボードで。
- 正しく行われた場合 ＃N / A エラーがセルF3に表示されます - 関数に入ったセル。
- ザ ＃N / A セルにエラーが表示される F3 細胞 D3 そして E3 空白です。 D3とE3は、我々が関数に Lookup_values。これら2つのセルにデータが追加されると、エラーはデータベースの情報に置き換えられます。
検索条件の追加
最後に、検索用語をワークシートに追加します。前の手順で説明したように、我々は条件に一致するように探しています ウィジェット 列1から チタン データベースの該当する列の両方の条件に一致する式が見つかった場合にのみ、3番目の列の値が返されます。
- セルをクリック D3.
- タイプ ウィジェット を押して 入る キーを押します。
- セルをクリック E3.
- タイプ チタン を押して 入る キーを押します。
- サプライヤーの名前 ウィジェット株式会社 セルF3に表示されるはずです。これは、Titaniumウィジェットを販売している唯一のサプライヤであるため、関数の位置です。
- セルF3をクリックすると、ワークシート上の数式バーに関数が表示されます。
{= INDEX（D6：F11、MATCH（D3＆E3、D6：D11＆E6：E11,0）、3）}
この例では、チタンウィジェット用のサプライヤは1つだけです。複数のサプライヤが存在する場合、データベースに最初にリストされたサプライヤが関数によって返されます。
