Excelテクニック：過去データと比較するための小計欄を作る方法

今回これを作ってみた理由

まずは以下の Excel表をご覧ください。

ウォーキングを趣味にしており、「年間1,000km歩こう」という目標を立てているのですが、その目標に対する進捗をこのExcelで毎月集計しています。

1ヶ月の歩行距離合計を該当する行に手入力し、14行目の「年間合計」で自動集計させています。

また、18行目には「目標の1,000kmまであとどのくらい残っているのか」を自動集計し、表示させています。上のスクショは1,000kmを超えたので、合計値がマイナスになっていますが、通常は「あと500km」のようにプラス値で表示されます。

新しい年が始まったら、表の右端に新しい列を追加。今回の例では「2015年」という列を追加しています。

2015年分も1月、2月と入力していったのですが、ここで1つ「不便な点」に気付きました。

14行目の「年間合計」、18行目の「目標までの残り距離」は、最新月の記録を入力すると自動計算されます。ここはいつも通りなので問題ありません。

年間合計の行には年間の合計値を自動集計する数式を入れています。簡単に言うと「2行目（＝1月）から13行目（＝12月）までの数値を合計する」というだけのシンプルな数式。Excelを既に使ってる方々であればお馴染みの「Sum関数」ですね。

これだと、今年（2015年）は2月までしか入力していませんので、2月時点での合計距離「108.19km」というのは分かるんです。

しかし過去のデータ、上のスクショだと2013年や2014年のデータは1月から12月まで入力済みですので、年間の合計値が既に表示されています。Sum関数だから当たり前。

今年は体調不良や仕事が多忙だった時期もあって、なかなか歩けていません。じゃあ、2年前や1年前は「2月の時点」でどのくらい歩いてたんだろう。それが気になりました。過去のデータと比較して、自分は今年は何kmほど足りないんだ、このままでは年間1,000kmが厳しいかもしれないぞ、というのを把握したかったのです。

当月時点での過去データと把握したいのですから、今年の当月時点は「約108km」と分かります。しかし過去データは年間合計値しか表示できていません。これでは比較ができない。2013年や2014年の「2月時点での小計」が必要だ、ということに気付きました。

そういうわけで、上のスクショのとおり「当月までの小計行」を作成し、年間合計欄のすぐ上の行に追加しました。過去データについても当月時点の小計を自動集計して表示し、今年と比較できるようにしています。

これにより「去年は2月までで178km、今年は108kmなので70kmも足りない！」と把握できるようになりました。

今回の記事では、この機能の作り方を解説します。最小限の入力だけで小計欄も合計欄も自動計算し、過去データとの比較も簡単に出来ます。

ウォーキングやジョギング、他にも「1年間で本を何冊読んだか」「貯金額を年間で幾ら貯めるか」というような「年間目標を立て、月ごとに実績値を入力して小計と年間合計を表示させたい」ときは、今回紹介する集計表で簡単に管理できますよ。

データ入力、合計欄の設定

今回紹介する作成法ならびにスクリーンショット画像は、MacのExcel（Office for Mac 2011）を使用しています。WindowsのExcelとは画面が異なるため、Windowsユーザーの方はスクショを見て「この画面は何？」となるかもしれません。機能自体はWindows版Excekにもありますのでご安心ください。

また、Excelの集計表作成に不慣れな人でもなるべく分かるよう初歩的なところから解説します。テーブル作成の基本的な方法をご存知の方は「手順8」までスッ飛ばしてもらっても大丈夫です。手順8の冒頭にて「ここまで作っておいてね」というサンプルを載せています。

手順1：表の見出しを入力する

まず、1行目に表の見出し行を作成します。A列には月を表示させるので、A1セルに「月」とだけ入力。B列以降は該当する年を入力します。

手順2：年月の見出し（1月〜12月）を入力する

A2セルに「1月」と入力し、Ａ2セルを指定した状態でカーソルをA2セル右下に持っていきます。カーソルが「＋」の形になったら下のＡ13セルまでドラッグ。2月から12月までが入力されます。

手順3：データ入力エリアの書式を変更する

Ａ15セルに年間合計行の見出しとして「年間合計」と文字を入力します。

次は2014年と2015年のデータに関する書式を設定します。B列とC列の列全体をマウスのドラッグなどで範囲指定し、青色に変わった場所ならどこでも良いので右クリック。サブメニューの「セルの書式設定」をクリック。

書式設定はお好みで構いません。今回の例では、分類を「数値」、小数点以下は「2」を指定し、「桁区切りを使用する」にチェックを入れます。

