「相対位置」の表示欄を作り,「=PERCENTRANK(全体,対象)」を入力
「合否」の表示欄を作り,「=IF(論理式,真の場合,偽の場合)」を入力

 Excelで試験の結果を表にまとめ,その表を使って受験者の上位50%を合格にするといったケースを考えてみよう。こんなとき,一般的には得点順に表を並べ替えて上から半分に「合格」をすると思うが,いちいち元の表を並べ替えずに合格を判定する方法がある。それが今回紹介する,PERCENTRANK関数とIF関数を組み合わせたテクニックだ(図1)。

図1●上位50%以上を合格にしたい。自動で判定するには?

PERCENTRANK関数で各受験者が上位何%に入るかを調べる

 まずはPERCENTRANK関数を使って,各受験者の点数について,各数値の相対位置を表示させる。つまり,これで各受験者が上位何%に入るかを調べるというわけだ。

 この関数では,データ全体のうち,最大値を「1」,最小値を「0」とし,対象となるデータの数値がその間のどこに位置するかを小数で示す。例えば,「=PERCENTRANK(B3:B8,B3)」のように入力すると,B3からB8セルに表示された数値の中で,B3セルの相対的な位置がわかる。なお,「全体」のセル範囲はコピーしたときにずれると困るので,「$B$3:$B$8」のように絶対参照にしておこう(図2~図4)。

図2●「相対位置」欄を作成し,PERCENTRANK関数を使って,「全体」「対象」の順に入力する

図3●セルの右下隅をドラグして,数式をコピーする

図4●それぞれの相対位置が表示された

IF関数で「上位50%に入っているか」を判定

 次に,IF関数を使って場合分けをする。今回は上位50%以上を合格にするので,「=IF(C3>=0.5,"合格","不合格"」のように入力する(図5)。「C3>=0.5」は「C3セルが0.5以上」という条件を示す。次の「"合格"」はその条件が正しいときに「合格」と表示させることを示す。次の「"不合格"」は条件が正しくないときに「不合格」と表示させることを示す。あとは,数式をコピーすれば,すべてのデータで合否判定ができる(図6,図7)。

 ただし,最小値を「0」に置くために,境目の合否が実感とずれるケースがあるので注意しよう。例えば,全体が10人で上位50%以上を合格とする場合は,このやり方で5人が合格となる。これは実感にあっているだろう。しかし,全体が11人の場合,計算上の50%は5.5人だが,このやり方では6人が合格となる。

図5●「合否」欄にIF関数を使って「論理式」「真の場合」「偽の場合」を入力する

図6●セルの右下隅をドラグして,数式をコピーする

図7●合否が表示された

※この記事はExcel 2002(Office XP)をもとに執筆しています。

【この連載について】

 Excelは仕事のツールとしては非常にポピュラーなソフトですが,多機能がゆえに,その便利な機能を十分に使いこなせていない人が非常に多いのではないでしょうか。そこでこの連載では,“操作自体は決して難しいものではないけれど,意外と知られていない”というExcelの便利な操作方法を厳選し,毎週1~2本のペースで紹介していきます。ぜひご期待ください!

■岡野 幸治 (おかの こうじ)

フリーランスライター。「日経PC21」「日経PCビギナーズ」などでパソコン関連の活用記事を中心に執筆中。