この記事は経理をしている方・税理士向けの記事です。
資料せんをエクセルフォーマットで作る
以前の記事で資料せんの作成手順について、説明させて頂きました。もし、ご覧頂いていないようであれば、下記のサイトもあわせてご確認ください。
今回は弥生会計などの会計ソフトから、エクセルデータを抽出して、データを加工するときに便利な関数を紹介したいと思います。
資料せんのデータを作るときに、
「あれっ？こんなことをしたいけど、どんな関数使うんだっけ？」
というときに、その都度、関数を調べなくても済むように、
「この記事で紹介する関数さえあれば、できちゃうよ！」
となってもらうことが目標です。
なお、上記のサイトにも記載していますが、特に取引量が多い場合、必要となるデータをあらかじめ絞り込んでエクセルデータを抽出したほうが作業が楽になります。絞り込んでから、元帳データもしくは仕訳データを抽出しましょう。
エクセルフォーマットで作るときに便利な関数
大阪国税局の資料せんで入力が必須となっている項目は下記のとおりです。
・取引区分
・取引先の住所又は所在地
・取引先の氏名又は名称
・取引等の年月日
・取引等の金額
・決済取引区分
※管轄が違う場合は、管轄の国税局で入力が必須となっている項目を確認してください。
この中で、エクセルデータの加工が必要となりそうな
・取引区分
・取引先の住所又は所在地
・取引先の氏名又は名称
・取引等の年月日
について、資料する関数を紹介したいと思います。
説明の際に、該当するデータはセルのA1(複数の場合はB1,C1…)に入っているものとして説明していますので、適宜変更してください。
取引区分
基本的には該当する取引区分を半角で入力するので加工の必要がなさそうですが、売上や仕入など、取引区分が「01」や「02」となっている場合です。この場合、前の0の文字が自動的に削除されてしまいます。
対処方法として、セルの書式で「標準」から「文字列」へ設定を変えることによって、前の0の文字が自動的に削除されることはなくなります。
もし、関数で対応したい場合は、
を利用しましょう。この関数はフォーマットを指定する関数です。
,(カンマ)の後ろは表示形式を指定するもので、数値が2桁で表示されます。
住所又は所在地
住所又は所在地の文字数は60文字以内の制約があります。
文字数が60文字以内かどうかを確認する方法として、60文字を超えた場合、×と判定するように、60文字以内かどうか判断するためのセルを用意し、そこに表示するようにしたいと思います。
まず、
を利用しましょう。この関数は文字数をカウントしてくれる関数です。
上記と似ている関数として「=LENB(A1)」がありますが、こちらは利用しないでください。文字が全角か半角かでカウントされる文字数が変わります。住所又は所在地は全角か半角か判断する必要がありませんので、「=LEN(A1)」を使用します。
次に、
を利用しましょう。この関数は条件が一致すると、ひとつめの,(カンマ)の後ろ、一致していなかったら、ふたつめの,(カンマ)の後ろの値を表示します。
このふたつを組み合わせて
とすると、60文字を超えた場合「×」が、60文字以内の場合「〇」が表示されます。
取引先の氏名又は名称
取引先の氏名又は名称は30文字以内の制約がありますが、この確認方法は住所又は所在地と同じですので、「60」を「30」に変更するだけです。
取引先の氏名又は名称は、30文字以内の制約だけではなく、全角にする必要があります。
そこで、
を利用しましょう。この関数は文字を全角にする関数です。
注意点は、特殊文字が使用されている場合、文字化けが発生します。その文字は「?」と表示されますので、最後の作業が終わった後に修正をしてください。(この理由は「最後に」で説明します。)
取引等の年月日
エクセルデータの加工の中で、一番めんどくさい加工が取引等の年月日です。会計ソフトからデータを抽出すると、ソフトによって、年と月と日とが別々のセルに抽出されるものや、年月日がまとめて一つのセルに抽出されるものがあります。
年月日の加工については、記載しようと考えていたのですが、ボリュームが大きくなりすぎるので、またの機会に別の記事として紹介したいと思います。記載したらリンクを貼らせて頂こうと思います。
2019/7/24追記
年月日の加工について、下記の記事に記載しましたので、参考にしてください。
最後に
すべてのデータの加工が終わったら、データをコピーし、資料せんのフォーマットに値張り付けしてください。値張り付けをしないと数式のままデータが入力されてしまいます。
値張り付けが終わったら、取引先名の氏名又は名称で「？」と文字化けしているところがないか再度確認して、適宜修正して完了となります。お疲れ様でした。