今後 Office 365 にシフトすればするほどその重要性は増しますが、そもそも「テーブル機能」がどれほど強力に Excel を使った業務を支援するか、という点を今回は VLOOKUP関数から紹介したいと思います。
VLOOKUP関数
VLOOKUP(検索値,範囲,列番号,検索の型)
この構文から、[範囲] と [列番号] をどう扱うかは Excel ユーザーにとっては定番ネタですね。
たとえば、Microsoft のサイトでは Excel 2003 向けの記事として以下のようなものがあります。
VLOOKUP, MATCH および INDEX を使用した動的検索
参照するリストの行が増えたら範囲を変更しなくてはならない、参照するリストの途中に列を追加したら列番号を変更しなければならない、というものです。これをワークシート関数を使って自動的に行うのが上記でいう「動的検索」です。
Excel 2007 以降は、参照先リストをテーブルに変換することで、範囲は「テーブル名」となり、行が追加されても追随します。列番号をとるのに列名の文字列を MATCH 関数を使い [#見出し] の構造化参照からとる方法を紹介している記事もいくつかありますが、テーブルの「構造化参照」の特徴を使うのであれば、COLUMN関数で構造化参照の列名を指定するのが「テーブル機能」らしい使い方です。（一番下の(追記)も参照してください）
VLOOKUP(検査値, テーブル名, COLUMN(列名）, 検索の型）
上記で「構造化参照」と言っていますが、関数を「関数の挿入ダイアログ」を使って入力していたらその便利さを見ることはありません。関数の挿入ダイアログは今となっては使ってはいけないと言えます。なぜなら構造化参照を使えないからです。
テーブルはブックの中でオブジェクトとして扱われ、そのテーブルの中にある列もまたオブジェクトとして扱われます。残念ながら日本語の Excel の場合は作成したテーブルに「テーブル１」といった日本語名が付いてしまうので、関数入力時に構造化参照の恩恵を受けられづらいのですが、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 から始める、としたほうがトラブルは少ないでしょう。
テーブル関連の投稿
[テーブルと入力規則] https://road2cloudoffice.blogspot.jp/2014/10/blog-post.html
[テーブルと集計行] https://road2cloudoffice.blogspot.jp/2014/10/blog-post_31.html
[テーブルと構造化参照] https://road2cloudoffice.blogspot.jp/2014/11/blog-post.html
[テーブルとピボット] https://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html
[テーブルとVBA] https://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html
[PR] VBAセミナー ベーシック、スタンダードを受講後は、これさえあれば何もいらない
=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 から始める、としたほうがトラブルは少ないでしょう。
テーブル関連の投稿
[テーブルと入力規則] https://road2cloudoffice.blogspot.jp/2014/10/blog-post.html
[テーブルと集計行] https://road2cloudoffice.blogspot.jp/2014/10/blog-post_31.html
[テーブルと構造化参照] https://road2cloudoffice.blogspot.jp/2014/11/blog-post.html
[テーブルとピボット] https://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html
[テーブルとVBA] https://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html
[PR] VBAセミナー ベーシック、スタンダードを受講後は、これさえあれば何もいらない
0 コメント:
コメントを投稿