普通、受注生産品の注文を受けたら、その製品の部品表からどんな材料を使うか情報を集め、発注することになります。
情報を集めるのがかなり大変で、あらゆる取引先ごとに必要な部品を取りまとめなければなりません。
この集計は１＋１のように決まり切っていることなので、注文を受けたらそのままいつのまにか発注してくれるという仕組みが作れるはずです。
一見夢のような話にも見えますが、ひとつひとつ細かく手順を踏んで考えてみます。
仕組み
仕組みはこうです。
- 発注を受けたらその製品と個数を記録します。
- 次にすべての製品の部品表からその製品の部品のデータだけを抜き出し、そこにそれぞれの部品の注文個数をかけ合わせます。
- その部品の発注先ごとに必要数を集計します。
- 発注先に注文します。
このようなステップになります。
こうなってくるとマクロなどでなんとかなりそうな気がします。
一つ考えたいのは、例えば小さなネジ1本が1本で仕入れられるかというとそれはなかなか難しく、例えば100本単位でしか注文できない場合があります。
そのネジを3本使う製品を10個受注しても、30個しか使わず、70個が余ります。それではもったいないので、例えば１注文ごとではなく、1週間の受注ごとに発注作業をまとめれば、もしかしたら似た製品でこのネジを使う場合もあるかもしれません。そうすればあまり個数を減らすことも可能かもしれませんね。
単純な注文のための集計ではなく、このような仕組みを実務的に考えていかないといつまでも使わない部品の余剰在庫に繋がります。
ということで、仕入れの集計は1週間に１回として考えてみましょう。
テーブル
テーブルとは、仕組みで使う一覧表のことです。
このテーブルを考えるのがこういうシステムを作る上での一番重要なポイントで、ここは妥協してはいけません。何度も見直して、重複して記録しているものがないかとか、こことこことは連携していなければならないけど実際には連携していないところとかは完全に潰しておく必要があります。
作りながらチェックすることもできますが、多くの場合、はじめからやり直しになりますので、はじめに考え抜くということが重要です。
では、実際に考えてみましょう。
今回、まず絶対に必要なものは、受注一覧表です。
いつ、どこから、何の製品を、何個受注したかを記録するものです。これで部品の注文個数が決まりますし、この表から後々、納品書と請求書を作成することになります。そこまで考えると受注金額もあるのでしょうけど、今回はわかりにくくなるので、部品に関わるものだけとし受注金額は記録しません。
次に必要なのは部品表です。
これは設計者が作成することが多いでしょう。設計者が部品を一番把握できるからです。
工場では、部品の注文先に見積をとることも意外と設計者が担当することが多いです。
部品表には、どの製品で使う、その部品を、何個使うかを記録します。
これも予算管理のためには部品の単価も記録していることもありますが、今回は後の部品ごとの注文先一覧表で管理します。ちなみに部品の単価って結構変動するのでVLOOKUP関数的にリレーションさせておかないと製品の見積金額が合わなくなる時もあります。なので一定期間は変動しないように契約書を交わしていることもあります。
部品表の次は、部品ごとの注文先一覧表です。
これは設計から部品表をもらった資材担当が管理することもあります。
なので、設計と資材は連携していなければなりません。
部品ごとに、仕入れ先を記録し、入荷単位、単位当たりの仕入れ金額を記録します。
最後は、受注先の一覧表で、受注先名と受注するための連絡先を記録します。
ここまでの4つが、今回で使うシステムのテーブルです。
Excelの表
Excelの表は次のようになります。ダミーデータをあらかじめ用意します。
ダミーデータは過不足にならないよう、また、重複しないようにチェックしながら重複の削除機能などを利用し作成します。
受注一覧表
部品表
この個数に受注個数をかければ必要数が出てきます。SUMIF関数を使えばよいでしょう。
部品ごとの注文先一覧表
この表に総必要数をSUMIF関数で入れて、注文単位で割って切り上げれば注文数がわかり、その数に金額をかければ購入金額が求まります。
受注先の一覧表
それぞれの受注先に決まった注文個数の一覧を送ればよいです。
作成プロセス
ここまでは手作業っぽく考えてきましたが、では実際にExcelで作成するにはどうすればいいでしょうか。
スタートは受注一覧表です。本来はこの表の何月何日から何月何日までの絞り込みが必要なのですが、今回はこの表全体を対象にしてみます。
この数量を製品ごとにSUMIFし、部品表に当てはめ、計算します。
この計算式は下にコピーしますが、今後、部品が増えることを考慮すると多めにコピーしても構わないと思います。
次にこの個数を部品ごとの注文先一覧表に入力し、注文単位で割って切り上げます。
次に実際の注文作業になりますが、その前に注文票を作ります。
この注文票の形はそのままPDFでメールで送られるようにします。
御中の右側にインデックス番号をとりあえず１と入力し、御中の左側にINDEX関数で仕入れ先の一覧から値を取ってくる関数を入力しておきます。
また、この範囲を印刷範囲とします。
次のような範囲を設定します。
抽出範囲の仕入れ先は、注文票の御中の前の取引先名を参照します。
これで、データタブのフィルターの中の詳細設定を開きます。
リスト範囲は、部品ごとの注文先一覧表。
検索条件範囲は、先ほどの検索条件の２行6列。
指定した範囲をクリックし、
抽出範囲は、先ほどの出力範囲の1行6列。
でOKすると、指定した取引先のデータが抽出されます。
あとはこの結果を注文票に反映します。
=IF(抽出結果のセル=””,””,抽出結果のセル)
の計算式でそれぞれのセルに反映します。
反映した式は下にコピーし、最後に合計金額をオートSUMで求めます。
ここまでで、インデックスの番号を変更して詳細設定のフィルターを設定すれば、自動的に注文票が作成できるようになりました。
その流れをマクロに記録すればワンボタンで注文票が作成され、その流れの中にメール送信も入れれば、メールの送信までワンボタンで可能です。
ではマクロの記録をしてみましょう。
表示タブのマクロ、マクロの記録で記録を開始します。マクロ名は「注文」としましょうか。
操作内容は、一旦関係ないセルをクリックしてから、先ほどのインデックスの番号にとりあえず2と入力し、詳細設定のフィルターを実行します。
Alt+Qでメールと入力すると、タイトルバーの検索の中にPDFで添付でメールの送信ができるのでこれをクリックします。
メーラーの新規メールが立ち上がったら次のように入力します。あて先はご自身のメールアドレスなどでいいでしょう。
入力したら、送信します。
そして、どこかのセルをクリックして、表示タブのマクロの中の記録終了をクリックします。
これで、表示タブの中のマクロ、マクロの表示で注文を実行をすればインデックス2の取引先に注文できます。
しかし、この状態ではインデックス2のみしか作成できないので、全部で4つの取引先に登録する必要があります。
その場合は繰り返しになりますが、繰り返しはVBAでないとできません。
表示の中のマクロのマクロの表示をクリックし、注文を編集します。
次のような画面になります。
Sub 注文()の下の行に
「Dim i As Integer」
「For i = 1 To 4」
End Subの上の行に
「Next」
と入力します。
これは、iという繰り返し回数でForからNextまでの間を1から4まで繰り返します。
ということをプログラムしました。
また、”2″となっている部分を繰り返し回数であるiに変更しましょう。
これで注文は4つの取引先の処理をするようになりました。
やってみてできなかったこと
実は現状のマクロの記録で行った上の手順では、メール送信の際に、PDFファイルとして添付できません。また、添付した新規メールを作成するだけであて先や本文を設定してくれません。
それを実現するにはOutlookと連携したVBAを作成する必要があります。
PDFファイルを発行するだけならできるので、それで作成したファイルを他の仕組みでメール添付し送るという方法も考えられます。
もしもわかりにくければサポートするサービスをご提供することも可能です。
お問い合わせはこちらから。
まとめ
今回は、お客様からの受注があって、その製品を作るのに必要な部品を仕入れを自動で行う仕組みを解説しました。
Excelで何かを作るにはテーブルを考え、Excelの表にして、ダミーデータを入れて、実際に作成するという流れです。
作成する手法は数多くありますが、その中でも私はExcelの機能でできることは機能で行い、関数や計算でできるところは数式で行い、一時的な記録はマクロの記録、繰り返しや分岐はVBAという段階にわけて作成しています。
実際には在庫のあるものは注文しないなどの仕組みも必要ですし、1週間仕入れをしなければその製品を組み立てはじめることもできないのでお客様に迷惑をかけますので、実際にはオーダーメードのものでもある程度はあらかじめ部品在庫は確保しています。
中には1部品がその製品の売上金額の10%以上になるような部品もあるので、そのような部品は受注があってからしか注文はできませんね。
そこで部品の納期をリードタイムとして管理していて、最大でかかるリードタイムからお客様に納める納期を決めています。
その辺も部品表の時点でデータがあれば受注前の見積もりの段階で納期を設定することができるはずですし、そのような仕組みを作ることも可能でしょう。
今回の仕組みが実現すれば、時短という概念はなくなります。注文する作業時間が0になるのですから。代わりにパソコンが数秒だけ動いて作業してくれるのです。
Excelや他のソフトウェアを使ってこのような何もしなくてよくなるツールの作り方、手順が少しでも伝わると嬉しいです。
コメント