エクセルでランク順に別シートへ並べ替えるには

文字探しのゲームとして、エクセルで任意の言葉（ここでは野菜名）をランダムに並び替えて
別シートに表示させたいと考えています。

sheet1に記載された野菜名を乱数（B列）とランク（C列）を使い、
sheet2のA1から右に、それぞれ1位、2位のところへ値を入れたいと考えています。
画像の場合、sheet2のA1にはアスパラガス、B1にはイチゴ、となります。
各列は11まであり、次の行はその続きとなります。
（1行目が1位から11位まで、2行目は12位から22位まで）
sheet2の1位、2位の場所はわかりやすいように記載しただけで、実際は数式で構いません。


ランクで毎回並びが変わってしまいますが、文字探しが目的のため、その点は問題ありません。

vlookup関数でと考えておりましたが、どうも違うように思えます。
作成のヒントをいただければと思います。
よろしくお願いいたします。

質問者からの補足コメント

• 回答ありがとうございます。
  xlookup非対応のバージョンでした。
  最初に断りを入れず申し訳ありません。

  No.1の回答に寄せられた補足コメントです。 補足日時：2021/10/05 07:44

• 皆様回答ありがとうございます。
  たくさん回答があり、正直理解するのに時間がかかっています。
  もう少しお時間をください。
  
  ランキングの件については、chonamiさんがいわれているように、
  ランダムに並び替えたいために乱数からのランキングということで理解していただければと思います。

  No.12の回答に寄せられた補足コメントです。 補足日時：2021/10/06 20:59

No.4 ベストアンサー 回答者： chonami 回答日時： 2021/10/05 08:42

A1=INDEX(sheet1!$A:$A,MATCH(COLUMN(),sheet1!$C:$C,0))

A6=INDEX(sheet1!$A:$A,MATCH(COLUMN()+11,sheet1!$C:$C,0))

それぞれ横にコピーではどうでしょうか？
質問のレイアウトに限ります。実際は違う場合は適宜変更してください。
ヒントということなので考え方の参考に。
(直打ちしたので何か間違ってたらすみません)

1位、2位のセルを残してその下に名前を出すとかなら1つの式でいけますが。
(1・3行目が項目、2・4行目に名前みたいに)

この回答へのお礼

こちらでまとめてお礼させていただきます。
皆様、いろいろと教えていただきありがとうございました。
質問の内容が不十分で、皆様に頭を悩ませてしまい申し訳ないです。

こちらの式が自分にはわかりやすく合っていましたので、
こちらを参考にして作成を頑張ってみたいと思います。

お礼日時：2021/10/14 05:57

No.12 回答者： chonami 回答日時： 2021/10/06 11:53

別に、本当にランキングが出したいんじゃないんじゃないですかね？

ランダムに任意の個数の文字列を抜き出したくて乱数を使ったからランキングを使って抜き出すだけでしょう。
（つまり順位じゃなくてナンバリングすることが重要）
別におかしな使い方ではないですし、意図を知るか知らないかで回答の内容が変わるとも思えませんが。
（知らないと回答に影響するような質問もたまにありますけど）

No.11 回答者： goomania 回答日時： 2021/10/06 10:15

No.10です。

前回回答の数式の中の「$A$1:$A$132」「$B$1:$B$132」といった範囲はご質問者が前回ご質問で示されたデータの範囲です。
今回のご質問で範囲が異なるなら適宜調整してください。

No.10 回答者： goomania 回答日時： 2021/10/06 09:55

No.7さんのがご指摘されてておられますが、

>どうせランダムなんだから何位がどこに表示されようが
>重複さえしなければどうでもいいのではありませんか？
という疑問がわいています。
そもそも、1位から22位までを表示させることで何を実現しようとしているのでしょうか?
疑問だらけではありますが、sheet1のB列に乱数を発生させて順位を決定し、その順番に別シートに表示するというお考えは理解できます。
ただ、乱数を発生させた時点で、昇順、降順はあるにしろ順番は決まるので、その順番に別シートへ転記してやればよいわけですから、必ずしもC列のRANK関数は必要でない気がしてきました。
そこで以下のような数式を考えてみました。この数式ではsheet1のC列は使用していません。実質的にC列は不要になりますが、表示結果の検証に役立ちます。

=IFERROR(INDEX(Sheet1!$A$1:$A$132,MATCH(LARGE(Sheet1!$B$1:$B$132,(COLUMN()+(ROW()-1)*11)/(COLUMN()<12)),Sheet1!$B$1:$B$132,0))&"","")

