Microsoft Word - 教科書大1b第12週06.doc

Size: px

Start display at page:

Download "Microsoft Word - 教科書大1b第12週06.doc"

ときなやぶき
5 years ago
Views:

1 5. 表計算におけるデータベース機能表計算ソフトには表作成グラフ作成と並んでもう一つ重要な機能があります表のデータベース操作ですこの章ではエクセルのもつデータベース操作機能について学んでいくことにしましょう 5-1 データベーステーブルデータベース機能とは表のデータを並べ替えたり条件にあったデータのみを取り出したり様々な集計を行うことをいいますそのような機能を使用するためにはまず表をデータベーステーブルの形式に作成しておくことが必要ですデータベーステーブルとは最上段の行に項目名が並びその下に行ごとに同じ形式でデータが続く形をしているものをいいますそして列をフィールド項目名をフィールド名行をレコードと言い換えますでは最初に一つのデータベーステーブルを作成しておきましょう下図に従って表を完成させファイル名研究費データベースで保存してください表は B2:H22 の範囲で作成してください整理番号購入月教員番号氏名支出場所支出コード金額年 4 月 T1 櫻井義夫文京区 AN110 86, 年 5 月 T2 浜秀樹文京区 BP , 年 6 月 T3 樋川有子文京区 DN101 23, 年 7 月 T2 浜秀樹文京区 BN110 56, 年 4 月 T1 櫻井義夫千代田区 CN120 5, 年 5 月 T3 樋川有子千代田区 CP230 6, 年 6 月 T2 浜秀樹千代田区 AN120 11, 年 7 月 T1 櫻井義夫千代田区 DN102 9, 年 8 月 T1 櫻井義夫千代田区 AP230 6, 年 4 月 T3 樋川有子豊島区 BN , 年 5 月 T3 樋川有子豊島区 AP230 1, 年 6 月 T2 浜秀樹豊島区 CP230 9, 年 4 月 T1 櫻井義夫新宿区 CN120 18, 年 5 月 T3 樋川有子新宿区 BP130 13, 年 6 月 T2 浜秀樹新宿区 CP230 15, 年 7 月 T1 櫻井義夫新宿区 DN102 78, 年 4 月 T3 樋川有子埼玉県 DN102 1, 年 5 月 T1 櫻井義夫埼玉県 CP230 1, 年 6 月 T2 浜秀樹埼玉県 DN101 1, 年 7 月 T3 樋川有子埼玉県 AN110 9,500 表 5-1 研究費データベース作成のヒント 1 整理番号 : 半角で入力部分的にオートフィル機能を使いましょう 2 購入月 : 2005/4 のように入力し後で表示形式を日付の 2005 年 4 月にしますあるいは一つだけ入力したらそれをすべてにコピーして月の番号だけを書き直すのも一法 48

3 教員番号 : 半角で入力 4 氏名 : 一度一つの名前を入力したら次回からは最初の1 文字を入れるとすぐに続く氏名が表示されるので Enter キーを押しましょうこれは入力支援機能の一つでうまく利用して効率よく氏名を入力しますあるいは一つの氏名を入力したらそれを必要な箇所にコピーするもの一つの方法ですそのとき飛び飛びのコピー先を指定するには Ctrl キーを押しながら指定し

2 3 教員番号 : 半角で入力 4 氏名 : 一度一つの名前を入力したら次回からは最初の1 文字を入れるとすぐに続く氏名が表示されるので Enter キーを押しましょうこれは入力支援機能の一つでうまく利用して効率よく氏名を入力しますあるいは一つの氏名を入力したらそれを必要な箇所にコピーするもの一つの方法ですそのとき飛び飛びのコピー先を指定するには Ctrl キーを押しながら指定しオートフィル機能以外の仕方でコピーしましょう 5 支出場所 : ここではオートフィル機能によるコピーを利用しましょう 6 支出コード : 半角で入力 7 金額 : コンマをつけながら入力してもよいし数字を入力してから表示形式をコンマ付きに変更しても OK コンマだけつけるにはコンマのアイコンが便利です最後に中央揃えや列幅調整などで表を見やすくしておきましょう罫線は引きません 5-2 データの並べ替え ( ソート ) 完成した表を一つあるいは複数の項目に注目して行ごとに並べ替えることができます注目する項目をキー並べ替える機能をソートと呼んでいますまたキーの値の小さい方から並べる順番を昇順大きい方から並べる順番を降順といいます操作は簡単ですから以下の例に従って体験してみましょう [ 例 1] 金額昇順 1 表の範囲内のセル ( どれでもよい ) をクリックしますこれで対象となるデータベーステーブルが指定されたことになります図 5-1 並べ替えダイアログボックス 2メインメニューでデータ並べ替えとフィルタ並べ替えをクリックし並べ替えダイアログボックスで最優先されるキーを金額にします 3 昇順になっていることを確かめたら OK をクリックして結果を確かめましょう 4 最優先されるキーや昇順降順を入れ替えたりして色々と試してみましょう特に氏名や支出場所をキーにした場合どのような並べ替えになりますか 49

