Excel講座

データベースの利用

本章で用いる実習用ファイル

リスト形式

Excelを簡易的なデータベースとして用いるときには「リスト形式」でデータを入力する。リスト形式では1行に一件のデータを入力する。

リスト形式の表は随時データを追加することができ、大量のデータを格納できる。

ABCDEFG
1ID名前グループ血液型生年月日身長
22203小片リサつばきファクトリーA1998/11/5159
31601山岸理子つばきファクトリーB1998/11/24155
41905藤井梨央こぶしファクトリーB1999/3/4150
52205広瀬彩海こぶしファクトリーO1999/8/4156
62003新沼希空つばきファクトリーO1999/10/20160.2
72206谷本安美つばきファクトリーB1999/11/16157.5
81602野村みな美こぶしファクトリーB2000/2/10155
91201小川麗奈こぶしファクトリーA2000/3/27159
101704岸本ゆめのつばきファクトリーB2000/4/1167
111102浜浦彩乃こぶしファクトリーAB2000/4/26162
121103田口夏実こぶしファクトリーA2000/7/21151
132207浅倉樹々つばきファクトリーAB2000/9/3153
142401小野瑞歩つばきファクトリーO2000/9/28162
151706和田桜子こぶしファクトリーB2001/3/8163.5
162208井上玲音こぶしファクトリーO2001/7/17161.5
172209小野田紗栞つばきファクトリーO2001/12/17153.2
182405秋山眞緒つばきファクトリーB2002/7/29160
19

リスト形式は以下の構造を持つ。

リスト形式の表
列:「フィールド」・1行目:「フィールド名」・行:「レコード」
フィールド
各列に同じ種類のデータを入力する。1列のデータの連なりを「フィールド」と呼ぶ。項目。
フィールド名
フィールドごとに付された名前。項目名。
レコード
1行に一件分のデータを入力する。各行のデータは相互に独立している(他の行を集計した行はレコードではない)。1行のデータの連なりを「レコード」と呼ぶ。

リスト形式の表では他のレコードと重ならない固有の(一意な;Uniqueな)値を持つフィールドを持たせるのが一般的である(今回の表では「ID」)。

課題:リスト形式

以下の表から「リスト形式」の表をすべて選べ。

  1. ABCDE
    1日付担当費目金額
    212月1日藤本交通費5300
    312月1日藤本接待費2300
    412月1日藤本消耗品費1500
    512月1日松浦交通費2400
    612月1日松浦消耗品費850
    712月2日藤本交通費2000
    812月2日藤本接待費2500
    912月2日藤本消耗品費1250
    1012月2日松浦交通費1250
    1112月2日松浦接待費870
    1212月2日松浦消耗品費650
    1312月3日藤本接待費1560
    1412月3日藤本交通費2500
    1512月3日藤本消耗品費1500
    1612月3日松浦交通費750
    1712月3日松浦接待費590
    1812月4日藤本交通費5500
    1912月4日藤本接待費1700
    2012月4日藤本消耗品費1200
    2112月4日松浦交通費950
    2212月4日松浦接待費850
    23
  2. ABCDEF
    1瀬田店大宮店深草店各月合計
    21月12500240001800054500
    32月15400220002150058900
    43月25400260001650067900
    54月13500240001750055000
    65月14000265001850059000
    76月17000270001750061500
    8各店平均16300.0024916.6718250.0059466.67
    9
  3. ABCDE
    1賛成反対計
    2男176256432
    3女362157
    4計212277489
    5

課題:アンケート調査(xlsx形式)

アンケート調査の回答結果をリスト形式の表で管理したい。

「アンケート入力」シートに下二件のデータを入力せよ。

問1 あなたはつばきファクトリーのCDを購入していますか
  1. 毎回購入している
  2. 8割ぐらい購入している
  3. 半分ぐらい購入している
  4. たまに購入する
  5. 購入したことがない
問2 特に強く応援しているメンバーは誰ですか
  1. 山岸理子
  2. 小片リサ
  3. 新沼希空
  4. 谷本安美
  5. 岸本ゆめの
  6. 浅倉樹々
  7. 小野瑞歩
  8. 小野田紗栞
  9. 秋山眞緒
