Excelでデータを追加するために行を足すと、合計を計算しているセル参照の範囲が変わるので、また計算式を作り直していることはありませんか。
面倒この上ありませんよね。Excelの作業というより手作業くらいの手間がかかっていそうです。
実はこれ、攻略法があります。普通のExcelとしての攻略法を紹介した後に、全くそういう苦労をしない方法の2つを紹介します。
Excel本来のセル参照の原理
まずは難しいことなく、普通になぜこんなことが起きるのかを考えます。
以下のような表があって、合計の値はB2からB5までの合計である「=SUM(B2:B5)」の式がセルB6に入っています。
5つ目の商品として１つ追加しようとするときは、6行目を選択して挿入しますよね。
そのとき、合計の計算式は挿入前の「=SUM(B2:B5)」のままになっています。だから皆さん困っています。
「=SUM(B2:B5)」のSUM関数は、B2を始まりとしてB5までの範囲を合計としていますよね。
その下に行を足してもSUM関数としては、私は関係ないところに挿入されたと思うので何もしないのです。
今度は、合計の範囲のど真ん中に行を入れてやりましょう。
そうすると、なんと、合計の範囲が「=SUM(B2:B6)」と広がるのです。
つまり、行を追加したときに、その範囲の中に行を追加すれば、ちゃんと範囲を大きくしてくれるのです。
でも、やっぱり5つ目の項目は6行目に入れたいものです。
その場合の対処としては、次のような方法があります。
6行目に高さが小さい行を1行挿入しています、そこまでの範囲を合計の範囲としています。SUM関数では空白は計算の対象にならないので6行目の空白になっていても問題ありません。
この状態で、6行目に1行挿入してみましょう。
ちゃんと「=SUM(B2:B6)」と広がっているのです。
このようにSUM関数の参照範囲をあらかじめ1行多くしておくことで挿入しても合計の計算式の範囲をいちいち変えなくてよくなります。
テーブル機能を使う
合計を求める表が一覧表である場合、絶対にテーブルにした方がいいです。様々なメリットがありますが、特に今回のような計算でああ投擲に使いやすくなります。
ではそのテーブル機能を実践してみましょう。
とりあえず合計がない一覧表にしました。合計は後から加えます。
この表の中のイスラ科のセルをクリックし、Ctrl+Tのショートカットキーを押します。
そうすると次のようなウィンドウが出てくるので「先頭行をテーブルの見出しとして使用する(M)」をクリックしてOKボタンをクリックします。
そうすると色がきれいにつきます。
この状態でリボンを見るとテーブルデザインタブに合っていて、この範囲にテーブル名を入力できます。テーブル名はわかりやすいものを設定したほうがいいですね。
この時点でもう一つ面白いのが、集計行です。
集計行をクリックすると、テーブルの下に集計行が現れます。
この自動で入る集計行、下向き三角で集計方法を変更できます。
平均やデータの個数も簡単に出せます。
この「集計」という文字は、書き換えて「合計」と入力することもできます。
これであれば1行足しても、計算式を変える必要がないのです。
この計算式を見てみましょう。
どうやら関数が入っているようです。
SUBTOTAL関数、あまり聞かない関数ですね。Subtotalは小計という意味です。
109というのが計算方法を表す番号で「合計」を表しています。
そしてその後の「[発売個数]」がポイントです。これはこのテーブルの発売個数すべてのデータ範囲という意味です。
テーブルになると、セルA1といった従来の範囲選択をする必要はなく、1行目にある項目名を入れるとその列のデータすべてを指すようになります。
いくら足しても自動でテーブル範囲は広がりますので、その都度対象となる範囲は変わらず、ずっと「[発売個数]」のままでいてくれるのです。
それなら一覧表は一覧表でデータだけ入れて合計は離れたセルで計算するということもできます。
集計のチェックを外せば集計行はなくなります。
この時点で1行開けて合計をSUM関数で計算するセルを足します。
そうすると計算式が「=SUM(ジオン[発売個数])」となります。
範囲がテーブル名[項目名]になってくれるのですね。
これでもやはりテーブルに1行足してもちゃんと計算範囲を認識してくれます。
またテーブルを右クリックすると、挿入が出てきて、その中にテーブルの行(下)があるので、これでテーブルに行を足せます。
ちょっと高度な使い方ですけど、Excelの機能を使った便利な使い方なのです。これから一覧表の管理と言ったらテーブルにするのが常識になるかもしれません。
まとめ
今回の記事のようにExcelは自動化を重視したツールですので、そのように作り上げていくことが必須です。
機能を理解し、計算書を自動的に構築することが肝要です。
そのためにはスキルアップは欠かせませんが、どうしても「勉強」になりがちです。
日ごろのExcelを使う中で、どうしたら手数を減らすことができるだろうということを探求することは、捉えようによっては楽しいことですので、そのように考えれば、勉強という苦しみから解放されます。
今回はExcelの計算範囲の原理を紹介しました。これは絶対的な基本です。一方で少し高度になりますがテーブルという便利機能も積極的に使っていきましょう。テーブルにはこの記事で紹介していないたくさんのメリットがあります。
