[[20160927170423]]　『数値の案分』（にとりん）　ページの最後に飛ぶ

『数値の案分』（にとりん）

シートは３枚あります。

シート１：基本情報
シート２：昨年度実績
シート３：本年度実績

シート１は以下の通りです。

　　　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　　　Ｆ　　　Ｇ　　　
１　　東京　６００
２　　大阪　３００
３　神奈川　２５０
４　　愛知　２００
５　　札幌　１５０
６　　福岡　１５０
７　　宮城　１００

シート２は以下の通りです。

　　　　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　　　Ｆ　　　Ｇ　　　
１　　　東京　　　？
２　　　大阪　　　？
３　　　東京　　　？
４　　　福岡　　　？
５　　　大阪　　　？
６　　　愛知　　　？
７　　神奈川　　　？
８　　　福岡　　　？
９　　　東京　　　？
１０　　大阪　　　？
１１　神奈川　　　？
１２　　宮城　　　？

シート３は以下の通りです。

　　　　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　　　Ｆ　　　Ｇ　　　
１　　　大阪　　　？
２　　　札幌　　　？
３　　　宮城　　　？
４　　　福岡　　　？
５　　　東京　　　？
６　　　東京　　　？
７　　神奈川　　　？
８　　神奈川　　　？
９　　　東京　　　？
１０　　愛知　　　？
１１　神奈川　　　？
１２　　札幌　　　？

シート１の数値をシート１Ａ列の条件からシート２・３のＡ列の条件で案分したいというものです。
例えば、上記の例ですと、、、

シート２

　　　　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　　　Ｆ　　　Ｇ　　　
１　　　東京　１００
２　　　大阪　　７５
３　　　東京　１００
４　　　福岡　　５０
５　　　大阪　　７５
６　　　愛知　１００
７　　神奈川　　５０
８　　　福岡　　５０
９　　　東京　１００
１０　　大阪　　７５
１１　神奈川　　５０
１２　　宮城　　５０

シート３

　　　　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　　　Ｆ　　　Ｇ　　　
１　　　大阪　　７５
２　　　札幌　　７５
３　　　宮城　　５０
４　　　福岡　　５０
５　　　東京　１００
６　　　東京　１００
７　　神奈川　　５０
８　　神奈川　　５０
９　　　東京　１００
１０　　愛知　１００
１１　神奈川　　５０
１２　　札幌　　７５

シート１東京６００はシート２に３つ、シート３に３つあるので各１００ずつ案分。
シート１大阪３００はシート２に３つ、シート３に１つあるので各７５ずつ案分。
シート１神奈川２５０はシート２に３つ、シート３に２つあるので５０ずつ案分。

といった感じです。

この場合のシート２Ｂ列、シート３Ｂ列に入力する関数を教えて頂きたいです。
何卒宜しくお願い致します。

＜使用 Excel：Excel2013、使用 OS：Windows10 ＞

実績のシートが実績ではなくなるのが、妙に思いますが…。

シート1に作業列を2列追加。1列目はA列と同じものがシート2とシート3に何件あるかのCOUNTIFした合計数。
2列目は数字を件数で割った値。
こうしておけば、シート2と3の方は、VLOOKUP関数でシート1のA列が一致するものの作業列の値を得るだけかと思いますよ。
（???） 2016/09/27(火) 17:42

？？？さん、すみません。
内容間違えましたので、再度掲載します。

～～～～～～～～～～～～～～～～～～～～～～

『数値の案分』（にとりん）

シートは３枚あります。

【シート１】：基本情報
【シート２】：昨年度実績
【シート３】：年度実績

【シート１】は以下の通りです。

【シート２】は以下の通りです。

　　　　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　　　Ｆ　　　Ｇ　　　
１　　　東京　１００　　　？
２　　　大阪　　５０　　　？
３　　　東京　　５０　　　？
４　　　福岡　　８０　　　？
５　　　大阪　　５０　　　？
６　　　愛知　１００　　　？
７　　神奈川　　６０　　　？
８　　　福岡　　３０　　　？
９　　　東京　　２０　　　？
１０　　大阪　１００　　　？
１１　神奈川　　４０　　　？
１２　　宮城　　３０　　　？

