通帳を入力せざるを得ないときのExcelへの入力効率化

経理をするには、預金のデータが必要です。
預金のデータがなく、やむを得ず通帳を入力せざるを得ないとき、Excelでどう効率化するかについてまとめてみました。

 

 

通帳を入力しないためにはネットバンクが必要

通帳（紙）をデータにするためには、入力しなければいけません。
その入力をしないようにするためにはネットバンクが必要です。

ネット上で銀行の取引データを見ることができ、振込もできるネットバンク。
ネットでデータを見ることができるので、そのデータを使うことができます。
預金のデータを入力しなくてよくなるわけです。

経理効率化の原則は「入力をなくす」こと。
ネットバンクを契約するだけで入力がなくなるのであれば、たとえ手数料がかかっても問題ないという考え方もできます。
ただ、そうはいっても無駄にお金を払いたくないもの。
ネットバンクでも手数料がかからないところを使いましょう。
メガバンクは見た目はよいのですが、ネットバンクで法人の場合はそれなりの手数料を取られます。
見た目をとるか手数料を取るかです。

私は、法人ではゆうちょ銀行と住信 SBIネット銀行を使っています。
どちらも手数料は無料です。

また、ネットバンクでデータといっても気をつけなければいけないことがあります。
それはデータなのに過去のものを見ることができないことです。
メガバンクは前月の1日からしか見れません。
その他の銀行でも2ヶ月以内だったり3ヶ月以内だったりすることがあります。
ネット専業銀行であれば5年、7年と履歴を見ることができますので、少なくとも1年は履歴が保存できる銀行を選んでおきましょう。
手数料は取られるわ履歴が残らないわという銀行は選んではいけません。

ネットバンクを契約していてもこの履歴の制限があるため、データを見れると思っていたらもう見ることができず（有料で明細を取り寄せることができますが、だいたい紙です）、結局は通帳を入力しなければいけない場合もあります。

 

通帳を入力せずにすむ方法

入力せずにすむ方法は、ネットバンクを使って次の2つのものがあります。

会計ソフトに取り込む

会計ソフトにネットバンクのログインID・パスワードを登録しておけば、データを取り込むことができます。
そんなことして大丈夫かと思われるかもしれませんが、万が一その銀行の口座残高明細が漏れることが考えられますが、不正振込については、もう1つの振込パスワードを入れなければ被害にあうことはありません。
あとは、セキュリティをとるか利便性をとるかどうかの問題です。
通帳で利用していても、通帳はともかくカードをスキミングされて引き出されるということはあり得ますので、完璧な安全はありません。

会計ソフトに取り込むというのはどういうことか。
たとえば 会計ソフトfreeeとネットバンクを連動させると次のように表示されます。
残高、明細が連動できるのです。

明細を見てみると、このように2月28日に入金があります。
通帳を入力するなら、日付を入れて内容を入れて金額を入れてという手間がかかりますが、会計ソフトに連動しておくとこのように、日付、内容、金額は自動的に取り込まれるのです。

どういった勘定科目で処理をするかということを入力し、必要であれば情報を追加すればそれで経理処理が終わります。
通帳を入力するときはその金額を間違えないか、残高が合っているかといったところに神経を使うものですが、連動しておけば、日付、内容（ネットバンクに記録されたもの）、金額は、少なくとも間違いようがありません。

私の場合、この取引は出版売掛金（出版に関する入金）と処理します。

さらにはこのパターンを登録することができ、次に同じ内容で振り込みがあったら同じように自動的に処理して登録してくれます。
クラウド会計ソフトのマネーフォワードだと毎回確認しなければいけませんが、freeeは自動登録してくれるできるので私好みです。

このように住信SBIネット銀行に「振込※カ）ダイヤモンドシャ」という入金があったときは、出版売掛金として登録するというルールをつくります。
このルールを細かくつくるのが会計ソフトでの効率化の要です。
その都度なり事前に登録するのは面倒くさいのですが、それで楽になるのでやる意味はあるでしょう。

なお、ネットバンクを契約していて会計ソフトと連動していたとしても、その口座へのアクセスするだけでもワンタイムパスワードが必要な場合があります。
ワンタイムパスワードを月に1回程度に入れておかないとネットバンクのデータが連動されないのです。
この過剰な仕様＋履歴が取れないという連続技でやられると、ネットバンクで契約していてお金を払っていてクラウド会計ソフトにもお金を払っていて連動していても、データが取れないという結末になってしまいます。

 

