学習日記「Excelでできるデータの集計・分析を極めるための本」
はじめに
農作物の収量データを集計するためのフォーマットを現在Spreadsheetで作成しています。これまで数年間の収量データを作物ごと、圃場ごとに参照できるデータベースのような使い方をしたいな、というイメージです。
できるだけシンプルに記入・参照できるもの作りたいので、そのためには「テーブル設計」や「表計算の基礎知識」を身につける必要があると思い、手に取った書籍の１つです。
「ピボットテーブルも関数もぜんぶ使う! Excelでできるデータの集計・分析を極めるための本」
本屋でExcel本の棚を眺めている中で、表計算ソフトでデータを扱うためのセオリーみたいなものが一番わかりやすく書かれていそうだなと思いました。また、あまり関数やテクニックなどの情報が多すぎず、普段Excelを使っていない（ちょっとした関数の使い方は知っているというレベルな）自分でも理解しやそうかなというのも重要でした。
ひとまず「データを打ち込んで集計する」までを実現したいので、
・第１章「実践的な話の前に、最低限押さえておいてほしい６つの基本」
〜
・第４章「集計元データの転記＆表レイアウト変更のテクニック」
を学んでみたのでその記録用のアウトプットです。
ちなみに僕が使っているのはSpreadsheetなので、Excelとは機能が違っている部分があります。この辺の違いについても最後に備考として書いておきました。
この書籍が役に立ちそうな人
対象者としては、Excelを普段それなりに使ってるが、もっと集計や分析に生かすための基本的な考え方やノウハウを学びたい、というところかなと思います。既にExcelをバリバリ使っていてもっと複雑な関数処理をしたい、と言う場合には物足りないかもしれません。
（１）実践の前におさえるべき６つの基本
書籍の第１章。Excelを活用する際に、まず運用方法やデータの扱い方としての基本６か条みたいな感じです。かなり重要なところかと思います。この章のためにこの本を選んだくらいかもしれません（笑）
なんとなく「構造化データを使おう」だったり「こう言うシートはダメ」みたいなのは聞いていたけれど、しっかり体系的に理解していなかった項目が多かったです。
第１章の目次を引用させて頂きます。
1-1 「このブックを誰が使うか」を考慮して、使う機能を選ぶ
1-2 Excelの表は「人向け」か「PC向け」かでレイアウトを分けないとダメ
1-3 「見やすい集計表」の基本レイアウトとは
1-4 いまさら聞けないテーブルの基礎知識
1-5 集計/分析目的から逆算し、必要なデータを用意すること
1-6 数字の粒度や扱う用語は「読み手」に合わせること
「人向け」か「PC向け」かでレイアウトを分ける
と言うのはよく耳にする基本中の基本ですが、しっかり言語化して整理されていました。
・PC向けのレイアウト：基本はテーブル（テーブルの定義は後述）
・人向けのレイアウト：ピボットテーブルを活用した集計表など特定の条件で集計された表
「見やすい集計表の基本レイアウト」
集計表として単純集計表、クロス集計表、階層集計表、多重クロス集計表など色々な種類がありますが、
人にとって見やすくするために「なるべくスクロールなしで一目で見られるようにする」という考え方のほか、
「基本的に総計や小計を入れる」
「構成比や目標達成率や過去との比率などの、指標となる数値を加える」
という作り方をします。その集計表からどんな情報を得たいか、を考えて盛り込む要素を選別することも重要です。
また、全体を通して
「そのブックを使うユーザーが自分以外に誰がいるのかを考慮して、そのユーザーが使える機能（スキル的にもバージョン的にも）を選んで使う」
こういった気配りもとても大事だなあと思いました。
（２）テーブルの基礎知識
中でも大事なのでさらにまとめ。
PC向けのレイアウトとして「テーブル」のレイアウトにするのが大事とのことですが、テーブルの定義として
1. 見出しが1行
2. 1行1データ
3. 1列同一種類データ
これらを満たしている必要があります。
例えば、見出しが２行にわたっている表なんかもよく見るのですが、それは「テーブル」と言う定義には当てはまらないんですね。
（おそらく基本すぎてあまり強調して書かれてはいませんが、１つのセルに複数のデータを羅列して書いたり、セルの結合なんかももちろん使わないように）
そしてテーブルには必ず通し番号などの「主キー」が必要で、この主キーによってテーブル内のレコード（行）がひとつに決まるようにしないといけません。
主キーがあることで他テーブルから情報を参照したりする際の目印になるため、必ず盛り込む必要があります。
主キーの列は一番左が良さそうですね。
テーブル関連の用語を簡単に整理。
・フィールド名（カラム名）：一番上の見出しのこと
・フィールド（カラム）：列のこと
・レコード：行のデータのこと
（３）集計に便利な関数（基本編）
第２章〜第４章にわたって、データの集計や整形、集計元データからの転記について具体的なテクニックが紹介されています。その中でも基本的な関数に絞ってインデックス的にまとめます（個人的によく使うだろうという主観でピックアップさせていただきます）
データの集計に使う関数
合計値や個数、平均値などの簡単な集計結果を確認するだけなら、集計したいセル範囲をドラッグするだけで右下のステータスバーで確認することができますが、特定のセルに結果を表示させたいときに、関数を用いて入力します。
・SUM() ：セル範囲の合計値を返す
・COUNTA() ：セル範囲のセル個数をカウントした値を返す
ちなみにカウントする関数にはいくつかあり、COUNTA()関数だと空白セル以外をカウントで、COUNT()関数だと数値データが入力されているセルのみをカウントする、と言う細かい違いがありま。
SUM()関数の応用で累計を計算することもできます。積算温度などこれで算出できるなー。
・SUMIFS() ：特定の条件に一致する数値の合計を返す
・COUNTIFS() ：特定の条件に一致するセルの個数を返す
月別、地域別、カテゴリ別、担当者別など「○○別集計」をする機会は多いので、かなりお世話になる関数だと思います。
完全一致が基本なので、ワイルドカード（*や?など）を使って部分一致で判断することもできますが、割愛。必要になったら整理します。
データの整形（前処理）に使う
人が手入力でデータを入力する際には、どうしても細かい入力ミスや違うフォーマットで入力してしまう（表記揺れやデータ型の違い、重複データなど）ということが起こりがちです。その入力されたデータを、集計や分析に適した形式に直そう、と言うのがこのデータ整形、前処理の段階です。
関数は色々あるので１つだけ。
SUBTITLE()：文字列中の指定文字を新しい文字列で置き換えたものを返す
いわゆる文字列の置換ができる関数です。かっこの中の引数には置換したい元の文字列（セル）を指定して、置換したい元の文字列、置換後の文字列を入力してやる感じです。
引数に文字列を直で指定するときは ”” （ダブルクォーテーション）で囲います。
数値、文字列の型変換（数値や文字列など）や表示形式（日付形式やカンマ区切りの数字で表示したい時とか）についても関数で変換できるけれど、実際にはSpreadsheetを使っているとタスクバーからやってしまうケースが多い気がします。
データの「抽出」「分割」「結合」についてはまた少しテクニックが必要そうなので、これも必要に応じてじっくりトライしようと思います。
（書籍ではExcelのパワークエリが紹介されていますね）
データの転記に使う
データ転記はデータ整形作業の１つで、集計元データから別表にデータを紐付けて入力する、という作業になります。
手作業で行うと「集計元データから対応するデータを探してコピペ」という作業になるのですが、関数で自動化することもできます。それがよく耳にするこの関数。
VLOOKUP() ：表を縦方向に検索して特定のデータに対応する値を取り出して返す
「対応するデータを探してコピペ」を代理してくれる便利な関数です。
例えば売上集計表を作る際に、売れた商品IDを列に入力すると、集計元の商品データからIDと照合して商品名を自動的に引っ張ってきてくれる、みたいな操作。
引数にはまずその表の検索に使う値（例:商品ID）を指定して、次に別表（例:商品リスト）の検索範囲を指定、次に結果として返したい値(例:商品名)のある列番号を指定してやります。検索範囲を指定するときは、一番左に検索に使う値（例:商品ID）つまり主キーが来るように指定します。
この関数によって「２つのシート（テーブル）を紐付けている」とも捉えられるのかなーと思いました。データベース的に言うと、リレーションのあるテーブルを外部キーを使って参照する、というイメージでしょうか？（わかりづらかったらスルーしてください）
MATCH()関数、INDEX()関数を組み合わせると、VLOOKUP()関数ひとつだと転記するのが難しい色んなケースに対応できそうです。
実際に使っていかないと難しそうです。また別途まとめる機会を作りたいな、と思っています。
（備考）ExcelとSpreadsheetの操作で違うところ
もちろん提供元やレイアウトやファイルがローカルかクラウドかというのは違いますが、操作に関していうと、ざっとここまでの範囲で大きく違いを感じたのは
・Spreadsheetでは「テーブル化」ができない
・データの整形で大活躍のパワークエリがSpreadsheetでは使えない
・細かいところでExcelでしか使えない関数があったりする
・ピボットテーブルの操作がExcelとSpreadsheetで違う
中でも「テーブル化ができるかできないか」という違いは気をつけなければいけないなと思いました。
Excelの場合は表をテーブル化しておくことで、そのテーブルをフィールド（列）名で参照できたり、ピボットテーブルを作った際にテーブルのレコード（行）を増やしても自動で集計結果に反映されるように設定することできます。
ところがSpreadsheetではこのテーブル化ができないので、レコードが増えた時に対応できるようにピボットテーブルの集計元の範囲をあらかじめ選択しなければいけません。
おわりに
基本的な考え方と、基本の関数をさらっと触れてきました。
書籍の中ではピボットテーブルの活用も（タイトルにも書かれているぐらいなので）だいぶ重要な軸を占めているのですが、それについては実際に使いながら理解して、アウトプットできそうだなーと思ったらまとめておきたいです。
章ごとに簡単な演習問題とサンプルデータがついていて、実際に考えて使ってみる、という練習ができるので学習する身としてはとてもありがたいです。
５章以降の「データの可視化」「問題点や関連性の強さの分析」についても学んで活用していきたいところです。「ビックデータ時代の集計方法」「プロセス全体の仕組み化」という見出しも気になります。
この記事が気に入ったらサポートをしてみませんか？