【Excel活用術】セルの書式設定にある「ユーザー定義」や「絶対参照」を使いこなせば、ワンランク上の計算式ができる【第56回】

Excelには地味だけど便利な機能がたくさんあります。例えばセルの書式設定にある「ユーザー定義」や、式を作るときに便利な「絶対参照」など。
今回は、前回の記事(【Excel活用術】3か月後は何日？特定の日付を簡単に計算できる「EDATE関数」が便利【第55回】)で紹介した「EDATE関数」を、ユーザー定義や絶対参照の機能を使ってさらに便利にする方法をご紹介します。　

具体的にどのように便利になるか、下記の図（前回の記事で作成したもの）を参考に説明しましょう。

C2とD2には、B2に入力された日付から「3ヶ月後」、「10ヶ月後」の日付を計算する式がEDTATE関数を使って入力されています。
でも、例えばここで「10ヶ月後じゃなくて5ヶ月後の日付を知りたい」ことになったらどうでしょう。
まず、D1の見出しを5ヶ月後に変更、その後D2の計算式の「10」を「5」に修正．．．．などと同じ数字を2回も打ち直すことになります。でも面倒ですよね。
そんな時に、D1を「5」と打ち替えるだけで再計算してくれるようになるのが「ユーザー定義」書式です。

「ユーザー定義」書式とは、デフォルトの書式にはなく、ユーザーが独自に作成した表示形式のことをいいます。
様々な書式(種類)を設定することが可能で、その中でぜひ覚えて欲しいのが、数字を入力すると、数字に文字をつけて表示してくれる書式です。
普通は、計算式で使っているセルに文字が入っていれば、エラーとなります。(下記図のように、文字列であるC1を計算式に盛り込もうとするとエラーになる)

でも下の図のC1のように、表示は「3ヶ月後」だけれど、実際に入力されているのは数値の「3」という書式設定をしておけば、画面表示もわかりやすく、きちんと計算もでき、データの変更もかんたんという、最高の状態が実現します。
具体的なやり方をこれからご説明します。　　

さっそくやってみましょう

準備

まずシートに簡単なデータを入力します。B2には日付、C1とD1には月数を表す数値を入力します。サンプルでは、「3」「10」を入力しました。

次にC1とD1を選択した状態で、「セルの書式設定」ダイアログボックスの「表示形式」タブを表示します。
「セルの書式設定」ダイアログボックスの場所は、「ホーム」タブの「数値」グループの右端にある斜め下向き矢印マークの「表示形式」ボタンをクリックすると出てきます。

「表示形式」タブが表示されたら左側にある「分類」項目から「ユーザー定義」をクリックし、種類項目で「0」をクリックします。

種類欄に｢0｣と表示されるので、0に続けて「ヶ月後」と入力します。

ダイアログボックスでOKをクリックすると、C1、D1の表示が「3ヶ月後」「10か月後」に変わります。

数式バーをみると、C1は数字の3だけが確認できます。これが「書式を設定したので3ヶ月後と表示されているけれど、入力されている値は3という数字」というしくみです。

おまけ

設定した書式は「ユーザー定義」の一番下に追加されます。

計算式を入力しよう

最後に計算式をC2に入力します。EDATE関数を使って関数式「=EDATE(B2,C1)」を入力します。

既に入力されている数式「=EDATE(B2,3)」を修正する場合は、セルをアクティブにした状態で、fx（関数の挿入）ボタンを押します。
「関数の引数」ダイアログボックスが表示されたら、「月」の値をC1に変更します。

これで完成です。
C1の値を変更すると、即座にC2の値が再計算されます。

さらにステップアップした式にする方法

ところで、C2に作った式をD2にそのままコピーすると、計算式が指し示すセルの場所がずれてしまい、正しい計算結果が得られません。

そんなときには「絶対参照」というテクニックを使って式をステップアップさせます。
絶対参照の詳しい説明はまた回を改めることにして、今回はカンタンに式をステップアップさせる手順を説明します。
数式の入っているC2をアクティブにした状態で、fx(関数の挿入)ボタンを押します。
「関数の引数」ダイアログボックスが表示されたら、「開始日」欄に入力されているセル番地B2にカーソルを合わせ、キーボードのF4キーを１回押します。
番地の表示がB2から$B$2に変わったことを確認して、OKボタンを押します。

もしB$1や$B1などに表示が変わってしまったら、何度かF4キーを押していると$B$2になります。
実際には、B2→$B$2→B$2→$B2→B2の順番でセル表示が変わります。
最後に式をD2にコピーして完成です。

今回のような計算では、式をコピーしたときにスタート日を指定するセル番地（B2）が横にずれてしまい、正しく計算されません。
式をコピーしてもスタート日は「絶対にB2です!!　ズレません!!!」と言い切るときにF4キーを使います。F4キーで表示される「$」マークは、アンカー（船の錨）の意味。「＆」のほうがわかりやすいのですが、Excelでは「&」は別の機能に使われてしまっているので、「$」マークをアンカーに見立てて「絶対ココ、動かない」を表現しています。
「＆」のつかいかたについてはこちらの記事（【Excel活用術】複数のセルに入っているデータを１つに繋げたい！【第7回】）を参考にしてください。

「ユーザー定義」書式や「絶対参照」は聞き慣れない用語で敬遠しがち。でも使ってみれば意外に使いみちが多いすぐれものです。
何回か練習してぜひ普段遣いのツールに加えていただけると嬉しいです。