エクセルで商品別の平均金額を出す方法2

質問

先ほど、回答を頂いた方にベストアンサーに選んだら回答が締め切ってしまったようです。
問題解決できたと思ったのですが、最後に躓いてしまいました。

ですのでもう一度解決法を教えて頂ければと思います。

先ほどの質問内容です。↓
-----------------------------------------------------------------------
始めて教えてgooを利用します。
よろしくお願いします。

エクセルで青果の仕入れを管理しようかと奮闘中なのですが、解らないことがあるので教えてください。（エクセルは初心者です。）

シート１に
　　　　A　　　　B　　　　C　　　　D
　商品名　　仕入価格
　キャベツ　１０００円
　玉ねぎ　　６００円
　キャベツ　８００円
　ポテト　　５００円
　玉ねぎ　４００円

シート２に
　　　　A　　　　B　　　　C　　　　D
　商品名　　平均額　価格（現）
　キャベツ
　玉ねぎ
　ポテト

１．商品別に仕入価格の平均額をシート２の平均額のセルに自動的に入力されるようにしたい。

２．シート２の価格（現）というセルには、シート１に入力した最後の仕入価格を出力したい。

この２つを実現する方法を教えてください。
よろしくお願い致します。
---------------------------------------------------------------------------

頂いた回答です↓
---------------------------------------------------------------------------
１．に関しては、Excel2007以降であれば、

=AVERAGEIF(Sheet1!$A$1:$A$6,A2,Sheet1!$B$1:$B$6)

という式、をB2に
Excel2003までであれば、

=SUMIF(Sheet1!$A$1:$A$6,A2,Sheet1!$B$1:$B$6)/COUNTIF(Sheet1!$A$1:$A$6,A2)

という式をB2に入れればで出来ます。

２．は配列数式を使います。

=INDEX(Sheet1!$B$1:$B$6,MAX((Sheet1!$A$1:$A$6=A2)*ROW(Sheet1!$A$1:$A$6)))

という式をC2に入れ、数式バーに入れ、CtrlキーとShiftキーを押しながら、Enterキーで確定します。

１．２．いづれも、下へそのままコピーすれば、残りも出ます。

---------------------------------------------------------------------------

１．は解決したのですが、２がまだ解決できていません。２の回答のようにすると「TRUE]と表示されてしまいます。

どなたか回答よろしくお願いいたします。

回答No.5

kagakusuki

今仮に、Ｓｈｅｅｔ１、Ｓｈｅｅｔ２ともに、「商品名」と入力されているセルは、A1セルであるものとします。
　又、Ｓｈｅｅｔ3のA列を作業列として使用するものとします。

　まず、Ｓｈｅｅｔ3のA1セルに次の数式を入力して下さい。

=IF(AND(INDEX(Sheet1!$A:$A,ROW())<>"",COUNTIF(Sheet1!$A$1:INDEX(Sheet1!$A:$A,ROW()),INDEX(Sheet1!$A:$A,ROW()))=COUNTIF(Sheet1!$A:$A,INDEX(Sheet1!$A:$A,ROW()))),ROW(),"")

　次に、Ｓｈｅｅｔ3のA1セルをコピーして、Ｓｈｅｅｔ3のA2以下に貼り付けて下さい。

　次に、Ｓｈｅｅｔ2のA1セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>COUNT(Sheet3!$A:$A),"",INDEX(Sheet1!$A:$A,SMALL(Sheet3!$A:$A,ROWS($1:1))))

　次に、Ｓｈｅｅｔ2のB1セルに「平均額」、C1セルに「価格（現）」と入力して下さい。
　次に、Ｓｈｅｅｔ2のB2セルに次の数式を入力して下さい。

=IF($A2="","",SUMIF(Sheet1!$A:$A,$A2,Sheet1!$B:$B)/COUNTIF(Sheet1!$A:$A,$A2))

　次に、Ｓｈｅｅｔ2のC2セルに次の数式を入力して下さい。

