PowerBIで、BI(BusinessIntelligence)ってやつにチャレンジしてみる(第2回)
まずはPowerBIのインストールだ。
PowerBIだが、閲覧はブラウザやiOSのアプリから見ることができるが、今回は実際にデータを作ることなので、PowerBI Desktopを入手しよう。Windows版オンリー。
インストールは、解説しているサイトがあるのでそこを見れば良いだろう。
今回は、以前VLOOKUPで売上データの集計について書いてみたが、そのデータを使ってみよう。
まずは、Excelと同じく元データを準備する。
今回使ってみるのは「組織マスタ」「商品マスタ」「販売データ」の3つ。Excelデータをそのまま取り込めるので、事前に準備しよう。
PowerBIのイケてるところは、HDDの参照データの場所を覚えていてくれるので、データを更新すれば、レポート側も簡単に更新できることだ。
次に、インストールしたPowerBIを開いてみる。「データを取得」というボタンから選ぼう。一般的なCSVも、EXCELデータも大丈夫。
データを取り込むと、簡単なプレビューが表示される。今回は商品マスタを取り込んだところヘッダ行が指定できていなかったので、編集する。
編集画面。今回は「商品」と、商品群をまとめた「商品括り」とした。
同じように、「組織マスタ」「売上データ」も取り込む。
左の表みたいなアイコンの「データ」を選択すると、「商品マスタ」「組織マスタ」「販売データ」が入っていることがわかる。
そろそろ本番。データを集計するためには、「集計する軸」が必要である。
今度は「リレーションシップ」という画面を使う。各データの繋がりを、マウスでドラッグして「にゅいん」とつなげていく。
元の「販売データ」には担当者名と商品名しかなかったが、各マスタに繋げることで、「販売データ」を所属組織などでも集計できるようになった。
あとは簡単。「レポート」の画面を開き、好きなレポート(グラフとか、表とか)をポチポチ選ぼう。
グラフを選択し、グラフの軸を、フィールドからドラッグするとあっという間にグラフができる。簡単。
で、BIの特徴は「動的にデータの変化を見れる」ことだったりするので、今回は「スライサー」ってやつをつけてみよう。「スライサー」を選択し、ラジオボタンで「所属組織」を入れてみる。選択ボックス的なものができた。
これで、見たい組織をポチっとおせば、グラフが動的に変化する。
今回はシンプルな棒グラフだが、どんなグラフでも動的に表現をすることができる。
で、PowerBI Desktopで作ったレポートは、 そのままクラウドにアップすることができるので、Webアプリケーションのように組織に共有することができる。各事業部門が自分の見たい軸でデータを見ることができるダッシュボードを、簡単に作ることが可能だ。
PowerBIで、BI(BusinessIntelligence)ってやつにチャレンジしてみる
事業データの分析について
BIについて
EXCELとBIが似ているところ
VLOOKUPとSUMIFSで事業分析(第4回)P/Lを作ってみる
縦軸に勘定科目、横軸に月が切られている。
(今回の記事では、当然ながらダミーデータも見せる事はできないので、マスクを入れている)
分析軸は縦軸が勘定科目、横軸は月。
で、(ここがミソだが)あくまでP/Lを分析したいのは事業なので、事業毎にシートを分けたい。元データを更新すれば一挙に全部門のデータが更新されるのが理想的だ。順番を追っていこう。
これは簡単。勘定科目の一覧は会計パッケージに規定されているので、縦軸に勘定科目一覧を並べる。科目名称は同じ科目名で原価・販管費が存在するようなケースもあると困るので科目コードも併記する。
推移表であれば単純にタテをSUMIFSで足しこむだけである。まあ、原価率なども知りたくなるので、そこだけ式を埋め込めば良い。
=SUMIFS(参照先の合計する列、参照先の科目コードが記載された列、集計表の科目コード)
日本語で言うと、こんな感じだ。
参照先の10月の列を足しあげろ。条件は1つ。
売上高のコードだ。よしなにやれ。
あとは、この式をよしなに全体展開すればよい。簡単。$マークの絶対参照だけ気をつけよう。ここを間違えると、コピペの後にイチイチ式を修正しなければならず、超時間がかかる。
タイムイズマネーである。
全社が出来れば次は部門。
まずは部門コードをもとに部門マスタをつくろう。前回の記事を参考にしてもらえばよい。狙いは、集計用組織をVLOOKUPで読み出せるようにすることだ。
複数部門を統合しているような場合もあるので、丁寧に分析をかけたい軸が表現できるようにマスタを作る。あとで使うので、集計したい組織のプルダウン用リストも作っておく。
元のデータ表の左に1列加える。
で、VLOOKUPを埋め込み、先のマスタを参照させ、集計用の部門を表示させる。黄色い列だ。これで、SUMIFSを使えば集計部門で拾うことができるようになる。
材料が揃ったので、集計表に移ろう。
先程作った全社のPLをコピーし、部門集計用のシートを作る。今度は集計表の上の方に、部門を入れる箇所を作る。ついでに、この部門は先のプルダウン用リストを参照先にしておく。
そろそろ大詰め。コピーした全社P/L表のSUMIFS分に一文加える。追加箇所が赤。
=SUMIFS(参照先の合計する列、参照先の科目コードが記載された列、集計表の科目コード、参照先の集計用部門の列、集計表のプルダウンの集計部門)
日本語で言うと、こんな感じだ。
参照先の10月の列を足しあげろ。条件は2つ。
売上高のコードだ。よしなにやれ。
あ、忘れてた。今回は第一事業部の組織が対象だ
あらためて式の$マークに気をつけながら、シート全体の関数に適用すると、
A1セルのプルダウンで組織を選ぶことで、自由に好きな組織のP/Lを表示することができる。このままでも使えるが、最後の仕上げに部門PLのシートをいくつか複製し、プルダウンのデフォルト値を各部門用に設定しておけば、そのまま各部門に展開が可能だ。
ちょっと駆け足で雑になったが、月次決算が〆ったら確定の推移表を更新するだけで、各部門が自分のP/Lを参照できる表の完成である。
VLOOKUPとSUMIFSで事業分析(第3回)検算とIFERRORを使った回避処理
①VLOOKUPを使って、「集計しやすい」データにする→集計軸をVLOOKUPで補足し、SUMIFSに耐えうる状態にする→集計軸に抜け漏れがないようにピボットの機能を一部使う→上記を行うためにマスタを作る②SUMIFSを使って、「シンプルに」集計する→①の集計軸で一気に計算させる
SUMIFSの弱点をカバーする方法
1:SUMIFSを使った、検算式をこっそり仕込む
2:IFERRORを使って、マスタに新しい値が入ってきても一旦処理させる方法
VLOOKUPにIFERRORを組み合わせエラー値を「その他」にする
= IFERROR(VLOOKUP(”元データの担当者”,”組織マスタの担当者と組織が入った表”,2,0),"その他")
これは日本語でいうと、以下のような命令になる。
B2のセルに、D2の担当者「田中」の組織を入れろ。「田中」の組織は、「組織マスタ」で「田中」を探し、見つかったら田中から数えて2番めの列に記載されている。検索条件は完全一致(0)であるので、ピッタリ合うものを探してこい
で、ピッタリ合うものが見つからなかった場合は「その他」にしておけ
VLOOKUPとSUMIFSで事業分析(第2回)
さて、分析といえば売上やP/Lなどがルーチンで必要である。
特に、集計の現場で往々にしてあるのが、元のデータが汚いことである。でも多少のデータクレンジングはエクセルでできるのでその辺を補足しつつ。
さて、データ集計で大切なことは3つある。
①当たり前だがまず間違わないこと。
でも人間は必ず間違えるので検算をしやすくシートを作る事が大切。
②元のデータは極力変更しないこと。
第三者が見て、元データが違うのか、計算が違うのか分からなくなるからだ。後任から「かーみーさんのデータが違っていたので・・・」と責められることは無いようにしたい。
③毎日の集計に耐えられること。
ビジネスで使うデータは毎日増え続ける。いちいち式を直すのはミスの元。
原則一度シートを作ったら更新データをコピペすると自動計算する仕組みが望ましい。
VLOOKUPとSUMIFSで売上データを分析する
では具体的な手順に行こう。
こんな資料を作りたい。月ごとの組織毎、商品グループごとのサマリーだ。
で、使えそうなのはこんな営業マンごとの売上の記録だ。しかしイケてないかな、組織
も商品グループも記載されていない。
大きな手順は以下のとおり。
①VLOOKUPを使って、「集計しやすい」データにする
→集計軸をVLOOKUPで補足し、SUMIFSに耐えうる状態にする
→集計軸に抜け漏れがないようにピボットの機能を一部使う
②SUMIFSを使って、「シンプルに」集計する
→①の集計軸で一気に計算させる
VLOOKUPを使って「集計しやすい」データにする。
1:集計列を加える
まず、元の売上データに「集計用の列」を加えよう。集計用の列は元データの左に。右ではない。元データがいつもX列までとは限らない(勝手に備考とかの列を右に作るやつがいるからだ)。営業マンの気まぐれで列が増えても問題が起きないように、集計用の列は左が鉄則である。
2:集計列に、集計用の値を入れる
次に、集計するために必要な軸を整えよう。今回足りない要素は「組織」「商品グループ」の2つ。担当者から「組織」を逆引きさせ、「商品」から「商品グループ」を逆引きさせればよい。
逆引きするためのマスタを作る
逆引きをさせるためには、マスタを作るのが一番簡単だ。例えば以下のような組織マスタ。これがあれば、VLOOKUPを使って、「営業担当社が広岡さんなら営業1部、山田くんなら営業2部」を入れることができる。なお、マスタを作るのはもちろん手作業なのでそのへんは頑張るしかない。
<組織マスタ>
商品はこんな感じ。
<商品マスタ>
3:ピボットテーブルで抜け漏れの無いマスタを作る
マスタを作る場合、リストの抜け漏れは致命的だが、数百件を超えるリストから抜け漏れなく洗い出すのは結構面倒だ。 例えば組織の場合、対象の表からピボットテーブルを作成し、担当者を行に指定すれば、簡単にユニークな担当者一覧を作ることができる。商品も同じように一覧を作る
<ピボットテーブルでユニークな一覧を抽出する>
4:集計用の表に、VLOOKUPでマスタから引っ張った「集計用の値」を当て込む
ここまでできれば、あとは簡単。元のデータの値を元にマスタから必要な「組織」「商品グループ」の値を引っ張ってくれば良い。商品も同様。
式は以下のような感じになる。
= VLOOKUP(”元データの担当者”,”組織マスタの担当者と組織が入った表”,2,0)
これは日本語でいうと、以下のような命令になる。
B2のセルに、D2の担当者「田中」の組織を入れろ。「田中」の組織は、「組織マスタ」で「田中」を探し、見つかったら田中から数えて2番めの列に記載されている。検索条件は完全一致(0)であるので、ピッタリ合うものを探してこい
VLOOKUPの式が入ったら、全行にコピペする。基本的に同じ式なので、データが増えても同じ式をコピペするだけで良い
5:SUMIFSで一気に計算する
ここまでできれば、ほぼ完成だ。元の集計表から、SUMIFSで条件を設定すれば良い。
式は以下のような感じになる。
=SUMIFS(合計対象範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, ...)
あー、わかりにくい。
わかりやすく日本語でいうと、以下のような命令になる。以下のD4の式。
販売データの「売上(G列)」を全部足せ。条件は2つ。
1つ目の条件は販売データの「売上月(A列)」が「2月(D2)」のものだ。
2つ目の条件は販売データの「組織(B列)」が「営業一部(B4)」のものだ。
この2つの条件に合うように、よしなに足し上げろ
細かく式を見ると、箇所箇所に$マークがついているのがわかると思う。
これは「絶対参照」というやつで、式をコピペしても固定になる。まあこのへんはどっかのタイミングで説明できればと。
商品も同様にSUMIFSをぶっこめば完成だ。以下はC10の式。
販売データの「売上(G列)」を全部足せ。条件は2つ。
1つ目の条件は販売データの「売上月(A列)」が「1月(D2)」のものだ。
2つ目の条件は販売データの「商品くくり(C列)」が「食品(B10)」のものだ。
この2つの条件に合うように、よしなに足し上げろ
6:まとめ
上記のような工程であれば、大体のデータの形は集計可能になる。P/Lなら勘定科目でマスタを作ればいいだろう。
今回は割愛するが、マスタも随時更新されるビジネスの場合は、IFERROR関数を使えばVLOOKUPでエラーになる値を「その他」にするなどの回避ができるので、いったん大枠外さない集計をかけ、後でエラー部を個別に精査するといった段取りも組めるようになる。
集計する元のデータを再度コピペし、ちょこちょことマスタの抜け漏れがないかの手続きを行えばいいので、毎日の集計にもそこそこ耐えうるレベルまで業務工数は減らすことが可能だ。
VLOOKUPとSUMIFSで事業分析
VLOOKUP
SUMIFS
ピボットテーブル
捺印権限を設定する!THE BOARDの使い方(第三回)
TheBoardの使い方(主に組織の設定とか)
権限設定とワークフローについて
The Boardで帳票までできるようになったら、次は権限設定だ。
正直にゲロると、私はカネ系の仕事は結構経験があるが、ヒト系の仕事はあまり経験が薄いので、ちょっと考察が浅いかなーと思いつつ。
さて言い訳はおいといて、上場していて決裁権限ガチガチな会社はもちろん中小企業ですべての捺印は社長という会社も、ハンコの取り扱いは大切だ。営業マンがテキトーに作った見積りで、赤字になってはたまらない。というか、時間のムダだ。
悩ましいのは、どこまでスピーディに商談を進めるかであろう。
「ブチョーが海外出張にいっているので見積りで出せません!」では話にならない。クライアントの要望にスピード高くビジネスを回すのも営業マンの価値である。「わが社は製品だけは負けません!」と叫ぶ営業マンも見受けられるが、じゃーお前の価値は何よ?と思うのがクライアントである。
組織レイヤ設定
で、The Boardは(クラウドだから当然だが)ブチョーが海外にいても承認できる。承認された帳表はPDF上に電子印がつかれる。
具体的には、承認経路を4レイヤーで設定することができる。
申請と承認
当然、ワークフローツールなので申請が上がればメールで通知が飛ぶようになっている。通知が飛ぶというのはとても大事で、それは「通知が来るまで考えなくて良い」ことを意味する。アタマの中をカラにして、本当に重要なものに取り組めるということだ。
組織設定(ロール)
また組織が大きくなれば、属する組織の属性によって管理したくなるのが普通である。会社で定められる職務権限はふつーバイネームではなく、職名で記載されるのがふつうだ。当然この辺の機能もついている。
ちょっと苦手な領域だったので軽く流してしまったが、一旦私の組織ではここまでの流れを作り、いま本番運用に適用しているところだ。
The Board https://the-board.jp/、元々「請求書管理面倒くせえなー」から検討を開始して入れたツールだが、結構お薦めだと思うので、帳表管理に四苦八苦している方は、ぜひ検討してみても良いと思う。
細かいので書かなかったが、「このクライアントだけは末日でなく1日付で発行!」とか内部管理データとは分けて表現だけ変更できたり、痒いところに手が届く感が満載である。
「毎営業日に書く」を日課にしてみたが昼休みに一気に書くのはつかれるので、次回からは(チカラを抜いた)Excel・Google SpreadSheetで、事業系のデータを扱うちょっとしたコツなんかを書いて、その後に「ちきゅう(SFA)」あたりに触れてみようかと。