エクセル実践塾 > エクセル実践塾2016

成績一覧から上位10名を選び出して表示したい

 

同順が含まれる場合に備えて、 順位を表す数値に固有の小数を付加し、 それを使って順位から表引きを行います

RANK関数で調べた順位は、 同順が考えられます。

同順が含まれる場合は、 順位から該当を表引きすることができません。

そこで、ROW関数を使って、 行ごとに0.5未満の小数を付加しておきます (0.5以上の小数を付加すると、四捨五入で順位が繰り上がってしまいます)。

なお、 作例のように、表引きの検索対象となる順位が、 表引きする項目の右側に位置する場合には、 VLOOKUP関数による表引きを行えません。

配列数式による表引きは、そのような場合に利用できます。

ただし、表引きできるのは数値に限られます。 作例の場合には、受験番号が数値で入力されているので、配列数式による表引きが可能となっています。


 

@作例では、 成績一覧から、 左図のような表で上位10人を選び出して表示します

ARANK関数で求めた順位を表す数値に、 ROW関数を使った小数を足します

ROW()とすると、 そのセルの行番号を求められます。

それを10000で割れば、 0.5未満の小数を得られます。 これで、同順であっても区別できる数値となります。

小数点以下の表示桁数を調整すれば、 順位を表す数値は、 RANK関数式によるものと変わらなくなります。

※5000行以上の表の場合には、 10000で割っても0.5以上の小数となってしまいます。 その場合には、100000で割るなど、 付加する小数が0.5以上とならないようにします。

BSMALL関数を使って、 上位10人の順位を表す数値を選び出します

CROW関数を使って、 取得する数値の順位を変えていけば、 上位10人の順位を表す数値を表示できます

作例の場合には、 ROW()-2とすれば、 順位の小さなものから10個の数値を選び出して表示できます。

小数点以下の表示桁数を調整すれば、 同順を含む順位になります。

D配列数式を使って、 順位を表す数値から、 受験番号を表引きします

配列数式を使うと、 セル範囲を比較対象にできます。

比較した結果が真のときに、 受験番号の数値を取り出して、 SUM関数の引数にします。

合致する受験番号は一つだけなので、 SUM関数で合計しても、 得られるのはその受験番号を表す数値となります。

ECtrl+Shift+Enterキーで、 配列数式として確定し、 それを同列のセルにコピーして使います

順位を表す小数から、 受験番号を表す数値が表引きされます。

F氏名は、 配列数式で表引きした受験番号から、 VLOOKUP関数を使って表引きします

検索対象となる受験番号の列を、 行番号のみの絶対指定にしておくと、 得点の表引きにも同じVLOOKUP関数式を流用できます

GVLOOKUP関数式を、 右ドラッグでコピーします

H検索対象の列を行番号のみの絶対指定にしてあるので、 列番号を 得点の入力されている列の番号に書き換えるだけで、 コピーしたVLOOKUP関数式を流用できます

VLOOKUP関数式を確定するときに、 Ctrl+Enterを使えば、 修正した数式を選択中のセルすべて適用できます。

●成績一覧表から、 (同順を含む) 上位10人を選び出して表示する方法を解説する動画です

関連する他のページ

学級や男女別の順位を求めたい
条件付きの順位を求めるときには配列数式を用います

テストごとの順位を知りたい
RANK関数を使います

合計点での順位を求めたい
RANK関数を使うと、全体における順位を求めることができます

並べ替えないで、勝ち数による順位を知りたい
RANK関数を使えば、並べ替えることなく順位がわかります

直近2カ月の営業成績のいずれかが上位5位以内の人を強調する
RANK関数での判定結果を加算することで、1度でも上位5位以内に入った人がわかります

過去半年で上位5位以内が3ヵ月以上ある人を強調する
別シートで各月の判定を行い、条件付き書式でそれらを加算します

直近2カ月の営業成績がいずれも下位5位以内の人を強調する
RANK関数での判定結果を乗算することで、両方で下位5位以内だった人がわかります

過去半年で下位5位以内が3ヵ月以上ある人を強調する
別シートで各月の判定を行い、条件付き書式でそれらを加算します

標準偏差や偏差値を求めたい
標準偏差をSTDEV.P関数で求めてから、 それを使って偏差値を計算します

偏差値を求めたい
偏差値は、z得点を10倍にして、50を足すことで、数値として扱いやすくしたものです

特集「条件付き書式」

複数の検索データに合致するセル数を調べたい
配列数式を使えば、COUNTIF関数で調べられます

別シートの一覧に含まれる商品名が入力されたときに、そのセルを任意の色で塗りつぶして強調したい
COUNTIF関数を使った条件式で、条件付き書式を設定します

素数を探し出したい
配列数式を使えば、一つの数式で、素数か否かを判断できます

マス計算の解答を作りたい
配列数式を使うと、 まとめて答を得られます

シフト表をガントチャート(帯)で表したい
配列数式で求めた結果を 条件付き書式で帯にします

『月』列を追加しないで、各月の売上を集計したい
配列数式なら、 数式による条件式を使うことができます

