多くの仕事でExcelは行われていますが、最近、よく見かけるのは「昔から代々使われているExcelでの集計作業が面倒くさい」という悲鳴です。
そこで今回は定期的に行なう集計をVLOOKUPとSUMIFの2つの関数で秒殺する方法をご紹介します。
こんにちは！ 松田軽太です。
なぜ昔から使われているExcel集計は非効率的なのか？
多くの会社にExcelが普及したのは2004年辺りではないでしょうか？
その頃に行われた「仕事のデジタル化」は手入力で打ち込みしていたワープロ的な使い方がExcelのオートSUMなどでの簡単な集計機能で自動的に集計したというところでしょう。
ワープロ的な使い方の頃は、紙で印刷された別の集計表を電卓で叩いて集計し、その集計結果を手打ちしていたのでしょうから、その部分がオートSUMで自動集計されただけでも、当時はかなり業務効率が上がったのでしょう。
しかし今は2019年です。
それらの第一次Excel導入期が2004年頃だとすると、既に15年が経っています。
実は多くの会社で15年前に考えたExcelの使い方が、そのまま一子相伝で伝承されているのです。
要するに アナタのExcelの使い方が時代遅れ になっているワケです。
今までのExcelでの作業手順はこんな感じ
よく見かけるのが「集計したいデータをフィルターで絞り込みして、合計数を隣の列に貼り付けて集計表を作成する」といった作業です。
【例題】売上実績データを商品群別に集計する
従来の作業手順を順にみていきしょう。
①販売システムから売上データをExcel出力してみました。
②商品群別に集計します。
その際、集計しやすくするために商品名を色づけしておきます。
③オートフィルタで集計したい商品群を絞り込みします。
④オートSUMで集計します。
⑤この作業を商品群ごとに繰り返すと作業が完了します。
この作業手順でも電卓を必死に叩いて集計するよりは全然マシですね。
でも集計作業を人が操作しなければなりません。
Excelでの作業自動化では、これらの集計作業を関数で自動的に行えるように仕組み化します。
Excelを仕組み化する考え方
Excelを仕組み化するとはどういうことでしょうか？
Excelを自動的に集計するためには、Excelが集計しやすいデータのカタチに整理してあげるのです。
整理する分類は
「集計元データ（トランザクション）」
「集計する基準（マスタ）」
「集計表（サマリー）」
の３つに分けます。
具体的に例を挙げてみます。
Excelに以下の2つのデータを用意します。
・売上実績データ
・商品マスタ
集計作業を秒殺するには「集計表（アウトプット）」の作り方が肝心なのです。
今回の集計ではVLOOKUPとSUMIFSという関数を利用します。
では、実際にExcelのブック（ファイル）を作っていきましよう。
先ほどの「売上データ」「商品マスタ」の2つのSheetを作成します。
●売上データの項目
伝票No.、計上日、商品コード、商品名、得意先コード、得意先名
数量、単価、金額
（この項目は手入力せずに販売システムからダウンロードしたデータを貼り付けるという想定です）
商品群別の集計項目として 商品群名、金額 を用意する
●商品マスタ
商品コード、商品名、商品群名
自動化するために関数を設定する
今回の自動集計では VLOOKUP関数とSUMIF関数を設定します。
①VLOOKUP関数の設定方法
「商品群名」という項目を新たに設けて、商品マスタから取得します。
=VLOOKUP(E4,商品マスタ!B:D,3,0)
②SUMIF関数の設定方法
SUMIF関数があなたの代わりに集計作業を自動的にやってくれるのです。
ここの仕掛けが非常に重要です！
「商品群別売上集計」という項目を設けて、商品群名欄に「筆記用具」「ノート類」「メモ帳」という入力します。
そして金額欄を設けてを下記のSUMIF関数を設定します。
=SUMIF(L:L,O5,K:K)
では実際の操作して動画をみてみましょう。
売上データを貼り付けただけで集計作業が終わります。
作業イメージは販売システムから出力したデータを売上データSheetに貼り付ける
という内容です。
このように貼り付けた瞬間に集計が完了します。
たったこれだけのことで、毎回、アナタの手を煩わせていた作業が自動化できるのです。
ほかの集計作業でも基本的は考え方は同じようになので応用できます。
自動化のポイントはデータの流れ
作業の自動化のポイントは『いかにデータの流れを滞りなく作れるか』を考えることです。
それはまるでボールコースターを作るのに似ています。
ボールコースターの道中に何を仕掛けるかを考えるのです。
そう考えると「仕事の仕組み化」を考えることが楽しくなってきますよ。
そのためのアイテムが関数なので、どの関数がどいう動きをするのかを知っておくことも大切です。
まずはVOOKUPとSUMIFは覚えておきましょう。
このようなExcel作業の秒殺方法をもっと知りたい方は羽毛田 睦土氏の著書『Excel 最高の学び方』でたくさん紹介されていますので参考にしてください。
関数は「使える順」に極めよう! Excel 最高の学び方 (できるビジネス)
- 作者: 羽毛田睦土
- 出版社/メーカー: インプレス
- 発売日: 2018/03/09
- メディア: 単行本（ソフトカバー）
- この商品を含むブログを見る
