リスト（データベース）活用
以下はリスト（データベース）を活用方法についての考察です。
リスト（データベース）
たとえば、次のようなリスト（データベース）を毎日作成するとします。データを表現するための属性を見出しとして表現し、それをリストの第一行目とします。
仮にこのシートのシート名を「DataBase」とします。（金額はランダムに作成したので不自然です）
日付 顧客名 売上金額 経費項目 経費金額
7/28 a 604 い 1149
7/28 a 1955 あ 9704
7/29 d 5338 お 6056
7/30 b 5937 う 2865
7/31 c 7665 え 3421
7/31 b 7179 え 3601
7/31 b 6756 い 2076
8/1 d 9591 い 1922
8/2 c 9867 う 1385
8/2 e 7170 い 6468
8/2 a 8615 え 804
8/2 b 6410 お 538
8/2 c 8798 あ 9830
8/2 a 593 え 9936
8/2 b 5545 う 4281
8/3 d 8089 い 547
8/3 d 479 い 1284
8/5 c 8342 え 6551
8/6 a 9988 お 5095
【事例１】リストから特定レコード抽出
上記のリストから別シートへ、VLOOKUP関数で特定期間の特定顧客名のレコードの抽出を試みます。
検索条件セルに名前定義
開始日、 終了日、 顧客名を入力するセルへそれぞれ「開始日」「終了日」「顧客名」という名前をつけます。
名前定義はセル選択して [名前ボックス] が簡易。
また、 [挿入]＞[名前]＞[定義] でもできます。
見出しを作成してある場合はメニューの[挿入]＞[名前]＞[作成]が簡単でしょう。
名前定義は必須ではありませんが、ここでは数式中で参照セル指定を単純化するため、また、各ユーザーの入力位置の違いを回避するために名前定義します。
検索KEYを作成
検索の補助とするためのKEY列を追加します。この列はVLOOKUP関数で検索するための検索値を表示するために使用します。
[A] [B] [C] [D] [E] [F]
[1] KEY 日付 顧客名 売上金額 経費項目 経費金額
[2] 7/28 a 604 い 1149
[3] 7/28 a 1955 あ 9704
[4] 7/29 d 5338 お 6056
[5] 7/30 b 5937 う 2865
[6] 7/31 c 7665 え 3421
[7] 7/31 b 7179 え 3601
[8] 7/31 b 6756 い 2076
[9] 8/1 d 9591 い 1922
[10] 8/2 c 9867 う 1385
[11] 8/2 e 7170 い 6468
[12] 8/2 a 8615 え 804
[13] 8/2 b 6410 お 538
[14] 8/2 c 8798 あ 9830
[15] 8/2 a 593 え 9936
[16] 8/2 b 5545 う 4281
[17] 8/3 d 8089 い 547
[18] 8/3 d 479 い 1284
[19] 8/5 c 8342 え 6551
[20] 8/6 a 9988 お 5095
KEY列には次のような数式を入力します。
=(C2=顧客名)*(B2>=開始日)*((B2<=終了日)+MAX($A$1:A1))
この式をレコード数分コピーします。フィルハンドル（セル選択時の右下の小さい■）ダブルクリックが簡易です。
この式は、そのレコードが
「顧客名と指定した期間に当てはまるかどうか」＋「そのレコードまでのKEY列の最大値」
を計算しています。この時点で「開始日」「終了日」「顧客名」に何も入力されていない状態ではKEY列には「0」が表示されます。
リストに名前定義
KEYを含めたにシート名「DataBase」のリスト範囲に次のように名前定義します。
- 名前
- LIST
- 参照範囲
- =INDIRECT("DataBase!A1:F"&COUNTA(DataBase!$B:$B))
この設定は、次の効果をねらいます。
- レコードが増えても対象リスト範囲を自動拡張する
- 数式入力の際の入力単純化
- 以降に説明する数式のわかりやすさ
この名前定義も必須ではありません。以降に解説する数式で使用する[LIST]という名前定義の代わりに、大きめなセル範囲を直接指定しおいても、その範囲からはみ出さない限り、問題は起こりません。
抽出するシートを作成
データを抽出するシートが次のような構成になっているとします。
下記の構成では A2:C2 の範囲がそれぞれ 「開始日」「終了日」「顧客名」 という名前を定義したセルです。
[A] [B] [C] [D] [E]
[1] 開始日 終了日 顧客名
[2] 7月31日 8月10日 b
[3] 日付 顧客名 売上金額 経費項目 経費金額
[4]
[5]
[6]
[7]
ためしに上記のように日付と顧客名を入力してみると、 DataBase シートは次のように表示されます。
[A] [B] [C] [D] [E] [F]
[1] KEY 日付 顧客名 売上金額 経費項目 経費金額
[2] 0 7/28 a 604 い 1149
[3] 0 7/28 a 1955 あ 9704
[4] 0 7/29 d 5338 お 6056
[5] 0 7/30 b 5937 う 2865
[6] 0 7/31 c 7665 え 3421
[7] 1 7/31 b 7179 え 3601
[8] 2 7/31 b 6756 い 2076
[9] 0 8/1 d 9591 い 1922
[10] 0 8/2 c 9867 う 1385
[11] 0 8/2 e 7170 い 6468
[12] 0 8/2 a 8615 え 804
[13] 3 8/2 b 6410 お 538
[14] 0 8/2 c 8798 あ 9830
[15] 0 8/2 a 593 え 9936
[16] 4 8/2 b 5545 う 4281
[17] 0 8/3 d 8089 い 547
[18] 0 8/3 d 479 い 1284
[19] 0 8/5 c 8342 え 6551
[20] 0 8/6 a 9988 お 5095
つまり、条件が当てはまらないレコードには「0」が、条件に当てはまるレコードに対して連番が作成されます。
[A] [B] [C] [D] [E]
[1] 開始日 終了日 顧客名
[2] 7月31日 8月10日 b
[3] 日付 顧客名 売上金額 経費項目 経費金額
[4]
[5]
[6]
[7]
こちらの表で該当するレコードを抽出する数式を入力します。
A4セルへ下記数式を入力します。
=IF(COUNTIF(LIST,ROW(A1)),VLOOKUP(ROW(A1),LIST,COLUMN(B1),FALSE),"")
この数式を必要な範囲へ（多少広い範囲へ）コピー、貼付してみます。すると次のように抽出できます。
[A] [B] [C] [D] [E]
[1] 開始日 終了日 顧客名
[2] 7月31日 8月10日 b
[3] 日付 顧客名 売上金額 経費項目 経費金額
[4] 2006/7/31 b 7179 え 3601
[5] 2006/7/31 b 6756 い 2076
[6] 2006/8/2 b 6410 お 538
[7] 2006/8/2 b 5545 う 4281
[8]
[9]
[10]
【事例２】リストから利益金額算出
【事例１】で作成した表で売上金額と経費金額の差額で計算することは出来ますが、ここはひとつ、 DataBase シートから直接計算して見ましょう。
=SUMIF(DataBase!A:A,">0",DataBase!D:D)-SUMIF(DataBase!A:A,">0",DataBase!F:F)
KEY列が０より大きいレコードの売上金額と経費金額の差額計算します。
以上のようなKEY列を作成したりしなくても、配列を用いた関数で一発で求めることも出来ます。が、私はそのような関数を理解する前段として、計算を助ける列（作業列）を作成した方法も大切であると考えます。
