Microsoft Word - 第2章.doc

Size: px

Start display at page:

Download "Microsoft Word - 第2章.doc"

かんじやまがた
5 years ago
Views:

第 2 章データベース機能この章ではワークシートのデータをデータベースとして有効活用する方法レコードの追加や削除また必要なデータの検索や抽出集計機能などのデータベース機能について学習します STEP 1: リストの作成データベースとは特定の目的で集められたデータの集まりのことです住所録や商品台帳社員名簿などがデータベースにあたります Excel では先頭行に項目名があり

1 第 2 章データベース機能この章ではワークシートのデータをデータベースとして有効活用する方法レコードの追加や削除また必要なデータの検索や抽出集計機能などのデータベース機能について学習します STEP 1: リストの作成データベースとは特定の目的で集められたデータの集まりのことです住所録や商品台帳社員名簿などがデータベースにあたります Excel では先頭行に項目名がありその下の行以降にデータが 1 行に 1 件という形式で入力されている表をリストといいます Excel のデータベース機能を効果的に利用するにはリスト形式で表を管理しリストとして定義する必要がありますリストの作成セル範囲をリストとして定義するとリストに含まれるデータだけを対象に並べ替えや抽出印刷グラフ作成などを行うことができますリストとして定義するには [ データ ] メニューの [ リスト ] から [ リストの作成 ] をクリックし [ リストの作成 ] ダイアログボックスでリストに変換したい範囲を指定しますリストの構成リスト内の任意のセルを選択するとリスト領域を囲む青い枠線が太く表示され [ オートフィルタ矢印 ] ボタン [ 挿入行 ] [ リスト ] ツールバーなどが表示されますフィールド列ごとに入力される同種類のデータフィールド名各フィールド ( 列 ) の先頭に入力される項目名フィールド名の右側に [ オートフィルタ矢印 ] ボタンが表示されますレコード行ごとに入力される 1 件分のデータ挿入行この行にデータを入力すると自動的にリストの範囲が広がりますサイズ変更ハンドルドラッグ操作でリストのサイズ ( 範囲 ) を変更することができますリスト作成時の注意事項同じ列に同じ種類の項目が入力されるようにリストを設計しましょうリストの先頭行には必ずフィールド名を作成しフィールド名とデータを確実に区別するためにフィールド名のセルにはデータ部分と異なる書式を設定しましょうリスト内では空白行や空白列を使用しないようにしましょうワークシートのリストと他のデータの間には少なくとも 1 つ以上の空白列や空白行を挿入するようにしましょう 10

STEP 2: フォームの入力フォームの構成タイトルバーにはシート名が表示されます総レコード数と現在何番目のレコードが表示されているかを確認することができますフィールド名数式が入力されているフィールドは編集用のボックスは表示されずフォームから編集することはできません A)

キーを押すと次のレコードに移動します E) フォームを使用して新規にデータを入力した場合はリストの最終行にデータが追加されます F) 他のレコードに移動したりフォームを閉じると新しく入力したレコードが追加されます特定のレコードの表示特定のレコードを表示するには次の 3 種類があります 1.

2 STEP 2: フォームの入力フォームの構成タイトルバーにはシート名が表示されます総レコード数と現在何番目のレコードが表示されているかを確認することができますフィールド名数式が入力されているフィールドは編集用のボックスは表示されずフォームから編集することはできません A) フォームに同時に表示されるフィールドは 32 個までです B) フィールドの並び順はシート上のリストのフィールド名順となります C) 次のフィールドにカーソルを移動するには Tab キーを押します前のフィールドに移動するには Shift + Tab キーを押します D) Enter キーを押すと次のレコードに移動します E) フォームを使用して新規にデータを入力した場合はリストの最終行にデータが追加されます F) 他のレコードに移動したりフォームを閉じると新しく入力したレコードが追加されます特定のレコードの表示特定のレコードを表示するには次の 3 種類があります 1. 1 レコードずつ移動するにはスクロールバー上下にある矢印ボタンをクリックします 2. 一度に 10 レコードずつ移動するにはスクロールバーのつまみと矢印ボタンの間をクリックします 3. リスト内の次のレコードに移動するには次を検索ボタンをクリックしますリスト内の前のレコードに移動するには前を検索ボタンをクリックします検索条件が指定されている場合は条件に一致するレコードの中で移動します 11