読書数管理や貯金管理など、整数しか入力せず小数点以下が必要ない場合は小数点以下の桁数を「0」にしてください。

手順4：テーブルに変換する

次に、集計表全体をテーブル（表）に変換します。表にしなくても今回の機能は実現できるのですが、Excelにおいてテーブル機能はかなり重要ですので、今回はテーブルを用いて解説します。

テーブルに変換するエリア（今回の例だとA1セルからC15セルまでの範囲）をドラッグなどで範囲指定し、メニューの「テーブルの新規」→「ヘッダーを付けて表を挿入」をクリック。こうすることでA1〜A3に入力済みの「月」「2014年」といった内容がデータではなく見出しとして認識されます。

テーブルへの変換が完了しました。

手順5：前年データを入力する

B列「2014年」の列に前年データを入力します。手順3で書式を変更したので、距離データが小数点以下も表示されています。

手順6：年間合計欄に自動集計用の数式を設定する

続いては年間合計欄の設定です。SUM関数をご存知の方はB15セルに直接入力してください。ご存知ない方は、B15セルを指定した後、メニューから「Σ」のアイコンをクリックし、選択肢から「合計」をクリック。

B15セルに集計用のSUM関数が設定され、集計対象としてB2〜B14が点線で囲まれて範囲指定されます。しかしB14セルは小計欄にする予定なので、年間合計の集計対象から14行目を除外する必要があります。

B14セルを集計対象から除外します。除外する方法は幾つかあります。

• 点線で囲まれた範囲指定の右下（＝B14セルの右下）をマウスでクリック後、B13までマウスを動かし、指定範囲を1セル分だけ狭める。
• 画面上部にあるSUM関数の数式欄で、数式の「B14」を「B13」に手入力で修正する。
• B15セルに表示されている数式欄で、数式の「B14」を「B13」に手入力で修正する。

これで年間合計欄の集計範囲が正しくなりました。

手順7：集計用の設定をC列（2015年）にコピーする

2014年の年間合計欄（＝B15セル）の右下にカーソルを持っていき、カーソルが「＋」になったら右にドラッグして、2015年の年間合計欄（＝C15セル）に合計用のSUM関数をコピーします。

この方法を使えば、B15セルでは「B2〜B13」だった集計対象範囲が、C15セルでは「C2〜C13」と、2015年の列を集計するよう自動的に反映してくれます。

ただし、現時点では2015年の各行（C2セル〜C13セル）にデータを何も入力していないので、年間合計欄は「0.00」となっています。では、テストとして何か値を入力してみましょう。

試しにC2セルとC3セルにデータを入力してみると、2015年の年間合計欄（＝C15セル）に合計値が正しく表示されていれば問題ありません。

合計が違うときは集計対象のエリア指定が間違ってますので、C15セルをクリックして、SUM関数の範囲が「C2:C13」となっているかを確認してください。

小計欄を作成する

手順8：小計見出しセルの書式を設定する

ここからは「小計欄」の作成作業です。手順1から手順7までの作業をスキップした人は、

上のスクショのようなExcelサンプルを作成しておいてください。15行目の「年間合計」行、B15セルとC15セルにはSUM関数で1年間の自動集計が出来るようにしています。

まずは、小計欄の見出し部分を作成します。小計の見出しセル（＝A14セル）を右クリックし、「セルの書式設定」をクリック。

書式設定で、分類を「ユーザー定義」、種類欄に「0″月時点小計”」と手入力します。0は数字のゼロで、ダブルクォーテーションは半角にしてください。

セルの書式設定を変更した直後は、上のスクショのとおり表示に変化はありません。現時点ではあくまで「書式」の設定を変更しただけですので、データがここに入力されない限り何も変化はないのです。

ではデータを入力してみましょう。

A14セルに半角数字の「2」を入力してから、Enterキーで確定させてください。

「2月時点小計」と表示されれば成功です。先ほど書式設定で「0″月時点小計”」と設定したことで、入力した「2」の後ろに「月時点小計」と文字列がくっついてくれます。

そんな手間をかけなくても、A14セルに直接「2月時点小計」という文字を入力すれば良いんじゃないの？ と思われる方がいるかもしれません。が、それではダメなのです。

A14セルは「2月時点小計」と表示されていますが、それはあくまで「書式設定のおかげで表示が変更されている」というだけの話。A14セルに実際に入っている値は「2」という数値のみです。

