LoginSignup
12
0
ビジネスエンジニアリング株式会社（B-EN-G）Advent Calendar 2023Day 17b-en-g
@masabonneinビジネスエンジニアリング株式会社 (B-EN-G)

タイの現地法人を経営している元ITエンジニアが経営分析・管理会計に重宝しているナウいExcel

Last updated at Posted at 2023-12-18

はじめに

この記事では、日系中堅IT企業のタイ法人を経営している元ITエンジニアが
経営分析・管理会計業務を行う際に重宝・多用しているExcel術を紹介します。

自己紹介

新卒で今の会社に入り、早20年以上が過ぎました。
2000年初頭に大量生産された、いわゆる「文系ITエンジニア」でした。
これまでの経歴ざっと以下のような感じです。

  • 自社製ERP(mcframe)事業部のプリセールス部門に配属
  • 当時、事業本部内の半分以上のメンバーが投入されていた大規模プロジェクトに参画
  • 商品開発部門に異動して、開発業務を経験
  • またプリセールス部門に異動（商品開発と兼務）し、歌って踊れる開発と営業ができる、ドラクエ3の賢者的ITエンジニア（だったと信じたい）にジョブチェンジ
  • コンサルタント＆プロジェクトマネージャーとしてmcframe導入プロジェクトをいくつか経験しつつ、製品の多言語化を推進
  • 2008年のタイを皮切りに、中国、シンガポール、インドネシアでmcframe導入プロジェクトを経験
  • 2014年からインドネシア法人立ち上げを担当し、2019年まで駐在。営業・導入・開発だけでなくバックオフィスも携わる。人生初めての部下はインドネシア人。
  • 2019年10月からタイ法人赴任。コロナ禍の一時帰国等々を経て、2023年から現地責任者。やっていることはインドネシアと同じく全部担当。

ナウい モダンExcelとの出会い

コロナ禍スタートの2020年5月ころ、フルリモートでmcframe原価管理（管理会計）の導入プロジェクトが始まりました。
このプロジェクトは管理会計を得意とする方が担当だったこともあり、
単なるシステム導入だけでなく、管理会計資料を新しく作りながら運用を検討していくような
良く言えばアジャイル型なプロジェクトでした。
担当者さんの要望する資料はだいたい以下のような感じ。

  • 絶対Excel（自分たちで加工したいから、自分たちでも分かるツールで）
  • グラフは要らない（分析に向いてない、データを恣意的に見せられるから）
  • 極力マクロは使わない（途中経過が分かるように）

ということで、なるべくデータウェアハウス的なものを作っておいて、
お客さん側でデータを好きに集計してもらうのが良いよね、と考えました（当たり前か）
そこで「Excel データ集計」あたりでググって出会ったのが以下の本でした。

Excelパワーピボット 7つのステップでデータ集計・分析を「自動化」する本
https://www.amazon.co.jp/dp/4798161187

いやはや目からウロコでした。
過去にはある程度プログラミングの経験はあるのですが、
新しいプログラミング言語を一から覚えるのはしんどい四十代にピッタリ！
データベース設計とSQLの知識だけで十分使えるのが
PowerQueryとPowerPivotです。
特にPowerPivotは今回のプロジェクト要件に合致していると思いました。
お客さんにも紹介して両社で多用することになりました。
おかげさまで実用的な「モダンExcel Tips」を多く得ることができました。
その後、2023年から自社の経営管理をするようになり、お客さん向けに使うだけでなく、
自社用の管理会計資料としてもモダンExcelを使うことになりました。

あなた無しには生きられない「モダンExcel Tips」

世界のどこかの同じ立場で同じような業務をされている方のニッチニーズに応えて
経営分析・管理会計に使える「モダンExcel Tips（PowerPivotメイン）」を紹介します。
お題としては管理会計のはじめの一歩、売上分析を行います。
※弊社タイ法人のデータではありません
※ちなみに、一般的なタイの法人はタイ商務省(DBD)のウェブサイトから主要経営指標を確認することができるようになっています（インドネシアの時はこんな便利なもの無かった）

事前準備

PowerPivotアドインを追加します。（開発タブ）

スクリーンショット 2023-12-17 174837.png

続いて、サンプルデータはChatGPTにお願いして作成してもらいました。
依頼文は以下のとおり。

次の条件を考慮して、仮想的なソフトウェア企業の売上データを作成してください。
#基本情報

  • 事業セグメント: ERP, IoT, BI
  • 売上カテゴリー: ソフトウェア販売, ソフトウェア保守, 導入サービス, その他
  • 品目(1-ERP): ERP-A, ERP-B, ERP-C, ERP-D, ERP-E
  • 品目(2-IoT): IoT-P, IoT-Q
  • 品目(3-BI): BI-X, BI-Y, BI-Z

