十日日記

Link 順位表を作成する

この画像は、ある宇宙船の船員を対象に行なったテストの結果だ。緑色見出しのデータを元に、黄色見出しのような順位表を作りたい。順位表は、上位順に並べるが、同一順位の場合には番号が若いものを優先させる。たとえば2位はピカード艦長とラフォージ少佐になっているが、このようなときには必ずピカードを上にする。どのように関数を組み合わせたらよいだろうか。

SMALL関数とVLOOKUP関数とを組み合わせて使うのだろうと考えた人は、大筋として正しい。ただしこのとき同一順位の存在が問題になる。画像で言えば、H3とH4とを区別する必要がある。

作業セルは使わずに済めばそれに越したことはないと思っているので、作業セルを使わない解法を以下に記す。それは、E列でRANK関数を使う際に予め次のような細工をしておくことだ。たとえばE2の場合、

=RANK(D2,D$2:D$6)+0.01*A2

のようにする。後半の「+0.01*A2」がキモで、要は順位と番号とを小数点によって結合させているわけだ。そしてG列やH列では、整数部分・小数部分を取り出す。ここではH2の数式を見てみよう。小数部分を取り出すのは、整数部分よりも難しい。

=ROUND(100*(SMALL(E$2:E$6,ROW()-1)-G2),0)

ROUND関数を使っていることに注意しよう。これは演算誤差対策だ。単純に「その数−整数部分」×100としては失敗する。もっとよい方法があれば教えてほしい。