Excel 表のランキングについて。

質問

図のような表があります。
その右側に個数が多い順にベスト3を表示したいのですが、どうしたら良いでしょうか。
番号と個数をベスト3順に表示したいのです。
完売時間の方は、番号と時間が早い順に表示したいです。
アドバイスよろしくお願いしますm(_ _ )m

回答No.7

MarcoRossiItaly · ベストアンサー

No.1です。何度もすみません。次の理由により、やや修正します。

シートの2行目に「番号」などの文字列が入力されているときは、No.1の式ではエラーが出ます。そのため「b:c」→「B$3:C$9999」などと、3～9999行目となるように範囲を修正しました。また、9999行目の近辺でも「1秒という時間差」（シリアル値1.16E-05）に対応できるようにするため、「10^8」→「10^10」としました。

G3　=index(B$3:C$9999,match(h3,index(C$3:C$9999-row(C$3:C$9999)/10^10,),),1)
H3　=large(index(C$3:C$9999-row(C$3:C$9999)/10^10,),row()-2)
G8　=index(B$3:C$9999,match(h8,index(E$3:E$9999+row(E$3:E$9999)/10^10+(E$3:E$9999="")*9^9,),),1)
H8　=small(index(E$3:E$9999+row(E$3:E$9999)/10^10+(E$3:E$9999="")*9^9,),row()-7)

これで、同順位もきちんと差別化しながら、作業列なしでランキングできているかと思います。

まあNo.1でも言ったとおり、実用的なExcelの使い方としては、オートフィルタや、RANK関数による作業列のほうがお勧めですが。数式を書くだけでかなりの労力がいるので…。

上の数式を利用する上での注意点も書いておきます。

●G列はH列を参照しているので、H列にも入力しないとG列にエラーが出ます。
●順位をH列の「row()-2」と「row()-7」という部分で計算しています。そのためH列のセルの位置を動かす場合は、「2」や「7」を、適切な値に修正してください。「row()」は、これが入力されているセルの行番号を返す式です。
●基本ですが、E列とH列の書式を同じにしておかないと、見かけ上は誤った値が表示されます。正しく設定してください。

お礼

素晴らしい！綺麗にできました。
他の方の回答も丁寧でしたが、図付きでわかりやすかったのでBAにさせてもらいますm(_ _ )m

回答No.6

kagakusuki

今仮に、元データの表とベスト３の表がSheet1にあるものとし、Sheet２のA列とB列を作業列として使用するものとします。

　まず、Sheet2のA3セルに次の数式を入力して下さい。

=IF(ISNUMBER(INDEX(Sheet1!$C:$C,ROW())),COUNTIF(Sheet1!$C:$C,">"&INDEX(Sheet1!$C:$C,ROW()))+COUNTIF(Sheet1!$C$2:INDEX(Sheet1!$C:$C,ROW()),INDEX(Sheet1!$C:$C,ROW())),"")

　次に、Sheet2のB3セルに次の数式を入力して下さい。

=IF(ISNUMBER(INDEX(Sheet1!$E:$E,ROW())),COUNTIF(Sheet1!$E:$E,"<"&INDEX(Sheet1!$E:$E,ROW()))+COUNTIF(Sheet1!$E$2:INDEX(Sheet1!$E:$E,ROW()),INDEX(Sheet1!$E:$E,ROW())),"")

　そして、Sheet2のA3～B3の範囲をコピーして、同じ列の４行目以下に貼り付けて下さい。

　次に、Sheet1のH3セルに次の数式を入力して下さい。

=IF(ROWS($8:9)>COUNT($E:$E),"",INDEX($B:$B,MATCH(ROWS($8:9),Sheet2!$B:$B,0)))

　次に、Sheet1のG3セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNT($C:$C),"",INDEX($B:$B,MATCH(ROWS($2:2),Sheet2!$A:$A,0)))

　次に、Sheet1のG3～H3の範囲をコピーして、Sheet1のG4～H5の範囲に貼り付けて下さい。

　次に、Sheet1のH8セルに次の数式を入力して下さい。

=IF(ROWS($8:8)>COUNT($E:$E),"",SMALL($E:$E,ROWS($8:8)))

