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

！記事内に広告を含みます

この記事には広告を含む場合があります。

記事内で紹介する商品を購入することで、当サイトに売り上げの一部が還元されることがあります。

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

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

（使用Excel；Excel2013）

目次表示

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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