テーブルが関わる数式に注目!

解説記事内の画像はExcel 2016のものですが、操作方法は下記のバージョンで同じです。
  • Excel 2016, 2013, 2010
Excel 2016・2013・2010用 練習ファイル
(table-structured-references.xlsx 10.1KB)

テーブルが関わる数式に注目!01 図のA6からD13番地は、ちょっとした一覧表をテーブルに変換したものです。

実は、テーブル内のセルを使って数式を組むと、その数式は、普段とは違う「構造化参照」という形式の数式になります。
「構造化参照」と聞くと、超難しそうに感じますが、構える必要はありません!

ちなみに、このページは、構造化参照を細かくではなく、ざっくり捉えるためのページとなっています。
なぜなら、数式を手入力するときも、マウスで設定するときも、構造化参照の表記はExcelさんが手助けしてくれますし、数式を入力した後も、「こういう意味の数式なんだな」と、ざっくり分かればそれで事足りるからです。
それではその「構造化参照」という形式の数式がどんなものなのか、見ていくことにしましょう!

まずはテーブル名を確認!

テーブルが関わる数式に注目!02 「構造化参照」うんぬんの前に、まずはA6からD13番地のテーブルに設定してあるテーブル名を確認してみます。
A6からD13番地のテーブル内のセルならどれでもいいので、どれか1つのセルを選択すると、

テーブルが関わる数式に注目!03 リボンに[テーブルツール]の[デザイン]タブが表示されます。
このタブの[プロパティ]グループにある[テーブル名]で、このテーブルのテーブル名を確認できます。
このテーブルのテーブル名は「テーブル1」であることが分かりました。


テーブルが関わる数式に注目!02 ではここで、後の操作のために、この「テーブル1」に集計行を追加します。
「テーブル1」内のセルならどれでもいいので、どれか1つのセルが選択されていることを確認し、

テーブルが関わる数式に注目!04 [テーブルツール]の[デザイン]タブ、[テーブルスタイルのオプション]グルーブの[集計行]にチェックを付けると、

テーブルが関わる数式に注目!05 「テーブル1」に集計行を追加することができました!
集計行を追加したので、Excelさんは気を利かせて、「実績」の合計を表示してくれています。

スポンサーリンク

構造化参照は、数式の意味が分かりやすい!

それでは、この「テーブル1」のデータを使って、様々な数式を設定してみることにしましょう!

テーブルが関わる数式に注目!06 まず、この「テーブル1」に入力されているスタッフの人数を求めてみます。
人数は、氏名が入力されているセルの個数を数えればいいので、「氏名」列のデータ部分である、A7からA13番地のセルの個数を数えればいいことになります。
そして、「氏名」列に入力されているのは、数字ではなく文字。
そこで、数字が入力されているセルの個数を数えるCOUNT関数ではなく、文字も含めた、空白以外のセルの個数を数えるCOUNTA関数を使います。

テーブルが関わる数式に注目!07 それでは早速、COUNTA関数を設定していきましょう。
スタッフの人数を表示させたいC1番地を選択し、[関数の挿入]ボタンをクリックします。

テーブルが関わる数式に注目!08 関数を指定するためのダイアログボックスが表示されるので、[関数の分類]で「すべて表示」を選択すると、すべての関数が[関数名]欄にABC順で表示されます。
今回はCOUNTA関数を使いたいので、一覧から「COUNTA」をクリックで選択し、[OK]ボタンを押します。
ちなみに、この一覧から「COUNTA」を選択する際、「COUNTA」の頭文字である「C」のところまで、一瞬でスクロールする技を使うと便利です。

テーブルが関わる数式に注目!09 前のダイアログボックスで「COUNTA」を選択して[OK]ボタンをクリックすると、このようなダイアログボックスが表示されます。
表示されたダイアログボックスの[値1]欄に、どの範囲のセルの個数を数えてほしいのかを指定します。

