プログラマが教える!効率的かつ利用しやすいエクセル資料の作り方

効率的かつ利用しやすい資料とはどんな資料でしょうか?
色々な意見があると思いますが、私は次のような性質を備えた資料のことだと思います。

  • データが入力しやすい
  • 資料として体裁を整える手間がかからない
  • 記入されたデータを利用しやすい

このような性質と真逆の存在があります。それは”ネ申エクセル”と呼ばれるものです。
「紙に印刷するためだけに作られた」エクセルファイルの意味なのですが、”紙”→”神”→”ネ申”という風に変化してこのように呼ばれるようになりました。

この記事ではネ申エクセルをリメイクする過程をお見せし、どうやって効率的かつ利用しやすい資料にしていけばよいのかをステップバイステップで示します。ですがその前にネ申エクセルの問題点について少しだけ話させてください。

ネ申エクセルの問題点

ネ申エクセルはそもそも運用が非生産的

ネ申エクセルは初めから以下のような運用を前提に作成されます。

  1. 印刷する
  2. 配布する
  3. 記入されたものを回収する
  4. 結果を(人間が1件ずつ)手作業で打ち込む

この運用の問題は「ツールが使えない」点にあります。例えば配布や回収にメールが使えません。印刷代や配布にかかる時間のコストはバカにできませんよね。またデータの入力も自動化できません。データの件数が増えればミスも起こりやすくなるので、それをチェックするためのコストもかかります。

それだけでなくネ申エクセルの存在は運用改善の障害になります。ネ申エクセルは「記入する人」「利用する人」の生産性を下げるので、それを避けるためにファイルを作り直すコストが必要になります。これを嫌って運用の改善が見送られれば、生産性の低い状態が放置されることになります。

例:ネ申エクセルはどうやって生産性を奪うのか?

ネ申エクセルのよくある例が、「エクセル方眼紙」と呼ばれるものです。これは下の画像のようにセルを正方形にして表をレイアウトする手法のことです。この手法を使うと簡単に表を作ることができるので、皆さんの職場にもこういったファイルがあるかもしれません。
excel_hoganshi

