Excelをデータとして加工したり、集計したいとき、受けとったデータがプリントアウトする前提でつくられた表の形式の資料だった、ということがあります。
これをデータとして整える手順についてまとめてみました。
データとして使う
もらったデータがプリントアウトする前提で表の形式ということもよくあります。
これを余白のないデータに変換すれば、データとして使えるようになります。
このような形です。
このようにデータとして使えるように整えることで、ピポットテーブルで集計したり、グラフにしてわかりやすい資料へ発展させることができます。
このように変換するにはExcelの機能を使います。
データとして使えるExcelに整える手順
まず開いたExcelの［データ］から「データの取得」→「ファイルから」→「Excelブックから」
を選びます。
Excelを選んで「インポート」をクリック。
このような画面になったら、
左側のフォルダ（この場合だと「1月支出内訳」）を選択→「データの変換」
をクリック。
すると、このような画面に替わります。Power Query（パワークエリ）というものです。
ラベルの［Kind］と［Hidden］は右クリックで削除しちゃいます（今回はいらないので）。
［Name］はインポートしたExcelのsheet名が表示される列です。
ここでは「sheet1」とありますが、今回インポートしたExcelでは元々「sheet1」という名前のシートが一つだけです。
一つだけなので、この［Name］タブも右クリックで削除しちゃいます。
このようなシンプル形式になったら、データとして使えるように実際に整えていきます。
ちなみに、右側にあるステップの「×」をクリックすれば、ひとつ前の状態に戻すことができます。
このように作業履歴がステップとして記録されるので、間違ったときは戻りたいステップからやり直せます。
そして、［Data］タブの右側にある矢印をクリックして「OK」。
（「元の列名を～」のチェックは外しておきます。見にくくなるので）
すると、このようにデータが展開されるので、この画面で整えていきます。
まず「null」という表示。
セルが空欄という意味ですが、データとして集計したり、加工するためにはこの空欄のセルというのは好ましくはありません。
今回のデータでは、「Column1」～「Column4」の各列に、この「null」があります。
しかも、1行目では、プリントアウトする資料にありがちな表の名前（タイトル）。
9行目も「2023/1/8」の日付のみデータが入っていて、他の列は「null」。
データとして生かしておいてもたいして意味のない行です。
このような感じで、ある程度の規則性がつかめたら、「null」を非表示にします。
今回のケースは左から4列目［Column4］は金額の列。
この列の「null」を非表示にすれば問題ありません。
このように、［Column4］のプルダウンメニューから「null」のチェックを外すと、
こうなります（↓）。
1行目はデータの列名なので、「1行目をヘッダーとして使用」をクリックすれば、1行目がヘッダーに置き換わります。
あと、18行目にある「合計」という行。
データとして集計することを考えると、この合計行はいりません。
同じように非表示にしておきます。
それと、「年月日」の表記。
2023/1/6のように西暦表記がある中で、R5.1.11という元号表記（しかも文字列）がまぎれています。
この文字列もデータとして整えることを考えると好ましくありません。
たとえば、
・2023/1/11（日付データ）
・R5.1.11（日付の文字列）
見た目は同じ日付ですが、Excel側からすると全くの別モノ扱いとなってしまいます。
ピポットテーブルで集計すると、このように正しく集計してくれません。
なので、「R5.1.11」の文字列を「2023/1/11」という日付データに置き換えてあげます。
あとから置換するという手もありますが、ここではこのPower Query上で置き換えてしまいます。
「R5.1.11」にカーソルを合わせて右クリック→「値の置換」で、日付データに置換できます。
すると、このようにデータとして整えることができるわけです。
これで完成です。
データとして整えて自由度をあげる
今回は例ではExcelのブックが一つ、シートも1つでしたが、受けとる資料によっては、
・Excelブックが複数ある
・ブック内にシートが複数ある
このような場合も多いです。
ただ、複数であったとしても、基本的な流れは変わりません。
Power Queryは、データ形式に整える選択肢の1つかと。
そして、データとして整えるには、どういった情報がどう整理されればいいかを考えるいい訓練になります。
まだ試したことがなけば一度実際に手を動かして試してみるのもおすすめです。
■編集後記
今日は5:15起床後に朝タスク。午前は会計士業、調べもの、午後は所用で外出、ブログなど。
■息子（8歳11ヶ月）
朝起きてくると「きのう、学校もデイサービスもひさしぶりだったから、カラダがだるいよ」と。
放課後デイも久々だったので、疲れがたまってしまったようで、大事をとって今日は学校をお休みに。
そのかわり、おうちで漢字のお勉強を。
■1日1新
・スタバのモカチョコ（ドリンク）
・とある研究
