【エクセル】他シートを参照した平均値の算出

質問

MicroSoft Office Excell 2003について質問をさせて頂きたいです。
下記に記載させて頂いた３つのシートから、
りんごの価格の平均をSheet3の[ ]に出す方法が知りたいです。

【Sheet1】
key　名称
001　りんご
002　みかん
003　りんご

【Sheet2】
key　価格１　価格２　価格３
001　100　　　80 　　　50
002　80
003　60 　　　90

【Sheet3】
名称　　平均価格
りんご　 [ ]

【補足】
・マクロを使用したくないです
・Sheet1とSheet2には変更を加えたくないです
・Sheet3に途中の計算結果等を載せたくないです
・シートが増えることは問題ないです
・価格２、価格３が存在しない場合は空白セルとなっています

方法をご存知の方がいらっしゃいましたら、
大変、恐縮ではございますが、ご教授頂けないでしょうか。
よろしくお願い致します。

回答No.1

kybo · ベストアンサー

Sheet3のＢ2のセルに以下の様に入力し、Ctrlキー+Shiftキーを押しながらEnter

=AVERAGE(IF(Sheet1!B2:B100=A2,IF(Sheet2!B2:D100="","",Sheet2!B2:D100),""))

お礼

簡潔なご回答ありがとうございます。
非常に助かりました。

大変、申し訳ないのですが、一点条件が漏れてしまっておりました。。
後出しの条件で大変申し訳ございません。

Sheet2には対応するkeyが存在しない場合があります。

例：
【Sheet2】
key　価格１　価格２　価格３
001　100　　　80 　　　50
003　60 　　　90

kybo様のご回答は、初めにこちらの提示した条件を満たしており、
簡潔なものでしたので、今後の他の方のご回答に寄らず、
ベストアンサーとさせて頂きたいです。
お手数をお掛けいたしました。

回答No.3

KURUMITO

key番号が違っているのに同じ名称（りんご）があるということでかなり難しい問題となっています。
シート1から3までには作業用の列を作りたくはないということなのでシート4に作業用のシートを別に作って対応することにします。
シート4のA2セルには次の式を入力してB2セルまでオートフィルドラッグコピーしたのちに下方にもオートフィルドラッグコピーします。

=INDEX(Sheet1!$A:$B,ROW(A2),COLUMN(A2))

これによってシート1の表をコピーすることになります。
C2セルには次の式を入力して下方にオートフィルドラッグコピーします。

=IF(OR(A2="",A2=0),"",IF(COUNTIF(B$2:B2,B2)=1,ROUNDDOWN(MAX(C$1:C1),-3)+1000,ROUNDDOWN(INDEX(C$1:C1,MATCH(B2,B$1:B1,0)),-3)+COUNTIF(B$1:B1,B2)))

この式では同じ名称（りんご）でkey番号が500有ったとしても対応できます。
D2セルには次の式を入力して下方にオートフィルドラッグコピーします。

=IF(C2="","",SUM(E2:Z2)/COUNTIF(E2:Z2,">0"))

E2セルには次の式を入力してZ2セルまで横にオートフィルドラッグコピーしたのちに下方にもオートフィルドラッグコピーします。

=IF(OR($A2="",$A2=0),"",IF(MOD($C2,1000)=0,INDEX(Sheet2!$A:$Z,MATCH($A2,Sheet2!$A:$A,0),COLUMN(B2)),IF(INDEX($E$1:$AA1,MATCH(ROUNDDOWN($C2,-3)+COUNTIF($B$1:$B1,$B2)-1,$C$1:$C1,0),COLUMN(A2))<>0,INDEX($E$1:$AA1,MATCH(ROUNDDOWN($C2,-3)+COUNTIF($B$1:$B1,$B2)-1,$C$1:$C1,0),COLUMN(A2)),INDEX(Sheet2!$A:$Z,MATCH($A2,Sheet2!$A:$A,0),COLUMN(B2)-COUNTIF(INDEX($E$1:$AA1,MATCH(ROUNDDOWN($C2,-3)+MOD($C2,1000)-1,$C$1:$C1,0),1):INDEX($E$1:$AA1,MATCH(ROUNDDOWN($C2,-3)+MOD($C2,1000)-1,$C$1:$C1,0),23),">0")))))

最後にお求めのシート3のA2セルには次の式を入力してB2セルまでオートフィルドラッグコピーしたのちに下方にもオートフィルドラッグコピーします。

=IF(COUNTIF(Sheet4!$C:$C,ROW(A1)*1000)=0,"",IF(COLUMN(A1)=1,INDEX(Sheet4!$B:$B,MATCH(ROW(A1)*1000,Sheet4!$C:$C,0)),INT(INDEX(Sheet4!$D:$D,MATCH(ROW(A1)*1000+COUNTIF(Sheet4!$B:$B,$A2)-1,Sheet4!$C:$C,0)))))

これによってA列には名称が、B列には金額の平均が表示されます。

お礼

ご回答頂きまして、ありがとうございます。
まさか、ここまで難解になるとは、思っておりませんでした。。
大変、ご面倒な質問に丁寧に回答頂きましてありがとうございます。

数式が複雑になってしまったのは、
私の質問の条件に問題があるのでしょうね。。

ご回答頂いた内容は、自分には高度で理解に時間がかかりそうですが、
一式ずつ、読み解かせて頂きたいと思います。

貴重なお時間を、このような質問に割いて頂いたkurumito様に、
感謝させて頂きたいです。

回答No.2

MackyNo1

Sheet1の横方向のデータ数が最大10個（K列まで）で、Sheet2の品名のデータ数が１００行目までの範囲に入力されているなら、Sheet3のA2セルに以下の式を入力して下方向にオートフィルしてください。

=AVERAGE(INDEX(Sheet2!$B$2:$K$100,MATCH(A2,Sheet1!$B$2:$B$100,0),))

お礼

ご回答ありがとうございました。
参考にさせて頂きたく思います。

こちらの入力ミス等であれば、大変申し訳ないのですが、
質問と同じ入力内容を記載したエクセルシートに、
ご回答頂いた数式を入れた所、結果が正しくありませんでした。
（例えば、Sheet2のD4に10000と入力した所、結果が77でした。）

また、下記の方のお礼にも書かせて頂いたのですが、
こちらの記載漏れで、
「Sheet2に対応するkeyが無い場合がある」
という事が、どこにも書かれていませんでした。
後付で大変申し訳ございません。