IT情報コーナー

EXCELピボットでデータ分析

以前のEXCELでは、処理できるデータ量(約65,536件)に制限があり、中小企業といえども数年間のデータを時系列に分析するまでの能力がありませんでした。融通が利かず、色々と試して見ましたが、今ひとつ、と言う感じで、私もピボット機能は無視してきました

それが、EXCEL2007が出現すると処理できるデータ量が一気に1,048,576件、列数も16,384列まで拡大され、これでかなり改善されたかと思い、実際にデータ分析をしてみましたが、ACCESSからEXCELにダウンロードできる件数はそれまでのデータ量と変わらず、といった感じで、100万件を超える機能が生かされない状況で、「やはり駄目か」と落胆してしまいました。

しかし、EXCEL2013が出現すると、最大行数、最大列数は同じ制限量ですが、ダイレクトにACCESSやSQL Serverデータベースから100万件を超えるデータがアクセスできるようになり、なおかつ、関数を使わずに様々な計算ができるようになりました。

月契約のOffice365が提供されるようになってからその進歩のスピードが速まり、今現在も進化が進んでいます。

かって私のように、「EXCELピボットは使い物にならない」と無視されている方が、今でも私の周りには結構いらっしゃいます。

しかし、昔のEXCELピボットとはまるで別物です。是非、皆さんも触ってみてください。

何が変わったか? 主な機能をご説明します。

EXCEL2003までのピボット機能

Ÿ

・ピボットでのマトリクス集計では、行レベルでは3階層まで、列ラベルは1階層しか集計できませんでした。

例えば、商品分類が大分類、中分類、小分類の3種類、商品コード、商品名と5段階があると、どこかの階層を削らなければなりません。列レベルも支店別、課別、担当者別に集計したくてもそれぞれ、支店別だけの集計、課別の集計というように、支店/課/担当者とドリルダウンでの原因分析ができませんでした。

Ÿ・集計項目が1種類だけ、と言うのも大きな弊害でした。

商品別売上分析を行おうとすれば、数量、金額の両方の項目で集計し、分析したいと思います。値引金額、平均単価(できれば標準単価との差異の分析)も一緒に集計したいと思いますが、以前のピボットでは簡単にはできませんでした。

・何より、最大行数が65,536件という制限は、中小企業でも流通業なら1年で100万件位の売上明細データはあり、昨年との比較など、とてもできるレベルではありませんでした。

EXCEL2013時点のピボット機能

Ÿ ・行レベルの階層は無制限となりました。大分類/中分類/小分類/商品コード/商品名と言った分析が問題なく可能となりました。

Ÿ・列レベルも無制限です。地区別/県別/担当者別の売上集計とか、支店別/課別/担当者別とか、自由にドリルダウン分析できるようになりました。

Ÿ・集計項目も何種類でも無制限です。数量、売上金額、値引金額、返品金額を1つのシート上で集計できます。

・Ÿフィルター機能もピボット上で可能となりましたので、1支店のみをみたいとか、1商品のみの時系列での売上状況を見たいとか、その場で、フィルター内容を自由に変えられます。、今までのように帳票に出力して会議参加者全員に配布する必要は無く、プロジェクター、または参加者のパソコンに表示させることで、ペーパーレスで、なおかつその場で問題点を分析できる会議に変貌します。

なお、フィルター機能とは別に、スライサー機能という機能によっても、データを抽出できます。これもかなり便利な機能です。

・Ÿ複雑な計算が1クリックで可能となりました。この機能は、別項目で説明します。

・Ÿ処理件数は、最大行数 1,048,576件、最大列数 16,384列で、こちらはEXCEL2007以降、変化はありません。

但し、EXCEL2013 以降、ProPlus版というプロフェッショナル向けの版でPower BIというBI機能を持ったEXCELが提供されるようになりましたが、この版では最大行数、最大列数、共にメモリがある限り無制限です。

値フィールドの設定(計算の種類)

ピボットの「Σ値」欄に設定した集計項目には計算オプションが用意されており、EXCEL関数を使用することなく、様々な計算ができるようになっています。

この計算オプションを利用すると、例えば商品の売上ABC分析が関数を利用しないで、この計算オペションだけでピボット上に実現できます。

又、前年比も計算オプションで計算できますので、前年度との日別売上対比などの資料は、EXCEL関数無しで作成できます。

