┌──────────────────────────┐
│【関数コレクションシリーズ】 ＝＝Number.041＝＝│
└──────────────────────────┘
◆ 今日の問題 「計算方法だけでなく項目名も切り替える」
Ｑ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
前回は、C2のセルにＤ列の数字を入れるだけで、その計算が出来るよ
うにしたが、今回は、A8に計算方法も表示するようにしたい。A8に関
数を設定せよ。
（例えば、C2に、1 を入れると、A8に「平均」が表示されるように）
※ 前回の表。B8には、=SUBTOTAL(C2,B2:B7) が入っているので、C2
のセルにＤ列の数字を入れると、それに応じた計算が行われる。
例）9を入れると「合計」が計算される
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃Ａ │Ｂ │Ｃ │Ｄ │Ｅ ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃１┃氏名 │得点 │計算方法│ 9│合計 ┃
┠─╂────┼────┼────┼────┼────┨
┃２┃大場佳代│ 1200│ │ 1│平均 ┃
┠─╂────┼────┼────┼────┼────┨
┃３┃阿部沙織│ 1600│ │ 4│最大値 ┃
┠─╂────┼────┼────┼────┼────┨
┃４┃山形三重│ 1400│ │ 5│最小値 ┃
┠─╂────┼────┼────┼────┼────┨
┃５┃高橋由美│ 1300│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃６┃本間貝菜│ 1200│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃７┃惣田ヨネ│ 1100│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃８┃ │=SUBTOTAL(C2,B2:B7) │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
Ａ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
A8のセルに =VLOOKUP(C2,D1:E4,2,0) と入力
─────────────────────────────────
・つまり、D1:E4 を検索する表として利用するということです。それさ
えわかれば、普通のVLOOKUP関数と同じです。
・VLOOKUP関数 は、指定された表の左端列と一致する値を同じ行から取
り出す役目をします。公式は、
=VLOOKUP(検索値,表の範囲,列番号,検索の型)
これまで何度も取り上げました。一番やさしい例は、122号 で説明し
ていますので、初めての方は参考にしてください。
☆━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━★
・前号と本号はセットで使うといいと思います。そうすれば、どんな計
算をしたのか一目瞭然です。計算方法を切り替えるだけでなく、項目
名も切り替えられるようにしましょう。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ＝＝Number.042＝＝│
└──────────────────────────┘
◆ 今日の問題 「複数の当たり数字から当たり・外れを表示させる」
Ｑ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
３つの当たりくじがある。当選番号の欄(B1:D1) に数字を入れると、
当選した人に「当たり」と表示するように関数を設定せよ。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃Ａ │Ｂ │Ｃ │Ｄ │Ｅ ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃１┃当選番号│ 4│ 21│ 34│ ┃
┠─╂────┼────┼────┼────┼────┨
┃２┃ │ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃３┃番号 │氏名 │結果 │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃４┃ 1│飯田沙織│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃５┃ 2│安田恵子│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃６┃ 3│阿部冬美│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃７┃ 4│矢口麻衣│当たり │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃８┃ 5│吉沢仁美│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃９┃ 6│辻村望美│ │ │ ┃
〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜
〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜
┠─╂────┼────┼────┼────┼────┨
┃23┃ 20│後藤麻里│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃24┃ 21│駕籠亜生│当たり │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃25┃ 22│石川里奈│ │ │ ┃
〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜
〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜
┠─╂────┼────┼────┼────┼────┨
┃37┃ 34│中沢綾子│当たり │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃38┃ 35│高橋愛乃│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃39┃ 36│小川真子│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃40┃ 37│新関理沙│ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
Ａ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
C4のセルに =IF(OR(A4=$B$1,A4=$C$1,A4=$D$1),"当たり","")
フィルハンドルをダブルクリック
─────────────────────────────────
・B1、C1、D1のいずれかが一致していれば当たりですから、OR関数を使
います。OR関数の意味は「または」。つまり、部分一致していればい
いということです。
・注意していただきたいのは、コピーする関係上、当選番号が動かない
ように絶対参照にすることです。そうしないと、B1、C1、D1の部分が
スライドしてしまいます。（絶対参照については参考を見てください）
・他にも、=IF(COUNTIF($B$1:$D$1,A4),"当たり","")でもＯＫ。これは、
COUNTIF関数で、0 以外は当たりを 0 の場合は空白をということです。
こちらならば、一致した場合は、1 になりますので当たりが表示され
ます。
◆参考◆ 絶対参照と相対参照について
-----------------------------------------------------------------
絶対参照とは、フィルハンドルでコピーしても値の変化しない参照方
法です。例えば、「$A$1」ように、$ マークをつけると、その部分は
変化しません。「$A1」や「A$1」のように片方だけ固定することもで
きます。
それに対して、通常使っているセル参照が、相対参照で、単に、A1の
ように表記します。これは、フィルハンドルでコピーすると、相対的
に変化します。
絶対参照を作りたいときは、直接 $マークをつけもいいのですが、と
りあえず、A1と入力した時点で、F4キーを押すと、自動的に絶対参照
になります。また、F4キーを続けて押すと、片方だけ固定など、いろ
いろなパターンが順番に現れますから、試してみてください。
F4キーは、関数の貼り付けボックスを使い、数式パレットを使ってい
るときも有効です。また、後から、その部分をクリックして、変更す
ることも可能ですので十分利用してください。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ＝＝Number.043＝＝│
└──────────────────────────┘
◆ 今日の問題 「最新の顧客を５人別表示にする」
Ｑ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下図のように、Ａ列に予約客の名前を次々書いていき、最新の５名を
C2からC6までに表示させるようにしたい。C2からC6に関数を設定せよ。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃Ａ │Ｂ │Ｃ │Ｄ │Ｅ ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃１┃氏名 │ │最新５名│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃２┃石田 │ │二瓶 │←現在の一番下 ┃
┠─╂────┼────┼────┼────┼────┨
┃３┃小山 │ │千田 │←下から２番目 ┃
┠─╂────┼────┼────┼────┼────┨
┃４┃酒井 │ │星川 │←下から３番目 ┃
┠─╂────┼────┼────┼────┼────┨
┃５┃大牟田 │ │西山 │←下から４番目 ┃
┠─╂────┼────┼────┼────┼────┨
┃６┃原島 │ │中 │←下から５番目 ┃
┠─╂────┼────┼────┼────┼────┨
┃７┃黒田 │ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃８┃中 │ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃９┃西山 │ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃10┃星川 │ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃11┃千田 │ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃12┃二瓶 │ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃13┃ │ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃14┃ │ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃15┃ │ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃16┃ │ │ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
Ａ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
C2のセルに、=INDIRECT("a"&COUNTA(A:A)-ROW()+2) と入力
フィルハンドルで、C6までコピー
─────────────────────────────────
・今回は、INDIRECT関数を使う方法を採用しました。INDIRECT関数は、
これまで何度か取り上げましたが、
=INDIRECT(文字列としてのセル番地)
で、セル番地の内容を表示します。ですから、
=INDIRECT("A1")
と、ダブルクォーテーション("")で、くくると、文字列と認識される
ため、A1のセルの内容を表示します。
注意していただきたいのは、=INDIRECT(セル番地)との区別です。
=INDIRECT(A1)
とやると、今度は、A1のセルの内容ではなく、A1のセルに入っている
セル番地の内容を返します。例えば、A1のセルに、B2と入っていれば、
B2の内容を表示するということです。この違いを理解しましょう。
・ここでは、まず、「a」という文字列に、COUNTA関数を使って、A列の
総数を数えます。そうすることにより、最終行のセル番地を取得する
ことができます。（※「A:A」は、Ａ列全体という意味）
例題でいうと、Ａ列の総数は、12ですから、a&12 で、A12ということ
になります。
・その後ろについている、ROW()+2 は、そのままコピーするための工夫
です。わかりやすく式を作ると
C2のセル =INDIRECT("a"&COUNTA(A:A)-0) １番下のセル
C3のセル =INDIRECT("a"&COUNTA(A:A)-1) 下から２番目のセル
C4のセル =INDIRECT("a"&COUNTA(A:A)-2) 下から３番目のセル
C5のセル =INDIRECT("a"&COUNTA(A:A)-3) 下から４番目のセル
C6のセル =INDIRECT("a"&COUNTA(A:A)-4) 下から５番目のセル
ということです。しかし、一つ一つ式を作るのが面倒ですので、行番
号を表す ROW関数を使い、引き算の部分と一致させます。
C2のセル ２行目にある → =ROW()は、2 → 2-2=0
C3のセル ３行目にある → =ROW()は、3 → 3-2=1
C4のセル ４行目にある → =ROW()は、4 → 4-2=2
C5のセル ５行目にある → =ROW()は、5 → 5-2=3
C6のセル ６行目にある → =ROW()は、6 → 6-2=4
このように、ROW関数 をうまく使うことにより、一つの式をコピーす
ることができます。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ＝＝Number.044＝＝│
└──────────────────────────┘
◆ 今日の問題 「常時、年間の残り日数を数える」
Ｑ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
本日から、今年あと何日と表示させたい。関数を設定せよ。ただし、
年が変わっても自動的に表示できるようにすること。
Ａ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
任意のセルに、=DATEDIF(TODAY(),DATE(YEAR(TODAY()),12,31),"D")
と入力
─────────────────────────────────
・１年が 365日と決まっていれば、引き算を使う方法もありますが、閏
年がある以上、それに対応できるようにしなければなりません。
今回の関数の構造は、基本的には、DATEDIF 関数を使って年末までの
日数を数えるものです。
DATEDIF関数は、期間を求める関数で、公式は
=DATEDIF(開始日,終了日,単位)
単位は、「年」ならば "Y"、「月」ならば "M"、「日」ならば "D"を
使います。ここでは、日にちを求めるわけですから、"D" を使います。
解答の関数式は、おおまかに言うと、下のようになります。
=DATEDIF(開始日,終了日,単位)
↓ ↓
今日 その年の最後の日
↓ ↓
TODAY() DATE(YEAR(TODAY()),12,31)
今日の日付は、TODAY関数を使えば、常に、本日の日付を表せます。
問題は、終了日ですが、その年の最後の日は、DATE関数をうまく使う
ことにより可能になります。
DATE関数は、=DATE(年,月,日) で、日付を作成します。
ですから、=DATE(2006,12,31) で、2006/12/31 という日付データを
作ることができるのです。
今回の問題は、年が変わっても対応できるようにしなければならない
ので、年の部分を少し工夫する必要があります。
そこで、YEAR(TODAY()) で、本日の「年」を抽出します。これにより
年が変わっても自動的に、その年の「年」になるので、常に、その年
の残り日数を数えることができるのです。
☆━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━★
・この式をどこかに入れておけば、常に年間カウントダウンを作ること
ができます。12月31日には、0 が表示され、元旦になると、364(閏年
ではない場合）が、自動的に表示されます。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ＝＝Number.045＝＝│
└──────────────────────────┘
◆ 今日の問題 「常時、月間の残り日数を数える」
Ｑ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
今月は、残り何日と表示させたい。関数を設定せよ。ただし、月が変
わっても自動的に表示できるようにすること。
Ａ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
任意のセルに、=DATEDIF(TODAY(),EOMONTH(TODAY(),0),"D")と入力
─────────────────────────────────
・前回は、年間のカウントダウンでしたが、今回は、月間のカウントダ
ウンです。考え方は同じです。まずは、DATEDIF関数を使います。
DATEDIF関数は、期間を求める関数で、=DATEDIF(開始日,終了日,単位)
でした。
（詳しくは http://www.pat.hi-ho.ne.jp/hirosilk/fc041.htm#44 ）
・今回の関数式の構造は、
=DATEDIF(開始日,終了日,単位)
↓ ↓
今日 その月の最後の日
↓ ↓
TODAY() EOMONTH(TODAY(),0)
EOMONTH関数は、指定した開始日から、指定した月数の月末の日付を
返す関数です。ここでは、当月の最後の日付が返されます。
この関数は、「分析ツール」がアドインされていないと使用できませ
ん。［ツール］−［アドイン］で「分析ツール」にチェックを入れて
ください。
詳しくは、↓を参考にしてください。
http://www.pat.hi-ho.ne.jp/hirosilk/fc031.htm#k036
つまり、常に、その日から、その月の末日までの日数を数えることが
できます。一つ式を作っておけば、月が変わっても、ずっと使えるの
です。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ＝＝Number.046＝＝│
└──────────────────────────┘
◆ 今日の問題 「一つ上のセルとの増減を計算する」
Ｑ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下の図のように、一つ前のセルとの増減を示したい。B3に関数を設定
せよ。ただし、行の削除を行っても式が壊れないようにすること。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃Ａ │Ｂ │Ｃ │Ｄ │Ｅ ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃１┃数値 │増減 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃２┃ 1000│ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃３┃ 1200│ 200│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃４┃ 1300│ 100│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃５┃ 1200│ -100│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃６┃ 1200│ 0│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃７┃ 1300│ 100│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃８┃ │ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃９┃ │ │ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
Ａ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
B3のセルに =IF(ISBLANK(A3),"",A3-OFFSET(A3,-1,0)) と入力
必要な分だけ、フィルハンドルでコピー
─────────────────────────────────
・まず、Ａ列のセルが空白の場合、右隣のセルを空白にする処理をしま
す。今回は、ISBLANK 関数を使ってみました。もし、A3のセルが空白
ならば、空白を。そうでなければ、A3-OFFSET(A3,-1,0,1,1)の処理を
しなさいという意味です。
問題の表は、Ａ列のセルに数字を入れたらＢ列に増減が現れなければ
意味がありませんから、この処理は必須です。ISBLANK 関数を使う代
わりに =IF(A3="","",A3-OFFSET(A3,-1,0)) としてもかまいません。
・今回の主眼は、OFFSET関数です。この関数は、基準となるセルを中心
として、そのセルからの距離でセル番地を取得します。
公式は、=OFFSET(基準,行数,列数,[高さ],[幅]) です。ちょっと複雑
ですので、一つ一つ解説します。
基準：元になるセル番地（ここから距離をはかります）
行数：行単位で動かす値（どのように動かすのかを数値で指定）
0 動かさない
正の数 下方向へ（「1」ならば１つ、「2」ならば２つ下へ）
負の数 上方向へ（「-1」ならば１つ、「-2」ならば２つ上へ）
列数：列単位で動かす値（どのように動かすのかを数値で指定）
0 動かさない
正の数 右方向へ（「1」ならば１つ、「2」ならば２つ右へ）
負の数 左方向へ（「-1」ならば１つ、「-2」ならば２つ左へ）
高さ：選択する行数を指定（省略可能、省略のときは「1」）
幅 ：選択する列数を指定（省略可能、省略のときは「1」）
つまり、基準になるセルの一つ上の場合、行数は「-1」、列数は「0」
になります。
したがって、OFFSET(A3,-1,0) の部分の意味は、A3の一つ上のセル、
つまり、A2ということになります。
この設定にすると、（基準のセル）−（一つ上のセル）という計算が
できるようになり、行を削除しても計算が狂いません。
☆━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━★
・今回の方法、単純に考えると、=IF(ISBLANK(A3),"",A3-A2) でもいい
ように思われます。もちろん、それでも正しく計算はされます。しか
し、行を削除すると、参照するセル番地がなくなるため、エラーにな
ってしまいます。その辺の違いを理解してください。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ＝＝Number.047＝＝│
└──────────────────────────┘
◆ 今日の問題 「階乗を求める」
Ｑ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
任意の数の人間を１列に並べたい。下図のように、A2に人数を入れた
ら、B2に順序の総数が入るように関数を組め。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃Ａ │Ｂ │Ｃ │Ｄ │Ｅ ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃１┃人数 │順序総数│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃２┃ │ │ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
Ａ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
B2のセルに =FACT(A2) と入力
─────────────────────────────────
・これは、この関数を知っているかどうかという問題です。FACT関数は、
階乗を求める関数です。ｎ個のものからｎ個を取る順列と同じことで
すが、これを知っていると、かなり便利。
・例えば、６人を並べる並べ方は、何と 720通りもあります。数字を入
れて確認してください。組合わせと違い順序も考慮も入れると、一気
に数字が増えるのです。ちなみに、10人ならば、3628800 恐ろしい数
字になります。
☆━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━★
・数字のマジックってすごいものですね。４人ならば、24通りなのに、
５人になると、一気に 120通りになる。並べるという行為は、かなり
大変な作業だと実感します。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ＝＝Number.048＝＝│
└──────────────────────────┘
◆ 今日の問題 「２進数を調べる」
Ｑ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下図のように、10進数の数字を２進数にしたい。関数を設定せよ。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃Ａ │Ｂ │Ｃ │Ｄ │Ｅ ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃１┃10進数 │２進数 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃２┃ 10│ 1010│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃３┃ 100│ 1100100│ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
Ａ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
B2のセルに =DEC2BIN(A2) と入力
フィルハンドルでコピー
（※）この関数は、分析ツールがオンになっていないと使用できませ
ん。［ツール］−［アドイン］から「分析ツール」にチェック
を入れてください。
─────────────────────────────────
・DEC2BIN 関数は、10進数を２進数に変換する関数です。公式は、
=DEC2BIN(数値あるいはセル番地)
です。ただし、511 までの数字しか変換されません。それ以上はエラ
ーになります。（10ビットまでしか変換できないため）
┌──────────────────────────┐
│【関数コレクションシリーズ】 ＝＝Number.049＝＝│
└──────────────────────────┘
◆ 今日の問題 「上位○％のタイムを求める」
Ｑ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下図のように、E1のセルに、0から1の間の数字を入れて、上位○％に
あたるタイムを表示させたい。E2のセルに関数を作成せよ。
（ 0は最低、1は最高タイムを表し、0.2なら上位20％相当のタイム、
0.5なら平均タイムを表すものとする）
┏━┳━━━━┯━━━━┯━━━━┯━━━━━┯━━━━┓
┃ ┃Ａ │Ｂ │Ｃ │Ｄ │Ｅ ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━━┿━━━━┫
┃１┃氏名 │タイム │ │上位○％ │ 0.2┃
┠─╂────┼────┼────┼─────┼────┨
┃２┃村崎色部│ 1:02:12│ │該当タイム│ 1:05:20┃
┠─╂────┼────┼────┼─────┼────┨
┃３┃清原納言│ 1:02:56│ │ │ ┃
┠─╂────┼────┼────┼─────┼────┨
┃４┃因幡福子│ 1:03:12│ │ │ ┃
┠─╂────┼────┼────┼─────┼────┨
┃５┃山内百代│ 1:04:02│ │ │ ┃
┠─╂────┼────┼────┼─────┼────┨
┃６┃木下音々│ 1:04:12│ │ │ ┃
┠─╂────┼────┼────┼─────┼────┨
┃７┃前田 梅│ 1:05:00│ │ │ ┃
┠─╂────┼────┼────┼─────┼────┨
┃８┃浅井市代│ 1:05:33│ │ │ ┃
┠─╂────┼────┼────┼─────┼────┨
┃９┃京極初代│ 1:06:33│ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━━┷━━━━┛
Ａ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
E2のセルに =PERCENTILE(B2:B9,1-E1) と入力
E2のセルの表示形式を h:mm:ss にする
─────────────────────────────────
・今回扱ったのは、PERCENTILE関数です。この関数は、百分率で、率に
位置する値を返します。
公式は、=PERCENTILE(セル範囲,率) 率は、1を100％とし、小数で指
定します。
・今回は、タイムを扱っていますので、タイムの良いほう、つまり、数
値の低いほうを上位としますので、１からE1の数値を引く形にしまし
たが、点数などを扱う場合など、数値の大きいほうを上位にしたい場
合は、そのまま、率を入れるだけで大丈夫です。
・それから、表示形式は、ユーザー定義を使って「時：分：秒」の形に
しました。解答には書きませんでしたが、念のために、操作を確認し
ておきます。
［書式］−［セル］から「表示形式」のタブを開く
「分類」から「ユーザー定義」を選ぶ
「種類」から「h:mm:ss」を選ぶ
（ない場合は、種類の上の細いボックスに直接入力してください）
・この関数は、今回の例のようにタイムの目安に使ったり、率で合格を
決める場合などに使えます。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ＝＝Number.050＝＝│
└──────────────────────────┘
◆ 今日の問題 「相対的にポイントを付ける」
Ｑ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下図のように得点が記された表がある。これに対し、百分率でポイン
トを付けたい。Ｃ列に関数を設定せよ。
（下図のように、最も得点が高いものを「100」最も低いものを「0」
として、残りを相対的に記してください）
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃Ａ │Ｂ │Ｃ │Ｄ │Ｅ ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃１┃氏名 │得点 │ポイント│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃２┃広山晴海│ 4840│ 71.4│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃３┃高橋直子│ 8230│ 85.7│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃４┃渋井洋子│ 4020│ 57.1│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃５┃野口瑞樹│ 8800│ 100│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃６┃戸佐玲子│ 2100│ 28.5│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃７┃千葉政子│ 1840│ 14.2│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃８┃増田朱実│ 1680│ 0│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃９┃有盛悠子│ 2400│ 42.8│ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
Ａ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
C2のセルに =PERCENTRANK($B$2:$B$9,B2)*100 と入力
フィルハンドルでコピー
─────────────────────────────────
・今回は、PERCENTRANK 関数です。この関数は、順位を相対的につける
関数で、公式は次の通りです。
=PERCENTRANK(セル範囲,セル番地)
（※）セル範囲は絶対参照で設定してください
この関数は、範囲内で、その値が全体の何％に値するのかを調べるこ
とができます。
・通常のRANK関数は、順位しか出ませんが PERCENTRANK関数は、相対的
な位置が出ますので、例えば、上位60％が合格というような計算をす
る場合は便利です。
・尚、今回は、ポイント計算ということで、全体に 100をかけましたが、
かけないで、表示形式を％にしてもかまいません。
| ★ エクセルの小技へ ★ | ★ TOPのページへ ★ | ★ 便利技（001〜010）へ ★ |