【めざせExcelマスターへの道】扱いやすいデータの基準とは - 出世はあきらめて30代から手に職をつけてみる

こんにちは。ヒトツメです。

1年ほど前、総務省が「統計表における機械判読可能なデータの表記方法の統一ルールの策定」というものを発表しました。お役所などでよく見かける、いわゆる「悪いExcelの使い方」を正すことを目的としたもので、河野行政改革担当大臣（当時）が、自身のTwitterで取り上げたことでも話題になりました。

総務省｜報道資料｜統計表における機械判読可能なデータの表記方法の統一ルールの策定

総務省では、各府省が政府統計の総合窓口（e-Stat）に掲載する統計表における機械判読可能なデータの表記方法の統一ルールを策定いたしましたので、お知らせいたします。

しかしながら、「機械判読可能」ということは、場合によっては人間にとっては見にくいものです。
こういったルールが定められていても、決して強制力があるわけではないので、キレイとは言い難いデータというものは、たくさんあったりします。

良いデータとは何か
OFFSETで解決
- 戻り値は「配列」
- 一つ飛ばしを表現する
配列を拡張する
さいごに

良いデータとは何か

今日のテーマは、ずばり「良いデータ」とはどのようなデータか、ということについて少し考えたいと思います。最後に、「良いデータ」とは言えないデータを関数で「良いデータ」に変換する方法についても解説していきたいと思います。

ピボットテーブルが楽

僕が思う「良いデータ」とはどのようなデータかというと、ズバリ「ピボットテーブルが楽」なデータです。それがすべてというわけではありませんが、少なくとも、一つの指標であるべきであると考えています。

計数などを把握するにあたって、上記のような、月ごとの売上と受注件数を記載したデータを扱うことがあると思います。
これでも十分、「良いデータ」であるように思います。セルの結合はされていないので、オートフィルタ―も使いやすいですし、内容や形式に関して、統一感もあり、非常に扱いやすいデータです（以降、①のデータと呼ぶことにします）。

しかしながら、このようなデータは、ピボットテーブルの扱いやすさ、という意味でいえば、次のようなデータに劣ります（以降、②のデータと呼ぶことにします）。

①のデータに関して、ピボットテーブルを作ろうとすると、月が書いてある部分は無視されて作成されてしまい、項目名と内容が不一致になってしまいます。「4月売上」といった項目名にするのも、一つの手ですが、類似の項目が、別の列にあると、ピボットテーブルを使って、例えば「1年間の売上」を出すことは非常に難しいです。

上のデータでピボットテーブルを作ろうとすると、項目名がわかりにくくなってしまったり、複数の列にまたがる売上の総合計を集計することが難しい

これに対して、②のデータの場合、売上は「売上」という列にすべてまとまっています。ピボットテーブルを活用することで、特定の月の売上だけを集計したり、年間の売上を集計したり、自由度が高まります。

継ぎ足しがしやすい

「それだけ？」と感じる方も多くいると思いますが、このようなデータのちょっとした差は、色々な場所で大きな差を生み出します。
例えば、これらのデータに6月の売上と受注件数を足しこむとします。そしてその時、「有限会社C」との契約関係が終了しており、6月以降、「有限会社C」の行が削除されているとします。そうした場合、①のデータに足しこむとすると、6月の売上が記載されたデータの「B商店」と「合同会社D」との間に、わざわざ行を挿入して、間に空白を作ったうえで右側にデータを追加しなければなりません。
1件のみ・1回だけであれば、大した労力ではありませんが、毎月そのような作業をし続けると考えると、かなりの作業時間になります。取引先が多くて、行の挿入・削除が増えてくると、かなりの作業量になります。

②のようなデータの場合、たとえ同じような事象が起きていたとしても、単に下にデータを継ぎ足すだけで終わります。

切り出しがしやすい

