同率順位の発生しないランキング表をエクセルで作成したい

EXCELで同率順位の発生しないランキング表を作成したいと思っています。

各ブロック毎にその人が進捗率何%かを計算して、E列「進捗率」が同じ場合にはF列「完了した作業」が多い方を上位とする関数を組みたく思いますが、同率順位を出す関数までは作れたのですが、同率順位の発生しない数式を作るのに苦戦しております。

D列「順位」に入っている数式は
COUNTIFS($E$3:$E$12, ">" & $E3, $B$3:$B$12, $B3) + 1
になります。

EXCELに詳しい方のお知恵をお借りしたく質問いたしました。
ご教示いただけますと幸いです。
よろしくお願いいたします。

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

• 

  こんにちは
  
  ご回答ありがとうございます。
  おっしゃる通り、データ値が全く同じ場合でも順位を付けられるようにしないといけなかったですね。
  「進捗率(%)も完了した作業数も同じ場合は、G列の目標作業数が多い方を上位とする」としたいです。
  ※これでもまたデータ値が同じになることがあると思いますが…
  
  素敵な数式もいただきましてありがとうございます。
  いただきました数式を入力し、同率の発生しないランキング表が完成しました！
  
  こちらを各ブロック毎に順位付けできればと思っております。
  Aブロック1～5位まで、Bブロックも1～5位までといった感じです。
  
  たくさん教えてくださったのにさらにご質問を重ねてしまい申し訳ございません。
  もしよろしければ追加ご教示いただければと思います。
  よろしくお願いいたします。

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

No.4 回答者： bonaron 回答日時： 2024/10/07 14:46

一応、力任せにやってみましたが。

=COUNTIFS($E$4:$E$13, ">" & $E4, $B$4:$B$13, $B4)+1+COUNTIFS($E$4:$E$13, $E4, $B$4:$B$13, $B4,$F$4:$F$13,">" & $F4)+COUNTIFS($E$4:$E4, $E4, $B$4:$B4, $B4,$F$4:$F4,"=" & $F4)-1

範囲を修正するのが大変そう。
手が滑りそうな。

No.3さんの
③完了した作業数が同じ場合は、行の並び順
を採用しています。
もし同順のままの方がよろしければ
3つ目の COUNTIFS 以下不要。

No.3 回答者： tatsumaru77 回答日時： 2024/10/06 17:29

作業列を作って良いなら、以下の方法でできます。

作業列を作りたくないなら、この書き込みは無視してください。
I列に作業列を作るとします。
画像では４行からデータが開始ですが、提示された計算式では、３行から開始しているように見えるので、データは３行から開始の前提で書きます。
順位は以下の順序で決めます。
①進捗率の大きい順
②進捗率が同じ場合は、完了した作業数の多い順
③完了した作業数が同じ場合は、行の並び順

あなたの希望では、③の完了した作業数が同じ場合は、目標作業数が多い順にしたいとのことですが、そもそも進捗率は、完了した作業数÷目標作業数　なので、①②が同じとき、目標作業数も必ず同じになります。
（進捗率を手入力しているなら別ですが）
よって、完了した作業数が同じ場合は、行の並び順としました。

I3に
=E3*10000000+F3*100+99-ROW(A1)
と入力し、E12までオートフィルでコピーしてください。

D3に
=COUNTIFS($I$3:$I$12, ">" & $I3, $B$3:$B$12, $B3) + 1
と入力し、D12までオートフィルでコピーしてください。

前提条件として
①完了した作業件数は９９９件以内であること
②行数は９９行までであること
となっています。
この前提条件が変わる場合は、I3の式を変える必要があります。

No.2 回答者： fujillin 回答日時： 2024/10/06 15:33

No1です。

＞Aブロック1～5位まで、Bブロックも1～5位までといった感じです。
ブロックごとに計算すれば済む話ではないでしょうか？

