これを作ってみた動機

今回のエントリータイトルを読んだだけでは何を作るのか良く分からんですよね。タイトルどうするか相当悩んだんですよ。

なので、実物をお見せしながらいろいろ説明します。まず、今回このエントリーを作るキッカケとなったExcel表について。

↑ 2013年から「ウォーキングで年間1,000km歩く」という年間目標を立ててます。iPhoneのウォーキング計測アプリで毎回記録した歩行距離の月間合計をExcelに記録して、年間目標まで残り何kmかってのを把握するようにしてます。

↑ 今年2015年も引き続きExcelで管理するため、列を追加しました。

↑ 1月、2月と、例年どおり記録していったんですが、1つ不便な点に気付きました。

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

ただ、今年の2月は体調不良や仕事の多忙などが重なって全然歩けませんでした。2年前は84km、昨年は115kmも歩いてるのに、今年はたったの50km。

以前「月次目標達成のためには週次目標への落とし込み（ブレイクダウン）をもっと意識せんとイカンよね」というエントリーを書いたのですが、今回のも全く同じことなんですよ。年間目標を達成するためには月間の歩行記録をもっと意識し、達成するために現在ペースはどうなのか、大丈夫なのかヤバイのかを把握しなきゃいけない。

それで気付いたのが、「昨年の2月までに何km歩いてたんだろ、2年前はどうだったんだろ」という前年同月時点での小計値が欲しいなあ、ってことでした。

↑ そういうわけで、最新月までの小計行を追加してみました。過去2年に関しても「2月時点の小計」を表示するように作ったので、「去年は2月までで178km、今年は108kmなので70kmも足りんのか」ってことが把握できるようになりました。

年間合計欄の自動集計は当然ですが、今回追加した小計欄も、過去の分をイチイチ入力なんて超めんどくさいですから、簡単な指定だけで過去年を含む全ての小計を自動集計させるようにしました。

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

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

今回紹介する作成法ならびにスクリーンショット画像は、MacのExcel（Office for Mac 2011）を使用しています。WindowsのExcelとは画面が異なりますのでご了承ください（同じ機能はWindows版にもありますので、今回のサンプルはWindows版Excelでも作れます）。

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

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

↑ まず、1行目に見出し行を作成します。A列には月を表示させ、B列は2014年の過去データ、C列には今年のデータを入力しますのでＡ・Ｂ・Ｃの1行目にそれぞれ見出しを入力しましょう。

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

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

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

↑ Ａ14セルには小計の見出しを入力しますが、ここは後で作業するのでいったん放置。その下、Ａ15セルに「年間合計」と見出しを入力します。

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

↑ 分類を「数値」、小数点以下は「2」を指定し、「桁区切りを使用する」にチェックを入れます。読書数管理や貯金管理の時は整数しか入力しないと思いますので、その場合は小数点以下を「0」にしても問題ありません。桁区切りのカンマは見映えの問題だけですので、お好みでどうぞ。

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

↑ 次に、集計表全体をテーブルに変換します。テーブルにしなくても大丈夫なのですが、今後のスキルアップを考えるとテーブル機能は大変重要なので、今回はテーブル作成を前提として解説します。

テーブルに変換したいエリア（今回の例だとA1セルからC15セルの範囲）をドラッグなどで指定し、メニューエリアにある「テーブルの新規」というアイコンの右側にある矢印の部分をクリック。

選択肢が表示されますので、「ヘッダーを付けて表を挿入」をクリック。こうすることでA1〜A3に入力済みの「月」「2014年」といった内容がデータではなく見出しとして認識されます。

↑ テーブルへの変換が完了しました。初期設定の色が自動的に設定されますので、色を変更したいときはメニューの「テーブル」にあるお好きなカラーパターンを指定してください。

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

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

私は前年の本物データをコピーしました。前年データがない人は何でもいいのでテキトーな数値をB列に入力しておいてください。

★手順6：合計欄に集計用関数を設定する

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

↑ B15セルに集計用のSUM関数が設定され、集計対象としてB2〜B14が点線で囲まれて範囲指定されます。しかしB14セルは小計欄にする予定なので、年間合計の集計対象から外さなくてはなりません。

↑ 集計対象を変更する方法はいろいろあります。

上記3つのどれでも結果は同じになります。これで年間合計欄の集計対象が正しくなりました。

★手順7：集計用の設定をC列にコピーする

↑ 手順2でやったのと同じ方法で、2014年の年間合計エリア（＝B15セル）の右下にカーソルを持っていき、カーソルが「＋」になったら右にドラッグして、C15セルに合計用のSUM関数をコピーします。

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

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

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

小計欄を作成する

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

ではでは、本題の小計欄作成に入ります。手順1〜手順7をスッ飛ばしてここまで来た人は、

↑ 手順1〜手順7の解説で上の状態まで完成させてますので、パパッと上のように作成しておいてください。ここから先は続きの解説です。

↑ 小計の見出しセル（＝A14セル）を指定してから右クリックし、「セルの書式設定」をクリック。

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

↑ 設定変更直後は、変更前と表示が変わりません。

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

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

これが何を意味するかというと、見出しに「2月時点小計」と文字列で入力してしまうのではなく、「2」という数値を使うことで、他のエリアで集計する時などにデータとして二次使用できる、ということです。「2月時点小計」という文字列を直接入力してしまうとそれが出来ません。

ナンノコッチャ？という方はスルーしてください。あとで意味が分かります。簡単に言うと、表示は書式設定によって「2月時点小計」にしてますが、実際にExcelが認識してる入力値は「2」という数値だけだ、ということです。簡単じゃないか(汗)

