8. 弥生会計、Excel で部門別５期比較損益計算書を作成
Excel を活用すると、「弥生会計」で実現できる「５期比較財務諸表」を一 歩進めて、部門別の複数年度比較財務諸表を作成できます。 この単元では、弥生会計で作成された資産表を元に Excel の「統合」機 能を利用して、部門別の複数年度比較ができる損益計算書を作成します。 操作事例では 卸事業部 25～27 期比較損益計算書 を作成します。 《操作の流れ 》 [1] 弥生会計で、当期会計データを新規 Excel ブックに書き出す。 [2] 過去の会計期間に切り替え、Excel ブックに追加書き出しをする。 [3] Excel に書き出したデータを「統合」用に加工する。 [4] 「統合」機能を利用して、複数年度比較損益計算書を作成する。 [5] 完成した表を、見栄えのするデザインに指定する。 《操作事例 -[1]データを Excel へ書き出す-》 弥生会計で作成される残高試算表から損益計算書データを Excel に取 り出します。 ⑧ 画面を「弥生会計」に切り替えます。 ⑨ タブを （集計タブ）に切り替えます。 ⑩ 「月次」グループから （残高試算表（月次・期間））を選択しま す。⑪ 入力済の仕訳データに基づく期間残高試算表が表示されます。 ⑫ （部門コンボボックス）をクリックし、表示され た部門名から「卸事業部」を選択します。 ⑬ 期間を選択します。 ⑭ をクリックすると、指定された期間と部門の数値が集 計されていることが確認できます。 ⑮ 「残高０を表示」チェックを外し、残高ゼロの科目を非表示にします。 ※この操作で未使用勘定科目が非表示になり、より解りやすい集計表にな ります。Excel 書き出しをする前には、ぜひチェックをはずしましょう。 １ヵ月のみ選択されている状態 ドラッグして全期間を指定した状態
⑯ 画面上部の機能ボタンから （Excel への書き出し）を押します。 ⑰ 「Excel への書き出し」ダイアログで必要な指定をして、OK を押します。 今回は以下の(a)から(c)項目の確認と設定をします。 ※(a)、(b)は既に指定済です。確認をしてください。 項目 指定することがら (a)書き出すファイル 新規のExcel ファイル (b)出力する部門(注１) 卸事業部 (c)出力対象(注2) 損益計算書 (注１) 複数の部門や「すべての部門を出力する」を選択すると、部門ごとに Excel シ ートが作成されます。 (注 2) 複数の帳票を出力対象にすると、帳票ごとに Excel シートが作成されます。 ⑱ データの取り出しが完了すると Excel が自動的に起動し、残高試算表 データが表示されます。
⑲ 過去の会計データ取り出しに備え、シート名を過去データと区別できる 名前に変更します。 （ここでは「損・卸事業部」を「27 損・卸事業部」にします。） ⑳ Excel ブックに名前を付けて保存し、Excel を終了します。 （ここではブック名を 「卸事業部損益計算書.xlsx」 にします。） ※「卸事業部損益計算書.xlsx」を開いた状態では、過去の会計期間デ ータを追加書き出しすることができません。 《操作事例 -[2]会計期間の切替と、別年度データの追加書き出し-》 会計年度を切り替えて過去データを参照し、Excel ブックに追加書き出し を行います。 ① ナビゲータを表示し、 （事業所データタブ）に切り替えます。 ② 「会計年度移行」グループから （年度切替）を選択します。
③ 表示されたダイアログボックスから２６期を選択し OK を押します。 ④ 参照できる会計期間が変更されます。 タイトルバーやナビゲータの上部に会計期間が表示されます。確認し ておきましょう。 ⑤ タブを （集計タブ）に切り替え、 （残高試算表（月 次・期間））を選択します。 ⑥ 27 期と同じく全期間の卸事業部損益計算書を表示し、 (Excel へ の書き出し)を押します。（57 ページの操作例参照）。 ⑦ 出力対象を 損益計算書 にします。
⑧ 「書き出すファイルについて」グループで「既存の Excel ファイルに追加 する」を選択し、 参照 ボタンをおして、保存済の「卸事業部損益計算 書.xlsx」を出力ファイルに指定し、 OK を押します。 ⑨ 書き出しが終了するとExcel が自動起動し、追加保存されたブックが表 示されます。 ⑩ シート名を「26 損・卸事業部」に変更し、上書き保存をして Excel を終 了します。 ⑪ 会計期間を25 期に切り替え、同様の操作を行い Excel ブックに追加 書き出しをします。 3 期分のデータが書き出された Excel ブッ
《操作事例 -[3]データを「統合」用に加工する-》 作成されたExcel データから不要な情報を削除して、「統合」がしやすい ように加工編集します。 〈編集する項目〉 (a)全てのシートの１～８行目を削除 (b)全てのシートの B、C、D、F 列を削除 (c)項目名「当期残高」を「25 期残高」～「27 期残高」に修正 ① 複数のシートにかかる操作を効果的に行うために、シートを作業グルー プ化します。 ② １～8 行目の行番号をドラッグして選択し、右クリックして表示されるショ ートカットメニューから 「削除(D)」 を選択して行を削除します。 最初のシートをクリックした後、最終のシートを Shift を押しな がらクリックすると、作業グループ化するシートをまとめて選択 右クリックで表示されるメニュ ー か ら 削 除(D) を クリ ッ ク し て、選択した行を削除する 行番号をドラッグして、削除する行を選択 不要な見出し行が 削除された状態
③ B～D 列と F 列を選択し、右クリックして表示されるショートカットメニュ ーから 「削除(D)」 を選択して列を削除します。 ④ アクティブでないシートをクリックして、作業グループを解除します。 右クリックで表示されるメニュ ー か ら 削 除(D) を クリ ッ ク し て、選択した列を削除する 離れた列を選択するには・・・・ B～D 列をドラッグしたあと、Ctrl を押しながら F 列をクリックして、離れた列をまとめて選択 不要な列が削除された状態 アクティブシートのみ、背 景色がハイライト表示され
⑤ 25 期シートの列見出「当期残高」を、「25 期残高」に変更します。 ⑥ 同様に、26 期シート・27 期シートの列見出しを「26 期残高」「27 期残 高」に変更します。 これで準備が整いました。 以降の操作事例で複数の会計期間を比較する損益計算書を作成します。 《操作事例 -[4]「統合」機能を使って比較損益計算書を作成する-》 ① をクリックして新規シートを作成します。 ② 新規作成したシートで損益計算書を作成したいセル（例えば A1 セルな ど）をアクティブにし、「データ」タブの （統合）アイコンをクリックしま す ※損益計算書はアクティブセルの位置に作成されます。
③ 統合ダイアログボックスが表示されたら、「統合元範囲」の入力枠にカー ソルがあることを確認します。 ④ 各シートのデータを範囲選択します。 範囲選択をすると、ダイアログ内の「統合元範囲」エリアに選択範囲のア ドレスが表示されます。 広い範囲のスムースな選択 (1)A1 をクリック (2)範囲の最後（B49）が見えるところまで画面をスクロール (3)Shift を押しながら範囲の最後(B49)をクリック 範囲選択したアドレスが表示され
⑤ データ範囲が正しいことを確認して、 追加 ボタンを押します。 ⑥ シートを切り替え、全ての対象範囲を追加します。 ※追加ボタンを押さないと、データ範囲は追加されません。 ⑦ 「統計の基準」グループの、「上端行」と「左端列」にチェックを入れます。 ⑧ OK を押すと、比較損益計算書が作成されます。 追加ボタンを押すと、選択範囲が統合元範 囲に追加されます。 ３期分のデータ範囲を追加したところ ⑦チェック前の状態
《操作事例 -[5]表に見栄えのするデザインを指定する-》 ⑤ 完成した表の任意の位置をアクティブにします。 ⑥ 「挿入」タブの （テーブル）アイコンをクリックします。 （テーブル作成は Ctrl + T でも実現可能です。） ⑦ 「テーブルの作成」ダイアログボックスが表示されたら、「先頭行をテーブ ルの見出しとして使用する」にチェックを入れ、OK を押します。 Excel の「統合」は、範囲選択されたデータの左端列と上端行の 見出しをキーワードに自動集計を行うツールです。 列見出し（左端列）と上端行（行見出し）をキーワードにデータ検 索を行うため、列見出しと行見出し位置（どちらか一方でも OK） さえ存在すれば、データ表がどこにあっても集計が可能です。 具体的には．．．． ・見出しは表ごとにバラバラの順番で並んでいても集計が可能 ・異なるブック間の集計が可能 ・同一シート内の複数の表での集計が可能 など、自由度の高い機能です。 範囲選択された元データが変更された際に連動して数値が更新されるよう にしたい場合は、「統合元データとリンクする」にチェックを入れます。
One Point
⑧ 表が「テーブル」として認識され、「テーブルツール」が表示されます。 ※「テーブルツール」はテーブル内にカーソルがあるときに表示されます。 このツールの「デザイン」タブで表のデザインを編集します。 「テーブルツール、デザインタブ」 ※フィルター表示 が気になる場合は、データタブの （フィルタ）アイコンをクリックして Off にします。 自動的にフォーマットされた表 「テーブルツール」 「テーブルスタイル」を選択したり、テーブルスタイルの オプションをチェックして、解りやすい表にアレンジする
9. 弥生会計、部門対比ができる残高試算表の作成
弥生会計では、事業所を部門別に管理することができます。さらにExcel の統合機能を活用すると、同一期間内の部門対比資料も作成することがで きます。 この単元では、弥生会計で作成された試算表を元に Excel の「統合」機 能を利用して、部門別の経費比較等ができる損益計算資料を作成します。 【完成例】 《操作の流れ 》 [1] 弥生会計の、試算表データを新規 Excel ブックに書き出す。 [2] Excel に書き出したデータを「統合」用に加工する。 [3] 「統合」機能を利用して、部門比較ができる損益計算書を作成する。 [4] 完成した表を、見栄えのするデザインに指定する。 《操作事例 -[1]データを Excel へ書き出す-》 弥生会計で作成される残高試算表から損益計算書データを Excel に取 り出します。（詳しい操作は57 ページ以降を参照） ① 画面を「弥生会計」に切り替え、 （集計タブ）の「月次」グル ープから （残高試算表（月次・期間））を選択します。② 入力済の仕訳データに基づく期間残高試算表が表示されます。 ③ 出力期間を全期間にして、 （Excel への書き出し）を押します。 ④ 「Excel への書き出し」ダイアログが表示されたら、 ・出力する部門を「すべての部門を出力する」 ・出力対象を「損益計算書」 にそれぞれ指定して、OK を押します。 ⑤ データの取り出しが完了すると Excel が自動的に起動し、残高試算表 データが表示されます。 Excel の統合では、統合された表の見出し列が統合元範囲 シート名の昇順に並ぶ仕様になっています。この仕様を利 用して、作成された表の再左端に来て欲しい事業部に一番 小さな数値番号を付加します。 統合元シートが10 枚以上に成る場合は、01、02、････10、11 のよう に番号の桁数を揃えてつけるようにします。 全社合計を右端列にしたい場合は、全社合計シートの番号を一番大き な数値にします。
One Point
《操作事例 -[2]データを「統合」用に加工する-》 作成されたExcel データから不要な情報を削除して、「統合」がしやすい ように加工編集します。（詳しい操作は64 ページ以降を参照） 〈編集する項目〉 (a)全てのシートの不要行(1～8 行)を削除 (b)全てのシートの不要列(B～D、F 列)を削除 (c)列見出しのうち「当期残高」を各部門名に変更 (d)シート名に番号を付加 ① 複数のシートにかかる操作を効果的に行うために、シートを作業グルー プ化します。（グループ化の操作は63 ページ参照） ② １～8 行目の行番号をドラッグして選択し、右クリックして表示されるショ ートカットメニューから 「削除(D)」 を選択して行を削除します。 ③ B～D 列と F 列を選択し、右クリックして表示されるショートカットメニュ ーから 「削除(D)」 を選択して列を削除します。 (a)不要行の削除 (b)不要列の削除 (d)シート名に番号付加 (c)列見出しの変更 Shift キーを利用して作業グループ化す
④ アクティブでないシートをクリックして、作業グループを解除します。 ⑤ 各シートの「当期残高」になっている列見出しを「全社」「卸事業部」等 の名称に変更します。 ⑥ シート名の先頭に番号を付加します。シート名を右クリックして表示され るメニューから「名前の変更」を選び、シート名の先頭に番号を付けま す。（「1 損・事業所・・」「2 損・卸・・・」のようにします。） ※シート名のダブルクリックでも名前の変更が可能です。 〈ちょっと待って!! 〉 部門ごとのシートをよく見ると、A 列の項目名に一部同じ名称があ ることに気づきます。仕入の合計項目と製造原価の合計です。 このままでは、統合の際一つの合計項目として扱われてしまうため、 「仕入合計」「製造原価合計」など区別のできる名前に変更します。 ※この操作は作業グループ化しての作業ができないことがあります。 シート名を右クリック 「仕入合計」等に変 「製品製造合計」等に変更
《操作事例 -[3]「統合」機能を使って比較損益計算書を作成する-》 ① をクリックして新規シートを作成します。 ② 新規作成したシートの任意のセルで、「データ」タブの （統合） アイコンをクリックします ③ 統合ダイアログボックスが表示されたら、各シートのデータを選択 して追加します。 ④ 全ての範囲を追加したら、「統計の基準」グループの「上端行」と 「左端列」にチェックを入れます。
⑤ OK を押すと、部門別に数値が一覧できる比較損益計算書が作成 されます。 《操作事例 -[4]表に見栄えのするデザインを指定する-》 ① 完成した表の任意の位置をアクティブにし、「挿入」タブの （テ ーブル）アイコンをクリックしてます。 （テーブル作成はCtrl + T でも実現可能です。） ② 「テーブルの作成」ダイアログボックスが表示されたら、「先頭行 をテーブルの見出しとして使用する」にチェックを入れ、OK を押 します。 ③ 表が「テーブル」として認識され、「テーブルツール」が表示され ます。ツールタブでオリジナルな部門対比損益計算書資料を仕上げ ましょう。
10.弥生会計、元帳データを Excel で自分流に加工する
元帳データを Excel に取り出すと、様々な加工や分析が可能になりま す。 この単元では、総勘定元帳から水道光熱費データを Excel に取り出し、 ピボットテーブルなどの機能を利用して部門別・月別に比較する資料を作 成します。 操作事例では 月別水道光熱費比較表とグラフ を作成します。 《操作の流れ 》 [1] 弥生会計で、総勘定元帳の水道光熱費データを新規 Excel ブックに 書き出す。 [2] Excel に書き出したデータを加工し、ピボットテーブルで集計する。 [3]日付や金額の表示形式を指定する。 [4] データをグラフ化して比較する。 《操作事例 -[1]データを Excel へ書き出す-》 弥生会計で作成される 総勘定元帳 から 水道光熱費データ を Excel に取り出します。 ① 「弥生会計」の （取引タブ）で、 （総勘定元帳）を選 択します。② 勘定科目ボックスから水道光熱費を選択します。 ※勘定科目ボックスで科目名の最初の数文字を入力して、科目を絞り込 みます。 ③ 水道光熱費の総勘定元帳が表示されたら、画面上部の機能ボタンから （Excel への書き出し）を押します。 ④ Excel への書き出しダイアログボックスが表示されたら、「新規の Excel ファイルに書き出す」を選択して、OK を押します。 ⑤ Excel への書き出しが完了すると、Excel が自動起動して水道光熱費 サーチキーで絞り込みされている
《操作事例 -[2]水道光熱費データをピボットテーブルで集計する-》 出力された水道光熱費データを、部門別・月別の水道代・電気代が一目 でわかる表に加工します。 ① 不要な行を削除します。 集計に必要な列は、 ・日付 ・部門 ・借方金額 ・摘要 の４列です。不要な列は列削除を利用して削除しましょう。 ② 見出し行と明細行の間に空白行ができます。 空白行を削除して、空白行がない状態にします。 不要な列を削除し 見出しを整えた状態 ①不要な列を削除する ②見出しと明細行間の空白行を削除する
③ アクティブセルを表内に置き、「挿入」タブの （ピボットテーブ ル）アイコンをクリックします。 ④ 「ピボットテーブルの作成」ダイアログが表示されたら、テーブル範囲が 正しいことを確認して、OK を押します。 ⑤ ピボットテーブルのデザイン画面が表示されたら、行ラベルに摘要と日 付、列ラベルに部門、値に借方金額をドラッグします。
⑥ 水道代と電気代の部門別月別金額一覧が完成しました。 《操作事例 -[3]日付や金額の表示形式を指定する -》 ① 日付の表示を年月表示にします。 ピボットテーブルのフィールドリストから、 をクリックします。 ② 表示されたメニューから、 を選択します。
③ 「フィールドの設定」ダイアログボックスの 表示形式 をクリックします。 ④ 「セルの書式設定」ダイアログボックスの分類ボックスから「ユーザー定 義」をクリックし、種類 「yyyy”年”m”月”」 を選び、OK を押します。 ⑤ 画面が「フィールドの設定」ダイアログボックス画面に戻るので、OK を 押します。 ⑥ A 列の表示が年月に変わります。
同様の方法で、借方金額項目をカンマ表示にします。 (1) をクリック (2)表示されたメニューから、 を選択 (3)「フィールドの設定」ダイアログボックスで 表示形式 をクリック (4)「セルの書式設定」ダイアログボックスの分類で「数値」をクリック (5)「桁区切り(,)を使用する」にチェックを入れる 《ピボットテーブル・金額と日付の表示形式を指定した状態》 毎月の経費がより明確になりました。
《操作事例 -[3]データをグラフ化して比較する-》 作成したピボットテーブルを使ってグラフを作成します。 グラフ化することで、数値の特徴がより掴みやすくなります。 ① ピボットテーブル内のいずれかのセルをアクティブにして、「挿入」タブ の （縦棒）アイコンをクリックします。 ② 表示されたグラフ一覧から、任意の積み上げグラフを選択します。 左から２つめが積み上げグラフ
水道光熱費グラフが表示されます。 ※グラフにすることで、水道光熱費のショップに占める割合の高いことが見 えてきました。このようにグラフ化することで、数値だけでは掴みにくい傾 向を捉えることができます。 ③ グラフを電気代だけの比較グラフにします。 グラフの をクリックし、表示されたメニューから「電気代、関 東電力」にチェックを入れてOK を押します。
④ 電気代だけに絞り込んだグラフが表示されます。 ※このようにピボットテーブルを元にしたグラフは、さらに必要なデータに絞 り込んで表示ができます。 グラフを活用する グラフはデータの傾向をより解りやすく表示する便利なツー ルです。用途によって、円グラフ、棒グラフ、折れ線グラフなど、 上手に使い分けてください。 ☆積み上げ棒グラフ 上のグラフのように会社全体の電気代の推移と各部門の内訳をうまく表 すことができます。 ☆折れ線グラフ 時間の流れ（時系列データ）の傾 向をうまく表現することができます。 右の得意先別売上推移グラフをみ ると、中央産業大阪支店に対する 販売額の急激な伸長がよく解ります。 時間の流れと他の得意先データと の比較が一度に表現できるからで
One Point
☆円グラフ データの割合を表現する場合によく使います。 下の円グラフは 70 ページで作成した部門別試算表データから、売上高と 販売管理費を抽出してグラフ化したものです。売上高の全社合計に対する 各部門の比率、販売管理費の全社合計に対する各部門の比率です。 売上に占める卸事業部の割合と、販管費に占める割合が随分違うことがよ く解ります。 このように Excel には用意されているグラフ機能を使うと、データの傾向 がより解りやすくなります。
