ピボットテーブル活用術(３)所要量計算 マクロ使わないシステムを紹介します！
エクセル・実践テンプレートは「汎用」です。
このサンプル・ファイルは、「花束納入」のお花の所要量計算です。
マクロ・プログラミングを使わずに開発しています。
あなたの会社でもデータを用意して、マウスでカスタマイズすれば、
どの会社でも「所要量計算」が可能になる汎用テンプレートです。
ストアカ・オンライン講座で解説・実習！【リクエスト受付中】
実践事例(3) Ｓ社様 お花仕入計画
お花のセットを顧客スーパー様に納品している会社さんです。
【こんなお悩みが】
お盆、お彼岸、お正月 注文が集ります。
年４回ですが、お花の仕入れ・・計算が大変。
欠品しないようにすると過剰な仕入れでロスが大きく発生する・・
「これ何とかなりませんか？」お悩みのご相談がありました。
手順の紹介です
1⃣ 事前に準備するデータ
このデータを準備できたら「計算」です。
①商品レシピ 花束のお花の構成（レシピ）
②納入計画 商品別、日付、数量を入力
③お花の仕入単価
2⃣ 操作・・すべて更新 クリックで 計算が完了
パワークエリ、ピボットテーブルで「システム化」しています。
マクロ・プログラミンではなく、マウスで設定しています。
操作は、「すべて更新」をクリックするだけです。
データを変更したり、何度も繰り返しで計算が可能です。
１．事前に準備するデータ
①商品別 花束のレシピ を登録
商品別・・構成品目[レシピ]を入力
お花の種類が増えたときは、列を追加してください。
⇒③レシピ品目単価に連携します。
②納入計画 入力
商品別、日付、数量を入力
お得意先からの注文データを利用して、作成もできます。
日付 開始日 入力・・追加、削除できます
納入先、ＩＤで商品レシピを参照しています
③レシピ品目単価
ＩＤ(番号)・草花のは、商品レシピを組み替えして取り込みます。
お花別の仕入単価を登録します。
２．エクセル 計算操作
計算の操作は、データ／「すべて更新」をクリックです。
すべて更新は、エクセル・ブックのパワークエリとピボットテーブルの
更新を、連続して実行します。
データの連携が途切れている場合には、再度、すべて更新を実行します。
３つのレポートが更新されます。
①日別の所要量計算
②総本数
③粗利計算
３つとも、クエリから直接ピボットテーブルを作成しています。
ピボットテーブルは、マウス設定でレポートを作成し、様々なレイアウトで活用ができる「データ分析ツール」です。
お花の所要量を計算していると、仕入の際に、お花の相場が上がった時に、お花のレシピを組み換えて、仕入金額増加の抑制が可能になります。
３．パワークエリとピボットテーブル
パワークエリで、データの変換・加工を行います。
このブックには、１１個のクエリを作成しています。
エクセル・ブックに、テーブル(緑枠)、あるいは、ピボットテーブル(青枠)で
出力できます。途中過程のクエリは、「読込しない」設定も可能です。
通常のピボットテーブルは、元データをテーブルに出力して、作成します。
クエリを元データにすると、データの重複が防止でき、ファイル容量が小さくなります。
４．使って、慣れて、身につける！
実践テンプレートは、「使って、慣れて、身につける」ものです。
この「サンプル」で、知識を実践に、使いこなし方を習得してください。
Ｓ社様での成果
①納品計画作成・・元データの収集 ３時間／１回 で完了
それまでは、忙しい中での作業でも、正確な情報は得られません。
顧客データの有効利用もあり、年４回の作業が効率的に行えるようにな
りました。社長さん自身の作業時間が減少です。
作成時間の削減 ５時間×４回×５０００円/時間＝１０万円 ／年
②仕入ロスの減少・・１０％ → ５％にできれば
サンプルのデータはお盆を想定した架空の数値です。
納入金額 (売上)・・１８００万円
所要仕入金額 １２００万円
これにロス10％ １２０万円 ⇒ 5％ ６０万円
粗利金額 ４８０万円 ⇒ ５４０万円
増益 12.5％ ＋６０万円
③皆さんも、このテンプレートで改善を実現できます！
◎過剰仕入によるロスの削減は、システム化で確実に効果が出ます。
自社に合うシステムの導入には、コストと時間がかかります。
実践テンプレートなら、カスタマイズとサポートで導入ができます。
是非、比較してみてください。
５．実践テンプレートの開発・カスタマイズ
①実践テンプレートの作成・開発 実例 Ｓ社様
１年前の７月、Ｓ社の社長さんと一緒に開発を始めました。
毎週１時間の打ち合わせで、実際のデータを用いて作り込みました。
最初の運用版が完成したのは、約１ヶ月、延べ２０時間くらいです。
仕入計画作成も同時に行っており、実開発工数は１５時間ほどです。
マクロを使う開発と比較してみてると、半分の工数だと思います。
テンプレートでのシステム化は、テスト的なパーツを組み合わせる、
プロトタイプで行っています。詳細なシステム設計は不要です。
ユーザーの要望を確認しながら、詳細の仕様を詰める。
見直しも容易です。
「使って、慣れて、身につける」エクセルです。
パワークエリと、ピボットテーブル活用の威力です！
お盆のエクセルが完成し、その後も、秋お彼岸、正月、春お彼岸と、
毎回、改善・改良を重ねてきました。
②カスタマイズすれば皆さんでも使えます！
このサンプルは、パワークエリとピボットテーブルで作っています。
データを拡張するカスタマイズをすれば、皆さんの会社でも導入できます。
実は、一番大変な作業はワークエリ、実際に使用するデータ準備です。
〇今あるエクセルから、パワークエリで加工して使う。
〇データの整理・統合、ピボットテーブル、パワークエリが有効です。
この準備作業の中から、さらに使いやすくする「カスタマイズ」を
しています。
システム開発は、仕様書を作ってプログラミングする。のが常道ですが、
ユーザーニーズを十分に把握することが、良いシステムの条件です。
皆さんの作業で面倒で大変＝改善効果の大きいところを部分開通させる。
それを結合させていけば、全体が完成できます。
６.所要量計算エクセルを活用してください！
所要量計算で仕入れ量の計算が正確にできないと、生産・納品に支障が出たり、在庫の増加、廃棄ロスの発生につながります。
しかし、所要量計算は大変複雑な計算作業なので、Ｅｘｃｅｌでのシステム化は専門家でも容易でありません。
ＤＸなどのシステム化・・生産管理システムなどの専用システムの導入は、コストも期間もかかり、ハードルが高いものです。
「所要量計算」～「採算計算」システム実現の思い
私は、４５年以上も、原価計算＝経理と、システム開発などの実務に直接携わってきました。
製紙メーカーの原価計算・原価管理は非常に複雑です。経理だけでなく、システムを独習して、システム再構築の専従リーダーも経験してきました。
さらに、ピボットテーブルを活用したエクセル・システムも作成して、会社の多くの仲間に利用してもらい、実践経験を重ねてきました。
今回、エクセルで「所要量計算」という複雑で夢のシステムを実現できたのは、パワークエリの実践活用ができたからです。
このスキルの習得には、３年以上もかかりました。
①ピボット解除・・横型データを縦型データに組み換え
商品レシピ[お花]、納品計画[日付]
②マージ(結合) ・・お花↔仕入単価、商品↔納入単価 1：1
商品別・日別・数量↔レシピ(お花・本数)を付与1：N
ＶＬＯＯＫＵＰ関数やマクロを使わずに、マウスの設定だけでデータソースを整備することができました。
パワークエリは、基本知識を身につけた人なら、カスタマイズなどが容易にできるようになります。
エクセルの学び直しは「実践ピボットテーブル」を中心に！
ピボットテーブル・パワークエリの２つの武器を操る＝実践テンプレートの応用力は、これまで不可能だと思われていたエクセルでのシステム化を、身近なものにしてくれます。
ＭＯＳ検定を取得されたり、Ｅｘｃｅｌを学んできた方には、この実践事例を参考にして、是非とも、知識を「実践力」に高めてください。
応用例：お菓子製造支援 エクセル
今、お菓子製造の製造支援・エクセルの稼働準備中です。
クッキーの注文が増えてきて、製造計画の立案、材料の手配などが追い付かない状況ということで、お手伝いを始めました。
個々のパーツでは、パワークエリとピボットテーブル＝ノン・プログラミングで作成しています。
このエクセルでは、パワークエリの更新のために、マクロを使っています。
また、入力チェックなどのために、Ｖｌｏｏｋｕｐ関数や、条件付き書式も使っています。
ユーザーが使いやすく役立つシステムを、早く、お手軽価格で提供したいと思っています。
この記事にご興味のある方は、是非お問い合わせください！
メンバーシップ ピボットテーブルもっと楽しむメイト を作りました。
お試しプラン ピボット姫の挑戦
定例会や、メンバーとの交流も行います。
ご希望者には、初回３０分無料でのZOOM解説も行う予定です。
カスタマイズ指導などのご要望も承ります。
この取り組みを支援してくれる仲間も募集しています。
是非、コメントや、応援をおねがいします。