Excel 関数

質問

Excelの関数で教えていただきたいことがあります。

A　　　　　　　B　　　　　　C　
店舗コード　売上金額　商品名
1111　　　　　1,000　　　　AAA
1111　　　　　2,000　　　　BBB
1112　　　　　1,000　　　　CCC
1111　　　　　2,000　　　　DDD

上記のように、店舗別X商品別の売上金額一覧があります。こちらから、店舗別のTop10ランキングを作成したいです。金額のTop10は集計表の店舗コードをKeyとしてLarge関数で引っ張ってきていますが、商品名も同様に引っ張ってきたいです。しかしながら、参照テーブルでは同額のものがあるため、indexとmatchの入れ子, offset, indirect関数、indexとsumproductの入れ子でもやり方が正しくないようでエラーになってしまいます。

集計表では、

商品名　　金額
で並べており、金額のところに店舗コードをKeyとしたLarge関数が入っています。こちらのLarge関数で指定している値と同じ行数の別の列数(B列を引っ張りたい)を返してくれればいいのですが、正しいやり方がわかりません。。。

どなたかご存じないでしょうか?　よろしくお願いします!

回答No.1

tom04

こんばんは！
一例です。

作業列を設ける方法が一番簡単だと思います。
↓の画像で左側（元データ）がSheet1・右側がSheet2として
Sheet2に表示するとします。
Sheet2のA1セルに　Key　のコードを入力すると

A3以降の表に表示されるようにしてみました。
尚、Sheet2の3行目項目名はSheet1の項目名と同じにしておいてください。

Sheet1の作業列D2セルに
=IF(OR(Sheet2!A$1="",A2<>Sheet2!A$1),"",COUNTIF(B:B,">"&B2)+COUNTIF(B$2:B2,B2))
という数式を入れオートフィルでずぃ～～～！っとしっかり下へコピーしておきます。

そしてSheet2のA4セルに
=IF(COUNT(Sheet1!$D:$D)<ROW(A1),"",INDEX(Sheet1!$A:$C,MATCH(SMALL(Sheet1!$D:$D,ROW(A1)),Sheet1!$D:$D,0),MATCH(A$3,Sheet1!$A$1:$C$1,0)))
という数式を入れ列・行方向にオートフィルでコピー！

これで画像のような感じになります。

※　金額が同じの場合は上位の行のデータが上に表示されます。
※　作業列が目障りであれば非表示にするか、
遠く離れた列に作業列を設けます。m(_ _)m