問3 1年間につばきファクトリーに掛ける費用はいくらぐらいですか。
      20万    円
問4 購入したことがあるグッズすべて教えて下さい
  1. シングルCD
  2. アルバムCD
  3. 写真
  4. ポスター
  5. フィギュアスタンドキーホルダー
  6. その他(      ペンライト・うちわ        )
問5 あなたの年齢・性別を教えて下さい
  1. 男
  2. 女

(  36   )歳

問1 あなたはつばきファクトリーのCDを購入していますか
  1. 毎回購入している
  2. 8割ぐらい購入している
  3. 半分ぐらい購入している
  4. たまに購入する
  5. 購入したことがない
問2 特に強く応援しているメンバーは誰ですか
  1. 山岸理子
  2. 小片リサ
  3. 新沼希空
  4. 谷本安美
  5. 岸本ゆめの
  6. 浅倉樹々
  7. 小野瑞歩
  8. 小野田紗栞
  9. 秋山眞緒
問3 1年間につばきファクトリーに掛ける費用はいくらぐらいですか。
  35,000   円
問4 購入したことがあるグッズすべて教えて下さい
  1. シングルCD
  2. アルバムCD
  3. 写真
  4. ポスター
  5. フィギュアスタンドキーホルダー
  6. その他(                                            )
問5 あなたの年齢・性別を教えて下さい
  1. 男
  2. 女

(   23    )歳

  • 各問の詳細な文言は記載不要(問番号で照合させる)
  • 問の選択肢は番号のみ入力する

課題:受注票(xlsx形式)

高橋商店受注票をリスト形式の表で管理したい。「受注票入力」シートにデータを入力せよ。

高橋商店受注票
受注番号 1
明細番号商品番号商品名単価数量
1A-1越前ガニ36002
2A-2若狭河豚130001
3B-2ソースカツ丼25001
4
5
受注番号 2
明細番号商品番号商品名単価数量
1A-1越前ガニ36001
2B-1越前そばセット35003
3B-2ソースカツ丼25001
4
5
受注番号 3
明細番号商品番号商品名単価数量
1B-2ソースカツ丼25005
2
3
4
5
受注番号 4
明細番号商品番号商品名単価数量
1A-1越前ガニ36001
2B-1越前そばセット35001
3B-2ソースカツ丼25003
4C-1若狭牛100001
5D-1梅ワイン42002
  • 明細番号は商品番号以下が記載されている場合のみ記載する
  • 商品名は商品番号と照合させるので、記載不要

並べ替えとフィルター

リスト形式の表は条件に従ってレコードを抽出(フィルタ機能)したり、特定のフィールドを基準に並べ替えることができる。

[並べ替えとフィルター]ボタン
「昇順」・「降順」

[ホーム][編集][並べ替えとフィルター]

[並べ替えとフィルター]グループ
「並べ替え」・「フィルタ-」

[データ][並べ替えとフィルター]

[ホーム]リボンと[データ]リボンに並べ替えやフィルタ機能ボタンが用意されている。どちらからアクセスしても同じ機能を呼び出せる。

並べ替え

「並べ替え」機能は元データ自体を並べ替えたいときにもちいる。元データが書き換わるので注意が必要である。

ABCDEFG
1ID名前グループ血液型生年月日身長
22203小片リサつばきファクトリーA1998/11/5159
31601山岸理子つばきファクトリーB1998/11/24155
41905藤井梨央こぶしファクトリーB1999/3/4150
172209小野田紗栞つばきファクトリーO2001/12/17153.2
182405秋山眞緒つばきファクトリーB2002/7/29160
19
  1. 並べ替えたい基準となるフィールド(列)のどこかのセルをアクティブにする(今回は「ID」を基準に並べ替える)
  2. [ホーム]リボンの[並べ替えとフィルター]ボタンないしは[データ]リボンの[並べ替えとフィルター]グループの[昇順]ないし[降順]を選択する(今回は[昇順])
ABCDEFG
1ID名前グループ血液型生年月日身長
21102浜浦彩乃こぶしファクトリーAB2000/4/26162
31103田口夏実こぶしファクトリーA2000/7/21151
41201小川麗奈こぶしファクトリーA2000/3/27159
172401小野瑞歩つばきファクトリーO2000/9/28162
182405秋山眞緒つばきファクトリーB2002/7/29160
19▲
複数項目での並べ替え

