図のA6からD13番地は、ちょっとした一覧表をテーブルに変換したものです。
実は、テーブル内のセルを使って数式を組むと、その数式は、普段とは違う「構造化参照」という形式の数式になります。
「構造化参照」と聞くと、超難しそうに感じますが、構える必要はありません！
ちなみに、このページは、構造化参照を細かくではなく、ざっくり捉えるためのページとなっています。
なぜなら、数式を手入力するときも、マウスで設定するときも、構造化参照の表記はExcelさんが手助けしてくれますし、数式を入力した後も、「こういう意味の数式なんだな」と、ざっくり分かればそれで事足りるからです。
それではその「構造化参照」という形式の数式がどんなものなのか、見ていくことにしましょう！
まずはテーブル名を確認！
「構造化参照」うんぬんの前に、まずはA6からD13番地のテーブルに設定してあるテーブル名を確認してみます。
A6からD13番地のテーブル内のセルならどれでもいいので、どれか1つのセルを選択すると、
リボンに［テーブルツール］の［デザイン］タブが表示されます。
このタブの［プロパティ］グループにある［テーブル名］で、このテーブルのテーブル名を確認できます。
このテーブルのテーブル名は「テーブル1」であることが分かりました。
ではここで、後の操作のために、この「テーブル1」に集計行を追加します。
「テーブル1」内のセルならどれでもいいので、どれか1つのセルが選択されていることを確認し、
［テーブルツール］の［デザイン］タブ、［テーブルスタイルのオプション］グルーブの［集計行］にチェックを付けると、
「テーブル1」に集計行を追加することができました！
集計行を追加したので、Excelさんは気を利かせて、「実績」の合計を表示してくれています。
スポンサーリンク
構造化参照は、数式の意味が分かりやすい！
それでは、この「テーブル1」のデータを使って、様々な数式を設定してみることにしましょう！
まず、この「テーブル1」に入力されているスタッフの人数を求めてみます。
人数は、氏名が入力されているセルの個数を数えればいいので、「氏名」列のデータ部分である、A7からA13番地のセルの個数を数えればいいことになります。
そして、「氏名」列に入力されているのは、数字ではなく文字。
そこで、数字が入力されているセルの個数を数えるCOUNT関数ではなく、文字も含めた、空白以外のセルの個数を数えるCOUNTA関数を使います。
それでは早速、COUNTA関数を設定していきましょう。
スタッフの人数を表示させたいC1番地を選択し、［関数の挿入］ボタンをクリックします。
関数を指定するためのダイアログボックスが表示されるので、［関数の分類］で「すべて表示」を選択すると、すべての関数が［関数名］欄にABC順で表示されます。
今回はCOUNTA関数を使いたいので、一覧から「COUNTA」をクリックで選択し、［OK］ボタンを押します。
ちなみに、この一覧から「COUNTA」を選択する際、「COUNTA」の頭文字である「C」のところまで、一瞬でスクロールする技を使うと便利です。
前のダイアログボックスで「COUNTA」を選択して［OK］ボタンをクリックすると、このようなダイアログボックスが表示されます。
表示されたダイアログボックスの［値1］欄に、どの範囲のセルの個数を数えてほしいのかを指定します。
今回数えてほしいのは、「テーブル1」の「氏名」列のデータ部分であるA7からA13番地。
普通にこの範囲をドラッグで範囲選択して指定してもいいのですが、テーブルには、テーブルならではの選択方法があるので、それを使って指定していくことにしましょう。
ダイアログボックスの［値1］欄に文字カーソルがあることを確認し、「テーブル1」の「氏名」列にある見出しの上端にマウスポインタを合わせ、マウスポインタが図のような黒い矢印に変わったらクリックすると、
「氏名」列のデータ部分のみを選択できます。
データ部分のみなので、見出しは選択されていません。
テーブルならではの選択方法のおかげで、範囲選択したかった場所を手っ取り早く選択することができました。
そして、ダイアログボックスの［値1］欄には、普段表示されるはずの「A7:A13」といったセル範囲が表示されるのではなく、ちょっと変わった表示になっています。
これが構造化参照。
角括弧で囲んだ見出しの名前は、「その見出しがある列」を意味するので、［氏名］は「氏名の列」という意味になり、図の意味は「テーブル1の氏名の列」になります。
次の章でご紹介するような表現がくっつかず、ただ［氏名］のようにシンプルに記載してあるものは、「列」の中でも「列のデータ部分」という風にとらえていいので、
今回の例では、A7からA13番地のことになります。
必要な指定が終わったので、ダイアログボックス右下の［OK］ボタンをクリックすると、
COUNTA関数を設定することができました！
COUNTA関数を設定したC1番地を選択して数式バーを見ると、たった今設定した数式の内容を確認することができます。
なんだか見慣れない表現の数式になっていますが、テーブル内のセルを使って数式を組み立てたので、テーブルの構造で表現された数式になっているんです！
もし数式で指定した範囲が、テーブルではない普通のセルだったら、このような数式になるはず。
でも、数式で指定した範囲が、テーブル内のセルだったので、テーブルの構造で表現された数式となり、どんな内容の数式なのかがパッと見で分かりやすくなっています！
スポンサーリンク
集計行のデータを引っ張るとこう表示される
お次はC2番地に、C14番地に集計されている実績の合計額が表示されるようにしてみます。
まずは合計額を表示させたいC2番地を選択し、C14番地とまったく同じものを表示させたいだけなので、「＝（イコール）」を入力後、
C14番地をクリックします。
数式バーの［入力］ボタンか、キーボードの［Enter］キーで数式を確定すると、
C2番地に、C14番地とまったく同じものが表示されるように設定できました！
ここで、C2番地を選択すると、数式バーで先ほどこのセルに入力した数式の内容を確認できます。
C14番地と同じものが表示されるようにしてあるわけですから、いつもなら図のような数式になるはず。
ところが、指定したC14番地は、テーブルの、しかも集計行にあるセルだったので、数式がテーブルの構造で表現されたものになっています！
前の章で確認していただいたとおり、角括弧で囲んだ見出しの名前は、「その見出しがある列」を意味するので、［実績］は「実績の列」ということになります。
そして、その前に「#（シャープ）」が付いた表現がありますが、これは更に場所を限定する表現。
［#集計］で、「集計行」を意味しますので、上の図の意味は、「テーブル1の「実績」の列にある集計と同じものを表示してね」という意味になります。
ちなみに［#集計］は、「集計行」を意味しますが、［#見出し］だと「見出し行」を意味します。
スポンサーリンク
「＠」の意味
今度はB7番地に、A7番地のフリガナが表示されるようにしてみます。
フリガナを表示させるのはPHONETIC関数ですから、B7番地にPHONETIC関数を設定していきます。
フリガナを表示させたいB7番地を選択し、［関数の挿入］ボタンをクリックします。
関数を指定するためのダイアログボックスが表示されるので、［関数の分類］で「すべて表示」を選択すると、すべての関数が［関数名］欄にABC順で表示されます。
今回はPHONETIC関数を使いたいので、一覧から「PHONETIC」をクリックで選択し、［OK］ボタンを押します。
ちなみに、この一覧から「PHONETIC」を選択する際、「PHONETIC」の頭文字である「P」のところまで、一瞬でスクロールする技を使うと便利です。
前のダイアログボックスで「PHONETIC」を選択して［OK］ボタンをクリックすると、このようなダイアログボックスが表示されます。
［参照］欄には、どのセルのフリガナを表示させたいのかを設定します。
今回はA7番地のフリガナを表示させたいわけですから、［参照］欄に文字カーソルがある状態で、
A7番地をクリックすると、指定したA7番地は、テーブル内のセルだったので、「A7」というセル番地ではなく、テーブルの構造で表現されます。
「＠」は、「この行の」という意味。
「この数式が入力されているのと同じ行にある」ということを意味します。
というわけで、[＠氏名]は、「この数式が入力されているのと同じ行にある「氏名」」、つまり「この行の「氏名」欄」という意味になります。
ダイアログボックス右下の［OK］ボタンをクリックすると、
数式が入力されると同時に、残りのセルにも自動的に同様の数式が設定されました！
普段なら、1つのセルに数式を入力した後、残りのセルにはオートフィルで数式をコピーする必要がありますが、テーブル内に数式を設定した場合には、1つのセルに数式を入力するだけで、残りのセルにも自動的に数式を補充してくれます。
ここで、最初に数式を設定したB7番地を選択した状態で数式バーを見て、B7番地に設定した数式を確認してみましょう。
そして、同じ列にある、自動的に数式が補充された他のセルも同じように選択して、そのセルに設定された数式を、数式バーで確認してみます。
そうです、この列のセルにはまったく同じ数式が入っているんです！
もし、テーブルになっていない表で今回の数式を設定したなら、B7番地には図のような「A7番地のフリガナを表示してね」という数式が、
B11番地には図のような「A11番地のフリガナを表示してね」という数式が入ります。
でも、構造化参照の数式は、そもそもの数式の意味が「この数式が入力されているのと同じ行にある「氏名」のフリガナを表示してね」という意味になるので、同じ数式でもうまく動くわけです。
スポンサーリンク
構成要素の名前で数式が組まれているということは
今度は、これまたテーブル内にあるD7番地に、星野さんの実績が、全体の実績の中で何位なのかを表示させてみます。
順位を表示させるのはRANK関数ですから、順位を表示させたいD7番地を選択し、［関数の挿入］ボタンをクリックします。
RANK関数を選択して［OK］ボタンをクリックすると、
このようなダイアログボックスが表示されます。
RANK関数の設定は、「この数字は、この中で、大きい方から数えて（何位ですか）」という設定になりますから、
今回の設定では、「星野さんの実績は、全体の実績の中で、大きい方から数えて（何位ですか）」となるように設定していきます。
まずは「星野さんの実績は、全体の実績の中で、大きい方から数えて何位ですか」の、「星野さんの実績は」の部分から。
星野さんの実績はC7番地ですから、［数値］欄に文字カーソルがある状態で、
C7番地をクリックすると、[＠実績]と表示されました。
「＠」は、「この数式が入力されているのと同じ行にある」という意味ですから、[＠実績]は、「この数式が入力されているのと同じ行にある「実績」」ということになり、この行は星野さんに関する行ですから、必然的に星野さんの実績のことを意味することになります。
1つ目の設定が終わったので、次の欄に文字カーソルを移します。
［Tab］キーを押すと、
文字カーソルが移りました。
ここでは「星野さんの実績は、全体の実績の中で、大きい方から数えて何位ですか」の、「全体の実績の中で」の部分を設定します。
全体の実績はC7からC13番地にある実績データすべてのことなので、普通にこの範囲をドラッグで範囲選択して指定してもいいのですが、テーブルには、テーブルならではの選択方法があるので、それを使って指定していくことにしましょう。
「実績」欄の見出しの上端にマウスポインタを合わせ、マウスポインタが図のような黒い矢印に変わったらクリックすると、
「実績」列のデータ部分のみを選択できます。
データ部分のみなので、見出しは選択されていません。
テーブルならではの選択方法のおかげで、範囲選択したかった場所を手っ取り早く選択することができました。
そしてダイアログボックスには、［実績］と表示されました。
［実績］は「実績の列」のこと。
［実績］のようにシンプルに記載してあるものは、「列」の中でも「列のデータがある部分」という風にとらえていいので、見出しを除いたC7からC13番地にある実績のデータすべてのことになるわけです。
今回の順位は実績が大きいものから1位、2位・・・としていきたいので、最後の［順序］欄には「0（ゼロ）」を入力し、ダイアログボックス右下の［OK］ボタンをクリックします。
数式が入力されると同時に、残りのセルにも自動的に同様の数式が設定されました！
普段なら、1つのセルに数式を入力した後、残りのセルにはオートフィルで数式をコピーする必要がありますが、テーブル内に数式を設定した場合には、1つのセルに数式を入力するだけで、残りのセルにも自動的に数式を補充してくれます。
ここで、最初に数式を設定したD7番地を選択した状態で、数式バーで数式を確認してみます。
そして、同じ列にある、自動的に数式が補充された他のセルも、同じように選択して、数式バーで数式を確認してみます。
やはり、この列にはまったく同じ数式が入っていることが分かります！
構造化参照で設定されたこの数式は、そもそもの意味が「この数式が入力されているのと同じ行にある「実績」が、全体の実績の中で、大きい方から数えて何位ですか」という意味になるので、「順位」欄のどのセルでも同じ数式でうまく動くわけです。
さてここで、表の構造の名前を使った数式になっているということの利点をもう1つ見ておきます。
もし、この順位付けした表がテーブルではなかった場合、
| D7番地に入る数式 | C7番地の数字が、C7からC13番地の中で、大きい方から数えて何位ですか |
|---|---|
| D8番地に入る数式 | C8番地の数字が、C7からC13番地の中で、大きい方から数えて何位ですか |
| D9番地に入る数式 | C9番地の数字が、C7からC13番地の中で、大きい方から数えて何位ですか |
となり、どのセルに入る数式もC7からC13番地にある全体の実績の中で順位付けされるよう、絶対参照を設定する必要があります。
ですが、今回組んだ数式では全体の範囲を指定する箇所に［実績］と指定してあります。
［実績］は必然的にC7からC13番地のことを指すので、このように構造化参照で指定されている場合は、絶対参照を設定する必要がありません。
これも構造化参照の利点の1つです。
スポンサーリンク
データを追加しても
さて、ここでデータを追加してみましょう。
テーブルの一番右下にある直角マークにマウスポインタを合わせ、
マウスポインタが両矢印の形になったら下にドラッグして、
二人分のデータが入れられる行を作ります。
追加した行の順位欄にエラーが表示されているので、どうなっているのか確認してみましょう。
エラーが出ているセルを選択して数式バーを見ると、行を追加しただけなのに、既に数式が補充されていることが分かります。
テーブルでは、こういうこともExcelさんが気を利かせて自動でやってくれるわけです。
そして、自動で数式が補充されたけれど、その数式で順位を付けようにも、まだ実績が入力されていないので、エラーが出てしまっているというわけです。
また、追加した行の「フリガナ」のセルを選択して数式バーを見ても、やはり、行を追加しただけなのに、既に数式が補充されていることが分かります。
行を追加した時点で既に数式が補充されているわけですから、適当なデータを入力すると、フリガナも順位もちゃんと表示されます！
更に、もし、この順位付けした表がテーブルではなかった場合、「C7番地の数字が、C7からC13番地の中で、大きい方から数えて何位ですか」といった数式になるため、
行を追加したなら、「C7番地の数字が、C7からC15番地の中で、大きい方から数えて何位ですか」という風に数式を修正してあげなければなりません。
ですが、今回組んだ数式では全体の範囲を指定する箇所に［実績］と指定してあるため、［実績］欄が広くなろうが狭くなろうが［実績］の範囲を常に認識してくれるので数式を修正する必要がありません。
スポンサーリンク
テーブルになる前の数式はそのまま
今回ご紹介した構造化参照は、既にテーブルの状態となっているセルを数式で使う場合に現れます。
ですので、テーブルに変換する前に数式を設定し、
その後でテーブルに変換しても、数式は以前のまま、構造化参照になるわけではありません。
構造化参照 まとめ
ここまでで、「構造化参照」という形式の数式がどんなものなのかをご紹介しました。
- 数式の意味が分かりやすい
- 1つのセルに数式を入力するだけで、残りのセルにも自動的に数式が補充される
- 絶対参照を設定する必要が無い
- データを追加しても、それを含めて改めてテーブル範囲として認識してくれるため、データを追加する度に数式を手直しする必要が無い
今回は、構造化参照をざっくり捉えるためのご紹介をしましたが、もっと深く知りたい！という方は、Excelを作っているMicrosoft社の「Excel テーブルでの構造化参照の使い方」をご覧ください。
テーブルは、かなり使いでのある機能なので、それに合わせて構造化参照の数式を目にする機会も多くなると思います。
そんな時に、今回ご紹介した特徴が頭の隅っこにあると、テーブルがどんどん身近な機能になっていきます！
Officeの使いこなしテクニックをメールでお届け！