↑ ここだけ右寄せ文字でカッコ悪いので、左寄せに変更しておきましょう（変更しなくても処理上の問題はありません、見映えだけの話なので）。

★手順9：指定月までの小計欄に集計関数を設定する

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

SUM関数は「指定された範囲の合計を計算する」という関数。その範囲指定にOFFSETという関数を使っています。意味としては「B2〜B13セルのうち、A14セル（＝2月時点小計のとこ）で指定した値の行数の範囲」みたいな感じになります。

A14セルには現在「2」が入ってますので、小計欄での集計範囲は2行分、つまりB2〜B3の2行ということになります。これが手順8の最後で書いた「見出し欄を数値にして、他の集計欄で二次使用する」という作戦です。

OFFSET関数の最後で「$A$14」と書いてます。これはA14セルのことなんですけど、他のセルにコピーした時などにセルの位置指定をExcelが勝手に変更したら困るので、「ここの値の場所は何があってもA14セルなんやで！」と固定させたいときにドルマークを付けます。

では実際にコピーしてみますよ。

★手順10：小計欄の数式をC列にコピーする

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

↑ C14セルにコピーされた数式の内容を確認してみると、範囲指定はドラッグコピーしたことで「C2〜C13」に自動変換されています。一方で固定したいA14セルは「$A$14」と指定したことで、ドラッグコピーしても自動変換されずA14セルのままになっています。

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

↑ 見出しのA14セルは「3月時点小計」と表示が変わり、2014年の小計欄（＝B14セル）も数値が自動更新されます。OFFSET関数で使っている範囲指定の数がA14セルで「2」から「3」に変更されたので、集計範囲も1月から3月までの「3行」に変わりました。

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

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

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

毎月のデータを入力して、年間合計と最新月時点での小計を自動集計するテーブルは手順10までで完成しました。最後に、年間目標達成までにあとどのくらい必要なのか、残り数値の表示欄を作ります。

★手順11：年間目標の数値欄を作成する

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

↑ 書式設定が完了したら、設定したセル（私の例だとA17セル）に「100」と半角で数値を入力してみましょう。

↑ おそらく上のように「＃（シャープ）」がズラーっと並んだ表示になってるのではないでしょうか。Excel業界では「シャープ祭り」と呼んでる症状です。ウソだけど。

これは表示させたい文字の長さに対してセルの横幅が足りていない時になる症状ですので、A列全体の横幅を広げてあげれば解決するんですけど、それをやっちゃうと上のテーブルまで広がってしまい、カッコ悪くなっちゃいます。

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

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

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

じゃあ、1000と入力すると？

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

★手順12：目標達成までの残り数値欄を作成する

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

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

↑ 手順11と同じく、ここも横幅が足りなくなると「シャープ祭り」になるので、A18セルと右隣りのB18セルを結合させておきましょう。

まだ値は何も入力されていないので、結合されたA18セルは空白になってます。ここに値を入力するのではなく、数式を指定しましょう。

残り数値は、年間目標（＝A17セル）から2015年の年間合計（＝C15セル）をマイナスすればいいので、

↑ A18セルに「=A17-C15」という数式を入力します。セル番号は「A17」などと手入力してもいいですが、イコールの記号を入力した後で、A17セルやC15セルをマウスなどでクリックする方法でも簡単に入力ができます。

↑ Enterキーで数式を確定させると、目標達成までの残り数値が自動計算されて表示されました。自動計算ですので、次に3月のウォーキング歩行距離を入力すれば、A18セルも自動的に更新されます。さらに、

↑ 年間目標のA17セルを「1000」から「1200」に変更すると、A18セルはA17セルの値を基に自動計算しますので、目標までの残り数値にも反映されます。

最小限の入力だけで小計や目標値を自動集計＆表示できる集計表のサンプル作りは以上となります。お疲れ様でした。

りくま ( @Rikuma_ )的まとめ

欲を言えば、たとえば3月のウォーキング距離実績を入力したら、Excelが「いま3月分を入力したぞ！」と感知して、小計見出し欄に「3」と自動セットしてくれるような仕組みを作りたかったんです。入力作業がさらに簡素化できますからね。

しかし私の現時点での知識レベルでは分かりませんでした。いろいろ調べたけど分からんかった〜。関数でそういうのがあるのかな。「Excel VBA」というプログラミング機能を使えば実現できそうなんですけどね。

「Access VBA」にはそういう機能があるんです（Accessは以前「本職」だったので、ある程度は分かります）。だからExcelにも多分あると思うんですけどね。まだExcel VBAも勉強中なので、作り方が分かったら実装します。その時にはブログでも紹介できればいいな。

またExcelで何か作れたら紹介しますね。

追記：月を自動入力させる簡単な方法があった！

エントリー公開後、SNSにてフォロワーさんから名案を教えて頂きました。COUNT関数を活用するというもの。

COUNT関数は、範囲指定内で数値を入力しているセルの数をカウントしてくれます。なんとなくしか知らなかったので、閃きもしませんでした。これを使って機能追加してみました。

↑ 小計見出し欄（＝A14セル）にCOUNT関数を入力します。パラメータは集計する範囲である2015年の「C2:C13」を指定。入力完了したらEnterキーで確定させてみると…

↑ 現在2015年は1月と2月の「2個」を入力してるので、COUNT関数がA14セルに「2」を自動セットしてくれました（手順8で書式設定を変更したため、表示上は「2月時点小計」となってます）。小計の数値も2014年・2015年とも間違いなく計算できてますね。

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

↑ 見出しが「3月時点小計」に変わりました。「3」という数値が自動セットされ、小計も自動集計、さらに年間目標までの残り距離も自動集計されました。手入力作業が1つ削減できました。