Excel 読み込みウィザード ~ その強力な機能と活用例 ~ SAS Institute Japan 株式会社 JMP ジャパン事業部 2015 年 6 月作成 1. はじめに 2013 年 9 月にリリースした JMP 11 の Windows 版では Excel 読み込みウィザード という Excel データを対話的に読み込む ことができる機能が追加されました JMP 12 以降では Macintosh 版でも Excel 読み込みウィザードが使用可能となっています 図 1 Excel 読み込みウィザード JMP のメニューバーより [ ファイル ] > [ 開く ] を選択し JMP に読み込む Excel ファイルを指定すると 読み込み時に 図 1 のよう な読み込みのプレビューウィンドウが表示されます ここでは 読み込み後のデータプレビューを確認しながら 対話的に Excel デ ータの開始行や開始列の指定などを行うことができます Excel 読み込みウィザード では 主に次の読み込みオプションを指定することができます Excel ワークシートの開始行 開始列 終了行 終了列の指定 列見出しの行数の指定 ( 列見出しが複数行にわたっているときに有用 ) 複数の列見出しを階層化した見出しに変更 階層化したデータを積み重ねた状態で読み込み 複数のワークシートを連結し 1 つのデータテーブルとして読み込み ワークシート別に 読み込み形式を指定 1
また Excel 読み込みウィザードで Excel ファイルを読み込むと 指定した設定が JSL(JMP のスクリプト言語 ) に自動保存できるた め この JSL を実行することにより 簡単に Excel データの再読み込みを行うことができます 次の 2 章では これらの読み込みオプションのいくつかの機能について 実際の Excel ファイルの読み込み例とともに説明します 注意 : 本文書は JMP 12.0 での機能を説明したものになります 今後の JMP のバージョンアップにおいて 機能の追加 修正等が発生する可能性があります Macintosh 版 JMP 12 の場合.xlsx ファイルのファイル名およびフルパスに日本語などのダブルバイト文字が含まれていますと開く際にエラーが発生します ファイル名やパスに含まれるフォルダ名が半角英数字のみのものとなるようにしてください 2.Excel 読み込みウィザードでのデータの読み込み この章では サンプルの Excel データを用い Excel 読み込みウィザードの機能や特徴を説明します 例 1: データの開始行 開始列 終了行の指定 図 2 の Excel データ (sample1.xls) のシート 売上 を JMP に読み込みます 図 2 sample1.xls 2
JMP で読み込む際 次のように読み込みたいとします 3 行目を列見出しとする データの開始は 4 行 3 列からとする データの終了行を 19 行目とする ( 合計の行は読み込まない ) Excel 読み込みウィザードでは 上記の条件を設定して読み込むことができます < 操作 > 1. JMP のメニューバーより [ ファイル ] > [ 開く ] を選択して Excel ファイル sample1.xls を指定し [ 開く ] ボタンをクリックしま す 2. Excel 読み込みウィザードが表示されますので ここで 列見出しの開始行を 1 から 3 に変更します すると自動的にデータの開始行が 4 に変更されます ( 図 3) ( 注意 : データの開始列に関して 実際 Excel ファイルは 3 列目からデータが開始していますが 1 列目 2 列目 (A 列 B 列 ) には データが入力されていないため 自動的に Excel の 3 列目がデータの開始列として認識されます そのため この例では データの開始列を 3 列目に変更する必要はありません ) 図 3 Excel 読み込みウィザード (1/2) 3. 右下の [ 次へ ] ボタンをクリックすると 次の設定ウィンドウに移ります ( 図 4) 4. データの最終行に 読み込みたい最後の Excel の行が 19 行目なので 19 と入力します ( 注意 : データのプレビューに表示される最終行とは異なります ) 3
図 4 Excel 読み込みウィザード (2/2) 5. 右下の [ 読み込み ] ボタンをクリックします 読み込みウィザードで指定した設定に基づき Excel のシート 売上 が読み込まれます ( 図 5) 図 5 ウィザードにより作成された JMP データテーブル 4
データテーブルの左上のテーブルパネルには ソース という名前のスクリプトが自動的に保存されます 左側の赤い三角ボタン をクリックし [ 編集 ] を選択すると 保存されたスクリプトの中身を確認することができます ( 図 6) 図 6 保存されたスクリプト Open() 関数の最初には 読み込んだ Excel ファイルの場所の情報 ( この例では C:\Temp\sample1.xls ) が記載されています この場所に同名の Excel ファイルがあれば このスクリプトを実行することにより Excel 読み込みウィザードで指定した設定で 再度読み込みを行うことができます 自動的に保存されたスクリプトは 図 6 の編集ウィンドウからコピーし スクリプトエディタ ([ ファイル ] > [ 新規作成 ] > [ スクリプト ]) により起動 ) に貼り付け jsl 形式で保存すると ( 図 7) 今後この jsl ファイルを実行することにより 自動的に読み込みを行うことができます Excel ファイルのデータが更新された際 再度 JMP で読み込むといった場合に便利です 図 7 スクリプトエディタに貼り付け 5
例 2: 階層がある列見出しの設定 複数のシートを連結して読み込み 図 8 の Excel データ (sample2.xls) を JMP に読み込みます 図 8 sample2.xls このデータは 気象庁の Web ページ (http://www.jma.go.jp/) から引用しています 2012 年の 1 月から 4 月までの 1 日ごとの 東京の気温や降水量を示しています データは 月別に 4 つのシート (201201, 201202, 201203, 201204) に分けて入力されてお り データの入力形式は 4 つのシートとも同一です JMP で読み込む際 次のように読み込みたいとします 列見出しは 2 行目と 3 行目を用い 気温や降水量については 気温 - 平均 気温 - 最高 気温 - 最低 降水量 - 合計 というように 2 行目の情報と 3 行目の情報を併せた形の列見出しにしたい データの開始行は 4 行目からとする 4 つのシートを縦に連結して 2012 年 1 月 ~2012 年 4 月までのデータを 1 つにまとめた JMP データテーブルを作成する Excel 読み込みウィザードで 上記の条件を満たすように読み込んでみます 例 1 と同様に JMP で Excel ファイル sample2.xls を開き Excel ウィザードを起動します ( 図 9) 6
図 9 Excel 読み込みウィザード < 操作 > 1. 列見出しの開始行を 2 行目に設定します 2. 列見出しの行数を 2 に設定します この設定を行うと Excel シートの 2 行目と 3 行目を列見出しとして使用することになり 階層化された列名として (2 行名の列名 ) (3 行目の列名 ) と 列名間にハイフンを挟む形式の新しい列名になります データのプレビューを参照すると 列名は 気温 - 平均 や 降水量 (mm) 合計 といった形式に変換されていることを確認できます 3. ワークシートを連結して列をマッチさせるにチェックをいれます この設定により 4 つのシートが連結されます そのためデータプレビュー左下の 表示されている行 121/121 は 4 つのシートを連結した行数 =121 が表示されています さらに 連結の際 ワークシート名を含む列を作成するにチェックをいれると 最初の列に シート名を示した新しい列 ( 元のテーブル ) が追加されます この列の値はシート名になるので 1 つのデータに連結された際 どのシートからのデータなのかを確認することができます 4. すべてのワークシートに使用にチェックが入っていることを確認します チェックが入っていると 4 つのシートすべてに 今回の設定が反映されます 5. [ 次へ ] ボタンをクリックします ここでのウィンドウ設定はそのままにし [ 読み込み ] ボタンをクリックします 図 10 のように JMP でデータが読み込まれます 7
図 10 JMP データテーブル 3.Excel 読み込みウィザードの応用 現在 インターネットではさまざまなデータをダウンロードでき データ自体が Excel ファイルで提供されているサイトもあります 本章では 政府統計の総合窓口である e-stat (http://www.e-stat.go.jp) から 都道府県別の人口の推移に関する Excel データをダウンロードし Excel 読み込みウィザード で JMP へ読み込みを行います さらに読み込んだデータに対し JMP のグラフ機能を用いて データの可視化を行います Excel ファイルのダウンロード 注意 : 下記で紹介する Web ページ Excel ファイルは 2015 年 6 月現在のものです 総務庁統計局 ( http://www.stat.go.jp/index.htm ) のページの下側にある 調査名で探す のタブから 2. 人口推計 のリ ンクをクリックします 推計結果の 統計表一覧 のリンクをクリックします (e-stat のページに移動します ) 8
長期時系列データ ( 平成 12 年 ~22 年 ) のリンクをクリックします 上記リンクの URL は 以下の場所になります (2015 年 6 月現在 ) http://www.e-stat.go.jp/sg1/estat/list.do?bid=000001039703&cycode=0 都道府県の項目にある 9 番の Excel ファイルをダウンロードします ( ファイル名 :05k5-9.xls) 図 11 は ダウンロードした Excel ファイルです 図 11 ダウンロードした Excel ファイル 平成 12 年 (2000 年 )~ 平成 22 年 (2010 年 ) の都道府県ごとの人口が示されているデータであり 年ごとにシートが分かれていま す 総人口のほかに 0~4 歳 5~9 歳 と 5 歳階級での人口も示されています この Excel データを Excel 読み込みウィザードを用いて 次のように読み込みたいとします 9
列見出しは 9 行目の 0~4 歳 5~9 とします 列見出しを読み込むことが出来ない列は データテーブル作成後に列 名を手動で入力します 全国合計 (13 行目 ) の行は読み込まず データの先頭行は 14 行目の北海道からとします 最後の行は 60 行目の沖縄県にします ( その下には男女別の表がありますが これは読み込みません ) Excel 読み込みウィザード ダウンロードしたファイル 05k5-9.xls を JMP で開き Excel ウィザードを起動します ( 図 12) 図 12 Excel 読み込みウィザード データの行 列見出しの開始行などを設定 列見出しの開始行 = 9 とします データの開始行 = 14 とします ワークシートを連結して列をマッチさせるにチェックをいれます さらに 連結の際 ワークシート名を含む列を作成するにチェックをいれます すべてのワークシートに使用にチェックが入っていることを確認します データのプレビューで 列見出し データの開始行が想定通りになっているか 最初の列 元のテーブル が正しく追加されてい るかどうかを確認し [ 次へ ] をクリックします 10
次のウィンドウでは データの終了行の設定を行います ( 図 13) 図 13 Excel 読み込みウィザード データの終了行の設定 データの最終行 = 60 とします データのプレビューで 正しく連結されているかどうか確認し [ 読み込み ] ボタンをクリックします 図 14 のように シートを連結した JMP データテーブルが作成されます 図 14 JMP データテーブル 11
注意 : 以下で説明する 読み込んだデータの加工 バブルプロットを用いて 少子高齢化の現象を考察 グラフビルダーを用 い 地図上で少子高齢化の現象を考察 は Excel 読み込みウィザードとは直接関係はありませんが 実際 JMP で読み込んだデ ータを可視化する例として記載しております 読み込んだデータの加工読み込んだデータに対し 各都道府県の 20 歳未満の人口割合と 65 歳以上の人口割合が 年ごとにどのように推移していくか バブルプロットで可視化してみます そのために 列名の変更 列 元のデーブル の値を再コード化し 年 ( 西暦 ) の値に変更します さらに 計算式を用いて 20 歳未満の人口割合と 65 歳以上の人口割合を求めます まずは 1 列目から 4 列目を それぞれ 年 都道府県コード 都道府県 都道府県 ( ローマ字 ) と列名を変更します 最初の列 年 を選択し [ 列 ] > [ ユーティリティ ]> [ 再コード化 ] により 次のように新しい値を入力して 値の置換を行います 新しい列 20 歳未満の割合 65 歳以上の割合 を作成し 次のように計算式を指定します 列 20 歳未満の割合 の計算式 列 65 歳以上の割合 の計算式 ( 注意 : 今回のデータは 2006 年までは 80 歳以上の人口を示していますが 2007 年以降では 80~84, 85 歳以上と 80 歳以上の人口区分が増えています そのため 2006 年までのデータでは 列 80 歳以上 に値が入力され 2 つの列 80-84 85 以上 は欠測値になり 2007 年以降では 2 つの列 80-84 85 以上 に値が入力され 列 80 歳以上 は欠測値になります このような場合でも Sum 関数を用いると欠測値はカウントせずに合計しますので 上記の計算式で 65 歳以上の人口を求めることができます ) 都道府県 の名称は 北海道 のように文字間に半角スペースが入っているため [ 編集 ] > [ 検索 ] > [ 検索 ] を選択し 検索 する文字列に半角のスペースを指定し [ すべて置換 ] ボタンをクリックすると 半角スペースを削除することができます 12
東北地方や関東地方など 地方での傾向も見たいため 新しい列 地方 を作成し 次のような計算式を指定します 図 15 が 分析用データテーブルです 新しい列 地方 20 歳未満の割合 65 歳以上の割合 が追加されています 図 15 分析用データテーブル 13
バブルプロットを用いて 少子高齢化の現象を考察 [ グラフ ] > [ バブルプロット ] を選択し 次のように列を指定します バブルプロットが出力されます 横軸は 20 歳未満の割合 縦軸は 65 歳以上の割合 各都道府県のバブルの大きさは その 年の人口の総数 ( 総人口 ) を示します 20 歳以上の割合と 65 歳以上の割合を比較するために 次の操作を行います 横軸と縦軸について 軸の最小値を 0.1 最大値を 0.3 目盛り間隔を 0.05 にします グラフ上で右クリックし [ カスタマイズ ] を選択します グラフをカスタマイズのウィンドウで左上の + ボタンをクリックし 中 央のスクリプトウィンドウに Y Fucntion(x,x); と入力します この操作で Y =X の直線を引くことができます バブルプロットの左上の赤い三角ボタンより [ ラベル ] > [ すべて ] を選択し すべてのバブルにラベルをつけます 図 16 は 上記の操作を行った後の出力になります これは 2000 年のプロットです 14
図 16 バブルプロット (2000 年 ) Y=X の直線は 20 歳未満の割合と 65 歳未満の割合のどちらが高いか 低いかをみる境界線になります 直線の下側にプロットが位置すれば 20 歳未満の割合が高く 上側は 65 歳以上の割合が高いことになります 図 16 の 2000 年では 総人口が多い都道府県のバブルの多くは直線の下側に 総人口が少ない都道府県のバブルの多くは直線の上側に位置しているようです ただし沖縄県はグラフの右下に位置し 他の都道府県に比べ 20 歳未満の割合が高く 65 歳未満の割合が低いことを示しています バブルプロットの左下にあるアニメーションの開始ボタン ( ) をクリックすると 年ごとの割合の推移をアニメーションで確認する ことができます 図 17 は 2010 年におけるバブルプロットです バブルプロットの左上の赤い三角ボタンより [ 軌跡線 ] > [ すべ て ] を選択すると バブルの軌跡を線で表示することができます 図 17 バブルプロット (2010 年 ) 15
バブルの軌跡から すべてのバブルが左上に進んでいることがわかり いわゆる少子高齢化が進んでいることを確認できます 2010 年では 沖縄県を除くすべてのバブルが直線より上側に位置するので 65 歳以上の人口割合が 20 歳未満の人口割合より高くなっていることがわかります バブルの色は 右上の凡例にあるように 地方 を示しますが 赤色である関東地方のバブルが それぞれ近くに位置しています ( 図 18) 図 18 バブルプロット (2010 年 ) で 関東地方のみ強調表示 そこで今度は グラフビルダーを用いて 地図上で都道府県の少子高齢化の状況を確認してみます グラフビルダーを用い 地図上で少子高齢化の現象を考察 データテーブルに戻り 新しい列を作成し 次のような計算式を作成します 列名は 65 歳以上の割合 /20 歳未満の割合 としま す この値が 1 より大きい場合は 65 歳以上の割合が 20 歳未満の割合より高いことになり 値が大きいほど 少子高齢化が進んで いると考えられます JMP のメニューバーより [ グラフ ] > [ グラフビルダー ] を選択し 左下の [ 地図シェープ ] に 都道府県 をドロップし 右上の [ 色 ] のゾーンに 65 歳以上の割合 /20 歳未満の割合 をドロップします さらに 右側の凡例を選択し カラーテーマや ラベルの数 最小 中央 最大を次のように設定します 16
図 19 がグラフビルダーの出力です このグラフでは 2000 年から 2010 年までの 65 歳以上の割合 /20 歳未満の割合 の平均値 が 都道府県別に 地図上で色分けして表示されています 図 19 グラフビルダーで地図表示 年ごとの推移をみるため ローカルデータファイタを用います グラフビルダー の左上にある赤い三角ボタンより [ スクリプト ] > [ ローカルデータフィルタ ] を選択します フィルタ列の追加で 列 年 を追加すると 2000 年 2001 年 2010 年と データにフ ァイルをかけて結果を参照することができます 図 20 図 21 図 22 は それぞれ 2000 年 2005 年 2010 年でフィルタをかけたときの出力になります 図 20 65 歳以上の割合 /20 歳未満の割合 (2000 年 ) 17
図 21 65 歳以上の割合 /20 歳未満の割合 (2005 年 ) 図 22 65 歳以上の割合 /20 歳未満の割合 (2010 年 ) 2000 年 2005 年 2010 年と見比べると 次第に濃い色になっている すなわち 65 歳以上の割合 /20 歳未満の割合 が大きくなっ ていることがわかります 特に 東北や北陸 中国地方の太平洋側や四国地方は 比較的少子高齢化が進んでいるようです 18
4. まとめ 第 2 章では Excel 読み込みウィザードの機能についてサンプルデータを用いて説明し 第 3 章では 実際にインターネットから Excel ファイルをダウンロードし Excel 読み込みウィザードを使って JMP で読み込み 読み込んだデータを用いて JMP で分析を行う例を示しました 第 3 章で用いた Excel ファイルは シートが年ごとに複数あり これらを Excel ウィザードでまとめて 1 つの JMP データテーブルとして読み込むことができたので 作業時間が大幅に短縮できています JMP で時系列のデータを扱うには 今回のように各年のデータを連結し 積み重ねた形で用意しておく方が その後の分析で都合が良いことが多くあります そのため 自動的に同形式の複数のシートを ウィザード上で自動的に連結する機能は 強力な機能といえます さらに Excel 読み込みウィザードで読み込んだ設定は JMP のデータテーブルにスクリプトとして自動的に保存されるので 定型的なファイルを 自動的に読み込むプログラムを自動的に作成できるという点も強力な機能です データ分析は 実際にデータを分析する時間よりも 分析の前処理である分析用のデータを作成することの方が 時間を要すると いわれています 本文で説明した Excel 読み込みウィザード を用いると Excel からのデータを効率よく読み込むことができ データの作成のプロセスを短縮することができます < 参考 > Windows 版 JMP 12 のデフォルト設定では [ ファイル ] > [ 開く ] で Excel ファイルを開いたとき 自動的に Excel 読み込みウィザードが起動する設定になっています ウィザードを設定せずに 直接 Excel ファイルを読み込みたいときは JMP のメニューバーから [ ファイル ] > [ 環境設定 ] を選択し Excel ファイルを開く方法 を すべてのシートを開く または 個々の Excel シートを開く に設定します ( 図 23) Macintosh 版 JMP 12 の場合.xlsx ファイルは常に Excel 読み込みウィザードで読み込むようになっています.xls ファイルを読み込む場合は Excel ウィザードを使用する のオプションをオフにして直接読み込むことも可能です 図 23 Excel ファイルを開く方法の環境設定 19