紙に印刷して使うだけなら問題の無いこの手法ですが、ひとたび電子データとして配布されると一気に職場の生産性を地に落とします。例えばこの「お名前」を別のエクセルにコピーしてみましょう。
name_copy
name_paste
(#^ω^)・・・
セルが結合され、実に残念な結果になりましたね。これを防ぐにはセルをダブルクリックして文字を全て選択しなければなりません。100個データがあったとしてもひとつひとつダブルクリックして、文字を選択して、コピーしなければならないのです。考えただけでもウンザリしますね。

またこの表はミスを起こしやすいです。例えばダブルクリックする時に1行飛ばしてしまうかもしれません。やっと全部コピーし終えて、データを数えてみたら1つ足りなかった・・・。なんて悪夢以外の何者でもありません。文字を選択する時に「先頭1文字だけ選択できていなかった」なんてのも気付きにくいミスの典型です。

ネ申エクセルは職場の生産性を下げるのです。ものすごーーーく。

ネ申エクセルが問題なのは分かった。じゃあどうすればいいの?

ここからが本題です。このようなネ申エクセルがあったとして、どうやってこれを改善すればよいのでしょうか?書類のレイアウトを簡単に変えることができるなら良いですが、職場によってはそれをするのに稟議が必要なこともあります。

そこで今回はこの資料をリメイクする過程を通して「資料のレイアウトを変えずにネ申エクセルを無くす」方法についてお教えします。
リンク先のファイルは、今月の2日に行われた東京都都議会議員選挙の投票結果です。開いてみたらお分かり頂けると思いますが、典型的なネ申エクセルです。
image2

リメイク手順

ステップ1.データ構造を把握する

まずは何はともあれ、データ構造の把握です。「データ構造」というと難しそうに聞こえますが、実際は割とすぐに理解できます。どういうことかというと・・・。

(頭の中の声)
どうやらこの資料はざっくり選挙区候補者のデータに分かれそうだなぁ。ただ「西多摩」地区のようにひとつの選挙区が複数の市区町村にまたがる場合もあるっぽい。候補者の得票市区町村毎に集計されている点も注目ポイントかな。

・・・と、このようにデータの特徴をピックアップするだけなんですね。コツはなるべく「名詞」に注目することです。(よい名詞が見つからない時は無理やり作ることもあります)慣れないうちは、考えたことを文字に起こすと無理なくできるでしょう。

では上記で太字にした名詞を基に資料の各要素を図にしてみましょう。

image3

ステップ2.データ用のシートを作成する

データ構造が把握できたら、次はデータシートの作成です。次の2点を考えながら、シートを作っていきます。

質問1:不要なデータはどれか?

例えば選挙区の法定得票数や供託物没収点といった値は選挙区の有効得票数が分かれば計算できますよね。同様に市区町村の残票数や開票率も不要です。こういったものはデータシートには入力しないようにします。
image4

質問2:どうすれば入力しやすくなるか?

データ入力が自動化できない場合、データシートが入力しやすいことはとても重要になってきます。作業手順が増えると手間もミスも増えてロクなことがありません。

今回の場合は得票の入力が大変そうですね。得票数を入力するのにいちいち候補者の情報を入力するのは手間ですから。こういった場合は、データをひとつにまとめてしまいましょう。
image5

ここまでの手順で最終的に要素は3つになりました。エクセルでデータを作るときは、「1シート1データ」が基本なので、最終的なシート数は3枚になります。

ステップ2.5.データをテーブル化する

これはちょっとしたコツのようなものですが、データシートのデータはテーブルにしておくとよいでしょう。パワークエリのような最新機能でテーブルの参照が使えますし、範囲選択もラクになります。

データをテーブル化するには、データシートの左上のセルを選択して「挿入 > テーブル」の順番にクリックしましょう。(ショートカットはCtrl+Tです)
after_table
ボタンを押すと小さなダイアログが出るので、「OK」を押してください。シートがテーブル化されます。
to_table

初期状態では「テーブル1」のような名前になっており参照しづらいので、名前を変更しましょう。名前の変更は「数式 > 名前の管理」で行えます。後から読んで「何のデータだっけ?」とならないような名前を付けておきましょう。
set_name

ステップ3.計算用のシートを作成する

計算が複雑になるものがある場合は、計算シートを用意しましょう。「データシートに直接関数を書いちゃだめなの?」と思うかもしれませんが、そこはグッと我慢してください。間違って計算式を消してしまうと、関数が複雑な程ダメージがでかいですから・・・(体験談)。

今回は候補者の連番を計算シートで算出します。ピボットテーブルと関数で簡単にできますね。
pibot_table

ステップ4.表示用のシートを作成する

ここまで来れば、あとは表示用のシートをゴリゴリ書いていくだけです。手間と根気のコピペ作業になりますが、一度やってしまえば次回からはデータを入力するだけで資料作成が完了します。

なおデータは別のシートに分離したので、このシートならエクセル方眼紙で好きにレイアウトしても問題ありません。

番外.変数用のシートを作成する

赤枠で囲った部分のように、資料の中に書き換えなくてはいけない箇所があると見落としのミスが発生しやすくなります。
image13

こういった部分は変数用のシートを作成し、関数に置き換えてしまいましょう。「変数用とデータ用のシートだけ見ておけば、あとはチェックしなくてもよい」と分かっていれば、ミスする可能性を減らすことができます。(シートの背景色を変えておくと分かりやすいですね)

変数シートの作り方は簡単です。シートを1枚追加し、書き換える内容が分かりやすいように適当に書き込んでおきましょう。
variable_sheet

各セルにはわかりやすい名前を付けておくと、計算式を見たときにも読みやすくなります。名前の管理は「数式 > 名前の管理」から開くことができますが、今回の場合は左上のボックスに入力するだけでも大丈夫です。
set_variable

最終的な関数はこうなります。ね、読みやすいでしょう?
set_function

結果

以上でネ申エクセルのリメイクが完了しました。ここまでの工程でどうなったのかを振り返りましょう。

結果1.データが表の形式で提供された

まず最もインパクトの大きい部分はここです。すべてのデータをデータシートに分離し、表の形式にまとめました。シートをCSVファイルとして出力してもきれいに出力されるので、他のプログラムからも扱いやすくなっているのが分かります。
csv_data

また直接CSVデータを扱えるということは、他のプログラムからデータを入力することも簡単にできるということでもあります。そのため将来はほとんど手間なくデータの入力ができるようになる可能性があります。

結果2.レイアウトを整える必要がなくなった

またすべてのデータをデータシートに分離したので、データが変わっても資料のレイアウトを変更する必要がありません。今まで罫線を引き直したり、セルの値を計算し直したりしていた手間を省略することができるでしょう。

結果3.ミスが起こりにくくなった

上記2つの結果、資料作成にかかる手間が減りました。これによりミスが起こる余地が少なくなり、また確認すべき部分も限定されたため、仮にミスがあっても発見される可能性が高まったことでしょう。

まとめ

今回はネ申エクセルを題材に、「効率的」かつ「データとして利用しやすい」資料を作成しました。Excelは使い方次第で本当に効率が変わります。ぜひ皆さんの職場でも活用してみてくださいね!

元SEのヤマムラです。エクセルが得意です。