3 第 2 章のトレーニング (1) ここでは既に入力されたデータをリストとして定義しフォームの使い方を練習しましょうデータベース機能を使用する場合はリスト内の任意のセルをアクティブにして行いましょう STEP 1 Excel を起動し My Documents から 2 章 (1) データベース.xls ブックを開きましょう STEP 2 4 行目のリストのフィールド名までが常に見えるようにウィンドウを固定し 102 行目の最後のレコードが見えるようにスクロールしましょう STEP 3 セル A4 から K102 のデータをリストとして定義しましょう ( 先頭行はリストの見出しとして使用するようにします ) リスト領域を示す青い枠線はリスト内の任意のセルをアクティブにすると挿入行を含むセル A4 から K103 までを囲みリスト外のセルをアクティブにするとセル A4 から K102 を囲んで表示されます STEP 4 フォームを使用してリストにデータを追加しリストサイズが変更されることを確認しましょう 1 新しいデータとして次のデータをリストに追加しますフリガナ勤続年数世代支給額フィールドは計算式により自動入力されます社員 No. 氏名部署名入社日性別基本給等級 1134 斉藤里佳子経理部 2005/9/5 女行目にデータが追加されリストサイズが変更されたことを確認します STEP 5 フォームを使用して特定のデータを表示しデータの一部を変更しましょう 1 社員 No.1005 早川孝弘さんのデータを表示します 2 等級フィールドを 1 から 3 に変更しフォームを閉じます 3 セル J6 に入力されている早川孝弘さんの等級が 3 に変更されていることを確認します STEP 6 フォームを使用して特定のデータを検索し削除しましょう 1 開発部の男のデータを検索します 2 検索されたデータの中から社員 No.1027 正田弘信さんのデータを削除しフォームを閉じます 3 22 行目に入力されていた正田弘信さんのデータが削除され社員 No.1027 が欠番になっていることを確認します STEP 7 上書き保存して Excel を終了しましょう 12

STEP 3: 並べ替えリスト定義済みのデータの並べ替え複数のフィールドを基準にしたりオプションを設定する場合は [ 並べ替え ] ダイアログボックスを使用しますリスト定義していないデータの並べ替え < 操作手順 1> [ 昇順で並べ替え ] ボタン [ 降順で並べ替え ] ボタン 1. 並べ替えの基準となるフィールド ( 列 ) の任意のセルをアクティブにします 2.

4 STEP 3: 並べ替えリスト定義済みのデータの並べ替え複数のフィールドを基準にしたりオプションを設定する場合は [ 並べ替え ] ダイアログボックスを使用しますリスト定義していないデータの並べ替え < 操作手順 1> [ 昇順で並べ替え ] ボタン [ 降順で並べ替え ] ボタン 1. 並べ替えの基準となるフィールド ( 列 ) の任意のセルをアクティブにします 2. [ 標準 ] ツールバーの [ 昇順で並べ替え ] ボタンまたは [ 降順で並べ替え ] ボタンをクリックします < 操作手順 2> [ 並べ替え ] ダイアログボックス 1. リスト内の任意のセルをアクティブにします表の一部分を並べ替えるときはあらかじめそのセル範囲を選択して行います 2. [ データ ] メニューの [ 並べ替え ] をクリックし [ 並べ替え ] ダイアログボックスを表示します 3. [ データ範囲の先頭行 ] で先頭行をタイトル行として並べ替えから除外するかデータとして並べ替えるかオプションボタンを選択します 4. OK ボタンをクリックします並べ替えの順序の規則しょうじゅん昇順 ( 小さい順 ) こうじゅん降順 ( 大きい順 ) 数数値負の最小値 ~ 0 ~ 正の最大値正の最大値 ~ 0 ~ 負の最小値値日付古い順新しい順数値 0 ~ 9 9 ~ 0 文 ( スペース )! " # $ % & ( ) *,. / : 記号字 [ ] ^ _ ` { } ~ + < = > 昇順の逆列アルファベット A ~ Z ( 小文字 ~ 大文字 ) Z ~ A ( 大文字 ~ 小文字 ) かなあ ~ ん ( 清音 ~ 濁音 ~ 半濁音 ) ん ~ あ ( 半濁音 ~ 濁音 ~ 清音 ) 空白常にリストの最後文字列と共に入力されている数値は左から右に 1 文字ずつ順番に並べ替えられます例えば A100 と入力されているセルは昇順にすると A1 より後 A11 より前に配置されます [ 並べ替えオプション ] ダイアログボックス [ 並べ替えオプション ] ダイアログボックスを使用すると [ 最優先されるキー ] に指定したフィールド ( 列 ) のデータをユーザー設定リストの項目順で並べ替えたり列単位に並べ替えたりすることができます例えば月が入力されている列を昇順に並べ替えた場合 10 月 11 月 12 月 1 月 9 月という順序で並んでしまいますこれを正しく並べ替えるには [ 並べ替えオプション ] ダイアログボックスで [ 並べ替え順序の指定 ] ボックスから [1 月,2 月,3 月 ] の項目を選択しますオプションボタンをクリックすると表示されますユーザー設定リストの項目とはオートフィルタ機能で自動入力ができる曜日や月などの項目のことで [ ツール ] メニューの [ オプション ] をクリックして表示される [ オプション ] ダイアログボックスの [ ユーザー設定リスト ] タブからユーザーが独自に作成することもできます 13

