2014/11/04

テーブルのすすめ 構造化参照

Excel 2007 以降で最も重要な機能は「テーブル機能」である。

参照先のデータとしてテーブルを使うとデータの増減に対応できる。この増減に対応するための記述方法が「構造化参照」であり、これまでの A1:C4 や $A$1 などの参照方法と違う。

構造化参照の利用は難しくない

ただ、さすが Excel である。普通の使い方をしていても、あまり意識せずに構造化参照を使うことができる。


[手数料金額]列の1行目に「 = 」入力のあとにカーソルを動かし、同じ列の [売上金額] と [手数料] を選ぶと、構造化参照された [@売上金額]、[@手数料(%)] が入力される。 @の意味は「この行の」という意味である。そして2行目以下の[手数料金額]に同じ数式がすべてに挿入される。

テーブルの外のセルから参照すると「テーブル名」が自動挿入されることもわかるだろう。
そして、列全体を選べば 「 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 キーを押すことで相対参照, 絶対参照の切り替えができる。

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