ピボットテーブル活用術(４)だれでも使える予算管理表！ 関数・マクロでは実現できなかった夢を実現！

ＡＩ時代にも生かせる経理４７年の集大成！
ピボットテーブルはマウスを使って、慣れて、身につけるツールです！

サンプルエクセルはこちらからダウンロード
毎日使える予算管理表　

この記事の要約をYoutubeで視聴できます　NotebookLM

１．理想の予算管理表を目指して４７年！

Ｅｘｃｅｌで予算管理レポートを作っている人が多いと思いますが、
みなさん、満足できていますか？
経理や、システム、エクセルの専門家がいるのに、
だれでも利用できる「予算管理術」は紹介されていません。

私は、４０年以上経理関係の実務に携わりました。
専門家の人と比べて知識は乏しいですが、経営管理(経理)、システム、
エクセルの３つの実践を重ねてきました。
この実践から生みだしたのが、「私の」理想の予算管理表です。
個々の知識だけで創り出したのではありません。

だれでも使える予算管理表　４つのメリット
マウス操作で、関数・マクロを使わずに実現！

①年間の実績見込が１表で見通せる
②毎月の予算を日数割で計算し、月の途中での予算差が管理できる
③マウス操作で「システム化」、最新データを用意し「更新」でデータ分析
④ユーザーがカスタマイズして「自分のシステム」に、習得・引継ぎが容易

こんな人に使って欲しい！

◎このエクセルをカスタマイズして、皆さんの実践に活用してください。
◎「ピボットテーブル＆パワークエリ」の実践スキルをもっと広めたい。

２．だれでも使える予算管理表のメリット

①いつでも「１表」で管理！

予算と実績との差異⇒３つの分類で「視える化」

月別管理を、確定、当月＝分析中、未発生[予測]に区分し、
年間を１表で管理できるようにします。
※未発生の月は、予算差＝０、実績見込み＝予算になります。

分析には、マクロ⇔ミクロ、推移という視点が必要ですが、
ピボットテーブルの「展開・折りたたみ」は、最適な機能です。

予算の精度の課題が必ず残ります。
「月ずれ」や増減だけでなく、予算に織り込んでいない事象。
月が進むと、こういう要因が増えてきます・・。ところが、
１表で管理すると、全体を見通せるので、すっきりできるのです。

◎毎月の分析コメントを「視える化」！
数値は１表にできても、コメントの作成は別々では、大変・・！
１表の下に、分析のコメントを入力。印刷は、当月のコメントにします。

前月のコメントは、次々と下にずれても、１年間の進捗が見られます。
月別にシートを区別しているやり方では実現できません！

②「当月の進捗管理」もしたい！

私が在籍した会社は、前月の実績、当月の「予測」を報告しました。
　Ｃ社・・毎月の実績報告に加えて、当月の予測も報告
　Ｆ社・・毎週月曜日に、前週末での当月進捗報告

予算を日割りにして、実績の進行日数で、当月予算を計算
実績－当月予算＝予算差　を出します。
1640－960[1200*80％]＝680

月の残る日は予算で　　当月予算＋予算差＝当月見込み　となります。
　　　　　　　　　　　　1200　+　680　= 1880

③パワークエリ＆ピボットテーブル　最強の組み合わせ！

予算管理表エクセルは、1⃣実績、2⃣予算から、3⃣予実比較へ　の３つで、
パワークエリ＆ピボットテーブルのみで作っています。

この最強の組み合わせを、「実践テンプレート」と呼んでいます。

実践テンプレートのメリットは、次の３つです。
◎マウス操作「ノンコード」でプログラミング不要でシステム化できる　
◎最新データを用意し「すべて更新」クリック　所定の作業を繰り返し可能
◎マウス操作でデータ集計・分析が様々なレポート・グラフに自在に行える

④習得・引継ぎが容易、カスタマイズで実務に活用！

この実践テンプレートは、Ｅｘｃｅｌの高度なSUMIFS、VLOOKUP関数、マクロ・プログラミングなどを使用していません。
パワークエリ、ピボットテーブルは、マウス操作だけで設定し、ステップや、フィールドリストなどの仕様が確認できます。
Ｅｘｃｅｌと比べて、習得・引継ぎが容易です。

このサンプルで、実践スキルの習得ができます。
簡単なモデルでの汎用テンプレートですが、自分のデータを用意して、
マウスでカスタマイズすれば、実務に利用できるテンプレートになります。会社の仲間と共有すれば「システム」になります。

