Excel関数　経費一覧表

こんにちは。
図解できないのでうまく説明できるか不安ですが、参考までに。

A1に年間目標金額　B1に￥3,000,000
A2に４月実績　　　　 B2に \200,000
A3に５月実績　　　　 B3に \300,000
：
A13に３月実績 という風にAのセルに項目を、Bのセルに金額を入れていくとします。

３月実績の下に目標残金として=B1-sum(B2:B13)

その下に月目標残金平均額として　=B14/countblank(B2:B13)

これで出せるかと思います。

要するに表の空白部分（残りの月）を数えてそれで残金を割って平均を出しているということです。





> 1年間の経費目標金額を決めて、目標金額から毎月の実績金額を引いて残りの月で平均割りして記載できるような関数があったら教えて下さい
>
> 例　消耗品の設定目標金額＝300万／1年間
> 　　4月実績　20万　5月実績　30万　
> 　　残り10か月の目標金額＝250万÷10か月＝25万／月

『まひるん☆』 さん
こんにちは
『 るな姫』 さんには、横から失礼致します。
私は問い合わせ内容そのものではございませんが費用の様々な企業の予実表を作成し、納品してきた1人です。
回答の投稿に添付機能があれば、事容易いのですが、残念です。

ご回答の前に私が対応してきた時の作成時考慮点を先に記述致します。
『まひるん☆』さんが既に検討済みであればご容赦願います。

◆考慮点◆
１．年額の予定額を決定予定額とする機は、いつなのか？
　　費用発生の前月なのか、発生の前四半期なのか
２．予定額から実績累計を減算することに異議はありませんが、実務では結果がマイナスになる時があります。その場合の表記をどのようにすべきか
３．残額を〇〇月数で按分しました時、総額との差額（max１１円）が算出される場合があります。
　　その端数をどこにどのような金額を計上するべきか
４．処理対象の費用は、会社一本で良いか、該当部課別でなくてよいか
５．期中での決定と暫定との判断はどう組み込むか
などです。

私案を提示させて頂きますが、何点か前提条件を設定させて頂きました。
１．予定、確定関係なく、表には月別の金額を表示する。そして、その判断に各月行毎に確定区分を設定し、予定額項目は関数を使用するものの上書き可能とする。
２．端数は四半期に分けておのおのの初月度に１円づつ加算し調整する。
　　３月末決算の場合、端数が０円になるまで４月⇒７月⇒１０月⇒・・・のように加算する。
　但し、加算は未決の月のみとする。

ご質問内容と上述を実現する為に非表示項目を設定する。（後述）
また、期末の下段に合計行と差分額行を設定する。
更に、実績額、予定額等金額の属性は、金額でなく数値でマイナス発生ありで定義する。
と致しました。
次の投稿で具体的な設定内容を記述致します。

『まひるん☆』 さん
以下が私の素案です。
前提条件に１点付加させて頂きます。
　・計画決定区分は期首月から順次、都度連続していること。（４月が決定していて、５月が未定、６月が決定のように抜け月は考えません。）

１．１行目：　〇〇年度　〇〇〇科目　　☆予算実績表☆　　　印刷日（todayを設定します。）
２．２行目：　〇〇科目計画額
　　入力項目、数値、マイナスあり、３桁区切り
３．３行目：　各項目名
　　「月度」、「主な摘要」、「実績発生額」、「計画決定区分」、「計画決定額」、「計画実績残額」、
　　「未決計画按分額」
　　以降の列は１２ヶ月分（１ケ月分で２列）設定し、「〇〇月按分額」、「〇〇月未決差分額」を設定します。これらの項目は全て数値項目の小数２桁、マイナスありとします。
４．４行目～１５行目：
　　各月のデータ
５．１６行目：　合計行
６．１７行目：　差分行（計画額 － 「〇〇月按分額」)

【決定区分】　初期値＝0 　（未決＝０、決定＝１）
【計画決定額】　初期値=0
　　　関数：　=同行の「〇〇月未決差分額」項目
　　決定額がこれと異なる場合は、上書き
【計画実績残額】　初期値＝０
　　　関数：　決算が３月の場合の期首月の当項目の関数：
　　　　　＝〇〇科目計画額　－　実績月額
　　　　　　　期首月＋１月以降の当項目の関数
　　　　　＝前月の「計画残額」　－　「当月月額」
・・・・・・
按分の差分振り分け関数：
=IF(差分行の金額>0,〇〇月按分額+1,〇〇月按分額)
これを差分額に応じて差分計上優先順位に基き同様な関数を設定します。
従いまして、按分される月によって　差分行の金額>0　の定数を変えます。
但し、按分は該当前月の残額に対して行います。
それを共通の関数を当てはめますとIF(・・・、IF・・・）））となり煩雑になりますので１２ヶ月分の計算項目を用意した次第です。

小数２桁表示としたのは、EXCELの特徴で可視化数値と異なる結果が出るのが理由です。