EXCEL関数について

質問

EXCELで比率を元に人数を求める計算をしたいです。

H１５年度の人数とH２１年度の人数を割って、比率を出しました。
その出た比率をもとに、シュミレーションで合計人数が２２００人になるようにしたいです。
※比率は変えずに合計人数が2200人になるように各地区の人数を出したい。
このような人数を求められる計算式、もしくは関数があれば教えてください
例

　　　H１５年度人数＿＿＿H21年度人数＿＿比率＿＿シュミレーション
A地区＿209・・・・・・・・・・・・・・・185・・・・・・・・・・89％→ ？人
B地区＿209・・・・・・・・・・・・・・・201・・・・・・・・・・96％→ ？人
C地区＿276・・・・・・・・・・・・・・・213・・・・・・・・・・77％→ ？人
D地区＿252・・・・・・・・・・・・・・・180・・・・・・・・・・71％→ ？人
E地区＿233・・・・・・・・・・・・・・・198・・・・・・・・・・81％→ ？人
F地区＿266・・・・・・・・・・・・・・・227・・・・・・・・・・85％→ ？人
G地区＿210・・・・・・・・・・・・・・・147・・・・・・・・・・70％→ ？人
H地区＿160・・・・・・・・・・・・・・・115・・・・・・・・・・72％→ ？人
I地区＿264・・・・・・・・・・・・・・・・189・・・・・・・・・・72％→ ？人
J地区＿203・・・・・・・・・・・・・・・144・・・・・・・・・・71％→ ？人
K地区＿121・・・・・・・・・・・・・・・・67・・・・・・・・・・55％→ ？人
L地区＿74・・・・・・・・・・・・・・・・ 49・・・・・・・・・・・66％→ ？人
M地区＿143・・・・・・・・・・・・・・100・・・・・・・・・・・70％→ ？人
N地区＿210・・・・・・・・・・・・・・172・・・・・・・・・・・82％→ ？人
O地区＿154・・・・・・・・・・・・・・115・・・・・・・・・・・75％→ ？人
P地区＿238・・・・・・・・・・・・・・183・・・・・・・・・・・77％→ ？人
Q地区＿141・・・・・・・・・・・・・・105・・・・・・・・・・・74％→ ？人
R地区＿178・・・・・・・・・・・・・・142・・・・・・・・・・・・80％→ ？人

合計＿3541人・・・・・・・・・・・2723人・・・・・・・・・・・・・・・・・・・・2200人（シュミレーション人数計）

回答No.4

okormazd · ベストアンサー

同じ比率で減らして2200人にすることは原理的にできない。
便法として、同じ比率で減らしたものの合計が、2125.46人になるから、2200人とこの合計の比率2200/2125.46=1.03507を各地区に掛けて各地区の配分とすればいいのではないか。

お礼

わかりやすい回答ありがとうございました。
解決できました！

回答No.5

root_16

No.3です。

連立方程式についてですが、A地区において
平成15年度：　209＝15a+ｂ・・・(1)
平成21年度：　185＝21a+b・・・(2)
(1)-(2)でa=-6/24 (1)に代入してb=1702/8
これで平成X年度のA地区の人数Yは
Y=-6/24 X+1702/8で表せることが分かります。
同様に他の地区も表せます。
同じXの関数の形をしているので全地区の合計もpx+qで表せます。
※すみません。面倒なのでp,qの値を求めていません。

これで2200=ｐｘ+ｑにより
全体が2200人になる場合の平成の年度が分かります。

No.4の人の回答でも同じなんで、こんな面倒な方法である
必要はないんですが
この方法で行うと、平成○.○○みたいに答えが出てくるので
平成○年の○月○日に2200人になりそうだという計算が
比較的容易です。

お礼

丁寧な回答ありがとうございました。
再度、質問してしまったにも関わらず、細かい説明でとてもうれしかったです。
ありがとうございました。

回答No.3

root_16

もし、「比率」といっているのが、
年度ごとの減少傾向のことを言っているのであれば、
地区ごとの人数をy、年度をｘとして
y=ax+bの形にして、15年度と21年度の値を代入して
連立方程式を解いてaとｂの値を求めます。
同様に全地区の人数を数式にして
全部足すとpｘ+ｑの形になると思います。
px+q＝2200とするとxが求まります。
そのｘが○年度のシミュレーションになると思います。
面倒な説明で申し訳ないですが。

補足

回答ありがとうございます。
大変申し訳ないのですが、連立方程式の数式を数値を使って教えていただけませんか？？

回答No.2

A88No8

こんにちは
>比率をもとに、シュミレーションで合計人数が２２００人になるようにしたい
　計算は、２２００×比率 で求められます。

　しかし基本的に比率を元に計算しただけでは端数の丸め誤差があるため、ぴったり２２００人になりません（１０人の９％は０．９人ですが人の最小単位は１人）。　２２００人ぴったりにする手順というか質問者さんの方針が必要です。

お礼

親切な回答ありがとうございました。
解決できました！

回答No.1

Kotori_D

自信はありませんが、比率を整数にして
（2200/「比率総数」）*「各地区比率」
で出ませんかね？

お礼

回答ありがとうございました！
無事に解決できました♪