月別アーカイブ: 2006年9月
データテーブルの上から何番目,左から何番目かを指定して該当するデータを取り出す(index関数)
2006年9月10日 よく使う関数
新橋と神谷町との間にあるうなぎ屋「本丸」です。
知る人ぞ知る老舗のようです。お昼の時間帯しかやってませんが、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)
2006年9月4日 よく使う関数
例えば、
[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関数)
2006年9月3日 よく使う関数
例えば、
[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」と入れる場合との違いは 次回説明します。