試算表とは、帳簿の記録が正しいかどうかを確認するための一覧表で、借方・貸方の残高を科目ごとにまとめたものです。
会計ソフトを使わずにExcelで試算表を管理している個人事業主や中小企業の経理担当者も多いでしょう。
この記事では、Excelで試算表を作る方法を関数の使い方から書式設定まで丁寧に解説します。
Excelの基本的な操作が分かる方であれば、実践的な試算表を作成できるようになります。
Excelで試算表を作る前に、試算表の基本を確認しておきましょう。
試算表には主に3種類あります。
合計試算表
各勘定科目の借方合計と貸方合計を集計したものです。
借方合計と貸方合計が一致することで、転記ミスがないことを確認できます。
残高試算表
各勘定科目の残高（借方残高または貸方残高）だけを集計したものです。
財務状態を一覧で把握するのに適しています。
合計残高試算表
合計試算表と残高試算表を組み合わせたもので、最もよく使われる形式です。
借方合計・貸方合計・借方残高・貸方残高の4列構成が一般的です。
この記事では合計残高試算表をExcelで作る方法を解説します。
Excelで試算表を作る場合、仕訳データシートと試算表シートを分けて管理するのがおすすめです。
シート①：仕訳データシート
日付・伝票番号・勘定科目・補助科目・摘要・借方金額・貸方金額の列を設けます。
日々の取引をこのシートに入力していきます。
例えばA列に日付、B列に勘定科目、C列に借方金額、D列に貸方金額という構成です。
シート②：試算表シート
勘定科目の一覧を縦に並べ、借方合計・貸方合計・借方残高・貸方残高を横に展開します。
仕訳データシートへの参照をSUMIF関数で集計します。
最終行に合計行を設けて、借方合計と貸方合計が一致しているかを確認します。
試算表作成の核心となるのが、SUMIF関数による勘定科目別の集計です。
SUMIF関数は「指定した条件に合致するセルの合計を求める」関数です。
SUMIF関数の基本書式
「=SUMIF(検索範囲, 検索条件, 合計範囲)」
検索範囲：仕訳データの勘定科目列
検索条件：集計したい勘定科目名
合計範囲：合計する金額列
具体的な設定例
仕訳データシートのB列に勘定科目、C列に借方金額が入力されているとします。
試算表シートで「現金」の借方合計を求めるには、「=SUMIF(仕訳データ!B:B,”現金”,仕訳データ!C:C)」と入力します。
試算表シートのA列に勘定科目名が入力されていれば、「=SUMIF(仕訳データ!$B:$B,A2,仕訳データ!$C:$C)」とセル参照にしてコピーすることで全科目の集計が効率化されます。
貸方合計も同様に設定する
貸方合計は「=SUMIF(仕訳データ!$B:$B,A2,仕訳データ!$D:$D)」のように、合計範囲を貸方金額列に変えるだけです。
$記号で列を絶対参照にしておくことで、数式を下にコピーしたときに列がずれません。
合計が集計できたら、次は残高を計算します。
残高は科目の性質（借方科目・貸方科目）によって計算方法が異なります。
借方科目の残高計算
現金・売掛金・備品などの資産科目は借方科目です。
借方残高 = 借方合計 – 貸方合計
計算結果が正の場合は借方残高欄に、負の場合は貸方残高欄に表示します。
貸方科目の残高計算
買掛金・借入金・資本金・売上などは貸方科目です。
貸方残高 = 貸方合計 – 借方合計
計算結果が正の場合は貸方残高欄に表示します。
MAX関数とIF関数を使った残高表示
借方残高欄：「=MAX(借方合計-貸方合計,0)」
貸方残高欄：「=MAX(貸方合計-借方合計,0)」
このようにMAX関数を使うことで、残高がマイナスの場合（つまり残高がない側）は0を表示し、すっきりした試算表になります。
または「=IF(借方合計>貸方合計,借方合計-貸方合計,””)」とIF関数でゼロや空白になるよう制御するのも有効です。
正しく記帳されていれば、借方合計の合計と貸方合計の合計は必ず一致します。
Excelでは以下のようにチェック機能を設定できます。
合計行のSUM関数
試算表の全科目の下に合計行を設け、各列にSUM関数を入力します。
借方合計列の合計：「=SUM(B2:B100)」（実際の範囲に合わせる）
貸方合計列の合計：「=SUM(C2:C100)」
貸借一致チェックの数式
合計行の隣のセルに「=IF(借方合計合計=貸方合計合計,”OK”,”不一致！”)」と入力します。
「OK」と表示されれば仕訳の転記に誤りがないことが確認できます。
「不一致！」と表示された場合は仕訳データを見直す必要があります。
条件付き書式でエラーを視覚化する
貸借チェックセルに条件付き書式を設定して、「不一致！」のときは赤字・赤背景で表示されるようにすると、一目でエラーを確認できます。
「ホーム」→「条件付き書式」→「セルの強調表示ルール」→「文字列」から設定できます。
数式の設定が完了したら、試算表を読みやすくするための書式設定を行いましょう。
勘定科目のグループ分け
資産・負債・純資産・収益・費用といった区分ごとに科目をまとめ、区分の見出し行に背景色をつけて区別を明確にします。
Excelの「グループ化」機能を使うと、区分ごとに折りたたんで表示できるため、大量の科目がある試算表でも管理しやすくなります。
金額に桁区切りカンマを設定する
金額欄を選択して「ホーム」→「数値の書式」→「会計」または「数値」（桁区切りあり）を選ぶことで、1,000,000のように桁区切りが入り読みやすくなります。
ゼロを非表示にする
金額が0の科目が多い場合、「Excelのオプション」→「詳細設定」→「ゼロ値のセルにゼロを表示する」のチェックを外すことで、ゼロを非表示にできます。
または条件付き書式でゼロのときにフォント色を白にする方法もあります。
月次試算表の管理方法
毎月の試算表を管理するには、シートを月ごとにコピーして管理する方法と、1シートに年間を通じた推移を横並びで管理する方法があります。
月次推移表形式にすると、売上・費用の月別推移が一覧でわかり、経営分析にも役立ちます。
月次や四半期ごとの試算表を作る場合は、期間を絞った集計が必要になります。
SUMIFS関数を使えば、日付と勘定科目の複合条件で集計できます。
SUMIFS関数の書式
「=SUMIFS(合計範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, …)」
月次集計の具体例
仕訳データシートのA列に日付、B列に勘定科目、C列に借方金額が入力されている場合、2024年4月の「売上」の借方合計を求めるには、
「=SUMIFS(仕訳データ!C:C,仕訳データ!A:A,”>=”&DATE(2024,4,1),仕訳データ!A:A,”<=”&DATE(2024,4,30),仕訳データ!B:B,”売上”)」
と入力します。
開始日・終了日を別セルに設定して参照すると、月を変えるだけで自動更新される便利な月次試算表が作れます。
Excelで試算表を作るには、仕訳データシートと試算表シートを分けた2シート構成が基本です。
SUMIF関数で勘定科目別に集計し、MAX関数やIF関数で残高を正しく表示することがポイントです。
貸借一致チェックの数式を組み込むことで、転記ミスをすぐに発見できる仕組みが作れます。
さらにSUMIFS関数を活用すれば月次試算表や期間別集計も実現でき、会計ソフトに近い機能をExcelで実装できます。