2014/10/24

テーブルのすすめ VLOOKUP関数

Office 2007 以降で最も重要な機能追加は「テーブル機能」である。
今後 Office 365 にシフトすればするほどその重要性は増すが、そもそも「テーブル機能」がどれほど強力に Excel を使った業務を支援するか、という点を今回は VLOOKUP関数から紹介したい。

VLOOKUP関数
VLOOKUP(検索値,範囲,列番号,検索の型)

この構文から、[範囲] と [列番号] をどう扱うかは Excel ユーザーにとっては定番ネタである。

たとえば、Microsoft のサイトでは Excel 2003 向けの記事として以下のようなものがある。

VLOOKUP, MATCH および INDEX を使用した動的検索

参照するリストの行が増えたら範囲を変更しなくてはならない、参照するリストの途中に列を追加したら列番号を変更しなければならない、というものだ。これをワークシート関数を使って自動的に行うのが上記でいう「動的検索」である。

Excel 2007 以降は、参照先リストをテーブルに変換することで、範囲は「テーブル名」となり、行が追加されても追随する。列番号をとるのに列名の文字列を MATCH 関数を使い [#見出し] の構造化参照からとる方法を紹介している記事もいくつかあるが、テーブルの「構造化参照」の特徴を使うのであれば、COLUMN関数で構造化参照の列名を指定するのが「テーブル機能」らしい使い方だ。(一番下の(追記)も参照されたい)

VLOOKUP(検査値, テーブル名, COLUMN(列名), 検索の型)


上記で「構造化参照」と言っているが、関数を「関数の挿入ダイアログ」を使って入力していたらその便利さを見ることはないだろう。関数の挿入ダイアログは今となっては使ってはいけないのだ。なぜなら構造化参照を使えないからである。

テーブルはブックの中でオブジェクトとして扱われ、そのテーブルの中にある列もまたオブジェクトとして扱われる。残念ながら日本語の Excel の場合は作成したテーブルに「テーブル1」といった日本語名が付いてしまうので、関数入力時に構造化参照の恩恵を受けられづらいのだが、table1 など英語表記にすると入力の手順が相当変わる。以下、その手順を追ってみよう。

1) 表を選択して [挿入] タブの [テーブル] をクリックし、テーブルの作成ダイアログで [先頭行をテーブルの見出しとして使用する] にチェックがあるのを確認して [OK] を押す。

2) 作成したテーブルの名前を table1 に変更する

3) 社員番号から名前を VLOOKUP 関数で取得する。その際の操作は以下となる。



最終的な数式は以下となった。

=VLOOKUP(E2,table1,COLUMN(table1[名前]),FALSE)

範囲指定の際に "ta" と打っただけで ta から始まる関数とテーブル名がリストされる。COLUMN関数の引数でも "ta" と打って table1 を選び、その直後に "[" を入れると table1 の列名がリストされるので、そこから [名前] を選び、"]" で閉じる。

これが構造化参照であり、テーブルを使うべき理由のひとつである。

Excel 2003 までの鉄板ネタ(INDEX/MACTHを使ったテクニック)は、Excel 2007 以降はテーブルによって克服され、使い勝手のよい、見やすい数式になっているのである。

なお、テーブル名で日本語が使えないわけではない。IME 入力による日本語入力のため数式オートコンプリートがすぐに反映しないだけである。「顧客テーブル」というテーブル名を付けた場合は、範囲名で「顧客テーブル」と入れればテーブル名として認識される。

(追記) COLUMN(テーブル名[列名]) が有効なのは、テーブルの領域が A1 から始まっている場合である。テーブルのようなデータの持ち方の場合は A1 からデータが入力されるが、もし、A列をあけて、1行名をあけるようなデータの持ち方をしている場合や1つのワークシートの複数のテーブルを作った場合は期待した結果にならないことに注意していただきたい。その場合は MATCH 関数を使って [#見出し] の配列から列番号をとることになるが、「テーブル」というデータを扱うのであれば、1シート1テーブル、A1 から始める、としたほうがトラブルは少ない。

[PR] VBAセミナー ベーシック、スタンダードを受講後は、これさえあれば何もいらない
 

0 コメント:

コメントを投稿

Powered by Blogger.

自己紹介


PowerBI コミュニティ勉強会の 沼口 です。
https://powerbi.connpass.com/
最近の Excel は Office 365 のクラウドサービスと 連携する方向性が打ち出されています。この「Road to Cloud Office」ブログでは、Excel ユーザーの視点から Power BI Service や、Office 365 の活用方法を模索した結果をお伝えしています。
Microsoft MVP for Data Platform 2017-2018