わえなび ワード&エクセル問題集 waenavi

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel】按分の計算方法と按分比率を補正する計算式の練習問題【割合の問題、就職活動】


検索キーワードを見ると、按分(案分 あんぶん)の計算についてネットで調べる人が多いようですが、その悩みは2種類に分かれます。

  • 「按分 関数」「按分とは」のように按分の意味が全く分かっていない
  • 「按分 端数処理」のように誤差が生じることを理解している

Excelで按分で生じる端数をどのように処理しようか、家事按分の比率はどうするかなどと議論している最中に、「ぁ、ぁ、あんぶんって何ですか?」と尋ねるのは勇気が必要です。

そこで、按分の計算方法を誰にも聞けない初心者の皆さんのために、Excelを使ってこっそりと教えます。

f:id:waenavi:20181121231600j:plain

 

 

目次

1.按分、按分率とは何か

例えば、1万円の報酬を5人で分ける場合、1人2000円となります。これは「等分」です。しかし、5人の労働時間や貢献度が異なる場合に、同じ賃金を均等配分したら不平等です。このように事実上の差異があるのに等分をすると逆に不平等になることがあります。

また、等しくない分け方をする場合でも、事前にルールを定めて合理的な理由に基づく分け方をしなければトラブルの元になります。ある比率に基づいて分ける方法を「按分」といい、その配分の基準は原則として「構成比率」を使います(構成比率は全体の何%?の前に、大きい数で割る感覚を鍛えなさい【Excel割合の問題】)。「按分率」という言葉を使うことがありますが、それは構成比率のことです。按分率が35%と65%の場合は、それぞれ0.35倍と0.65倍をするということです。

それでは、実際にExcelを使って按分の計算をしてみましょう。

 

2.比による按分

問題

所持金3,000円を1:1:2に分けなさい。

f:id:waenavi:20181121183001j:plain

 

解説

3000円を3人で分けると1000円ずつになります。これは3等分です。

f:id:waenavi:20181121184515j:plain

 

1:1:2に分けるときには、一人は1500円(3000円の50%)です。ほかの2人は各750円(3000円の25%)になります。これをExcelを使って計算します。

f:id:waenavi:20181121184519j:plain

 

1+1+2をします。

f:id:waenavi:20181121184523j:plain

 

構成比率を出します。

f:id:waenavi:20181121184927j:plain

 

25%、25%、50%になります。3000円に構成比率をかけます。3000は絶対参照です。

f:id:waenavi:20181121184535j:plain

 

これで完成です。

f:id:waenavi:20181121184538j:plain

 

按分は、「分けようとしているもの」に「構成比率」をかけます。

f:id:waenavi:20181121185217j:plain

 

別解

3000円に構成比率をかけます(=3000*1/4)。

f:id:waenavi:20181121185220j:plain

 

このように1つの式にまとめて求めることもできます。

f:id:waenavi:20181121185223j:plain

 

3.人口比例配分

問題

2つの町D、Eの人口に応じて7議席を配分しなさい。

f:id:waenavi:20181121183116j:plain

 

解説

7議席を分けようとしています。人口に応じて配分するので、人口の構成比率をかけます。

f:id:waenavi:20181121214104j:plain

 

人口の合計を出します。

f:id:waenavi:20181121214108j:plain

 

7議席に構成比率をかけます。

f:id:waenavi:20181121214111j:plain

 

小数になります(このような誤差が発生する)。

f:id:waenavi:20181121214118j:plain

 

四捨五入をすれば2議席と5議席になります(こんなにきれいに割り切れることは少ない)。

f:id:waenavi:20181121214126j:plain

 

4.ポイント制による按分率の補正

問題

野球・サッカー・将棋のクラブに対して、合計が50万円になるように配分したい。

(1)部員数に応じて按分しなさい。

f:id:waenavi:20181121214433j:plain

 

(2)学校行事への参加状況や全国大会での実績などを評価してポイントにした。これを加味して部員数*評価ポイントで按分しなさい。

f:id:waenavi:20181121214436j:plain

 

(3)クラブに所属している者のうち37名は、ほとんど活動実績のない架空の部員であることが判明した。この部員を0.1人とカウントした部員数で按分しなさい。

f:id:waenavi:20181121214439j:plain

 

解説

(1)通常の按分

分けようとしているのは50万円で、部員数に応じて按分するので、部員数の構成比率をかけます。

f:id:waenavi:20181121215037j:plain

 

部員数の合計を求めます。

f:id:waenavi:20181121215040j:plain

 

122人です。これで構成比率が出るのと同時に配分もできます。50万円に構成比率をかけます。50万円と合計人数は絶対参照です。

f:id:waenavi:20181121215044j:plain

 

これで完成です。

f:id:waenavi:20181121215051j:plain

 

(2)比重をかける

単純に人数だけで按分すると不都合な場合があります。多めに配分したい場合、何らかの評価基準を設けてポイントを付けます。これを比重またはウェイトといいます。

f:id:waenavi:20181121215058j:plain

 

比重を設定した場合は、これをかけてから構成比率を出します。

f:id:waenavi:20181121215101j:plain

 

部員数に評価ポイントをかけます。

f:id:waenavi:20181121215106j:plain

 

このポイント数で配分します。ポイント数の合計を出します。4340ポイントです。50万円に構成比率をかけます。

f:id:waenavi:20181121215110j:plain

 

これで完成です。

f:id:waenavi:20181121215649j:plain

 

部員数が変わらなくても評価ポイントを変えれば配分が変わります。

f:id:waenavi:20181121215114j:plain

 

(3)形式的人数と実質的人数

形式的に届け出のある部員数で計算すると架空の部員を登録して、水増し計上するおそれがあります。実態調査をして実質的に活動している部員数で配分した方が良いです。部員数から幽霊部員を引くと実際にクラブ活動をしている人数が出ます。