[ 例 2] 氏名昇順金額降順第 2 第 3 のキーを指定してソートすることもできますその場合はまず最優先されるキーでソートされ最優先されるキーが同じ所だけ第 2 のキーでソートされこれら二つのキーが全く同じ箇所が複数あるときのみその箇所だけ第 3 のキーでソートされますもし最優先されるキーですべてが一意的にソートされてしまう場合は第 2 第 3

3 [ 例 2] 氏名昇順金額降順第 2 第 3 のキーを指定してソートすることもできますその場合はまず最優先されるキーでソートされ最優先されるキーが同じ所だけ第 2 のキーでソートされこれら二つのキーが全く同じ箇所が複数あるときのみその箇所だけ第 3 のキーでソートされますもし最優先されるキーですべてが一意的にソートされてしまう場合は第 2 第 3 のキーは何の役割も果たしません以下の例で確認しましょう 1 最優先されるキーを氏名昇順第 2 のキーを金額降順に 2 最優先されるキーを支出場所昇順第 2 のキーを購入月降順第 3 のキーを整理番号昇順に 3 最優先されるキーを整理番号降順第 2 のキーを金額降順第 3 のキーを教員番号昇順に 5-3 データの抽出データベーステーブルにおいてオートフィルタ機能を使えば設定して条件に合うデータだけを取り出すことができます以下の例に従って体験しましょう [ 例 1] 購入月が 2005 年 6 月であるレコードの抽出 1 表の範囲内のセル ( どれでもよい ) をクリックします 2メインメニューでデータフィルタを選びオートフィルタをクリックしますするとすべてのフィールド名にマークがつきます 3 購入月のマークをクリックしてその中の 2005 年 6 月を選びますどうです 2005 年 6 月のレコードだけが抽出されたでしょう 4 元に戻すのは購入月のマークをクリックしてすべてを選びます [ 例 2] 金額のトップテンの抽出 1 金額のマークをクリックして数値フィルタトップテンを選び下図のトップテンオート図 5-2 トップテンオートフィルターフィルタで OK をクリックします抽出するレコード数をトップシックスなどのように自由に選べます 2 色々な項目で試して見ましょう氏名や支出場所ではうまくいかないでしょう 50

[ 例 3] 金額が 5,000 以上レコードの抽出 1 金額のマークをクリックして数値フィルタユーザ設定フィルタオートフィルタオプションを選び下図のオートフィルタオプションダイアログボックスで金額 5,000 以上の条件を設定し OK をクリックします図 5-3 オートフィルタオプションダイアログボックス 2 項目や条件を色々と変えて試してみましょう [ 例 4]

4 [ 例 3] 金額が 5,000 以上レコードの抽出 1 金額のマークをクリックして数値フィルタユーザ設定フィルタオートフィルタオプションを選び下図のオートフィルタオプションダイアログボックスで金額 5,000 以上の条件を設定し OK をクリックします図 5-3 オートフィルタオプションダイアログボックス 2 項目や条件を色々と変えて試してみましょう [ 例 4] 金額が 6,000 以上で 10,000 以下のレコードの抽出条件式は二つの条件を組み合わせて設定することも可能です二つの条件を組み合わせるやり方には 2 種類あって AND 条件 [ 条件 1]AND[ 条件 2]([ 条件 1] と [ 条件 2] がともに成立しなければならない ) OR 条件 [ 条件 1]OR[ 条件 2]([ 条件 1] か [ 条件 2] のどちらかだけでも成立すればよい ) となりますこの例の条件は図 5-4 AND 条件の例 51