また、②のようなデータの場合、「単に売上だけを比較したグラフを作りたい」となったとしても、「会社ごと・月ごと」の「売上のみのデータ」を作るために、必要な操作は非常に限定的です。

ピボットテーブル、もしくはSUMIFSなどを用いて、会社ごと・月ごとの売上データを作成
作成したデータに対して、グラフを作成

といったところかと思います。
慣れている人であれば、数秒で完結します。

しかしながら、①のデータのような場合、1行おきに記載されている売上データだけを切り出すというのは、意外と骨が折れます。
どんなに慣れていても、数分は少なくともかかります。
ピボットテーブルを使えば、多少は楽ですが、それでも②のようなデータを加工することに比べれば、かなり時間がかかると思います。

OFFSETで解決

以上を踏まえて、問題は、どのようにして①のデータを②のデータに変換するかですが、この時、OFFSET関数を使うと解決することが多いです。

戻り値は「配列」

OFFSET関数の構文は下記のとおりです。

=OFFSET(参照, 行数, 列数, [高さ], [幅])

この時、参照には、セルの名前（例えばA1など）を入れますが、OFFSET関数の最大の特徴は、4つ目と5つ目の引数に、2以上の数字を入れることで、「配列」が返却される、ということです。

例えば、次のような式を入れた場合、その内容は、「A1:B2」と同じになります。

=OFFSET(A1,0,0,2,2)

なので、「=SUM(OFFSET(A1,0,0,2,2))」は、「=SUM(A1:B2)」と同じということになります。

一つ飛ばしを表現する

OFFSET関数の戻り値が配列であることを使った活用方法は後程説明するとして、もう一つOFFSET関数の便利な特徴は、「○○飛ばし」を表現しやすいということです。
セルの参照を相対参照にしていようと絶対参照にしていようと、参照しに行くセルについては、式をコピーする先の位置以上に動かすことは、通常できません。しかしながら、OFFSET関数は、参照位置からどの行数分（あるいはどの列数分）動いた先を参照しに行くかを選択することができます。

したがって、次のような記載をすることによって、この式を一つ横にコピーすることで、二つ横の参照位置を参照することができるわけです。

=OFFSET($A$1,0,(COLUMN(A1)-1)*2)

このとき、COLUMN(A1)は、A1の列番号、つまり1を示しており、「(COLUMN(A1)-1)*2」は「0」になります。
ただこれは、相対参照になっているので、この式を一つ横にコピーすると、この部分は、「(COLUMN(B1)-1)*2」となります。この時の、この式の結果は、「COLUMN(B1)」は「2」なので、「（(2-1)×2で）2」となります。

このようにOFFSETを使うことで、売上が一つ飛ばしに入力されていても、売上の部分だけが入力されたデータを簡単に作成することができます。
このように、OFFSETは、少し癖のあるデータでも、規則的な癖であれば、その癖をうまく把握することで、画一的で扱いやすいデータに変換させたり、うまくデータを切り出したりすることができるわけです。

このような関数を使うことで、①のデータを②のデータに変換することが可能となるわけです。

配列を拡張する

ついでのような書き方になってしまいますが、OFFSETの特徴、「配列を返却する」という特徴の活用方法についても少し解説をしたいと思います。このとき、一番わかりやすいのは、「一番下に合計行をつける場合」です。

通常、一番下に合計行をつける場合、SUM関数を用いますが、合計行をつけるデータの行数が、毎回違う場合、SUMの中に入れるセルの範囲を毎回変える必要があります。
しかしながら、OFFSET関数を使えば、合計行を入れる場所によって、参照しに行く配列を拡張させるということができるので、どんな場所にも入れられる合計行用の関数を作ることができます。

=SUM(OFFSET($A$1,0,0,ROW([関数を入れる場所の一つ上のセル]),1))

このように記載することで、SUMの中に含まれる参照範囲が、下に行くにつれて徐々に拡張されていき、一番上からセルのすぐ上のセルまで全体を参照することができます。