灯油定期配送管理①～日付算出

この地域では灯油の定期配送というのがよくあります。

冬になるとどうしても灯油を使う量が多くなるので、いちいち注文しなくても灯油を定期的に配送してくれるシステムです。

自分ならどのように管理するだろうか。ということで、Excelでこのような表を作成してみました。



顧客IDや地区コードなど必要なフィールドを作成します。

普段はオートフィルターなどを使い、地区コードや配送予定日で抽出したデータで、その日巡回するお客様を特定します。

作った初期段階では、配送予定日や前回配送日、給油量は、手入力する必要があります。



給油業務が終了したら、本日の給油したお客さまのデータに配送日と給油量を入力します。



入力すると次回の配送日がサイクル（日数）と入力した配送日データから算出されます。同時に今回入力したデータが履歴として残るように、前回配送データ算出に準備されます。



実際に配送日算出セル「G4」に数式を入力していきます。

配送日入力のデータにサイクル日数をプラスした日付が次回の配送予定日になります。そして配送がない場合は、配送予定に変化がないので、配送予定の値がそのまま表示されるようにします。

※表の範囲は「A3」から「L15」までです。




=IF(E4="",H4,D4+E4)

「配送日入力」セル「E4」が空白だったら、「配送予定」セル「H4」を表示し、入力されたら、「サイクル」セル「D4」と「配送日入力」セル「E4」をプラスした日付を表示する。という意味になります。

コピーしましょう。



この時点では、入力がないので、配送予定の日付がそのまま表示されます。

次に履歴用に用意した前回配送の日付を算出する数式を入力します。

入力したの日付と給油数が履歴となります。入力がない場合は前回配送のデータがそのまま表示されるようにします。

セル「I4」を選択し、数式を入力します。


=IF(E4="",K4,E4)

配送日入力セル「E4」が空白だったら、前回配送セル「K4」を表示し、入力されたら、配送日入力セル「E4」を表示する。という意味になります。

となりの前給油算出にも数式を入力しましょう。



=IF(F4="",L4,F4)

給油入力用のセル「F4」が空白だったら、前回給油セル「L4」を表示し、入力されたら給油入力セル「F4」を表示する。という意味になります。

コピーしましょう。



現時点では、まだ日付、給油量ともに入力されていなので算出フィールドと配送予定、前回配送ともに同じ値です。



変化を確かめましょう。

3/18に給油したお客様のデータに日付と給油量を入力します。

配送日入力と給油量を入力してみましょう。



配送日算出は入力日付にサイクルの日数をプラスした日付になりました。前算出フィールドはともに入力した日付および給油量になってます。また、入力していないところは変化ありません。

ただしく数式が入力されました。

あとは、算出された範囲をそれぞれコピーし、貼り付けて、入力したエリアをクリアすれば、明日からの作業準備が整います。



このコピーと貼り付け作業、さらに入力した範囲のクリアをマクロの記録を使って自動化します。

灯油定期配送管理②～マクロの記録・・・・・へつづく

灯油定期配送管理②～マクロの記録

灯油定期配送管理①～日付算出からのつづき

灯油定期配送の表にその日の給油したお客様データに給油日と給油量を入力すると、次回の配送予定日と、入力したデータを前回配送履歴として残す数式を入力しました。



今度は次の日からの配送データを抽出及び入力する準備をしなくてはなりません。

したがって算出したデータを正式なデータとして、「配送日算出」は「配送予定」「へ、「前配送算出」と「前給油算出」は「前回配送」と「前回給油」へそれぞれコピーして貼り付けます。

さらに、「配送日入力」と「給油」は次の日のためにクリアしておきます。



この作業をマクロの記録を使って自動化します。（画像はOffice2007での説明になります。）

まずは、「開発」タブの「コード」グループから、マクロの記録をクリックします。

※Office2003は「ツール」→「マクロ」→「新しいマクロの記録」



ダイアログボックスが表示されます。

マクロ名は「更新」にし、ショートカットキーを英数小文字で「k」と入力し「OK」をクリックします。
※マクロ名は任意でかまいません。ショートカットキーは一応「更新」なので「k」にしただけです。



これにより、マクロを実行するとき、Ctrlキーを押しながら、「k」を押すだけで簡単に実行できます。

最初に前配送算出データ範囲を選択し、コピーをします。（右クリックメニューからコピー）



前回配送のセルを選択します。コピーした範囲は数式が入力されているので、貼り付けるときは右クリックメニューから「形式を選択して貼り付け」を選択します。



ダイアログボックスの貼り付けから「値」を選択して「OK」をクリックします。



値のみのコピーがされました。



コピー範囲の解除をしておきましょう。キーボードの「ESC」キーを押します。点滅部分が消えるはずです。



次に配送日算出範囲も同様にコピーし「値」のみの貼り付けを行います。

このように貼り付けされます。「ESC」キーを押しておきましょう。



最後に入力範囲をクリアします。範囲を選択し、「Delete」キーを押します。



そして、一番最初のデータの先頭にカーソルを置きます。



「記録終了」ボタンをクリックして、マクロの記録終了です。



これで記録した時点から記録終了までの一連の流れが自動で行うことができるようになりました。

算出範囲のデータをそれぞれコピーして貼り付け、入力範囲をクリアする。たったこれだけですが、毎回手動でやるとなると、これが手間なんですね。

表は、算出されたデータがコピーされ、入力範囲もクリアされた状態になりました。



それでは、正常に動くか試したいのですがその前に一つ作業をしましょう。

算出エリアは次回の配送と履歴を表示するための、一時待機場所のようなものなので、随時表示しておく必要はありません。非表示にする作業をしましょう。

算出エリアの列番を選択し、右クリックメニューから「非表示」を選択します。あくまでも非表示にするだけです。削除ではありません。（配送日算出、前配送算出、前給油算出を非表示にしましょう。）



非常にシンプルで見やすくなると思います。

それでは、3/20に給油してきたお客様に配送日と給油量を入力をしてみます。



Ctrlキーを押しながら「k」キーを押します。



配送予定日、前回配送データともに更新されました。

これで明日からの作業ができますね。

今回は少ないデータで解説しましたが、本来は数千件以上あると思います。配送データを抽出するときは、地区や顧客IDを並び替えたり、オートフィルター機能などを使用し効率のよい作業をしましょう。

また、新規のお客様の追加なども考えられますので、マクロの記録時、今回のように、データが入力された範囲のみコピーするのではなく、かなり多めに（現顧客数の倍ぐらい）選択しておくと安心して追加できます。（VBコードを修正できる方は必要ありません。）

参考にしてみてください。