【シート３】は以下の通りです。

　　　　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　　　Ｆ　　　Ｇ　　　
１　　　大阪　　８０　　　？
２　　　札幌　　５０　　　？
３　　　宮城　１００　　　？
４　　　福岡　　４０　　　？
５　　　東京　　３０　　　？
６　　　東京　　８０　　　？
７　　神奈川　　６０　　　？
８　　神奈川　　３０　　　？
９　　　東京　　２０　　　？
１０　　愛知　　４０　　　？
１１　神奈川　　６０　　　？
１２　　札幌　１００　　　？

【シート１】の数値を【シート１】Ａ列から地区を拾い出し、【シート２】【シート３】のＡ列の地区毎にＢ列の比率でＣ列に案分数値を表示したいというものです。
例えば、上記の例ですと、、、

【シート２】

　　　　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　　　Ｆ　　　Ｇ　　　
１　　　東京　１００　２００
２　　　大阪　　５０　　５４
３　　　東京　　５０　１００
４　　　福岡　　８０　　　？
５　　　大阪　　５０　　５４
６　　　愛知　１００　　　？
７　　神奈川　　６０　　　？
８　　　福岡　　３０　　　？
９　　　東京　　２０　　４０
１０　　大阪　１００　１０８
１１　神奈川　　４０　　　？
１２　　宮城　　３０　　　？

【シート３】

　　　　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　　　Ｆ　　　Ｇ　　　
１　　　大阪　　８０　　　？
２　　　札幌　　５０　　　？
３　　　宮城　１００　　　？
４　　　福岡　　４０　　　？
５　　　東京　　３０　　６０
６　　　東京　　８０　１６０
７　　神奈川　　６０　　　？
８　　神奈川　　３０　　　？
９　　　東京　　２０　　４０
１０　　愛知　　４０　　　？
１１　神奈川　　６０　　　？
１２　　札幌　１００　　　？

【シート１】東京６００は【シート２】に３つ（１００・５０・２０）、【シート３】に３つ（３０・８０・２０）あるので、
【シート２】Ｃ１は６００÷（１００＋５０＋２０＋３０＋８０＋２０）×１００＝２００。
【シート３】Ｃ５は６００÷（１００＋５０＋２０＋３０＋８０＋２０）×３０＝６０。

【シート１】大阪３００は【シート２】に３つ（５０・５０・１００）、【シート３】に１つ（８０）あるので、
【シート２】Ｃ２は３００÷（５０＋５０＋１００＋８０）×５０＝５４。
【シート３】Ｃ１は３００÷（５０＋５０＋１００＋８０）×８０＝８６。
※１未満四捨五入

といった感じです。

この場合の【シート２】Ｃ列、【シート３】Ｃ列に入力する関数を教えて頂きたいです。
何卒宜しくお願い致します。

～～～～～～～～～～～～～～～～～～～～～～
（にとりん） 2016/09/27(火) 17:51

修正している間にご回答いただいておりました。
宜しくお願い致します。
（にとりん） 2016/09/27(火) 17:53

 質問に書いてある式でいいのなら

 Sheet2のC1
 =ROUND(SUMIF(Sheet1!A:A,A1,Sheet1!B:B)/(SUMIF(A:A,A1,B:B)+SUMIF(Sheet3!A:A,A1,Sheet3!B:B))*B1,0)

 Sheet3のC1
 =ROUND(SUMIF(Sheet1!A:A,A1,Sheet1!B:B)/(SUMIF(A:A,A1,B:B)+SUMIF(Sheet2!A:A,A1,Sheet2!B:B))*B1,0)

 こういうこと？

 Sheet2のC10は「107」になりますけど。
（笑） 2016/09/27(火) 19:56

コメント返信：

[ 一覧(最新更新順) ]

[[20160927170423]] 『数値の案分』（にとりん） ページの最後に飛ぶ

[[20160927170423]]　『数値の案分』（にとりん）　ページの最後に飛ぶ