Excelで加工する

ネットバンクに連動できる会計ソフトを使っていない場合、連動したくない場合は、Excelで加工して会計ソフトに取り込むという方法があります。
従来は、この方法しかありませんでした。
今でもこの方法のほうが早い場合があります。
クラウド会計ソフトを開いていちいち登録してというよりもExcelで行ったほうが早い場合もあり、私はこちらでやることも多いです。
また、新規のお客様で、これまで連動していない場合はネットバンクのデータを使うしかありません。
（過去にさかのぼれる限りですが）

ネットバンクのデータを CSV 形式でダウンロードしてExcelに貼り付けます。
ネットバンクにないデータとして勘定科目がありますので（場合によっては消費税課税区分）それを入力しなければいけません
この場合、VLOOKUP関数を使うと楽です。

「振込＊カ）ダイヤモンドシャ」だったら出版売掛金にするというパターンをつくっておき、それをVLOOKUP関数で読み取ります
会計ソフトの科目推測も基本的にはこの考え方です。
機械学習とか入ってるらしいですが。

その勘定科目を使って会計ソフトへのインポート形式に数式で連動させます。
会計ソフトに取り込む場合はこの一部分で、画像のデータでいうと H 列から F 列までをコピーして新しいファイルに貼り付けて CSV データとして保存してから取り込みましょう。

ExcelファイルをCSVで保存するときに、マクロを使う理由 | EX-IT

 

CSV でダウンロードできない場合、 CSV でダウンロードできるネットバンクでも手軽に1画面だけですむ場合のであればブラウザ上で選択してコピーして

Excelに貼り付ければ使えます。

Excelに貼り付けた後に、貼り付けのオプションが右下に表示されますので 右側のアイコン［貼り付け先の書式に合わせる］をクリックすればきれいにデータが整います。
もしうまくいかない場合はブラウザでコピーする範囲をちょっと多めにするとうまくいくこともあるので試してみましょう。

パソコンの中にありさえすれば、いかようにもなるわけです。

通帳を入力せざるを得ないときのExcelテクニック

そして、どうしても入力をせざるを得ないときは、Excelに入力してそれをインポートするようにしています。
会計ソフトに直接入力したほうが早いという方はそのほうがいいでしょう。
私は会計ソフトの操作性が好きではなくデザインも好きではないので Excel で入力したほうが気分的にも早く入力できます。
同じように考える方は、Excelでの入力→インポートも試してみましょう。

次のような通帳を入力することを考えます。
（私は通帳を持ってないので引き続き妻の古い通帳を借りました）。

 

見出し、入力規則の設定

まっさらのExcelシートの1行目に次のような項目を入れ、残高を入れます。
日付を年月日に分けるのは後々入力しやすくするためです。

さらには数字が入る列を選択（  A から C 列を選択してCtrlキーを押しながらEからG列を選択すると、画像のように選択できます）して

リボンの［データ】タブ→［データの入力規則］で日本語入力をオフにしましょう。
こうするとこの列には半角英数字しか入らなくなります。
入力モードを切り替えなくても入力できるようになるので楽です。

さらに  Control + Shift +1で金額が入るEから G 列を桁区切りにしておきましょう 。

同様にして日本語を入力する摘要の列にデータの入力規則の日本語入力をオンにしておきます。

残高を計算する数式を入力し、テーブルにする

次に残高を計算する数式を入れます 。
こういった数式です。

ここで Ctrl＋ Tを押してデータをテーブルにしておきましょう。
Ctrl＋Tの後、次のような表示になっていることを確認してEnterキーを押してみてください 。

こいたテーブルという機能を使えるようになり数式を自動的に入れてくれたり色を変えたりすることができます。
テーブルについてはこちらの記事を参考にしていただければ。

簡単！きれい！ミスが減る！ 何かと便利なExcelの［テーブル］ | EX-IT

数字の入力

私は最も気を使う金額欄を先に入力して残高を合わせてから、他のところを入力していきます。
会計ソフトと違いExcelは金額だけを先に縦に打つことができますので、このような使い方ができるわけです。

金額をまず打っておいて48,251という残高が通帳とあってるかどうかを確認します。
通帳会計ソフトに入れてると、残高合ってるかなと見て違っていて、それを探さなきゃいけなかったりすることがあり、それが私はストレスなので昔からこうやってはいました。
今はほとんどやることがありませんが。