sheet2のA1に上記数式を記述し、右方向、下方向へコピーしてください。
なお、K列より右にコピーしても空白が表示されるだけです。
また、3行目以降コピーした場合には23位以降のものが順位に沿って表示されますので、不要なら削除してください。

No.9 回答者： mike_g 回答日時： 2021/10/05 09:42

添付図参照(Excel 2019)

￣￣￣￣￣￣￣￣￣￣￣↓↓↓↓↓↓↓↓↓↓↓↓↓

No.8 回答者： d-q-t-p 回答日時： 2021/10/05 09:31

銀鱗さんへ

> 同じ数値がある場合、RANK関数は同じ順位と認識し、その次の順位
> が無いことになります。
RANKは疑似ランダム関数であり漸化式で値が割り出されています。
つまり完全な乱数ではない代わりに重複もしません。

よって「同じ数値がある場合」を考慮する必要はないかと思います。
下手なことをするとかえって偏りが出ます。

No.7 回答者： d-q-t-p 回答日時： 2021/10/05 09:12

どうせランダムなんだから何位がどこに表示されようが重複さえしな

ければどうでもいいのではありませんか？

Sheet1の C1の数式を
=IFERROR(INDEX(A:A,RANK(B1,B:B)),"")
下方向にコピー

としておけば Sheet2は順番に参照するだけでいいと思いますけど。

Sheet2の A1
=IFERROR(INDEX(Sheet1!$C:$C,COLUMN()+(ROW()-1)*11)&"","")
K2までコピー

> xlookup非対応のバージョンでした。
Microsoft365ならそもそも

=LET(_rng,Sheet1!A:A,_flt,FILTER(_rng,_rng<>""),_cnt,COUNTA(_flt),_srt,SORTBY(_flt,RANDARRAY(SEQUENCE(_cnt))),IFERROR(INDEX(_srt,SEQUENCE(2,11)),""))
でスピル表示されます。

No.6 回答者： 銀鱗 回答日時： 2021/10/05 09:12

XLOOKUP関数が使えないなら、INDEX関数とMATCH関数の組み合わせにRANK関数を入れれば良いじゃない。

・・・余談（のほうが長いのは愛嬌）・・・

同じ数値がある場合、RANK関数は同じ順位と認識し、その次の順位が無いことになります。
A列の
　3行目に100.000
　4行目に123.456
　5行目に123.456
　6行目に200.000
があるとすると、
　RANK(A3,＄A$3:$A$6,0)
　3行目は「４」
　4行目は「２」
　5行目は「２」
　6行目は「１」
となり「３」が存在しない事になります。
これを回避するために、行番号を数値に加える工夫をすると良いでしょう。
（行番号はROW関数で取得できます）
その際、元の数字に影響しない小さい数値に加工すると良い。
すなわち、
　3行目には0.0003
　4行目には0.0004
　5行目には0.0005
　6行目には0.0006
を足してやれば良いという事になります。
1列作業用の列を設けると分かりやすい。
B列に
　A3+ROW()*0.0001
などと加工した値を作り、作ったB列を検索対象とすれば良い。
実際には
　0.000 04
　0.000 05
など、この例では99行まで対応できる数値を足すようにすると良いでしょう。
ただし、有効桁が15桁を超えないよう注意してください。
Excelをはじめとするスプレッドシート式の表計算ソフトでは15桁までの計算しかできません。
　123456789012345678
と18桁の数値を入力しても
　123456789012345000
と、下3桁は無かったことにされます。
これはスプレッドシート式の表計算ソフトの仕様です。

No.5 回答者： mygoonickname 回答日時： 2021/10/05 08:47

No.2の者です。

一部、関数の指定が間違っていたので、修正です。
誤り：
=VLOOKUP(COLUMN()+(ROW()-1)*10,Sheet1!$A$1:$C$20,3,True)

正しく：
=VLOOKUP(COLUMN()+(ROW()-1)*10,Sheet1!$A$1:$B$20,2,True)

No.3 回答者： トモクンアヤチャン 回答日時： 2021/10/05 08:03

№1です。

すみませんでした。
VLOOKUPは、参照値が参照範囲の一番左にないといけないので、A列とC列を入れ替えるか、D列（Ｃ列の右）に＝A1のような同値を用意する必要があります。
№2さんの解答でいいと思います。