STEP 4: 集計リスト内の集計リスト定義したデータは [ リスト ] ツールバーを使用してリストに集計行を表示し集計方法を選択してフィールドごとの集計を簡単に行うことができます自動集計特定のフィールドをグループごとに集計する場合は自動集計を使用します自動集計を行うにはあらかじめ集計するフィールドを基準に並べ替えを実行してデータをグループごとにまとめ

5 STEP 4: 集計リスト内の集計リスト定義したデータは [ リスト ] ツールバーを使用してリストに集計行を表示し集計方法を選択してフィールドごとの集計を簡単に行うことができます自動集計特定のフィールドをグループごとに集計する場合は自動集計を使用します自動集計を行うにはあらかじめ集計するフィールドを基準に並べ替えを実行してデータをグループごとにまとめリスト内の任意のセルをアクティブにして自動集計を行います集計を実行するとリストにはグループごとに合計行 ( 小計総計 ) が挿入されさらにアウトラインも作成されますリスト定義したデータに自動集計を行うことはできませんリスト定義を解除したい場合はリスト内の任意のセルをアクティブにし [ リスト ] ツールバーの [ リスト ] または[ データ ] メニューの [ リスト ] から [ 範囲に変換 ] をクリックして標準の範囲に変換しますアウトラインアウトラインとはワークシートの行や列をグループ化して折りたたみ表示と非表示を切り替えることができる機能です自動集計を行うと行方向に自動的にアウトラインが作成されリストの詳細データを折りたたんで小計行や総計行だけを表示したり必要な部分だけ詳細データを展開して参照することができますまたアウトラインはグループ化したい行や列を選択し [ データ ] メニューの [ グループとアウトラインの設定 ] から [ グループ化 ] をクリックして手動で作成することもできますアウトライン記号次の例では自動集計により自動作成された行方向のアウトラインに加え C 列から D 列を必要に応じて非表示にできるようにグループ化してアウトラインを設定していますまた行方向では日進フラワーのみ詳細データを表示しています [ レベルバー ] 同じレベルのグループであることを示しクリックすると詳細データを非表示にすることができます [ 詳細非表示記号 ] クリックしグループ内の詳細データを非表示にすることができます [ レベル記号 ] クリックし各レベルまでを表示します最下位レベル ( ここでは ) をクリックすると全てのデータを表示することができます [ 詳細表示記号 ] クリックし非表示になっている詳細データを表示することができます 14

6 可視セルの選択アウトラインなどを利用して行や列を非表示にしていてもその範囲をコピーすると非表示になっている行や列を含めてコピーされます非表示になっている範囲を除いた可視セルのみをコピーして別表を作成したい場合などはジャンプ機能を使用すると簡単に選択することができます < 操作手順 > 1. 目的のセル範囲を選択します 2. [ 編集 ] メニューの [ ジャンプ ] をクリックし [ ジャンプ ] ダイアログボックスを表示します 3. セル選択ボタンをクリックし [ 選択オプション ] ダイアログボックスを表示します 4. [ 可視セル ] オプションボタンを選択し OK ボタンをクリックします 15

