エクセルによる月次推移表の活用方法【index関数とmatch関数】

月次推移表をエクセルで作成しておきますと、様々な資料の作成に応用が利きます。エクセルの機能を活用することで、容易に期別財務諸表の比較を行うことができるようになり、また経営分析やグラフの作成にも役立ちます。

月次推移表について

月次推移表とは各月におけるすべての勘定科目の残高を記載し並べたものです。貸借対照表科目及び損益計算書科目を網羅し、損益計算書科目につきましては、単月だけでなくその事業年度における累計額も作っておきますと、より有効な資料となります。

以下、サンプルの事業者は2018年4月に設立した3月決算法人としております。なお、数字は架空のものです。

｜作成手順

月次推移表の作り方を説明いたします。エクセルにて月次推移表ファイルを用意します。

まずは列に使用している勘定科目を並べます。科目の名称は会計ソフトのものと統一しておくことが望ましいです。異なる科目名としたい場合は別シートを用意する作業が増えることになります。行には会計月を事業年度開始月から並べます。こちらも表示については統一しておきます。

年月の入力の仕方の一つの例としましては、yyyy(年)／m(月)／d(日) 形式で入力を行い、エクセルのDATE関数を用います。

DATE関数の書式は、=DATE（年, 月, 日）となります。

これを応用しC1セルには次のように入力します。

= DATE（YEAR（B1）, MONTH（B1）+1, DAY（B1））

上記の例では　B1セルに2018/4/1と入力しC1セルに関数を入力した後、コピー＆ペースト（右側へドラッグ）により各年月を表示させております。

なお、年月の表示形式はセルの書式から変えることができますので、お好みの表示形式に変更可能です。

※セルの書式設定 ＜ 表示形式 ＜ 日付

損益計算書科目の累計は自動的に行われるように計算式を予め入力しておきます。事業年度開始月である4月からの累計金額となるよう、SUM関数で集計します。

2020年7月度の売上高の累計額であるAC58セルの算式は、

= SUM（$Z25:AC25） 　となります。

｜会計ソフトの利用

会計ソフトを利用している場合は、会計ソフトから単月の勘定科目残高データをCSVファイルでダウンロードし、月次推移表ファイルにCSV貼付用のシートを設けて、同シートにコピー＆ペーストします。

CSVのデータそのままですと、不要な集計項目があったり、管理用又は財務報告用の勘定科目名と異なっている等、不具合が生じることがあります。そのような場合には集計表シートを用意し、CSVファイルと同様の勘定科目を並べ、不要な科目は削除します。

そして勘定科目を検索キーとしてVLOOKUP関数により金額を参照します。

集計が正確に行われたことを確認しましたら、月次推移表シートを用意し、管理用又は財務報告用の勘定科目を並べ、集計用シートの数値データを当該会計月の列にコピー＆ペーストします。

注意点としまして、科目名を変更した場合であっても集計用シートと月次推移表シートの科目数及び並び順は一致するようにします。

実際に作成しますと、次のようになります。

※表示の都合により2021年3月期の資産の状況のみとしております。

 

 

 

エクセル関数について

作成した月次推移表における特定のセルの値を引用するには、INDEX関数及びMATCH関数を用います。二つの関数を複合することで指定した列（勘定科目）と行（会計月）の交差する数値を返すことができます。

｜関数の解説

INDEX関数とは、指定した表から指定した行及び列が交差する位置のデータを取得する関数で、書式は次のようになります。

＝INDEX（配列, 行番号, 列番号）

配列には表のセルの範囲を、行番号には行の数値を、列番号には列の数値を入れます。

また、MATCH関数とは、指定したセルの範囲から、指定した値が先頭から何番目にあるかを検索し数値を返す関数で書式は次のようになります。

＝MATCH（検査値, 検査範囲, 照合の種類）

検査値には検索する値を、検査範囲には対象となるセルの範囲を、照合の種類には検索する方法を入れます。完全に一致するものを検索するときは、照合の種類を0（ゼロ）とします。

｜関数による実践

サンプルを使って実際に入力してみます。例えば2021年3月期における単月及び累計の売上高を調べようとします。月次推移表シートに加え、数値を返すためのサンプルシートを用意し、上記の複合関数を入力します。

左側が月次推移表シート、右側がサンプルシートの一部表示となります。

右側のサンプルシートの単月売上高B2セルの計算式（青枠部分）は、

=INDEX(月次推移表!$B$2:$AK$56,MATCH(サンプル!$A2,月次推移表!$A$2:$A$56,0),MATCH(サンプル!B$1,月次推移表!$B$1:$AK$1,0))

とします。