　次に、Sheet1のG8セルに次の数式を入力して下さい。

=IF(ROWS($8:8)>COUNT($E:$E),"",INDEX($B:$B,MATCH(ROWS($8:8),Sheet2!$B:$B,0)))

　次に、Sheet1のG8セルの書式設定をSheet1のE3セルと同じ設定として下さい。

　次に、Sheet1のG8～H8の範囲をコピーして、Sheet1のG8～H8の範囲に貼り付けて下さい。

　これで個数と完売時間のベスト３が、番号順に自動的に表示される様になります。

　尚、上記の関数は、元データの表を編集する際に、セルの切取りや挿入、削除等を行って、セルが上下方向にずれた場合であっても、正常に動作する様になっております。

回答No.5

tom04

No.4です！
たびたびごめんなさい。
投稿後に気づいたのですが、「個数ベスト3」に同順位があった場合
番号の大きな行が先に表示されてしまいますので、F3セルの数式を訂正してください。

F3セルは
=INDEX(A:A,MATCH(LARGE($I:$I,4-ROW(A1)),$I:$I,0))
として列方向・行方向にオートフィルでコピーしてください。

何度も失礼しました。m（_ _）m

回答No.4

tom04

こんにちは！
すでに回答は出ていますが・・・

万一同順位があった場合に対応できる一例です。

↓の画像のように作業用の列を2列設けています。
作業列1のI3セルに
=IF(B3="","",COUNTIF(B:B,"<"&B3)+COUNTIF($B$3:B3,B3))

作業列2のJ3セルに
=IF(D3="","",COUNTIF(D:D,"<"&D3)+COUNTIF($D$3:D3,D3))
という数式を入れ、I3・J3セルを範囲指定　→　J3セルのフィルハンドルで
オートフィルで下へずぃ～～～っとコピーしておきます。

結果のF3セルに
=INDEX(A:A,MATCH(LARGE($I:$I,ROW(A1)),$I:$I,0))
という数式を入れ、列方向と行方向にオートフィルでコピー！

F8セルには
=INDEX($A:$D,MATCH(SMALL($J:$J,ROW(A1)),$J:$J,0),(COLUMN(A1)-1)*3+1)
という数式を入れ、列方向と行方向にオートフィルでコピー！
（時間の表示形式は好みで！）

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

※　同順位がなければ、当然作業列を設ける必要はなく、もっと簡単な数式で大丈夫です。

参考になりますかね？m（_ _）m

回答No.3

KURUMITO

回答No2です。
G8セルへの入力の式が一部間違ってしまいましt。次の式にしてください。

=INDEX($B:$E,MATCH(SMALL($E:$E,ROW(A1)),$E:$E,0),IF(COLUMN(A1)=1,1,IF(COLUMN(A1)=2,4)))

回答No.2

KURUMITO

G3セルには次の式を入力してH3セルまでドラッグコピーしたのちに3行下まで下方にドラッグコピーします。

=INDEX($B:$C,MATCH(LARGE($C:$C,ROW(A1)),$C:$C,0),COLUMN(A1))

G8セルには次の式を入力してH8セルまで横にドラッグコピーしたのちに3行下目で下方にドラッグコピーします。

=INDEX($B:$E,MATCH(SMALL($E:$E,ROW(B1)),$E:$E,0),IF(COLUMN(B1)=1,1,IF(COLUMN(B1)=2,4)))

なお、H8セルから下方には時間のシリアル値が表示されますので「セルの表示形式」から時間の表示に変えます。

回答No.1

MarcoRossiItaly

G3　=index(b:c,match(h3,index(c:c-row(c:c)/10^8,),),1)
H3　=large(index(c:c-row(c:c)/10^8,),row()-2)
G8　=index(b:c,match(h8,index(e:e+row(e:e)/10^8+(e:e="")*9^9,),),1)
H8　=small(index(e:e+row(e:e)/10^8+(e:e="")*9^9,),row()-7)

全く実用的ではありませんが、一応、上のとおり不可能なことはないです。素直にオートフィルタや、RANK関数による作業列を用意する方法をお勧めしますけどね。