こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

【Sheet2】
key 価格1 価格2 価格3
001 100   80    50
002 80
003 60    90

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

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


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

投稿日時 - 2012-01-09 13:56:26

QNo.7234166

困ってます

質問者が選んだベストアンサー

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

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

投稿日時 - 2012-01-09 16:21:50

お礼

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

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

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

例:
【Sheet2】
key 価格1 価格2 価格3
001 100   80    50
003 60    90


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

投稿日時 - 2012-01-09 17:52:28

ANo.1

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

-広告-
-広告-

回答(3)

ANo.3

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列には金額の平均が表示されます。

投稿日時 - 2012-01-10 08:04:29

お礼

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

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

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

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

投稿日時 - 2012-01-11 00:08:09

ANo.2

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

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

投稿日時 - 2012-01-09 17:36:10

お礼

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

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

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

投稿日時 - 2012-01-09 18:03:09

-広告-
-広告-

あなたにオススメの質問

-広告-
-広告-