INDEXの行番号は、一つ目のMATCHによりA2セル”売上高”が月次推移表シートにおけるA2セルからA56セルの間の何番目にあるかを検索し、INDEXの列番号は二つ目のMATCHによりB1セル”Mar-21”が月次推移表シートにおけるB1セルからAK1セルの間の何番目にあるかを検索します。そして配列には月次推移表シートの金額が入力されているB2セルからAK56セル（ここでは累計金額部分は含めません）を選択します。

結果、月次推移表シートのA2セルから24番目の行、B1セルから36番目の列が交差するセルであるAK25セルの値200,000が返されました。

一方、サンプルシートの累計売上高B5セルの計算式（赤枠部分）は、

=INDEX(月次推移表!$B$58:$AK$89,MATCH(例!$A5,月次推移表!$A$58:$A$89,0),MATCH(例!B$4,月次推移表!$B$1:$AK$1,0))

となります。

注意点としましては、INDEX関数の配列とMATCH関数の勘定科目の選択は、累計金額部分である58行目から89行目を範囲とする点です。A列には売上高という科目が2つ存在するために、このような対処が必要となります。

 

財務資料への応用

上記の関数の利用によるデータの取得は、様々な財務資料の作成に応用が利きます。勘定科目名や会計月名は直接入力するよりもデータの入力規則を利用すると便利です。エクセル画面上段にあるデータの入力規則をクリックし、

設定＜条件の設定＜入力値の種類　から「リスト」を選択します。

元の値には、勘定科目の場合は月次推移表シートのA列の範囲を、会計月の場合は１行の範囲を選択します。これで容易に比較する科目と年月を選択することができます。

上記は会計月を選択する場合の画面です。

｜比較財務資料

当期・前期・前々期の決算月の数値を並べることで財務諸表の連年比較が可能となります。損益は累計金額を引用します。これは株主や金融機関など外部向けの財務報告資料の基礎資料にもなります。

 

赤枠部分はデータの入力規則によりリスト化し容易に選択できるようにします。緑色部分はINDEX関数とMATCH関数により、自動で数値が返されます。

こちらはリスト化した後のサンプル画像です。

タブを押せば希望する勘定科目を選ぶことができます。

もちろん会計月の選択も同様にできます。

 

 

 

 

当期に対する増減額（上記D列・G列）や増減率（上記E列・H列）を記載すればより有意義な資料となります。

最近3ヶ月の会計月を並べれば、直近の売上高や営業利益等の変遷や財務状況を見ることができます。

その他には、当月度とその前年同期及び前々年同期の月を並べればイレギュラーな取引の発見などに役立ちます。

｜財務分析指標

会社の収益性や流動性を分析する場合、総資本利益率（ROA）や流動比率などの財務分析指標を用いることが定番ですが、必要な各数字を参照するのはそれなりに時間と手間を要します。

あらかじめ計算式を入力しておき、上記のように必要な会計月の科目残高がすぐに引用できれば、資料の作成時間も短縮することが可能です。

資料の準備ですが、使用する財務分析指標の計算式と計算基礎となる財務情報を用意します。財務情報は上記で説明したINDEX関数とMATCH関数により引用します。比較を行うために二期分を並べて作ることをお勧めします。

実際に作成した画面の一部を解説付きで掲載しました。

 

財務指標分析の計算式ですが、例えば売上高営業利益率であれば営業利益金額のセル ÷ 売上高金額のセルとし、株主資本利益率（ROE）であれば当期純利益金額のセル ÷ 純資産金額のセルとすれば求められます。

その他にも棚卸資産回転期間を見たいときは、棚卸資産金額のセル ÷（売上高金額のセル ÷ 12）とします。工夫をすれば様々な指標の算定に対応可能かと思われます。

｜グラフの作成

経営資料にはグラフが付き物ですが、こちらも上記のテクニックを応用することで作業時間の短縮が見込めます。作成方法ですが、まずはグラフの基礎データとなる表を用意します。

これまで同様に列に科目名を、行に会計月をリスト化しておきます。金額はINDEX+MATCH関数から引用します。グラフの場合、科目名が長い（販売費及び一般管理費など）とグラフからはみ出てしまう為、見た目を重視する場合は列を追加して科目の省略名を入力しておくのが良いでしょう。

ご参考までに貸借対照表用の基礎資料はこのようになります。

こちらは貸借対照表の三期比較グラフのサンプルです。

グラフの作成方法自体は今回はご紹介しませんが、ポイントとしましては、

 

といった箇所に気を付けますとグラフの見た目が良くなり、貸借対照表や損益計算書をビジュアル化したような感じとなります。

 

 

 

まとめ（Conclusion）

月次推移表は多くの財務資料の基データとなり得ますので、会計ソフト内にある財務データは保管しておくだけではもったいないです。

エクセルの機能と併せて使えば、様々な資料を少ない時間で作成することが可能となりますので是非活用しましょう。