いろいろな関数 ７
今回の関数は[VLOOKUP]、[HLOOKUP]＆[LOOKUP]関数です。
[VLOOKUP] 関数
書式：「 VLOOKUP(検索値,範囲,列番号,検索の型) 」
[VLOOKUP]や[HLOOKUP]関数は、複数の検索範囲の中から列番号（行番号）を指定するだけで、検索値の
データを取り出すことができます。
税表、運賃表等段階的に（不連続に）金額が変わるものに有効です。多品目価格表などに利用できます。
その１ （検索の型 FALSEの場合)
作例は「某市町の水道料金」早見表です。
（下表をコピーしてExcelの[A1]セルに貼り付けて、ご使用ください。）
| 氏 名[A1] |
水道使用量[B1] |
使用料金[C1] |
表１列目[D1] |
表２列目[E1] |
| 山田太郎 |
18 |
|
10 |
1701 |
| 川田花子 |
15 |
|
11 |
1911 |
| 草田一郎 |
21 |
|
12 |
2121 |
| 木田一美 |
130 |
|
13 |
2331 |
| 上田春子 |
17 |
|
14 |
2541 |
| 中田夏男 |
70 |
|
15 |
2751 |
| 下田秋子 |
11 |
|
16 |
2961 |
| 天野和雄 |
14 |
|
17 |
3171 |
| 地脇智子 |
19 |
|
18 |
3381 |
| 人見賢治 |
50 |
|
19 |
3591 |
|
|
|
20 |
3801 |
|
|
|
21 |
4053 |
|
|
|
30 |
6321 |
|
|
|
50 |
11991 |
|
|
|
70 |
19551 |
|
|
|
130 |
42231 |
関数の選択
- VLOOKUP関数を実行したいセル（回答を表示したいセル）をクリックしてアクティブ状態にします。
- [fx]「関数貼り付け」ボタンをクリックします。
すると、「関数の貼り付け」ダイアログボックスが表示されます。、
- 「関数の分類（C）」から[検索/行列]、「関数名（N）」から［VLOOKUP］を選択して［OK］をクリックします。「数式パレット」ダイアログボックスが表示されます。
数式パレットの注釈
テーブルの左端列を検索し、（下図１で、「D列」です。）
指定した列（D列）と同じ行にある値（「E列」の数値）を返します。テーブルは昇順で並べておく必要があります。
検索値には配列の先頭列から検索したい値を指定します。（以上注釈説明）
では、入力にはいります。
- 「検索値」のボックスにカーソルがあるのを確認します。
- 検索したい所は「水道使用量」なのでセル[B2]をクリックします。（「検索値」に[B2]と入力されます。）
- キーボードの「Tab」キーを押します。カーソルが「範囲」に移動します。
- 範囲選択（照合範囲）は「表１列目」から「表２列目」になりますが、項目名は含めませんので[D2]から[E17]までです。
（照合範囲は列単位に作られています。[VLOOKUP]の「V」はVerticalのVで、「縦の」を意味しています。）
- 範囲を選択した状態で[F4]キーを押しますと「＄」付きの「絶対参照」になります。（$D$2:$E$17）
そうしないと、関数をオートフィル等でコピーした時に、照合範囲（参照する表の範囲）まで、一緒にずれてしまうからです。
「Tab」キーを押します。
- 列番号：
パレットの注釈では：「列番号には（範囲）の中で目的のデータが入力されている列を左端から数えた列数で指定します。」となっています。
上の「８」で選択した列を、左から「１列目」「２列目」と数えます。「２列目」にありますので[2]と入力します。
「Tab」キーを押します。
- 検索の型は「検索値と一致する値」だけを検索するので[FALSE]と入力します。
- [OK]ボタンを押します
- [Enter]キーで確定します。
- 一番最後にオートフィル機能の「フィルハンドル」で[C2]セル右下隅にマウスを当て、下にドラッグして完成します。
- 再度整理します。
- =
- VLOOKUP ： 関数名
-
- ()の中に「引数1,2,3,4」と４個入ります。
-
- 検索値 （引数１です。）
- 表から検索したい値のセル番地（ここでは、B2）
- 範囲 （引数２です。）
- 参照する表の範囲です。項目は含めません。絶対参照で入力します。
- 列番号 （引数３です。）
- 上記注釈のとおり。
- 検索の型（引数４です。）下記の決まりがあります。
- TRUE 検索値と一致する値が無いときには、検索値より小さい値の最大値が検索されます。
- FALSE 検索値と一致する値が有った場合のみ検索します。
- 検索値が連続せず、単一になっていて、それに様々な情報、データがあるような場合は、FALSEの指定が前提となります。
FALSEを指定することにより、入力ミスや間違いなどを防止することができます。
- 図１は「表が小さい」場合「参照先」の表も同一表内で処理しました。
- こらが、膨大な表になりますと。シートを別にして参照することになります。
（余談になりますが、現実私は１立方メートル刻みの表で１３０立方メートルの表を数年間使用していました。）
- 図２は別シート参照の場合の数式です。数式バーをご覧下さい。
- 上の表で「D列とE列」をコピーして、別シートに貼り付けてください。それだけで、シートを跨いだ参照が自動的に作られる筈です。
図１
図２
,
[VLOOKUP] 関数
書式：「 VLOOKUP(検索値,範囲,列番号,検索の型) 」
その２ （検索の型 TRUE の場合)
その１と全く同じ式で、手順も全く同じです。異なるところは、「検索の型」に [TRUE]を入れることです。（TRUEの場合は省略しても良いことになっています。）
TRUEの場合は、検索値と一致する値が無いときには、検索値より小さい値の最大値が検索されます。
（下表をコピーしてExcelの[A1]セルに貼り付けて、ご使用ください。）
| 氏名 |
国語 |
数学 |
英語 |
平均点 |
評価 |
| 山田太郎 |
80 |
80 |
85 |
82 |
A |
| 川田花子 |
45 |
45 |
70 |
53 |
|
| 草田一郎 |
40 |
55 |
30 |
42 |
|
| 木田一美 |
90 |
65 |
70 |
75 |
|
|
|
|
|
|
|
|
|
成績 |
評価 |
|
|
|
|
40 |
D |
|
|
|
|
50 |
C |
|
|
|
|
70 |
B |
|
|
|
|
80 |
A |
|
|
|
|
|
|
|
|
[HLOOKUP] 関数
書式：「 HLOOKUP(検索値,範囲,列番号,検索の型) 」
検索の型 TRUE の場合-入力省略可
[HLOOKUP]関数の[引数1,2,3,4]は[VLOOKUP]関数と全く同じです。
ただ、照合参照表が「横並び」になっているだけです。[HLOOKUP]のHはHorizontalのHで、
「横の」とか（組織が）「横割りの」の意味です。
また、表の「戻り値」の求め方によって検索の型の[TRUE]や[FALES]を使い分けることになります。
（下表をコピーしてExcelの[A1]セルに貼り付けて、[TRUE]型で試してください。）
| [A1] |
4月[B1] |
5月[C1] |
6月[D1] |
7月[E1] |
8月[F1] |
9月[G1] |
半年計[H1] |
| 茨城店売上 |
398765 |
423456 |
634567 |
545678 |
456789 |
767890 |
|
| 同上ランク |
|
|
|
|
|
|
|
| 栃木店売上 |
498765 |
587654 |
676543 |
465432 |
654321 |
843210 |
|
| 同上ランク |
|
|
|
|
|
|
|
| 評価基準 |
300000 |
400000 |
500000 |
600000 |
700000 |
800000 |
|
| ランク |
● |
▼ |
× |
△ |
○ |
◎ |
|
３番手になりましたが、本家本元の[LOOKUP]関数に入ります。
[LOOKUP]関数は、検査値と対応する同じ位置のセルの値を検索範囲の中から取り出す関数です。
[LOOKUP] 関数
書式：「 LOOKUP(検査値,検査範囲,対応範囲) 」
その１
作例：[LOOKUP]関数で「コード番号」から「顧客名」を表示します。
この場合、「コード」には「顧客名」だけが対応しています。
引数の選択で「検査値、検査範囲、対応範囲」選択の場合
操作手順
- 先ず、客番号にある[５]を消去してください。(念のため)
- 関数を設定する[セル]を選択（客番号の右のセル）します。
- 「関数の貼り付け」ボタン[fx]をクリックします。
- 現れたダイアログの「関数の分類(C)」から「検索/行列」、「関数名(N)」から[LOOKUP]を選択し、
｛ＯＫ｝ボタンです。
- 現れた「引数の選択」ダイアログで「検査値、検査範囲、対応範囲」を選択し[ＯＫ]ボタンです。
すると、「数式パレット」が表示されます。
- 検査値の小窓にカーソルの点滅を確認して、客番号（（請求Noの下ののセル）をクリックし、[Tab]キーです。
- 検査範囲の小窓には、コードNo.の「1〜10」の範囲選択し、[Tab]キーです。
いずれの、セル参照、セル範囲参照でも、オートフィルのコピーを必要としませんので「相対参照」で結構です。
検査範囲の対象となる番号は必ず「昇順」に並べて置くこと。
- 対応範囲の小窓には「氏名欄」の10名分の範囲選択し、[ＯＫ]ボタンです。
- [Enter]キーで確定します。
客番号の[5]を変えて見てください。名前が変ります。
（下表をコピーしてExcelに貼り付けて、ご使用ください。）
| 請求No. |
＊＊＊ |
| 5 |
|
| (上は客番号です) |
| （別表） |
| 顧客名簿 |
| コードNo. |
氏名 |
| 1 |
山田太郎 |
| 2 |
川田花子 |
| 3 |
草田一郎 |
| 4 |
木田一美 |
| 5 |
上田春子 |
| 6 |
中田夏男 |
| 7 |
下田秋子 |
| 8 |
天野和雄 |
| 9 |
地脇智子 |
| 10 |
人見賢治 |
Sheetを跨いだセル（または、セル範囲）参照
ここで、別表（となっていますから）をコピーしてSheet２に貼り付けてください。
手順５の後「検査範囲」の小窓にカーソルが移ったら、[Sheet２]をクリックして、手順６を行います。
自動的に「Sheet2!」と入力されます。
一旦「Sheet1」に戻して、「対応範囲」の小窓にカーソルを移し、また「Sheet2」をクリック、範囲を選択しますが、殆ど同じ手順でＯＫです。
[LOOKUP] 関数
書式：「 LOOKUP(検査値,配列) 」
その２
上記４の引数の選択で「検査値、配列」を選択した場合
上の手順５からになります。
- 検査値の小窓にカーソルの点滅を確認して、客番号（請求Noの下のセル）をクリックし、[Tab]キーです。
- 配列は「コードNo,」「氏名」のデータ部分の２列１０行を範囲選択します。
（別表を[Sheet2]に移動してある場合には[Sheet2]をクリックしてからです。）
- [ＯＫ]ボタンを押します。
- [Enter]キーで確定します。
例題は単純な形ですが。複雑になっても考え方は同じです。
トップへ戻る
いろいろな関数目次へ戻る
いろいろな関数８へ進む
