The Road to EXCELER ~エクセラーへの道~
ブログ
  • HOME »
  • ブログ »
  • 月別アーカイブ: 2006年9月

月別アーカイブ: 2006年9月

データテーブルの上から何番目,左から何番目かを指定して該当するデータを取り出す(index関数)

新橋と神谷町との間にあるうなぎ屋「本丸」です。

知る人ぞ知る老舗のようです。お昼の時間帯しかやってませんが、12時近くに行くとすごくいっぱい並んでいます。
写真は「鰻づくし重」。うな肝たっぷり、温泉玉子も入ってて1200円は超お得です。

゚・*:.。..。.:*・゚゚・*:.。..。.:*・゚ ゚・*:.。..。.:*・゚゚・*:.。..。.:*・゚ ゚・*:.。..。.:*・゚゚・*:.。..。.:*・゚

例えば、

[Sheet1]

A B C D E F
1 番号 氏名 レベル 難易度 得点   補正値
2 100 Dさん A 45  
3 120 Aさん B 50  
4 130 Eさん C 80  
5 140 Cさん B 95  
6 150 Bさん C 60  

という表があったとき、次の[Sheet2]の「レベル-難易度別補正表」を使って各人の得点を補正しようと思います。

[Sheet2]「レベル-難易度別補正表」

A B C D
1   A B C
2 50 40 30
3 30 20 10
4 10 5 0

例えば、Dさんの場合は、レベル「A」の難易度「難」ですから、対応表により補正値は「50」になります。
これを関数式で表し、Dさんの補正後の得点を求めるには、以下の手順で行います。

(1)[Sheet1]のF2セルに、=index(Sheet2!A1:D4,2,2) とindex関数を入力する。

(2)[Sheet1]のG2セルに、=E2+F2 と入力する。

index関数の( )内には、まずどこの範囲からデータを持ってくるのかを入れます。

上の例では、[Sheet2]の「レベル-難易度別補正表」からデータを持って来るので、その範囲「Sheet2!A1:D4」と入れます。

次に「,」で区切って、その範囲の何行目から持ってくるのか(上から何番目か)を入れ、さらに「,」で区切って、その範囲の何列目から持ってくるのか(左から何番目か)を入れます。

上の例では、レベル「A」の難易度「難」に相当するデータですから、「レベル-難易度別補正表」の上から2番目,左から2番目のデータになります。
よって、「2,2」と入れることになります。

大量にデータ処理する場合は、この「2,2」に相当する部分も関数で持って来て計算させることができます。

その方法は次回説明します。

データを探し出して持ってくる(vlookup関数)(2)

例えば、

[Sheet1]

A B C
1 番号 氏名 得点
2 100 Dさん 45
3 120 Aさん 50
4 130 Eさん 80
5 140 Cさん 95
6 150 Bさん 60
7
8 得点 コメント
9 80 よくできました
10 60 もう少し
11 40 もっとがんばりましょう

という2つの表があったとき、上の表を、番号,氏名,得点,コメントの入った1つの表にしたい場合は、以下の手順で行います。
コメントは、60点未満は「もっとがんばりまょう」,60点以上80点未満は「もう少し」,80点以上は「よくできました」と表示させるものとします。

(1)A8からB11までを範囲指定し、[データ(D)]→[並べ替え(S)]。

(2)「並べ替え」ウィンドウの「範囲の先頭行」で「タイトル行(R)」のラジオボタンにチェックし、「最優先されるキー」で「得点」を選択し、「昇順(A)」のラジオボタンにチェックして「OK」。

(3)D2セルに、=vlookup(C2,A$9:B$11,2,true) と vlookup関数を入れる。

(4)D2セルを指定し、[編集(E)]→[コピー(C)]。

(5)D2セルからD6セルを範囲指定し、[編集(E)]→[貼り付け(P)]。

vlookup関数の( )内に入れるパラメータは、先述の通りのルールに従って入れればOKですが、最後に「true」と入れることによって、検索値が見つからなくてもエラーを表示せず、次の基準にもとづいてデータを持ってくることができるようになります。

最後に「false」と入れた場合は、検索値が見つからないと「#N/A」というエラー値が表示されます。この例でいうと、Dさん,Aさん,Cさんの得点は下表に無いため、「#N/A」とエラー値が表示されることになります。
ところが、「true」と入れると、検索値が見つからなくても、検索値未満で最も大きい値が使用されます。この例でいうと、例えばDさんの得点「45」は下表にはありませんが、下表の中で、45未満で最も大きい数値「40」のコメントを持ってくることができます。

注意事項として、「true」を指定するときは、範囲の左端の列のデータは、昇順に並べ替えておかないと 正しく計算が行なわれません。
手順(1)~(4)は、そのための手続きです。

<<ガイドシートURL>>
http://www.tcat.ne.jp/~ryu/PLE/Kansu/37.pdf

データを探し出して持ってくる(vlookup関数)

例えば、

[Sheet1]

A B
1 番号 氏名
2 100 Dさん
3 120 Aさん
4 130 Eさん
5 140 Cさん
6 150 Bさん
7
8 氏名 得点
9 Aさん 50
10 Bさん 60
11 Cさん 95
12 Dさん 45
13 Eさん 80

という2つの表があったとき、上の表を、社員番号,氏名,得点の入った1つの表にしたい場合は、以下の手順で行います。

(1)C2セルに、=vlookup(A2,A$9:B$13,2,false) と vlookup関数を入れる。

(2)C2セルを指定し、[編集(E)]→[コピー(C)]

(3)C2セルからC6セルを選択する。

(4)[編集(E)]→[貼り付け(P)]

vlookup関数の( )内には、まず2つの表に共通する検索用のキーを指定します。上の例では「氏名」をキーにして「得点」を持ってきますので、「A2」と入れます。

次に「,」で区切ってどこの範囲から持ってくるのかを入れます。上の例では、上表の「氏名」に対応する得点を下表から探して持ってくる必要があるので、下表のデータの範囲「A$9:B$13」を入れます。このとき、先ほど指定した検索キーが必ず左端に来るように範囲指定する必要があります。$マークをつけているのは、下方向にコピーしたときに参照範囲が変わってしまうのを防ぐためです。

次にまたまた「,」で区切って 指定した範囲から何列目のデータを持ってくるのかを入れます。上の例では、下表の「氏名」列から数えて2列目にある「得点」を持ってくるので、「2」と入れます。

最後に「,」で区切って「false」と入れます。これは通常 最後に「false」と入れるルールになっている、と覚えておいても問題なく使えると思います。「true」と入れる場合との違いは 次回説明します。

Page 4 / 4«34

最近の投稿