松田軽太の「ひとり情シスのすゝめ」は、ひとり情シスを推奨しているのではなく、様々な事情によりやむなく”ひとり情シス/ゼロ情シス”という状況になってしまっても頑張っていらっしゃる皆様のお役に立つような記事をお届けいたします。

今回はExcelでの集計を秒殺する方法。脱Excelなどと言っても実際はゼロにすることはなかなか難しく、大企業や勢いのある中小企業ではRPAが導入されていますが、まだまだ経営層のRPAへの理解が少なく、Excelを使い、多くの集計作業を行っている方も多いのではないでしょうか。しかしながら「昔から代々使われてきてはいるが、毎回行うExcelでの集計作業はやはり面倒くさい」というのも事実。

そこで今回は定期的に行なう集計をVLOOKUPとSUMIFの2つの関数で秒殺する方法をご紹介します。

Excelで行う定期的な集計をVLOOKUPとSUMIFの2つの関数で秒殺する

Tipsをご紹介する前になぜExcel集計が秘湯率なのかを見ていくことにしましょう。

なぜ昔から使われているExcel集計は非効率的なのか？

多くの会社にExcelが普及したのは2004年辺りではないでしょうか？その頃に行われた「仕事のデジタル化」は手入力で打ち込みしていたワープロ的な使い方からExcelのオートSUMなどでの簡単な集計機能で自動的に集計したというところでしょう。
ワープロ的な使い方の頃は、紙で印刷された別の集計表を電卓で叩いて集計し、その集計結果を手打ちしていたのでしょうから、その部分がオートSUMで自動集計されただけでも、当時はかなり業務効率が上がったのでしょう。

しかし、現在は2019年。DX（デジタルトランスフォーメーション）が叫ばれている真っ只中です。（だからと言って、今回はExcelのTipsをご紹介するのですが。笑）

それらの第一次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は覚えておきましょう。

 

※本記事は松田軽太様許諾の元、「松田軽太のブロぐる」の記事をベースに再編集しております。

---

松田軽太（まつだ・けいた）

とある企業に勤務する現役情シス。会社の中では「何をしているのかナゾな職場」でもある情シス業務についてのTipsや基礎知識などを紹介する。

ブログ『松田軽太のブロぐる』を運営。