使える? 「生産原価費目 購入帳」


匠への技

 「匠への技」第9弾    こりゃ使えますぞ! 《生産原価費目》 資材購入帳。 
質問の中で特に数多く寄せられている出金データの管理、つまり経費帳のこと。

今回のポイントです。
  1. 入力の簡素化を目的とした階層型リスト参照の方法
  2. リスト参照用データテーブルへの追加工夫
  3. 代金決済に伴う〆期日管理ルールと関数式
  4. 「フィルタオプションの設定」機能を応用した検索方法
  5. 配列数式による科目毎、月別集計表の作成

特に1と2について汎用的に使える機能となるのでよくご観察下さい。


ブック名:takumivol-009.xls  
 シート名:出金伝票、単月検索、集計、テーブル      

 シート:出金伝票    データエントリーシートです。
  
/日付  → 取引日を入力。  入力形式は 10/5 のタイプとしています。

/科目  → 生産原価の費目をリストから選択し入力します。 
       リストテーブルへのデータは自動追加型としています。

       >>名前の定義およびリスト定義の方法はここをクリック!

/摘要  → 科目に属する摘要に絞り(階層テーブル)選択する。(直接入力も可とする。)
         摘要追加も科目と同様に自動追加型としています。

/購入先 → リストテーブルから選択による入力としています。 
        購入先の追加は科目と同様の設定です。

/決済  → リスト選択による入力とする。
        無記入:即日現金払い
        買掛1:10日〆、〆同月末払い
        買掛2:10日〆、〆翌月10日払い

/買掛金額 → 購入金額を入力

/月    → 日付から月を抽出(非入力項目)
セルB4の関数式    =IF(B4="","",MONTH(B4))

/買掛決済〆日 → 決済条件に準じた決済〆日(〆基準日)を自動計算(非入力項目)

 <関数式>
=IF(OR(B4="",F4=""),"",
     IF(DAY(B4)<=10,DATE(YEAR(B4),MONTH(B4),DAY(10)),
               DATE(YEAR(B4),MONTH(B4)+1,DAY(10))))

10日を基準として入力日付が10日以内の場合は日付と同じ年・月に10日をセット、以外の場合は日付の翌月10日を基準日にセットする関数式です。 
■ OR(B4="",F4="") → 項目で日付と決済のいずれか入力されていない時を判断しています。 
−真の条件 :  どちらかが無記入の場合は "" を代入します。
−偽の条件 :  〆基準日の計算です。ネストIFです。 

■ DAY(B4)<=10 → 日付の日が10日以内であるかを判断しています。
−真の条件
 日付と同年月の10日が〆日となるので  DATE(YEAR(B4),MONTH(B4),DAY(10)) となります。
−偽の条件
 翌月の10日が〆日となるので  DATE(YEAR(B4),MONTH(B4)+1,DAY(10))  月を求める時に  MONTH(B4)+1 1ヶ月後  を求める関数式になります。真と偽の条件違いはここだけです。

/代金決済日  → 決済の条件に準じた代金決済日を自動計算(非入力項目)

 <関数式>
=IF(I4="","",IF(F4="買掛1",DATE(YEAR(I4),MONTH(I4)+1,DAY(0)),
     IF(F4="買掛2",DATE(YEAR(I4),MONTH(I4)+1,DAY(10)),"決済区分無")))

決済区分から代金決済日を求める関数式です。買掛決済〆日を基準日として実際の支払日を求めます。

  I4="" → 決済が無記入を判断しています。
 
−真の条件   決済区分が無記入の時は "" を代入します。
−偽の条件   ネストIF  F4="買掛1" 入力値が「買掛1」の判断です。
 
−真の条件    〆基準日の同月末支払となり同月末日を求めます。
※月末日を求める箇所がポイントです。
 DATE(YEAR(I4),MONTH(I4)+1,DAY(0)) となります。MONTH(I4)+1 は買掛決済〆日の翌月が計算されます。しかし、 DAY(0) によって同月末が求められます。
−偽の条件   ネストIF  F4="買掛2" 入力値が「買掛2」の判断です。
−真の条件    〆基準日の翌月10日の支払日を計算します。
            DATE(YEAR(I4),MONTH(I4)+1,DAY(10))
−偽の条件  買掛1または買掛2以外の入力値となるので "決済区分無" を代入します。

 シート:単月検索    シート「出金伝票」を基準に検索する項目名と入力値を指定し「検索開始ボタン」をクリック。
リストからお望みのデータを抽出することができます。
この機能は「フィルタオプションの設定」をマクロに置き換えし手作業での煩わしい操作を省力しています。
  

 「検索開始」ボタンとリンクするマクロコードです。
Sub kensaku()
'
   Sheets("出金伝票").Range("b3:j2003").AdvancedFilter Action:=xlFilterCopy, _
   CriteriaRange:=Range("b2:b3"), CopyToRange:=Range("e2:l2"), Unique:=False
   Range("b2").Select

End Sub


 シート:集計   月別費目毎の集計表です。配列数式での集計です。
  
セルB2の関数式
=SUM((A5=出金伝票!$H$4:$H$13)*(集計!$B$4=出金伝票!$C$4:$C$13)*(出金伝票!$G$4:$G$13))
  ※13行をリミットとしていますので試す場合はここの数値を大きく変更して下さい。


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

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

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