[[20150421104200]]　『表作成について』（羊）　ページの最後に飛ぶ

『表作成について』（羊）

過去のデータを見させていただきました。
似た感じで下記のような表現をしたいです。
関数でうまくできる方法がありましたら教えていただきたいです。

sheet1のような変動したい単価表を作成します。
＜sheet1＞
商品名　区分　4月単価　5月単価　6月単価　7月単価　8月単価　9月単価　・・・　3月単価　
商品Ａ　　Ａ　　100.00　　110.00　　110.00　　118.80　　118.80　　118.80　　・・・　118.80
商品Ｂ　　Ｂ　　100.00　　100.00　　100.00　　100.00　　100.00　　108.00　　・・・　108.00
商品Ｃ　　Ｃ　　200.23　　200.23　　200.23　　200.23　　200.23　　200.23　　・・・　200.23

例）sheet1では自由に単価を変更したい。
Ａは5月に10％単価を変更、7月に8％単価を変更。（1年のうちに何度も単価を変えることがあります。）
Ｂは9月に８％単価を変更。
Ｃは１年間変動なし。

【表現したい結果表】
＜sheet2＞
コード　区分　4月単価　5月単価　6月単価　7月単価　8月単価　9月単価　・・・　3月単価
1111　　Ａ　　　10.50　　　11.55　　　11.55　　　12.47　　　12.47　　12.47　　・・・　12.47
2222　　Ｂ　　　　3.33　　　3.33　　　3.33　　　　3.33　　　　3.33　　　3.60　　　・・・　 3.60
3333　　Ａ　　　　5.50　　　6.05　　　6.05　　　　6.53　　　　6.53　　　6.53　　　・・・　 6.53
4444　　Ａ　　　　7.20　　　7.92　　　7.92　　　　8.55　　　　8.55　　　8.55　　　・・・　 8.55

１．sheet2では、sheet1と同じ区分の月単価が変動した場合、変動した後の単価で数値セットしたい。
例）区分Ａのコード「1111」は５月に10％変動があったため10.50の10%で11.55をセット。
　　6月もそのまま11.55をセット。
　　さらに７月に８％単価が変わったので11.55の８％で12.47をセット。
　　（小数点第二位四捨五入で表現します）
　　7月以降変動がないためそのまま12.47がセットされる。

このような変動表を作成したいです。

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 >小数点第二位四捨五入で表現します
 これは表示だけ小数点以下2桁表示とするのか、値そのものを小数点以下2桁とするのかどちらだろうか？

 値そのものを小数点以下2桁にする場合はSheet2のD2セルに
 =ROUND($C2/SUMIF(Sheet1!$B$2:$B$100,$B2,Sheet1!$C$2:$C$100)*INDEX(Sheet1!$D$2:$N$100,MATCH($B2,Sheet1!$B$2:$B$100,0),COLUMN(A1)),2)
 と入力して右および下へフィルコピー、表示だけ小数点以下2桁にする場合は
 =$C2/SUMIF(Sheet1!$B$2:$B$100,$B2,Sheet1!$C$2:$C$100)*INDEX(Sheet1!$D$2:$N$100,MATCH($B2,Sheet1!$B$2:$B$100,0),COLUMN(A1))
 として表示形式で小数点以下2桁に。

 どちらもSheet1のデータが最大100行目までとしている。
 もっと行数がある場合は式中の「$100」部分を大きくしてくれ。
（ねむねむ） 2015/04/21(火) 11:24

ありがとうございます。凄いです。
この意味を教えてください。よろしくお願いします。

 =ROUND($C2/SUMIF(Sheet1!$B$2:$B$100,$B2,Sheet1!$C$2:$C$100)*INDEX(Sheet1!$D$2:$N$100,MATCH($B2,Sheet1!$B$2:$B$100,0),COLUMN(A1)),2)

（羊） 2015/04/21(火) 13:15

 式自身は
 =ROUND(A/B*C,2)
 という構造になっていてROUND関数でA/B*Cの結果を小数点以下2位未満を四捨五入している。
 A/B*C
 は

 A:４月の単価[$C2]
 B:４月の単価変動率[SUMIF(Sheet1!$B$2:$B$100,$B2,Sheet1!$C$2:$C$100)]
 C:その月の単価変動率[INDEX(Sheet1!$D$2:$N$100,MATCH($B2,Sheet1!$B$2:$B$100,0),COLUMN(A1))]
 となっている。

 BはSUMIF関数でSheet1のB列がSheet2のB列の値と同じSheet1のC列の値を持ってきている。
 その値で4月の単価を割ることで変動していない実質単価を求めている。

 INDEX関数の構文は
 =INDEX(セル範囲,セル範囲内での行,セル範囲内での列)
 で、一つのセルを求める。
 ここでCの式は
 INDEX(５月から3月までの変動率表,Sheet1のB2セルからB100セル列の値がSheet2の$B2セルと同じ行,D列から何列目か)
 で、対応する区分・月の変動率を求めている。

 もし、まだ不明な点があれば質問してくれ。
（ねむねむ） 2015/04/21(火) 13:42

ありがとうございます。
COLUMN(A1)はA1と表示してあるのはどういう意味があるのでしょうか？

（羊） 2015/04/21(火) 13:56

 COLUMN関数は指定したセルの列番号を返す。
 (セル指定を略した場合は入力されているセルの列番号を返す）
 式を入力した1列目では1、コピーした2列目では2がほしいためA1を指定している。

 D列への入力なので
 COLUMN()-3
 でもいいが、表の位置を変更したい場合、「COLUMN()-3」では3の部分を他の値に変えなければならないが「COLUMN(A1)」であれば変更がいらないためにセルの指定で「A1」を指定している。

（ねむねむ） 2015/04/21(火) 14:10

コメント返信：

[ 一覧(最新更新順) ]

[[20150421104200]] 『表作成について』（羊） ページの最後に飛ぶ

[[20150421104200]]　『表作成について』（羊）　ページの最後に飛ぶ