VLOOKUPとSUMIFSで事業分析 - 中小企業がクラウドで戦える。いい時代だなあと思える人に捧げるブログ

かーみーです。

今は中小企業がクラウドによって大手と渡り合えるようになった素晴らしい時代。

これからの方に参考になればとの備忘録。

前回まで請求書管理ツール的な結構マニアックなテーマを扱ったのでもう少し軽いやつを。みんなが大好きエクセルだ。

私もエクセルラブなので、MacBookにVMWAREでWindowsを積んでいる。Macのアホみたいに遅いエクセルはエクセルでないと思っている。

でも、エクセルも良いが時代はクラウドで、クラウドといえばGoogleである。

個人は無料だ。企業でも数百円。しかもSpreadSheetのほとんどの関数はエクセル同様ときている。こいつを使わないのはかなりもったいない。なのでエクセルでもスプレッドシートでも使えるテーマで書こうと思う。

世間ではExcel（スプレッドシート）初級・中級・上級という言葉がある。

概ね、「VLOOKUPができれば中級！」と言われるのが一般的だが、売上等を集計する仕事をするのであれば、「ピボットテーブル」「SUMIFS」系を覚えておくと、およそ欲しいデータは作れると思う。

その先にはマクロとかVBとかあるが、そこは私は良く分からないので、他のブログで調べると良いだろう。きっとすごいんだろうと思いつつ・・・

まずはそれぞれの関数の特徴はこんな感じ。

もう説明する必要もないほど有名な関数。

複数列で構成されたデータ項目から、特定の列でデータを引っ張る関数だ。

ちょっと世代が古いが、スキーでいうとパラレルターンにあたる。これが自由に使いこなせれば「お、少しはエクセル使えんじゃん」と言われる。

データ集計の現場では、集計を行うための「キレイなデータ」を作るために、マスタと組み合わせてデータをクレンジングする際に使える。ちょっとマニアックだがIFERRORとかと組み合わせるとより能力を発揮する。

後工程の分析を考慮してない基幹システムでは、クソなデータしか吐き出さないケースが往々にしてある。そんなデータを分析可能なレベルまでサマる時にも重宝する。

結構マニアックだが、HLOOKUPという兄弟がいる。勘が鋭い人は分かると思うが、VLOOKUPのVはバーティカル、HLOOKUPのHはホリゾンタルを意味している。行で検索するか、列で検索するかの違いだけ。

ただしデータはフツーは新しい行がどんどん増えるが列は固定のケースが多い。

Excel 2010 でもワークシートの最大サイズは 1048576 行× 16384 列なので、データは縦に広がるのが一般的。これがVLOOKUPとHLOOKUPの人生を分けた物と思われる。

複数の条件付きSUMである。要は「この条件で足し上げろ」というやつ。イケてるのは複数条件が設定できることだ。「営業部門Aの商品Bについて、C月分だけ売上を足し上げろ」的な事ができる。兄弟にCOUNTIFとかがある。

こいつが自由に使えると、P/Lの部門・科目で切った月毎集計が一撃で出来るようになる。一つの式を作ればコピペで一気に部門・科目・月毎で集計できる。

弱点は、集計行・列の要素をMECEで捉えないと、部分の合計が全体に一致しない。このあたりはピボットテーブルのチカラを借りると良い。MECEはマッキンゼーとか由来の言葉で有名なので、分からない人はググると良い。

ある表を、指定の行列で自由に集計できる機能。データさえ揃えば自由な分析が可能だが、いかんせん「データがキレイでないと」思う通りの結果が出ない。

上記のような理由から、元データをマスタとVLOOKUPでクレンジングをかけ、ピボットをかけて使ったりする。

ピボットのもう一つの特徴は、データ項目をユニークにできる事だ。SUMIFSは集計には万能な関数だが、要素が完璧にMECEでないと全体像がずれるリスクがある。ピボットを使って項目がMECEか検算することができる。

弱点としては、データ項目が増えると必ず再計算が必要な事だ。なのでデータが日々増え続けるような動的な表の集計には向かない。

静的なデータで構成されるエクセル時代には強力だったが、動的なスプレッドシート時代に入った今、ちょびっと物足りなかったりもする、強力だがスピード感には欠ける機能である。ちなみにピボットテーブルだけは関数ではない。

ざっくりと、上記3つくらいの概念が入っていれば、大体のデータは集計できるようになる。

次回はこの3つを組み合わせて集計を行う基本的な手法について説明してみようかと。