第3回 - 2019/10/11

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


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

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

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

集計機能の利用

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

並べ替え

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

オートフィルタ

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

集計処理

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

ピボットテーブル

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


本日の演習

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

練習問題3-1

練習問題3-2

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

練習問題3-3

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

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

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

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

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

練習問題3-5

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

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


解答

練習問題3-1

個数の多いものから順に並べ替える。個数が入力されているG列のいずれかのセルを選択して、「並べ替えとフィルター」グループにある「降順」ボタンをクリックする。

金額の多いものから順に並べる。金額が入力されているH列のいずれかのセルを選択して、「並べ替えとフィルター」グループにある「降順」ボタンをクリックする。

練習問題3-2

まず、顧客番号順に並び替える。顧客番号が入力されているB列のいずれかのセルを選択して、「並べ替えとフィルター」グループにある「昇順」ボタンをクリックする。その後、「アウトライン」グループにある「小計」ボタンをクリックする。 

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

顧客ごとの合計が表示されている。アウトラインの「2」ボタンをクリックして、合計だけを表示した。

練習問題3-3

ピボットテーブルは簡単でしょう。右側に出るピボットテーブルのパネルから「日付」をその下の「行」にドラッグすると、「月」「日付」のように、「月」が加わります。この時、「月」をクリックして削除をして、「日付」だけを残すようにすれば、列は日付だけになります。右側のパネルの上のリストにある「日付」を左側のワークシートの列の部分にドラッグ&ドロップすれば、「日付」だけが1列目に設定されます。

練習問題3-4

こんなピボットテーブルを作ってみました。たてに品番も単価も並べてあります。ポイントは「単価」を行ラベルあるいは列ラベルに設定するということです。すると、品番が104と321がどうも2種類の単価があるようです。集計結果を「金額」の個数にしてあるので、いずれも1回だけ販売したものであることがわかります。

ここで、品番=104、単価=9980の個数/金額の「1 」と書かれたセルをダブルクリックすると、その計算のもとになったデータを別のシートに表示します。つまり、津川が値引きして、1001番の顧客に売ったというのが分かります。同様に、品番=321、単価=220の個数/金額の「1 」と書かれたセルをダブルクリックすると、その計算のもとになったデータが別のシートに表示され、小田中が値引きして、2001番の顧客に売ったというのが分かります。

練習問題3-5

ヒントの通りです。曜日の値のフィールドを元データに増やします。新たに増えた曜日の列には式が設定されていますが、式は同じ行のセルしか参照していないので、そのまま下にコピーすれば良いでしょう。

値は「個数」列の合計を表示するようにしてみました。