「血液型」のように重複する値があるフィールドで並べ替えると、同じ値内の並びは並べ替え前の状態に依存する。

  • その前が「生年月日」昇順に並んでいたら、同じ「血液型」で「生年月日」が早い順に並ぶ。
    ABCDEFG
    1ID名前グループ血液型生年月日身長
    22203小片リサつばきファクトリーA1998/11/5159
    31201小川麗奈こぶしファクトリーA2000/3/27159
    41103田口夏実こぶしファクトリーA2000/7/21151
    172208井上玲音こぶしファクトリーO2001/7/17161.5
    182405秋山眞緒つばきファクトリーB2002/7/29160
    19▲▲
  • その前が「ID」昇順に並んでいたら、同じ「血液型」内で「ID」が小さい番号順に並ぶ。
    ABCDEFG
    1ID名前グループ血液型生年月日身長
    21103田口夏実こぶしファクトリーA2000/7/21151
    31201小川麗奈こぶしファクトリーA2000/3/27159
    42203小片リサつばきファクトリーA1998/11/5159
    172209小野田紗栞つばきファクトリーO2001/12/17153.2
    182401小野瑞歩つばきファクトリーO2000/9/28162
    19▲▲

※大きなまとまりを作りたい項目を最後に並べ替える。

課題:並べ替え グループ(xlsx形式)

グループ(昇順)で並べ替えよ。ただし同グループ内では血液型(昇順)、ID(昇順)の順序で並べ替えよ。

並べ替え:SORTBY(元データ,基準1,順序1,基準2,順序2,…)

SORTBYは元データを並べ替え基準にしたがって並べ替えた結果(配列)を返す関数である。

  • 順序が1の時は昇順
  • 順序が-1の時は降順
ABCDEFG
1ID名前グループ血液型生年月日身長
22203小片リサつばきファクトリーA1998/11/5159
31601山岸理子つばきファクトリーB1998/11/24155
41905藤井梨央こぶしファクトリーB1999/3/4150
172209小野田紗栞つばきファクトリーO2001/12/17153.2
182405秋山眞緒つばきファクトリーB2002/7/29160
19
20ID名前グループ血液型生年月日身長
21=SORTBY(A2:F18,D2:D18,1,A2:A18,1)
22

血液型昇順、同じ血液型のなかではID昇順に並べ替えた表(スピル配列)をセル範囲A21:F37に出力する。

課題:並べ替え SORTBY(xlsx形式)

SORTBY関数を用いてグループ(昇順)で並べ替えた表を新たに作成せよ。ただし同グループ内では血液型(昇順)、ID(昇順)の順序で並べ替えよ。

フィルター

表の任意のセルをアクティブにして、[フィルター]ボタンを押すと、フィールド名のセルに▼ボタンが付く。

[フィルター]ボタン
[ホーム]リボンのフィルターボタンと[データ]リボンのフィルターボタン
ABCDEFG
1ID名前グループ血液型生年月日身長
22203小片リサつばきファクトリーA1998/11/5159
31601山岸理子つばきファクトリーB1998/11/24155
41905藤井梨央こぶしファクトリーB1999/3/4150
172209小野田紗栞つばきファクトリーO2001/12/17153.2
182405秋山眞緒つばきファクトリーB2002/7/29160
19

この画面がオートフィルターが設定された画面である。各フィールドの▼ボタンをクリックするとメニュー画面が表示される。このメニューはフィールドデータの種類によって変化する。

この画面で「並べ替え」のほか、条件にしたがったレコードの抽出ができる。

文字列のフィルター
フィルターメニュー画面
数値のフィルター
フィルターメニュー画面
日付のフィルター
フィルターメニュー画面

ここからさらに詳細にフィルターを設定する[フィルターオプション]ダイアログボックスを呼び出せる。

フィルターオプション
[フィルターオプション]ダイアログボックス
課題:フィルター(xlsx形式)

2000/1/1以降に生まれた血液型が「AB」型であるメンバーを抽出せよ。