ＤＸなどで、データの利用が進んでいますが、ユーザーが使いやすい
「デー分析」にするのは容易ではありません。
エクセルでも、ＡＣＣＥＳＳやＰｏｗｅｒＢＩのような使い方が出来るので、是非、参考にしてください。

３．パワークエリでデータ編集(活用)

パワークエリは、マウス操作で、メニュー操作から、
元データの作り込み、データの変換・加工のステップを登録し、
「更新」クリックで最新のデータ作成を行うツールです。

　1⃣　実績　　　当月⇒累計、予算のレベルに合わせる
　2⃣　予算作成　入力⇒組換
　3⃣　予実比較　[追加]　
　　　年月判定・・当月、確定、未　を判定
　　　ピボットで組み換え

1⃣　実績

①当月分　データ読込・・商品の単価で金額計算
②最新累計　当月を除外　・・　マージ・左反
　　　　　　当月分追加

③実績集計　グループ化
さらに、明細管理　　②最新累計＋追加　予算

2⃣　予算

①予算入力
　商品もしくは実績集計から予算用データの準備　売店・商品別の単価
　これに、月別の数量を入力
②予算組換え
　　組換　横型⇒縦型‥　ピボット解除された他の列
　　乗算　単価×数量＝売上・粗利金額

3⃣　予実比較

①予算実績　予算と実績　追加
　　　ソース　追加　予算、実績
　　　マージ　重点管理　売店・商品

②組換え差異
　　ピボット解除　・・　売上金額、粗利金額、数量を１列に
　　結合・・予算_売上、実績_売上・・
　　ピボット　・・結合　予算_売上・・実績_売上に組換え
　　確定・未を選択　　→「未」差異＝0　⇒「実見」　　

③ピボットテーブルでレポート作成
最新データを準備すれば、「すべて更新」をクリックするだけで、
データを変更して、何度も繰り返しが可能です

４．ピボットテーブルでデータ分析

パワークエリで作成したデータを読み込んで、データ分析します。
先ず、１つ作成して、オプション設定などの作り込みします。
それをコピーして、使いやすいレポートを増やしていきます。

通年・予算管理表
　１表ですが、展開・折りたたみで、マクロ⇔ミクロ、推移で分析が可能

ダッシュボードで視覚化しよう！
　　スライサーで、レポート、グラフを制御します。「魅せる化」です！
　　推移表＋グラフ
　　集計表、クロス集計表

セキュリティ対策
　オプション／データ　
　　　ファイルにデータを保存の☑を外しました。
　　　ファイルを開くときにデータを更新に☑しています。
　⇒他のエクセルにピボットテーブルだけをコピーしても
　　明細データの出力がされません！

ピボットテーブルの参照元　
　クエリ_組み換え差異　・・　クエリ／テーブルから通常の作成
　クエリ - pクエリ_組み換え差異・・クエリから直接作成

５. Ｅｘｃｅｌと比較をしてみよう！

Ｅｘｃｅｌ　　　　⇒表を作る　　セルに入力　＝「知識」　　　　覚える
実践テンプレート　⇒データ集計・分析　マウスでメニュー選択　　慣れる　
　　　　　　　　　※ＡＣＣＥＳＳ、ＰｏｗｅｒＢＩのような使い方

①Ｅｘｃｅｌの修得・・学んで覚える！

Ｅｘｃｅｌの作表では、この４つスキルが使われていますが、
修得の負担が大きく、実践で応用するのが大変です。
　①ＳＵＭＩＦＳ関数
　②ＶＬＯＯＫＵＰ関数
　③ＩＦ関数
　④ＶＢＡマクロ
ピボットテーブルは、ＭＯＳ検定では、ＥＸＰＥＲＴ・上級ですが、
基本の使い方、知識に止まっているので、使いこなす人が多くありません。

②実践テンプレートの習得・・使って、慣れて、身につける！

パワークエリを使うと、累計データの更新、比較データの作成という高度な処理が可能です。ピボットテーブルも、使いこなせれば、様々なデータ分析がマウスで行えます。関数、マクロを使わずに、レポート・システムに仕上げることが可能です。

ピボットテーブル、パワークエリのYouTube動画の視聴をお薦めします。
どちらも、「マウス操作」が主体で、メニュー操作に慣れることです！

そして、正しい使い方を知らずに使って、「計算が合わない！」「遅い！」という「呪い」に罹ってしまう人が多いようです。
ｎｏｔｅに解説記事を紹介していますので、お役立てください。

Ｅｘｃｅｌとの大きな違いは、使い方が共通で、習得しやすいことです。
マウスを使って、全て仕様が判るので、引継ぎもしやすいのです。

６．カスタマイズで実践しよう！

