やりたいこと
タイトルのとおりなのですけれども、言い方を変えれば次のように言えますの。
- 対応表から値を引っ張ってくる
- マスタの項目名に対応する値を自動的に入力する
- セル範囲の1列目を検索して、対応する2列目の値を結果として返す
具体的には、人物とその都道府県のリストがありますの。各人物に荷物を送りたいので都道府県に対応した配送料を表示したいですわ。
都道府県を見て送料を手打ちしてもできますけれども、面倒ですの！それに、間違えや入力後のチェックも面倒ですの！
計算式でパパっと表示して、オートフィルで残りも一気に終わらせる方法をノートいたします。
ポイント
- VLOOKUP 関数を使用する（エクセルでも Google スプレッドシートでも可能）
- 検索範囲は「絶対参照」で指定する（オートフィル時に楽）
VLOOKUP 関数を実際に使って「入力した都道府県に対応する送料を自動的に表示する」手順の解説
まずは、条件ですの。
- 都道府県は C列。今回は C6 を例とする
- D6 に C6 に入力された都道府県「群馬県」に対応する送料「1030」円を自動表示させる
- 都道府県一覧表の、B18 〜 C64 に都道府県と送料の対応が入力されている
では実際に VLOOKUP 関数を使用して入力内容に対応する 値を自動的に表示させていきましょう。
- D6 セルを選択し、「=VLOOKUP(」と入力
- 第1引数として、「検索キー」となる「C6」を指定。検索キーとは検索する値「群馬県」のこと。
- 第2引数として、「範囲」となる「B18:C64」を指定。この範囲の先頭列の中から「検索キー」で指定した「群馬県」を検索する。
- 第3引数として、「2」を入力。「指数」という項目だが、検索がヒットしたとき、何番目の列の値を返すかを指定。「範囲」 の先頭列を 1 とする。
よって、1列目が都道府県、2列目が送料である今回は、送料を表示させたいので2列目の「2」を入力した。 - 第4引数として、「false」を入力。検索対象の列（指定した範囲の先頭列）が並べ替え済みなら TRUE を指定。TRUE の場合は、「検索キー」に最も近い値が返されます。
ソートはしていないので FALSE を指定する。
以上で D6 セルへの入力が完了し、「群馬県」に対応する送料「1030」が自動的に表示されましたの♪
続いて、オートフィルで残りの行も対応する送料を表示させていきましょうね。
- D6 セルの第2引数「範囲」の「B18:C64」のどこかにカーソルを合わせる。
- 「F4」キーを押し、「B18:C64」→「$B$18:$C$64」と絶対参照にする。
- D6 セル右下の「■」をドラッグし、都道府県に対応する配送料を表示したい一番下のセルまでオートフィルする。
単純にオートフィルしてはいけませんの！そうしますと範囲「B18:C64」がずれていってしまいますの＞＜。
ですから、「B18:C64」→「$B$18:$C$64」と絶対参照に変更しております。この一手間がとても重要ですの。詳しくは以前取り組みましたこちらの投稿をご参考までに♪
VLOOKUP のヘルプ引用！
全体像をパッと確認したい時のために、VLOOKUP 関数の仕様をここに引用しておきますの♪
例VLOOKUP(10003, A2:B26, 2, FALSE)概要垂直方向の検索です。範囲の 1 列目で指定したキーを垂直方向に検索し、同じ行内にある指定したセルの値を返します。
検索キー検索する値です（例: 42、”ネコ”、I24）。
範囲検索対象の範囲です。範囲の先頭列で [検索キー] で指定したキーを検索します。
指数値を返す列の指数です。[範囲] の先頭列を 1 とします。
並べ替え済み – [任意]検索対象の列（指定した範囲の先頭列）が並べ替え済みであるかを指定します。TRUE の場合は、[検索キー] に最も近い値が返されます。
LOOKUP 関数を使用しない理由
- LOOKUP の配列に入力されている値はコード順の昇順に配置されている必要がある。
LOOKUP 関数は難ありのため使用しませんの。検索範囲のデータがソートされていないと正常に動作しませんの。
Excel 2013 のヘルプでは次のような事が書かれておりました。
重要array 内の値は、昇順に並べておく必要があります。たとえば、数値は -2、-1、0、1、2 の順、アルファベットは A から Z の順、ブール型の値は FALSE、TRUE の順とします。そうでない場合、LOOKUP 関数では正しい値を見つけることができません。また、文字列の大文字と小文字は区別されません。
また、次は Google スプレッドシートのヘルプからの引用ですわ。
LOOKUP関数は、search_rangeまたはsearch_result_arrayのデータを並べ替えると正常に動作しません。データを並び替えていない場合は、VLOOKUP、HLOOKUP、または他の関連する関数を使用します。
検索対象をソートしてしまいますと、都道府県の並びが人間にとって見辛い表になってしまいます。不便ですからできるだけソートしたくありません。ですので LOOKUP 関数は使用いたしませんでした。
最後に、おまけ
配送料はこちらを使用させていただきました。ゆうパックですの。ありがとう存じます！
また、今回の例で使用した都道府県の一覧を掲載いたしますの。都道府県だけの表って、意外とパッと見つかりませんの！
| 北海道 |
| 青森県 |
| 岩手県 |
| 秋田県 |
| 山形県 |
| 宮城県 |
| 福島県 |
| 茨城県 |
| 栃木県 |
| 群馬県 |
| 埼玉県 |
| 千葉県 |
| 東京都 |
| 神奈川県 |
| 山梨県 |
| 新潟県 |
| 長野県 |
| 富山県 |
| 石川県 |
| 福井県 |
| 静岡県 |
| 愛知県 |
| 三重県 |
| 岐阜県 |
| 滋賀県 |
| 京都府 |
| 大阪府 |
| 兵庫県 |
| 奈良県 |
| 和歌山県 |
| 鳥取県 |
| 岡山県 |
| 島根県 |
| 広島県 |
| 山口県 |
| 香川県 |
| 徳島県 |
| 愛媛県 |
| 高知県 |
| 福岡県 |
| 佐賀県 |
| 大分県 |
| 熊本県 |
| 長崎県 |
| 宮崎県 |
| 鹿児島県 |
| 沖縄県 |
以上です。