[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複数の条件で順位を決めたいのです。』（エクセル素人親父）
皆様
いつも大変お世話になっております。
ありがとうございます。
早速、皆様のお知恵をお借りしたく存じます。
複数(2つ)条件で順位を決めたいのです。
複数条件は・・・
条件１：勝ち点
条件２：勝率
です。
勝ち点が同じ人が複数発生する状況なので、同勝ち点の人を勝率で順位を決めたいのです。
更に順位をオートフィルではなく自動的に1位から順に並べたい、且つ棄権者を省いて1位から順に並べたいのです。
A列：エントリーナンバー
B列：棄権者のエントリナンバー
C列：選手名
D列：勝ち点
E列：勝率
F列：順位(１位から下へ)
のような感じで考えております。
皆様、どうぞお知恵をお貸しください。
よろしくお願いいたします。
＜ 使用 Excel：Excel2003、使用 OS：Windows7 ＞
（γ） 2022/01/08(土) 09:34
同率順位が生じないようにするには 「Rank関数 重み」で 自動的(?)に並べ替えるには 「Vlookup 並べ替え」又は「Index Match 並べ替え」 でWeb検索すると参考になるサイトが見つかります。 （参考） 2022/01/08(土) 10:11
先ずはオートフィルが間違えでした。すみません。
手動で並べ替えるのではなく自動で並べ替えられるようにできないかぁ。というのが望みです。
そして次の方のご回答ですが、参考にさせていただきます。ありがとうございます。
ただ、自分なりに検索をしてみたのですが、私のニックネームにあるようにド素人故に出来れば数式をご教示頂けますと助かります。
申し訳ありません。よろしくお願いいたします。
（エクセル素人親父） 2022/01/08(土) 11:41
４例程度のサンプルの提示が出来ないですか？（結果も含む)
実際は、何人くらいまで想定しているんですか？
（半平太） 2022/01/08(土) 17:00
2 2 2 花子 6 0.33
3 3 昭 18 0.66 １
4 4 正一 15 0.66 3
5 5 順子 12 0.66 4
半平太様
おはようございます。
すみません。サンプルとは如何なるものなのか分からなかったので上記のようなものを書いてみましたが間違っていたらすみません。
A列〜E列までは私が入力します。
F列にD列の勝ち点が同じ人同士(花子と昭)に更に順位を付けるためにE列の勝率が高い方が1位になるようにF列に計算式を入れたいのです。
参加人数は60名前後です。
よろしくお願い致します。
（エクセル素人） 2022/01/09(日) 09:39
サンプルからすると、 「1位から順に並べたい」ではなく、単に 「順位を求めたい」ですね？
99名まで可能性があるとして、
F2セル =IF(OR(A2="",B2<>""),"",MATCH(D2+E2,INDEX(LARGE((B$2:B$100="")*(D$2:D$100+E$2:E$100),ROW(Z$1:Z$99)),0),0)) 下にコピー
<結果図> 行 _________A_________ ____________B____________ ___C___ ___D___ __E__ _________F_________ 1 エントリーナンバー 棄権者のエントリナンバー 選手名 勝ち点 勝率 順位(１位から下へ) 2 1 太郎 18 0.33 2 3 2 2 花子 6 0.33 4 3 昭 18 0.66 1 5 4 正一 15 0.66 3 6 5 順子 12 0.66 4
（半平太） 2022/01/09(日) 15:01
お世話になります。
ご教示本当にありがとうございます。
すみません。順位はできれば上から1位、２位、 ３位、 棄権者（空白） ４位・・・
みたいな感じで並べられるととても助かります。
でも、半平太様にご教示頂いた数式でも十分です。
ありがとうございました。
最後に、質問の仕方が悪く皆さまに二度手間を取らせてしまいましたことをお詫びいたします。
申し訳ございませんでした。
（エクセル素人親父） 2022/01/10(月) 09:46
お世話になります。
度々申し訳御座いません。
やはり作業効率を考えるとF2から下に1位から順にA列、B列、C列、D列、E列が並ぶようになるととても助かります。
何度も申し訳ありません。
ご教示を頂けますと幸いです。
宜しくお願い申し上げます。
尚、現在は出先ですので帰宅次第に上でご教示頂いた数式を活用させて頂きます。
（エクセル素人親父） 2022/01/10(月) 10:10
＞順位はできれば上から1位、２位、 ３位、 棄権者（空白） ４位 ＞F2から下に1位から順にA列、B列、C列、D列、E列が並ぶように
ちょっとイメージがつかめないです。
（半平太） 2022/01/10(月) 16:43
こんにちは。説明が下手で申し訳ありません。
下図の様に順位が自動的に並べられるようになるのが理想です。
よろしくお願いいたします。
行 _________A_________ ____________B____________ ___C___ ___D___ __E__ _________F_________
1 エントリーナンバー 棄権者のエントリナンバー 選手名 勝ち点 勝率 順位(１位から下へ) 2 3 昭 18 0.66 1 3 1 太郎 18 0.33 2 4 4 正一 15 0.66 3 5 5 順子 12 0.66 4 6 2 2 花子 6 0.33 （エクセル素人親父） 2022/01/10(月) 20:33
ふーむ、別シートに表示したいと言う話なのでしょうか？ ※A列からE列は、生データがあるエリアであり、数式を入力することは出来ないのですけど。
もしかして、VBAで実現したいと言う話だったんですか？
（半平太） 2022/01/10(月) 21:16
（エクセル素人親父） 2022/01/11(火) 07:06
<元 シート サンプル> 行 __A__ __B__ __C__ __D__ __E__ 1 番号 棄権 選手 勝点 勝率 2 1 太郎 18 0.33 3 2 2 花子 6 0.33 4 3 昭 18 0.66 5 4 正一 15 0.66 6 5 順子 12 0.66
<順位 シート> １．G2セルに配列数式を入力する (※）配列数式とは、G2:G100を一括選択して、Ctrl+Shift+Enterキー押下で数式の入力を確定する
G2セル =IF(COUNTA(元!A:A)<ROW(),"",1/MOD(LARGE(ROUND((元!D2:D100+元!E2:E100)*1000,0)+1/ROW(),ROW(Z1:Z99)),1))
２．2行目に下式を入力して、A列〜F列の100行目迄コピーする
(1) A2セル =IF($G2="","",INDEX(元!A:A,$G2)) これをC2〜E2セルにコピーする
(2) B2セル =IF($G2="","",IF(INDEX(元!B:B,$G2)="","",A2)) (3) F2セル =IF($G2="","",IF(AND(D1=D2,E1=E2),F1,ROW()-1))
<結果図> 行 __A__ __B__ __C__ __D__ __E__ __F__ ___G___ 1 番号 棄権 選手 勝点 勝率 順位 Helper 2 3 昭 18 0.66 1 4 3 1 太郎 18 0.33 2 2 4 4 正一 15 0.66 3 5 5 5 順子 12 0.66 4 6 6 2 2 花子 6 0.33 5 3
（半平太） 2022/01/11(火) 12:51
棄権者の順位を出してしまった。(-_-;)
(訂正) (3) F2セル =IF(OR($G2="",B2<>""),"",IF(AND(D1=D2,E1=E2),F1,ROW()-1))
（半平太） 2022/01/11(火) 13:06
おはようございます。
できました！
配列数式・・・初めて聞く言葉でしたしCtrl+Shift+Enterキーを押しながらどうやって入力するんだろ？と悩みながら色々試したところ何となくできました！
本当に有難うございました。
（エクセル素人親父） 2022/01/12(水) 09:09
何度も恐れ入ります。
今ふと見たら、私が作成した表では順位が正しく表示されません。
本来であれば 2番の花子が勝ち点18で1位、9番の光一が勝ち点15で2位のはずなのです。
因みにF2〜F11の数式は
=IF(OR(A2="",B2<>""),"",MATCH(D2+E2,INDEX(LARGE((B$2:B$11="")*(D$2:D$11+E$2:E$11),ROW(Z$1:Z$9)),0),0))
です。
私の間違いがどこかをご教示頂けませんか？
何度も恐縮です。
よろしくお願いいたします。
A列 B列 C列 D列 E列 F列
1 エントリー?? 棄権者?? 指名 勝ち点 勝率 順位
2 1 太郎 3 6.00 9
3 2 花子 18 3.33 2
4 3 昭 15 3.30 6
5 4 博 12 6.60 3
6 5 浩子 12 6.60 3
7 6 6 一郎 15 6.60
8 7 正一 3 6.60 8
9 8 茜 6 6.60 7
10 9 光一 15 6.60 1
11 10 純子 12 6.60 3
（エクセル素人親父） 2022/01/12(水) 09:48
色々と問題がありました。 m(__)m １．勝率は100%以下と言う前提でしたが、現実には100(つまり10,000%)以下だったこと。 ２．棄権者の勝ち点を無視して作らなかったこと(サンプルでは棄権者が最下位だったので、問題が顕在化しなかった）
G2セルを下式に変えてトライしてください。※配列として入力するのをお忘れなく
=IF(COUNTA(元!A:A)<ROW(),"",1/MOD(LARGE(ROUND((元!D2:D100*10000+元!E2:E100)*1000,0)*(元!B2:B100="")+1/ROW(),ROW(Z1:Z99)),1))
（半平太） 2022/01/12(水) 10:21
ご教示を頂いた数式をヘルパーG2に入力すると以下の様になってしまいました。
番号 棄権 選手 勝ち点 勝率 順位 1 A B C D E F G 2 #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! 3 #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! 4 4 博 12 6.60 #NUM! 5 5 5 浩子 12 6.60 #NUM! 6 6 6 一郎 15 6.60 6 7 7 7 正一 3 6.60 7 8 8 8 茜 6 6.60 8 9 9 9 光一 15 6.60 9 10 10
（エクセル素人親父） 2022/01/12(水) 17:50
｛｝で閉じるでいいのですよね？
本当にド素人ですみません。
（エクセル素人親父） 2022/01/12(水) 17:55
XL2003はMODの精度が低いセイかも知れないです。
勝ち点の最大はいくらなんでしょうか？
100以下なら、以下の修正をやってみてください。
＞=IF(COUNTA(元!A:A)<ROW(),"",1/MOD(LARGE(ROUND((元!D2:D100*10000+元!E2:E100)*1000,0)*(元!B2:B100="")+1/ROW(),ROW(Z1:Z99)),1)) ~~~~~~ ↓0を一つとって1000にする =IF(COUNTA(元!A:A)<ROW(),"",1/MOD(LARGE(ROUND((元!D2:D100*1000+元!E2:E100)*1000,0)*(元!B2:B100="")+1/ROW(),ROW(Z1:Z99)),1))
※勝ち点の最大が100以上なら別案を考えないとならないです。
（半平太） 2022/01/12(水) 19:14
おはようございます。
勝ち点の最大は30です。
今、ご教示頂いた
=IF(COUNTA(元!A:A)<ROW(),"",1/MOD(LARGE(ROUND((元!D2:D100*1000+元!E2:E100)*1000,0)*(元!B2:B100="")+1/ROW(),ROW(Z1:Z99)),1))
を入力してみましたが、やはり順位はおかしく表示されてしましました。
因みに「元」シートの方の順位は間違いのままですが、そちらは数式を変更しなくても良いのでしょうか？
（エクセル素人親父） 2022/01/13(木) 07:33
すみません。詰めが甘かったです。 順位シートのG2セルには下式を入れてみてください。当方は以下のサンプルで、結果図の通りに出ました。
なお、A列とB列の番号データは「数値型」であることを前提にしています。
G2セル =IF(COUNT(元!A:A)-COUNT(元!B:B)<ROW(Z2:Z100),"",ROUND(1/MOD(LARGE(ROUND((元!D2:D100*1000+元!E2:E100)*1000,0)*(元!B2:B100="")+1/ROW(Z2:Z100),ROW(Z1:Z99)),1),0))
<元 シート サンプル> 行 __A__ __B__ __C__ __D__ _ E _ 1 番号 棄権 選手 勝点 勝率 2 1 太郎 3 6 3 2 花子 18 3.33 4 3 昭 15 3.3 5 4 博 12 6.6 6 5 浩子 12 6.6 7 6 6 一郎 15 6.6 8 7 正一 3 6.6 9 8 茜 6 6.6 10 9 光一 15 6.6 11 10 純子 12 6.6
<順位 シート 結果図> 行 __A__ __B__ __C__ __D__ __E__ __F__ ___G___ 1 番号 棄権 選手 勝点 勝率 順位 Helper 2 2 花子 18 3.33 1 3 3 9 光一 15 6.6 2 10 4 3 昭 15 3.3 3 4 5 4 博 12 6.6 4 5 6 5 浩子 12 6.6 4 6 7 10 純子 12 6.6 4 11 8 8 茜 6 6.6 7 9 9 7 正一 3 6.6 8 8
＞因みに「元」シートの方の順位は間違いのままですが、そちらは数式を変更しなくても良いのでしょうか？
えーと、元シートの順位の話は消滅したと認識しておりますが？ 順位シートが正しく出ればいいと言う認識なのですけど。
必要なら、VLOOKUP関数を使って順位シートから逆に引っ張ってきてください。
（半平太） 2022/01/13(木) 10:10
試しましたが、以下の通りになってしまいました。
半平太様の方では問題が無かったとのことでしたので、私側のスペックの問題ですね。
今まで、私みたいな知識の無い者に対して丁寧にご対応下さった事を感謝申し上げます。
本当に有難うございました。
【元シート】 A B C D E 1 太郎 3 6.00 2 花子 18 3.33 3 昭 15 3.30 4 博 12 3.33 5 浩子 12 3.33 6 6 一郎 15 6.60 7 正一 3 6.60 8 茜 6 6.60 9 光一 15 6.60 10 順子 12 6.60
【順位シート】
A B C D E F G
2 花子 18 3.33 1 3
9 光一 15 6.60 2 10
10 順子 12 6.60 3 11
5 浩子 12 3.33 4 6
7 正一 3 6.60 5 8
0 0 0 0.00 6 12
0 0 0 0.00 6 14
0 0 0 0.00 6 16
（エクセル素人親父） 2022/01/13(木) 13:12
１．あれ？ 私のは「太郎さん」が出てないですね。またミスってしまった。
２．それと、そちらの元シートの1行目にタイトルがないですけど、実際はどうなんでしょうか？
1行目にタイトルがあるなら G2セル =IF(COUNT(元!A:A)-COUNT(元!B:B)<ROW(X1:X99),"",ROUND(1/MOD(LARGE(ROUND((元!D2:D100*1000+元!E2:E100)*1000,0)*(元!B2:B100="")+1/ROW(X2:X100),ROW(X1:X99)),1),0)) そうすると、結果は下記になりますが。
<順位 シート 結果図> 行 __A__ __B__ __C__ __D__ __E__ __F__ ___G___ 1 番号 棄権 選手 勝点 勝率 順位 Helper 2 2 花子 18 3.33 1 3 3 9 光一 15 6.6 2 10 4 3 昭 15 3.3 3 4 5 10 順子 12 6.6 4 11 6 4 博 12 3.33 5 5 7 5 浩子 12 3.33 5 6 8 8 茜 6 6.6 7 9 9 7 正一 3 6.6 8 8 10 1 太郎 3 6 9 2
（半平太） 2022/01/13(木) 13:26
＞私側のスペックの問題ですね。
一旦XL2010で作成した後、XL2000で再検証しておりますので、スペックの問題はクリアしているはずです。
（半平太） 2022/01/13(木) 13:35
こんにちは。
できました！
完璧にできました！
本当にありがとうございます。
私のような出来の悪い質問者に最後まで匙を投げずにご対応下さり感謝申し上げます。
活用させて頂きます！
（エクセル素人親父） 2022/01/13(木) 15:08
棄権者も下にまとめて出す仕様だった。(-_-;)
G2セル =IF(COUNT(元!A:A)-COUNT(元!B:B)<ROW(X1:X99),・・
~~~~~消去~~~~~
（半平太） 2022/01/13(木) 15:39
解決されたようで何よりです。 遅ればせながら、「データ」の「並び替え」機能を利用を再度、推奨します。
<<元シート レイアウト>> シート名はSheet1とします。（レイアウトは借用しました） 行 __A__ __B__ __C__ __D__ _ E _ 1 番号 棄権 選手 勝点 勝率 2 1 太郎 3 6 3 2 花子 18 3.33 4 3 昭 15 3.3 5 4 博 12 6.6 6 5 浩子 12 6.6 7 6 6 一郎 15 6.6 8 7 正一 3 6.6 9 8 茜 6 6.6 10 9 光一 15 6.6 11 10 純子 12 6.6
<<順位シート レイアウト>> 行 __A__ __B__ __C__ __D__ _ E _ _F_ 1 番号 棄権 選手 勝点 勝率 順位 2 3 4 （以下省略）
■順位シートに計算式を入れます。 (1)全体は単純参照します。 ・ A2セルに、=Sheet1!A2 と入力 ・ これをA2:E11にコピーします。 (2)「棄権」列だけ、少し細工します。 ・ B2セルに、=IF(Sheet1!B2="","-",Sheet1!B2) と入力 ・ これを下にコピーします。 (3)「順序」の列も少し細工 ・ F2セルに =IF(B2="-",ROW(A1),"") 注 ROW(A1)は変えないで下さい。 ・ これを下にコピーします。
■ソートの実行
・A1:E11（F列は対象外です）を選択し、「データ」の「並び替え」をクリックして、以下を設定。
（先頭行をデータの見出しとして使用 にチェックを入れる。)
・最優先されるキー 棄権を選択 / 並び替えのキーはセルの値 / 順序 降順
・次に優先されるキー 勝点を選択 / 並び替えのキーはセルの値 / 順序 大きい順
・次に優先されるキー 勝率を選択 / 並び替えのキーはセルの値 / 順序 大きい順
・OKボタンをクリック
・これで並び替えが完了です。
以上です。
<<順位シート レイアウト>> （処理結果です） 行 __A__ __B__ __C__ __D__ _ E _ _F_ 1 番号 棄権 選手 勝点 勝率 順位 2 2 - 花子 18 3.33 1 3 9 - 光一 15 6.6 2 4 3 - 昭 15 3.3 3 5 4 - 博 12 6.6 4 6 5 - 浩子 12 6.6 5 7 10 - 純子 12 6.6 6 8 8 - 茜 6 6.6 7 9 7 - 正一 3 6.6 8 10 1 - 太郎 3 6 9 11 6 6 一郎 15 6.6
なお、データに変更があったときは、 ・A1:E11（F列は対象外です）を選択し、「データ」の「並び替え」をクリック という上記と同じ操作をすれば、 ・上記の設定がすでにセットされたままになっていますから、単にOKを押すだけです。 ・データ変更を反映して、自動的に並び替えが行われます。 （ほとんど負荷はなく、自動更新に近いと思います）
■留意点
・数式ありのものを並び替えるには、別シートで行うことが肝心のようです。
・つまり、元データのあるシートと同一シート内では結果が変わってきます。（不思議）
並び替えた数式が、新たに元とは別のセルを参照してしまうようです。
（私は、別シートから参照しないとうまくいかない旨、初めて知りました。
これは、個人的には、余り良い仕様とは言えないと思います。）
マイクロソフト社公認の質問掲示板での議論があり、 https://answers.microsoft.com/ja-jp/msoffice/forum/all/excel%E3%81%A7%E9%96%A2%E6%95%B0%E3%81%AE%E7%B5%90/e6af0113-b4e9-4ad6-b1bd-263dd1c3e06e 一言でいうと、「それが仕様です」ということのようです。 なぜ差が生ずるのか納得いかないが、仕方がない。（こういうことはかなり一般的ですね。）
(余談めいた独り言）
# 質問者さんは、提示された数式を理解されたのだろうか。 # 同じようなことに出くわしたとき、ご自分で対応できるのでしょうか。
# マイクロソフト社は、こういうときのために、 # 特別のツール(「並び替え」機能等）を用意しています。 # なんでも数式でやるのは大変だろうと考えて、汎用的な道具を提供しているのです。
# もちろん数式で可能であり、今回の回答をいただいた方には、そのご苦労を多としたいと思います。 # ただ、そうしたスキルの高い人ばかりではないのであって、 # 一般ユーザー誰もが「なんでも数式で」というのはいかがなものだろうかと思います。 # "数式対応が一番簡単だろう"とでも思っているのでしょうか。 # # 数式案も長所も勿論ありますが、Excelが用意している道具を適宜使うことも # 考慮に入れたほうがよいと思います。 # そのほうが、比較的楽に、他人の力を借りなくても対応できるはずだと思います。 9:32 タイプミスを修正。 （γ） 2022/01/14(金) 07:17
詳しく解説を頂きありがとうございます。
並び替えを一度やってみます。
ただ、私の手順が悪いのが、一番上が１７位、次から１位、２位となったりします。
そのあたりも半平太様にご教示頂いたことを踏まえて、もう少し勉強したいと思います。
ありがとうございました。
（エクセル素人親父） 2022/01/14(金) 18:02
あれ？
並替え案は「γさん」ですけども。。
（半平太） 2022/01/14(金) 19:04
間違えておりました。
すみません。
（エクセル素人親父） 2022/01/15(土) 10:00
おはようございます。
ご教示をありがとうございます。
私が理解している数式はビギナーレベルの簡単なものだけです。
今回、みなさんがご教示下さった数式は全く理解できませんでしたのでそのままコピペをして使わせていただくしか他なりません。
もちろん、並べ替え機能で私の思う並べ替えが出来ればよかったのですが、それすら知識がなかったようでうまい具合に並べ替えができていませんでした。
今回、γ様にご教示頂いた手順で再度挑戦してみます。
有難うございました。
（エクセル素人親父） 2022/01/15(土) 10:05
回答者のγことガンマです。 念のため、補足・追記しておきます。
「棄権」列に修正を加えたことだけが少し特別でした。 これは、棄権していない人は「空白」となっており、 ソートすると空白は最後にされてしまい、都合が悪かったのです。
今考えなおすと、 ・普通に、他の項目と同様に、Sheet1シートの項目を参照したうえで、 ・表示形式を #;; などとして0を表示しないようにすればよかった。 ・ソートは昇順（小さい順）で普通にできます。（複数の棄権者がいれば番号順に並びます。）
Excel2003での複数列のソートは下記参照。 https://www.wanichan.com/pc/excel/2003/05/174.html 私は2019を前提に操作手順を書いてしまいましたが、考え方は変わりありません。 ひょっとしたら、Excel2003だと数式のままのソートはうまくいかないかもしれません。 その場合は、面倒でも他シートにコピーペイストしてからソートすれば確実に実行できます。 （結果が得られることが大切で、少々の手間は問題にはならないはずです。） （γ） 2022/01/15(土) 10:40
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.