Excelでテーブルに数式をセットする際の注意点

「知っていると得するかもしれないExcelの癖」シリーズ第九回、テーマは「テーブルでの数式利用」になります。
Excel 2016バージョン1902を使用して確認しています。
なお「テーブルとして書式設定」の機能につきましては「行を1行おきに色分けする方法」のテーマで説明していますので合わせてご一読いただければ幸いです。

Excelのテーブル機能は、表計算ソフトのセル参照機能の延長として捉えるのではなく、データベースソフトのテーブル機能として捉えた方が理解し易いことと存じます。

いきなりそんなこと言われても「ちょっと何を言っているのか良く解らない」と感じられることと存じますが、まずはテーブルに数式をセットする際に一番重要となる「構造化参照」という言葉について掘り下げてみたいと思います。

「構造化参照」とは?

「構造化参照」については、既に説明をされているページがございます。その中でもMicrosoft MVPを受賞されている下記「Be COOL Users」サイトのページは解り易くまとめられていますので、まずはご一読いただければ幸いです。

テーブルが関わる数式に注目！【Excel・エクセル】

テーブル内のセルを使って数式を組むと、その数式は、普段とは違う「構造化参照」という形式の数式になります。今回は、この構造化参照をざっくり理解します！Excel・エクセルの技！

www.becoolusers.com

なお上記とは別のMIcrosoftの「Excel テーブルでの構造化参照の使い方」のページの中では、テーブル内に数式を入力する場合はセル参照(相対参照や絶対参照)ではなく、「テーブルの名前」や「列の名前」を使用する構造化参照を利用することが推奨されています。

この構造化参照のポイントは下記の2点、「@」と「#」の使い方になる認識です。

• 構造化参照のなかに「@」という表現があるのですが、この「@」はデータベースソフトにおけるカレントレコードと同じく「現在参照している行」を指し示す「特殊項目指定子」の1つになります。
• 特殊項目指定子という表現は、先ほどの「Be COOL Users」ページには出てきませんが、『「#（シャープ）」が付いた表現』という表現で書かれており、［#集計］とか［#見出し］という形で数式の中で使用されます。

ここで「@」や「#」を使用した数式の説明に入る前にテーブルの持つ特徴についてまとめて置きたく存じます。

テーブルの持つ特徴

①テーブルの範囲は名前で管理される

テーブルを設定すると、数式タブ→「定義された名前」リボングループの「名前の管理」にデフォルトで「テーブル1」という名前が自動的に設定されます。

この自動設定された名前は、テーブル全体が削除されるか、「範囲に変換」されるかしないと削除することはできません。
(左図のように削除ボタンが無効化される)

なおこの名称は、テーブルのどこかをクリックした時に表示されるデザインタブ→「プロパティ」リボングループのなかのテーブル名として表示されます。

②テーブルには見出し行が設定される

構造化参照で使用される「列の名前」は必須になり、見出し項目を持たない表では、見出し行が自動的に挿入されて表自体が1行下に移動します。。

そのためテーブル設定された見出し項目を含む行を行範囲で選択し、マウス右ボタンでショートカットメニューを表示した時には、「削除」が無効化されています。

逆説的な表現をすると、「名前の管理」も含めて「テーブルのために本来の表計算ソフトの機能を制限している」ことになります。

③テーブル限定のオートコレクト機能

オートコレクト機能は、文章入力時のスペルチェック機能が良く知られているという認識ですが、テーブルのためにだけ追加されているオートコレクト機能があります。

ファイルタブ→オプション→(Excelのオプション画面左側のメニューから)文書校正→オートコレクトのオプションボタン→入力オートフォーマットタブ

かなり奥まったところにあるのですが、この「入力オートフォーマット」タブの中の左図赤枠の設定はテーブルに限定した機能になります。

なおこの機能を説明するMicrosoftのページ「入力オートフォーマットを構成するかオフにする」は存在するのですが、Wordにおける説明がメインで構成されています。
そのページに貼り付けられている画面もWordの入力オートフォーマットの画面になっておりますが、Excelの説明も含まれていますのでご安心ください。

