Excelでは、様々な集計ができます。
しかし、ただ集計を勉強したい、と思っても、どこから手を付けたらいいかわかりません。
教科書を買ってきても、イメージがわかない題材だと、わかったような、わからなかったような、そんな感覚になってしまったこともあるのではないでしょうか。
練習するには、リアルなデータを使って、実際の仕事をするかのように練習しなければ覚えられないものです。
そこで、今回はインターネット上に公開されている「宮城県の水産物の水揚げ量」のデータを実際に使って練習したいと思います。題材がリアルになるだけでは練習になりません。練習するあなたもそれを仕事と思って練習に臨みましょう。あなたは、宮城県の魚を仕入れるお寿司屋さんということにしましょう。
データを読み取って、これからどんな商品が展開できるか考えてみましょう。
このように公開されているデータで勉強するのは無料でできます。
今回は、Excelの「=」から始まるような計算式や関数といった使い方ではない、もっと表計算ソフト的な側面で、なるべく専門用語なしで紹介したいと思います。
みやぎ水産NAVIとは
みやぎ水産NAVIは、宮城県の水産物の流通や消費に関するデータを提供するウェブサイトです。生産者や消費者、流通業者など、水産物に関心のある人々にとって有用な情報が掲載されています。
みやぎ水産NAVIでは、データをダウンロードすることができます。
ダウンロードしてみよう
このようなデータはすべてをダウンロードすると膨大な量になります。
そこで、ダウンロードするページは、単純にクリックしてダウンロードできる、という形ではなく、欲しいデータを選択し、それでダウンロードするということが必要になります。
Excelの知識ではありませんが、このようにどんなデータをダウンロードするのか、そのような操作も、データ集計の一つのスキルなので、慣れておく必要があります。
まず、「みやぎ水産NAVI」にアクセスします。
さて、このページのどこからデータをダウンロードするのでしょうか。
それを見つけなければいけません。これが慣れていないと人手間かかるかもしれません。
Webページをゆっくりでいいのでしっかり探す、ということが求められます。
また、直接「ダウンロードはこちらから」などのようには書いていません。ほしいデータは何か、考えながら探っていきましょう。
ページの真ん中くらいに、「水揚日報情報」とあります。
その表の下に「水揚データのダウンロードと過去の水揚日報情報はこちら」という文章を見つけることができます。
この「こちら」の部分が色違いの下線がある文字になっていて、クリックすると該当の情報のページにジャンプするリンクになっています。
では、このリンクをクリックしてみましょう。
「水揚データのダウンロード」コーナーがあります。
ここにある「魚種名一覧ダウンロード（Excelファイル）」のボタンに目が行きがちです。しかし、このボタンはあくまで魚の名前の一覧のExcelデータが表示されるだけで、水揚げ量のデータではありません。
「水揚データのダウンロード」の一覧にある、項目をクリックしてほしいデータを選択することが必要です。
選択する操作するものは次の3種類があります。
以下のような下向き三角が表示されているボックスは、下向き三角をクリックすると、選択肢が表示され、その中の指定したいものをクリックすれば選択できます。
以下のような小さな四角形は、クリックすることでそれを選択できます。この場合は市場に全市場を選択するというチェックを入れることができます。
＋のマークは、クリックすると詳細な内容を表示します。
市場の＋をクリックすると全部の市場が表示され、そのうちのデータのほしい市場だけ選ぶことができます。
2022年と2023年のすべての市場で、全部の漁業種、全部の魚類を指定すると次のようになります。
ここでまだダウンロードはできていません。まだ探すデータはこれだと指定しただけです。
ここで、このデータを探すことをするので「検索」ボタンをクリックするのです。
そうすると、データをどこに保存するのかを指定するウィンドウが表示されるので、保存したい場所を指定して、保存します。わかりやすい場所に保存しましょう。
ダウンロードしたデータから何がわかる？
まず、何はともあれ、ダウンロードしたデータをExcelで開いて見てみましょう。
今回、ダウンロードしたデータは、本来の形のExcelのデータの形をしていません。CSVという、文字が読めるものなら何でも読み出せる、そういった性質のファイルなのです。
そのため、素直に読みだせないときもあります。このようなダウンロードできるデータはいくつか種類があります。Excelで読み出せる形のデータのほとんどは、ExcelデータとCSVの2通りになると思います。
このCSVの場合は、右クリックして、プログラムから開くでExcelを指定し、開きましょう。
次のような内容のデータです。
さて、データを開いたらどんなデータでも、まずはどんなデータか見ていきましょう。
1行目に項目名が入っている表ですね。Excelの表は、このように1行目が項目名の場合とそうではない場合があります。
1行目が項目名になっている表では、データを集計するのが楽になるという特徴があります。そのかわり、一目見ただけでは、このデータになんの意味があるのかわからない形でもあります。
これからデータを集計していき、見やすい形に整えていって、それで初めて意味が見えてきます。
項目は、市場、魚種、漁業種、年、月、旬、水揚げ量（キロ）があります。
- 市場は、宮城県内の各市場ですね。本来であれば、自分のお店の近くの市場のデータを見ればいいのかもしれません。
- 魚種は、魚の種類ですね。細かく魚の種類が書いてあります。気になるのは「類」や「累計」と書いてあるところです。Excelは「カナガシラ類」のほかに「カナガシラ」になっているとその２項目は別のものとして集計します。「スケトウダラ累計」と「スケトウダラ」についても同じです。できれば事前にこのようなところは統一しておくとよいでしょう。あとで処理します。
- 漁業種は魚を獲った方法が書いてあるのかと思います。専門ではないので詳しくはわかりませんが、獲り方によって鮮度や味、見た目に差が出るのかもしれません。寿司で使う魚に適した漁業種があるのかもしれません。
- 年、月、旬は、水揚げした年と月、そして、月の中でも上旬か中旬か下旬かという違いも細かく分かれています。
- 水揚量は、数字で重さが書いてありますね。こういった数値は、例えば魚種によって、グラム、キログラム、トン、匹のような単位の違いがある場合もありますが、今回はキロで統一されているので、比較しやすいと思います。統一されていないと、単純に数字で比較できなくなるので、比較する場合、とても面倒な換算する必要があります。
このような表ですが、だいたいイメージできましたでしょうか。
おそらく、収穫した方が、どのくらい収穫できたか、漁協に申告し、それがまとまっているのではないかなと思います。
海産物や農産物が、このようなデータになるというのはなかなかイメージが付かないかもしれませんが、実際には、納品伝票のようなものがあって、それがまとまっているから、毎日、市場で魚が売られ、私たちもスーパーなどで魚を買うことができるのです。
また、そのデータが蓄積されていることによって、分析にも役立てることができます。
このデータは29846行まであります。1行目は項目名なので29845件のデータがあるということですね。
多いと感じましたか？1年で365日あって、魚の種類が全部で100種類あって、毎日記録していったら36500件のデータになります。今回は1日ごとではなく月を3つに分けて、1年を36に区切り、それごとに魚種、市場、漁業種で分けているのでこのデータ量になるのだと思います。おそらくその年月旬にその魚が取れなかった場合は0を記録しているのではなく、行自体がないようになっていると思います。
では、データを把握したら、今度はこのデータから何を知れるのかを考えていきましょう。
特にExcelということを意識せず、時間をたっぷりかけてもいいので手作業で、電卓で集計していったら、どんなことがわかるか、想像してみましょう。
まず、全体の重量は簡単にわかりそうですね。水揚量を上から全部足せば求められます。でも集計というのはただ合計を出すだけではありません。
例えば、この表から魚種が「アカムツ」だけを取り出しその水揚量の合計を求めます。同じように、全部の魚種ごとに水揚量を求めたら、魚種ごとの水揚量の合計を求めることができるので、一番獲れた魚はどれかわかりますよね。
同じように、この表から「2022」年のデータを取り出し、そのデータをさらに月ごとに取り出して合計を求めれば、2022年の月ごとの水揚量がわかります。何月くらいがピークかわかるのですね。それを「2023」年でも行えば、2022年と2023年で月ごとの漁獲量の変化を比べることができます。2022年のピークは9月だったけど、2023年は2か月遅れて11月だなぁということが明確にわかるようになるのです。
このような観点で考えていくと、どんなデータがわかるでしょうか。
- 市場ごとの水揚量ランキング
- 魚種ごとの2年間の漁獲量推移
- それぞれの市場で扱っているもので一番多い魚種
他にもたくさん求めることができると思います。思いついたものをメモしておきましょう。
次に、今度は、わかることの中で、自分が一番知りたいものをピックアップします。
たとえば、寿司屋さんを始めたいのであれば、寿司のネタになる魚はどこの市場が多く扱うのか、それがわかれば、その市場から近い方が輸送コストは安くなるはずです。
このように、自分は寿司屋さんなので、どういうデータがほしいのか、ということを考えていきます。
今回は、魚種ごとに2022年と2023年で獲れるようになった魚はどれで、とれなくなった魚はどれかというのを探してみたいと思います。
去年取れなかった魚はレア度が高く、価値も高いと感じている人も多いかもしれません。それをリーズナブルな値段で提供出来たら、ヒット商品になるのではないかと考えたのです。
では、まず練習で、その集計を進めてみましょう。
前の年よりも獲れたかどうかだけではなく、どのくらい多く獲れたかという数字が重要です。2年間の比較を魚種ごとに行いますが、このような場合、データを見たい年のデータを前の年のデータを基準として割る、前年比という求め方が一般的です。
まれに割り算の比ではなく、引き算の差で見た方がいい場合もあります。
今回は割り算の比を使います。
データを集計してみよう
いきなりこの表から前年比を求めることはできません。まずはひとまとめにするデータをひとまとめにしなければいけません。このひとまとめにすることこそ、集計なのです。
データの集計は、グループでくくって計算することです。
グループというのは、どの項目でまとめるのかということです。
魚種ごとの2022年と2023年の比較なら、グループは、魚種です。そして魚種だけではなく、年もです。
つまり年ごとに魚種ごとにグループにします。
さらに計算方法もポイントです。計算方法は、平均、合計、最大値、最小値などを求めます。ほとんどの場合は合計になると思いますが、本当に合計でいいのかよく考えて決定します。
ここまでの内容を表にすると次のようなイメージです。
縦に魚種、横に年毎にします。項目の多いものを縦に配置するとスッキリします。
これで年ごとに集計したものを割り算すれば、前年の2倍獲れたとか、半分しか獲れなかったとかがわかるのです。
では、魚種「アイナメ」の「2022」年のデータを合計してみましょう。
2022年のアイナメのデータを上から探していくと、2行目に見つかり、次は30行目、その次は48行目、49行目にあります。
このように上から下まですべて確認して、同じ年の同じ魚種の水揚量を、足していきます。
・・・・・・
大変ですよね。30000行をこの方法で集計するのはとても大変です。見落としてしまうかもしれなくて、そうなるとデータがおかしくなってしまいます。
１つの方法として、次のような方法もあります。
まず、魚種のデータのどこかで右クリックして、並べ替えの中の昇順をクリックします。
そうすると、魚種がアイウエオ順で並ぶので、「アイナメ」が一か所に集まります。
ここはポイントです。並べ替えすることによって一つのものを1か所に集めることができるということを覚えておいてください。
この状態だと、年がどういう順になっているかわからないので、ここで年でも昇順で並べ替えます。
そうすると、はじめに2022年のアイナメからのデータが2行から始まり、15025行目から、2023年のアイナメからのデータが始まるようになります。
これで、同じ年の同じ魚種が1か所に集まりました。
Excelではマウスのドラッグで選択すると、その範囲の合計を下の方に求めてくれます。
この合計値は、入力しなくてもクリックするとコピーできるので、Excelのほかの場所に貼り付けることもできます。
これを繰り返し行えば、集計ができるのです。
でも、本当に全部の魚種で2年分、この方法で集計しますか？
ピボットテーブルの使い方
Excelは表計算ソフトと呼ばれています。
その意味をとても簡単に表現すると、データの集計を簡単にしてくれるもの、と私は解釈しています。
本来、このような集計はExcelが得意で、上でお話ししたような手作業をせずとも、簡単に集計をする機能があります。
1種類のものであれば、ひとつひとつの値を足して電卓で求める、というが常です。それに対し、Excelは、複数の種類のものを別々に集計するのに使う、ということになります。
では、その集計がどれだけ簡単にできるか、やってみましょう。
そのためには、「挿入」タブの中のピボットテーブルを使います。
まず、集計元になるデータのどこかをクリックします。
「挿入」タブの中の「ピボットテーブル」のボタンをクリックします。
次のようなウィンドウが表示されるので、そのままOKします。
次のような画面になります。
前のデータは消えていません。下の方に「mizuage_」なんとかと書いてあるところに隠れています。ここをクリックすると表示できますが、このままにしておきましょう。
右の上のほうにあるこれは、元の表の項目一覧です。「水揚量（キロ）」は下に隠れているようなので、スクロールすると出てきます。
では、「水揚量（キロ）」を表示し、その文字を下の「値」のエリアにドラッグしてみましょう。
Excelのデータとして、水揚量の総合計を一瞬で計算しました。
今度は、「魚種」を「行」のエリアにドラッグしましょう。
何が起きたかお分かりになりますか？
一瞬で、総合計だった水揚量を、魚種ごとの合計に分割したのです。
では今度は、「年」を「列」エリアへ。
そうすると、縦方向に魚種ごとの水揚量の合計が求められていたデータに対し、横方向に年ごとにも分けることができました。
でもこの状態は、実際の合計値で合って、まだ最終的に求めたい比率にはなっていません。前年比は2022年に対する比率ですよね。
2023年のいずれかのデータを右クリックして「計算の種類」の中の「基準値に対する比率」を選択します。
次のようなウィンドウが表示されるので、基準フィールドを「年」、基準アイテムを「2022」に設定し、OKボタンをクリックします。
2023年における前年比を求めることができました。見えている範囲だと、アカムツが去年の10倍獲れているようですね。逆にイサキは不漁のようです。
今だと全部の魚種が表示されているので、一部の魚種だけを表示したい場合、行ラベルの▼をクリックすると、どのデータを表示するかチェックできます。
また、「カレイ」の種類だけを表示したい場合、魚種の名前に「カレイ」とついているものを探すことができます。
行ラベルの▼をクリックし「ラベルフィルター」の中の「指定の値で終わる」をクリックします。
カレイは「カレイ」か「ガレイ」で終わるので「レイ」で探した方がいいでしょうね。OKします。
カレイだけのデータを出しました。
実際の合計値も同時に表示したい場合は、今すでにある合計は比率を求めるために使っているので、もう一度「水揚量（キロ）」の項目を今の「合計/水揚量（キロ）」の下にドラッグします。
表が大きくなりすぎるのですが、2023年のデータとして、前年比の右に合計値を出すことができました。
こんどは、石巻だけのデータにしてみましょう。「市場」を「フィルター」のエリアにドラッグします。
上に「市場」が出てきます。この▼をクリックし、市場一覧が表示されるので、その中から「石巻」を選択して、OKボタンをクリックします。
石巻だけのデータになります。
このように▼で選択すれば他の市場だけのデータを見ることができます。
値、行、列、フィールドの各エリアに入れたものは、▼をクリックすれば「フィールドの削除」で削除することができます。
このピボットテーブルのデータを使って報告書などを作成する場合、ピボットテーブルのデータは特殊な範囲になっているので、他の場所にデータをコピーし、項目名を変えてからグラフ作成や書式設定を行いましょう。
このとき、縦横の合計を含めてコピーすると、ピボットテーブルとして貼り付いてしまうので、普通のデータにするために縦横計は選択しません。
ピボットテーブルの作成順序は次の通りです。
- 1行目が項目名になっている元のデータをExcelで開く
- 元のデータの範囲のどこかをクリック
- リボンの「挿入」の中の「ピボットテーブル」のボタンをクリックする
- そのままOK
- 「値」エリアに合計したい項目を入れ、総合計を出す
- 「行」エリアに縦に分割したい項目を入れ、分割する
- 横にも分割したい項目があれば「列」エリアに横に分割したい項目を入れ、分割する
- 必要に応じて、集計された値を右クリックして、計算の種類を変更する
- 一部だけ出したい項目があれば「フィルター」エリアにドラッグし、選択する
- ピボットテーブルが出来上がったら、別の範囲にデータをコピーする。
ここまでできたら、このデータからこういうことが知りたいなと思っていたことを、実際に集計して求めてみましょう。
まとめ
どうしてもExcelを覚えるときは、計算式や関数といったことから入って覚える形になっていますが、それは本来のExcelの役割と比べたときに、Excelはもしかしたら電卓のようにただ計算するだけの便利なものというのが本来の姿なのでは？と思い始めています。
今まで、ピボットテーブルは中級者以上のものとされていました。しかし、よく考えてみたら、こんなに計算が簡単にできるピボットテーブルって、もしかしたら初心者こそ使うべきなんじゃ？と思ったので、今回、勉強方法を提案しました。
Excelの本来の役割を、大量の一目見ただけではわからないデータを、グループごとに仕分けしてそれぞれの合計などを求めることと設定しました。
そうすると、四則計算やSUM関数の前に（相対参照、絶対参照の前に）、集計機能がすでにあるから使ってと伝えたかったのです。
そうすればExcelの敷居がぐんと下がると思います。いつまでも難しい、大変、めんどうくさい、というExcelの使い方を一歩先に進めるのではないかと思いました。
もちろん、ピボットテーブルがゴールではなく、いずれ関数や絶対参照を理解しないといけないときがきますが、まずは、表計算で集計するというのはこういうことなんだと、簡単操作で集計ができるピボットテーブルで理解できるので、では仕分けする関数は何があるかな？どういうシチュエーションだとピボットテーブルでは無理なのか、ということがわかってくると思います。
今回は、Excelが何なのかを無料で覚える効果的な方法について紹介しました。
