1つ星2つ星3つ星4つ星5つ星 (4.50 評価, 2 投票)
 

目的 記帳データを日本語フォーマットに変換する
作業の流れ
  1. 記帳担当者から元データをもらって貼り付ける
  2. 日本式財務レポートのフォーマットに変換する数式をつくる
使用するブックとシート
  • ブック「記帳データ.xls」、シート「bookdata」、「jp-format」

記帳データの貼付

新規にExcelファイル(ブックともいいます)を作成し、「記帳データ.xls」と名前をつけます。

記帳が完了したことを確認し、財務レポートを作成する元となるデータを記帳担当者からもらい、記帳データ.xlsのシートに「値の貼り付け」をします。シートには「bookdata」と名前をつけておきましょう。

今回の例は中国の会計ソフト「金蝶」からエクスポートした「科目余額表」(日本でいう残高試算表)のデータを使用しています。会計データとして一番操作しやすいのは残高試算表で、集計前の仕訳データは明細を確認するときに使います。これ以外にも科目番号と金額がセットになっているデータなら国・地域を問わず何でも使えますので、適当に読み替えてみてください。

図1:科目余額表の例

excel-report-1-1
次にデータを数式で扱いやすくするため、データの範囲に名前をつけておきます。「数式」タブの「名前の管理」から「新規作成」ボタンを押し、名前を「bookdata」、参照範囲をシート「bookdata」のB列からL列に指定します。列だけを指定したのは、翌月以降に行が増えても同じ範囲内で扱えるようにするためです。

図2:データ範囲に名前をつける

excel-report-1-2

日本式フォーマットへの変換

中国の会計ソフトから出てくるフォーマットと日本の損益計算書のフォーマットでは、科目の順番に若干の違いがあります。よってこの両者をつなぐための変換作業が必要です。

新しいシート「jp-format」を追加しましょう。このシートに、日本式の損益計算書のフォーマットと、それに対応する元データの科目番号を記入します。科目番号に金額を対応させることで、日本式フォーマットへの変換を行います。科目番号のない項目には、後で数式を入れる予定です。

表1:日本式フォーマットの例
日本式フォーマット 対応する中国の科目(新準則)
Ⅰ.売上高 6001 主营业务收入
Ⅱ.売上原価 6401 主营业务成本
売上総利益
Ⅲ.販管費 6602 管理费用
消耗品費 660201 办公费
交通費 660202 差旅费
通信費 660203 电话费
支払家賃 660204 房租及管理费
社会保険料 660205 社保费
給料 660206 工资
租税公課 660207 邮寄费
交際費 660208 业务招待费
減価償却費 660209 折旧费
福利厚生費 660210 职工福利费
長期前払費用償却 660211 装修费摊销
その他費用 660212 其它
税金及び付加 6403 营业税金及附加
営業利益
Ⅳ.営業外収益
受取利息 660302 利息收入
為替差益 660303 汇兑损益
その他
Ⅴ.営業外費用
支払手数料 660301 手续费
為替差損 660303 汇兑损益
その他
経常利益
Ⅵ.特別利益
Ⅶ.特別損失
過年度損益修正 6901 以前年度损益调整
税引前利益
企業所得税 6801 所得税费用
税引後利益
セルC2に「vlookup関数」を書いていきます。先程設定したデータ範囲「bookdata」の中から科目番号の一致する左から6番目のデータを取ってくる、という命令は「=vlookup(A2,bookdata,6,false)」と書きます。vlookup関数は慣れるまで難しいですが、覚えるととても便利です。

図3:vlookup関数を書く

excel-report-1-3
ちゃんと金額を取り出せたことを確認できたら、この数式を下までコピーしましょう。セルの右下の■マークを左クリックしたまま下にスライドさせていくと簡単です。

図4:数式をコピーする

excel-report-1-4
しかしこのままでは、科目番号を指定していない場所まで数式がコピーされて、エラーの表示が出ています。エラー値をまとめて削除するためには、範囲を選択した状態で「検索」から「ジャンプ」、「セル選択」を指定し、数式の「エラー値」のみをチェックしてOKを押します。するとエラー値だけが選択されますので、Deleteキーを押して削除してください。

図5:エラー値にジャンプする

excel-report-1-5
これでデータの日本式フォーマットへの変換作業は完了です。すべての基本となる部分ですから、慌てずに項目をしっかり確認しながら作りましょう。

図6:日本式フォーマットへの変換完了

excel-report-1-6

コメント:0

コメントフォーム
個人情報を記憶する

トラックバック:0

この投稿へのトラックバックURL
http://www.nacglobal.net/2009/08/excel-jp-format-exchange/trackback/
この投稿へのリンク
[Excel財務レポート] (一日目)記帳データのフォーマット変換 from 香港・中国・東南アジア法令情報サイト NAC Global .NET