Excelで簡単なデータベースクエリにINDEXとMATCHを使用する

2024

もともと、Excelは実際のデータベースではありませんでした。初期のデータベース機能は量的にも質的にも限られていました。また、Excelデータベースのすべてのレコードが一度に画面に表示されるため、一度にすべてがメモリに格納されるため、Excelデータベースは非常に小さくなければなりませんでした。レコードが少ない複数のフィールド、レコードが多いフィールドはほとんどありません。 VLOOKUP（垂直）およびHLOOKUP（水平）は、データベースに特定の情報を問い合わせるために利用できる唯一の機能であった。たとえば、$ 1000以上$ 5000未満の売上げを含むすべてのレコードを検索して抽出することができますが、フラット・ファイル（データベース・マトリックスは1つのみ）に限定されます。

ピボット・テーブルはユーザーがリレーショナル・データベースを作成できるように開発されました。照会が簡単で、メモリを少なくし、より正確な結果を提供します。しかし、リレーショナルデータベースがなくても、より強力で信頼性の高いデータベース機能が必要な場合は、これを試してみてください。

[さらに読む：あなたの新しいPCには15の無料で優れたプログラムが必要です。 >インデックス、マッチ、およびインデックスの一致

Excelでは、INDEX関数は特定の位置（リスト、テーブル、データベース内）から項目を返します。

MATCH関数は、、テーブル、データベース）。また、INDEX-MATCH関数を使用すると、テーブルからデータを簡単に抽出できます。

INDEX関数の構文は、INDEX（array、row_num、[column_num]）です。配列は、使用しているセルの範囲です。 Row_numは、明らかに、探しているデータを含む範囲の行番号です。 Column_numは、探しているデータを含む範囲の列番号です。

MATCH関数の構文は、MATCH（lookup_value、lookup_array、[match_type]）です。 lookup_valueは、探している番号またはテキストです。値は、値、論理値、またはセル参照になります。 lookup_arrayは、使用しているセルの範囲です。 match_typeは、MATCH関数、つまり完全一致または最も近い一致を判定します。

A。 INDEX関数

この例では、有名なCommodore James Norringtonが、カリブ海の海賊船をすべて追跡するスプレッドシートを持っています。 Norringtonのリストは、船の戦闘編成によって編成され、彼の海図と一致します。船が前進しているのを見たら、彼は彼のスプレッドシートに索引式を入力し、船とその能力を特定することができます。この最初の質問では、Norringtonは進んでいる船の種類を知りたいと考えています。

1。クエリ（つまり、関数と結果）の場所（セルまたはセル範囲）を選択し、そのセルにカーソルを移動します。例：行18のセル。

2。 = INDEX（A2：I16

注：絶対参照を必要とする場合は、次のように入力します。この場合は、式をハードコーディングすることを意味するので、コピーした場合は範囲が変更されません）、各セル参照の後にF4キーを1回押します。

3.次に、コンマを入力して引数を区切って（つまり、別々の数式を）入力し、行番号とコンマ、列番号（yes）を入力します（通常の列の文字ではなく番号でなければなりません）、右のかっこ（または単にEnterキーを押して、Excelに最後の括弧を追加させます）。、15,2）。

注：行番号は、スプレッドシートの最初の番号ではなく、範囲内の最初の番号から始まります。たとえば、eve n Cavalleriaの海賊船はExcelの行16にありますが、実際には行の数が15行になっています。範囲はA2から始まり、I16を通るからです。 A2が行1の場合、A16は行15です）。

4. Norringtonが探していた船のタイプはWar Sloopであることに注意してください。

JD Sartain

データベース内の特定の情報を見つけるためにindex関数を使用します。 INDEXの範囲

ここで、このデータベーステーブルでもっと多くのことを行うことができます。範囲を再定義する必要はありません。簡単にするために、範囲を一度定義して名前を付けます。次に、範囲の名前を式に入れます。

1。 A2に進み、範囲A2〜I16。

2を強調表示します。式タブで、Defined Namesグループの

Define Name

を選択します。

3。ポップアップダイアログボックスで、[名前]フィールドボックスに範囲の名前を入力します。 4。次に、ワークブックまたはワークブック内のワークシートのいずれかであるスコープ（範囲がある場所）を入力します。 5。必要に応じてコメントを入力します。

6。最後に、参照先フィールドに正しい名前と範囲が表示されていることを確認し、

。

をクリックします。範囲が実際にExcelに保存されていることを確認したい場合は、この小さなテストを試してください。Ctrl + G（GoToコマンド）を押します。 GoToダイアログで Ships を選択し、

OK をクリックすると、Excelは範囲A2：I16。範囲の定義と保存方法を再表示します。 INDEX with SUM&AVERAGE formula Norringtonは艦隊の戦闘能力を評価しています。最初に、彼は海賊が何隻の大砲を持っているか、船1台あたりの大砲の平均数、そしてそれらの海賊船を全員乗せている乗組員の総数を知りたいと思っています。彼は以下の式を入力します： 1。 = SUM（INDEX（Ships、、8））は、334、大砲の総数、および

2です。 =平均（INDEX（船舶、、8））は22.27、船舶あたり約22.27大砲に相当する。

3。

船と8番の間にカンマ、スペース、カンマがあるのはなぜですか？そしてこれらの数字は何を意味していますか？（INDEX（Ships、、7））は2350で、すべての船上のすべての乗組員の総数です。 Norringtonはすべての行を必要とし、8は8番目の列を表します（これは列H、大砲です）。

いくつかの質問がありますなぜそれらの列の下部にSUMおよび/またはAVERAGE式を入力するだけではありませんか？この小さなスプレッドシートでは、そう簡単にできるでしょう。しかし、スプレッドシートが5000行と300列の場合、SUMとAVERAGEを使用してINDEX

JD Sartain

03 INDEXの式を使用することをお勧めします。

範囲の名前が付けられたら、Norringtonはこの同じブックを作成し、列Aのこれらのクエリを定義する説明と共に、列B（数式の代わりに結果を表示）にクエリ（数式）を書き込みます（注：列Cは列Bにある実際の数式を示しています）。

5000レコードの膨大なデータベースを視覚的に見たり、数式が計算されている間に数秒待つ必要はありません。彼は彼のクエリシートから必要なすべての情報を得ることができます。

JD Sartain

04 Commodore James Norringtonの海賊船の情報/クエリーシートです。 D。 MAXとのINDEX MATCH

今、Norringtonは最も人口の多い船に何人の海賊がいるのかを知りたいと思います。彼は最高の海賊版を得るためにINDEX with MAX formulaを使用しますが、どの船がそれを運んでいるかを知る必要もあります。そのため、MAXの式を持つINDEX / MATCHを使用して、海上で最も海賊がある船を見つけます。

1。 = MAX（INDEX（Ships、、7））は船舶の海賊数の最大値である300に等しい

2。 = INDEX（$ A $ 2：$ A $ 16、MATCH（MAX（船舶）、$ G $ 2：G $ 16,0））は、海賊版が最も多い船であるRoyal Jamesと同等です。 = INDEX（$ F $ 2：$ F $ 16、MATCH（MAX（船舶）、$ G $ 2：G $ 16,0））は、ロイヤル・ジェームスの船長Stede Bonnetと300,

JD Sartain の海賊乗組員