次は日付です。
上と同じところを入れなくても後で数式で入れることができますので数字が変わるところだけを入れていきます 。

数式の入れ方については後述します。

摘要（内容）の入力

摘要（内容）を次に入れていきます。
このとき最も多いものは空欄にしていることが多いです。
毎回入力するのも手間ですので。
この場合は「カード」が結構多いので「カード」のところは打たないで、空欄にしておきます。
（5月6日の欄は人の名前が入っているので仮名として私の名前を入れておきます）。

空欄部分は、列を選択してCtrl＋H（置換）で、「検索する文字列」を空欄に、「置換後の文字列」を「カード」として、［すべて置換］にすれば

空白のセルに同じもの（この場合「カード」）を入れることができます。
これはいろんな使い方ができるのでぜひ覚えておきましょう 。
金額で同じものが多いとき（振込手数料、一定額の送金など）も使っています。

 

科目の入力

科目を入れるためにVLOOKUP関数の元となるデータを準備します。
このときに摘要欄にどんな種類があるかというリストをつくらなければいけないのですが、かんたんにつくるのであればピボットテーブルが使えます。

このテーブルのいずれかのセルを選択して、リボンの［挿入］→［ピボットテーブル］ を選びます。。
（よりかんたんにするならクイックアクセスツールバーでやりましょう）。

別シートにピボットテーブルができ上がりますので、ここで摘要にチェックを入れます。

摘要欄の固有のデータができ上がりますので、これを使って右側の B 列に科目を入れていきます。

たとえばこんな感じです。
・DC だったらカード会社なので未払費用
・イノウエヨウイチだったら地代家賃
というパターンをつくります。
シート名は自分がわかるように変えておきましょう。
私は、codeとすることが多いです。

Ｈ列には、こういったVLOOKUP関数を入れれば、カードだったら現金、イノウエヨウイチだったら地代家賃と自動表示できます。

日付の処理

日付は、こういった処理もできます。
先ほど一番上の数字しか入れてませんので、それを数式でデータとして整える方法です 。
If 関数で年つまりセル A3が空欄だったら I 列の上1つ上のセルを表示し、そうじゃなかったらそのまま年を表示するというものです。

月や日も同様にやりましょう。
このセルで解説すると日（セルC4）は、空欄なので、K列の1つ上のセルに入っている6を表示しています。

年月日のデータがあれば、いかようにも処理できます。
この場合はDATE関数で3つをつなげると日付データになるというものです。
とにかくデータにしてしまえば何とでもやるようはあります。

あとは、会計ソフトの形式のデータを数式でつくっておき、それをデータの数だけコピーして新しいシートでCSVファイルとして保存し、取り込むだけです。

Excelにわざわざ入力して取り込まなくても最初から会計ソフトに入れたほうが効率かもしれません。
が、効率化というのは気分の問題もあるので、私はこの方法でやっています 。
会計ソフトの入力がいくら早くなってもそこでしか使えませんが、Excelで数式やテーブル、ピボットテーブル、VLOOKUP関数の使い方を覚えておけば、他のところでも使えるというのも、こういった方法を選ぶ大きな理由です。

サンプルを置いておきますので使ってみていただければ。
（弥生会計へのインポート形式です）
EX-ITサンプル　通帳入力→インポート.xlsx

ただ効率化の大前提は入力をなくすことですので入力をなくすことも進めていきましょう

 

 

 

 

---

■編集後記

アイアンマン、どうしようか迷うくらいならと、発注しました。
受注生産で、届くのは来年夏だとか。
いろいろ調べると予約して手に入れないとプレミア価格（2倍以上）でメルカリやヤフオクで買わなければいけなくなるようです。
今発売されているアイアンマン（マーク50以前）はそうなっています。
発注したのは最新のマーク85（開発が進みいろんなタイプがあるのが商売上手で）です。

 

■昨日の「1日1新」

娘と原宿へ
原宿　サンリオショップ

■昨日の娘日記

昨日は水族館（品川アクアパーク）へ。
外苑前のマーベルのショップ（トイサピエンス）に行きたかったので、こっそりそっちに向かうと、「水族館は？」「水族館行きたい」となにやら察している感じでした。
ショップに着くと、好きなアイアンマンがいたので喜んでいましたが。
その後、原宿経由で水族館へ。
途中、キティちゃんを気に入ったようだったので、買いました。

水族館も楽しんでいましたが、イルカショーは、30分前に席に着き、15分で満足したようで帰ることに……。