ピボットテーブル活用術(４)だれでも使える予算管理表！ 関数・マクロでは実現できなかった夢を実現！
ＡＩ時代にも生かせる経理４７年の集大成！
ピボットテーブルはマウスを使って、慣れて、身につけるツールです！
サンプルエクセルはこちらからダウンロード
毎日使える予算管理表
ストアカ・オンライン講座で解説・実習！【リクエスト受付中】
請求書ソフトのＣＳＶデータを集計する「実践テンプレート」を公開しました。本記事の予算管理と組み合わせれば、ＤＸにつながります。
この記事を読んで、良く判らない！もっと知りたい方には、
毎月５名様 先着で ＺＯＯＭ無料相談３０分 受付中です！
１．理想の予算管理表を目指して４７年！
Ｅｘｃｅｌで予算管理レポートを作っている人が多いと思いますが、
みなさん、満足できていますか？
経理や、システム、エクセルの専門家がいるのに、
だれでも利用できる「予算管理術」は紹介されていません。
私は、４０年以上経理関係の実務に携わりました。
専門家の人と比べて知識は乏しいですが、経営管理(経理)、システム、
エクセルの３つの実践を重ねてきました。
この実践から生みだしたのが、「私の」理想の予算管理表です。
個々の知識だけで創り出したのではありません。
だれでも使える予算管理表 ４つのメリット
①年間の実績見込が１表で見通せる
②毎月の予算を日数割で計算し、月の途中での予算差が管理できる
③マウス操作で「システム化」、最新データを用意し「更新」でデータ分析
④ユーザーがカスタマイズして「自分のシステム」に、習得・引継ぎが容易
こんな人に使って欲しい！
◎このエクセルをカスタマイズして、皆さんの実践に活用してください。
◎「ピボットテーブル＆パワークエリ」の実践スキルをもっと広めたい。
２．だれでも使える予算管理表のメリット
①いつでも「１表」で管理！
予算と実績との差異⇒３つの分類で「視える化」
月別管理を、確定、当月＝分析中、未発生[予測]に区分し、
年間を１表で管理できるようにします。
※未発生の月は、予算差＝０、実績見込み＝予算になります。
分析には、マクロ⇔ミクロ、推移という視点が必要ですが、
ピボットテーブルの「展開・折りたたみ」は、最適な機能です。
予算の精度の課題が必ず残ります。
「月ずれ」や増減だけでなく、予算に織り込んでいない事象。
月が進むと、こういう要因が増えてきます・・。ところが、
１表で管理すると、全体を見通せるので、すっきりできるのです。
◎毎月の分析コメントを「視える化」！
数値は１表にできても、コメントの作成は別々では、大変・・！
１表の下に、分析のコメントを入力。印刷は、当月のコメントにします。
前月のコメントは、次々と下にずれても、１年間の進捗が見られます。
月別にシートを区別しているやり方では実現できません！
②「当月の進捗管理」もしたい！
私が在籍した会社は、前月の実績、当月の「予測」を報告しました。
Ｃ社・・毎月の実績報告に加えて、当月の予測も報告
Ｆ社・・毎週月曜日に、前週末での当月進捗報告
予算を日割りにして、実績の進行日数で、当月予算を計算
実績－当月予算＝予算差 を出します。
1640－960[1200*80％]＝680
月の残る日は予算で 当月予算＋予算差＝当月見込み となります。
1200 + 680 = 1880
③パワークエリ＆ピボットテーブル 最強の組み合わせ！
予算管理表エクセルは、1⃣実績、2⃣予算から、3⃣予実比較へ の３つで、
パワークエリ＆ピボットテーブルのみで作っています。
この最強の組み合わせを、「実践テンプレート」と呼んでいます。
実践テンプレートのメリットは、次の３つです。
◎マウス操作「ノンコード」でプログラミング不要でシステム化できる
◎最新データを用意し「すべて更新」クリック 所定の作業を繰り返し可能
◎マウス操作でデータ集計・分析が様々なレポート・グラフに自在に行える
④習得・引継ぎが容易、カスタマイズで実務に活用！
この実践テンプレートは、Ｅｘｃｅｌの高度なSUMIFS、VLOOKUP関数、マクロ・プログラミングなどを使用していません。
パワークエリ、ピボットテーブルは、マウス操作だけで設定し、ステップや、フィールドリストなどの仕様が確認できます。
Ｅｘｃｅｌと比べて、習得・引継ぎが容易です。
このサンプルで、実践スキルの習得ができます。
簡単なモデルでの汎用テンプレートですが、自分のデータを用意して、
マウスでカスタマイズすれば、実務に利用できるテンプレートになります。会社の仲間と共有すれば「システム」になります。
ＤＸなどで、データの利用が進んでいますが、ユーザーが使いやすい
「デー分析」にするのは容易ではありません。
エクセルでも、ＡＣＣＥＳＳやＰｏｗｅｒＢＩのような使い方が出来るので、是非、参考にしてください。
３．パワークエリでデータ編集(活用)
パワークエリは、マウス操作で、メニュー操作から、
元データの作り込み、データの変換・加工のステップを登録し、
「更新」クリックで最新のデータ作成を行うツールです。
1⃣ 実績 当月⇒累計、予算のレベルに合わせる
2⃣ 予算作成 入力⇒組換
3⃣ 予実比較 [追加]
年月判定・・当月、確定、未 を判定
ピボットで組み換え
1⃣ 実績
①当月分 データ読込・・商品の単価で金額計算
②最新累計 当月を除外 ・・ マージ・左反
当月分追加
③実績集計 グループ化
さらに、明細管理 ②最新累計＋追加 予算
2⃣ 予算
①予算入力
商品もしくは実績集計から予算用データの準備 売店・商品別の単価
これに、月別の数量を入力
②予算組換え
組換 横型⇒縦型‥ ピボット解除された他の列
乗算 単価×数量＝売上・粗利金額
3⃣ 予実比較
①予算実績 予算と実績 追加
ソース 追加 予算、実績
マージ 重点管理 売店・商品
②組換え差異
ピボット解除 ・・ 売上金額、粗利金額、数量を１列に
結合・・予算_売上、実績_売上・・
ピボット ・・結合 予算_売上・・実績_売上に組換え
確定・未を選択 →「未」差異＝0 ⇒「実見」
③ピボットテーブルでレポート作成
最新データを準備すれば、「すべて更新」をクリックするだけで、
データを変更して、何度も繰り返しが可能です
４．ピボットテーブルでデータ分析
パワークエリで作成したデータを読み込んで、データ分析します。
先ず、１つ作成して、オプション設定などの作り込みします。
それをコピーして、使いやすいレポートを増やしていきます。
通年・予算管理表
１表ですが、展開・折りたたみで、マクロ⇔ミクロ、推移で分析が可能
ダッシュボードで視覚化しよう！
スライサーで、レポート、グラフを制御します。「魅せる化」です！
推移表＋グラフ
集計表、クロス集計表
セキュリティ対策
オプション／データ
ファイルにデータを保存の☑を外しました。
ファイルを開くときにデータを更新に☑しています。
⇒他のエクセルにピボットテーブルだけをコピーしても
明細データの出力がされません！
ピボットテーブルの参照元
クエリ_組み換え差異 ・・ クエリ／テーブルから通常の作成
クエリ - pクエリ_組み換え差異・・クエリから直接作成
５. Ｅｘｃｅｌと比較をしてみよう！
Ｅｘｃｅｌ ⇒表を作る セルに入力 ＝「知識」 覚える
実践テンプレート ⇒データ集計・分析 マウスでメニュー選択 慣れる
※ＡＣＣＥＳＳ、ＰｏｗｅｒＢＩのような使い方
①Ｅｘｃｅｌの修得・・学んで覚える！
Ｅｘｃｅｌの作表では、この４つスキルが使われていますが、
修得の負担が大きく、実践で応用するのが大変です。
①ＳＵＭＩＦＳ関数
②ＶＬＯＯＫＵＰ関数
③ＩＦ関数
④ＶＢＡマクロ
ピボットテーブルは、ＭＯＳ検定では、ＥＸＰＥＲＴ・上級ですが、
基本の使い方、知識に止まっているので、使いこなす人が多くありません。
②実践テンプレートの習得・・使って、慣れて、身につける！
パワークエリを使うと、累計データの更新、比較データの作成という高度な処理が可能です。ピボットテーブルも、使いこなせれば、様々なデータ分析がマウスで行えます。関数、マクロを使わずに、レポート・システムに仕上げることが可能です。
ピボットテーブル、パワークエリのYouTube動画の視聴をお薦めします。
どちらも、「マウス操作」が主体で、メニュー操作に慣れることです！
そして、正しい使い方を知らずに使って、「計算が合わない！」「遅い！」という「呪い」に罹ってしまう人が多いようです。
ｎｏｔｅに解説記事を紹介していますので、お役立てください。
Ｅｘｃｅｌとの大きな違いは、使い方が共通で、習得しやすいことです。
マウスを使って、全て仕様が判るので、引継ぎもしやすいのです。
６．カスタマイズで実践しよう！
実践テンプレートを２５年探求して即席・システムに！
２５年以上もピボットテーブルでのシステム化を探求してきました。
３つのポイントがあります。
①元データの作り込み
②ピボットテーブルの使いこなし
③さらなる改善・フィードバック
①元データの作り込み・・データの整理・統合
私が勤めた会社では、ＤＷＨ＝データベテースが利用できたので、
ピボットテーブルを直ぐに実務に活用できました。
しかし、データ分析を進めるためには、そのままのデータでは不十分。
データの作り込みのために、関数、ＶＢＡマクロを自習を続けました。 Excel2016からパワークエリが使えるようになり、驚くほど楽になりました。
②ピボットテーブルの使いこなし データキャッシュの探究
ピボットテーブルは、簡単に集計できますが、４つの箱に入れるという制約があります。いかにして、満足の出来るレポートにするかを追求しました。
また、Ｅｘｃｅｌとは違う挙動に苦労しました。
３０年も使い続けて、メニュー、特にオプション設定などを全部クリックして調べ尽くし、メモリに隠れている「データキャッシュ」特性を究明して、
「使いこなし方」＝実践ノウハウを培ってきました。
③さらなる改善・フィードバック
Ｅｘｃｅｌは沢山の知識を覚えて使います。
知識のままでは実践できないので、応用力を身につける必要があります。
ピボットテーブルは、元データがあれば簡単にレポートができます。
分析を向上させて、役立つ情報を得るには、
情報力 ＝ データ活用力 ✖ データ分析力 ✖ 実践力
元データとピボットテーブルの両方を向上させる応用力＝実践力が必要です。個々の知識を「足し算」ではありません。「掛け算」なのです。
Ｅｘｃｅｌを３０年も使っていますが、ピボットテーブルは手探りで使い始めたので、２５年前のエクセルは「システム」もどきの粗末なものでした。
でも、１つモデルを作れば、それを改善しながら、作り込んできました。
カスタマイズ「実践」の積み重ねです。
業務共通のエクセルをひな型として、みんなで使えるシステムにレベルアップしてきました。他の業務でも使えるように汎用化・共通化も行いました。
それが、損益管理、販売管理、製造原価管理などのシステムになりました。
会社生活を卒業し４年経過しても、後輩たちに使い続けてもらっています。
３０年かけて「実践テンプレート」という理想形に到達できたのです。
みなさんも、カスタマイズして実践してみよう！
システム開発では、仕様書を作ってプログラミングが常套手段です。
ユーザーニーズを十分に把握することが、必須の条件です。
「ユーザー」自身が直接関わると、開発の負担が軽減されます。
実践テンプレートは、エクセルでユーザー自ら、実践スキルを
身につけて使うものなので、この条件に最適なツールと言えます。
専門知識や資格を取得された方なら、容易にカスタマイズできます。
みなさんも一緒にカスタマイズに挑戦してみませんか？
今使っているエクセルなどから、入力データ、出力データを用意する。
「使って、慣れて、身につける」これを実践しましょう！
エクセルなら、準備作業、移行作業などの負担が小さく、導入も早く、
導入後の改善・変更も容易です。
最初から完璧なものでなくても、出来たものを使って成果を出す。
仕事の負担が軽減されたり、役立つ情報が得られます！
使いながら、改善していくバイパス方式です。
このテンプレートは、会社が違っても、似たような業務なら、カスタマイズして、自分用のテンプレートになります。
仲間の方にも使ってもらうことも可能です。
エクセルでもできるんです！
是非、皆さんに使っていただき、もっと良いものに発展させたいですね。
ストアカ・オンライン講座で解説・実習！【リクエスト受付中】
この記事にご興味のある方は、是非お問い合わせください！
ピボットおじさんの無料相談窓口 ３０分ＺＯＯＭ解説 受け付け中！
毎月５名様 先着順です！
ｎｏｔｅの記事について直接聞きたい・・
ピボットテーブルを使っていても「使いこなせない！」
皆さんのお悩みを30分無料 ＺＯＯＭで解決します。
是非、コメントや、応援をおねがいします。
他にも実践事例を紹介しています
ピボットテーブル＝即席ラーメン理論の「即席・実践システム」
即席・実践システムの開発について解説・紹介しています。
この記事が気に入ったらサポートをしてみませんか？