#出力情報

  • 期間は2021年から2023年の３年間分
  • データの項目は「取引ID, 年月, 事業セグメント, 売上カテゴリー, 顧客, 品目, 売上金額」としてください。
  • 取引IDは連番にしてください。
  • 顧客は20社程度にしてください。
  • 「ソフトウェア販売」の売上金額は、100,000～1,000,000の範囲の整数でランダムに設定してください。
  • 「ソフトウェア保守」の売上金額は、50,000～300,000の範囲の整数で、すべての顧客に対して一定額で毎月発生させてください。
  • 「導入サービス」の売上金額は、100,000～1,000,000の範囲の整数でランダムに設定してください。
  • 「その他」の売上金額は、50,000～100,000の範囲の整数でランダムに設定してください。
  • 毎月の合計売上金額は、50%の範囲内でランダムにしてください
  • 毎月の合計売上比率は、ERP : IoT : BI = 7 : 2 : 1にしてください。
  • 毎年の合計売上金額を5～10%向上させてください。
  • 作成したデータはExcel形式でダウンロード可能な状態で出力してください。
  • データは日本語でお願いします。

できたのは以下のようなデータ。

スクリーンショット 2023-12-18 000531.png

要望の後半の合計値に対する依頼は叶いませんでしたが、
おかげさまでもっともらしいデータが作成できて超絶助かりました。

準備運動編

まずはPowerPivotではなくExcelの機能ですが、
意外と知られていなくて便利機能Tips。

テーブルを使おう

売上や原価等の経営データは日々追加されていきますので、
集計元のソースデータはテーブルにしましょう。
（ホームタブ→テーブルとして書式を設定）
ピボットテーブルを作る際に、範囲指定が不要になり、集計ミス防止になります。
見た目もキレイだし。

スクリーンショット 2023-12-18 000926.png
個人的には「中間」配色の1行目のスタイルが好きです。
今後使いやすいように「テーブル名」も指定しましょう。
（テーブルデザインタブ→テーブル名）

スクリーンショット 2023-12-18 000814.png
T_SALESというテーブル名にします。

ピボットテーブルを使おう

データ集計・分析するなら、なにはともあれピボットテーブル使いましょう。
（挿入タブ→ピボットテーブル）

スクリーンショット 2023-12-18 001541.png
ここで範囲ではなく必ずテーブルを指定します。

ピボットテーブルのフィールドをドラッグ＆ドロップで指定します。
（ピボットテーブル分析タブ→フィールドリスト）

スクリーンショット 2023-12-18 002042.png
今回は、みんな大好き「縦軸：カテゴリー、横軸：年月」にしました。
デザイン・小計も好みのものをチョイスします。
（デザインタブ→ピボットテーブルスタイル、小計）

スライサーを使おう

全社分を俯瞰できるようになったら、その後は顧客や月別に詳細を見たくなりますよね。
そんな時に便利なのが「スライサー」です。
昔からあったのに意外と知らなかった機能の一つ。
年月と顧客でスライサーを追加してみます。
スクリーンショット 2023-12-18 002443.png

スライサーの位置とサイズをうまく合わせて見やすくします。
スクリーンショット 2023-12-18 002749.png

スライサーの値を選択するとピボットテーブルの表示が更新されます。
ShiftやCtrlキーで複数選択も可能です。プチ感動。

ベーシック編

顧客別・年月別ができてくると次にやりたくなるのは、
顧客業界別、品群別、時系列別（四半期・通期別）
あたりですよね。
ここでようやく本題の「PowerPivot」に入ります。
「PowerPivotとは」とか「PowerPivot特長」で探してもらうと
大量の解説WebサイトやYouTubeがあるのでそちらを参照いただきたいのですが、
30秒でお伝えするとしたら以下3点です。

  • Power BI の一部をExcelアドイン化（したようなもの）
  • 複数テーブルをJoinして使える（ピボットテーブルは1テーブルのみ）
  • メジャーという仮想列を追加して、DAXという分析用関数を使える

データモデルを使おう

PowerPivotではテーブルではなく「データモデル」を使います。
データベースのテーブルのように、列の定義や結合条件を設定することができます。
（PowerPivotタブ→データモデルに追加）

スクリーンショット 2023-12-18 003138.png

列の書式や追加が可能です。
年月は日付型、売上金額はカンマ付き整数にしました。
スクリーンショット 2023-12-18 003523.png

複数テーブルをJoinさせよう

本編の目標である、顧客業界別、品群別、時系列別をやるために、
マスタテーブルを追加してJoinさせます。

