エクセルの関数でベスト３の名前を表示させたい

ご教授お願いします。

添付の表のようにベスト3まで店名を表示したいのですが、過去の質問で以下の数式を使うと表示をさせることは可能でした。

＊＊＊＊＊過去の質問の答え＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
Ｆ２に
=OFFSET($A$1,0,MATCH(LARGE($B2:$D2,COLUMN()-COLUMN($E2)),$B2:$E2,0),1,1)
と入力して確定（エンター）
その後、このＦ２をＦ２：Ｈ４にコピーしてみてください。
＊＊＊＊＊過去の質問の答え＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

ただし表の上のぶどうのように順位が同じになった時に、上位の店舗がダブって出てしまいます。

これを強制的に順位をつけ、下の表のようにしたい、数量が０だった時にー（バー）を表示させたいのですがそういったことは可能ですか？

ご教授お願いします。

No.1 回答者： yuji3690 回答日時： 2017/01/26 17:21

A店とB店のどちらを上の順位にするかは特に指定なしですか？

店は３つだけですか？それとも簡略化させているだけですか？

セルの中身が長くなってしまうので、できるならIJK列あたりに、各順位の数値を一旦表示させておきたいところです。
とりあえずF2とG2を書きますね。F列はF2をG列とH列はG2をコピーしてください。

F2=IF(MAX(B2:D2)=0,"-",INDIRECT(ADDRESS(1,MATCH(LARGE($B2:$D2,COLUMN()-COLUMN($E1)),$A2:$D2,0))))

G2=IF(LARGE($B2:$D2,COLUMN()-COLUMN($E1))=0,"-",IF(LARGE($B2:$D2,COLUMN()-COLUMN($E1))=LARGE($B2:$D2,COLUMN()-COLUMN($E1)-1),INDIRECT(ADDRESS(1,SUMPRODUCT(($B2:$D2=LARGE($B2:$D2,COLUMN()-COLUMN($E1)))*1,(COUNTIF(INDIRECT("$B"&ROW()&":"&ADDRESS(ROW($B2:$D2),COLUMN($B2:$D2))),LARGE($B2:$D2,COLUMN()-COLUMN($E1)))+COUNTIF($B2:$D2,">"&LARGE($B2:$D2,COLUMN()-COLUMN($E1)))=COLUMN()-COLUMN($E1))*1,COLUMN($B2:$D2)))),INDIRECT(ADDRESS(1,MATCH(LARGE($B2:$D2,COLUMN()-COLUMN($E1)),$A2:$D2,0)))))

試行錯誤の末です…その順位の数字を何度も使っているので、別セルに表示できればもっと短くできます。
ご参考まで

No.2 回答者： CoalTar 回答日時： 2017/01/26 17:44

それぞれの店に左から 0、0.1、0.2を引いて無理やり順位を付けた

F2セルに
=IF(LARGE($B2:$D2,F$1)=0,"-",INDEX($B$1:$D$1,MATCH(LARGE($B2:$D2-{0,0.1,0.2},F$1),$B2:$D2-{0,0.1,0.2},0)))
[Ctrl]+[Shift]+[Enter]配列数式、{}で囲まれる。

No.3 回答者： 銀鱗 回答日時： 2017/01/26 18:06

強制的にランクを付けるには、

この場合、横方向なので、列番号をランク付けの値に足してやります。（この場合、引きますけど…）
ただし、目立たないよう1000分の1にした値を足します。
そうすれば、見た目は同じランクでも、実は小数点以下の数字があるため強制的にランクが決定されます。

　=OFFSET($A$1,0,MATCH(LARGE($B2:$D2　　　　　　　　　　　　　,COLUMN()-COLUMN($E2)),$B2:$E2　　　　　　　　　　　　　　,0),1,1)
　↓
　=OFFSET($A$1,0,MATCH(LARGE($B2:$D2-COLUMN($B2:$D2)/1000,COLUMN()-COLUMN($E2)),($B2:$D2)-COLUMN($B2:$D2)/1000,0),1,1)

ただし、配列数式にしていますので、確定はCtrlキーとShiftキーを押しながらEnterキーを押してください。（Ctrl+Shift+Enter)

これを同位なら後ろの店を優先する場合は、1000分の1にしている部分
　-COLUMN($B2:$D2)/1000
を引くのではなく、ホントに足してください。

※一部にB列からE列を参照するような部分があったので、B列からD列までに修正しています。それ、明らかに誤りです。

No.4 回答者： tom04 回答日時： 2017/01/26 18:21

こんばんは！

一案です。
↓の画像のように別の列に作業用の表を作成してみてはどうでしょうか？

画像ではJ2セルに
=IF(B2>0,COUNTIF($B2:$D2,">"&B2)+COUNTIF($B2:B2,B2),"")
という数式を入れ、右へデータ分フィル＆コピー　→　そのまま下へずぃ～～～！っとコピーしています。

そしてF2セルに
=IFERROR(INDEX($B$1:$D$1,,MATCH(SMALL($J2:$L2,COLUMN(A1)),$J2:$L2,0)),"-")
という数式を入れ、列・行方向にフィル＆コピー！

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

この回答へのお礼

ありがとうございます。店舗がA~Cの3店舗分で出していましたが16店舗分になるとまた数式が変わっちゃいますか？

お礼日時：2017/01/27 15:42

No.5 ベストアンサー 回答者： tom04 回答日時： 2017/01/27 16:33

No.4です。

＞16店舗分になるとまた数式が変わっちゃいますか？

元データの配置はお示しの画像通りでよいのですね？
（画像が小さくて見えにくかったらごめんなさい）

元データはSheet1にあり、16店舗となると1位～16位までの表示は↓の画像のようにR列～AG列まで！とします。
やり方はまったく同じなのですが、作業用に別シートを使った方が良いと思います。
画像ではSheet2を作業用として使っています。
Sheet2のB2セルに
=IF(Sheet1!B2>0,COUNTIF(Sheet1!$B2:$Q2,">"&Sheet1!B2)+COUNTIF(Sheet1!$B2:Sheet1!B2,Sheet1!B2),"")
という数式を入れ店舗数（16列分）右へコピー　→　そのまま下へコピーしておきます。

そしてSheet1のR2セルに
=IF($A2="","",IFERROR(INDEX($B$1:$Q$1,,MATCH(SMALL(Sheet2!$B2:$Q2,COLUMN(A1)),Sheet2!$B2:$Q2,0)),"-"))
という数式を入れ、列・行方向にコピーすると
画像のような感じになります。

※　Sheet2のA列品名は不要ですが、
判りやすくするため入れています。m(_ _)m