ただし混ざりあっていると解り難いので冗長的ではありますが下記に転記致します。

最初のオプションはテーブルの書式に関するものであることが解ります。
このチェックボックスのチェックを外した状態で、テーブル内に行または列を追加しても、追加した行または列は書式を引き継ぎますが、隣接した行または列でデータを入力した時には自動的にテーブルに取り込まれる(テーブルの範囲が拡張される)ことはなくなります。

二番目のオプションはExcelの機能の中でも、かなり強権的といいますか、有無を言わさずといいますか、合っていようが無かろうがまず適用されてしまいますので、適用除外の仕方も含めて解ったうえで使用するのが良い認識です。

④テーブルを横に並べると「挿入・削除・数式と値のクリア」が制限される

数式には直接関係しない特徴ですが、テーブルの配置を決める際に知っていた方が良いと存じます。

左図のようにテーブルを横並びにすると、双方が重なる行でマウス右ボタンからショートカットメニューを表示すると、「挿入・削除・数式と値のクリア」が無効化されます。

行が重ならなければ、赤枠の無効化は解除されますが、テーブルを配置するシートのデザインを考える時は注意が必要です。

なお、個々のテーブルを選択すればテーブルごとでの行の挿入・削除はできますのでご安心ください。
できないのはシートに対しての一行挿入です。

テーブルでの数式はカレントレコードを処理するのが基本

まずは、構造化参照を使った数式の基本的な考え方を整理したいと思います。

一般的にデータベースからレコードを読み出して処理をする時には、テーブルに格納されているデータからキーとなる項目を使って一つのレコードを取り出します。

これに対してExcelのテーブルにはキー項目は存在しません。
それはテーブルは、すべてのデータが目に見えている形で存在するために、いちいち取り出す必要がないためです。

ただし、そのようなテーブルの中でもカレントレコードにあたる考え方は存在し、それがExcelシートでフォーカスを持ったセルの行になります。

もし行指定で複数行選択していたとしても、その行範囲のなかでフォーカスを持ったセルは最初の行になっています。
これは先頭行の最初のセルだけ背景色は無く、他のセルと背景色のかかり方が異なることで解ります。

従って、クリックさえすればテーブル内のどこの行でもカレントレコードになるため、構造化参照を使うと[@列見出し名]でテーブル内のセルの値を取り出すことができるようになります。

この点がセル参照の場合と大きく異なるところです。

テーブル内の数式で前行を参照することは想定外

『構造化参照を使うと[@列見出し名]でテーブル内のセルの値を取り出すことができる』という説明に納得がいかない人は、「前行のセルの情報を見る必要がある時はどうしたらよいのか?」ということが気にかかっていることと存じます。

データベースソフトを使用する場合だと、「一つ前のレコード」を意識する事はありませんが、表計算ソフトの場合は確かにそういった処理をすることもあり得ます。

ただし、構造化参照では前行の項目を参照するという「特殊項目指定子」は提供されていません。
となるとテーブル内でセル参照を利用することは推奨されていない以上、前行を参照する方法としてはOFFSET関数を使うしかありません。

しかし一般的に馴染みが薄いOFFSET関数と聞くと皆様の気持ちが折れてしまう事と存じます。

では、実際にテーブル内でセル参照を使用したらどのような動きになるのか?を見て行きたいと思います。

数式入力時にオートコレクト機能が働く場合と、働かない場合

なかなか具体的な数式設定の話題に入れず恐縮ですが、テーブルに数式を入力するやり方により、オートコレクト機能が働く場合と、働かない場合がありますので、「同じ数式を設定しているのに動きが違う」といった混乱をおこさないように、注意が必要です。

「テーブルに数式を入力するのに、どれだけ前提知識が必要なのか?」というお怒りはごもっともでございますが、本テーマではこれが最後になります。

①数式でない値が列に設定されているとオートコレクト機能は働かない