テーブルが関わる数式に注目!06 今回数えてほしいのは、「テーブル1」の「氏名」列のデータ部分であるA7からA13番地。
普通にこの範囲をドラッグで範囲選択して指定してもいいのですが、テーブルには、テーブルならではの選択方法があるので、それを使って指定していくことにしましょう。

テーブルが関わる数式に注目!10 ダイアログボックスの[値1]欄に文字カーソルがあることを確認し、「テーブル1」の「氏名」列にある見出しの上端にマウスポインタを合わせ、マウスポインタが図のような黒い矢印に変わったらクリックすると、

テーブルが関わる数式に注目!11 「氏名」列のデータ部分のみを選択できます。
データ部分のみなので、見出しは選択されていません。
テーブルならではの選択方法のおかげで、範囲選択したかった場所を手っ取り早く選択することができました。
そして、ダイアログボックスの[値1]欄には、普段表示されるはずの「A7:A13」といったセル範囲が表示されるのではなく、ちょっと変わった表示になっています。
これが構造化参照。

テーブルが関わる数式に注目!12 角括弧で囲んだ見出しの名前は、「その見出しがある列」を意味するので、[氏名]は「氏名の列」という意味になり、図の意味は「テーブル1の氏名の列」になります。
次の章でご紹介するような表現がくっつかず、ただ[氏名]のようにシンプルに記載してあるものは、「列」の中でも「列のデータ部分」という風にとらえていいので、

テーブルが関わる数式に注目!06 今回の例では、A7からA13番地のことになります。

テーブルが関わる数式に注目!13 必要な指定が終わったので、ダイアログボックス右下の[OK]ボタンをクリックすると、

テーブルが関わる数式に注目!14 COUNTA関数を設定することができました!
COUNTA関数を設定したC1番地を選択して数式バーを見ると、たった今設定した数式の内容を確認することができます。
なんだか見慣れない表現の数式になっていますが、テーブル内のセルを使って数式を組み立てたので、テーブルの構造で表現された数式になっているんです!

テーブルが関わる数式に注目!16 もし数式で指定した範囲が、テーブルではない普通のセルだったら、このような数式になるはず。

テーブルが関わる数式に注目!15 でも、数式で指定した範囲が、テーブル内のセルだったので、テーブルの構造で表現された数式となり、どんな内容の数式なのかがパッと見で分かりやすくなっています!

スポンサーリンク

集計行のデータを引っ張るとこう表示される

テーブルが関わる数式に注目!17 お次はC2番地に、C14番地に集計されている実績の合計額が表示されるようにしてみます。

テーブルが関わる数式に注目!18 まずは合計額を表示させたいC2番地を選択し、C14番地とまったく同じものを表示させたいだけなので、「=(イコール)」を入力後、

テーブルが関わる数式に注目!19 C14番地をクリックします。
数式バーの[入力]ボタンか、キーボードの[Enter]キーで数式を確定すると、

テーブルが関わる数式に注目!20 C2番地に、C14番地とまったく同じものが表示されるように設定できました!
ここで、C2番地を選択すると、数式バーで先ほどこのセルに入力した数式の内容を確認できます。

テーブルが関わる数式に注目!21 C14番地と同じものが表示されるようにしてあるわけですから、いつもなら図のような数式になるはず。

テーブルが関わる数式に注目!22 ところが、指定したC14番地は、テーブルの、しかも集計行にあるセルだったので、数式がテーブルの構造で表現されたものになっています!

