エクセルの順位抽出について

質問

Sheet1のA列にコード、Ｂ列に商品名、Ｃ列に分類　Ｄ列に売上数、
Ｅ列に売上金額を入力した一覧表があります。
A_1：1000 B_1：牛肉 C_1：食品 D_1：20 E_1：3000
A_2：1050 B_2：お茶 C_2：飲料 D_2：18 E_2：3010
A_3：2000 B_3：鉛筆 C_3：文具 D_3：12 E_3： 900
A_4：2050 B_4：お米 C_4：食品 D_4：12 E_4：9010
A_5：3000 B_5：牛乳 C_5：飲料 D_5：25 E_5：2000
A_6：3050 B_6：定規 C_6：文具 D_6：28 E_6： 700
中略
A_300：10000 B_300：肉まん C_300：食品 D_300：38 E_300：9000

１.食品対象で売上金額の上位２０のコードをSheet2のA列に表示
２.全商品対象で売上金額の上位２０のコードをSheet3のA列に表示
上記の様な抽出をしたいのですがどの様な関数を使用すれば良いでしょうか？
（オートフィルタを使用してのコピペという手作業をなくすためにSheet1の内容が更新されれば自動でSheet2,3の内容も更新されている
のが希望です）

よろしくお願いします。

回答No.1

tom04 · ベストアンサー

こんばんは！
参考になるかどうか判りませんが・・・
一例です。

↓の画像で説明させていただきます。
Sheet1にSheet2とSheet3に抽出するための作業用の列を使っています。
Sheet1の作業列F2セルに
=IF(C2=$F$1,RANK(E2,$E$2:$E$1000)*1000+ROW(A1),"")
（F1セルに分類名を入れれば他の分類も表示できます）

G2セルに
=IF(A2="","",RANK(E2,$E$2:$E$1000)*1000+ROW(A1))

としれ、F2・G2セルを範囲指定し、G2セルのフィルハンドルで下へずぃ～～～！っとコピーします。

そしてSheet2のA2セルに
=INDEX(Sheet1!$A$2:$A$1000,MOD(SMALL(Sheet1!$F$2:$F$1000,ROW(A1)),1000))
として21行目までオートフィルでコピー

同様にSheet3のA2セルに
=INDEX(Sheet1!$A$2:$A$1000,MOD(SMALL(Sheet1!$G$2:$G$1000,ROW(A1)),1000))
としれ21行目までコピー

画像ではデータが少ないためにエラー表示になっていますが
データが多いみたいなのでたぶんエラーは表示されないと思います。

尚、数式はSheet1の1000行目まで対応できるようにしていますが、
データ量が1000行を超えるようであれば数式を少し変更する必要があります。

以上、長々と書きましたが
お役に立ちますかね？m(__)m

回答No.2

KURUMITO

売上金額が同じ額の場合でもできるようにするためにはやや面倒になりますが作業列を使って対応します。
なお、1行目は項目名があり、データは2行目から下方にあるとします。
シート2のＡ1セルには分類を入れることにして、例えば食品と入力しておきます。
そこでシート1のＧ2セルには次の式を入力します。
=IF(C2=Sheet2!A$1,E2,"")
次にＨ2セルには次の式を入力します。

=IF(G2="","",IF(RANK(G2,G:G)<=20,RANK(G2,G:G)+COUNTIF(G$2:G2,G2)/1000,""))

Ｉ2セルには次の式を入力します。

=IF(E2="","",IF(RANK(E2,E:E)<=20,RANK(E2,E:E)+COUNTIF(E$2:E2,E2)/1000,""))

Ｇ2セルからＩ2セルまでを選択してそれらの式を下方にオートフィルドラッグします。

次にシート2ではＡ1セルに分類を入力し、2行名にはシート1と同じ項目名を入力します。
Ａ3セルには次の式を入力し右方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。

=IF(OR(ROW(A1)>COUNT(Sheet1!$H:$H),COLUMN(A1)>5),"",INDEX(Sheet1!$A:$E,MATCH(SMALL(Sheet1!$H:$H,ROW(A1)),Sheet1!$H:$H,0),COLUMN(A1)))

これでシート2には分類に応じた売上上位20位までの行が額の値の降順で表示されます。

シート3はすべてのデータの上位20位までですので1行目には項目名を入力し、
Ａ2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。

=IF(OR(ROW(A1)>COUNT(Sheet1!$I:$I),COLUMN(A1)>5),"",INDEX(Sheet1!$A:$E,MATCH(SMALL(Sheet1!$I:$I,ROW(A1)),Sheet1!$I:$I,0),COLUMN(A1)))