f:id:waenavi:20181121215120j:plain

 

幽霊部員は0.1人とカウントするので、これに幽霊部員x0.1を足します。

f:id:waenavi:20181121215123j:plain

 

これで実質的な部員数で出ます。

f:id:waenavi:20181121215126j:plain

 

これを使って配分します。50万円に構成比率をかけます。

f:id:waenavi:20181121215130j:plain

 

これで完成です。

f:id:waenavi:20181121215133j:plain

 

5.練習問題1(配置人数)

問題

次の図は、グラウンド、体育館、第2体育館の3つのイベント会場の来場者予想人数である。警備員60人で警備するときの配置人数を、来場者数をもとに計算しなさい。

※いろいろな考え方があり、いろいろな答えがあります。

f:id:waenavi:20181121220727j:plain

 

解説

単純に来場者予想人数に応じて配分します。合計は2550人です。60人に構成比率をかけます。

f:id:waenavi:20181121220730j:plain

 

36人、14人、10人という配分になります。

f:id:waenavi:20181121220734j:plain

 

別解その1

体育館のイベントは午前のみ、第2体育館のイベントは午後のみです。イベント日程を考え、午前と午後に分けて考える方法があります。計算式を複合参照(7行目固定)に変えます。

f:id:waenavi:20181121220737j:plain

 

コピーを2回します。

f:id:waenavi:20181121221006j:plain

 

イベントのない会場の人数を消します。これで午前と午後の配分がそれぞれ計算できました。

f:id:waenavi:20181121221009j:plain

 

午前は43人と17人、午後は47人と13人に分け、午前中に体育館の警備をしていた4人が、午後、グラウンドに移動することになります。

f:id:waenavi:20181121221012j:plain

 

別解その2

体育館でイベントがなくても不審者が入らないように最低限の警備員を置くことも考えられます。

f:id:waenavi:20181121221015j:plain

 

例えば、予備の警備員として3人を確保したい場合、はじめから57人として計算すればよいです。

f:id:waenavi:20181121221019j:plain

 

6.練習問題2(会費)

問題

パーティー費用70万円を参加予定者130人で分けようとしている。そのうち45人については会費を多めに設定したい。会費を計算するための表を作り、会費を決定しなさい。

※いろいろな考え方があり、いろいろな答えがあります。

解説

70万円を130人で分けようとしています。130等分すると同じ会費になってしまいます。

f:id:waenavi:20181121222048j:plain

 

配分を変えるための表を作るには、まず、グループを作ります。そして、比重(ウェイト)を変えながら調節します。

f:id:waenavi:20181121222051j:plain

 

まとめるとこのような表になります。支出としての費用が70万円です。AグループとBグループに分けて、Aが会費が高いグループです。人数は45人と85人です。

f:id:waenavi:20181121222054j:plain

 

比重はどれだけ格差を設けるかによって変わってきます。ここでは、会費の比重を1.5倍に設定してみます。この比重は後で変更することができます。

f:id:waenavi:20181121222058j:plain

 

比重を設定したときは比重をかけた値で配分します。

f:id:waenavi:20181121222101j:plain

 

比重を設定したときは実際の人数をかけます。これを使って70万円を配分します。

f:id:waenavi:20181121222104j:plain

 

合計を出します。70万円に構成比率をかけます。

f:id:waenavi:20181121222107j:plain

 

これはグループごとの金額を表しています。Aグループは合計で約31万円、Bグループは合計で39万円余りとなります。70万円を31万円と39万円にとりあえず分けたことになります。これを人数で割れば会費となります。

f:id:waenavi:20181121222114j:plain

 

Aグループは6885円、Bグループは4590円です。小数では会費を集めることができないので、きりのいい金額で、例えば、これを7000円と4600円にしたとします。

f:id:waenavi:20181121222117j:plain

 

割り算をすると、実際の倍率は1.52倍です。

f:id:waenavi:20181121222121j:plain

 

実際の人数をかけて、実際に集まる金額を計算します。

f:id:waenavi:20181121222126j:plain

 

合計で706000円です。収入にあたる会費が706000円ですから、引き算をすれば6000円余ることが分かります。しかし、Aグループの人が1人でも欠席すると会費が7000円なので足りなくなる危険性があります。

f:id:waenavi:20181121222129j:plain

 

はじめに比重を1.5倍としましたが、今度は1.3倍で考えてみましょう。

f:id:waenavi:20181121222132j:plain

 

再計算されて会費(F列)が変わります。これに近い金額で、例えば、6300円と4900円にしたとします。実際の倍率は1.28倍です。合計がちょうど70万円になります。欠席者が出た時のためにもう少し会費が多いほうがいいかもしれません。

f:id:waenavi:20181121222136j:plain

 

7.補足

(1)最初の問題の答え

最初の問題は、20000+25000=45000なので、半分の6300円を5:4で分けて、6300円、3500円、2800円となります。

f:id:waenavi:20181121231600j:plain

 

(2)端数処理について

端数処理の方法は、四捨五入・切り上げ・切り捨て等の方法があります。詳しくは、こちらの記事をご覧ください。

 

(3)家事按分について

家事按分の方法については、こちらの記事をご覧ください。

 


解説は以上です。


8.動画版はこちら(無料)

この記事は、わえなびExcel新演習1割合の重要事例 Program1-9、1-10 の動画の内容を書き起こし、加筆修正したものです。

動画版(完全版)は、Youtubeにすべて無料で公開しております。ぜひ、ご覧ください。

Excel新演習1数式・割合の重要事例(全13回)【わえなび】 - YouTube

 

 


Copyright(C)2018-2021 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