クリックで覚えるピボットテーブルとマクロの使い方

2018年12月13日

Excelのピボットテーブルとマクロについて。

Excelを使用して驚いた3大機能の内の2つがピボットテーブルとマクロという機能。他1つは関数やグラフなど馴染みある基本的な機能、対してピボットやマクロは使い所が限られハードル高い。

そこで教材を用意したので一緒にやってみましょう。

クリックで覚えるピボットテーブルとマクロの使い方

どういう機能か簡単に説明。

  • ピボットテーブル・・・自動集計
  • マクロ・・・録画と再生

自動集計と言われても使い方がわからない、マクロの方はプログラミングの知識が必要そうなので難しそう、と思っていたのが10年くらい前までの私。

当サイトで400~500票クラスのアンケート結果をグラフ化したり、Googloeフォームの円グラフを棒グラフ化したり、年代でクロス集計しているアレはピボットテーブルを使用し数分で集計している、と言えばわかりやすいだろうか。

先日の記事のクロス集計がそう。

音楽と騒音や映像とかゲームに関するアンケート結果 - BTOパソコン.jp
https://btopc.jp/research/ton-vis-res-results.html

他人のキーボード打鍵音気になる?(年代別)

コピペや関数を駆使してここまで持って行こうとすると労力が半端ないところ、ピボットテーブルなら5分もあればできてしまう。しかもクリックだけでOKというExcelの便利さを体験可能。

ここから全部クリック操作のみ。キーボードは接続しなくてOK。外さなくていいので接続したまんまでももちろんOK。

クリックで覚えるピボットテーブルの使い方

今パソコンから見ておりExcel使えるなら実践してみましょう。ダウンロードするファイルサイズは約6KB、ながら情報ぎっしり。

https://btopc.jp/2018/12/05/20181201.zip

ファイルをダウンロードしてZIPを展開するか中身を見ると90KBくらいのCSVファイルが1つあるので開く。内容はアンケート結果のソースで日時の列だけ削除したもの。

IPアドレスなどの個人絡みは最初から記載なし。念のため行はシャッフルしており、誰がどう答えたかは絶対わからないレベルとしております。

pivot-table-01-source

1行目が質問内容、列は各回答という単純な構成。

この状態からフィルタして件数を拾い・・・とかやると日が暮れてしまうためピボットテーブル。

画像のインターフェイスはExcel 2007(サポート切れ)だけれども他のExcelもだいたい同じはずで、挿入タブの中にあるピボットテーブルボタンを押す。

pivot-table-02-pt

右下のような窓が出て自動的に全体を範囲指定してくれるので何も変更せずOKボタンをどうぞ。

すると新規シートが作成されこのような状態になるはず。

pivot-table-03-sheet

Sheet1が作られ、左のピボットテーブルの枠にフォーカスが合っている(クリックした状態になっている)ので右へ何するか一覧が表示されております。

問1を集計してみましょう。

pivot-table-04-start

上の赤枠内の質問内容をドラッグして下の赤枠2箇所、行ラベルの中へ入れるだけで自動集計。

並びを、はい>いいえ>どちらでもない、にしたいなら「はい」のセルをクリックしてセル上部をドラッグすれば行の入れ替えが可能。セル1つでOK、行を切り取りペースト挿入は不要。ここでは並びはこのまんまで参ります。

この状態でグラフを作ることもできるけれど、それではせっかくのピボットテーブルの威力がわからないので年代とのクロス集計へ。

pivot-table-05-cross

「年代をどうぞ」をドラッグして列ラベルへ入れると列が年代に分かれ、それぞれの年齢に応じた集計結果が自動的に表示される、これが凄い。マイクロソフトが凄いとは言っていないし、Lotus 1-2-3のダイナミッククロス集計をパクったのかも知れないし。

それは良いとして、「(空白)」となっている未回答はクロス集計する意味がないため、列ラベルと書かれたセルの右にある▼を押して空白を除外するフィルタを実行。

pivot-table-06-space

次いでカーソルをピボットテーブルの集計リストの上(どこでもいい)に乗せた状態でグラフの種類を選んでみましょう。

pivot-table-07-gp

ここでは100%になる積み上げ横棒グラフで。どうでも良いけれど、私は3Dとかグラデーションしたりの変な小細工は見づらいだけなので嫌い。

1セルしか指定しなくともピボット上ならグラフも自動で作成される。

pivot-table-08-finish

後は通常のグラフいじる手順と同じ。

注意することはピボットテーブルの中身変えると当たり前的にグラフのデータも変わるため、私がアンケート結果をいくつも集計する際はこのシートを何度もコピーしたり、ピボットテーブル上のデータを別の場所へコピペしたり、画像を都度保存しております。

ついでに、保存するならExcelのブック形式で。

pivot-table-09-save

CSVはテキスト状態なのでグラフなど保存できない点も当たり前感。

以上、本当にクリックだけでできてしまう、ウルトラ超効率的集計機能がピボットテーブル。何ができるかとこの動作を覚えておいて損はしない。

クリックで覚えるマクロの使い方

もう一つのクリックだけで使うマクロ教室も簡単に参りましょう。

macro-01-rec

開発タブの中にあるマクロの記録を押すと録画スタート、の前にマクロ名を命名したりショートカットの設定も可能。今回はマクロの名前は変更せずショートカットを「m」とした。

