集計方法のスキルアップと、「必要とされ、且つ、分かりやすいレポート」について
関数は、エクセルでの数値集計やデータ成形の際に必要なスキルになります。ここでは財務&会計スタッフが知っておくべき関数を紹介します。
---------------------- 目次 ----------------------
1.財務会計部門でよく使う関数(1) VLOOKUP関数とSUMIF関数
2.財務会計部門でよく使う関数(2) COUNTIF関数とSUBTOTAL関数
--------------------------------------------------
エクセルのテーブル化したデータベースを集計する機能として、ピボットテーブルとパワーピボットはとても便利です。関数やプログラミングのコードを書かなくても、ビジュアル的な操作で自動的に集計ができます。両者の違いは、パワーピボットが複数のテーブルを関連付ける事が出来る点です。
----------------------目次-----------------------
-----------------------------------------
Power BI Desktopは、無料でダウンロードができるBIツールで、見栄えの良いダッシュボードやグラフが作成できるようになります。
--------------------- -目次----------------------
-----------------------------------------
１.財務でよく使う関数(1)
財務スタッフが仕事の場面でVLOOKUP関数とSUMIF関数を使う事を想定し、具体的なケースを紹介します。(ダウンロードするエクセルファイルを添付しています)
【Case1】VLOOKUP関数 --- 納品書の自動転記
・発注書の内容量と単価のデータをVLOOKUP関数で自動転記させる
・発注書に入力の際、「プルダウンリスト」より品名を選び、発注個数を入力すれば、金額及び合計金額は自動計算される
【Case2】VLOOKUP関数 --- 売価リストへ原価マスタから原価情報を追加(ファイルの結合)
・原価マスタから原価データをVLOOKUP関数で製品販売リストへ自動転記させる。(列の追加)
・製品毎の原価率を表示させる資料を作成する
【Case3】SUMIF関数 --- 年間販売計画で受注確度毎に集計
・製品毎の積み上げで年間販売計画を作成。SUMIF関数を使い、受注確度毎に月別の販売計画を集計する
【Case4】 VLOOKUP関数とSUMIF関数(応用) --- BankStatementから費用科目の自動集計
・各ページ毎の説明
Case4.1 --- オリジナルのBank Statement、相手先(顧客、供应商)の列を含む
Case4.2 --- 「科目」と「科目名称」の列を追加する
Case4.3 --- 相手先マスタ
Case4.4 --- VLOOKUP関数で相手先から 「科目」と「科目名称」を関連付けする
Case4.5 --- SUMIF関数で経費科目毎に集計する →集計表の完成
Case4.6 --- Case4.4のシートも元データにして、Pivot Tableで集計する、VLOOKUP関数とSUMIF関数の 集計はPivot tableの機能で代用する事ができる →集計表の完成
２.財務でよく使う関数(2)
COUNTIF関数は、データを検索条件によってカウントしたり、条件分岐の処理をさせたりする、とても使い勝手の良いです。又、SUM関数で集計が上手くできない時に使うSUBTOTAL関数を紹介します。
(ダウンロードするエクセルファイルを添付しています)
【Case5】COUNTIF関数 --- 条件に一致するデータを数える
・COUNTIF関数により、検索条件に合ったものの件数をカウントする --- 件数
・COUNTIF関数とIF関数の組み合わせにより、検索条件に合った場合の分岐の処理をする --- Company Nameに「Ltd」が付く場合に「該当」を表示
【Case6】SUBTOTAL関数 --- 合計や平均などの集計
・各グループ毎の合計/平均/データ件数/最大値/最小値などをSUBTOTAL関数で集計、更に合計を表示する。
【Case7】SUBTOTAL関数 --- フィルターと一緒に
・SUBTOTAL関数でフィルターを掛けた分の合計だけを集計する。(SUM関数では出来ない事)
3.財務でよく使う関数(3)
IF 関数を使用して条件を論理式で指定すると、その結果が真 (True) の場合は真の場合の値を返し、偽 (False) の場合は偽の場合の値を返します。
IF関数の書式
=IF(論理式 , [値が真の場合] , [値が偽の場合])
IF関数をネスト(入れ子式)して3通り以上の場合分けを処理する
=IF(論理式 ,[ 値が真の場合] , IF(論理式,[値が真の場合],[値が偽の場合] ) )
↑値が偽の場合、更にネストする
IF関数の進化版であるIFS関数では、ネストしなくても、複数の条件を選択する事ができます。
=IFS(論理式1 ,[ 1が真の場合] , 論理式2 , [2が真の場合] , ……, TRUE, [条件を満たさない時] )
(ダウンロードするエクセルファイルを添付しています)
4.ピボットテーブルの優秀性
ピボットテーブルの優秀性は、以下のような点です。
・エクセルでクロス集計が簡単に作成できる
・元のデータを更新するだけで、クロス集計表をアップデートできる
・関数を使うことなくマウス操作だけで大量のデータの分析ができる
・集計項目の入れ替えも簡単かつ視覚的に行える
ここではPL作成や経費比較分析の資料を紹介します。(ダウンロードするエクセルファイルを添付しています)
エクセルファイル各シートの説明
・科目余額表 --- ここでは2021年9月から３ヶ月分の試算表を例にしています。
・マトリクス→リスト --- このシートは、科目別試算表のマトリクス表です。このままでは、データベースとして使い辛いので、これをリスト表に置き換えて、エクセルのテーブルに変換していきます。
・科目マスタ --- 各勘定科目のマスタです。
・元テーブル --- マトリクス表からリスト表に変換したテーブルにピボットテーブルで集計する際に必要な列を科目マスタから引っ張って来ます。(VLOOKUP関数を使用)
・PL --- ピボットテーブルで集計し、PLの主要項目を表示しています。(売上高をプラス、売上原価と販管費をマイナス表示で集計)
・経費 --- ピボットテーブルで経費の各科目を月別に集計しています。
・経費比較① --- 「経費」シートに月毎の累計を表示しています。
・経費比較② --- 「経費」シートに前月比に列を表示し、更に、データバーでプラスが緑、マイナスが赤になるように設定しています。
・経費比較③ --- 「経費」シートに各経費の経費全体に対する占有率の欄を加え、更に、タイムラインとスライサーを追加しています。
5.パワーピボットの優秀性
・複数のテーブルの存在を前提にリレーションシップでそれぞれのテーブルを関連付ける。取引のテーブルにデータを追加していく事で、月次レベルの集計などで容易に内容が更新できる。
・メジャーやDAT関数の使用で様々な計算や表示が可能となり、レポートの分析結果が格段に幅広くなる。
(ダウンロードするエクセルファイルを添付しています)
例題では、売上明細、予算、顧客、商品、カレンダーの各テーブルから構成され、リレーションシップで紐付けします。2018年度から3年度分の売上実績データが入っており、下記６種類の資料を作成していきます。
・ジャンル別売上推移
・ジャンル別収益性分析
・ジャンル別売上割合
・当期売上累計
・前年同期比表
・予算実績対比表
6.POWER BIの優秀性
Power BI Desktopは、無料でダウンロード、インストールが可能なBIツールです。プログラミングの知識がなくてもデータの抽出から分析、レポート(ダッシュボード)やグラフの作成までが可能で、具体的には以下のような操作を行う事ができます。
・各種データ処理（抽出、変換、統合など）
・視覚化された分かりやすいレポートの作成
・作成したレポートを定期的に自動更新
(具体的な資料のイメージをPDFで作成しています)
ここでは例題として、中国市場における2017年度～2020年度のデスクトップ、ノートPC、タブレット、ディスプレイ、マウス、携帯端末の各製品売上データを、棒グラフ、線グラフ、円グラフで可視化するレポートを作成しています。実際にプレゼンする場合には、例えば、年度、地域、製品化カテゴリーなどの切り口で、シート内の各グラフを連動して可視化する事ができます。