Excelで行を挿入・削除してもすぐに復活できる計算式ならOFFSET関数を使おう
このブログではアフィリエイト・アドセンス広告を利用しています
当ブログでは、アドセンス・アフィリエイト広告を掲載しています。
消費者庁が発表しているルールに沿って記事を作成していますが、問題のある表現を見つけた際にはご連絡ください。
Excelで作る現金出納帳に差引残高の列が必要な場合、1行上のセルの数値に入金額を足し、出金額をマイナスするような数式を設定していると思います。
後から行を挿入して入力データを追加したい時に、単なる数式だと、オートフィルで最終行まで数式を更新しなければいけなくなってしまいます。
そのひと手間を省略したいなら、OFFSET関数が便利ですよ。
目次（読みたいところへジャンプ！）
OFFSET関数を使えば行を挿入・削除しても常に正しいセルを参照してくれる
この記事は、前回のナンバリングの裏技記事の続きです。
ROW関数の活用で、自動付番されるシートになりましたが、このままの数式（=G7+E7-F7）では行を挿入した時に差引残高が正しく計算されません。参照する行がズレてしまうからです。
そのため、オートフィルで数式を再設定する必要が発生してしまいます。
そのひと手間を省きたいなら、数式にOFFSET関数も使ってみましょう。
OFFSET関数とは、元となるセル位置から指定した行数と列数分移動したセルの値を取得できる関数です。
元となる基準セルから、どれだけ行と列を動かすのかを引数で設定できます。1行上ならマイナス1、1行下なら1と設定していきます。
OFFSET関数は、範囲指定も可能なため、行の高さと列の幅まで指定できますが、必要ない時は省略することもできます。指定範囲の合計値や平均値などを算出したい時には便利な機能です。
今回作成したい現金出納帳の差引残高を算出するには、1行上の差引残高に2列左の入金額を足してから1列左の出金額をマイナスすればいいわけですよね。
それをExcelの数式で表すと、次のようになります。
=OFFSET(G7,-1,0)+OFFSET(G7,0,-2)-OFFSET(G7,0,-1)
現在地から1行上、2列左、2列左と参照したいセルの位置が変わらないため、OFFSET関数を使う方が行を挿入したり削除しても崩れにくくなるのです。
IF関数でブランク処理をしてあげると、日付が入力されていない行は空白表示になります。
=IF(B7="","",OFFSET(G7,-1,0)+OFFSET(G7,0,-2)-OFFSET(G7,0,-1))
これで、行を挿入したり削除しても、ナンバリングや差引残高が常に正しく表示される現金出納帳が完成しました。