この「2」という数値は他の数式で後ほど利用することになるため、「2月時点小計」という文字ではダメ、「2」という数値でなければなりません。ただし、見出しに「2」としか表示されていないと「何だこれ？」となってしまうため、見た目を分かりやすくするため書式設定で変更した、という訳なのです。

見た目の問題ということで書式設定を変更したA14セルですが、ここだけ右寄せ文字になっていると見映えが良くないので、「左寄せ」に変更しておきます。あくまで見映えだけの問題なので、左寄せにしなくても問題なく正常に処理はされます。

手順9：小計欄に集計用の数式を設定する

見出し欄は完成しました。続いては、2014年の小計欄を作成します。

2014年の小計欄（＝B14セル）をダブルクリックして、以下の数式（赤文字の部分のみ）を入力します。画面上部の数式欄に入力しても構いません。

『=SUM(OFFSET(B2:B13,0,0,$A$14))』

今回の数式「=SUM(OFFSET(B2:B13,0,0,$A$14))」がどんな機能なのかを解説すると、「B2セルからB13セルの範囲の中で、先頭から★セル分だけの合計を計算する」という内容になります。

「先頭から★セル分だけ」のところですが、★の中に入る数値はA14セルの値、つまり先ほど「手順8」で作業した、小計欄の見出しに入力した「2」という数値がここで使われることになります。したがって「先頭から2セル分だけ」という意味になります。

OFFSET関数の最後で「何セル分なのかはA14セルに書かれてますよ」という指定をしているのですが、「A14」ではなく「$A$14」と書いてます。これはA14セルを常に固定させたい時の書き方で、「＄」マークを付けることでコピー＆ペーストした際にセルがズレてしまう現象を防止してくれます。

では実際にコピーしてみましょう。

手順10：小計欄の数式を2015年（C列）にコピーする

「手順2」や「手順7」でやったのと同じ方法で、2014年の小計欄（＝B14セル）の右下にカーソルを合わせ、カーソルが「＋」の形になったら右にドラッグして、2015年の小計欄（＝C14セル）にコピーします。

2015年の小計欄（＝C14セル）にコピーされた数式の内容を確認してみると、範囲指定はドラッグコピーしたことで「B2〜B13」という範囲式が「C2〜C13」に自動変換されています。

一方でA14セルは「$A$14」と指定していますので、コピーしても自動変換されず、A14セルのまま固定されています。

では1つテストをしてみます。小計見出し欄（＝A14セル）に入力していた「2」を「3」に変更し、Enterキーで確定させてみてください。

まず、見出しのA14セルは「3月時点小計」と表示が変わりました。

2014年の小計欄（＝B14セル）は、先ほどまでは「1月から2月までの小計」が表示されていましたが、A14セルを「3」にしたことで「1月から3月までの小計」という式に変更され、実際に3ヶ月分の小計値がB14セルに正しく表示されていることが確認できます。

なお、2015年の3月（＝C4セル）は空白なので、集計範囲は3ヶ月分（C2〜C4）に変更されたものの、小計欄（＝C14セル）の数値自体に変化はありません。

これで、毎月の入力を済ませた後にA14セルで月の数字を変更すれば、該当月時点での小計を自動計算してくれるという仕組みが完成しました。

年間目標達成までの残り数値を表示させる

毎月のデータを入力して、年間合計と最新月時点での小計を自動集計するテーブルは手順10までで完成しました。

次は、年間目標達成までにあとどのくらい必要なのかを確認できるよう、「目標距離」と「残り距離」の表示欄を作成します。

手順11：「目標距離」欄を作成する

手順8でやったのと同じ事をここでも設定します。A17セルを右クリックして、セルの書式設定として「“年間目標の「”#,##0″km」まで”」というユーザー定義を指定します。

書式設定が完了したらテストをしてみます。A17セルに「100」と半角で数値を入力してみましょう。

最初は上のスクショのように「＃（シャープ）」がズラーっと並んだ表示になっていると思います。

これは表示させたい文字の長さに対してセルの横幅が足りていない時になる症状ですので、A列全体の横幅を広げてあげれば解決します。ただ、A列は上のテーブルにも関係していますので、A列を広げる方法だとテーブルの月の欄も無駄に広がってしまい、カッコが悪い。

なので、右隣りのB17セルと連結させてしまいましょう。

A17セルとB17セル、2つのセルをドラッグなどで範囲指定してから、メニューにある「セル結合」をクリック。

セル結合により横幅が広がったので、文字列が正常に表示されるようになりました。現在A17セルには「100」が入ってますので、『年間目標の「100km」まで』と表示されています。