5 とすればよいわけですまた記号? や * は図中に説明されている機能を果たすので便利ですこのオートフィルタオプションダイアログボックスを駆使して以下の抽出を行ってください支出コードの先頭が文字 A で始まるもの支出コードの中に文字 P を含むもの整理番号の末尾が 4 のもの金額が 1000 円より大きくかつ 3000 円より小さいもの支出場所が文京区かまたは埼玉県のもの 5-4 データベースの集計表 5-1 のような形式の表において教員ごとや購入月ごとの合計を計算することを考えましょう今までの方法を使うならまず集計する項目についてソートしておき合計関数を入れる行を挿入し合計関数を入れて計算させることになりますねこれはかなり面倒ですそこで最初のソート以外は一度に処理できる方法をここで学んでおきましょう例として購入月ごとの集計を実行します以下の手順にしたがって操作してください 1 購入月について昇順になるようにソートします ( これをしておかないと集計が正しく行われません!) アイコンを使うのが最も手っ取り早いでしょう 2メインメニューでデータアウトライン小計とクリックします 3 表示された集計の設定ダイアログボックスを下図のように指定しますグループの基準購入月コンボボックスで選択します集計の方法合計コンボボックスで選択します集計するフィールド金額金額欄にチェックを入れます図 5-5 集計の設定ダイアログボックス 52

6 4 OK をクリックします購入月ごとの集計と全体の集計が表示されますこの表示形式は集計の設定ダイアログボックスの最後に指定したようになるわけですここでは集計行がデータの下に挿入され元の表が現在の集計表で置き換えられます 5 元の表に戻すには元に戻すアイコンをクリックするか集計の設定ダイアログボックスですべて削除をクリックします元の表やそれぞれの集計表をすべて残しておきたい場合は必要な数だけ元の表をコピーしてから集計しましょう [ 練習 ] 以下の集計をしましょう 1 教員ごとの金額合計および金額平均 2 支出場所ごとの金額合計および金額平均 3 支出コードごとの金額合計と金額数値の個数 5-5 クロス集計前節では一つの項目について指定した数値の集計をしましたが二つの項目を組み合わせた集計も可能です例として購入月ごとに見た教員別金額合計を計算してみましょう以下の表のようなイメージです教員 A 教員 B 教員 C 合計金額 4 月 5 月 6 月 7 月 8 月合計金額表 5-2 クロス集計表のイメージ以下の手順にしたがって操作してください 1データベーステーブルの中のセル ( どれでもよい ) をクリックします 2メインメニューで挿入テーブルピボットテーブルをクリックします 3 ピボットテーブルの作成で範囲が $B$2:$H$22 であることを確認し新規ワークシートをクリックしたら OK を押します 5すると新しいシートが開かれで下図のようなピボットテーブルツールが現れます 53

図 5-6 ピボットテーブルツールの形式 6この図の右下にある項目のうち次の 3 つを図の中に以下のようにドラッグします氏名行の位置へ購入月列の位置へ金額データの位置へすると以下のようなクロス集計表が作成されるはずです合計 / 金額購入月氏名 2005 年 4 月 2005 年 5 月 2005 年 6 月 2005 年 7 月 2005 年 8 月総計樋川有子

7 図 5-6 ピボットテーブルツールの形式 6この図の右下にある項目のうち次の 3 つを図の中に以下のようにドラッグします氏名行の位置へ購入月列の位置へ金額データの位置へすると以下のようなクロス集計表が作成されるはずです合計 / 金額購入月氏名 2005 年 4 月 2005 年 5 月 2005 年 6 月 2005 年 7 月 2005 年 8 月総計樋川有子浜秀樹櫻井義夫総計表 5-3 クロス集計表 Excel では一つのファイル (book) に複数のワークシートを入れることができます画面左下にワークシートタブがありそれによって現在見えているワークシートがわかり他のワークシートタブをクリックすることでそのワークシートに移動できることは以前確かめましたねなおワークシートタブはその上でダブルクリックして名前を変えることができます以下のように変えて見ましょう 54

8 図 5-7 ワークシートタブ一度作成したクロス集計表は別の項目の組み合わせで集計しなおすように編集することもできます集計表において氏名や購入月を元に戻して別の項目を行や列の位置にドラッグすればよいのですなおそのつど集計表を残しておきたければまず集計表を別の場所にコピーしておいてから項目を入れ替えるようにします例として氏名と支出場所のクロス集計をしてみましょう 55

ワープロソフトウェア

ワープロソフトウェア表計算ソフト (Excel) 表計算ソフト (Excel) とは表計算ソフト数値データの集計分析に用いられるアプリケーション表グラフの作成統計関数によるデータ解析データベースなどを行うことができるメリットとして計算が自動また簡単なシミュレーションができる Excel Microsoftによって提供されている表計算ソフトの名称関数の入力やマクロ機能,GUIの操作に優れており様々な用途に使用されている