EXCELのPower BI機能

EXCELのPower BI機能はOffice Professional Plus 2010から部分的に利用できるようになりましたが、Office365 ProPlusでは全機能が利用できます。

今も姉妹品のPower BI Desktop共々、日々進化を続けております。

詳細は別のコラムで紹介しますが、今までのEXCELの弱点をカバーし、それ以上に「データの見える化」の概念を根本から変えるだけの力を持ったツールになって来ました。

今までならEXCELでは本格的なデータ分析は難しいと考え、他の様々な 高額なBIツールを導入してきましたが、一般ユーザーならEXCELだけで十分なデータ分析、見える化が実現できます。

まずは試して見ましょう。

経営システム 相談コーナー

私のところへ相談にこられた案件をまとめてみました。こんな経営システム全般に関する悩みをお持ちの方、解決するお手伝いをします。

①当社のシステムは、効果が上がっているようには見えない。何が悪いのだろうか?

②当社の情報システムは、毎月の運用コストが高くて大変だ。もっとコスト削減したい。

③当社の情報システムは、個別に導入したためにまとまりがなく、重複する入力が多すぎる。

④在庫管理システムを導入したが、いっこうにシステムで在庫が把握できない。

⑤当社は、販売管理システム等が稼働しているが、販売分析等の情報活用ができない。

⑥月次決算処理が遅くて、翌月半ばにならないと決算数値が出てこない。

⑦キャッシュフローの把握のため、毎日、試算表がでないか。


①当社のシステムは、効果が上がっているようには見えない。何が悪いのだろうか?

まず、システム導入時に、システム導入による目的と目標、及び目標に対する投資効果を検討されたでしょうか。

目的や目標が明確に定義されていなければ、何を持って効果があったのか、判断のしようがありません。システム導入する前に目的を明確にし、システム導入によりどのような状態に会社をしたいのか、目標を数値で定義します。目標数値は、いつまでにクリアするのかも重要なキーポイントです。1年後なのか、3年後なのか、もっと先なのか。目標数値が明確になることにより、投資可能なコストの限界が見えてきます。

これらの目的、目標の設定がまさに「IT戦略」です。

企業では、経営上の大きな投資をする際、目的を定め、投資効果を明確にしなければ稟議決済は通らないはずです。しかし、多くの企業で、IT導入になるとこの当たり前な検討がされず、既存のシステムの導入時の反省も明確にされないまま、継続的な投資が行われています。
すでにITが稼働されている企業におきましても、まず「自社のITはどうあるべきか」を検討する「IT戦略」を策定してみませんか。

ご相談はこちらにどうぞ。

②当社の情報システムは、毎月の運用コストが高くて大変だ。もっとコスト削減したい。

まず、第一にIT導入時のコスト、及び導入後の運用コストを詳細に把握されていますでしょうか。
導入時のコストは稟議決済を取る必要もありますので詳細に把握されているケースが多いのですが、運用コストを詳細に管理されている中小企業は意外と少ないのが実情です。

ここでいう運用コストの把握は、総額ではなく、情報担当部門の人件費、ハードウェア、ソフトウェア、通信費、紙、磁気媒体等の諸経費まで含め、システム単位、回線単位等、かなり詳細に把握、時系列にコストの動きをチェックします。
コスト削減対策は、ここから開始します。現行コストを検討し、削減可能な代替案を提案していきます。

ご相談はこちらにどうぞ。

③当社の情報システムは、個別に導入したためにまとまりがなく、重複する入力が多すぎる。

実は、日本企業の約半分、50数%の企業がこの状態にあります。この課題は、中小企業のみならず上場企業でも多くの企業が抱えており、日本企業の「IT有効活用」の最大の課題と言っても過言ではありません。詳細は、「ひとくちコラム」の「IT経営」をご参照ください。
原因ははっきりしています。長期的なIT戦略を策定せず、場当たり的にシステム化を進めてきた結果として、販売、仕入、会計等のシステム間の連携がとれず、場合によっては同じ企業内でシステムごとにコード体系が異なるなど、信じられないシステムが稼働しています。上場企業でも例外ではありません。内部統制で数社の業務文書化を担当させていただきましたが、全体最適化されたシステムが稼働している企業は1社もありませんでした。
私が、企業内の情報部門責任者として総合情報化を推進したとき、最初に行ったのが、長期的なIT戦略の策定でした。どのようなシステム化を目指すか、目標となる「あるべき企業の理想型」をグランドデザインとして描き、まず5カ年の長期計画を詳細に策定しました。グランドデザインの実現には、さらなる長期計画が必要でしたが、第1次長期計画が実現した段階で見直しを行いました。
ITコーディネータ協会が提唱する「ITCプロセス」がまさにこの手法をガイドラインとしてまとめたものです。ITCプロセスを元に、実際にIT戦略の実現に成功した経験も踏まえお手伝いさせていただきますので、まず、今からどの方向に向かうべきか、IT戦略からやり直して見ませんか?

