もともとExcelは真のデータベースとして設計されていませんでした。初期のデータベース機能は、量的にも質的にも限られていました。また、Excelデータベース内のすべてのレコードが一度に画面に表示されるため（つまり、メモリ上にすべて格納される）、Excelデータベースは非常に小さくなければなりませんでした。つまり、複数のフィールドに少数のレコード、あるいは少数のフィールドに多数のレコード、そして最小限の計算量で済む必要がありました。
VLOOKUP（垂直方向）とHLOOKUP（水平方向）は、データベースから特定の情報をクエリできる唯一の関数でした。例えば、売上高が1,000ドル以上5,000ドル未満のすべてのレコードを検索して抽出するクエリは、フラットファイル（データベースマトリックスが1つだけ）に対してのみ実行できました。
ピボットテーブルは、クエリの実行が容易で、メモリ使用量が少なく、より正確な結果を提供するリレーショナルデータベースを作成できるように開発されました。しかし、リレーショナルデータベースをお持ちでない、あるいは必要としていないけれど、より強力で信頼性の高いデータベース機能が必要な場合は、まずはピボットテーブルをお試しください。
Excel では、INDEX 関数は特定の位置 (リスト、テーブル、データベース内) から項目を返します。
MATCH関数は、リスト、テーブル、データベース内の値の位置を返します。また、INDEX関数とMATCH関数を併用することで、テーブルからのデータの抽出が簡単になります。
INDEX関数の構文は、INDEX(配列, 行番号, [列番号])です。配列は、操作するセル範囲です。行番号は、当然ながら、検索対象のデータを含む範囲内の行番号です。列番号は、検索対象のデータを含む範囲内の列番号です。INDEX関数は列の文字を認識しないため、左から数えた数字を使用する必要があります。
MATCH関数の構文は、MATCH(検索値, 検索範囲, [一致の種類]) です。検索値は、検索する数値または文字列で、値、論理値、またはセル参照を指定できます。検索範囲は、処理対象となるセルの範囲です。一致の種類によって、MATCH関数の判定結果（完全一致または最も近い一致）が決まります。
A. INDEX関数
この例では、有名なジェームズ・ノリントン提督がカリブ海の海賊船をすべて追跡したスプレッドシートを所有しています。ノリントンのリストは、海賊船の戦闘隊形に基づいて整理されており、その隊形は彼が作成した海図と一致しています。海賊船が前進しているのを見ると、彼はスプレッドシートにインデックス式を入力し、その船とその積載量を特定します。この最初のクエリで、ノリントンは前進している船の種類を知りたいのです。
1. クエリ（つまり関数と結果）の場所（セルまたはセル範囲）を選択し、カーソルをそのセルに移動します。例：18行目の任意のセル。
2. INDEX関数（先頭に等号を付けます）と開き括弧を入力し、次のようにデータベース/テーブル範囲を強調表示（または入力）します: =INDEX(A2:I16
注：絶対参照（この場合、数式をハードコードしてコピーしても範囲が変更されないようにする）を使用する場合は、各セル参照の後にF4キーを1回押してください。範囲全体を選択することもできます。範囲全体を選択した後にF4キーを1回押すだけで、完全な絶対参照記号が追加されます。
3. 次に、引数（つまり、数式の各要素）を区切るためにカンマを入力します。次に、行番号とカンマ、列番号（通常の列番号ではなく数字でなければなりません）と右括弧を入力します（または、Enterキーを押してExcelに閉じ括弧を追加させることもできます）。完成した数式は次のようになります：=INDEX($A$2:$I$16,15,2)。
注: 行番号は、スプレッドシートの最初の番号ではなく、範囲の最初の番号から始まります。例えば、海賊船「カヴァレリア」はExcelの行16にありますが、範囲はA2からI16までなので、数式では実際には15行目になります。A2が行1だとすると、A16は行15になります。
4. ノリントンが探していた船の種類は戦闘スループであることに注目してください。
JD サルティン インデックス関数を使用して、データベース内の特定の情報を検索します。
B. インデックス範囲
これで、このデータベーステーブルでさらに多くのことができるようになりました。何かを知りたいたびに範囲を再定義する必要はありません。簡単にするために、範囲を一度定義して名前を付けます。そうすれば、数式に範囲名を入力するだけで済みます。
1. A2 に移動し、A2 から I16 までの範囲を強調表示します。
2. [数式] タブで、[定義された名前] グループから[名前の定義]を選択します。
3. ポップアップ ダイアログ ボックスで、[名前] フィールド ボックスに範囲の名前を入力します。
4. 次に、範囲 (範囲が配置されている場所) を入力します。範囲は、ワークブックまたはワークブック内のワークシートの 1 つになります。
5. 必要に応じてコメントを入力します。
6. 最後に、「参照先」フィールドに正しい名前と範囲が表示されていることを確認し、「OK」をクリックします。
7. 範囲がExcelに確実に保存されていることを確認したい場合は、次の簡単なテストをお試しください。Ctrl+G（移動コマンド）を押します。移動ダイアログで「Ships」を選択し、 「OK」をクリックすると、Excelは範囲A2:I16を再び強調表示します。
C. SUMとAVERAGE式を使ったINDEX
ノリントンは艦隊の戦闘能力を評価している。まず、海賊が保有する大砲の数、1隻あたりの平均大砲数、そしてすべての海賊船に搭載されている乗組員の総数を知りたいと考え、以下の式を入力する。
1. =SUM(INDEX(Ships, ,8))は334で、大砲の総数であり、
2. =AVERAGE(INDEX(Ships, ,8)) は 22.27 となり、船 1 隻あたり約 22.27 門の大砲となります。
3. =SUM(INDEX(Ships, ,7)) は 2350 となり、これはすべての船の乗組員の合計数です。
Ships と数字 8 の間にカンマ、スペース、カンマがあるのはなぜでしょうか。また、これらの数字はどういう意味でしょうか。Ships は範囲 (カンマが続く) であり、Row 引数は、Norrington がすべての行を必要とするため空白 (またはスペース) です。また、8 は 8 番目の列 (列 H、Cannons) を表します。
なぜSUMやAVERAGEの式を列の下部に入力しないのかと疑問に思う人もいるかもしれません。この小さなスプレッドシートなら、確かに同じくらい簡単です。しかし、スプレッドシートに5000行300列ある場合は、INDEX関数を使う必要があります。
JD サルティン 03 SUM と AVERAGE を使用した INDEX 数式。
範囲に名前を付けたら、Norrington は同じブック内の空のスプレッドシートを開き、列 B (数式ではなく結果が表示されます) にクエリ (数式) を書き込み、列 A にそのクエリを定義する説明を書き込みます (注: 列 C には、列 B にある実際の数式が表示されます)。
5000件ものレコードからなる巨大なデータベースを目で確認したり、数式が計算されるのを数秒待ったりする必要はもうありません。必要な情報はすべてクエリシートから取得できます。スプレッドシートが大きくなるほど、特に数式が多い場合は動作が遅くなることに注意してください。
JD サルティン 04 ジェームズ・ノリントン提督の海賊船の情報/問い合わせシート。
D. インデックスマッチとMAX
さて、ノリントンは最も船員数の多い船に何人の海賊が乗っているか、そしてどの船が乗っているかを知りたいと考えています。彼はINDEXとMAXの式を使って最も多くの海賊を乗せている船を求めますが、同時にどの船が海賊を乗せているかも知る必要があります。そこで彼はINDEX/MATCHとMAXの式を使って、どの船に最も多くの海賊が乗っているかを調べます。
1. =MAX(INDEX(Ships, ,7))は300で、船の1つに乗船している海賊の最大数です。
2. =INDEX($A$2:$A$16, MATCH(MAX(Ships), $G$2:G$16, 0))は、最も多くの海賊を乗せた船であるロイヤル・ジェームズ号に等しい。
3. =INDEX($F$2:$F$16, MATCH(MAX(Ships), $G$2:G$16, 0))は、300人の海賊船員を率いるロイヤル・ジェームズ号の船長、ステッド・ボネットに等しい。
JD サルティン INDEX-MATCH と MAX を使用して、データベースから特定の情報を取得します。
