参照先のデータとしてテーブルを使うとデータの増減に対応できます。この増減に対応するための記述方法が「構造化参照」であり、これまでの A1:C4 や $A$1 などの参照方法と違います。
構造化参照の利用は難しくない
ただ、さすが Excel です。普通の使い方をしていても、あまり意識せずに構造化参照を使うことができます。
[手数料金額]列の１行目に「 = 」入力のあとにカーソルを動かし、同じ列の [売上金額] と [手数料] を選ぶと、構造化参照された [@売上金額]、[@手数料(%)] が入力されます。 @の意味は「この行の」という意味です。そして２行目以下の[手数料金額]に同じ数式がすべてに挿入されます。
テーブルの外のセルから参照すると「テーブル名」が自動挿入されることもわかるでしょう。
そして、列全体を選べば 「 DeptSales[手数料金額] 」 や 「 DeptSales[売上金額] 」、「 DeptSales[営業担当者] 」を参照します。列名をいれると、その列にあるすべてのデータ行を選択します。（これを完全修飾といいます）
この構造化参照式を理解すると、カーソルで範囲を選らばずに列全体を参照する式をいれて、合計やカウントを出すことができます。もちろん、数式オートコンプリートが入力を支援してくれます。
なお、数式オートコンプリートのドロップダウン リストからの選択は下矢印、上矢印のキーを使って移動し、[TAB] キーで確定します。
集計行をテーブルの一番下に追記することをすでに紹介しましたが、その集計行の値を構造化参照で参照することも可能です。これはオートフィルターの結果を取得するのに有効です。（以下のように同じワークシート内で参照することは少なく、出力用・報告用の別ワークシートから参照することが多いでしょう。）
これらの構造化参照式は以下のマイクロソフトのページにくわしく解説されています。
翻訳が中途半端なので読みづらいですが、だいたいの概要はわかると思います。
Excel テーブル数式で構造化参照を使う
http://office.microsoft.com/ja-jp/excel-help/HA102749547.aspx
ただ、構造化参照式を直接入力しなくても、テーブルのセル範囲を選択したとき、それが構造化参照できるようであれば自動的に構造化参照式が入力されるので、あまり難しく考える必要はありません。そのような式が数式に入力された時に驚かないことのほうが重要です。
構造化参照を使いたい関数
すでに VLOOKUP 関数で構造化参照の使い方を紹介しましたが、他にも構造化参照の恩恵を受ける関数があります。「範囲」や「列番号」を使う関数は構造化参照の利用を検討してみるとよいでしょう。
MATCH 関数 http://office.microsoft.com/ja-jp/excel-help/HA102752945.aspx
検査範囲で構造化参照を使います。ただし、列名を検査して列番号を取得したい場合は、COLUMN 関数と構造化参照を組み合わせたほうがシンプルで見やすいでしょう。
テーブルでの使用用途は検索したい文字列/数値を列全体に対して行い、行数を取得するパターンです。ただし、VLOOKUP 関数同様、検査対象列に一意のデータ（ユニークなデータ）が入力されていれば簡単ですが、同じデータが存在する場合は格段に利用（数式）が難しくなります。本当に関数でやらなければいけないのか、テーブルのオートフィルターを使って目視での絞込みによる利用ができないかを検討してください。また、VBA はこのようなケースが得意です。
INDEX 関数 http://office.microsoft.com/ja-jp/excel-help/HA102752976.aspx
MATCH 関数で行番号が分かれば、次に使うのは INDEX 関数です。
列番号は上述のように COLUMN 関数を使えば、行と列で指定したセルの内容を取得できます。
また、対象となる列が決まっている場合は以下のように記述できます。
DeptSales テーブルの営業担当者列の3番目（3行目）を取得
（この "3" をMATCH関数で取得するパターンが多い）
=INDEX(DeptSales[営業担当者], 3)
SUMIF および SUMIFS 関数
SUMIF 関数 http://office.microsoft.com/ja-jp/excel-help/HP010062465.aspx
SUMIFS 関数 http://office.microsoft.com/ja-jp/excel-help/HA102753226.aspx
地域が南西部（A1)の売上金額の合計を求めているのが以下です。
その他の使い方
行数をもとめる =ROWS(テーブル）
見出し（列名）と集計行が除外され、データ部分の行数を返します。SUBTOTAL 関数の COUNTA で列を指定した場合は、空のセル(行)はカウントされません。反面、ROWS 関数を使うと空の行もカウントされます。
列数をもとめる =COLUMNS(テーブル)
列番号をもとめる =COLUMN(テーブル[列名])
構造化参照とオートフィル
構造化参照を使った数式をオートフィルでコピーできます。
相対参照としての動きが基本です。
以下では第1四半期の合計をサマリーの表に SUM 関数を使って計算し、オートフィルで、第2、第3、第4四半期の合計をオートフィルで数式のコピーをしています。
色別の合計を出すために SUMIF 関数を使います。この時は注意が必要です。
適切でない構造化参照が以下です。
オートフィルによって、SUMIF の検索範囲を指定した dataTbl[色] が移動するためです。
この場合、dataTbl[色] を絶対参照のような指定をしなければなりません。
その指定方法が dataTbl[[色]:[色]] です。こうすることでオートフィルを使っても検索範囲が移動することはありません。
なお、テーブルに限った話ではありませんが、SUMIF 関数の検索条件の指定で、カーソルを動かして「B4」を指定し、F4 キーを押すことで相対参照, 絶対参照の切り替えができます。
テーブル関連の投稿
[テーブルとVLOOKUP] https://road2cloudoffice.blogspot.jp/2014/10/vlookup.html
[テーブルと入力規則] 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
0 コメント:
コメントを投稿