セルに値が入っていると、オートコレクト機能により上書きされることはありません。
ただし値が下記の形(＝を付けてた形)で入っている場合は上書きされますので注意が必要です。

=100

逆な言い方をしますと、「既に数式が入っていたとしても、後から入力した数式で上書きされてしまいます」ので注意が必要です。

②ショートメニューからの「集計列の自動作成を停止」はブック全体に反映する

オートコレクトのオプションアイコンをクリックすることで表示される左図のショートカットメニューで「集計列の自動作成を停止」を選択すると、すべてのシートでオートコレクトが働かなくなります。

これを元に戻すには前段の「テーブルの持つ特徴」見出しの③に記載した手順でオートコレクトのオプション設定を元に戻す必要があります。

従いまして、その列にだけ設定されたオートコレクト機能を元に戻すしたい時は、左図ショートカットメニューの一番上の「元に戻す-集計列」を選択してください。

③オートコレクトで設定された数式を2行以上削除した後、別の数式を入力した時はオートコレクトは働かない

上記の「元に戻す-集計列」のほかに、手動で2ヶ所以上、オートコレクトで設定された数式を削除した場合、空白になったセルに数式を入力してもオートコレクトは働きません。

逆な言い方をしますと、一つのセルだけを削除して、そこに新しい数式を入力すると今まで設定されていた数式がすべて上書きされてしまいますので注意が必要です。

なおもしもオートコレクト機能を復活させたい場合は、一度その列のすべての数式を削除してから再度数式を入力してください。

テーブルに数式をセル参照と構造化参照で設定する

ようやく本テーマの本題に入ります。
ここではオートコレクト機能を使用して実際の動き方を確認したいと思います。

①列1に行番号表示の数式を入力する

A1:D11のセル範囲に「テーブル1」という名前のテーブルを設定し、A2セルに連番を表示するための数式を入力します。数式としては下記を使用します。

=ROW()-ROW(見出しが設定されている行)

見出しが設定されている行の設定は、絶対参照であれば

• ROW($A$1)
• ROW($1:$1)

などの形になりますが、構造化参照では下記のような設定になります。

=ROW()-ROW(テーブル1[#見出し])

A2セルに数式を入力すると、オートコレクト機能により、対象列のすべての行に数式がセットされます。

②列1の累計を表示する数式を3パターンで列2から列4に入力する

オートコレクトを働かせるためには、一列すべて同じ数式にする必要があります。
そのため、累計計算のためには前行を参照する必要がありますが、先頭行の数式だけを変えることはできません。

そこで、先頭行では前行となる見出し行は文字列になることを判定条件として、「前行が数値であるか?」をCOUNT関数で確認するためにIF関数を数式に含めています。

C列とD列の数式の違いは、構造化参照かどうかになりますが、構造化参照の場合は、すべての行の数式がまったく同じ数式になるのに対して、C列のセル参照の場合は、セル参照のところが各行で異なります。

なお結果としては下左図のように列2から列4は同じになります。
この状態で4行と5行の間に1行挿入をしてみます。
結果は下右図になります。

5行目に1行挿入されたことで、オートコレクト機能によりすべての列で数式が自動的にセットされていますが、B列の赤枠のところをよく見ると計算が正しくありません。

B6セルの左上隅にエラーインジケーターが表示されていますが、それ以外はExcelは何もメッセージを出してくれません。

=IF(COUNT(B4)=0,0,B4)+A5

=IF(COUNT(B4)=0,0,B4)+A6

まとめ

テーブルに数式を設定する際に、いろいろと知ってなければならないことが多くあり長文になってしまったこと、それに対してまとめが短いこと、ご容赦ください。

テーブルでの数式を構造化参照にした方が良いかは、意見が分かれるところだと思いますが、ただしテーブル内で前行参照が必要な場合は、セル参照のままで数式に設定する事は避けるべきです。

思わぬ計算間違いを引き起こす可能性があります。

以上、最後までご一読いただきありがとうございました。