Excelのピボットテーブルとマクロについて。
Excelを使用して驚いた3大機能の内の2つがピボットテーブルとマクロという機能。他1つは関数やグラフなど馴染みある基本的な機能、対してピボットやマクロは使い所が限られハードル高い。
そこで教材を用意したので一緒にやってみましょう。
クリックで覚えるピボットテーブルとマクロの使い方
どういう機能か簡単に説明。
- ピボットテーブル・・・自動集計
- マクロ・・・録画と再生
自動集計と言われても使い方がわからない、マクロの方はプログラミングの知識が必要そうなので難しそう、と思っていたのが10年くらい前までの私。
当サイトで400～500票クラスのアンケート結果をグラフ化したり、Googloeフォームの円グラフを棒グラフ化したり、年代でクロス集計しているアレはピボットテーブルを使用し数分で集計している、と言えばわかりやすいだろうか。
先日の記事のクロス集計がそう。
音楽と騒音や映像とかゲームに関するアンケート結果 - BTOパソコン.jp
https://bto-pc.jp/research/ton-vis-res-results.html
コピペや関数を駆使してここまで持って行こうとすると労力が半端ないところ、ピボットテーブルなら5分もあればできてしまう。しかもクリックだけでOKというExcelの便利さを体験可能。
ここから全部クリック操作のみ。キーボードは接続しなくてOK。外さなくていいので接続したまんまでももちろんOK。
クリックで覚えるピボットテーブルの使い方
今パソコンから見ておりExcel使えるなら実践してみましょう。ダウンロードするファイルサイズは約6KB、ながら情報ぎっしり。
https://bto-pc.jp/2018/12/05/20181201.zip
ファイルをダウンロードしてZIPを展開するか中身を見ると90KBくらいのCSVファイルが1つあるので開く。内容はアンケート結果のソースで日時の列だけ削除したもの。
IPアドレスなどの個人絡みは最初から記載なし。念のため行はシャッフルしており、誰がどう答えたかは絶対わからないレベルとしております。
1行目が質問内容、列は各回答という単純な構成。
この状態からフィルタして件数を拾い・・・とかやると日が暮れてしまうためピボットテーブル。
画像のインターフェイスはExcel 2007（サポート切れ）だけれども他のExcelもだいたい同じはずで、挿入タブの中にあるピボットテーブルボタンを押す。
右下のような窓が出て自動的に全体を範囲指定してくれるので何も変更せずOKボタンをどうぞ。
すると新規シートが作成されこのような状態になるはず。
Sheet1が作られ、左のピボットテーブルの枠にフォーカスが合っている（クリックした状態になっている）ので右へ何するか一覧が表示されております。
問1を集計してみましょう。
上の赤枠内の質問内容をドラッグして下の赤枠2箇所、行ラベルと値の中へ入れるだけで自動集計。
並びを、はい＞いいえ＞どちらでもない、にしたいなら「はい」のセルをクリックしてセル上部をドラッグすれば行の入れ替えが可能。セル1つでOK、行を切り取りペースト挿入は不要。ここでは並びはこのまんまで参ります。
この状態でグラフを作ることもできるけれど、それではせっかくのピボットテーブルの威力がわからないので年代とのクロス集計へ。
「年代をどうぞ」をドラッグして列ラベルへ入れると列が年代に分かれ、それぞれの年齢に応じた集計結果が自動的に表示される、これが凄い。マイクロソフトが凄いとは言っていないし、Lotus 1-2-3のダイナミッククロス集計をパクったのかも知れないし。
それは良いとして、「（空白）」となっている未回答はクロス集計する意味がないため、列ラベルと書かれたセルの右にある▼を押して空白を除外するフィルタを実行。
次いでカーソルをピボットテーブルの集計リストの上（どこでもいい）に乗せた状態でグラフの種類を選んでみましょう。
ここでは100％になる積み上げ横棒グラフで。どうでも良いけれど、私は3Dとかグラデーションしたりの変な小細工は見づらいだけなので嫌い。
1セルしか指定しなくともピボット上ならグラフも自動で作成される。
後は通常のグラフいじる手順と同じ。
注意することはピボットテーブルの中身変えると当たり前的にグラフのデータも変わるため、私がアンケート結果をいくつも集計する際はこのシートを何度もコピーしたり、ピボットテーブル上のデータを別の場所へコピペしたり、画像を都度保存しております。
ついでに、保存するならExcelのブック形式で。
CSVはテキスト状態なのでグラフなど保存できない点も当たり前感。
以上、本当にクリックだけでできてしまう、ウルトラ超効率的集計機能がピボットテーブル。何ができるかとこの動作を覚えておいて損はしない。
クリックで覚えるマクロの使い方
もう一つのクリックだけで使うマクロ教室も簡単に参りましょう。
開発タブの中にあるマクロの記録を押すと録画スタート、の前にマクロ名を命名したりショートカットの設定も可能。今回はマクロの名前は変更せずショートカットを「m」とした。
セルA1～F1に1～5の数値を入力して記録終了ボタンを押す。
どうしてもキーボード使いたくないならセルの色を変更とかクリックだけの動作をマクロで記録してどうぞ。メニュー内の操作以外、言い換えるとセル内の動作は何でもといえるほど録画してくれる。
話を戻し、Ctrl+mキーを押せば一瞬で1～5の数値が入力される、これがマクロ。私が録画と再生と表現していた意味がおわかりかと。
ソースコードを見るなら上の画像、右の赤枠のコードの表示で。クリックだけでやるなら赤枠左のマクロを押して選んで実行。
下の画像、左にある標準モジュールの中のModule1をダブルクリックするとソースコードが表示。
故意にA1セルを選択し忘れた。本気で忘れていたウソだけれども。
記録ボタンを押した後にA1セルを選択して1～5の数値を入れるとA1から始まり、上の状態だとカーソルが合っているセルに「1」が入り、その後にB1、C1～と入力するコードになっていることが読み取れるかと。
こうして録画した後にソースコードを眺めていると、「この場合はこう書けば良いのか」がわかるようになりプログラミングの勉強にもなる。
実際にExcelのマクロはVBA（Visual Basic for Applications）とほぼイコールなのでマクロを素で書けるようになればプログラマとしての第一歩。私は効率厨なので自分に必要なコードしか覚えないので1歩も踏み出していないけれど。
今回は簡単な動作しかしていないけれど、シートをコピーしたり新規作成したり、ボタンを設置して押せばマクロが動作する、印刷ボタンなどの設置も可能。
大昔、小さな会社に居た頃にExcelで会計ソフトもどきを作った際、日計を自動で月計してグラフも自動で作成し翌月分のシートを作成するところまでやったことがございます。
注意点は、マクロはマクロウィルスというウィルスを仕込む事ができるため、現在は多くの表計算ソフトで「マクロ入っているから無効にした」と無効化されるはず。
自作したり信頼できるファイルならば手動で実行を。
余談ながらクソくだらない注意点としては、デジタル情弱な上司には私がパソコンで遊んでいるようにしか見えなかったようで、日次や月次レポートが手書きや手入力で数十分かかるところ、私が数分でできるようにしてしまい事務員から大感謝され上司大嫉妬というクソくだらない過去を思い出したので取扱や環境にご注意あれ。
さすがに今どきそんなアナログ的な情弱上司は居ないかとも思うけれど、彼が生きているなら今48歳くらいなので割と若い。
ピボットテーブルで世界と効率が大きくが変わった
私は当サイトのアンケート集計するという遊びのためにピボットテーブルを覚えたわけではございません。
元はといえば、BTOメーカーの修理工場勤務していた時、喫煙所で社長と雑談中に「各担当者の修理完了台数と保留数と平均修理完了時間を集計してみたい」とお漏らししてみたところ、ピボットテーブルを使えば良いと言われ修理現場で実践してくれたことがきっかけ。
余談ながら、なぜそういう集計をしたかったかは、私が現場の修理担当で最も早く数多くパーペキに修理完了している（はず）に対し、能書きと値打ちこくだけのPCオタクな現場仲間が言うほど仕事（作業）していないだろうと証明したかったため。
多くの仕事管理ソフトでは今回のアンケートのような一覧形式になるデータベースという形で管理されているため、アンケートや修理状況以外にも同じ形式でデータが保管されていることが多い。
具体的な例としては、以前企画系の部署にいた頃は市場調査のデータを今回のアンケート結果のように年代で分けてみたり、経理部署の時は出張中に弥生という会計ソフトのデータをエクスポートしテキスト形式で送ってもらい、CSVへ変更してExcelで取り込みピボットテーブルで勘定科目別に金勘定の確認も。
現在は経営企画系なので、営業担当者別の売上データを取り出して曜日や時間帯に分けて実績を確認するなど、元のデータをデータベース形式にしておけば数分かつクリックだけでグラフ化までできてしまう。
もっと言えば、業績の良い営業社員へいくらおごったかのコスパまで実はExcelで計算するという、リアルヒツジ先輩怖すぎ笑えないこともしていたり。
もし今回のピボットテーブルとマクロの話がアホほど役に立つような仕事をしておられ、どちらも習得しつつ向上心もあるほどならばAccessにも手を出してみましょう。ピボット+マクロの上級者向け、だと思う。私はAccess使えないので不明ながら多分そう。
Excel使い始め当初の関数やグラフ化には当時感動したけれど、マクロとピボットテーブルは度肝を抜かれたという表現が合っているほど凄い機能。人生変わった（今の立場になれたのもマクロやピボットのおかげ）と言えども大げさではございません。
まだ学生でも就職して現場で「マクロ書ける」とか「ピボットテーブル使える」と言えるなら私が一目置くほどであり、遊びからでも良いし、一度だけでも体験しておいて欲しい。
その前に基本的な関数、SUM、IF、COUNT系がわかる程度は当然として、ながら、先にマクロやピボットから入るExcel入門はアリだと思う。中々今回のような教材的データが無いのでピボットテーブルは入りにくい。
アンケート協力してくれた約517人の皆様に感謝しつつ学べ。
おまけ：関数とピボットを組み合わせた例
教材ファイルは日時のA列を削除したのでこの集計はできないけれど、ふと「アンケートは何日くらいで締（閉）めて良いのだろう？」と思ったので何となく集計してみた。
A列に「2018/11/19 0:00:00 午前 GMT+9」のような形式で日時があり、日付だけ取り出したいのでデータの一覧表右端に「=LEFT(A1,10)」と書いて最下行までコピーすると「2018/11/19」までが取り出せる。※=LEFT～：A列の左から10文字の意味
その列だけを選択してピボットテーブルにして行ラベルと値に日時を入れてグラフ化したものが上の画像。このグラフが出力されるまで1分48秒とかExcel有能すぎましょう。
結果、アンケートは最低7日間、長くても2週間もやれば良いことが判明。といった感じで、ピボットテーブル（やマクロで）どういうことができるのか知っておけば必要な時に使いどころがわかるという実例。
その他、下に出ているはずの関連リンク先はコメント常連の庶民Aさんと私のコラボによる保存版レベルな良記事なので、最近の若者にはおすすめというか絶対に読んでどうぞ。
そのリンク先の最後のこれはややウソ。
ちなみにピボットテーブルから直接グラフを作る技は、このブログでのアンケート結果集計で覚えたほど実践派。
覚えたのではなく常用レベルになった、として。
私はピボットテーブルより関数をよく使いますね。利用頻度は群を抜いてIF系が多し。
>コピペや関数を駆使してここまで持って行こうとすると労力が半端ない
＞この状態からフィルタして件数を拾い・・・とかやると日が暮れてしまう
手動でやるとしたら
1．フィルタをかけて30歳未満の行だけ抽出する
2．打鍵音の列で「はい」「いいえ」「どちらでもない」の数を拾う
3．30代の行だけ抽出するため以下略
4．拾った数をアンケート総数で割りパーセントを出す
5．グラフを作る
これが原始的な方法でしょうか。私だと恐らく40分は掛かりますね。何より「間違っていないか検証できない」点が最大のウイークポイント。ピボットテーブルは有用。
本当に原始的な方法は目視で数えて電卓で計算する、ですけれど。
＞私は3Dとかグラデーションしたりの変な小細工は見づらいだけなので嫌い
棒グラフで3Dにすると、棒が100％に届いているのかいないのか、パッと見で判別しづらいですね。
＞マクロ
1から作ることは無く、やりたい動作を「○○ マクロ」なんぞで検索し、誰かが作ったマクロを95％くらいパクって使う機会が多いです。自動記録を使う機会はほぼ無し。
＞開発タブの中にあるマクロの記録
ちなみに標準で開発タブは無いはずですから、まずは開発タブの表示から始め。
Office サポート｜[開発] タブを表示する
https://support.office.com/ja-jp/article/-%E9%96%8B%E7%99%BA-%E3%82%BF%E3%83%96%E3%82%92%E8%A1%A8%E7%A4%BA%E3%81%99%E3%82%8B-e1192344-5e56-4d45-931b-e5fd9bea2d45
パソコントラブルＱ＆Ａ｜エクセル（Excel）のリボンに「開発」タブを追加表示
https://www.724685.com/weekly/qa111027.htm
＞Access
あれ私もさっぱり分かりませんね。そもそもオフィスのProfessionalでないと入っていないはずですし、エクセルワードだけで十分だろな職場だと実物を見たことが無い可能性も。
私的エクセル3大機能を今決定。3大というより衝撃的だった3つがこれ。
1.SUM関数
2.マクロ
3.ピボットテーブル
SUMはパソコンを使用していても「エクセル何それうまいの？」だったものの、売上データや経理事務を手書きしていた職場で導入された最新のWindowsで知りビビった。
=sumと書いて（昔のエクセルは）範囲をドラッグすれば全部足し算されるとか、こんなことができるなら自力で貸借や試算表どころか決算書まで出来てしまうと直感し感動した記憶がございます。実際に作った、もう10年以上前の話。
それから「ボタン押せば印刷できるマクロ」を録画して編集して何かと自動化してみたり、ピボット使ってPC修理工場で「お前は私より修理完了台数大幅に少ないですよね？」とドヤってみたり。それでも「フーン」だったので社長へ「このクズと一緒に働きたくない」と言って「じゃあこっち来な」と大本営へ…。
ピボットテーブルの何がハードル高いかは、今回の記事で勝手に教材化させてもらったファイルのように、ひとえに「何に使えるか体験できない」なのが認知されない原因だと思い勝手に使った。
（勝手ながらアンケート投票に）ご協力ありがとうございます。
＞最近の若者へ
私が学生の頃は元々数字に強いわけではなかったけれど、ゲーム（デジタルに限らずアナログ、ビリヤードとか）で勝率とかにこだわったり、経理事務のバランスシートの美しさとは逆のえげつない資産の中身に興味を持った経緯がございます。
経理ゲームは経営に通じるし、経営は経理なくして語れない。公務員になるなら関係ないかも知れないけれど、民営ならば営業大好きな人でも経理事務、せめて決算書だけでも見て数分で自分の会社がどうなのかわかるくらいになってほしい。
デカい企業なら公告あるから毎年見るべき。中小は公開しないこともあるけれど、経理部署とか可能なら役員へ「前期（または過去3期くらい）の決算の貸借と試算を見たい」と言えばOK。私なら「こいつデキる（のか？）」と疑うので、せいぜいハッタリ効かせて自分で自分のハードル上げなさいと言いたい。
そういうのは抜きにして、経理や経営の人へ「決算書の見方教えて」と言うのはチート並に有効。「こいつ会社のこと真剣に考えてくれているのか？」と思ってしまうので、やるなら真剣にどうぞ。真剣になれない会社ならやめておくべき。
経営者は経理を熟知しているし、経理（自社の数字）熟知した人間に対して上辺だけの真剣さはウソだとすぐバレる。
追記：在籍中なブラック企業で決算書とか見せろと言ったらクビか左遷になると思うので自己責任にて。中小ならそれ以前に「就業規則」や（労働組合ない会社規模なら）「36（さぶろく）協定」知らないならブラックだと思うべし。※どちらも労働者側が知る権利と野党側は提示する義務がある。
余談ながら私は今雇ってくれている現親会社の面接時「その他何か質問ありますか？」と聞かれて前期の決算見たいと言い、後日本当に郵送されて来たので「来週から出社したい」のように言った気がする。もう二度とブラックには勤めたくなかった。