表計算を利用した集計作業 この回では Excel を用いた集計作業を行う 集計は 企業などで事実を記録したデータを目的に従って整 理する作業である Excel では 集計に特化した機能として テーブル ピポットテーブル という機能が ある 1. 課題の確認 売上データを利用して 必要な情報を抽出したり 集計したりする 利用するソフトウェア Microsoft Excel 2016 前バージョンでも同等の機能がある 1.1. 演習の内容 あらかじめ整理されたデータではなく ただ時系列に事実を記録したデータを用いる場合 集計作業が必 要となる このようなデータとしては アンケートデータ 売上データ 家計簿などである これらを日々 記録して あるタイミングで記録全体を俯瞰し 分析するために集計作業が行われる 2. テーブルの作成 ダウンロードしたデータには 食品を販売している店舗が いつ 誰が 誰に 何を 販売したか が 131 件記録されている 既に表形式にレイアウトされており 見出しも設定されている このデータを Excel の機能 テーブル を用いて分析する作業を行う 2.1. データの書式設定 ここでは テーブルを設定する前に 表内のデータ書式を設定する Excel では 数値データや日付データ に書式を設定することで 後の計算や集計において書式に応じた機能が利用できるためである ここでは 日付 のデータに 短い日付形式 売上金額 のデータに 会計 の書式を設定する ただし Excel では日付と認識できるデータを入力すると 自動的に日付の書式が設定される そのため ダ ウンロードしたデータには既に 日付 の書式設定が施されている 例えば 日付の表示形式を 2014/6/23 から 2014 年 6 月 23 日 に変更したい場合 日付のデータを全て選択し ホーム タブの 数値 にあ るプルダウンメニューから 長い日付形式 をクリックする 日付のデータを選択 日付の書式設定を変更 すると 日付の表示形式が変更される 11-1
変更された状態 同様に価格のセルを書式設定する場合は 金額のセルをすべて選択し 書式 のプルダウンメニューか ら 会計 を選択する すると が追加され 金額としての書式が設定される 金額に と が追加 会計 を設定 2.2. テーブルの設定 ここでは 書式設定された表をテーブルとして Excel に認識させる まず テーブルに設定したい範囲を 見出しも含めてマウスのクリック ドラッグで選択する 自動で範囲を認識させることが可能な場合もある 選択したのち ホーム タブにある テーブルとして書式設定 をクリックする するとプルダウンメ ニューが表示されるので 任意のテーブルデザインを選択する ①テーブルを全選択 ②任意のデザインをクリック テーブルを全選択し テーブルとして書式設定 11-2
するとダイアログが表示され テーブル範囲の確認が行われる 選択範囲に問題がなく 見出しデータの 設定も間違いがなければ OK ボタンをクリックする 範囲を確認する 見出しが含まれていれば 先頭行をテ ーブルの見出しとして にチェックを入れ る テーブルとして書式設定 ダイアログ 以上の操作で 選択した範囲がテーブルとして認識される 見出しにプルダウンが設定される テーブル全体にデザインが施される テーブル設定の結果 2.3. テーブルの操作 テーブル設定された表は 並べ替え フィルタなどの操作ができるようになる 例えば 担当者を 川原 だけにしたい場合は 下記のような操作を行う 担当者 の見出しに設定されているプルダウンをクリックし 表示されたメニューから すべて選択 のチェックを外す そして 川原 のチェックを入れて OK ボタンをクリックする フィルタを設定すると プルダウンメニューに漏斗のアイコンが表示される フィルタを解除する場合は プルダウンメニューをクリックし すべて選択 にチェックを入れる 11-3
並べ替えもできる ①プルダウンをクリック ② すべて選択 のチェックを外す ③チェックを付ける セルにあるすべての データが表示されて いる 漏斗のアイコンは フィルタが 設定されていることを示してい る フィルタの設定 テーブルのフィルタは セルの書式設定に基づいた設定もできるよう になっている 例えば 日付の書式設定が施された箇所のフィルタメニ ューは 年 月 ごとにフィルタを設定することが可能になっている 月ごとのフィルタが可 能になっている 2.4. 列の追加 数式の処理 テーブルが設定された範囲では 列を追加すると自動的にテーブルの範囲の一部として設定される また 数式をテーブルの範囲内で作成すると 自動的に列全体に数式が作成される 例えば 売上金額 の見出しセルの右に ８万以上 を入力する すると自動的にテーブルの範囲が拡 張され 書式設定が施される 自動で書式設定 列の追加 11-4
次に 8 万以上 に数式を追加する 先頭のセルに下記の数式を入力すると 自動的に列全体に数式が入 力される ここで G3 セルをマウスのクリックで参照した際に セル単位ではなく列単位での参照となっ ていることを確認する 列単位の参照になる 自動で複製される 数式の設定 ここで 数式の調整を行った場合も その都度自動的に数式の更新が行われる このように テーブルの 機能を用いることで データの形式によっては作業を大幅に効率化できる 3. ピポットテーブル ここでは データを集計してクロス表を作成する ピポットテーブル と呼ばれる機能を利用する これ を用いることで クロス表の要素を適宜変更しながらデータの状態を分析することができる 3.1. ピポットテーブルの作成 ピポットテーブルを作成するには 対象となるデータがテーブルとして設定されている必要がある 今回 は既にテーブルが作成されているが 表選択 挿入 テーブル ピポットテーブル をクリ ックすることで テーブルの設定を含めて一度に行うこともできる まず 作成したテーブルの中をクリックし カーソルを配置する すると 上部メニューに テーブルツ ール が表示される ここで テーブルツール の デザイン をクリックし メニューを表示する そして ツール にある ピポットテーブルで集計 をクリックする ① デザイン をクリック ② ピポットテーブルで 集計 をクリック テーブルツール の デザイン すると ピポットテーブルの作成 ダイアログが表示される ここでは 集計の対象となるテーブルの範 囲 今回は先に作成したテーブル とピポットテーブルを作成する場所を設定する 11-5
集計の対象を設定 テーブルが対象の場合は テ ーブル名が表示されている 作成する場所を指定する 通常は 新規 を選ぶ ピポットテーブルの作成 ダイアログ 設定を確認し OK ボタンをクリックすると 新しいシートにピポットテーブルが作成される クロス表が表示される場所 テーブルの見出し一覧 フィールド 見出し を追加 する場所 ピポットテーブル の画面 ピポットテーブルでは テーブルの見出しに従って フィールド が表示されており 適宜必要な要素を 選択しながら作成することになる 例えば 担当者 ごとの販売回数 No. を集計したい場合 フィールド から 担当者 を 行 に ドラッグし No. を 値 にドラッグする 仮に 売上金額の集計をしたい場合は 売上金額 を 値 にドラッグする すると 選択されたフィールドに従ってシート上にピポットテーブルが表示される 11-6
結果が表示される No. を 値 にドラッグ 担当者 を 行 にドラッグ フィールド の追加 3.2. 集計方法の変更 ここで 値 にある No. が数値であるために 自動で 合計 と設定されてしまっているので 合計 /No. をクリックし プルダウンメニューから 値フィールドの設定 をクリックし 表示されたダイアロ グで データの個数 を選択する データの個数 を選択し OK をクリック メニューから 値フィール ドの設定 をクリック 集計方法の変更 以上の設定で 担当者 ごとで No. の個数がカウ フィルタも利用できる ントされた表が作成される ここで 値 に 売上金額 が利用された場合 合計 平均 最大 などを利用して 計算を行う表を作成す ることも可能である No. の個数がカウン トされる 11-7
3.3. クロス表の作成 次に 担当者ごとの商品別販売回数をクロス表で集計する クロス表を作成するには 行 列 にク ロス集計したい要素のフィールドを追加する よって 列 に 商品名 のフィールドを追加すればよい クロス表が作成される 商品名 を 列 にドラッグ クロス表の作成 商品名 の追加 次に 日付 を用いたクロス表を作成する テーブルで 日付 フィールドには日時を示す書式設定が施 されているため 少し操作が異なってくる まず 列 に登録されている 商品名 のプルダウンメニューを表示させ フィールドの削除 をク リックし 商品名 の表示を解除する 次に 日付 を 列 にドラッグして 担当者 と 日付 のクロス表を作成する 日毎に集計されてしまう Excel2013 以前 日付 を登録した結果 11-8
①データを右クリック ③ 月 を選択 ② グループ化 をクリック フィールドのグループ化 すると 日付の表示が月ごとにグループ化され 月単位での集計が行われる 集計結果 また 複数の要素を組み合わせたクロス表を作成することもできる 例えば 商品ごとの月ごと販売回数 を集計したい場合は 列 に 商品名 をドラッグし 日付 の上に 商品名 が表示されるように設 定する 2016 の場合は 月 が作成されているので 月 の上にドラッグ 日付 のうえに 商品名 がある 複数の要素によるクロス表の作成 11-9
課題 講義で作成したファイルに下記の作業を行え 商品の販売記録テーブルを用いて 新たにピポットテーブルを作成せよ 講義で作成したピポットテーブルは変更しない 作成したピポットテーブルにおいて 商品ごとの売上金額合計を月別に表示するクロス表を作成せよ さらに 商品ごとに担当者も表示されるように 行 に要素を追加せよ 完成イメージ 上記の作業を施した演習ファイルを下記の要領で提出すること 提出期限は講義内で指定する 必ず 件名に 組 学籍番号 例 ３組 情報リテラシー 学籍番号 p18991 の場合 第１１回課題 を記載すること 3-991 情報リテラシー 11-10 第１１回課題