実践テンプレートを２５年探求して即席・システムに！

２５年以上もピボットテーブルでのシステム化を探求してきました。
３つのポイントがあります。
　①元データの作り込み
　②ピボットテーブルの使いこなし
　③さらなる改善・フィードバック

①元データの作り込み・・データの整理・統合
私が勤めた会社では、ＤＷＨ＝データベテースが利用できたので、
ピボットテーブルを直ぐに実務に活用できました。
しかし、データ分析を進めるためには、そのままのデータでは不十分。
データの作り込みのために、関数、ＶＢＡマクロを自習を続けました。　　　　　Excel2016からパワークエリが使えるようになり、驚くほど楽になりました。

②ピボットテーブルの使いこなし　データキャッシュの探究
ピボットテーブルは、簡単に集計できますが、４つの箱に入れるという制約があります。いかにして、満足の出来るレポートにするかを追求しました。
また、Ｅｘｃｅｌとは違う挙動に苦労しました。
３０年も使い続けて、メニュー、特にオプション設定などを全部クリックして調べ尽くし、メモリに隠れている「データキャッシュ」特性を究明して、
「使いこなし方」＝実践ノウハウを培ってきました。

③さらなる改善・フィードバック
Ｅｘｃｅｌは沢山の知識を覚えて使います。
知識のままでは実践できないので、応用力を身につける必要があります。

ピボットテーブルは、元データがあれば簡単にレポートができます。
分析を向上させて、役立つ情報を得るには、
　情報力　＝　データ活用力　✖　データ分析力　✖　実践力
元データとピボットテーブルの両方を向上させる応用力＝実践力が必要です。個々の知識を「足し算」ではありません。「掛け算」なのです。　　　

Ｅｘｃｅｌを３０年も使っていますが、ピボットテーブルは手探りで使い始めたので、２５年前のエクセルは「システム」もどきの粗末なものでした。

でも、１つモデルを作れば、それを改善しながら、作り込んできました。
カスタマイズ「実践」の積み重ねです。

業務共通のエクセルをひな型として、みんなで使えるシステムにレベルアップしてきました。他の業務でも使えるように汎用化・共通化も行いました。
それが、損益管理、販売管理、製造原価管理などのシステムになりました。
会社生活を卒業し４年経過しても、後輩たちに使い続けてもらっています。

３０年かけて「実践テンプレート」という理想形に到達できたのです。

みなさんも、カスタマイズして実践してみよう！

システム開発では、仕様書を作ってプログラミングが常套手段です。
ユーザーニーズを十分に把握することが、必須の条件です。
「ユーザー」自身が直接関わると、開発の負担が軽減されます。

実践テンプレートは、エクセルでユーザー自ら、実践スキルを
身につけて使うものなので、この条件に最適なツールと言えます。
専門知識や資格を取得された方なら、容易にカスタマイズできます。

みなさんも一緒にカスタマイズに挑戦してみませんか？
今使っているエクセルなどから、入力データ、出力データを用意する。
「使って、慣れて、身につける」これを実践しましょう！

エクセルなら、準備作業、移行作業などの負担が小さく、導入も早く、
導入後の改善・変更も容易です。
最初から完璧なものでなくても、出来たものを使って成果を出す。
仕事の負担が軽減されたり、役立つ情報が得られます！
使いながら、改善していくバイパス方式です。

このテンプレートは、会社が違っても、似たような業務なら、カスタマイズして、自分用のテンプレートになります。
仲間の方にも使ってもらうことも可能です。
エクセルでもできるんです！

是非、皆さんに使っていただき、もっと良いものに発展させたいですね。

お願い　アンケートにご協力ください（２分）

この記事のご感想を反映していきます！

ストアカ・オンライン講座で解説・実習！【リクエスト受付中】

この記事にご興味のある方は、是非お問い合わせください！

ピボットおじさんの無料相談窓口　３０分ＺＯＯＭ解説　受け付け中！
毎月５名様　先着順です！

ｎｏｔｅの記事について直接聞きたい・・
ピボットテーブルを使っていても「使いこなせない！」
皆さんのお悩みを30分無料　ＺＯＯＭで解決します。

是非、コメントや、応援をおねがいします。

他にも実践事例を紹介しています

ピボットテーブル＝即席ラーメン理論の「即席・実践システム」

即席・実践システムの開発について解説・紹介しています。

ストアカ・オンライン講座で解説・実習！【リクエスト受付中】

請求書ソフトのＣＳＶデータを集計する「実践テンプレート」を公開しました。本記事の予算管理と組み合わせれば、ＤＸにつながります。