スポンサーリンク
以前に、Excelのテーブルの「構造化参照式」について簡単に説明した。
その時は構造化参照式について軽く触れる程度にしたが、今回は構造化参照式の様々なバリエーションと、有効活用の例を書いてみた。
テーブルの内部と外部で指定した場合の違い
まず下図の2つの例を見てみる。
[@単価]、[@数量]というものに、「メインTBL」という語句が、下の方の図だけ付いている。
テーブル内部 
テーブル外部
これは、
- 上の図のF3セルはテーブルの内部だから、改めてテーブル名は書かれない。
- 下の図のI3セルはテーブルの外部だから、テーブル名を明記する。
ということだ。
通常のExcelの計算式で、自シートを参照する計算式ならシート名が省略されるのと同じことだ。
自分のテーブル内で、何か他の項目を参照する
下図では、テーブル内の項目「単価」と「数量」を掛け合わせる計算式が書かれている。
普通のExcel表ならD3とかE3と書かれるところが、項目名が分かりやすく書かれるようになっている。
このようにテーブル内の項目を参照する計算式では
[@単価]
などのように、
[@項目名 ]
といった表記がされる。
これが最もよく使われるものだろう。
もちろんこれは、同じ行のデータを参照しているからできることだ。
上の例ではF3セルがD3セル・E3セルと同じ行のデータを参照しているが、これがD4セルとか違う行を参照していたら全く効果がなくなる。
テーブルの列をまるごと対象にする
下図では、「金額」項目の列全体を合計する指定をしているが、
[金額]というように、項目が
[]
記号で囲まれ表示されている。
先ほどと異なり、@記号がない。
後述する例にもあるが、@記号は、自分と同じ行番号を示すということだ。
なお上図の8行目は一見するとテーブルの外部にあるようにも見えるが、これはテーブルの「集計行」という部分だ。
リボンの「テーブルデザイン」タブで、「集計行」にチェックを入れると追加される。
この集計行は、タイトル行と同様にというか、テーブルのデータ行(この例では3行目～7行目)とは独立した部分として取り扱われる。
たとえばCtrl+Aコマンドを押すとテーブルのデータ全体が選ばれるが、この集計行は選ばれない(もう一度コマンド入力すれば選ばれるが)。
ちなみに、テーブル内のどこかのセルで、
=[
とだけ入力してみよう。
すると入力補完機能が現れ、その一番上に
@ ― この行
という項目が出てくる。
これが@記号の意味を端的に表しているわけだ。
タイトル行全体を指定する場合
テーブルのタイトル(見出し)行全体を指定した場合
と表示される。
こんなの何に使うかって、まあ使い所は少ないのだけど、特定のケースにおいてはかなり使える。
下図で「メインテーブル」「サブテーブル」という2つのテーブルを並べている。
1つのExcelファイルにシートで分けた、項目の並びがちょっと違う2テーブルだ。
メインテーブル 
サブテーブル
では、メインテーブルのF列にある「金額」という項目が、サブテーブルではどこの列にあるのか。
それをMATCH関数で求める式をサブテーブル側のD1セルに書いているが
というようになる。
この数式も一見複雑そうに見えるが、細かく入力の方法を書くと、割と簡単(なはず)だ。
- サブテーブル側のD1セルをクリックして 「 =M 」まで入力すれば第一候補にMATCH関数が出てくるので、それを選択。
- タイトル部分のD2セルをクリックすれば、
サブTBL[[#見出し],[金額]]
と自動的に入る。 - カンマ , を入力。
- メインテーブルのタイトル行全体を選択すれば、
メインTBL[#見出し]
と自動的に入る。 - ,0)
を手入力する。
という感じだ。
そして、これでD1セルに6という結果が求められたが、こんなのを何に使うのかというのが本題だけど、それは後で述べる。
自分と同じ行において、テーブルの行全体を対象にする場合
「自分と同じ行において」と回りくどい書き方をしたが、たとえば下図のような感じだ。
下図ではH3セルにおいて、テーブルの同じく3行目(製品1の行)を行全体指定したわけだが、そこで
のように、[@]表示されているわけだ。
この指定も、何の場面で使うのか分かりにくいのだが、先ほど述べたタイトル行全体指定とINDEX関数などと組み合わせて使うことが考えられる。
複合技：テーブルのタイトル名を突き合わせる
下図でサブテーブルの方に
という式を入れている。
メインテーブル 
サブテーブル
メインTBL[@]
というのが、「メインテーブルの中で、サブテーブルと同じ行全体」を示す。
そしてINDEX関数
だが、途中に空けた空白 も勿論必要な部分だ。
これにより、
- メインTBL[@] → メインテーブルの中で、サブテーブルと同じ行全体(ここでは3行目)から検索
- 空白 → 行数について指定する箇所だが、既に行数は問題ではないので空白にしている。0と明記しても良い。
- $D$1 → 先ほどの例で求めた、「金額」という文字がメインテーブルのタイトル行で6列目にあるのを求めたもの
というようになる。
要するにまとめると、
しかしそれらが、それぞれテーブルの何列目かというのは、細かく求めるのは面倒だ。
でもとにかく、両テーブルの「金額」項目を突き合わせしたい。
という場合に使えるわけだ。
もちろん実務では、こんな「金額」項目1つだけを突き合わせしようなんて生易しいケースで、こんな手の混んだ技は使わない。
もっと各テーブルの何十個もあって、何の項目が何列目にあるか把握するなんて手に負えない場合に、私はこのやり方を見付けた。
そしてこの技を使うには、複数あるテーブルの開始行を統一することだ。
今回の例ではメインテーブル・サブテーブルのタイトル行を2行目に設定しているが、これが1行目と2行目などとバラバラになっていると上手く行かない。
複合技:SUMIFS関数にも使える
いま述べた複合技は、SUMIFS関数とINDEX関数を組み合わせた集計にも用いることができる。
下図のサブテーブルの方に書いた計算式
は、メインテーブルの「年」列の値がサブテーブルの「年」と等しいものをSUMIFSで集計しているわけだ。
メインTBLについても、これまでの例なら
メインTBL[@]と書かれていたが、今回はテーブル内の1行だけを対象にするのではなくテーブル全体を対象にするので、[@]記号が外れている。
このSUMIFS関数とINDEX関数を組み合わせた集計は、以前にもちょっと書いたので、ここではこの辺りにしておく。
スポンサーリンク