では「100」から1ケタ増やし、「1000」に変更してみましょう。

書式設定で数値部分を「#,##0」と指定したので、カンマが必要な桁数（今回の例だと4ケタの1000）を入力すると自動的にカンマを挿入して表示してくれます。あくまで書式設定は表示だけの話ですので、実際に入力されているのは「1000」という数値です。

手順12：目標達成までの「残り距離」欄を作成する

目標達成までの残り距離を表示させるため、A18セルも書式設定します。

ユーザー定義で「“あと “#,##0.00″km”」と指定してください。カンマが必要な桁数にも対応し、小数点以下も表示できる指定です。

手順11と同じく、ここも横幅が足りなくなると「＃」だらけで表示してしまうので、A18セルと右隣りのB18セルを結合させておきましょう。

まだ値は何も入力されていないので、結合されたA18セルは空白になってます。

「残り距離」欄は、値を手入力するのではなく、年間目標（＝A17セル）から2015年の年間合計（＝C15セル）を自動でマイナスするようにしたいので、数式をセットします。

A18セルに「=A17-C15」という数式を入力します。

数式を入力した後で Enterキーを押して数式を確定させると、目標達成までの残り距離が自動計算され、「あと◯◯km」という書式で表示されました。自動計算ですので、次に3月のウォーキング歩行距離を入力すれば、A18セルも3月分の距離を反映して自動計算されます。

年間目標のA17セルを「1000」から「1200」に変更すると、A18セルはA17セルの値を基に自動計算しますので、目標までの残り距離（A18セル）も瞬時に反映されます。これがExcelの便利なところですね。

最新月を自動で入力する方法

「手順8」から「手順10」までの一連の作業により、A14セルの「小計の見出し欄」に数値を入力すると、その数値を元に小計を自動計算し、表示するという機能が完成しました。

この機能のメリットは「今年の最新月の小計だけでなく、過去の小計も同時に確認できる」という点です。たとえば、A14セルに「6」と入力すれば、今年の1月から6月まで半年間の小計が自動計算されるだけでなく、過去の年も同じように半年間の小計を自動計算し、表示してくれます。A14セルの数値を変更すれば、それに応じた月数の小計が表示されます。

一方、この機能のデメリットは、たとえば5月になり、4月分の歩行距離を入力したとしても、A14セルが「3」のままだと3ヶ月分の小計が表示されてしまいます。正しい小計が見たければ、A14セルを自分で「4」と変更しなければならない。これが面倒だと感じる人もいるでしょう。

これが面倒だと感じる人のための機能も今回紹介します。具体的に説明すると、「3月分の距離を入力すると同時に、A14セルに「3」と自動入力させたい」というもの。

小計の見出し欄（＝A14セル）にCOUNT関数を入力します。集計する範囲は2015年の1月から12月まで、つまりセルだと「C2:C13」という範囲になります。

なので、A14セルに『=COUNT(C2:C13)』という数式をセットしてみてください。すべて半角文字にしましょう。全角文字が混じっていると正常に動作しません。

入力完了したらEnterキーで確定させると、

上のスクショの例だと、現在2015年は1月と2月の「2個」を入力してるので、COUNT関数によりA14セルに「2」が自動でセットされます。「手順8」で書式設定を変更したので、表示は「2月時点小計」となっています。

小計の数値も、2014年の小計（＝B14セル）、2015年の小計（＝C14セル）の両方とも正しい数値が自動計算され表示されています。

ではテストとして、3月に「100」を入力してみます。

データを入力した月の数が「3」に増えましたので、A14セルの小計見出し欄に「3」が自動入力され、表示も「3月時点小計」に変わりました。

「3」という数値が自動セットされたことで、B14セルやC14セルの小計欄も自動計算され数値が更新されます。さらにA18セルの「残り距離」欄も自動集計され表示が変わりました。

これで「A14セルに毎回数値を入力しないといけない」という手入力作業が1つ削減できました。

まとめ

Excelには「Excel VBA」というプログラミング機能が付いています。Excel VBAをマスターしてしまえば、プログラムによりどんな機能でも比較的簡単に作れちゃいます。しかしプログラミングを修得するまでがちょっと大変。

そういう意味では、Excelをどれだけ便利に活用できるかは、便利な関数をどれだけ知っていて、使いこなせるかに掛かっているような気がします。関数を駆使して数式を作っていけば、いろんな便利機能が実装できますからね。プログラミングを知らなくても出来ますし。