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