« 「アクティブ列との相違」を使って、他のセルとは異なっている数式を見つける。 | トップページ | エスカレーターの "それ" は「配慮」だと思う。 »

Excel で「上位○位」「下位○位」「それら以外」をわかるようにする。

この記事は自分用の覚え書きです。

■MacBook Pro macOS Mojave
バージョン 10.14.6

■ Office365 Business サブスクリプション
Excel for Mac バージョン 16.29(19090802)

I.E さん。

Excel で「上位○位」「下位○位」「それら以外の点数」がわかるようにするやり方を考えてみました。
あれこれと関数を使わないで、誰でもさわれるようにしておきたい。——ということだったので、関数1つとあとはオートフィルタで対応するやり方です。

手順さへわかれば「エクセルを使うのは初めて」という人にもやっていただけると思います。

■課題
データ数は1万2千件数百。
氏名の「ヨミ」で「昇順」に並び替えてあるので、「点数」の列は何の順にもなっていなくて、大小の点数が散らばっている。
そのままの状態で、「上位2千人」「下位2千人」「それら以外の点数」がわかるようにしておきたい。

■手順

以下の例では、わかりやすくするためにデータ数を15件にしてあります。
抜き出すのは、上位5位と下位5位にしてみました。
実際のデータ数は1万2件越えだそうですが、何件あってもやることは同じです。

こういう表ですよね。「ヨミ」の昇順で並べてあるので、点数は何の順にもなっていません。
(ネット上にある人名を生成してくれるサービスで、氏名をつくっています。)

00yomi_syoujun

やることは大きく3つです。

  1. 作業列を2つ追加して、点数について「高い方から数えた場合の順位」と「低い方から数えた場合の順位」を求める。
    使う関数は、RANK.EQ 関数だけです。
  2. 「高い方から数えた場合の順位」について、オートフィルタで1位から5位までを抜き出してセルに色をつける。
  3. 「低い方から数えた場合の順位」について、同じく1位から5位までを抜き出して、別の色をセルにつける。

「上位」と「下位」のセルに色をつけておけば、「それら以外」も見た目でわかります。

では、手順の詳細です。

1.「高い方から数えた場合の順位」と「低い方から数えた場合の順位」を求める。

上位○位と下位○位を見つけるために、RANK.EQ 関数を使って点数の順位をまず求めます。

順位を求めるために昔からあるのは RANK 関数ですが、現在は RANK.EQ 関数の利用をマイクロソフトは推奨しています。
互換のために RANK 関数は残っていますが、将来的にいつ廃止されてもおかしくありません。 ヘルプの RANK 関数の説明にもそんな風に書いてますから、これからは RANK.EQ 関数を使っておくのがよいと思います。

元の表に作業列を追加して、各人の点数について「高い方から数えた場合の順位」と「低い方から数えた場合の順位」を求めます。
D列とE列が作業列です。それらのセルに数式を入れています。

01koujunn_syoujun

RANK.EQ 関数の書式は次のようです。

RANK.EQ(数値,範囲,[順序])

引数「順序」に 0 (ゼロ) を指定するか、順序を省略すると、引数「範囲」のなか「数値」について、高い方から数えた順位が表示されます。
上図では、D列の数式内で 0 を指定しています。

セル D2 の数式は、=RANK.EQ(C2,$C$2:$C$16,0) になります。
引数「順序」は省略してもよいのですが、引数があることを一応示しておきたくて 0 を入れました。

D2 を下にコピーするので、「点数」の範囲が変わらないように絶対参照にしておくのが大事です。
セル C6 の 100 が、C列の中では一番大きいので、D6 に 1 が表示されています。

次は、E列のセルの数式です。セル E2 の数式は、=RANK.EQ(C2,$C$2:$C$16,1) です。
「順序」に 0 以外の数値(ここでは 1にしています。0 に対して 1 としておくのが考えやすいと思ったからです。 )を指定すると、「範囲」のなかの「数値」について、 低い方から数えた順位が表示されます。
上の図ではセル C15 の 39 が、C列の中で一番小さいので、E15 に 1 が表示されています。

なお、1万2千を超える行数だと数式をコピーするのは大変です。
図のような表だと、D2 に数式を入れたら、セルの右下角にカーソルを合わせて、パッドを2本指で2回タップすると、D列の一番最後の行まで数式が入力されます。
D列を入力したあとならE列でも同じことができます。

Fillhandl02

2.「高い方から数えた場合の順位」について、オートフィルタで1位から5位までを抜き出す。

順位を求めたら、「高い方から数えた順位」について上位5位を抜き出します。
D列でオートフィルタの「指定の範囲内」を選んで、「1以上5以下」を指定します。
(実際には、ここで「1以上2000以下」を指定ですね。)

データ件数がいくつかあるか(この場合は5つに決まってますが)は、ステータスバーに表示されます。
(図ではステータスバーは載せていません。)

02joui5nin01

上位5位が表示されます。

「点数」の列のセルに色をつけておきます。ここでは、薄い青にしてあります。

実際は2000件ですから、control+shift+↓で、一番下のセルまで一気に選択できます。
下図でいえば C4 をクリックしてから control+shift+↓ です。

03joui5nin02

RANK.EQ 関数は、同点の場合は同じ順位をちゃんとふってくれます。
上図では、セル D4 と D16 のどちらも 90点 で2位なので、2 が表示されています。

3.「低い方から数えた場合の順位」について、1位から5位までを抜き出す。

オートフィルタをクリアして、今度は「低い方から数えた順位」——下位5位までのデータを抜き出します。
することはD列のときと同じです。
E列でオートフィルタの「指定の範囲内」を選んで、「1以上5以下」を指定します。
(実際は、ここでも「1以上2000以下」を指定)

04kai5nin01

下位5位が表示されるので、「点数」の列のセルに先とは異なる色をつけます。
ここでは、薄いオレンジにしてあります。

05kai5nin02

これで「上位5位」と「下位5位」、「それら以外の点数」がわかるようになっています。

オートフィルタをクリアして全体を見てます。
下図のC列で薄い青がついているセルが上位5位、薄いオレンジがついているセルが下位5位です。
色がついていないセルが、それら以外の点数です。

06tensu_irodukego01

|

« 「アクティブ列との相違」を使って、他のセルとは異なっている数式を見つける。 | トップページ | エスカレーターの "それ" は「配慮」だと思う。 »

コメント

コメントを書く



(ウェブ上には掲載しません)




« 「アクティブ列との相違」を使って、他のセルとは異なっている数式を見つける。 | トップページ | エスカレーターの "それ" は「配慮」だと思う。 »