弥生会計インポート用CSVをエクセル関数で作る(1)

前回までの記事で、
インポートファイルのフォーマットもわかったので、
弥生会計インポート用CSVファイルの元になる
シートを作成していきます。

CSVファイルをエクセル関数で作る

例として、次のような、
みずほ銀行の入出金を記録している「預金出納帳」から
インポート用シートを作っていきます。

預金出納帳の例

インポートデータの借方・貸方に記帳する科目等の決定

最初に、仕訳データの中で一番重要な
「借方・貸方の勘定科目」をいれる計算式を考えます。

※借方、貸方がよくわからない、という方は、
「借方=左側」、「貸方=右側」と読み替えてください。

インポート用シートで言うと、
E列「借方の勘定科目」、K列「貸方の勘定科目」
に該当します。

まずは、この列をどのように入れたらいいか、
考えていこうと思います。

上の例で考えると、
結果的には、次のような感じになります。
インポート用シートに勘定科目を入れるとこうなる
※色は、説明のためにつけています。
 実際のインポート用データ作成時には、色をつける必要はありません。

ここでのポイントは、
入金の場合と、出金の場合で、
借方・貸方に来るべき勘定科目が入れ替わってしまう、ということ。

具体的には、次のような感じになります。

借方勘定科目(E列)

入金の場合:普通預金
出金の場合:仕入高、給料手当、旅費交通費(=出納帳に入力した科目)

貸方勘定科目(K列)

入金の場合:売上高、預り金(=出納帳に入力した科目)
出金の場合:普通預金

を入れてやる必要があります。

エクセルで場合わけをするときは「if関数」を使う

上で書いたことをエクセルの「セル」を使う表現にすると、

  • 「入金欄に金額が入っていれば」H14セル
  • 「入金欄に金額が入っていなければ(※)」D18セル

を転記する必要がある、ということになります。

※入金欄・出金欄の両方に金額が入る、ということは、絶対にあり得ないので、
 入金欄に金額が入っていなければ、出金扱いということで問題ないです。

このような、
「・・・の場合はXX、そうじゃない場合にはYY」という表現が出てきたときに、
よく使うのが「if関数」です。

if関数を使うと、上の操作は、

=IF(G18>0, H14, D18)

と書くことができます。

同様に、
「貸方勘定科目」欄は、

  • 「入金欄に金額が入っていれば」D18セル
  • 「入金欄に金額が入っていなければ」H14セル

を転記する必要があります。

if関数を使うと、上の計算式は、

=IF(G18>0, D18, H14)

と書くことができます。

先ほどの「借方」の計算式と比較すると、「D18」「H14」が入れ替わっているだけ、
というのがわかると思います。

式をコピーしやすいように絶対参照にする

この計算式は、仕訳が入っている行数分だけ、
下にコピーしていきたいのですが、

上で書いた2つの計算式。
そのまま下にコピーすると、うまく動きません。

なぜかというと、
上で書いた計算式では、
G18セル、D18セル、H14セルの3つが出てくるわけですが、

H14セルだけは、
式をコピーしても「ずれないで」ほしいからです。

G18セル

下にコピーしたら連動して下にずれてほしい→行は相対参照

D18セル

下にコピーしたら連動して下にずれてほしい→行は相対参照

H14セル

下にコピーしても常にH14セルを参照していてほしい→行は絶対参照

ですから、H14セルに絶対参照を適用して、

E1セル: =IF(G18>0, H$14, D18)
K1セル: =IF(G18>0, D18, H$14)

というような式を入れることになります。

他のシートのセルなので「預金1!」をつける

今回は、
インポート用のシート「import用」を、
出納帳のシートとは「別に」作成します。

上の計算式は、
「預金1」シートではない「import用」というシートに入れることになります。

そのような場合に、
「預金1」シートのセルを参照したい、という場合には、

単にセル名を入れるのではダメで、
セル名の前に「預金1!」と入れることになっています。

※マウスで参照先のセルを選べば、自動で入力されるので、
 計算式を自力で入れる場合は、特別に意識する必要はありません。

結局、最終的な計算式は、
弥生インポート用勘定科目を入れるための計算式
となります。

式の部分だけ、再度、下に書いておきます。

E1セル: =IF(預金1!G18>0, 預金1!H$14,預金1! D18)
K1セル: =IF(預金1!G18>0, 預金1!D18, 預金1!H$14)

エクセル基礎講座 「無料」動画マニュアル

「経理事務のためのエクセル基礎講座(初級編)」(動画マニュアル 総収録時間162分)を無料プレゼント中です!

このマニュアルで解説していることを一通り学べば、経理事務を行う上で最低限必要となる知識が得られます。

ご登録者の方には、合わせて、公認会計士が実体験を通して身に付けたエクセルを使う技をメールにてお伝えしていきます!

無料動画講座 登録フォーム

※ご登録頂いたメールアドレスに、エクセルを使いこなすための情報を配信するメールセミナー「エクセル倍速講座」も合わせて配信させていただきます。