日本政府観光局が公開している訪日外客数のデータを使って、Power BI Desktop や Excel、Power BI Service でインバンドのインサイトを探してみよう、という試みの第5回目です。
各年の複数ワークシートで構成されている訪日外客数のブックを取り込んだところまできました。
今の状態は、クロス集計表で表現されている「セルの範囲」が複数年、縦に連なっている状況です。第4回で説明したように、個々の列の情報は同じものという大前提があります。
ここから「不要な情報を取り除く」作業をする、または「必要な情報のみを残す」作業をします。
そのために最初はちょっと違和感があるかもしれませんが、この複数年のクロス集計が縦に連なったセル範囲を「テーブル」のように扱います。テーブルのように扱うことで、見出し行を追加し、「オートフィルター」機能が使えるようになります。そのフィルター機能を使って「不要な行」を取り除く、もしくは「必要な行だけを残す」操作をします。
(1) タイトル行のベースになる行を探して、タイトル行にする
これらの操作は、現実のデータを見て検討するので、必ず以下で設定する内容であったり、順番であったりが正しいわけではないことに注意してください。扱うデータは「ナマモノ」であり、調理方法（加工方法）はさまざまです。その時その素材にあった方法を見つけるしかありません。
重要なのは、その操作が「再利用可能か？」を常に意識したほうが良い、ということです。この「再利用可能か？」は所々で解説したいと思います。
今回扱っている訪日外客のデータで、テーブルに変換したときにタイトル行になって欲しいのは、4月、5月、6月といった月であり、その行にあるべき（表示はされていませんが）国名などになります。
一切並べ替えをしないで取り込んだ直後のデータは以下のようなものです。
この時、最初の行は 2003 年の昔のデータなので、基本このワークシートは「変更されない」と「仮定」します。そうすると、「タイトル行」にしたいデータ行は4行目にあります。
このように「最初の3行はとりあえず使わない、要らない」というのがわかっているのであれば、「上位の行の削除」を使って、上から何行を削除する、という操作が可能です。
クエリ エディターの[ホーム]タブは、データを取り込んだ直後によく使う基礎的な機能/コマンドをまとめています。[行の管理]や[列の削減]のグループにはデータ取り込み直後に良く使う機能があり、その中に [行の削除] の [上位の行の削除] があります。
どの列が選択状態になっていても、ここでの指定は「行全部」となります。上位の行の3行を削除、という指定は、すべての列を含む3行を削除します。
また、今回の訪日外客のデータ展開で、第1列目の[Name]にはテーブル名に相当する 2003 などの年が「必ず」入っているので、列全てが null になる「空白行」は存在しません。そのため、（くどいですが）「今回の訪日外客のデータ」では全ての列が空白になる「空白行の削除」は該当しません。
また、この時点で当然疑問に思いますが、2003年のデータの[5月] や [6月]の行をこれから「先頭行」として使う設定を行いますが、2004年以降のデータの[5月]や[6月]は、データとして残っています。これはどうなるんだ？と思われるでしょう。これは、後ほどの作業で結果的に「消す」作業をします。
上位3行を消すと、 [5月] [伸び率] [4月]・・・のデータを含む行が1行目になるので、この行を [見出し行] として使います。そのまま [ホーム] タブの [変換] グループにある [先頭の行を見出しとして使用] をクリックして、1行目を見出し行（タイトル行）に変換します。
この変換により、重複した列名は許されない、もしくは必ず列名がなければいけませんから、[伸率]、[伸率_1] などの名前の自動変更が行われます。
ここからは、そのデータにあった対応を行っていきます。JNTO の訪日外客のデータの場合は、以下の方法で不要なデータを取り除くことができました。これらの方法や考え方は、他のデータでも十分に適用可能だと思います。
(2) フィルターをつかって不要な行を取り除く
今、この状態は、複数のワークシートを追加(Append）し、2003年のデータの上から3行分を削除し、4行目を見出し行にしたに過ぎません。不要なデータはまだ山のようにあります。以下はスクロールしていくと見つけることができる不要なデータの例です。（行が多いときはスクロールせずに、フィルターオプションから、重複しないデータの確認ができます）
「総数」や「アジア計」、「ヨーロッパ計」といった、小計、合計の行は必要ありません。
小計行もいりませんが、コメント・注釈行もいりません。
2003年の上位3行で消した表のタイトルは当然次の年のデータでは出てきます。これらの行は必要ありません。
もし、必要となるデータの国が決まっているのであれば、[Column2] のオートフィルダーで国の名前を直接指定するのも一つの方法です。以下であれば、Column2 に「中国」、「台湾」、「米国」、「韓国」、「香港」がある「行だけ」を選択して絞り込むことができます。
ただ、実際には国のデータは昔のデータにはなかった国のデータが追加されることも多く、必要な国を指定して、国を固定するより、不要なデータを削除するほうが「再利用性」が高いと言えます。今後どんな国が増えるかわからない、という状況は、「今後どんな製品が増えるかわからない」や、「今後どんな営業所が・・・」「今後どんなお客様が・・・」に通じるものがあると思います。
この場合に使いたいフィルターオプションは「指定の値を含まない」フィルターオプションです。
このテキストフィルター オプションを使えば、新しい国が入ってきても対応可能です。もちろん、「新しい不要な行」が入ってきたら、その対応をするのには変わりません。新しい不要な行と、新しい国のどちらが追加される可能性が高いかを実データから想定する必要はあります。
・漢字の「計」がある国名はない、という前提で「計」の文字を含まないものにする
＞アジア計、ヨーロッパ計、アフリカ計、北アメリカ計など、地域の小計が排除される
・漢字の「注」がある国名はない、という前提で「注」の文字を含まないものにする
＞注１：、注２の行が排除される。もちろん漢字1文字の「注」に問題がありそうならば、その行に含まれる「本資料」や「表中の」などを指定すると確実になります
・「総数」」を含む行を外す
＞「2004年 訪日外客数（総数）」といったデータのタイトルを表す行と「総数」の計算行が排除される
・「出典：」を含む行を外す
＞表の下にある「出典：日本政府観光局（JNTO)」を含む行が排除される
・「本表で」を含む行を外す
＞「＊本表で、通年の・・・」のコメント行が排除される
このような設定を繰り返して、すべての不要な行を削除します。確認はオートフィルターのリストを見るとが一番いいでしょう。
Column2 は「国」または「地域」のデータ列になったので、見出し行の [Column2] をダブルクリックすると編集モードになるので [国名] などに変更します。また、第1列目の [2003] は年々のデータかを表すので [年] に変更しておきます。
これで不要な行の削除が終わりました。
長くなりましたので、次回は、列について操作します。
お楽しみに。
[PR] M言語(Power Query Formula Language)について書いている書籍はコレ！
0 コメント:
コメントを投稿