きむおばプロフィール画像

きむおばです。関東在住の40代の主婦です。 契約社員・派遣・フリーランスの3足のわらじ中。フリーランスでは、ファイナンシャルプランナーとしてライターやセミナー講師をしたり、サイト制作やプログラムを作ったりしています。
お金・子育て・趣味のこと…何でも気ままに書く雑記ブログです。
目標は、ライター・サイト制作・広告他の収入バランスを整えること。ブログ収入UPに今年も力を入れます。

Menu
主婦の働き方・稼ぎ方 

Excelで行を挿入・削除してもすぐに復活できる計算式ならOFFSET関数を使おう

Excelの活用でもっと事務処理を時短化

Excelで作る現金出納帳に差引残高の列が必要な場合、1行上のセルの数値に入金額を足し、出金額をマイナスするような数式を設定していると思います。

後から行を挿入して入力データを追加したい時に、単なる数式だと、オートフィルで最終行まで数式を更新しなければいけなくなってしまいます。

そのひと手間を省略したいなら、OFFSET関数が便利ですよ。

OFFSET関数を使えば行を挿入・削除しても常に正しいセルを参照してくれる

Excel 現金預金出納帳 ナンバリング方法
この記事は、前回のナンバリングの裏技記事の続きです。

Excelで行を挿入・削除してもすぐに復活できる連番の方法は=ROW()


ROW関数の活用で、自動付番されるシートになりましたが、このままの数式(=G7+E7-F7)では行を挿入した時に差引残高が正しく計算されません。参照する行がズレてしまうからです。
そのため、オートフィルで数式を再設定する必要が発生してしまいます。

そのひと手間を省きたいなら、数式にOFFSET関数も使ってみましょう。

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))

EXCEL OFFSET関数で差引残高が常に正しく計算される方法

これで、行を挿入したり削除しても、ナンバリングや差引残高が常に正しく表示される現金出納帳が完成しました。

Excelの活用でもっと事務処理を時短化
Twitterで最新情報をお知らせ
記事がお役に立てたらシェアお願いします♪