No1の式はややわかりにくいので、もっと簡単な式の例を記しておきます。
添付図では、Ｊ列を作業列としてJ4セルに
　=RANK(E4:E13,E4:E13)*100+RANK(F4:F13,F4:F13)
の式を入力してあります。

この結果、「E列の重複を許す順位」を100倍したものに、下二桁は「F列の重複を許す順位」を加算した数値が表示されます。
（E列の方が評価の優先順位が高いので、高位になるようにしてあります）
（対象人数が99人を超える場合は、桁数の調整が必要です）

このJ列の結果を用いて、再度順位付けすれば良いので、D4セルに
　=RANK(J4#,J4#,1)
を入力してあります。

こちらの方法であれば意味を理解しやすいでしょうから、応用もできる事と思います。


※ 一発でブロック分けをして求めることも不可能ではないと思いますけれど、No1より数倍複雑な式になることが予想され、さらに理解できなくなるだけだと思いますので止めておきます。

No.1 回答者： fujillin 回答日時： 2024/10/06 11:57

こんにちは

＞同率順位の発生しない数式を作るのに苦戦しております。
数式の前に、評価方法を考える必要があります。
ご提示の、
＞E列「進捗率」が同じ場合にはF列「完了した作業」が多い方を上位とする
というルールだけでは、同率が発生する可能性があります。

極端な例で言えば、データの値が全く同じ場合でも順位を付けられるルールにしておかないと『同率順位が発生しないルール』にはなりません。
（例えば、最後は記載順で決めるとか・・）

＞EXCELに詳しい方のお知恵をお借りしたく
ここまでのルールに関しては、Excelは関係ありません。
決めたルールをどのように数式化するかで初めてExcelの問題になります。

簡単な方法は作業列を使う方法ですね。
決めたルールを一つの指標に換算して作業列に求め、その値に基づいて順位付けをすれば良いです。
Excelの場合、数値の有効桁数が15桁ありますので、例えば上位5桁を第一優先順位の指標、次の5桁を第二の指標・・などのようにして数値化すれば、一つの値で評価できるようになります。


「御託はいらないから、結果をよこせ！」と言うのなら・・
文章にご提示の内容と、図で示された内容が一致していないように思われますけれど・・・
Office365等であれば、Ｄ4セルに
=MATCH(INT(F4:F13*1E8/G4:G13)*1E3+F4:F13,SORT(INT(F4:F13*1E8/G4:G13)*1E3+F4:F13,1,-1),-1)
を入力すれば、順位が下方にスピルされます。
（下部のセルを空欄にしておかないと、スピルできません）

ただし、ご提示の図とは異なり、結果はＤ4セルから順に
　7, 6, 5, 1, 2, 4, 3, 9, 10, 8
となります。
図で「5位にしたい」とあるのは7位に、「2位にしたい」は3位になります。
（多分、文章とは異なる評価基準を採用しているのだと想像しますけれど、当方にはその内容はわかりかねます）

※ 上式はご提示の文章に基づく評価方法なので、上に述べたように同率順位が発生する可能性があります。
※ 進捗率は有効8桁（＝実質7桁）で評価、作業数は999以下であると仮定しています。

この回答へのお礼

こんにちは

ご回答ありがとうございます。
おっしゃる通り、データ値が全く同じ場合でも順位を付けられるようにしないといけなかったですね。
「進捗率(%)も完了した作業数も同じ場合は、G列の目標作業数が多い方を上位とする」としたいです。
※これでもまたデータ値が同じになることがあると思いますが…

素敵な数式もいただきましてありがとうございます。
いただきました数式を入力し、同率の発生しないランキング表が完成しました！

こちらを各ブロック毎に順位付けできればと思っております。
Aブロック1～5位まで、Bブロックも1～5位までといった感じです。

たくさん教えてくださったのにさらにご質問を重ねてしまい申し訳ございません。
もしよろしければ追加ご教示いただければと思います。
よろしくお願いいたします。

※教えてgooを使い慣れておらず、補足にも入れてしまいました、申し訳ございません。

お礼日時：2024/10/06 13:02