第3回 - 2012/10/12

ワークシートでデータを扱う


実習用のデータの入ったファイルを取り込む(ダウンロード)

実習で使うデータを、以下の手順でダウンロードして、Excelで開いてください。以下の手順は、Internet Explorerを使った場合です。

  1. まず、ここを右ボタンでクリックする
  2. メニューから「対象をファイルに保存」を選択する
  3. 保存する場所を指定するダイアログボックスが表示される
  4. たとえば、「デスクトップ」を指定して、「保存」ボタンをクリックする
  5. 保存後、デスクトップに「売上データ」というファイルが取り込まれている
  6. これをダブルクリックすると、Excelで開くことができる

集計機能の利用

リスト形式に作成されたデータの例が、実習用データブックの「集計処理」というワークシートにあります。これをもとに、この架空の売上データを集計するということを行います。実際にExcelを操作しながら説明を行います。→デモ

並べ替え

データの並べ替え(ソート)は簡単にできます。

オートフィルタ

リスト上でデータの絞込みを行うことができます。オートフィルタという名前がついていますが、その使い方のポイントを説明します。

集計処理

一定の条件にしたがって、データを集計することができます。集計結果は「アウトライン」という機能で、要約的な表示にしたり、詳細を含めた表示にすることができます。集計処理の方法と、アウトラインの使い方を説明します。

ピボットテーブル

ピボットテーブルは、クロス集計の高機能なものです。(クロス集計-一般には2つの分類をそれぞれ縦横に並べて、交差する部分に合計などを記入する。たとえば、商品ごと、担当者ごとの売上が一覧されるような形式。)複雑な集計を一度にできるなかなか便利な機能です。原理を理解し、うまくデータ処理に結び付けてください。


本日の演習

最初にダウンロードしたファイルのデータを使って以下の作業を行ってみましょう。講義でやったことと重複するかもしれませんが、まあ、それは気にせずに。

練習問題3-1

練習問題3-2

集計の機能を利用して、顧客ごとの合計売上金額を求める

練習問題3-3

ピボットテーブルを作り。表の縦方向に日付、横方向に担当者名が並ぶような表をつくること。そして、売上金額の合計を計算すること

ところで10月17日の船井の合計売上金額はいくらか? (などと、ピボットテーブルの結果をきちんと読み取ることができるように)

練習問題3-4 ちょっと難しいかも…

一覧表はおおむね、ある商品に対して決められた商品番号と商品名、単価が設定されているようだ(実は違うのだが)。だけど、ある売上はお客の値切り攻撃に負けて、単価を下げて販売してしまった。それがどれかを探し当てたい。どうすればいいだろうか? 何月何日に誰がどの顧客に売ったデータかがわかればよい。

ヒントとしては、ピボットテーブルをうまく作ると、ほいほいと出て来る。

練習問題3-5

月ごとに、各商品が何個ずつ販売されたのかを集計したい。ピボットテーブルを使えばいい。3ヶ月分しかないのでちょっとつまらん結果だがいいとしよう

ヒント:A2には日付データが入っている。I2に「=MONTH(A2)」という式を入れてみると、月の数値がI列に表示される。


解答

練習問題3-1

個数の多いものから順に並べ替える

金額の多いものから順に並べる

練習問題3-2

まず、顧客番号順に並び替える。

集計を行う。顧客番号をグループの基準にすること

顧客ごとの合計が表示されている。

練習問題3-3

ピボットテーブルは簡単でしょう。

10月17日の船井の合計売上金額は1,440円。

練習問題3-4

こんなピボットテーブルを作ってみました。たてに品番も単価も並べてあります。ポイントは「単価」を行ラベルあるいは列ラベルに設定するということです。すると、品番が104と321がどうも2種類の単価があるようです。

ここで、品番=104、単価=9980の合計金額の「59880」と書かれたセルをダブルクリックすると、以下の図のように、その計算のもとになったデータを別のシートに表示します。つまり、津川が値引きして、1001番の顧客に売ったというのが分かるというわけです。

また、品番=321、単価=220の合計金額の「880」と書かれたセルをダブルクリックすると、以下の図のように、その計算のもとになったデータを別のシートに表示します。つまり、小田中が値引きして、2001番の顧客に売ったというのが分かるというわけです。

練習問題3-5

ヒントの通りです。月の値のフィールドを、ピボットテーブルの行に指定すればいいです。