=IF($A2="","",INDEX(Sheet1!$B:$B,SMALL(Sheet3!$A:$A,ROWS($1:2))))

　次に、Ｓｈｅｅｔ2のA1セルをコピーして、Ｓｈｅｅｔ2のA2セルに貼り付けて下さい。

　次に、Ｓｈｅｅｔ2のA2～C2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　これで、仕入価格の商品別平均額と、商品別最新仕入価格が、商品名も含めて、Ｓｈｅｅｔ2の表に、自動的に表示されます。

回答No.4

某HN クロメート(Chromate)（@CoalTar）

Sheet2!C2セルに
=INDEX(Sheet1!B:B,MAX(INDEX((A2=Sheet1!$A$2:$A$6)*ROW(Sheet1!$A$2:$A$6),)))
でもいいかも。でも数式の意味が分からないと思うので

Sheet1!D2セルに
=IF(ISNA(MATCH(A2,D3:D$3000,0)),A2,"")
下へオートフィル と作業列を作成すればよいと思います。

お礼

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

まだ奮闘中です..

エクセルの画像をアップしたいので新しく質問を立てたいと思います。

回答No.3

mshr1962

ちゃんと計算できますよ。
数式入力時にEnterではなくShift+Ctrl+Enterで配列数式化で

=INDEX(Sheet1!$B$1:$B$6,MAX((Sheet1!$A$1:$A$6=A2)*ROW(Sheet1!$A$1:$A$6)))

の表示が

{=INDEX(Sheet1!$B$1:$B$6,MAX((Sheet1!$A$1:$A$6=A2)*ROW(Sheet1!$A$1:$A$6)))}

になってますか？

上記以外だと配列数式の代わりにSUMPRODUCT関数を使って
=INDEX(Sheet1!$B$1:$B$6,SUMPRODUCT(MAX((Sheet1!$A$1:$A$6=Sheet2!$A2)*ROW(Sheet1!$A$1:$A$6))))
でも同様の結果になります。

お礼

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

まだ奮闘中です。
成功したら報告いたします。

回答No.2

KURUMITO

配列数式などを使うことで計算に負担がかかります。作業列を使って対応するのがよいでしょう。
またこれまでの提案された方法では商品名が自動で表示されるわけではありません。

シート1のC2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,INT(MAX(C$1:C1))+1+1/100000,INT(INDEX(C$1:C1,MATCH(A2,A:A,0)))+COUNTIF(A$2:A2,A2)/100000))

次にシート2ですがA1セルには商品名、B1セルには平均額、C1セルには価格(現)とそれぞれ項目名が有るとしてA2セルには次の式を入力してC2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(COLUMN(A1)=1,IF(COUNTIF(Sheet1!$C:$C,ROW(A1)+0.00001)=0,"",INDEX(Sheet1!$A:$A,MATCH(ROW(A1)+0.00001,Sheet1!$C:$C,0))),IF(COLUMN(A1)=2,IF($A2="","",SUMIF(Sheet1!$A:$A,$A2,Sheet1!$B:$B)/COUNTIF(Sheet1!$A:$A,$A2)),IF(COLUMN(A1)=3,IF($A2="","",INDEX(Sheet1!$B:$B,MATCH(INT(INDEX(Sheet1!$C:$C,MATCH($A2,Sheet1!$A:$A,0)))+COUNTIF(Sheet1!$A:$A,$A2)/100000,Sheet1!$C:$C,0))),"")))

これで商品名も自動で追加表示されるようになりますね。

お礼

回答どうもありがとうございます。
まだ、奮闘中です..

成功したら報告します。

回答No.1

keithin

寄せられた回答は合ってます。

＞２の回答のようにすると「TRUE]と表示されてしまいます。

ということは，寄せられた回答「のように」できてないってことです。
再度数式を見直してください。

それでも解明できないときは，「あなたが実際にエクセルに投入して間違った答えが出た数式」を，あなたのエクセルからコピーして掲示しご相談を書くようにしてみてください。

お礼

回答ありがとうございます。
再度チャレンジしてみます。
成功したら報告いたします。