昨日はExcel で
イベント進行表を作る時に便利な
「Time関数」についての記事を書きました。
※昨日の記事はこちら
早速、お世話になっている社長さんが
この記事を読んでくれたようで
必要としている方に届いてよかったです。
実は昨日の記事には続きがあって、
あとからもう一つ関数を付け足しました。
今回はすぐに見つかったので
大ごとにはならなかったのですが、
金額計算などの場合は
大問題に発展する可能性もあります。
自戒も込めて
ぜひ覚えていただきたい関数を
ご紹介します。
昨日お伝えしたイベント進行表は、
すぐ上のセルの値（時間）に
左のセルの分（ふん）数を足して
経過時間を計算するというものでした。
（「分」はTime関数でシリアル値に変換）
この計算式、
一見何の問題もないように見えます。
事実、これが検定試験なら
この計算式は正解です。
でも、実務では
作っている最中に
加工される場合があります。
例えば、
ゲストスピーチの後に
親戚の子供たちによる花束贈呈が入ったとか、
祝電紹介が
ウェディングケーキ入刀の後になったとかです。
例えば、
８番のゲストスピーチの下に
行を挿入してみましょう。
まず、挿入した行に（当たり前ですが）
計算式が入っていないことに気づきます。
これはすぐわかりますね。
見落としがちなのは、
挿入した行のすぐ下の行の計算式です。
一つ上の時間と
左の分（ふん）数を足していたはずなのに、
２つ上の時間との足し算になっています。
この計算式のズレはこの行だけでなく、
これ以降すべての行の計算式でズレが発生します。
これはダメですよね？
Excel の計算式をコピーするときは
「相対参照」といって、
「一つ上のセルと足し算する」などという
セルの位置を自動で認識する機能があります。
このおかげで、
計算式のコピーがすごく楽ちんです。
ただ、相対参照が有効なのは
計算式をコピーするときだけです。
この例のように、
間に行を挿入したり、
逆に削除したり、
入れ替えたりした場合は
画像のように、
「相対参照」が効かないため、
計算がエラーになってしまいます。
このように、
あとから挿入・削除・入れ替えなどが
発生する可能性がある場合は
「Offset関数」を使います。
Offset関数を使うと
この相対参照がズレることなく、
正しい計算をしてくれます。
では、計算式を入れていきましょう。
今回の計算式は、
計算式入力セルの１つ左のセルと、
１つ上のセルを足し算します。
行挿入や行入替をする可能性はありますが、
同じ行であれば影響は受けません。
なので、
Offset関数にしないといけないのは、
「１つ上のセル」という部分です。
Offset関数の引数は
（参照, 行数, 列数）です。
参照は、
基本的に計算式を入力するセル番地
行数は、
参照のセル番地の行番号を「０」として、
１つ上なら「−１」、
１つ下の行なら「１」という具合に指定します。
列数は、
参照のセル番地の列番号を「０」として、
一つ右なら「１」、
左なら「−１」と指定します。
本当はこの３つ以外に
「高さ, 幅」という引数も指定できるのですが、
これは特殊な場合のみ指定するものなので、
今回の説明では省略します。
この例の場合だと、
参照は計算式を入力するセル番地
行数は１つ上なので「−１」
列数は同じ列なので「０」を指定します。
Offset関数を組み込んだ計算式を入力したら
下までコピーします。
計算式を修正したあと
この表のどこかに行を挿入すると
一時的に計算がおかしくなりますが
挿入行に計算式をコピペすると、
正しい値に再計算されます。
削除しても大丈夫。
入れ替えても計算がおかしくなることはありません。
あ、今回は説明用なので
プログラムナンバー（左端）は
削除・入替がわかるようにしてますが、
ここもOffset関数で
「一つ上のセルに１を足す」
にすれば、番号を振り直さなくてもOKです。
いかがでしょうか。
計算式をコピーするときは
「相対参照」といって、
自動的にセルの位置を考えて
コピーしてくれます。
でも、コピーした後の作業には
適用されません。
そのため、
計算ミスに気付きにくく、
この間違いに気づくのに
ものすごく時間がかかったというのも
よく聞く話です。
今回のように、
「一つ上のセルと足し算する」
というのは、
家計簿などの金額計算でよく使われます。
行を挿入したり
削除したり
入れ替えたりして、
金額が合わなくなったら大変ですよね。
行全体の挿入・削除・入替の
可能性がある場合は
「Offset関数」と覚えておいてくださいね。
Excel でのお困りごとはないですか？
タイムカードみたいに、
勤務時間を計算できないかな？
そこに時給を入力したら、
給料明細ができないかな？
など、いろんなことができますよ。
ご相談くださいね。