顧客マスタ、品目マスタ、カレンダーマスタを追加します。
本来ERP等を使用していれば、対象のマスタテーブルをPowerQueryで取得するだけなのですが、
今回は手動で作成します。
スクリーンショット 2023-12-18 004108.png　顧客マスタ（M_CUSTOMER）

スクリーンショット 2023-12-18 004450.png　品目マスタ（M_ITEM）
※事業セグメントと同じ・・・（まあ普通はトランザクションテーブルにセグメントなんて持たないですもんね）

スクリーンショット 2023-12-17 220416.png　カレンダーマスタ（M_CALENDAR）
※日本以外の会社は会計期間1～12月というのが多いです

続いて、テーブルをJoin（リレーションシップを作成）させます。
（ピボットテーブル分析タブ→リレーションシップ）
スクリーンショット 2023-12-17 221044.png

リレーションシップを新規作成します。
スクリーンショット 2023-12-17 221133.png

テーブルにトランザクション、関連テーブルにマスタを指定して、同じ列名でつなぎます。
SQLのJOIN句書くのと同じです。
スクリーンショット 2023-12-17 221201.png

PowerPivot管理画面から、正しくリレーションシップが作成できていることを確認できます。
（PowerPivotタブ→管理→ダイアグラムビュー）
スクリーンショット 2023-12-17 221703.png

ちゃんと、マスタ：トランザクション＝１：Nになっていることを確認できたら、
準備運動編と同じ用にピボットテーブルを作成していきます。
ピボットテーブルの行・列フィールドはマスタから、値はトランザクションから取得するのがポイントです。

スクリーンショット 2023-12-18 005231.png

スライサーも追加して、なんとなくそれっぽいピボットテーブルになります。

メジャー・DAXを使おう

ここで、管理会計っぽくするために、比率を追加してみます。
ここで使用するのがメジャーとDAXです。
DAXはSQLのWindow関数に似てる感覚あります。（個人的感想）
今回は、総計（総売上）に対する各行の比率を追加してみます。
※実は普通のピボットテーブルの機能でできるのですが、あえてDAXで
ピボットテーブルのフィールドから、T_SALESを選択して右クリック→メジャーの追加

スクリーンショット 2023-12-18 005338.png

ひとまず、売上の合計値を求めるだけのメジャーを作成します。
DAXも通常のExcel関数と同じSUMを使います。
メジャーの名前はテーブルの列名との重複が許されないので、微妙に変更する必要があります。
集計方法はDAXで指定する形になるので、集計方法がわかるような名前にすべきです。

スクリーンショット 2023-12-18 005537.png

引数は、「テーブル(データモデル)[列名]」というように、SQLっぽく指定します。
作成できたら、値フィールドに追加してみましょう。
「合計 / 売上金額」と同じ値が入るはずです。
スクリーンショット 2023-12-18 011040.png

「合計 / 売上金額」を値フィールドから外して、また新しくメジャーを追加します。
追加するのは、対象年月の全品目の売上合計です。

スクリーンショット 2023-12-18 012401.png

いきなりCALCULATEとかALLとかExcelでは聞いたことがない関数が出てきてしまい恐縮ですが、
「ALL(M_ITEM)＝品目を無視して全品目」を意味しています。
（範囲を無視したり考慮したり指定できるところが、なんかSQLのWindow関数っぽい）
できたメジャーを値フィールドに追加すると、対象月の総計と同じ値が各行に入っているのがわかります。

スクリーンショット 2023-12-18 013219.png

このメジャーの値を利用して、全品目に対する対象品目の売上比率を求めようとしています。
それでは売上比率のメジャーを追加します。

スクリーンショット 2023-12-18 013130.png

四則演算のスラッシュでも割り算できるのですが、DAXではゼロ割等のエラーを回避できる
DIVIDEを使用するのが推奨されています。
ここで、先ほど作成した2つのメジャーを引数に指定します。
できあがったら、「売上総合計」を外して「売上比率」を値フィールドに追加します。

スクリーンショット 2023-12-18 013646.png

スライサーを使ってもちゃんと売上合計に応じた売上比率が表示されます。

おわりに（もっと学びたい方へ）

売上だけでなく、費用（固定費・変動費）も入れてCVP分析！や、
予算や昨年同月との予実昨年比較！あたりもやりたいところですが、また別の機会に。
興味を持たれた方は、前述の本の購読を推奨します。
また、そもそも元データ（売上、原価、利益）をどうやって取ればいいの？
という企業の担当者の方は、ぜひ弊社のmcframeを検討いただければ幸いです。
（なんというオチ）

12
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
12
0