Excel テーブルの構造化参照式 本格的に活用してみる

Excel テーブル

スポンサーリンク

以前に、Excelのテーブルの「構造化参照式」について簡単に説明した。

Excel 「テーブル」の直感的で分かりやすい数式(構造化参照)

2019年12月13日

その時は構造化参照式について軽く触れる程度にしたが、今回は構造化参照式の様々なバリエーションと、有効活用の例を書いてみた。

テーブルの内部と外部で指定した場合の違い

まず下図の2つの例を見てみる。
[@単価]、[@数量]というものに、「メインTBL」という語句が、下の方の図だけ付いている。

これは、

  • 上の図のF3セルはテーブルの内部だから、改めてテーブル名は書かれない。
  • 下の図のI3セルはテーブルの外部だから、テーブル名を明記する。

ということだ。

通常のExcelの計算式で、自シートを参照する計算式ならシート名が省略されるのと同じことだ。

Excel VBA 計算式中の自シートを参照する部分を削除するマクロ 個人用マクロブックに組み込める

2018年9月6日

自分のテーブル内で、何か他の項目を参照する

下図では、テーブル内の項目「単価」と「数量」を掛け合わせる計算式が書かれている。

普通のExcel表ならD3とかE3と書かれるところが、項目名が分かりやすく書かれるようになっている。

このようにテーブル内の項目を参照する計算式では
[@単価]などのように、
[@項目名 ]といった表記がされる。

これが最もよく使われるものだろう。

もちろんこれは、同じ行のデータを参照しているからできることだ。
上の例ではF3セルがD3セル・E3セルと同じ行のデータを参照しているが、これがD4セルとか違う行を参照していたら全く効果がなくなる。

Excel 「テーブル」の直感的で分かりやすい数式(構造化参照)

2019年12月13日

テーブルの列をまるごと対象にする

下図では、「金額」項目の列全体を合計する指定をしているが、

[金額]というように、項目が
[]記号で囲まれ表示されている。

先ほどと異なり、@記号がない。

後述する例にもあるが、@記号は、自分と同じ行番号を示すということだ。

なお上図の8行目は一見するとテーブルの外部にあるようにも見えるが、これはテーブルの「集計行」という部分だ。
リボンの「テーブルデザイン」タブで、「集計行」にチェックを入れると追加される。

この集計行は、タイトル行と同様にというか、テーブルのデータ行(この例では3行目~7行目)とは独立した部分として取り扱われる。
たとえばCtrl+Aコマンドを押すとテーブルのデータ全体が選ばれるが、この集計行は選ばれない(もう一度コマンド入力すれば選ばれるが)。

ちなみに、テーブル内のどこかのセルで、
=[
とだけ入力してみよう。

すると入力補完機能が現れ、その一番上に
@ ― この行
という項目が出てくる。

これが@記号の意味を端的に表しているわけだ。

タイトル行全体を指定する場合

テーブルのタイトル(見出し)行全体を指定した場合

メインTBL[#見出し]
と表示される。

こんなの何に使うかって、まあ使い所は少ないのだけど、特定のケースにおいてはかなり使える。

下図で「メインテーブル」「サブテーブル」という2つのテーブルを並べている。
1つのExcelファイルにシートで分けた、項目の並びがちょっと違う2テーブルだ。

では、メインテーブルのF列にある「金額」という項目が、サブテーブルではどこの列にあるのか。

それをMATCH関数で求める式をサブテーブル側のD1セルに書いているが

=MATCH( サブTBL[[#見出し] , [金額]] , メインTBL[#見出し] , 0)
というようになる。

この数式も一見複雑そうに見えるが、細かく入力の方法を書くと、割と簡単(なはず)だ。

  1. サブテーブル側のD1セルをクリックして 「 =M 」まで入力すれば第一候補にMATCH関数が出てくるので、それを選択。
  2. タイトル部分のD2セルをクリックすれば、
    サブTBL[[#見出し],[金額]]
    と自動的に入る。
  3. カンマ , を入力。
  4. メインテーブルのタイトル行全体を選択すれば、
    メインTBL[#見出し]
    と自動的に入る。
  5. ,0)
    を手入力する。

という感じだ。
そして、これでD1セルに6という結果が求められたが、こんなのを何に使うのかというのが本題だけど、それは後で述べる

自分と同じ行において、テーブルの行全体を対象にする場合

「自分と同じ行において」と回りくどい書き方をしたが、たとえば下図のような感じだ。

下図ではH3セルにおいて、テーブルの同じく3行目(製品1の行)を行全体指定したわけだが、そこで

メインTBL[@]
のように、[@]表示されているわけだ。

この指定も、何の場面で使うのか分かりにくいのだが、先ほど述べたタイトル行全体指定とINDEX関数などと組み合わせて使うことが考えられる。

複合技:テーブルのタイトル名を突き合わせる

下図でサブテーブルの方に

=INDEX(メインTBL[@],  , $D$1)
という式を入れている。

メインTBL[@]というのが、「メインテーブルの中で、サブテーブルと同じ行全体」を示す。

そしてINDEX関数

=INDEX(メインTBL[@],  ,$D$1)
だが、途中に空けた空白  も勿論必要な部分だ。

これにより、

  • メインTBL[@]  →  メインテーブルの中で、サブテーブルと同じ行全体(ここでは3行目)から検索
  • 空白  → 行数について指定する箇所だが、既に行数は問題ではないので空白にしている。0と明記しても良い。
  • $D$1 → 先ほどの例で求めた、「金額」という文字がメインテーブルのタイトル行で6列目にあるのを求めたもの

というようになる。

要するにまとめると、

2つあるテーブルの両方に「金額」という項目があることが分かっている。
しかしそれらが、それぞれテーブルの何列目かというのは、細かく求めるのは面倒だ。

でもとにかく、両テーブルの「金額」項目を突き合わせしたい。

という場合に使えるわけだ。

もちろん実務では、こんな「金額」項目1つだけを突き合わせしようなんて生易しいケースで、こんな手の混んだ技は使わない。
もっと各テーブルの何十個もあって、何の項目が何列目にあるか把握するなんて手に負えない場合に、私はこのやり方を見付けた。

そしてこの技を使うには、複数あるテーブルの開始行を統一することだ。
今回の例ではメインテーブル・サブテーブルのタイトル行を2行目に設定しているが、これが1行目と2行目などとバラバラになっていると上手く行かない。

複合技:SUMIFS関数にも使える

いま述べた複合技は、SUMIFS関数とINDEX関数を組み合わせた集計にも用いることができる。

下図のサブテーブルの方に書いた計算式

=SUMIFS( INDEX(メインTBL,  ,$C$1), メインTBL[年] , [@年] )
は、メインテーブルの「年」列の値がサブテーブルの「年」と等しいものをSUMIFSで集計しているわけだ。

メインTBLについても、これまでの例なら
メインTBL[@]と書かれていたが、今回はテーブル内の1行だけを対象にするのではなくテーブル全体を対象にするので、[@]記号が外れている。

このSUMIFS関数とINDEX関数を組み合わせた集計は、以前にもちょっと書いたので、ここではこの辺りにしておく。

Excel SUMPRODUCT関数とSUMIFS関数 縦横混合の集計を比較してみる

2019年6月4日

スポンサーリンク