総務・庶務担当ご用達! 『郵便料金自動計算』


匠への技

 「匠への技」第6弾は、郵便物の重量から郵便料金を割り出す計算です。 
料金テーブルをどのように設定したら効率的か等々出来上がるまでの課程にチョットだけ振れてみました。
今回も同様の形式でまとめています。


 画面デザイン       シート名:郵便料金

 → 明細部 項目説明へJUMP                


■設計のポイント
@部署集計テーブルの考え方
   部署毎に1日の使用する量を分析するために部署テーブルと集計テーブルを一体にした。
  K列は部署名、L列は部署の使用累計金額

A料金テーブル
まず、郵便料金の体系 をみると定型と定形外があります。また、普通と速達があります。それぞれに重量があり料金が決められています。
画面デザインのN列とO列を参考します。N列は、6桁の料金コード、O列が料金です。
コードは6桁でコード形態は
普通・速達 定型・定形外 重量
1桁
1:普通
2:速達
1桁
1:定型
2:定形外
4桁

1〜4000c
となります。普通と速達を2つのテーブルで管理するより一つのテーブルで管理した方が関数式が簡単になる点に着目し、テーブルを1つにしました。  料金表と見比べ意味を理解して下さい。また、データの入力不完全は #VALUE! で、テーブルに存在しない区分、重量を入力するとエラー #N/A となりエラーをそのまま表示することで入力の妥当性を判定するようにしました。


 使用する関数  
  1. IF(論理式,真の条件,偽の条件)   
  2. CEILING(数値,基準値)
  3. SUMIF(範囲,検索条件,合計範囲)
  4. VLOOKUP(検索値,範囲,列番号,検索の型)
  5. AND(論理式1, 論理式2, ...)
・・・・・6回目登場
・・・・・2回目登場
・・・・・3回目登場
・・・・・2回目登場
・・・・・初回


 解説
見出し部  (鑑部)
  1. セルF3 取扱日 (入力)

明細部
 ※明細部のセルは列で説明します。また、関数式のサンプルは明細の先頭行となる5行目を基準としています。
  1. A列 部署                                           <画面デザインへ>
    部署名を入力します。

  2. B列 普通・速達
    普通郵便・速達郵便の区分を入力します。
    1:普通郵便
    2:速達郵便
    ※この入力コードは料金テーブル1桁目のコード参照で使います。

  3. C列 定型・外                                          <画面デザインへ>
    定型郵便物と定形外郵便物の区分を入力します。
    1:定型
    2:定形外
    ※この入力コードは2.同様料金テーブルの2桁目のコード参照で使います。

  4. D列 重量(c)                                         <画面デザインへ>
    秤で計量した郵便物の重量をcで入力します。
     
    ここまでのA列からD列までが手入力です。

     
  5. E列 換算重量                                         <画面デザインへ>
      

    関数式 =IF(D5="","",IF(D5<=100,CEILING(D5,25),IF(D5<=250,CEILING(D5,50),IF(D5<=1000,CEILING(D5,250),CEILING(D5,1000)))))

    100グラムまでが25の倍数250グラムまでが50の倍数1000グラムまでが100の倍数それ以上が1000の倍数で換算重量を求め、料金テーブルの重量と一致させています。この倍数にあてはめる最適な命令はCEILING関数しかありません。IF関数で IF(D5="","" 非入力時の空欄の代入も忘れてはいけません。

  6. F列 キー                                               <画面デザインへ>
    関数式  =IF(AND(B5<>"",C5<>""),B5*100000+C5*10000+E5,"")
    キーを計算する条件が AND(B5<>"",C5<>"") B列とC列どちらも入力されている時に計算し、以外は空欄を代入します。B5に10万倍している理由は6桁の先頭桁を意味します。C5に1万倍は6桁の内の2桁目のコードに該当させるためです。その値に換算重量を加算すればテーブルの料金コードができあがります。

  7. G列 料金                                              <画面デザインへ>            

    関数式  =IF(F5="","",VLOOKUP(F5,$N$2:$O$32,2,FALSE))  

    キーが設定されていない場合は空欄を代入します。 =IF(F5="","", 以外は、F列のキー項目の値をキーとしてVLOOKUP関数を使い料金テーブルから該当するキーの料金を代入します。 書式を複写しますので料金テーブルの範囲は絶対セルで定義です。VLOOKUPの検索の型FALSEは完全一致を意味し不一致の場合は、 #N/A を返します。

  8. L列 部署テーブルの累計金額
    関数式  =SUMIF($A$5:$A$29,K2,$G$5:$G$29)
    部署名テーブルの部署名K列をキーとして料金セル範囲内から該当する部署の料金を集計します。


 もひとつ おまけ
部署名を入力する時にちょっとした小技を使っています。
入力規則 で部署テーブルを範囲とした名前をリストさせその中から選択させる技です。
部署名毎に集計を行うので名称違い防止に最適な入力方法です。

その方法は、
 @メニューバー 「データ」→「入力規則」

 A入力値の種類 リストを選択

 B元の値  部署テーブルの名称範囲を指定

   



○「匠への技」VOL-6で作成したExcelブックのダウンロード

  演習できるExcelブックを用意しています。下図のダウンロードボタンをクリックして下さい。

     Excel Book Name: takumivol-006.xls  Download file: takumivol-006.lhz