ご相談はこちらにどうぞ。

④在庫管理システムを導入したが、いっこうにシステムで在庫が把握できない。

中小企業では、在庫管理システムが稼働しているにもかかわらず、在庫管理ができていない企業は非常に多いと感じています。逆に、信頼できる在庫情報を保持している企業の方が少ないのでは無いでしょうか。
多くの原因は、在庫管理システム自体よりも「運用」にあります。
会計システムや給与システムは、経理担当者や人事担当者など、限られた担当者のみが利用しますが、在庫管理システムは、販売、仕入のシステムと連動し、営業、購買、倉庫担当者等が、商品の動きに連動して入荷、出荷、移動、廃棄等の処理をしなければなりません。忙しいのでとりあえず商品を動かし、後で登録、と思いながらシステムへの登録を忘れてしまった、などの事象が発生すれば在庫は合わなくなります。現場担当者は、在庫が合わないケースを見つけると、1品合わないだけでも「この在庫管理は、使い物にならない」と無視するようになります。
自分自身に原因があることを理解しないで、、、。
このように、在庫管理システムは、商品、原材料等に関係する全担当者が関わりますので、これらの方すべてが決められた通りにシステム登録しない限り「使い物」になりません。一部の担当者のみが利用する財務会計システムや給与システムと異なり、連携する全部門の担当者がルールに基づいて運用しなければなりません。
現場にしてみれば、「忙しい中で一々システムに登録していられない」という現実もありますので、システム開発側も現場で登録可能な手法を模索し、システム化を図らねばならないことは当然です。

在庫管理こそ、関係する各部門が協議し、在庫管理方法を同意した上で、全員がルールを守る、これができないとシステムは活用できません。

もう1点、在庫管理で重要な点があります。

在庫保管場所の管理です。まず、5S(整理・整頓・清掃・清潔・躾)が大変重要です。

ITの導入だけで在庫管理はできません。在庫管理システムで、「10個」あると表示されても、その商品がどの場所に、どのような状態で保管されているかわからなければ、役に立ちません。
この当たり前のことができていない中小企業が意外と多いという現実があります。
私が、コンサルティングに入る際、最初に拝見させていただくのが、倉庫です。正直、倉庫を見れば、その企業の管理レベル、企業文化がほぼわかります。そして始めるのが、「5S」です。私自身も作業服を着て、現場担当者と一緒に倉庫整理をします。
ある企業では、ほとんど人が入らなくなってしまった返品在庫の倉庫がありました。最初は倉庫の奥まで人が入れないほど、商品が雑然と置かれていました。ここから、まずゴミと化した廃棄物を廃棄し、きれいに清掃した上で在庫場所を商品分類ごとに決め、棚割管理しました。この作業は社員全員、毎週1回、時間を決めて行いました。2ヶ月かかりましたが、社員に「やればできるんだ」という意識が芽生えてきて、社員の仕事に対する意識自体が変わってきました。在庫管理でも、システムは、「IT」と「業務革新」の両方が実現して初めて成果が現れます。

ご相談はこちらにどうぞ。

⑤当社は、販売管理システム等が稼働しているが、販売分析等の情報活用ができない。

