MOS365 Excelの出題範囲に「数式の中で構造化参照を使用する」とあります。この聞きなれない「構造化参照」は深入りすればとても多くの機能を持つものなのですが、まずはMOSとしてわかっておいた方がいいレベルで解説したいと思います。
構造化参照とは
「構造化参照」とは、テーブルに設定された範囲を元データにした計算式の参照方法です。普段は「=SUM(B2:B10)」のような計算式で作成しますが、テーブル内のデータを使った構造化参照では「=SUM(販売一覧[販売金額])」といった言葉で設定します。
テーブル
テーブルとは、一覧表が作成された範囲の1セルをクリックして、「ホーム」タブの「テーブルとして書式設定」、あるいはCtrlキーを押したままTのキーを押すと、テーブルの作成ダイアログボックスが表示され、OKボタンをクリックすると、カラフルな一覧表に代わります。この状態がテーブルです。
テーブルでは、フィルターや並べ替えといった一覧表を扱うのに便利な機能があったり、一覧表としてデータが壊れないようになっていたり、一覧表を扱う上で必ずと言っていいほど設定しておいた方がよい機能です。
テーブルには後から名前を設定することができます。
構造化参照の式
実際の構造化参照の式の例を紹介しながら構造化参照の基礎を学んでいきましょう。
例）「スタッフ」テーブルで「スタッフID」の数を数える。
=COUNTA(スタッフ[スタッフID])
このように構造化参照では、「テーブル名[列の項目名]」という指定をします。その指定した範囲は1行目の列名のセルは含まれません。あくまでデータの数のみが指定されます。
例）「スタッフ」テーブルで、F列の「部署」ごとに人数を数える。
=COUNTIF(スタッフ[部署],F2)
実は構造化参照では、絶対参照を意識しなくてもよいのです。この場合セルF2はテーブルの外で構造化参照ではないのですが、「スタッフ[部署]」の部分については構造化参照になるので絶対参照となるため、セルG2の計算式のまま、セルG4までコピーができます。
例）「スタッフ」テーブルのセル数を数える
=COUNTA(スタッフ)
テーブル全体の数を数えるにはテーブル名だけを指定します。
例）「スタッフ」テーブルの「部署」から「時給」のセル数を数える
=COUNTA(スタッフ[[部署]:[時給]])
複数列を対象とした場合、「テーブル名[[開始列名]:[終了列名]]」という指定になります。「[]」が二重になるのがポイントです。
例）「スタッフ」テーブルの「時給」に100を足した値をF列に計算する
=スタッフ[@時給]+100
同じ行であれば、「テーブル名[@項目名]」で指定できます。違う行では普通に「D3」のようなセル参照になります。「@」が付くのが特徴で、一般的に手書きの表でも単価を表すときに「@100」のように書くこともあり、「@」は単一の物、つまり１つのセルを表すというように覚えておくとわかりやすいと思います。
例）「スタッフ」テーブルの「組立」だけの範囲を使ってそのセル個数を数える
=COUNTA(B2:B5)
この場合、構造化参照にはなりません。構造化参照にできるのは列ごとです。
例）「スタッフ」テーブルの「時給加算」項目に「時給」に500を足した値を計算する
=[@時給]+500
この場合は、「スタッフ」テーブルの中から同じ「スタッフ」テーブルの中を参照した場合で、この場合は「[項目名]」、1つのセルを参照するなら「[@項目名]」となります。
構造化参照のメリット
構造化参照の実例を見た限り、名前で指定できるからわかりやすい、絶対参照を意識しなくてもよいというメリットが見えてきました。
でも、それらはちょっとした計算式ならテーブルにせずともわかりやすいですし、絶対参照は設定すればそれで済みます。実はその2つ以外にもメリットがあります。
データの増減に強い
普通のセル参照ではデータが増えれば計算式もそのセルの分、変更する必要がありますが、構造化参照にすれば、データが増えても計算式を変更することはありません。あくまでテーブル全体に対し、項目名の名前で指定できるのです。
これが一番のメリットなのです。
項目の入れ替えに強い
項目を左右に入れ替えることがテーブルでは簡単にできるのですが、項目名の左右が変わったとしても、あくまで項目名で見ているので、計算式の変更は必要ありません。また項目を追加したり削除したりしてセルの場所が変わっても問題ありません。
関数やスピルに強い
MOS365では「SORT関数」や「FILTER関数」などの、1つのセルに計算式を入力したら複数のセルに結果を出力できるスピル機能を使った関数が出題されます。これらは一覧表を扱う関数が多く、とてもテーブルと相性が良いため、構造化参照での入力を覚えておきたいところです。同様に「SUM関数」や「SUMIF関数」のような一覧表から集計する関数、VLOOKUP関数のように一覧表を検索する関数の場合も構造化参照での入力方法に慣れておくとよいでしょう。
MOS365での正解は
MOS365の場合、採点基準が公開されていないので確定ではないのですが、テーブルデータが元になっている場合、指定がなくても構造化参照にした方が正解になる可能性は100%近くあると思います。
マウス操作であればセルやセル範囲を指定しただけで構造化参照が設定されます。しかし、手入力で入力したら構造化参照ではなく入力することも可能です。注意しましょう。
構造化参照の簡単な入力方法
実は構造化参照は特に大量なセル範囲を指定するときにとても簡単に設定できる方法があります。元のテーブルがあるシートと計算式が入るシートが別の場合はマウスを使った設定が最も簡単で、もし同じシートの場合は手入力で簡単な方法があります。その2種類の手順を紹介します。
マウスを使った操作
まず、元データのテーブルを下方向にドラッグし、テーブルのどのセルでも構わないのでクリックすると、列見出しがテーブルの項目名に代わりますので、この状態を作っておきます。
計算式を入力するシートに移動し、「=」に続き必要な関数名などを入力、テーブルデータを参照する直前まで入力します。
そこで、テーブルのシートを選択します。
シートを変更しても計算式は入力途中のままキープされます。
この状態で、指定したい列見出しをクリックします。
すると、「テーブル名[項目名]」が指定できます。
ここで、すぐに、もし引数の続きがあるのであれば「,」を、もし関数の入力が終わるのであれば「)」を、計算式の入力が終わるならEnterキーを押します。というのもそれをせずに元の計算式のシートに戻ると、せっかく設定した設定が変わってしまうので、項目名をクリックしたらとにかくすぐに行ってください。
では今回は元の計算シートに戻ります。
残りの計算式を入力して、「)」で閉じます。
Enterキーを押します。
結果が計算されます。
この方法は、どんな大量のデータがってもできる方法なので、大量データがあっても何千行何万行をドラッグする必要がなくなる方法です。少ないデータであればドラッグした方が早いですが、使い分けていきましょう。
大量データの場合、1シートに1つのテーブルだけがあるケースが多く、またそのような使い方をすべきです。
そのようなことから大量データの構造化参照はこの方法が時短と正確性においてとても有効だと思います。
この方法は計算するシートと構造化参照するテーブルが別でないとできないので注意してください。
手入力の操作
手入力での設定では準備は必要ありませんが、あらかじめテーブル名だけは「テーブルデザイン」タブの「テーブル名」でテーブルをクリックして、調べておきます。
「=」に続き必要な関数名などを入力、テーブルデータを参照する直前まで入力します。
テーブル名を手入力すると入力したテーブル名の文字が青くなり、またテーブルの範囲も青い枠線で囲まれます。
テーブル全体を参照するならそのままでいいのですが、項目を参照する場合は「[」を入力します。そうすると項目名一覧が表示されるので、下矢印キーを使ってその項目を指定するのか指定します。
TABキーを押すと計算式に入ります。
ここで「]」を入力します。そうすると再度文字が青くなりその範囲も青で囲まれます。
ここで、すぐに、もし引数の続きがあるのであれば「,」を、もし関数の入力が終わるのであれば「)」を、計算式の入力が終わるならEnterキーを押します。
今回は続きの指定があるので「,」に続き手入力していきます。
Enterすれば結果が求められます。
構造化参照が出題される試験
構造化参照が出題範囲にはいっているのはMOS365 Excel Associate（一般）ですが、構造化参照はテーブルを使った基本的な使い方になるのでMOS365 Excel Expert（上級）でも出題されると思っていいです。また、同じ理由からバージョンが前の場合でも構造化参照は出題されるでしょう。
まとめ
今回はMOS365の出題範囲である構造化参照について解説しました。MOS365として構造化参照のポイントは、参照するデータがテーブルかどうかを見極めて、テーブルなら構造化参照、そうではない場合はセル参照、という区別ができるようにしておくのが最重要です。
参照元がテーブルにしてもセルにしても、操作は、マウス操作で範囲を指定した方が、構造化参照できる場合は構造化参照になり、セル参照の場合はセル参照に自動でしてくれるので安全です。
100行を超えるデータの場合、スクロールが大変なので、専用の1シートのテーブルデータになっていて、そこには計算式は作らないで他のシートに作るようになっていると思うので、構造化参照はしやすいと思います。
コメント