エクセルで仕入帳を作成

質問

エクセルの初心者です。
現在、MS Office2007のexcelで仕入帳の作成をしています。

各シートごとに仕入先の商品のやりとりを出しています。仕入先のシートは全部で7つあります。
さらに、別シートにて仕入商品の平均単価を出したものを作成しております。
その平均単価を出しているシートについてなのですが。

画像のように、各シート（仕入先）で購入した商品の月毎の単価を出そうと試みているのですが、
単価平均のシートのセルB４に

=SUMIF(Ａ!$C5:$C1000,"1",Ａ!$F5:$F1000)/SUMIF(Ａ!$C5:$C1000,"1",Ａ!$D5:$D1000)

というような数式をいれてます。

これを、同じシートのセルC5に入力する場合は数式のA!をB!にひとつずつ手入力で変更しなければならないのでしょうか？

また、B5に入力するセルも"1"を"2"にひとつずつ手入力で変更するのでしょうか？

まとめて変更する方法、もしくは数式自体変更したほうがいいのか
大変お手数ではありますが、ご教示よろしくお願いします。

回答No.3

tom04 · ベストアンサー

こんばんは！
一例です。

B4セルに
=SUMIF(INDIRECT(B$3&"!$C$5:$C$1000"),$A4,INDIRECT(B$3&"!$F$5:$F$1000"))/SUMIF(INDIRECT(B$3&"!$C$5:$C$1000"),$A4,INDIRECT(B$3&"!$D$5:$D$1000"))
という数式を入れ、列方向と行方向にオートフィルでコピーではどうでしょうか？

余計なお世話かもしれませんが、エラー処理していませんので、各SheetにA列の「商品」がない場合は「0」で割ってしまうことになります。
その場合は　#DIV/0!　のようなエラーが表示されると思います。それを回避したい場合は

IF関数を使って、かなり長くなりますが、
=IF(ISERROR(数式),"",数式)　といった具合の数式にすればエラーの場合は空白になると思います。

長々と失礼しました。m(__)m

お礼

返事が遅くなりました、申し訳ございません。

思わず、手をたたいてしまいました（笑）
これですと、このまま数式をコピーするだけで反映されますね。
いやらしい話、すごい簡単でした。
INDIRECTの数式も勉強になりました。
ただ、数式がおっしゃった通り、長くなりましたね・・・。

ISERRORの数式を使わせていただきました。
大変ありがとうございました。

回答No.2

keithin

月間平均のシートの，
３行目に，各シートの正確な名前を記入しておきます
もしくは各シートの正確なシート名を，３行目の各記載内容と合わせておきます
また式中で検索する品名は，Ａ列の記載内容を利用します

元の数式：
=SUMIF(Ａ!$C5:$C1000,"1",Ａ!$F5:$F1000)/SUMIF(Ａ!$C5:$C1000,"1",Ａ!$D5:$D1000)

品名を利用：
B4:
=SUMIF(Ａ!$C5:$C1000,$A4,Ａ!$F5:$F1000)/SUMIF(Ａ!$C5:$C1000,$A4,Ａ!$D5:$D1000)

シート名(仕入れ先名)を利用：
B4:
=SUMIF(INDIRECT(B$3&"!$C:$C"),$A4,INDIRECT(B$3&"!$F:$F")) / SUMIF(INDIRECT(B$3&"!$C:$C"),$A4,INDIRECT(B$3&"!$D:$D"))
右にコピー，下にコピーしたときに，式中の＄記号の有り無しによって参照セルが自動で追従する様子を実際に手を動かして確認し，＄記号の使い方をマスターしてください。

お礼

返事が遅くなりました、申し訳ございません。

思わず、なるほどと思ってしまう数式でした。
＄記号はマスターとまではいきませんが、大変勉強になりました。

ご回答、大変ありがとうございます。

回答No.1

mu2011

>数式のA!をB!にひとつずつ手入力で変更
　⇒シート名が文字列は難しいのでA1、A2と数字と組み合わせれば以下の様な感じで如何でしょうか。
=SUMIF(INDIRECT("A"&COLUMN(A1)&"!C5:C1000"),COLUMN(A1),INDIRECT("A"&COLUMN(A1)&"!F5:F1000"))/SUMIF(INDIRECT("A"&COLUMN(A1)&"!C5:C1000"),COLUMN(A1),INDIRECT("A"&COLUMN(A1)&"!D5:D1000"))

お礼

返事が遅くなりました、申し訳ございません。
エクセルは全くの初心者なので、数式が大変勉強になりました。

シート名は実際は仕入先を記載します。
私のやり方も下手なのですが、ちょっと適用しにくかったですね・・・。

ご回答、大変ありがとうございました。