按分をする場合の関数について

前年の売上に対して本年の目標を定めようと思います。
ところが各営業部の合計、社合計の月別合計が先に決定しておりその按分に苦労しています。
上の表にある各営業部の月度別実績を基にして、下の表にある営業部別合計、月度別合計を
変えることなく空欄の目標値を算出する関数はあるでしょうか？

No.4 ベストアンサー 回答者： tom04 回答日時： 2019/04/10 20:29

こんばんは！

アップされている画像では行合計は「5550000」になり、列合計は「5500000」になりますね。
行の合計が列の合計と同じく、「5550000」だとしての一例です。

↓の画像のような配置だとします。（画像ではB7セルの4月合計を　950000　ではなく　900000　として、列の合計に合わせています）

画像では作業用の列を1列設けています。
（各営業部ごとの構成比になり、各営業部の合計金額の比率として使用します。）

作業列J2セルに
=H2/H$7
という数式を入れフィルハンドルで下へコピーしています。
この構成比で各月の合計を振り分けます。
ただし、1円単位で誤差が生じると思いますので、
画像では第5営業部の行・9月（最終月）の列　でそれぞれ誤差調整をしました。

B2セルに
=ROUND(B$7*$J2,0)
という数式を入れ第4営業部のB5セルまでフィル＆コピー！

B6セルには
=B7-SUM(B2:B5)
としています。

B2～B6セルを範囲指定　→　B6セルのフィルハンドルで8月（F列）までフィル＆コピー！

最後の調整月9月のG2セルに
=H2-SUM(B2:F2)

としてフィルハンドルでG6セルまでコピー！

これでなんとか帳尻合わせができないでしょうか。

※　作業列が目障りであれば、遠く離れた列にするか
非表示にしてください。m(_ _)m

この回答へのお礼

ありがとうございます！やはり按分→ROUND関数→端数調整という方法しかありませんね。
実際の表はタテ2600行ヨコ４８行の大きなものなので苦労しますが地道に頑張ります！

お礼日時：2019/04/12 10:36

No.3 回答者： ほい3 回答日時： 2019/04/10 17:44

エクセル上でｘは、*で入れます。

念のため。

No.2 回答者： ほい3 回答日時： 2019/04/10 17:41

エクセルの表とします、質問時はＡＢＣの枠や行も表示で聞く方が

答えやすいです。
本年目標４月の営業１部に＝今年の営業１部の合計座標（＄I18等）ｘ
昨年の４月の営業１部の金額座標（C７など）/昨年の営業１部の合計
座標（＄I7等）と式入れて、空欄全枡に相対コピーでしょう。
$Iと言うのは列固定の設定です。
これだと、4月合計が、950,002円に成ったりしますが、誤差なので
無視すれば大丈夫です。

No.1 回答者： moon-and-star 回答日時： 2019/04/10 17:38

関数ではなく考え方でしょう。

例えば営業１部合計が1,500,000なんだから、単純に6ヶ月で割るか？
実績合計に対する各月の割合を計算して目標額から算出するか？
　　
それを計算式にする以外にありません。