Excelで行ごとに残高を集計するようなシートを使っていると、行削除により式がおかしくなることはありませんでしょうか。こうした場合の対処法を確認しておきましょう。

金銭出納帳のExcelフォーマット

お客さまの中には、以前は紙の出納帳を作成している方が何人かいらっしゃいました。

こうした方には、こちらから出納帳のExcelフォーマットを提供して、紙からデータでの管理に移行してもらいました。

Excelのフォーマットを使ってもらえれば、少なくとも行ごとに残高を計算する手間は減りますし、さらに毎月同じような入出金であれば過去のデータをコピペすることで入力の手間も省くことができます。

ただこうしたフォーマットを使っていただく中で、時々こんな質問をいただくことがありました。

「行を削除したら残高が正しく計算されなくなった」

お渡ししているフォーマットはこんな感じの簡単なものですが、残高を計算する式としては、例えばD8セルだと

D7+B8-C8

としています。

要するに前の行の残高にその行の入金を足して、出金を引くという算式です。

ところが、ここで

「7月3日の入力は間違いだったので行ごと削除する」

という作業をするとどうなるでしょうか。

上図のように、前の行の参照先がなくなってしまったため、削除した後の行については「#REF」というエラーになります。

※ちなみに、以前は「#REF」エラーにならずに、例えばD8セルだと「D6+B8-C8」という式に変わってしまったと記憶していますが、仕様が変わったのかもしれません。

【2024.7.21追記】
D8セルの数式が「D6+B8-C8」となってしまう症状は、行削除ではなく、行追加した場合に発生するものでした。行追加については、今回のOFFSET関数を使う方法でも、複数行を挿入した場合などは、テーブル内でOFFSET関数を使った数式が自動的にコピーされず、完全には対応できませんのでご了承ください。

こうなってしまうと、行削除をする度に残高の式を修正しなければなりません。それほど難しくない作業とはいえ、少々面倒です。

広告

ExcelのOFFSET関数とは

この問題について、以前とある方に相談したときに教えてもらったのが、OFFSET関数を使う方法です。

Microsoftサポート：OFFSET関数

OFFSET関数というのはセルを参照するための関数ですが、セルの指定の仕方が

基準となるセルからどれだけ行と列をずらすか

という考え方になっています。

そのため

残高を計算する式を入れるセルから1行だけ上にずらしたセル

という指定をすることが可能です。

例えばD8セルからD7セルを参照するには

OFFSET(D8,-1,0)

と書きます。

関数の中の項目の意味は

  • 最初の項目：基準となるセルの場所
  • 2つ目の項目：基準となるセルから行をどれだけずらすか指定(上にずらす場合はマイナスで書き、下にずらす場合はプラスの数字を入れます)
  • 3つ目の項目：列をどれだけずらすか指定(今回は同じ列のため0とします)

です。

この関数を使って残高を計算する数式を書き換えると、D8セルであれば

=OFFSET(D8,-1,0)+B8-C8

とすればよいわけです。

この状態で先ほどと同じように、7行目を削除すると･･･

先ほどの数式とは違ってエラーにはなりません。数式も正しく残高を計算するものとなっています。

広告

使う人に配慮した仕組みを準備する

OFFSET関数の使い方については、「それくらい知ってるよ」という方も多いのではないかと思います。

行ごとの残高を計算する際の数式への工夫は、ほんのちょっとしたことです。

とはいえ、Excelフォーマットを渡して使ってもらう人に

「あれ、なんかエラーが出た」
「税理士に聞かないと」

みたいな手間はできるだけとらせたくありません。

さらにいえば、Excelに苦手意識を持っている人は意外と多いもので、そうした人たちが今回のようなエラーに遭遇してしまうと、それだけで仕事の手が止まってしまい

「やはり紙の出納帳の方がカンタンだ！」

となってしまうかもしれません。

数式の作り方を少し変えるだけで、こうした手間や心配がなくなるわけです。

受け取った側は気付かない程度の配慮ですが、これで将来のトラブルを減らせますので、仕組みを作る際にはこうした点にも気をつけておきたいものです。

投稿者

加藤 博己
加藤 博己加藤博己税理士事務所 所長
大学卒業後、大手上場企業に入社し約19年間経理業務および経営管理業務を幅広く担当。
31歳のとき英国子会社に出向。その後チェコ・日本国内での勤務を経て、38歳のときスロバキア子会社に取締役として出向。30代のうち7年間を欧州で勤務。

40歳のときに会社を退職。その後3年で税理士資格を取得。

中小企業の経営者と数多く接する中で、業務効率化の支援だけではなく、経営者を総合的にサポートするコンサルティング能力の必要性を痛感し、「コンサル型税理士」(経営支援責任者)のスキルを習得。

現在はこのスキルを活かして、売上アップ支援から個人的な悩みの相談まで、幅広く経営者のお困りごとの解決に尽力中。

さらに、商工会議所での講師やWeb媒体を中心とした執筆活動など、税理士業務以外でも幅広く活動を行っている。
広告