図書館の本棚

集計方法のスキルアップと、「必要とされ、且つ、分かりやすいレポート」について

関数は、エクセルでの数値集計やデータ成形の際に必要なスキルになります。ここでは財務&会計スタッフが知っておくべき関数を紹介します。

---------------------- 目次 ----------------------

1.財務会計部門でよく使う関数(1) VLOOKUP関数とSUMIF関数

2.財務会計部門でよく使う関数(2) COUNTIF関数とSUBTOTAL関数

3.財務会計部門でよく使う関数(3) IF関数とIFS関数

--------------------------------------------------

エクセルのテーブル化したデータベースを集計する機能として、ピボットテーブルとパワーピボットはとても便利です。関数やプログラミングのコードを書かなくても、ビジュアル的な操作で自動的に集計ができます。両者の違いは、パワーピボットが複数のテーブルを関連付ける事が出来る点です。

----------------------目次-----------------------

4.ピボットテーブルの優秀性

5.パワーピボットの優秀性

-----------------------------------------

Power BI Desktopは、無料でダウンロードができるBIツールで、見栄えの良いダッシュボードやグラフが作成できるようになります。

--------------------- -目次----------------------

6.POWER BIの優秀性

-----------------------------------------

1.財務でよく使う関数(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.財務でよく使う関数(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月から3ヶ月分の試算表を例にしています。

・マトリクス→リスト --- このシートは、科目別試算表のマトリクス表です。このままでは、データベースとして使い辛いので、これをリスト表に置き換えて、エクセルのテーブルに変換していきます。

・科目マスタ --- 各勘定科目のマスタです。

・元テーブル --- マトリクス表からリスト表に変換したテーブルにピボットテーブルで集計する際に必要な列を科目マスタから引っ張って来ます。(VLOOKUP関数を使用)

・PL --- ピボットテーブルで集計し、PLの主要項目を表示しています。(売上高をプラス、売上原価と販管費をマイナス表示で集計)

・経費 --- ピボットテーブルで経費の各科目を月別に集計しています。

・経費比較① --- 「経費」シートに月毎の累計を表示しています。

・経費比較② --- 「経費」シートに前月比に列を表示し、更に、データバーでプラスが緑、マイナスが赤になるように設定しています。

・経費比較③ --- 「経費」シートに各経費の経費全体に対する占有率の欄を加え、更に、タイムラインスライサーを追加しています。

5.パワーピボットの優秀性

・複数のテーブルの存在を前提にリレーションシップでそれぞれのテーブルを関連付ける。取引のテーブルにデータを追加していく事で、月次レベルの集計などで容易に内容が更新できる。

・メジャーやDAT関数の使用で様々な計算や表示が可能となり、レポートの分析結果が格段に幅広くなる。

(ダウンロードするエクセルファイルを添付しています) 

 

​ 

例題では、売上明細、予算、顧客、商品、カレンダーの各テーブルから構成され、リレーションシップで紐付けします。2018年度から3年度分の売上実績データが入っており、下記6種類の資料を作成していきます。

・ジャンル別売上推移

・ジャンル別収益性分析

・ジャンル別売上割合

・当期売上累計

・前年同期比表

・予算実績対比表

6.POWER BIの優秀性

Power BI Desktopは、無料でダウンロード、インストールが可能なBIツールです。プログラミングの知識がなくてもデータの抽出から分析、レポート(ダッシュボード)やグラフの作成までが可能で、具体的には以下のような操作を行う事ができます。

・各種データ処理(抽出、変換、統合など)

・視覚化された分かりやすいレポートの作成

・作成したレポートを定期的に自動更新

(具体的な資料のイメージをPDFで作成しています)

ここでは例題として、中国市場における2017年度~2020年度のデスクトップ、ノートPC、タブレット、ディスプレイ、マウス、携帯端末の各製品売上データを、棒グラフ、線グラフ、円グラフで可視化するレポートを作成しています。実際にプレゼンする場合には、例えば、年度、地域、製品化カテゴリーなどの切り口で、シート内の各グラフを連動して可視化する事ができます。