記録することによって見えてくるもの

HLOOKUP関数を使って月次比較損益計算書から概況書の数字を集計する

かわべ
WRITER
 
この記事を書いている人 - WRITER -
かわべ
生誕:1969年 東京 生まれ 趣味:楽器演奏(St.Bass、E.Bass)     読書(ミステリー)     映画鑑賞(学生時代、映画館でアルバイト経験あり)
<スポンサーリンク>  
 

会計ソフトの月次比較損益計算書は左から右へ比較表が作成されていますが、法人事業概況説明書(以下「概況書」)は、月ごとに縦に記載していくため、私はExcelのファイルを作成しHLOOKUP関数を使って集計表を作成し転記するようにしています。

HLOOKUP_TEST_16

※ 会計ソフトに概況書の作成機能がある場合は、Excelで集計させる必要はありません。HLOOKUP関数を使う機会は少ないので、記事にしてみました。

(使用Excel;Excel2013)

参考 国税庁;法人事業概況説明書

<スポンサーリンク>    
 

HLOOKUP関数を使って月次比較損益計算書から概況書の数字を集計する

HLOOKUP関数を使って月次比較の損益計算書から法人事業概況説明書に記載する数値を集計する手順を記載します。

月次比較損益計算書をExcelに貼り付ける

会計ソフト作成した月次比較損益計算書をExcelに貼り付けます。

HLOOKUP_TEST_11

 

概況書の作成に必要な科目の数値を転記する

貼り付けた月次比較損益計算書シートとは別のシートを用意します。私は、月次比較損益計算書のシートを「元データ」としもう1つのシートを「概況書作成」という名称にしました。

HLOOKUP_TEST_12

「概況書作成」シートに概況書の作成に必要な科目の月次比較の数値を転記します。

HLOOKUP_TEST_13

上の例では、売上高合計、仕入高、役員報酬、給料手当、外注費を転記しました。元データの数値が変わった場合に備えて、各セルには「=元データ!・・・」(・・・はセルの位置のアルファベットと数値)を入力してあります。

役員報酬と給料手当の下には、概況書に人件費の合計を記載することを考え、「人件費合計」の行を設定し、各月の役員報酬と給与手当の合計数値をSUM関数を使って計算してあります。

 

千円未満端数切捨ての数値を表示

次に上記の表の下に千円未満端数切捨ての数値を表示する表を作成しました。

HLOOKUP_TEST_14

11行以下の数値が表示されているセルには、千円未満の数値が表示されるように1,000で割って、INT関数を使って端数を切り捨てるという式を入力しました。

例)B12セル 「=INT(B3/1000)」

※ 決算の数値は最終月に含めて千円未満の端数を切り捨てる式を入力しました。

 

千円未満端数切捨ての表に名前をつけて定義

「名前の定義」を使って、千円未満端数切捨ての表を「月次比較表千円」と定義しました。Excel2013のテーブル機能を使っていますので、わざわざ定義をしなくても良いのですが、記事のために定義しました。

HLOOKUP_TEST_15

 

HLOOKUP関数を使って概況書用の数値の集計

同じシートに概況書のフォーマットを作成します。

HLOOKUP_TEST_16

上の画像のとおり20行目には「行数」という行を設定しました。

この「行数」には、上て定義した「月次比較表千円」の範囲のなかで、その科目が(全21行のなかで)何行目にあるのかという行数の数値を入力しておきます。

たとえば「売上高」であれば、(範囲内の上から)2行目なので「2」、「外注費」であれば(範囲内の上から)7行目なので、「7」と入力しておきます。(HLOOKUP関数の式で使用します。)

それぞれ月ごとの数値のセルには次のような式を入力しました。

例)B22セルの場合

「=IF(ISERROR(HLOOKUP($A22,月次比較表千円,B$20)),0,HLOOKUP($A22,月次比較表千円,B$20))」

HLOOKUP関数を使って「月次比較表千円」と定義した範囲からA22セルと同じ列のB20セルに入力した行数(つまりH2604月の「2」行目)を参照するような式になっています。

また、IF関数とISERROR関数を使って、該当数字がない場合は、「0」を表示するようにしてあります。

 

まとめ

このExcelファイルを作成しておくと、決算の数字が申告書の作成過程で変更されても、元データに新しい月次比較表を貼り付けるだけで概況書の数値が表示されるので便利です。(会計ソフトの機能を使える場合はそのほうが便利ですが・・・)


■□◆◇ 編集後記 ◇◆□■

「春一番」は春分の日までに吹かないとその年はなかったということになるそうです。(朝のニュース番組で聞きました。)関東は今年はないのか・・・。

 

Pocket
このエントリーを Google ブックマーク に追加
LINEで送る

この記事を書いている人 - WRITER -
かわべ
生誕:1969年 東京 生まれ 趣味:楽器演奏(St.Bass、E.Bass)     読書(ミステリー)     映画鑑賞(学生時代、映画館でアルバイト経験あり)

報酬のお見積り

法人の顧問報酬と決算報酬を、前期の売上実績当期の売上予測でお見積もりすることができます。(※)

LINK 報酬お見積もり【法人用】 web

お名前やメールアドレスの入力は必要ありません。ウェブ上で簡単にお見積もりができます。

※ オプション料金は別途となります。

Copyright© J-musu-no-blog , 2015 All Rights Reserved.