第 2 章 データベース機能

第 2 章 データベース機能
第2章
データベース機能
この章では、ワークシートのデータをデータベースとして有効活用する方法、レコードの追加や削除、また必要なデータの検索や
抽出、集計機能などのデータベース機能について学習します。
STEP 1：リストの作成
データベースとは、特定の目的で集められたデータの集まりのことです。住所録や商品台帳、社員名簿などがデータベースに
あたります。Excel では、先頭行に項目名があり、その下の行以降にデータが 1 行に 1 件という形式で入力されている表を
『リスト』といいます。Excel のデータベース機能を効果的に利用するには、リスト形式で表を管理し、リストとして定義
する必要があります。
≪リストの作成≫
セル範囲をリストとして定義すると、リストに含まれるデータだけを対象に並べ替えや抽出、印刷、グラフ作成などを行うこ
とができます。リストとして定義するには、[データ]メニューの[リスト]から[リストの作成]をクリックし、[リストの作成]
ダイアログボックスでリストに変換したい範囲を指定します。
≪リストの構成≫
リスト内の任意のセルを選択すると、リスト領域を囲む青い枠線が太く表示され、[オートフィルタ矢印]ボタン、[挿入行]、
[リスト]ツールバーなどが表示されます。
『フィールド』
列ごとに入力される
同種類のデータ
『フィールド名』
各フィールド(列)の先頭に入力される項目名。
フィールド名の右側に[オートフィルタ矢印]ボタン
が表示されます。
『レコード』
行ごとに入力される
1 件分のデータ
『挿入行』
この行にデータを入力すると、
自動的にリストの範囲が広が
ります。
『サイズ変更ハンドル』
ドラッグ操作でリストのサイズ(範囲)
を変更することができます。
„ リスト作成時の注意事項
・同じ列に同じ種類の項目が入力されるようにリストを設計しましょう。
・リストの先頭行には、必ずフィールド名を作成し、フィールド名とデータを確実に区別するためにフィールド名の
セルには、データ部分と異なる書式を設定しましょう。
・リスト内では空白行や空白列を使用しないようにしましょう。
・ワークシートのリストと他のデータの間には、少なくとも 1 つ以上の空白列や空白行を挿入するようにしましょう。
10
第 2 章 データベース機能
STEP 2：フォームの入力
≪フォームの構成≫
タイトルバーにはシート名
が表示されます。
総レコード数と現在何番目の
レコードが表示されているか
を確認することができます。
フィールド名
数式が入力されているフィールドは、編集
用のボックスは表示されず、フォームから
編集することはできません。
A)
フォームに同時に表示されるフィールドは 32 個までです。
B)
フィールドの並び順は､シート上のリストのフィールド名順となります。
C)
次のフィールドにカーソルを移動するには、 Tab キーを押します。前のフィールドに移動するには、 Shift ＋ Tab
キーを押します。
D)
Enter キーを押すと次のレコードに移動します。
E)
フォームを使用して新規にデータを入力した場合は、リストの最終行にデータが追加されます。
F)
他のレコードに移動したり、フォームを閉じると新しく入力したレコードが追加されます。
≪特定のレコードの表示≫
特定のレコードを表示するには、次の 3 種類があります。
1.
1 レコードずつ移動するには、スクロールバー上下にある矢印ボタンをクリックします。
2.
一度に 10 レコードずつ移動するには、スクロールバーのつまみと矢印ボタンの間をクリックします。
3.
リスト内の次のレコードに移動するには、 次を検索 ボタンをクリックします。リスト内の前のレコードに移動するに
は、 前を検索 ボタンをクリックします。
※
検索条件が指定されている場合は、条件に一致するレコードの中で移動します。
11
第 2 章 データベース機能
第２章のトレーニング（１）
ここでは、既に入力されたデータをリストとして定義し、フォームの使い方を練習しましょう。
データベース機能を使用する場合は、リスト内の任意のセルをアクティブにして行いましょう。
STEP 1
Excel を起動し、My Documents から“2 章(1)データベース.xls”ブックを開きましょう。
STEP 2
4 行目のリストのフィールド名までが常に見えるようにウィンドウを固定し、102 行目の最後のレコードが見えるようにスクロール
しましょう。
STEP 3
セル A4 から K102 のデータをリストとして定義しましょう。(先頭行は、リストの見出しとして使用するようにします)
※
リスト領域を示す青い枠線は、リスト内の任意のセルをアクティブにすると、『挿入行』を含むセル A4 から K103 まで
を囲み、リスト外のセルをアクティブにするとセル A4 から K102 を囲んで表示されます。
STEP 4
フォームを使用してリストにデータを追加し、リストサイズが変更されることを確認しましょう。
①
新しいデータとして次のデータをリストに追加します。
※
②
‘フリガナ’・‘勤続年数’・‘世代’・‘支給額’フィールドは、計算式により自動入力されます。
社員 No.
氏名
部署名
入社日
性別
基本給
等級
1134
斉藤里佳子
経理部
2005/9/5
女
220000
1
103 行目にデータが追加され、リストサイズが変更されたことを確認します。
STEP 5
フォームを使用して特定のデータを表示し、データの一部を変更しましょう。
①
社員 No.1005
②
‘等級’フィールドを“1”から「3」に変更し、フォームを閉じます。
③
セル J6 に入力されている早川孝弘さんの‘等級’が“3”に変更されていることを確認します。
早川孝弘さんのデータを表示します。
STEP 6
フォームを使用して特定のデータを検索し、削除しましょう。
①
“開発部”の“男”のデータを検索します。
②
検索されたデータの中から社員 No.1027
③
22 行目に入力されていた正田弘信さんのデータが削除され、社員 No.1027 が欠番になっていることを確認します。
STEP 7
上書き保存して、Excel を終了しましょう。
12
正田弘信さんのデータを削除し、フォームを閉じます。
第 2 章 データベース機能
STEP 3：並べ替え
≪リスト定義済みのデータの並べ替え≫
複数のフィールドを基準にしたり、オプションを設定する場合は、[並べ替え]ダイアログボックスを使用します。
≪リスト定義していないデータの並べ替え≫
＜操作手順 1＞
[昇順で並べ替え]ボタン、[降順で並べ替え]ボタン
1.
並べ替えの基準となるフィールド(列)の任意のセルをアクティブにします。
2.
[標準]ツールバーの[昇順で並べ替え]
＜操作手順 2＞
1.
ボタン、または[降順で並べ替え]
ボタンをクリックします。
[並べ替え]ダイアログボックス
リスト内の任意のセルをアクティブにします。
※
表の一部分を並べ替えるときは、あらかじめそのセル範囲を選択して行います。
2.
[データ]メニューの[並べ替え]をクリックし、[並べ替え]ダイアログボックスを表示します。
3.
[データ範囲の先頭行]で先頭行をタイトル行として並べ替えから除外するか、データとして
並べ替えるかオプションボタンを選択します。
4.
OK ボタンをクリックします。
≪並べ替えの順序の規則≫
しょうじゅん
こうじゅん
昇 順 (小さい順)
数
数
値
負の最小値 ～ ０ ～ 正の最大値
正の最大値 ～ ０ ～ 負の最小値
値
日
付
古い順
新しい順
数
値
0 ～ 9
9 ～ 0
文
記号
字
列
アルファベット
か
な
(スペース) ! " # $ % & ( ) * , . / : ; ? @
昇順の逆
[]^_`{|}~+<=>¥
A ～ Z (小文字~大文字)
Z ～ A (大文字~小文字)
あ ～ ん (清音～濁音～半濁音)
ん ～ あ(半濁音～濁音～清音)
空白
※
降 順 (大きい順)
常にリストの最後
文字列と共に入力されている数値は、左から右に 1 文字ずつ順番に並べ替えられます。
例えば、「A100」と入力されているセルは、昇順にすると「A1」より後、「A11」より前に配置されます。
„ [並べ替えオプション]ダイアログボックス
[並べ替えオプション]ダイアログボックスを使用すると[最優先されるキー]に指定したフィールド(列)のデータを
『ユーザー設定リスト』の項目順で並べ替えたり、列単位に並べ替えたりすることができます。
例えば、月が入力されている列を昇順に並べ替えた場合、「10 月・11 月・12 月・1 月…9 月」という順序で並んでしまいます。
これを正しく並べ替えるには、[並べ替えオプション]ダイアログボックスで、[並べ替え順序の指定]ボックスから[1 月,2 月,3 月…]
の項目を選択します。
オプション ボタンをクリック
すると表示されます。
※
『ユーザー設定リスト』の項目とは、オートフィルタ機能で自動入力ができる曜日や月などの項目のことで、[ツール]
メニューの[オプション]をクリックして表示される[オプション]ダイアログボックスの[ユーザー設定リスト]タブから
ユーザーが独自に作成することもできます。
13
第 2 章 データベース機能
STEP 4：集計
≪リスト内の集計≫
リスト定義したデータは、[リスト]ツールバーを使用してリストに集計行を表示し、集計方法を選択してフィールドごとの集
計を簡単に行うことができます。
≪自動集計≫
特定のフィールドをグループごとに集計する場合は、『自動集計』を使用します。
『自動集計』を行うには、あらかじめ集計するフィールドを基準に『並べ替え』を実行してデータをグループごとにまとめ、
リスト内の任意のセルをアクティブにして『自動集計』を行います。
集計を実行すると、リストにはグループごとに合計行(小計・総計)が挿入され、さらにアウトラインも作成されます。
※
リスト定義したデータに自動集計を行うことはできません。リスト定義を解除したい場合は、リスト内の任意のセルを
アクティブにし、[リスト]ツールバーの[リスト]、または[データ]メニューの[リスト]から[範囲に変換]をクリックして
標準の範囲に変換します。
„ アウトライン
アウトラインとは、ワークシートの行や列をグループ化して折りたたみ、表示と非表示を切り替えることができる機
能です。自動集計を行うと行方向に自動的にアウトラインが作成され、リストの詳細データを折りたたんで小計行や
総計行だけを表示したり、必要な部分だけ詳細データを展開して参照することができます。
またアウトラインは、グループ化したい行や列を選択し、[データ]メニューの[グループとアウトラインの設定]から[グループ化]
をクリックして手動で作成することもできます。
„
アウトライン記号
次の例では、自動集計により自動作成された行方向のアウトラインに加え、C 列から D 列を必要に応じて非表示にできるように
グループ化してアウトラインを設定しています。また行方向では、“日進フラワー”のみ詳細データを表示しています。
[レベルバー]
同じレベルのグループであることを示し、
クリックすると詳細データを非表示にする
ことができます。
[レベル記号]
クリックし、各レベルまでを表
示します。
最下位レベル(ここでは
)を
クリックすると全てのデータ
を表示することができます。
[詳細表示記号]
クリックし、非表示になっている詳細
データを表示することができます。
14
[詳細非表示記号]
クリックし、グループ内の詳細データを
非表示にすることができます。
第 2 章 データベース機能
„ 可視セルの選択
アウトラインなどを利用して行や列を非表示にしていても、その範囲をコピーすると非表示になっている行や列を含
めてコピーされます。非表示になっている範囲を除いた『可視セル』のみをコピーして別表を作成したい場合などは、
ジャンプ機能を使用すると簡単に選択することができます。
＜操作手順＞
1.
目的のセル範囲を選択します。
2.
[編集]メニューの[ジャンプ]をクリックし、[ジャンプ]ダイアログボックスを表示します。
3.
4.
セル選択 ボタンをクリックし、[選択オプション]ダイアログボックスを表示します。
[可視セル]オプションボタンを選択し、 OK ボタンをクリックします。
15
第 2 章 データベース機能
第２章のトレーニング（２）
ここでは、[リスト]ツールバーを利用した集計とグループごとの自動集計について練習しましょう。
STEP 1
Excel を起動し、My Documents から“2 章(2)並べ替えと集計.xls”ブックを開きましょう。
STEP 2
“販売実績”シートのセル A1 から I80 のデータをリストとして定義しましょう。
STEP 3
[オートフィルタ矢印]ボタンを使用して、‘利益合計’フィールドを大きい順に並べ替えましょう。
STEP 4
リストに集計行を表示して、各フィールドに合計や平均を求めましょう。
①
[リスト]ツールバーの[集計行の表示/非表示]
②
セル B82 に‘店舗名’フィールドのデータの個数を求めます。
③
セル D82 に‘単価フィールドの平均を求めます。
④
セル E82 に‘数量’フィールドの合計を求めます。
⑤
セル F82 に‘金額’フィールドの合計を求めます。
※
ボタンをクリックして、集計行を表示します。
初期設定では、リストの右端列に集計結果(数値の列=合計、文字の列=データの個数)が表示されます。
STEP 5
集計行を非表示にして、リスト定義を解除しましょう。
①
[リスト]ツールバーの[集計行の表示/非表示]ボタンをクリックします。
②
[リスト]ツールバーの[リスト]から[範囲に変換]をクリックし、
確認メッセージの はい ボタンをクリックします。
STEP 6
‘店舗名’ごとに‘数量’・‘金額’・‘利益合計’フィールドの合計を自動集計しま
しょう。
①
[標準]ツールバーの[昇順で並べ替え]
ボタンを使用して‘店舗名’
フィールドを基準に昇順で並べ替えます。
16
第 2 章 データベース機能
②
[データ]メニューの[集計]を使用し、‘店舗名’フィールドを基準に‘数量’・‘金額’・‘利益合計’フィールドの“合計”を
自動集計します。
③
行の[レベル記号]を使用し、集計したデータを 2 レベルまで表示します。
STEP 7
STEP 6 で行った集計結果を“店舗別集計”シートの表にコピーしましょう。
①
“販売実績”シートをアクティブにし、‘数量’フィールド、‘金額’フィールド、
‘利益合計’フィールドの集計結果(セル E18 からセル F87 とセル I18 から I87)
を範囲選択して、『可視セル』を選択します。
②
選択した『可視セル』をコピーし、“店舗別集計”シートのセル B4 から D10 に
値として貼り付けます。
STEP 8
STEP 6 で行った“販売実績”シートの集計をリストから削除しましょう。
①
集計したリスト内の任意のセルをアクティブにします。
②
[データ]メニューの[集計]をクリックし、[集計の設定]ダイアログボックスの すべて削除 ボタンをクリックします。
STEP 9
‘商品名’フィールドを昇順、‘店舗名’フィールドを昇順、‘単価’フィールドを降順で並べ替えましょう。
①
リスト内の任意のセルをアクティブにします。
②
[データ]メニューの[並べ替え]をクリックし、[並べ替え]ダイアログボックスを表示して次の設定を行い、 OK ボタンを
クリックします。
¾
最優先されるキー：‘商品名’の昇順
¾
2 番目に優先されるキー：‘店舗名’の昇順
¾
3 番目に優先されるキー：‘単価’の降順
2 番目
最優先
3 番目
STEP10
‘商品名’フィールドごとに‘数量’
・
‘金額’フィールドの合計を集計しましょう。([集計するフィールド]の[利益合計]チェックボッ
クスはオフにします)
17
第 2 章 データベース機能
STEP11
STEP10 で行った集計に、‘店舗名’フィールドごとの‘数量’・‘金額’フィールドの合計の集計
を追加しましょう。([現在の集計表と置き換える]チェックボックスをオフにして集計します)
STEP12
行の[レベル記号]を使用し、集計したデータを 3 レベルまで表示します。
STEP13
上書き保存して、Excel を終了しましょう。
18
第 2 章 データベース機能
STEP 5：オートフィルタ
『フィルタ』とは、特定の条件に該当するレコードをリストから抽出する機能です。オートフィルタを使用すると、リストの
必要な部分だけを簡単に抽出し、表示することができます。
≪リスト定義していないデータのオートフィルタ≫
＜操作手順＞
1.
データ内の任意のセルをアクティブにします。
2.
[データ]メニューの[フィルタ]から[オートフィルタ]をクリックし、列見出しに[オートフィルタ矢印]ボタンを表示します。
3.
オートフィルタを解除するには、再度、[データ]メニューの[フィルタ]から[オートフィルタ]をクリックします。
※
リスト定義していない場合、オートフィルタは 1 つのワークシートにつき 1 箇所のデータのみに有効です。
≪リストのすべてのデータを表示≫
抽出したリストからすべての抽出条件を一度に解除し、非表示になっているデータ(行)をすべて表示するには、[データ]
メニューの[フィルタ]から[すべて表示]をクリックします。
※
オートフィルタは、解除されません。
STEP 6：オートフィルタオプション
≪トップテンオートフィルタ≫
フィールド内のデータの上位または下位から、項目数あるいはパーセンテージの範囲を指定し、データを抽出します。
抽出条件は、データを抽出するフィールドの[オートフィルタ矢印]ボタンをクリックし、一覧から[(トップテン)]をクリックし
て表示される[トップテンオートフィルタ]ダイアログボックスから指定します。
※
[(トップテン)]では、条件に合ったデータの抽出のみが行われます。抽出されたデータの並び順を変更したい場合は、
さらに[並べ替え]を行います。
スピンボタン
をクリックして数値
を変更するか、直接数値を入力します。
[上位]か[下位]を指定します。
[項目]か[パーセント]を指定します。
≪[オートフィルタオプション]ダイアログボックス≫
[オートフィルタオプション]ダイアログボックスを使用すると、1 つのフィールドに対して、2 つの条件または数値が「～以
上」や「～より小さい」などのあいまいな条件で抽出することができます。
2 つの条件を与える場合には、2 つの条件を結ぶ接続詞(AND または OR)が必要です。選択した接続詞により抽出結果が異な
ります。
AND 条件
OR 条件
両方の条件を共に満たしているものを
例：A AND B
抽出したい場合に使用します。
A であり、なおかつ B である
どちらか一方の条件を満たしている
例：A OR B
ものを抽出したい場合に使用します。
A あるいは、B である
『フィールド名』
比較演算子を一覧から
指定します。
下向き三角ボタンをクリックして、条件
を一覧から選択するか、ボックスに直接
入力して指定します。
条件を 2 つ指定する場合は、
[AND]か[OR]を指定します。
19
第 2 章 データベース機能
≪AND 条件の使用例≫
‘日付’が“2005 年 1 月 1 日”から“2005 年 12 月 31 日”までを抽出する場合
日付で期間を指定する場合、Excel では日付が新しいほど数値が
大きいと認識します。そのため『日付が“2005/1/1”以上であり、
なおかつ“2005/12/31”以下である』という考え方をします。
„ ワイルドカード
部分的に一致する文字列を検索するには、『ワイルドカード』文字を使います。
ワイルドカード文字は、1 文字または任意の文字数を表し、必ず半角文字で入力します。
ワイルドカード
20
使用例
検索する文字列
? (疑問符)
疑問符と同じ位置にある任意の 1 文字
* (アスタリスク)
アスタリスクと同じ位置にある任意の文字列
「南?風」と指定すると、
「南東風」や「南西風」が
検索されます。
「*東」と指定すると、
「北東」や「南南東」が検索
されます。
第 2 章 データベース機能
第２章のトレーニング（３）
ここでは、オートフィルタの練習をしましょう。
STEP 1
Excel を起動し、My Documents から“2 章(3)オートフィルタ.xls”ブックを開きましょう。
STEP 2
オートフィルタ機能を使用して、‘部署名’フィールドが“人事部”の社員データのみを表示しましょう。
14 件のデータが抽出されたことを確認します。
ステータスバーには、
総レコード数と抽出
されたデータの件数
が表示されます。
STEP 3
集計行を表示して、次のフィールドに集計結果を表示しましょう。
¾
‘部署名’フィールド：データ個数
¾
‘基本給’フィールド：平均
※
集計行には、現在抽出されている‘部署名’フィールドが“人事部”の『社員数』、
『基本給の平均』、
『支給額の合計』が
表示されます。
STEP 4
さらに人事部の中で‘性別’フィールドが“女”の社員データのみを表示し、集計行がそれぞれ変化することを確認しましょう。
(社員数：6 人、‘基本給’の平均：¥355,000、‘支給額’の合計：¥2,410,000)
STEP 5
すべてのデータを表示しましょう。
21
第 2 章 データベース機能
STEP 6
トップテンオートフィルタ機能を使用し、
‘支給額’フィールドで金額が多い上位 5 名の社員データのみを表示し、金額が多い順に
並べ替えましょう。確認後、すべてのデータを表示します。
STEP 7
オートフィルタオプション機能を使用し、‘勤続年数’フィールドが“5”または“10”の社員データのみを表示しましょう。
14 件のデータが抽出されたことを確認し、すべてのデータを表示します。
STEP 8
オートフィルタオプション機能を使用し、‘部署名’フィールドが“営業部”の社員の中で‘基本給’フィールドが 30 万円台の
データのみを表示しましょう。
10 件のデータが抽出されたことを確認し、すべてのデータを表示します。
STEP 9
オートフィルタオプション機能を使用し、‘入社日’フィールドが“2001/4/1”から“2003/3/31”の社員の中で‘部署名’フィー
ルドが“営業部”以外の社員データのみを表示し、リスト以外の任意のセルをアクティブにしましょう。
5 件のデータが抽出されたことを確認します。
※
リストの選択を解除すると、[オートフィルタ矢印]ボタンや『挿入行』は非表示になります。
STEP10
ブックは保存せずに、Excel を終了しましょう。
22