セルA1~F1に1~5の数値を入力して記録終了ボタンを押す。

macro-02-end

どうしてもキーボード使いたくないならセルの色を変更とかクリックだけの動作をマクロで記録してどうぞ。メニュー内の操作以外、言い換えるとセル内の動作は何でもといえるほど録画してくれる。

話を戻し、Ctrl+mキーを押せば一瞬で1~5の数値が入力される、これがマクロ。私が録画と再生と表現していた意味がおわかりかと。

ソースコードを見るなら上の画像、右の赤枠のコードの表示で。クリックだけでやるなら赤枠左のマクロを押して選んで実行。

下の画像、左にある標準モジュールの中のModule1をダブルクリックするとソースコードが表示。

macro-03-source

故意に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人の皆様に感謝しつつ学べ。

おまけ:関数とピボットを組み合わせた例

LEFT-A-10

教材ファイルは日時の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さんと私のコラボによる保存版レベルな良記事なので、最近の若者にはおすすめというか絶対に読んでどうぞ。

そのリンク先の最後のこれはややウソ。

ちなみにピボットテーブルから直接グラフを作る技は、このブログでのアンケート結果集計で覚えたほど実践派。

覚えたのではなく常用レベルになった、として。

今月のオススメBTOパソコン

リンク用ソース

コメント(3)

私はピボットテーブルより関数をよく使いますね。利用頻度は群を抜いて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

パソコントラブルQ&A|エクセル(Excel)のリボンに「開発」タブを追加表示
https://www.724685.com/weekly/qa111027.htm

>Access
あれ私もさっぱり分かりませんね。そもそもオフィスのProfessionalでないと入っていないはずですし、エクセルワードだけで十分だろな職場だと実物を見たことが無い可能性も。

コメントする ※要ユーザ登録&ログイン

BTOパソコンメーカー比較

パソコン工房

高性能: ★★★★ 長保証: ★★

コスパ: ★★★★★ 安保証: ★★

部品選: ★★★☆☆

初心者や実用重視のPCユーザー向け。デスクトップ以外にノートも多数有り価格と性能のバランスが良く選びやすい。全国に実店舗が多数有るBTO PCメーカー。

マウスコンピューター

高性能: ★★★★★ 長保証: ★★

コスパ: ★★★★ 安保証: ★★

部品選: ★★★☆☆

パソコン工房と同様に機種が多くノートの取扱も有りゲーム用やクリエイター向けPCも有り。送料が比較的高額なので総額注意。知名度は国内トップクラス。

ドスパラ

高性能: ★★★★★ 長保証: ★★

コスパ: ★★★★ 安保証: ☆☆

部品選: ★★★☆☆

デスクトップPCならパソコン工房とマウス以外にドスパラも見る価値有り。秋葉原に本拠地を構える昔ながらのBTOパソコンメーカーで知名度はマウス並に高い。

DELL

高性能: ★★★☆☆ 長保証: ★★

コスパ: ★★☆☆☆ 安保証: ☆☆

部品選: ☆☆☆☆

2011年頃から安く無くDELLを選択肢に入れる理由が薄く回線抱き合わせ販売の価格がまぎらわしい。ゲームPCのAlienwareは見た目重視コスパ最悪なので要注意。

日本HP

高性能: ★★★★ 長保証: ★★

コスパ: ★★★☆☆ 安保証: ★★

部品選: ☆☆☆☆

高性能ノート以外は見る価値が薄く高額なLenovoという感じ。早々にWindows 7を捨て8に切り替えたので7が必要なら行っても無駄。2012年後半から迷走中。

TSUKUMO

高性能: ★★★★ 長保証: ★★

コスパ: ★★☆☆☆ 安保証: ★★

部品選: ★★☆☆☆

ヤマダ電機による買収後はマニアックな感は無くなり家電通販のような普通のパソコン屋に。BTO PCは機種が少なくやや割高。ツクモファンなら選択肢へ。

FRONTIER

高性能: ★★★☆☆ 長保証: ☆☆☆

コスパ: ★★☆☆☆ 安保証: ☆☆☆

部品選: ★★☆☆☆

2013年7月に(株)KOUZIROが倒産しヤマダ電機子会社インバースネットが続投。長期保証が消滅。これと言った特徴が無く難点は有れど利点が見当たらない。

サイコム

高性能: ★★★★★ 長保証: ★★

コスパ: ★★☆☆☆ 安保証: ★★★

部品選: ★★★★★

自作しない中~上級者のPCユーザ向け。初心者にはカスタマイズが難しく動かない構成でも購入できるので注意。パーツへのこだわりや知識が有るなら。

※並びはBTOメーカーの知名度の順。大小関わらず信用できない要素があるメーカーは未掲載。

勝手に評価シリーズ

結果として宣伝になっていますが依頼されたわけでは無く、依頼されてもやりません。

データ復旧のIUECを勝手に評価
あなたの街の~を勝手に評価
ESETセキュリティを勝手に評価

お知らせ

Windows 7サポート終了は2020年1月14日


カテゴリと更新通知

プロフィール

ヒツジ先輩

書いてる人:

BTOパソコンの元修理担当。ハードウェアに超詳しいワケではありませんが、どうしたら故障するのか何となく解るので壊れにくいパソコンを紹介します。