PC関連

会計ソフトで元帳の残高がマイナス!エクセルで簡単に原因を調べる方法

未払費用等の残高がマイナスになっていたり、なるはずの数字じゃない残高の場合、以下のような方法で調べるでしょう。

  • 仕訳件数が少ない場合:元帳を印刷して貸借の金額をチェックする。
  • 仕訳件数が多い場合:元帳のデータをエクスポートしてエクセルで調べる。

振替仕訳が多かったり、摘要の内容が間違っていたりすると、調べるのは大変です。

あらゆる方法で残高チェックをしていて、たどり着いた方法を紹介します。未払費用の他、預り金、仮受金、仮払金、売掛金、買掛金等の科目も同じ方法でチェックできます。

対象:規模の大きくない会社や個人で会計ソフトを使用していて、消込機能がない

 

残高がおかしい元帳の例

支払手数料の例です。

日付 借方 借方金額 貸方 貸方金額 摘要
04/01 支払手数料 3,000 未払費用 3,000 4月分
04/30 未払費用 3,000 普通預金 3,000 3月分
05/30 未払費用 3,000 普通預金 3,000 4月分
06/01 支払手数料 3,000 未払費用 3,000 5月分
06/30 未払費用 3,000 普通預金 3,000 5月分
07/30 未払費用 3,000 普通預金 3,000 6月分

わかりやすくするため、補助科目なしです。

支払手数料・未払費用の元帳の例です。

 

日付 相手勘定科目 摘要 借方 貸方 残高
  前月繰越       3,000
04/01 支払手数料 4月分   3,000 6,000
04/30 普通預金 3月分 3,000   3,000
05/30 普通預金 4月分 3,000   0
06/01 支払手数料 5月分   3,000 3,000
06/30 普通預金 5月分 3,000   0
07/30 普通預金 6月分 3,000   -3,000

残高がマイナスでどこか間違っている例です。

あらゆる科目でこのような「残高がマイナス」になっていた職場がありました。仕事のほとんどが合わない仕訳の原因探しと修正仕訳入力でした。

売掛金や買掛金のチェックすら誰もやっていない、ってことは小さな会社だと驚くようなことでもありません。それすら管理してなければ、未払費用、前払費用、預り金等、あらゆる科目が合わな過ぎて悲惨な状態だったりします。

単純な仕訳で仕訳データも少なければ元帳を印刷してチェック可能ですが、仕訳データが増えたら厳しいです。1回のチェックのみではなく、仕訳が増えたタイミングや月1でチェック等する場合、毎回元帳印刷なんてやってられません。

元帳データをエクスポートしてチェックする方法でも、次回チェックするときやりにくくなります。簡単にチェックできないかなといろいろ考えて、最終的にたどり着いた方法です。仕訳の追加があっても管理できます。

 

エクセルの残高チェック管理表・未払費用の例

残高チェックをエクセルのテーブルとピボットテーブルで管理します。単純な方法だけど、チェックが楽になります。

会計ソフト(システム)から元帳データをダウンロードする

(1)元帳のCSVデータをダウンロードして、エクセルで元帳データを開きます。

(2)前月繰越の金額を貸方または貸方にコピーして、残高項目は削除します。

以下は必要な項目のみのデータ例です。

経理・エクセルで消込管理・元帳

(3)項目を3つ追加します。

  • 確認番号
  • 消込
  • 摘要2

項目名は自分がわかりやすい名前に変更してOKです。

(4)表をテーブルに変換します。

経理・エクセルで消込管理・元帳

借方または貸方に連番を振ります。ここでは貸方に連番を振ります。

参考

(5)フィルター設定をし、項目「貸方」の空白セルのチェックを外します。

経理・エクセルで消込管理・元帳

(6)項目「確認番号」の一番最初のセルに「1」と入力し、確認番号のセル全てを選択します。

経理・エクセルで消込管理・元帳

(7)Altキーを押しながら「 ; 」を押します。(Alt + ;)

→ わかりにくいですが、表示が変わります。

経理・エクセルで消込管理・元帳

(8)データをすべて表示します。(フィルター解除)

経理・エクセルで消込管理・元帳

(9)リボン [ ホーム ] > [ 編集 ] グループ > [ フィル ] >[ 連続データの作成 ]を選択します。

経理・エクセルで消込管理・元帳

(10)範囲:列、酒類:加算、増分値:1に設定し、「OK」をクリックします。

経理・エクセルで消込管理・元帳

→ 貸方に値があるセルのみ連番が振られます。

経理・エクセルで消込管理・元帳

 

費用計上した分の支払仕訳をチェックする

費用計上に対する支払った仕訳を探し、確認番号と消込と摘要2に入力します。

  • 集計で貸借を比較するため、入力する内容は貸借同じです。
  • 元帳の適用は借方と貸方のテキスト内容が違う場合があるため、摘要2を作っています。

経理・エクセルで消込管理・元帳

→ すべてチェック後の例。黄色セルは本当は6月分だったけれど、「5月分」と間違えている例です。元帳データを確認していてよくある間違い。

経理・エクセルで消込管理・元帳

ここでの例は、1対1で完結していますが、実際は仕訳が複数で1つの支払だったりします。その場合は振った連番を変更します。

貸借の金額が合っているかチェックする

(1)データ範囲内を選択し、ピボットテーブルの設定をします。

  • 行:確認番号、消込、摘要2
  • 値:借方、貸方(合計)

経理・エクセルで消込管理・元帳

小計を表示しない

経理・エクセルで消込管理・元帳

レポートのレイアウト:表形式で表示

経理・エクセルで消込管理・元帳

スタイルは好みで変更してOK。

経理・エクセルで消込管理・元帳

(2)借方と貸方の差額項目を追加します。

ピボットテーブルのデータを選択し、リボン [ ピボットテーブル分析 ] > [ 計算方法 ] グループ > [ フィールド/アイテム/セット ] を選択します。

経理・エクセルで消込管理・元帳

集計フィールドを選択します。

経理・エクセルで消込管理・元帳

次の設定をします。

  • 名前:差額(好きな名前でOK)
  • 数式:借方ー貸方

経理・エクセルで消込管理・元帳

→ 差額項目が追加されます。

経理・エクセルで消込管理・元帳

元帳データでチェックした金額の差額が「0」か確認できます。

仕訳データを追加する場合

  1. 元帳データの伝票番号を並べ替えします。(昇順)
  2. 会計ソフトより元帳データをエクスポートし、伝票番号を昇順で並べ替えます。
  3. エクセルに「2」のデータを追加分のみ値のみ貼り付けます。

実際はもっとたくさんのデータがあるし、相手科目の仕訳は複数に別れていたりして複雑だったりします。そのような場合はここで説明した方法でやると簡単にチェックができます。

簡単とはいえ・・・・全ての科目をエクセルで管理し続けるのは大変すぎます。

 

-PC関連
-

© 2020 バイクOBA3