自社データやオープンデータを、Tableauなどの他のBIツール・分析ソフトで活用したいというニーズはたくさんあります。
しかし、実際には、なかなかデータ活用が進みません。
その原因の一つは、実はExcelの使い方にあります。
企業や政府官公庁で所持しているデータの相当部分が、Excelで管理されています。しかし、それらは分析ソフトからうまく読み込みできなかったり、データ自体の精度が低かったりするのです。
そこで、今回の記事では、データ活用をするためのExcelの基本ルールをお伝えします。この記事の内容を知っていると、圧倒的に業務効率化が進みます。
データ分析にかかる時間が5分の１になるといっても過言ではありません。
データ分析を手掛けたい人にとっては、最初に知っておくべきもっとも大切な内容です。
Excelの基本ルールでデータ分析が5倍効率化する理由
データクレンジングの必要性
Excelの基本ルールを押さえるだけで、どうしてデータ分析が5倍も効率化できるのでしょうか。
データ分析の世界では、データクレンジングやETLと呼ばれるデータ前処理に、全体の8割ぐらいの時間がかかるというのが定説です。
たとえば食品工場では、カットする前に野菜を洗い、同じ大きさに切らないといけません。同様に、データ分析を行うためには、データをまず洗ったり、同じ形式に揃えたりする必要があります。
特にExcelで作られたデータの多くは、ほぼそのままでは使えません。専用のデータベースソフトとは違って、Excelはあまりにも自由だからです。Excelの中では、どのセルに何を入力しても、エラーが出ることはありません。
しかし、それでは見る人間には解釈できても、他のソフト（特に他の分析ソフト）はうまく読みこめないのです。
データクレンジングを業務効率化する2つの方法
データクレンジングを業務効率化するには、大きく分けて2つの方法があります。
①データクレンジングの自動化
1つは、データクレンジングを効率化するツールを使用する方法です。TableauならばTableau Prepでも可能ですし、Excel本体ではVBAやPowerQueryを利用することもできます。これらのツールでは、ひとたび処理の流れを構築すれば、クレンジング業務を自動化することができます。
ただし、この方法では、煩雑なプログラミングが必要です。
また、場合によっては、どうしても手作業で編集しないといけない場合も出てきます。
そのため、データクレンジングの自動化は、あくまで次善策だと考える方が良いでしょう。
②入力ルールを徹底し、クレンジングを不要にする
それよりも、データクレンジングが不要なように、Excelの基本ルールを徹底することが大切です。そうすることで、データ分析業務の8割を省略できるのです。
実は、これはデータ分析の実務に携わっている人は当たり前に行っていることです。しかし、実際に企業内でExcelを使用している人の多くは、データベースについての基礎知識がないため、これらのルールが広く共有されていません
実際、政府官公庁においても、その基礎知識の欠如がデータ活用の大きな足かせとなっていました。政府が国民に公表している各種統計データ（オープンデータ）の多くで、デタラメなExcelの使われ方がされていたのです。
ちなみに総務省は2020年、政府官公庁向けに統計データのための統一ルールを公表しました。
統計表における機械判読可能なデータの表記方法の統一ルールの策定
今回の記事も、内容は少し異なりますが、主旨はおおむね同じですので、あわせてご参照ください。
Excelのデータベース形式の原則
データベース形式の目的は、分析ソフトにデータを正しく読みこませること
Excelを元データとして使用する際の形式（以下データベース形式）で作る目的は、「分析ソフトにデータを正しく読みこませること」です。
データベース形式というと、非常に複雑に聞こえるかもしれませんが、実はとても簡単です。
まずは正しいデータベース形式の例をお見せします。
とても簡単ですね。シンプルだから、機械が迷いなく読める、そう考えてください。
さて、データベース形式の原則は3つです。
原則① 表頭は1行目のみ
表の一番上にある項目名（オーダー日・製品IDなど）の行を「表頭」と呼びますが、データベース形式では表頭はExcelの1行目のA列から配置してください。
また、よく複数行にまたがって表頭を作っている場合がありますが、それも可能な限り避けてください。
表頭は1行目の1行のみです。
原則② 1行1レコード・1セル1データ
2行目からは、実際のデータが書き込まれています。
データが書き込まれた1つの行のことを「レコード」と呼びます。これが一つのデータのかたまりだと考えてください。
1行に1レコードが入力されます。
また、1つのセルには1つのデータだけが入力されます。
原則③ 同じ列は同じ形式
ある列のことを「項目」と呼びます。同じ項目では、すべての行で同じ形式のデータを入れてください。
同じ形式とは、データ型（文字列や数値・日付）が同じというだけではありません。たとえば電話番号の項目ではハイフンを入れるか入れないか、氏名の間に空白を空けるか空けないか、ということも含めて、可能な限り形式を統一することが原則です。
Excelデータベース形式でやってはいけないこと
表頭は1行目・1行1レコード（1セル1データ）・同じ列は同じ形式、このシンプルな3つの原則について述べました。
以上の原則は、機械が間違いなくデータベースを解釈するために必要です。
この3つの原則から簡単に導きだせる、「Excelデータベース形式でやってはいけないこと」を挙げていきます。
シートの真ん中から表がはじまる
データベース形式では、表は左上A1セルから始まるのが鉄則です。
ところが、多くの表では、たとえば表タイトルの下から表が始まることが多いですが、可能な限り避けましょう。
シートの最初からデータが始まらなければ、分析ツールが、シートのどこからどこまでを読み取れば良いのか判断できない場合があるからです。
どうしてもどこかにタイトルを入れたいときは、シート名にタイトルを設定しましょう。
表頭が複数行にまたがる
次に、よくある間違いですが、複数行の表頭がある表です。
分析ソフトは色や書式設定までは解読しないため、人間には容易にわかる意味を、正確に把握することが困難です。たとえば、下の表では、売上の2021年と利益の2021年を区別することができません。一例ですが、「売上2021年」や「利益2022年」と1行にまとめるか、年度は一つの項目にするのがベターです。
1シートに複数の表がある
Excelの一つのシートには、複数の表は入れないでください。
たとえば、下のように複数の表を同じシート内に入れると、分析ソフトがどこからどこまでが同じ表なのか判断がつかず、データを読み間違える可能性が高くなります。
セル結合
「セル結合を使うな」という警句は聞いたことがある人は多いでしょう。
データベースとして使うときにも、セル結合は禁物です。そもそも、セル結合すると、「1セル1レコードの表」ではなくなります。したがって分析ソフト側でも、正しく読みこまれなくなります。
1セルに複数データ
原則②でも述べましたが、1セルにつき1データです。次のように、同じ項目に複数のデータが入っているものは避けてください。
このような場合、売上の項目と利益の項目は、必ず分けましょう。
たとえば、こういうカテゴリ表も、データとしては使えません。
冗長に見えるかもしれませんが、必ず次のように1セル1データに整形してください。
項目の省略
何度も同じ項目が繰り返されると、つい冗長に感じるため、省略したくなります。しかし、読むのは人間ではなく、ソフトです。省略すると、正しくデータが読みこめなくなります。
ソフトウェアは、データを行単位（レコード単位）で読みこみます。上の表をソフトウェアが読むと、「サバ」は該当するカテゴリが存在しないと解釈されます。
同様に、「同じ」を意味する記号を使うのも避けましょう。下の表だと、「サバ」のカテゴリは「〃」になってしまいます。
セル内の余分な空白・文字・改行
セルの中には余分な空白や文字を入れないようにしてください。
たとえば、見た目の体裁を整えるために、空白を入れないでください。
上の表では「捕手」「投手」の項目が、他の項目と見た目を合わせるために、「捕□手」「投□手」と、スペースが入っています。そうすると、たとえば分析ソフトで正しく表示されないだけではなく、他のデータとうまく結合できないという不具合が発生する可能性があります。
また、項目の中に注釈を入れるのも避けましょう。注釈・備考は、別の欄に分けて記入するのが望ましいです。
さらにセルの中に改行を入れると、分析ソフトによってはエラーが出る可能性がありますので、避けてください。
フォーマットの不統一・表記揺れ
データベース形式には、同じ列は同じフォーマットにするという原則があります
これには二つの理由があります。
①異なるフォーマットの場合、分析ソフト側で正しく読みこまれない。
たとえば、次の表を見てください。
2月・5月の「▲」は文字として入力されているため、分析ソフト側で正しく読みこまれない可能性が高いです。
②表記ゆれによって、同じものが別のデータとして解釈される
たとえば、下の表では、郵便番号がハイフンあり/なし、半角/全角など、統一されずに記入されています。
これだと①の読み込みエラーが起きる可能性の他に、「123-4567」「1234567」「１２３４５６７」がそれぞれ別のものとして解釈されます。
そのため、それらを同一のものとするためには、データクレンジング過程で、同じデータへと修正（名寄せ）する必要があります。そうした無駄な作業を減らすためにも、記入方法の統一を徹底してください。
集計行
Excelをデータベースとして使う場合、集計・小計行は入れないでください。
これは、あまり言われることがないのですが、データの問題を引き起こす可能性があります。
たとえば、下のようなExcel表を見てみましょう。
全データの売上合計は、合計行にあるとおり、392419円のはずです。
しかしこの表を、Tableau Desktopで読みこませると、売上合計は784838円になります。
なぜこのような問題が起きたかと言えば、本来はデータの一部ではない「集計」が、1つのレコードとして解釈されるからです。TableauはExcelの合計行を誤って解釈し、「合計」という名前の製品の売上が392419円あると理解してしまったのです。
それが売上が2倍になってしまった原因です。
こうしたデータ解釈の問題を避けるために、Excelのデータベース形式に集計行は入れないでください。
まとめ
ここまで、データ分析のための元データとしてExcelを使うときの「禁止事項」を解説してきました。
大切なことは、これらのルール一つ一つ覚えることではなく、考え方を理解することです。
上にあげた「禁止事項」の多くは、人間にとって読みやすい表を作ろうとするときに生じやすいものです。
たとえば、上の行の繰り返しを避けるためにセル結合や空白行を使う人がいます。また、マイナスが見にくいから、その代わりに「▲」を入れる人もいます。また、美しい表の体裁を保つために、データの中に空白を入れる人も出てくるわけです。
しかし、人間にとって読みやすい表は、機械にとっては読みにくい表なのです。
なので、「人間の読みやすさのために、データを改変するのはやめましょう」というのが、Excelデータベース形式の根底にある考え方なのです。
繰り返しになりますが、これらのルールはあくまでデータベース形式としてExcelを利用するときのルールです。すべてのExcelの使い方で、このルールを徹底する必要はありません。
データベース形式は、あくまでさまざまな用途で使えるExcelの、1つの使い方にすぎません。
たとえば、取引先に送る請求書に、「集計行」を省くことは不可能でしょう。また、業績レポートをExcelで作成するとき、一枚のシートに複数の表やグラフが入っていなければ、報告書としては非常に読みにくいものになってしまいます。
なので「禁止ルール」をがちがちに考える必要はありません。用途に合わせて、適宜使い分けていきましょう。
人間にとっての読みやすさと、機械にとっての読みやすさを両立させるためにはどうしたら良いのかについては、またいつか、別の記事でご紹介したいと思います。
コメント