損益計算書の比較表の作り方
こんにちは。
アシカです。
ありがたいことに、SNSのフォロワーさんからExcelを教えてほしいと言っていただけまして、その方の質問に答える形で、実際の職場の需要に沿った記事を書きたいと思います。
今回は、
【前提】：四半期ごとの決算データを入力したら、
【結果】：過去のデータとの比較が自動でできる仕組みを作ります（＝比較表が自動で作成されるようにする）。比較は①四半期前との比較だけではなく、②半年前、③一年前との比較も自動で行われるようにします。
今回はマクロを使わず関数だけで作ります。
＜＜【前提】について＞＞
四半期ごとにデータをとりまとめて、シート「データベース」に計数を入力していく。データは毎期右に増やしていく。
以下のようにセルC2（水色のセル）に数字を入れると・・・
対応する期の比較表が自動で表示される。
１２＝その下の２０２３年９月を今期として、その１年前との比較がされている。（半年前との比較表、四半期前との比較表も同時に作られる）
通番１１（＝２０２３年６月）と入力したら、
２０２３年６月と２０２２年６月との比較が自動でされる。
シート「データベース」を作る
では、このExcelファイルの作り方の説明を考えていきます。「何と何が同じなのか？」「何と何が因果関係があるのか？」に注目しながら考えていきます。
今回でいうと、
元のデータ：毎回の項目は同じ。また、各期のデータの中でも「売上総利益」＝「売上」ー「売上原価」というように別の項目から集計できるものがあり、もちろん毎回同じ計算式。
【四半期前】との比較表：任意の期のものとそれより【四半期前】のものとを比べる。
【半年前】との比較表：任意の期のものとそれより【半年前】のものとを比べる。
【一年前】との比較表：任意の期のものとそれより【一年前】のものとを比べる。
比較表は【】内の「〇〇前」が異なるだけで、あとは同じです。ということはほぼ同じ仕組みでいけそうです。
というようなことを、私は作成前にだいたいイメージしました。
では、完成にむけて作っていきます。
シート「データベース」を作る
項目を入力します。
損益計算書は縦書きなので、今回は縦の表になります。
（もちろん、この後作る比較表も縦の表になります。）
各項目について、計算式が入れられるものは入れてしまいましょう。
売上総利益＝売上高ー売上原価
営業利益＝売上総利益ー販売費および一般管理費
経常利益＝営業利益＋営業外収益ー営業外費用
税引前当期純利益＝経常利益＋特別利益ー特別損失
法人税等＝税引前当期純利益×３５％（小数点未満切り捨て）
当期純利益＝税引前当期純利益ー法人税等
※「法人税等」は、法人の形態や利益の額によって税率が異なるでしょうが、私は詳しくないので（だから経理は向いてないのだろうか・・・）、一律３５％で計算させてください。
とりあえず、項目の右のセルに数式を入力し、その行には色をつけてしまいましょう（これから毎期データ入力するにあたり、そのセルには入力しなくて良いのですから、色をつけて目印にしておきます）。
なお、「法人税等」は小数点以下を切り捨てていますので、ROUNDDOWN関数を入力しています。
＝ROUNDDOWN(数値,桁数）
※小数点以下を切り捨てなので、「桁数」は０を入力。
同じように、右に四半期ごとの計数を作っていきます。
黄色のセルの部分は右にオートフィル入力でいけますね。
これでデータベースは作れました。
あとはこれを元にした比較表のシートを作成するだけ・・・
本当にそうでしょうか？
たとえば、Excelは何を元に「M列の一年前の計数がI列」だと判断できるのでしょうか？４行目に「〇〇年××月」と入力していますし、この「××」を元に判断できそうですが・・・。でも「年」「月」が入っていますので、単純な引き算の数式を入力するだけでは難しそうです・・・。
で、思ったんです。
各期に番号ふっちゃえばいいじゃん、と。
計数は四半期ごとに入力していますので、規則性があります。
それなら、
【四半期前】：１列左
【半年前】：２列左
【一年前】：４列左
じゃないですか。で、その「〇列左」を通番で管理すれば簡単に自動で比較表を作れそうです。
以下の画像のように、
としちゃえば、たとえば２０２３年９月と一年前の２０２２年９月の比較は、
通番「１２」と通番（１２-４=）「８」の比較、と読み替えられるようになります。
で、それを参照する数式を入力しちゃいましょう。
セルB１：E２に以下のように入力します。
セルC２＝B２-１
セルD２＝B２-２
セルE２＝B２-４
そして、これから作る比較表でセルC2、セルD2、セルE２をそれぞれ参照していけばよさそうです。
シート「〇〇前比較表」を作る
では、比較表のシートを作っていきます。
とりあえず、以下の画像を作ってみました。項目はシート「データベース」と同じです。
さて、シート「データベース」から【検索】するので、VLOOKUP関数を入力して・・・と思ったのですが、うまくいきません。
当たり前です。
VLOOKUP関数は「Vertical（縦） LOOKUP」の略であり、横方向の検索には対応していません。じゃあ、横に参照していく関数はないのか？ということで検索したらありました。
横＝Horizontal ということで「HLOOKUP関数」です。
＝HLOOKUP(検索値,検索範囲,行番号,[検索方法])
※[]は省略可
※最新のExcelだと「XLOOKUP関数」なる縦横双方の検索に対応した関数があるようですが、我が家のExcelが対応していないので今回は使い（え）ません。
「検索値」とは、シート「データベース」のセルB２、C２、D２、E２のいずれかです。
「検索範囲」とは、シート「データベース」のA４：M１８です・・・がM列までなのは現在のデータの話でそのまま入力してはいけません。どこまで右に追加してもいいように、Excelのシートの一番右の列である「XFD列」までを検索範囲とします（つまりA４：XFD１８）。
よって、試しに、シート「データベース」のセルB２に「１２」と入力したうえで、シート「1年前比較表」のセルC５に以下のように入力してみます。
=HLOOKUP(データベース!$B$2,データベース!$A$4:$XFD$18,2,0)
※意味：シート「データベース」のデータベースについて、通番「１２」の列のうち２行目を表示する。
無事、セルC5に通番「１２」に対応する「２０２３年９月」が表示されました。あとはセルC18までオートフィルで入力すれば簡単ですね！！
「２０２３年９月」だらけになってしまいました。
全然簡単じゃなかったです。すみません。
HLOOKUP関数内の「行番号」つまり「２」はオートフィルでは３、４、５・・・と入力されていかないようです。（だからずっと２行目を表示してしまっている）
しょうがないのでひとつひとつ手で入力していきましょう。
・・・とはしたくないですよね！！
何万行もあるデータでも同じことをするつもりなのか？って話です。
ということで調べたら使える関数がありました！
今回はROW関数を使います。
＝ROW（［参照］）
※[]は省略可
ROW関数は、「参照」にセル番地を入れれば、そのセルが何行目か表示してくれる関数です。（「参照」に何も入力しなければ、「＝ROW（）」と入力したそのセルが何行目か表示します）
・・・単体で見たら意味が分からない関数ですが、今回役に立つわけです。
なんだか、漫画の雑魚キャラが活躍するみたいでわくわくしてきました。
「ROW()」はセルC５に入れれば「５」（行目）という意味です。今回は「２」の意味にしたいので、単純に「ROW()ー３」と入力すればOKです。
そして、「ROW()」はセルＣ６に入れれば「６」（行目）になるので、「ROW()ー３」で「３」の意味になります。セルＣ７以降も同様です。
試しに入力して表示してみた画像は以下のとおりです。
このROW関数をHLOOKUP関数に組み込みます。
セルC5に
=HLOOKUP(データベース!$B$2,データベース!$A$4:$XFD$18,ROW()-3,0)
と入力して、さらにセルC１８までオートフィルで入力します。
「１年前」（Ｅ列）には、（今期から１年前の計数の通番である）「検索値」にシート「データベース」のセル「E２」を入力し、あとは「今期」（C列）と同じです。
セルE５に
=HLOOKUP(データベース!$E$2,データベース!$A$4:$XFD$18,ROW()-3,0)
と入力して、さらにセルE１８までオートフィルで入力します。
間のD列には、比較の数値を表示させたいので、
セルD６に
＝C６-E６
と入力して、セルD１８までオートフィル入力します。
あとは、色付けたり枠線引いたり数値を右側に表示するように変更したら以下のようにできあがります。
次に半年前の比較表を作ります。
作ったシート「1年前比較表」をコピーしてシート名を「半年前比較表」に変更します。
そして、セルE４は「半年前」と入力しなおして、
セルE５：E１８の「検索値」はシート「データベース」のセルD２を参照するよう変更します。（セルE５を変更してオートフィル入力でOKです）
四半期前の比較表も同様です。
作ったシート「1年前比較表」をコピーしてシート名を「四半期前比較表」に変更します。
そして、セルE４は「四半期前」と入力しなおして、
セルE５：E１８の「検索値」はシート「データベース」のセルC２を参照するよう変更します。（セルE５を変更してオートフィル入力でOKです）
これで、冒頭の説明のとおり、シート「データベース」のセルB２に通番を入れれば、それに対応した過去との比較表が自動で作れるようになりました。
（余談）通番さえ入れないようにする
余談ですが、そのセルB２に通番を入れることさえやめてしまいましょう。
（※通番には数字を入れるだけなのでそこまで手間じゃないですし、任意の数字を入れられる分、過去のデータを基準とした比較表を作ることもできるので、空欄のままでも良いとは思いますが、絶対に過去のデータを基準とした比較表を作ることがないのならば、通番を入力すること自体が手間なので今からその手間を省きます）
さて、通番にはなんの関数をいれればいいでしょうか？
要は、最新のデータに対応した通番が毎回入力されていればよいわけです。
最新のデータに対応した通番というのはつまり、シート「データベース」の４行めの一番右の数値なわけですが、これって、セルB４から１ではじまって、あとは右に１づつ数が増えていってるだけじゃないですか。
ということは、COUNT関数を使えばうまくいきそうです。
COUNT関数
参照範囲内の数値や日付、時刻の「個数」を求める
セルB2に「=COUNT(4:4)」と入力すれば、４行目の中の数値や日付、時刻の「個数」が表示されます。セルA４の「通番」は文字であり数値や日付、時刻のどれでもないのでカウントされていません。つまり、そのまま入力すれば、一番右の数字が返されます。
以上です。
ここまで読んでいただきありがとうございました。
↓↓募金箱↓↓
ここから先は
¥ 300
この記事が気に入ったらチップで応援してみませんか？