一般的に、パッケージシステムとして市販されている販売システムには、様々な販売分析帳票が揃っていますが、ほとんど利用されていない企業が目立ちます。以前、パッケージシステムの管理帳票と言えば支店別、担当者別といった集計表が一般的でしたが、たとえば、最近の「勘定奉行」の管理帳票は、推移表や対比表などの帳票が用意され、中身は充実してきました。
問題は、中小企業の場合、担当者に時間的余裕がなく、情報を分析するだけの時間が取れないことにあります。しかし、たとえば、販売分析を行うことにより、欠品、機会損失を抑えた「効果的」な発注作業を「効率的」に行うことが可能となります。
分析では、「比較する」と「流れ(推移)を見る」ことに主眼をおいています。何と比較するか。いろいろな因子で比較することにより、その違いから現在の数値を分析していきます。
例えば、昨年との比較、先週との比較、天候や気温の違い、イベント、社会情勢の違い等。パッケージシステムで出力した売上集計だけとにらめっこしても何も出てきません。
また、もう1つの「流れを読む」は、「時系列」の考え方です。

月とか週とかの比較では、そのときだけの比較なってしまい、その後、どう推移するかが見えにくくなってしまいます。今から売上は上がっていくのか、下がっていくのか、またどの程度の上げ下げが予測されるか、これらを予測するのに、「時系列」に数値を見ることは重要です。
チェーンストアでは、1年間を52週の週別管理する手法が一般的になっていますが、他業種でも、週別管理が有効な業種は多いと思います。ぜひ、試してみることをお奨めします。

ご相談はこちらにどうぞ。

⑥月次決算処理が遅くて、翌月半ばにならないと決算数値が出てこない。

月次決算が月半ばにならないと出てこない企業は、中小企業のみならず、中堅企業でも非常の多いと感じています。

翌月15日過ぎに出力されたのでは、前月に発生した課題を当月で解決、予算の未達分を取り戻すことは難しくなります。取り戻そうとしても、対策を立てて実際に対策実施するのは10日くらいしか残されていません。
前月の結果に対する対策を有効に講じるためには、まずスピードが重要です。
翌月半ばにならないと決戦数値が出力できない企業に多いのが、正確な決算数値を出そうとするあまり、すべての請求書が処理されるまで月次決算を待ってしまうことにあります。期末決算は、確かに正確な決算を行わねばなりませんが、月次決算は何のために行うのでしょうか。中小企業にとって、月次決算は税務署に申告する必要もありませんので、経営状況を把握し、期末に向けて予算を達成するための課題を洗い出し、対策を講じるために行うのではないでしょうか。とすれば、1円まで合わせることに時間を費やすよりも、1日も早く結果を出し、対策を講じることの方が重要ではないでしょうか。
決算数値が遅い企業の経営者の方は、自社の決算数値がどのようなプロセスを経て作成されているかまではあまり関心が少ないようです。しかし、1日早く出力されれば、それだけ早く対策を講じることができるのです。一度、月次決算のプロセスをチェックして見てください。

ご相談はこちらにどうぞ。

⑦キャッシュフローの把握のため、毎日、試算表がでないか。

昔、勤務していた製薬メーカーでは、35年前にすでに毎日試算表を出力することを求められ、売上、仕入、経費等の情報が会計システムに連動する仕組みを構築しました。その後、開発型ベンチャー企業で財務責任者をしていた頃は、資金繰りのために、やはり毎日、BS(バランスシート)の出力が欠かせませんでした。

日本が高度成長期には、中小企業でも比較的資金繰りに困らず、どんぶり勘定でも何とかなっていたと思いますが、昨今の日本では、そんな悠長なことは言っていられないはずです。しかし、いわゆるキャッシュフローをリアルタイムに近い形で把握できる中小企業は非常に少ないと思います。
これを実現するためには、単に会計システムだけの問題では解決しません。売掛、買掛、未払、これらが発生するもととなる売上、仕入、経費等が日々、確実に把握できて初めて日々の資金繰りを管理することが可能となります。
多くの中小企業では、売上は販売管理システムで、仕入は購買管理システムで管理していますが、会計システムに日次で連携しておりませんので、月次で合計売上高、合計仕入高を仕訳入力しています。これでは、日々、試算表を出力し、資金繰りに生かすことはできません。

ご相談はこちらにどうぞ。

HOME / セミナーのお知らせ / ひとくちコラム / サービス / ITお悩み相談 / 会社概要 / お問い合わせ / リンク / 利用規約

Copyright © 2010 IWATA SYSTEM CONSULTANT, All Rights Reserved.

iphone6ケース ルイヴィトン iphone6ケース シャネル 手帳型 シャネル iphone6ケース タバコ ブランドスマホケース