プログラムの開発をしていて、利用するためのデータをExcelで受け取ることがあります。そのときに、データとして使いにくいExcelと使いやすいExcelがあります。同じ作るなら使いやすく再利用しやすいExcelを作りませんか？ということでどういうExcelを作れば使いやすいのかについて書いてみようと思います。
データとして使いにくいExcelとは？
データとして使いにくいExcelとはいったいどんなものでしょうか？
たとえばこのExcelですが、印刷プレビューで見ると、
こんな感じでA4横にちょうどいい感じに収まっています。
印刷したときに見やすいように空行や空セル列が挿入されたりもしています。
このExcelは印刷用としては大変良いのですが、データとしてプログラムで使おうとすると加工をしなければ使えないものになってしまいます。
Excelをプログラムでデータとして使うとき
プログラムでデータとして使うためにはCSVの形にすることが多いです。このExcelをCSVに落としてみます。
ファイル＞名前を付けて保存 をクリック
ファイルの種類で、CSV(カンマ区切り)を選択します。
適当な名前を付けて、保存をクリックします。
CSVを開くとこんな感じになってました
15 外国人住民の国籍別人口,,,,,,, ,○○○○に関する国別統計,,,,,, 4月1日現在,,,,,,, ,,,,,,, 国名,,,,平成23,24,25,26 ,,,,,,, ,,,,,,, ,,,,,,, ,,中国,,"3,700","3,800","3,570","3,730" ,,韓国,,"2,200","2,000","2,100","1,900" ,,フィリピン,,"1,000","1,050",900,"1,000" ,,米国,,250,200,200,230 ,,ペルー,,200,200,180,170 ,,,,,,, ,,ブラジル,,190,180,150,170 ,,ベトナム,,90,100,110,200 ,,タイ,,150,140,120,100 ,,マレーシア,,118,100,130,120 ,,英国,,30,40,50,60 ,,,,,,, ,,その他,,"1,100","1,200","1,100","1,100" ,,,,,,, ,総数,,,"9,028","9,010","8,610","8,780" ,,,,,,, ,,*但し○○○○は○○○○とする。,,,,, ,,,,,,, ,,,,,,,
見た感じ、行によってばらばらな感じで値が入っています。そしてこれの何が使いにくいのか、ということについて説明します。
プログラムで使う場合、「決まったところに決まった値が入っている」ということが前提となります。そのことにより、1つめの列は国名で(国名ということは1行目の見出しで判断とか)、2つめの列は何年の値、ということがわかるためです。
プログラムで使う場合、このExcelからはは各年度に対する各国の数値を利用することになります。
しかし、このExcelでは
・データに関係の無い文字情報が入っている(1,2,3,25行目)
・空白の行が入っている(4,7,22,24行)
・空白の列が入っている(A,B,D列)
・数字セルに3桁区切りの書式を設定している
・5,6行目に渡ってセルの結合が使われている
という部分があるためにCSVにしたときにデータとして使うには手作業で修正しないといけません
この場合の使いやすいExcelデータはどんな形か？
ではこの同じデータを使いやすい形のExcelにするとどうなるでしょうか。
それは、
こんな感じです。ポイントとして、
・1行目は2行目以降に何が入るのかを示す項目名が入っている
・2行目以降は項目に関する値のみが入っている
・位置を調整するための空行や、空セルが入っていない
・データに関係しない文字情報は入っていない
という事があります。
CSVデータは、
国名,平成23,24,25,26 中国,3700,3800,3570,3730 韓国,2200,2000,2100,1900 フィリピン,1000,1050,900,1000 米国,250,200,200,230 ペルー,200,200,180,170 ブラジル,190,180,150,170 ベトナム,90,100,110,200 タイ,150,140,120,100 マレーシア,118,100,130,120 英国,30,40,50,60 その他,1100,1200,1100,1100 総数,9028,9010,8610,8780
こんな感じです。1行目に見出しが入るため、各行の何番目に何が入っているか分かるし、各行の値は規則通りに並んでいます。
プログラマーとしてはこれで万々歳！なのですが… でもこれ印刷したら、
こんな形になっちゃいますね(^_^;。うーん。
ということで、分ければ資源 もとい 「分ければオープンデータ！」
ということで資料を印刷して配る人にも、資料から必要なデータをもらうプログラマにも良い方法を考えました。
名付けて分ければ資源 もとい 「分ければオープンデータ！」 です！
まずデータはプログラマが使いやすい形で作る
住所録に新しい住所を書き込む、と思ってください。郵便番号には郵便番号だけ、住所には住所だけ、名前には名前だけを書きますよね。そのような感じでまず1行目に見出しを付けて(セルの結合とかはせずに)、その中身を2行目以降に書いていきます。
これが例であげたデータが見やすくCSV出力できるデータのシートです。
印刷用に見やすくするのはリンク貼り付けを使う
このデータを使って印刷用に見やすい形に整えます。データのシートはそのままにしておいて、新しいシートにデータの内容を「リンク貼り付け」します。
まず、
使う範囲を選択してコピーします。
新しいシートを開き、
A1セルで、右クリックして リンク貼り付けをクリックします。
データが貼り付けられました。データのシートと今は全く同じに見えますが、データがリンク状態で貼り付けられていますので、間に空行を入れたり空きセル列を入れたりしても中身はデータ用のシートのものが参照して表示されているので変わりません。
また、値を変更するときもデータ用シートで変更しておけば自動的にリンク貼り付けしたセルの値も変更されます。
このシートで
空行を入れたり、
間隔を開けるために空きセル列を入れたり、
データと関係の無い文字を入れたり、数字に3桁区切りの書式を設定したり、その他印刷したときに見やすいような修正を入れていきます。
セルを結合したりしても全く問題ありません。結果的に、
この形にすることができます。これなら印刷するときも全く問題ないですね。
プログラマからデータが欲しいとか、オープンデータとしてデータを提供するときには データ用のシート を使い、印刷やWebページで表示するときはリンク貼り付けして作った印刷用のシートを使う、これで 再利用可能なExcelデータになると思います。
結論ですが、Excelのようなデータは色々なところで再利用が可能ですので、是非一つの手間で色々なことにつかえるようなデータ形式で作るようにしてみませんか(^^)/
コメント