Excel の表では、数値や文字などのデータを入力するだけでなく計算式を作成することがあります。
今回は、テーブル内での数式の作成の特徴と動きをご紹介します。

 

■数式のオートコレクト
Excel の表でよくあるのは、作成した計算式をほかのセルにもコピーするという操作です。これを
例に見てみます。

ここで作る数式は、以前、「行番号を使って表に連番をふる －ROW 関数－」でご紹介した
ROW 関数による行番号の取得を利用した連番の作成です。ROW 関数については以前の投稿を
ご覧になってください。

テーブルの列のセルに数式を入力して [Enter] キーを押すと、選択しているセルに計算結果が表示され、同じ列のほかのセルにも数式がコピーされます。
既定では [オートコレクトのオプション] (雷のマークの小さなボタン) も表示されます。
 
 
 

[オートコレクトのオプション] をクリックすると、[元に戻す － 集計行]、[集計列の自動作成を停止]、
[オートコレクト オプションの設定] と表示されます。
 

「今はほかのセルにはコピーしてほしくない」という場合には [元に戻す － 集計行] を、
「今後もほかのセルにコピーをする機能は使いたくない」という場合には [集計列の自動作成を停止] を使います。

[オートコレクト オプションの設定] をクリックすると、[オートコレクト] ダイアログボックスが表示されます。

ちなみに、[オートコレクト] ダイアログ ボックスは、[Office ボタン] をクリックして [Excel のオプション] をクリックし、[Excel のオプション] ダイアログ ボックスを表示して [文章構成] の[オートコレクトのオプション] をクリックしても表示できます。

 

 

 

もう数年使っていますが、私はあまりこの機能を邪魔に思うことはなかったかな・・・。
使われる目的や場面によって異なると思うのでなんとも言えませんが。
数式を作成する列 (集計列) を使う場合には自動的に一番下のセルまで数式がコピーされるので
便利ですね。
 

■テーブルの構造を利用した数式の作成

ここでは、「販売データ」テーブルの [単価] 列に入っている値と、[数量] 列に入っている値を
掛けて「金額」を求めたいとします。
テーブルではない標準の表の場合も「=」を入力して、計算元の値が含まれるセルをクリックして
選択しますよね。これをテーブルで同じように操作するとどのような結果になるか見てみましょう。

 

1. 計算式を作成するセルに「=」を入力して、同じ行の [単価] 列のセルをクリックします。
計算式には、「=販売データ[[#この行],[単価]]」と表示されています。
これは、「販売データ」テーブルの、計算式を作成しているセルと同じ行の [単価] 列と
いう意味です。「E4」とは表示されないのです。

 

2. アスタリスク (*) を入力して、同じ行の [数量] 列のセルをクリックします。
計算式には、「=販売データ[[#この行],[単価]]*販売データ[[#この行],[数量]]」と表示されています。
この状態で [Enter] キーを押します。

 

3. テーブルが拡張されて集計列が作成されます。
また、計算結果が表示され、同じ列のほかのセルに数式がコピーされます。
先にテーブルの範囲を拡張しておかなくても、計算式を作成したときに自動的に拡張されています。

セル G 4 にも G18 にも「=販売データ[[#この行],[単価]]*販売データ[[#この行],[数量]]」という数式が同じように表示されていることを確認してください。
「#この行」ってどこ？と考えてみましょう。G4 にとっての「この行の単価」はセル E4 で、
G18 にとっての「この行の単価」はセル E18 ですよね。
データベースのテーブルのように、列ごとに同じ種類のデータを収めている表では、
数式の入っているセルと入っていないセルがあるとか、セルによって数式が違うというのは、
避けたいものです。テーブルのこんな機能も効率アップとミスの防止の両方をサポートしてくれますね。
 
なお、テーブルの列の先頭 (見出し) のセルは空白にはできません。そのため、自動的に拡張されて
作成された列の見出しには「列1」という列名が表示されています。
必要に応じて変更してください。(今回の場合だと「金額」がよいかしら)

そして、[No] 列の連番を ROW 関数で求めると、「ROW 関数の行番号を使いやすくする ―リスト機能―」でご紹介した動きと同じように、後から行を追加したり、削除したときに自動的に連番が
調整されるというメリットがあります。
また、テーブル内の列に計算式が作成されている場合、あとから縦方向の範囲が拡張されたときに
自動的に数式もコピーされるのでとても便利です。
 

テーブルを使うことのメリットをいくつか書いてきましたが、もちろんこれらだけではないです。

テーブルのお話をすると、間に小計が入れられないとねーとか、セルを結合したいんですけど、、、と
いったご質問やリクエストをいただくことがあるのですが、これを基本的なことからきちんと
ご理解いただかないといけないのだなと反省します。

今回の投稿に特化したお話しではないですが、
テーブルがきっかけでもよいので、そろそろ、データを入力して格納しておくための場所 (シートや
ブック、データベースなど) と、印刷に適した？思い通りの？見栄えを整える場所を
区別していかないといけないのではないかと思っています。

お客様からは、基本的には現状の表や仕組みのままで、新機能を使ったら何ができるかを教えて。と
いうリクエストをいただくこともあるのですが、正直なところ、データの持ち方や表の作り方、
考え方を変えない限り、新しいバージョンだから飛躍的にどうこうはならないです。
たくさんのユーザー、何年もの間使ってきたファイルとルール、これらを変えるのは容易でないことはとってもよくわかるのですが、どこかで変えないとずっとそのままですね。

「紙で印刷するため」「この書類と同じ見栄えにするため」というデータの持ち方、表の作り方は、
そろそろやめていかないと。地道にお話ししていこうと思います。

石田 かのこ