テーブルが関わる数式に注目!23 前の章で確認していただいたとおり、角括弧で囲んだ見出しの名前は、「その見出しがある列」を意味するので、[実績]は「実績の列」ということになります。
そして、その前に「#(シャープ)」が付いた表現がありますが、これは更に場所を限定する表現。
[#集計]で、「集計行」を意味しますので、上の図の意味は、「テーブル1の「実績」の列にある集計と同じものを表示してね」という意味になります。

ちなみに[#集計]は、「集計行」を意味しますが、[#見出し]だと「見出し行」を意味します。

スポンサーリンク

「@」の意味

テーブルが関わる数式に注目!24 今度はB7番地に、A7番地のフリガナが表示されるようにしてみます。
フリガナを表示させるのはPHONETIC関数ですから、B7番地にPHONETIC関数を設定していきます。

テーブルが関わる数式に注目!25 フリガナを表示させたいB7番地を選択し、[関数の挿入]ボタンをクリックします。

テーブルが関わる数式に注目!26 関数を指定するためのダイアログボックスが表示されるので、[関数の分類]で「すべて表示」を選択すると、すべての関数が[関数名]欄にABC順で表示されます。
今回はPHONETIC関数を使いたいので、一覧から「PHONETIC」をクリックで選択し、[OK]ボタンを押します。
ちなみに、この一覧から「PHONETIC」を選択する際、「PHONETIC」の頭文字である「P」のところまで、一瞬でスクロールする技を使うと便利です。

テーブルが関わる数式に注目!27 前のダイアログボックスで「PHONETIC」を選択して[OK]ボタンをクリックすると、このようなダイアログボックスが表示されます。
[参照]欄には、どのセルのフリガナを表示させたいのかを設定します。
今回はA7番地のフリガナを表示させたいわけですから、[参照]欄に文字カーソルがある状態で、

テーブルが関わる数式に注目!28 A7番地をクリックすると、指定したA7番地は、テーブル内のセルだったので、「A7」というセル番地ではなく、テーブルの構造で表現されます。

テーブルが関わる数式に注目!29 「@」は、「この行の」という意味。
「この数式が入力されているのと同じ行にある」ということを意味します。
というわけで、[@氏名]は、「この数式が入力されているのと同じ行にある「氏名」」、つまり「この行の「氏名」欄」という意味になります。

テーブルが関わる数式に注目!30 ダイアログボックス右下の[OK]ボタンをクリックすると、

テーブルが関わる数式に注目!31 数式が入力されると同時に、残りのセルにも自動的に同様の数式が設定されました!
普段なら、1つのセルに数式を入力した後、残りのセルにはオートフィルで数式をコピーする必要がありますが、テーブル内に数式を設定した場合には、1つのセルに数式を入力するだけで、残りのセルにも自動的に数式を補充してくれます。


テーブルが関わる数式に注目!32 ここで、最初に数式を設定したB7番地を選択した状態で数式バーを見て、B7番地に設定した数式を確認してみましょう。

テーブルが関わる数式に注目!33 そして、同じ列にある、自動的に数式が補充された他のセルも同じように選択して、そのセルに設定された数式を、数式バーで確認してみます。
そうです、この列のセルにはまったく同じ数式が入っているんです!

テーブルが関わる数式に注目!34 もし、テーブルになっていない表で今回の数式を設定したなら、B7番地には図のような「A7番地のフリガナを表示してね」という数式が、

テーブルが関わる数式に注目!35 B11番地には図のような「A11番地のフリガナを表示してね」という数式が入ります。

テーブルが関わる数式に注目!36 でも、構造化参照の数式は、そもそもの数式の意味が「この数式が入力されているのと同じ行にある「氏名」のフリガナを表示してね」という意味になるので、同じ数式でもうまく動くわけです。

スポンサーリンク

構成要素の名前で数式が組まれているということは

テーブルが関わる数式に注目!37 今度は、これまたテーブル内にあるD7番地に、星野さんの実績が、全体の実績の中で何位なのかを表示させてみます。

テーブルが関わる数式に注目!38 順位を表示させるのはRANK関数ですから、順位を表示させたいD7番地を選択し、[関数の挿入]ボタンをクリックします。

テーブルが関わる数式に注目!39 RANK関数を選択して[OK]ボタンをクリックすると、

テーブルが関わる数式に注目!40 このようなダイアログボックスが表示されます。
RANK関数の設定は、「この数字は、この中で、大きい方から数えて(何位ですか)」という設定になりますから、

テーブルが関わる数式に注目!41 今回の設定では、「星野さんの実績は、全体の実績の中で、大きい方から数えて(何位ですか)」となるように設定していきます。

テーブルが関わる数式に注目!42 まずは「星野さんの実績は、全体の実績の中で、大きい方から数えて何位ですか」の、「星野さんの実績は」の部分から。
星野さんの実績はC7番地ですから、[数値]欄に文字カーソルがある状態で、

テーブルが関わる数式に注目!43 C7番地をクリックすると、[@実績]と表示されました。
「@」は、「この数式が入力されているのと同じ行にある」という意味ですから、[@実績]は、「この数式が入力されているのと同じ行にある「実績」」ということになり、この行は星野さんに関する行ですから、必然的に星野さんの実績のことを意味することになります。

テーブルが関わる数式に注目!44 1つ目の設定が終わったので、次の欄に文字カーソルを移します。
[Tab]キーを押すと、

テーブルが関わる数式に注目!45 文字カーソルが移りました。
ここでは「星野さんの実績は、全体の実績の中で、大きい方から数えて何位ですか」の、「全体の実績の中で」の部分を設定します。

テーブルが関わる数式に注目!46 全体の実績はC7からC13番地にある実績データすべてのことなので、普通にこの範囲をドラッグで範囲選択して指定してもいいのですが、テーブルには、テーブルならではの選択方法があるので、それを使って指定していくことにしましょう。
「実績」欄の見出しの上端にマウスポインタを合わせ、マウスポインタが図のような黒い矢印に変わったらクリックすると、

テーブルが関わる数式に注目!47 「実績」列のデータ部分のみを選択できます。
データ部分のみなので、見出しは選択されていません。
テーブルならではの選択方法のおかげで、範囲選択したかった場所を手っ取り早く選択することができました。
そしてダイアログボックスには、[実績]と表示されました。
[実績]は「実績の列」のこと。
[実績]のようにシンプルに記載してあるものは、「列」の中でも「列のデータがある部分」という風にとらえていいので、見出しを除いたC7からC13番地にある実績のデータすべてのことになるわけです。

テーブルが関わる数式に注目!48 今回の順位は実績が大きいものから1位、2位・・・としていきたいので、最後の[順序]欄には「0(ゼロ)」を入力し、ダイアログボックス右下の[OK]ボタンをクリックします。

テーブルが関わる数式に注目!49 数式が入力されると同時に、残りのセルにも自動的に同様の数式が設定されました!
普段なら、1つのセルに数式を入力した後、残りのセルにはオートフィルで数式をコピーする必要がありますが、テーブル内に数式を設定した場合には、1つのセルに数式を入力するだけで、残りのセルにも自動的に数式を補充してくれます。


テーブルが関わる数式に注目!50 ここで、最初に数式を設定したD7番地を選択した状態で、数式バーで数式を確認してみます。

テーブルが関わる数式に注目!51 そして、同じ列にある、自動的に数式が補充された他のセルも、同じように選択して、数式バーで数式を確認してみます。
やはり、この列にはまったく同じ数式が入っていることが分かります!

テーブルが関わる数式に注目!52 構造化参照で設定されたこの数式は、そもそもの意味が「この数式が入力されているのと同じ行にある「実績」が、全体の実績の中で、大きい方から数えて何位ですか」という意味になるので、「順位」欄のどのセルでも同じ数式でうまく動くわけです。


テーブルが関わる数式に注目!53 さてここで、表の構造の名前を使った数式になっているということの利点をもう1つ見ておきます。
もし、この順位付けした表がテーブルではなかった場合、

D7番地に入る数式C7番地の数字が、C7からC13番地の中で、大きい方から数えて何位ですか
D8番地に入る数式C8番地の数字が、C7からC13番地の中で、大きい方から数えて何位ですか
D9番地に入る数式C9番地の数字が、C7からC13番地の中で、大きい方から数えて何位ですか

となり、どのセルに入る数式もC7からC13番地にある全体の実績の中で順位付けされるよう、絶対参照を設定する必要があります

テーブルが関わる数式に注目!54 ですが、今回組んだ数式では全体の範囲を指定する箇所に[実績]と指定してあります。
[実績]は必然的にC7からC13番地のことを指すので、このように構造化参照で指定されている場合は、絶対参照を設定する必要がありません
これも構造化参照の利点の1つです。

スポンサーリンク

データを追加しても

テーブルが関わる数式に注目!55 さて、ここでデータを追加してみましょう。
テーブルの一番右下にある直角マークにマウスポインタを合わせ、

テーブルが関わる数式に注目!56 マウスポインタが両矢印の形になったら下にドラッグして、

テーブルが関わる数式に注目!57 二人分のデータが入れられる行を作ります。

テーブルが関わる数式に注目!58 追加した行の順位欄にエラーが表示されているので、どうなっているのか確認してみましょう。
エラーが出ているセルを選択して数式バーを見ると、行を追加しただけなのに、既に数式が補充されていることが分かります。
テーブルでは、こういうこともExcelさんが気を利かせて自動でやってくれるわけです。
そして、自動で数式が補充されたけれど、その数式で順位を付けようにも、まだ実績が入力されていないので、エラーが出てしまっているというわけです。

テーブルが関わる数式に注目!59 また、追加した行の「フリガナ」のセルを選択して数式バーを見ても、やはり、行を追加しただけなのに、既に数式が補充されていることが分かります。

テーブルが関わる数式に注目!60 行を追加した時点で既に数式が補充されているわけですから、適当なデータを入力すると、フリガナも順位もちゃんと表示されます!

テーブルが関わる数式に注目!61 更に、もし、この順位付けした表がテーブルではなかった場合、「C7番地の数字が、C7からC13番地の中で、大きい方から数えて何位ですか」といった数式になるため、

テーブルが関わる数式に注目!62 行を追加したなら、「C7番地の数字が、C7からC15番地の中で、大きい方から数えて何位ですか」という風に数式を修正してあげなければなりません。

テーブルが関わる数式に注目!63 ですが、今回組んだ数式では全体の範囲を指定する箇所に[実績]と指定してあるため、[実績]欄が広くなろうが狭くなろうが[実績]の範囲を常に認識してくれるので数式を修正する必要がありません

スポンサーリンク

テーブルになる前の数式はそのまま

今回ご紹介した構造化参照は、既にテーブルの状態となっているセルを数式で使う場合に現れます。

テーブルが関わる数式に注目!64 ですので、テーブルに変換する前に数式を設定し、

テーブルが関わる数式に注目!65 その後でテーブルに変換しても、数式は以前のまま、構造化参照になるわけではありません。

構造化参照 まとめ

ここまでで、「構造化参照」という形式の数式がどんなものなのかをご紹介しました。

  • 数式の意味が分かりやすい
  • 1つのセルに数式を入力するだけで、残りのセルにも自動的に数式が補充される
  • 絶対参照を設定する必要が無い
  • データを追加しても、それを含めて改めてテーブル範囲として認識してくれるため、データを追加する度に数式を手直しする必要が無い

今回は、構造化参照をざっくり捉えるためのご紹介をしましたが、もっと深く知りたい!という方は、Excelを作っているMicrosoft社の「Excel テーブルでの構造化参照の使い方」をご覧ください。

テーブルは、かなり使いでのある機能なので、それに合わせて構造化参照の数式を目にする機会も多くなると思います。
そんな時に、今回ご紹介した特徴が頭の隅っこにあると、テーブルがどんどん身近な機能になっていきます!

スポンサーリンク