第 2 章のトレーニング (2) ここでは [ リスト ] ツールバーを利用した集計とグループごとの自動集計について練習しましょう STEP 1 Excel を起動し My Documents から 2 章 (2) 並べ替えと集計.

リストに集計行を表示して各フィールドに合計や平均を求めましょう 1 [ リスト ] ツールバーの [ 集計行の表示 / 非表示 ] ボタンをクリックして集計行を表示します初期設定ではリストの右端列に集計結果 ( 数値の列 = 合計文字の列

フィールドの合計を求めます STEP 5 集計行を非表示にしてリスト定義を解除しましょう 1 [ リスト ] ツールバーの [ 集計行の表示 / 非表示 ] ボタンをクリックします 2 [ リスト ] ツールバーの [ リスト ] から [

7 第 2 章のトレーニング (2) ここでは [ リスト ] ツールバーを利用した集計とグループごとの自動集計について練習しましょう STEP 1 Excel を起動し My Documents から 2 章 (2) 並べ替えと集計.xls ブックを開きましょう STEP 2 販売実績シートのセル A1 から I80 のデータをリストとして定義しましょう STEP 3 [ オートフィルタ矢印 ] ボタンを使用して利益合計フィールドを大きい順に並べ替えましょう STEP 4 リストに集計行を表示して各フィールドに合計や平均を求めましょう 1 [ リスト ] ツールバーの [ 集計行の表示 / 非表示 ] ボタンをクリックして集計行を表示します初期設定ではリストの右端列に集計結果 ( 数値の列 = 合計文字の列 = データの個数 ) が表示されます 2 セル B82 に店舗名フィールドのデータの個数を求めます 3 セル D82 に単価フィールドの平均を求めます 4 セル E82 に数量フィールドの合計を求めます 5 セル F82 に金額フィールドの合計を求めます STEP 5 集計行を非表示にしてリスト定義を解除しましょう 1 [ リスト ] ツールバーの [ 集計行の表示 / 非表示 ] ボタンをクリックします 2 [ リスト ] ツールバーの [ リスト ] から [ 範囲に変換 ] をクリックし確認メッセージのはいボタンをクリックします STEP 6 店舗名ごとに数量金額利益合計フィールドの合計を自動集計しましょう 1 [ 標準 ] ツールバーの [ 昇順で並べ替え ] ボタンを使用して店舗名フィールドを基準に昇順で並べ替えます 16

2 3 [ データ ] メニューの [ 集計 ] を使用し店舗名フィールドを基準に数量金額利益合計フィールドの合計を自動集計します行の [ レベル記号 ] を使用し

フィールド利益合計フィールドの集計結果 ( セル E18 からセル F87 とセル I18 から I87) を範囲選択して可視セルを選択します 2 選択した可視セルをコピーし

集計したリスト内の任意のセルをアクティブにします 2 [ データ ] メニューの [ 集計 ] をクリックし [ 集計の設定 ] ダイアログボックスのすべて削除ボタンをクリックします STEP

をクリックし [ 並べ替え ] ダイアログボックスを表示して次の設定を行い OK ボタンをクリックします最優先されるキー : 商品名の昇順 2 番目に優先されるキー : 店舗名の昇順 3

8 2 3 [ データ ] メニューの [ 集計 ] を使用し店舗名フィールドを基準に数量金額利益合計フィールドの合計を自動集計します行の [ レベル記号 ] を使用し集計したデータを 2 レベルまで表示します STEP 7 STEP 6 で行った集計結果を店舗別集計シートの表にコピーしましょう 1 販売実績シートをアクティブにし数量フィールド金額フィールド利益合計フィールドの集計結果 ( セル E18 からセル F87 とセル I18 から I87) を範囲選択して可視セルを選択します 2 選択した可視セルをコピーし店舗別集計シートのセル B4 から D10 に値として貼り付けます STEP 8 STEP 6 で行った販売実績シートの集計をリストから削除しましょう 1 集計したリスト内の任意のセルをアクティブにします 2 [ データ ] メニューの [ 集計 ] をクリックし [ 集計の設定 ] ダイアログボックスのすべて削除ボタンをクリックします STEP 9 商品名フィールドを昇順店舗名フィールドを昇順単価フィールドを降順で並べ替えましょう 1 リスト内の任意のセルをアクティブにします 2 [ データ ] メニューの [ 並べ替え ] をクリックし [ 並べ替え ] ダイアログボックスを表示して次の設定を行い OK ボタンをクリックします最優先されるキー : 商品名の昇順 2 番目に優先されるキー : 店舗名の昇順 3 番目に優先されるキー : 単価の降順 2 番目最優先 3 番目 STEP10 商品名フィールドごとに数量金額フィールドの合計を集計しましょう ([ 集計するフィールド ] の [ 利益合計 ] チェックボックスはオフにします ) 17

9 STEP11 STEP10 で行った集計に店舗名フィールドごとの数量金額フィールドの合計の集計を追加しましょう ([ 現在の集計表と置き換える ] チェックボックスをオフにして集計します ) STEP12 行の [ レベル記号 ] を使用し集計したデータを 3 レベルまで表示します STEP13 上書き保存して Excel を終了しましょう 18

STEP 5: オートフィルタフィルタとは特定の条件に該当するレコードをリストから抽出する機能ですオートフィルタを使用するとリストの必要な部分だけを簡単に抽出し表示することができますリスト定義していないデータのオートフィルタ < 操作手順 > 1. データ内の任意のセルをアクティブにします 2.

オートフィルタを解除するには再度 [ データ ] メニューの [ フィルタ ] から [ オートフィルタ ] をクリックしますリスト定義していない場合オートフィルタは 1 つのワークシートにつき 1 箇所のデータのみに有効ですリストのすべてのデータを表示抽出したリストからすべての抽出条件を一度に解除し非表示になっているデータ ( 行 ) をすべて表示するには [ データ ]

10 STEP 5: オートフィルタフィルタとは特定の条件に該当するレコードをリストから抽出する機能ですオートフィルタを使用するとリストの必要な部分だけを簡単に抽出し表示することができますリスト定義していないデータのオートフィルタ < 操作手順 > 1. データ内の任意のセルをアクティブにします 2. [ データ ] メニューの [ フィルタ ] から [ オートフィルタ ] をクリックし列見出しに [ オートフィルタ矢印 ] ボタンを表示します 3. オートフィルタを解除するには再度 [ データ ] メニューの [ フィルタ ] から [ オートフィルタ ] をクリックしますリスト定義していない場合オートフィルタは 1 つのワークシートにつき 1 箇所のデータのみに有効ですリストのすべてのデータを表示抽出したリストからすべての抽出条件を一度に解除し非表示になっているデータ ( 行 ) をすべて表示するには [ データ ] メニューの [ フィルタ ] から [ すべて表示 ] をクリックしますオートフィルタは解除されません STEP 6: オートフィルタオプショントップテンオートフィルタフィールド内のデータの上位または下位から項目数あるいはパーセンテージの範囲を指定しデータを抽出します抽出条件はデータを抽出するフィールドの [ オートフィルタ矢印 ] ボタンをクリックし一覧から [( トップテン )] をクリックして表示される [ トップテンオートフィルタ ] ダイアログボックスから指定します [( トップテン )] では条件に合ったデータの抽出のみが行われます抽出されたデータの並び順を変更したい場合はさらに [ 並べ替え ] を行いますスピンボタンをクリックして数値を変更するか直接数値を入力します [ 上位 ] か [ 下位 ] を指定します [ 項目 ] か [ パーセント ] を指定します [ オートフィルタオプション ] ダイアログボックス [ オートフィルタオプション ] ダイアログボックスを使用すると 1 つのフィールドに対して 2 つの条件または数値が ~ 以上や ~より小さいなどのあいまいな条件で抽出することができます 2 つの条件を与える場合には 2 つの条件を結ぶ接続詞 (AND または OR) が必要です選択した接続詞により抽出結果が異なります両方の条件を共に満たしているものを例 : A AND B AND 条件抽出したい場合に使用します A でありなおかつ B であるどちらか一方の条件を満たしている例 :A OR B OR 条件ものを抽出したい場合に使用します A あるいは B であるフィールド名比較演算子を一覧から指定します下向き三角ボタンをクリックして条件を一覧から選択するかボックスに直接入力して指定します条件を 2 つ指定する場合は [AND] か [OR] を指定します 19

AND 条件の使用例日付が 2005 年 1 月 1 日から 2005 年 12 月 31 日までを抽出する場合日付で期間を指定する場合 Excel では日付が新しいほど数値が大きいと認識しますそのため日付が 2005/1/1 以上でありなおかつ 2005/12/31 以下であるという考え方をしますワイルドカード部分的に一致する文字列を検索するには

11 AND 条件の使用例日付が 2005 年 1 月 1 日から 2005 年 12 月 31 日までを抽出する場合日付で期間を指定する場合 Excel では日付が新しいほど数値が大きいと認識しますそのため日付が 2005/1/1 以上でありなおかつ 2005/12/31 以下であるという考え方をしますワイルドカード部分的に一致する文字列を検索するにはワイルドカード文字を使いますワイルドカード文字は 1 文字または任意の文字数を表し必ず半角文字で入力しますワイルドカード検索する文字列? ( 疑問符 ) 疑問符と同じ位置にある任意の 1 文字 * ( アスタリスク ) アスタリスクと同じ位置にある任意の文字列使用例南? 風と指定すると南東風や南西風が検索されます * 東と指定すると北東や南南東が検索されます 20

総レコード数と抽出されたデータの件数が表示されます STEP 3 集計行を表示して次のフィールドに集計結果を表示しましょう部署名フィールド : データ個数基本給フィールド : 平均集計行には現在抽出されている部署名

12 第 2 章のトレーニング (3) ここではオートフィルタの練習をしましょう STEP 1 Excel を起動し My Documents から 2 章 (3) オートフィルタ.xls ブックを開きましょう STEP 2 オートフィルタ機能を使用して部署名フィールドが人事部の社員データのみを表示しましょう 14 件のデータが抽出されたことを確認しますステータスバーには総レコード数と抽出されたデータの件数が表示されます STEP 3 集計行を表示して次のフィールドに集計結果を表示しましょう部署名フィールド : データ個数基本給フィールド : 平均集計行には現在抽出されている部署名フィールドが人事部の社員数基本給の平均支給額の合計が表示されます STEP 4 さらに人事部の中で性別フィールドが女の社員データのみを表示し集計行がそれぞれ変化することを確認しましょう ( 社員数 :6 人基本給の平均 : 355,000 支給額の合計 : 2,410,000) STEP 5 すべてのデータを表示しましょう 21

STEP 6 トップテンオートフィルタ機能を使用し支給額フィールドで金額が多い上位 5 名の社員データのみを表示し金額が多い順に並べ替えましょう確認後

件のデータが抽出されたことを確認しすべてのデータを表示します STEP 8 オートフィルタオプション機能を使用し部署名フィールドが営業部の社員の中で基本給

入社日フィールドが 2001/4/1 から 2003/3/31 の社員の中で部署名フィールドが営業部以外の社員データのみを表示し

13 STEP 6 トップテンオートフィルタ機能を使用し支給額フィールドで金額が多い上位 5 名の社員データのみを表示し金額が多い順に並べ替えましょう確認後すべてのデータを表示します STEP 7 オートフィルタオプション機能を使用し勤続年数フィールドが 5 または 10 の社員データのみを表示しましょう 14 件のデータが抽出されたことを確認しすべてのデータを表示します STEP 8 オートフィルタオプション機能を使用し部署名フィールドが営業部の社員の中で基本給フィールドが 30 万円台のデータのみを表示しましょう 10 件のデータが抽出されたことを確認しすべてのデータを表示します STEP 9 オートフィルタオプション機能を使用し入社日フィールドが 2001/4/1 から 2003/3/31 の社員の中で部署名フィールドが営業部以外の社員データのみを表示しリスト以外の任意のセルをアクティブにしましょう 5 件のデータが抽出されたことを確認しますリストの選択を解除すると [ オートフィルタ矢印 ] ボタンや挿入行は非表示になります STEP10 ブックは保存せずに Excel を終了しましょう 22

Excel2013　データベース1(テーブル機能と並べ替え)

Excel2013　データベース1(テーブル機能と並べ替え) OA スキルアップ EXCEL2013 データベース 1( テーブル機能と並べ替え ) 1 / 8 Excel2013 データベース 1( テーブル機能と並べ替え ) データベース機能概要データベース機能とはデータベースとは売上台帳顧客名簿社員名簿など特定のルールに基づいて集められたデータを指します Excel のデータベース機能には並べ替え抽出集計分析といった機能があります