目標
今回のテーマはネ申Excelとも呼ばれるExcel方眼紙のデータ化（テーブル化）です。
Excel方眼紙とは、各セルを正方形にし、セルの結合を駆使して項目を並べた入力フォームのことです。
そのデータ化の煩わしさから悪名高いExcel方眼紙ですが、「どの座標にどのデータがある」というフォーマットさえ決まっていれば、パワークエリでデータ・テーブル化することは難しくありません。
技術的なポイント
基本的にどの座標に何のデータがあるかを調べ、それを拾ってくるという流れです。
実践
今回は、単に一つのExcel方眼紙のデータを取得するだけでなく、以下のように一つのフォルダ内の複数のファイルをまとめてデータ化するシナリオです。
Excel方眼紙の項目の座標を知る
まず対象のExcel方眼紙のデータ項目を調べます。サンプルとしてファイルを一つ開いてください。
※ 今回は分かりやすくするため、入力セルの色を変えています。
入力項目を見ると、右上に入力日、中央に氏名、生年月日、住所の4つがあります。
ありがちですが、入力日は和暦、月、日がそれぞれ別々の入力セルになっています。
次にそれぞれの座標を数字で取得します。行はもともと数字ですが列がアルファベットなので一工夫します。
ファイル → オプション → 数式
数式の処理 → R1C1参照形式を使用するにチェック
これで列も数字表記になりました。
ここから各項目の座標を調べます。
セルの結合が行われた場合、結合されたセルの左上のセルにデータが入るので、左上の座標だけ抑えればOKです。
なお、入力項目にカーソルを当てると行・列の色がハイライトされるので便利です。
以下の例では、行・列の一番小さい数字を取ってくればよいので、行:7、列:10の座標となります。
それぞれの項目の座標を調べると以下のようになります。
Excel座標
| 項目 | 行 | 列 | 値 |
|---|---|---|---|
| 入力日（和暦） | 2 | 24 | 1 |
| 入力日（月） | 2 | 27 | 12 |
| 入力日（日） | 2 | 30 | 18 |
| 氏名 | 7 | 10 | Excel 太郎 |
| 生年月日 | 7 | 25 | 1970年1月1日 |
| 住所 | 9 | 10 | 東京都 XX区 １２３４５ |
行・列の座標で指定したセルの値を取得する
次に行列の座標を指定すると、そこの値を取ってくるクエリを作ります。
実際のデータを使う前に、簡単なサンプルテーブルを自作して実験を行います。
データ取り込み用の新しいExcelファイルを作り、以下の手順で空のクエリを開きます。
データ → データの取得と変換 → その他のデータソースから → 空のクエリ
Power Queryエディターが開いたら数式バーに以下のように入力して、実験用テーブルをマニュアル作成します。
= #table({"列1", "列2", "列3"}, {{11, 12, 13}, {21, 22, 23}, {31, 32, 33}})
実験用テーブルが作成されました。
今度は、試しに列2の3行目をクリックします。
すると、以下のように画面下にセルの値が32と表示されます。
次に同じセルを右クリックし、ドリルダウンを選択します。
するとテーブル表示が消え、ドリルダウンされた一つの値だけが表示されます。
ついでに数式バーを見ると以下の式が表示されています。
= ソース{2}[列2]
ここで数式を見ると、三つの部分があることが分かります。
１）ソース 【テーブル】
これは直前のステップで作成されたテーブルのことです。
２）{2} 【行】
パワークエリではテーブルの行を中カッコ { } で指定します。
また、パワークエリでは数字のカウントは常に0から始まります。
したがって数字は2ですが3行目のことです。
３）[列2] 【列】
パワークエリでは各カッコ [ ] でテーブルの列を指定します。
したがって、列2のことです。
つまり、これら３つの情報を渡すことができれば、Excel方眼紙の特定の項目を取得できそうです。
カスタム関数を作る
さて、ここまで分かったのでこのクエリを徐々に関数化してゆきます。
関数化するということは、クエリの外から入ってきたデータを元に、別な何かをアウトプットするということです。ここで言う別な何かとは、Excel方眼紙の特定の項目に他なりません。
クエリの名前の変更
まず関数化するにあたって、クエリの名前を変更します。
右側のクエリの設定でプロパティの名前をfnGetValueに変更します。ちなみにfnはfunctionから来ています。
行のパラメーター化
先に述べた3つの要素をクエリの外から持ってこれるように（パラメーター化）します。
こういう場合はつねに一番簡単なところから始めます。
行を見ると{2}とあるので、この数字をクエリの外から入ってきた数字と置き換えられるか実験します。
ホーム→詳細エディターで詳細エディターを開き、クエリの内容を以下赤字のように変更します。
(myRow as number) =>
let
ソース = #table({"列1", "列2", "列3"}, {{11, 12, 13}, {21, 22, 23}, {31, 32, 33}}),
セル値 = ソース{myRow}[列2]
in
セル値
つまり、
１） (myRow as number) =>でクエリをmyRowという数字パラメーターを受け取る関数にし、
２） そこで受け取った数字をmyRowとして行の指定に使用しています。
ここまで変更して完了を押すと、表示が以下のように変わります。
myRowとして入力ボックスが表示され、その下に呼び出しボタンが表示されます。
試しにmyRowに1と入力して呼び出しを押してください。
すると、新しく自動作成されたクエリから、関数fnGetValueが指定されたパラメーターで呼び出され、2行目の値を取得することに成功しました！
列のパラメーター化
次に列をパラメーター化します。
前回は行だったので数字の指定だけで十分でしたが、今回は列という文字の指定なので一工夫が必要です。つまり、座標としての数字を指定し、そこから何番目の列かという形で列名を指定します。
左側のクエリペインでfnGetValueを選択し、詳細エディターを開いてください。
詳細エディターが開いたら、以下のように変更します。
(myRow as number, myCol as number) =>
let
// テーブルの作成
ソース = #table({"列1", "列2", "列3"}, {{11, 12, 13}, {21, 22, 23}, {31, 32, 33}}),
// 列の指定
列名リスト = Table.ColumnNames(ソース),
列名 = 列名リスト{myCol},
列リスト = Table.Column(ソース, 列名),
// 行の指定
セル値 = 列リスト{myRow}
in
セル値
今回はクエリが長くなってきたので//でコメント行を入れました。//の後は処理されないので、わかりやすいコメントを入れましょう。
列の指定は以下の手順で行っています。
１） 列名リスト = Table.ColumnNames(ソース)： テーブルの列名一覧を取得
{"列1", "列2", "列3"}
２） 列名 = 列名リスト{myCol}： 列名リストのX番目の文字を取得
{"列1", "列2", "列3"} （myCol = 1 の場合）
３） 列リスト = Table.Column(ソース, 列名)： 列名の値を縦にリスト化
{12, 22, 32}
行の指定は上の列リストを使って以下のようになります。
１） セル値 = 列リスト{myRow}
{12, 22, 32} （myRow = 2 の場合）
それでは以下のように入力して呼び出しを実行してみてください。
myRow = 2 （3行目）
myCol = 1 （2列目）
結果が32になれば成功です。
無事、32となり、3行2列目の座標の値をゲットできました！
テーブルのパラメーター化
最後にテーブルをパラメーター化して完成です。
まず、現在カスタム関数内にある実験用テーブルを別なクエリとして作成します。
画面右上から以下の手順で空のクエリを開きます。
新しいソース → その他のソース → 空のクエリ
数式バーに以下のように入力して、実験用テーブルを作ります。
= #table({"列1", "列2", "列3"}, {{11, 12, 13}, {21, 22, 23}, {31, 32, 33}})
クエリの名前を実験用テーブルにします。
再び左側のクエリペインよりfnGetValueを選択します。
同じく詳細エディターを開き、以下のように変更します。
(myTable as table, myRow as number, myCol as number) =>
let
// テーブルの作成
ソース = myTable,
// 列の指定
列名リスト = Table.ColumnNames(ソース),
列名 = 列名リスト{myCol},
列リスト = Table.Column(ソース, 列名),
// 行の指定
セル値 = 列リスト{myRow}
in
セル値
テーブルの部分をmyTableとしてパラメーター化し、letの中のソースの値と差し変えました。差し替えただけなので、後続の処理はそのままで結構です。
今度はmyTableもパラメーターに追加されているので、以下のように選択し呼び出しを押します。
myTable = 実験用テーブル
myRow = 2 （3行目）
myCol = 1 （2列目）
無事32の結果が表示されました！
※ 2019/12/27 この座標を-1するアイデアは『いちばんやさしいExcelピボットテーブルの教本 人気講師が教えるデータ集計が一瞬で終わる方法』著者 羽毛田 睦土先生のアドバイスを受けて追加した箇所です。
これでも関数の機能としては十分ですが、もう一歩手を加えてさらに使い易くします。
パワークエリでは数字はすべて0開始なので、1開始のExcel座標はすべて-1して渡さないといけません。それだったら関数の中で-1してしまえばよいというわけで、以下のように修正します。
(myTable as table, myRow as number, myCol as number) =>
let
// テーブルの作成
ソース = myTable,
// 列の指定
列名リスト = Table.ColumnNames(ソース),
列名 = 列名リスト{myCol - 1},
列リスト = Table.Column(ソース, 列名),
// 行の指定
セル値 = 列リスト{myRow - 1}
in
セル値
今度は以下のパラメータで関数を実行します。
myTable = 実験用テーブル
myRow = 3 （3行目）
myCol = 2 （2列目）
今回はExcel座標を渡しても32の結果が得られました！
これで関数は完成です。左側のクエリペインの呼び出された関数はもういらないので削除します。
Ctrlキーを押しながらそれぞれクリックし、右クリックで削除を選びます。
これですっきりしました。実験用テーブルは消しても構いませんが、変更や調査の為に残しておくと便利です。
複数Excelファイルの一括取り込みとデータ化
いよいよ仕上げです。次はExcel方眼紙をまとめて読み込みます。
以下の記事を参考に複数のExcelファイルを読み込みます。
まず、データソースとしてフォルダーを指定します。
新しいソース → ファイル → フォルダー
Excel方眼紙のファイルが保存されているフォルダー パスを指定します。
そのままデータの変換を押します。
データが表示されたら、まずは不要な列を削除します。
Ctrlキーを押しながらContentとNameを選択→ホーム→列の削除→他の列の削除
次に以下の式で列の追加 → カスタム列で、データテーブルを追加します。
=Excel.Workbook([Content]) (大文字・小文字を正確に入力してください。)
カスタム列が追加されたら右上の展開ボタンを押し、列はDataのみを選択し、プレフィックスのチェックを外してOKを押します。
※ シートを指定する場合などはいったんその他の列も含めて展開し、フィルターで絞り込んでください。
いよいよ先ほど作った関数を使って座標を指定します。ここで最初のステップで用意したExcel座標が登場します。
Excel座標
| 項目 | 行 | 列 | 値 |
|---|---|---|---|
| 入力日（和暦） | 2 | 24 | 1 |
| 入力日（月） | 2 | 27 | 12 |
| 入力日（日） | 2 | 30 | 18 |
| 氏名 | 7 | 10 | Excel 太郎 |
| 生年月日 | 7 | 25 | 1970年1月1日 |
| 住所 | 9 | 10 | 東京都 XX区 １２３４５ |
これでExcel方眼紙のデータを読み込む準備ができました。さっそく氏名データを読み込んでみましょう。
列の追加 → カスタム関数の呼び出し
カスタム関数の呼び出し画面では以下のように設定します。
新しい列名： 氏名
関数クエリ： fnGetValue
myTable： Data （左側のアイコンを列名にしてください。）
myRow： 7
myCol： 10
これで氏名としてExcel方眼紙のデータ取り出しに成功しました！
同じ要領で他の項目を追加していきます。
必要なデータはすべて準備できましたので、仕上げにかかります。
まず、分割入力された入力和暦、入力月、入力日を結合して日付型データにします。
列の追加 → カスタム列 で入力年月日として以下の計算式を入力します。
"令和" & Text.From([入力和暦]) & "年" & Text.From([入力月]) & "月" & Text.From([入力日]) & "日"
入力年月日が追加されたら型変換で日付を選びます。
日付型データに変換されました。
ここまでできたら以下の列をそれぞれ型変換し、それ以外の列は削除します。
Name： テキスト
氏名： テキスト
住所： テキスト
入力年月日： 日付
生年月日： 日付
※ Nameはファイル名になりますので、意味を持たせる名前にしてデータとして利用するなど状況に応じてご使用ください。
ここまで来たら完成です。ホーム → 閉じて読み込む でExcelのワークシートテーブルに読み込んでください。
これでExcel方眼紙を一括データ化できました！皆さんを悩ませ、時に日本人の文化論にまで発展するネ申Excelですが、PowerQueryさえあれば比較的簡単にデータ化が可能です。
制限事項
上記手順でデータ化したExcel方眼紙ですが、以下のようなケースはデータ化は対応が難しいのでご注意ください。
フォーマットが固定されていないファイル
今回のアプローチは、あくまでどの座標にどのデータがあるかが固定されているケースにのみ通用しますので、ファイルごとに座標がズレていると上手くいきません。
Excelファイルを緩く作っておくと、入力者が自由にレイアウトを変更してしまう可能性があるので、セルのロックやワークシートの保護を使ってフォーマットを固定してから配りましょう。
以下、参考記事です。
modernexcel7.hatenablog.com
そのほか
なお、Excel方眼紙と同じくしばしば話題になるセルの結合についてですが、こちらもPowerQueryでデータ化が可能です。以下記事を参考にしてください。
modernexcel7.hatenablog.com