直近3回の来店日を知りたい
LARGE関数を使った配列数式で求められます

学級や男女別の順位を求めたい
条件付きの順位を求めるときには配列数式を用います

源泉徴収税額表を使って、 所得税を求めたい
配列数式を使えば、 空白の含まれる表からでも、 該当値を取得することができます

複数の検索データに合致するセル数を調べたい
配列数式を使えば、COUNTIF関数で調べられます

階級ごとの度数を数式で求めたい
FREQUENCY関数を使えば、配列数式で簡単に度数を求められます

さらに条件を加えて、順位を求めたい
配列数式での条件の追加は、追加する条件式の乗算で行えます

COUNTIFS関数の使えない2007より前のExcelでも利用できる表を作りたい
COUNTIFS関数と同等の、配列数式を利用する方法があります

一つおきのセルの値を合計したい
配列数式を使えば、特殊な条件に合致するセルの値だけを合計することができます

マス計算を簡単に行いたい
交差するセルで、項目値の計算結果を求めたいときには、Ctrl+Shift+Enterキーで配列数式にします

他のシートのデータを転記して使いたい
あらかじめデータ範囲に適当な名前をつけておけば、配列数式で簡単にできます

住所から都道府県を取り出したい
あらかじめ都道府県の頭3文字を抜き出したリストを用意して、 それを使ってVLOOKUP関数による表引きを行います

アンケートをピボットテーブルで集計したい
数値で入力されている選択肢の情報を、 文字列に直してから集計します

顧客番号から顧客名を表引きしたい
VLOOKUP関数を使います

納品書の明細もVLOOKUP関数で転記したい
納品番号に行番号を追加すれば、 それを使って 各明細行の表引きが可能です

納品書の日付と顧客名を自動転記したい
元表がテーブル化されていれば、 そのテーブル名と項目列名を指定して、 納品番号から表引きできます

顧客名の顧客コードを表引きしたい
検索する「顧客名」を左端に移動して、 VLOOKUP関数で表引きします

テーブル化した商品リストから、 商品コードで商品情報を表引きしたい
表引きする側とされる側の両方をテーブル化すれば、 項目列名で表引きすることができます

住所から都道府県名を取り出したい
都道府県名の一覧を用意すれば、 VLOOKUP関数で住所から都道府県名を取り出せます

評価点を5段階の評定に直したい
変換表を用意すれば、VLOOKUP関数で直せます

住所から都道府県名を取り出したい
都道府県名の頭3文字を使って、住所から該当する都道府県を探して表示します

テーブル化されている表から表引きしたい
F5キーでテーブルおよび列の指定を簡単に行えます

住所データから抜き出した都道府県名を完全なものにしたい
VLOOKUP関数を使って、都道府県名リストと照合します

祝日名や振替休日を、日付の隣に表示したい
祝日名はVLOOKUP関数で、振替休日はCOUNTIF関数で照合します

受注時に在庫数を確認したい
VLOOKUP関数で在庫表から参照できます

祝祭日を自動的に表示させたい
祝祭日のリストを用意しておけば、日付に該当するものを、VLOOKUP関数で表引きできます

料金表から規定料金を導きたい
VLOOKUP関数を使えば、料金表から表引きできます

テーブル化した表を使って表引きしたい
あらかじめ適当なテーブル名をつけておけば、その名前で表引きを行えます

配分で5段階評価したい
配分表を用意すれば、VLOOKUP関数で評価値を求められます

都道府県名から送料を求めたい
VLOOKUP関数を使って、都道府県名に該当する送料を検索します

例外の市を含む 都道府県から、送料を求めたい
IFERRORを使って、2つのVLOOKUP関数で段階的に送料を検索します

入力済みの住所を、都道府県名と、それ以降の住所に分けたい
郵政事業株式会社の郵便番号データで都道府県名を表引きすることで、 それ以降の住所と分けて表示することが可能です

郵便番号から、都道府県名と、それ以降の住所を、別々に入力したい
郵政事業株式会社のWebで公開されている郵便番号データを使えば、 郵便番号から都道府県名などを表引きできます

商品名や単価を、商品一覧から表引きしたい
表引きする表にあらかじめ適当な名前を付けておけば、VLOOKUP関数で簡単に表引きできます

あとから見てもわかりやすいVLOOKUP関数式にしたい
「範囲」として指定するセル範囲に、あらかじめ適当な名前を付けておけば、 VLOOKUP関数式でもそれを使えるのでわかりやすくなります

商品番号を入力すると、自動的に商品名が表示されるようにしたい
VLOOKUP関数式を設定すれば、商品リストからの表引きを行えます

商品番号を入力すると、自動的に商品名以外の情報も表示されるようにしたい
商品名などを表示するセルが、表引きの対象リストと同じ並びになっていれば、商品名のVLOOKUP関数式をコピーして使えます

一覧表のデータを、申請書の必要な箇所にコピーしたい
あらかじめ必要なVLOOKUP関数式を設定しておけば、 データの一つを入力するだけで、関連する項目を簡単に埋められます

出前館

アウトドア&フィッシング ナチュラム