かなりマニアックな使い方ですが・・・
目次
やりたいこと
・給与奉行から出力できる銀行振り込みデータをeLTAX形式に変更したい
・eLTAX形式に変更することで、銀行振込でないeLTAXでの処理が可能になる
問題点
・出力されるデータが実質1行になっている
・4区分のレコードのうち、1レコードを編集したい
・データレコードで異なるデータ区分が多々ある
出力されるデータが実質1行になっている
給与奉行から出力される銀行振込用データ(地銀協データ)は実質1行データになっており、このデータのままだと編集することが難しい。
完成データは1行あたり120文字のデータ。
4区分のレコードのうち、1レコードを編集したい
地銀協フォーマットはデータの1レコードあたり固定長120バイトと決まっています。
データは4つのレコードに分類されます。
1.ヘッダーレコード(1から始まる)
2.データレコード(2から始まる)
3.トレーラーレコード(8から始まる)
4.エンドレコード(9から始まる)
このうち編集したいのは2から始まるデータレコードのみ。
データレコードで異なるデータ区分が多々ある
編集したいデータレコード。
左側が地銀協フォーマット、右側がeLTAXフォーマット。
地銀協フォーマットで削除したい部分が灰色、eLTAXフォーマットで追加したい部分が黄色。
これらを削除したうえで、追加する必要があります。
操作手順
データの取り込み
新規Excelを開き、「データ」→「テキストまたはCSVから」をクリックします。
読み込む方法を選ぶウィンドウが開きます。
読み込み方法で設定するのは3項目。
1.元ファイル
2.区切り記号
3.データ型検出
元ファイル
読み込む文字コードを選択します。
基本的にはExcelが自動的に選択してくれますが、必要に応じて変更しましょう。
今回は「シフトJIS」になっていました。
区切り記号
どの記号で区切るかを設定をします。
コンボボックスから選択できるものだけでなく、
「カスタム」
「固定幅」
これらでも区切ることが可能です。
データ型検出
データ型検出は、Excelが取り込まれたデータをもとに自動的に判定してくれるというものです。
データ型は主に
「文字列」
「数値」
「日付」
があります。
区切り記号を「コロン」に変更しました。
今回は「コロン」で区切りたいための処理ではなく、区切らないために、データの中にない記号を選択しました。
先程まで分割されていたデータは1列データに変換されました。
「データの変換」をクリックします。
Power Queryエディターが開きます。
データが1列で読み込まれました。
ステップには「変換された型」が追加されました。
データ型検出について
データ型検出について、どのように違いが出るのか比較して見てみましょう。
比較するのは「最初の200行に基づく」と「データ型を検出しない」。
違いについて羅列しておくと
1.1行目がヘッダーになるか
2.商品名が数値となるか文字列となるか
3.売値と仕入値が数値となるか文字列となるか
4.登録日が日付となるか文字列となるか
読み込みビュー
上:最初の200行に基づく
下:データ型を検出しない
Power Queryエディター
上:最初の200行に基づく
下:データ型を検出しない
データの行と列の入れ替えと編集部分の抽出
データを選択した状態で、
「ホーム」→「列の分割」→「文字数による分割」
を実行します。
文字数による分割
その名の通り、何文字数えたら列を区切るかというものです。
文字数:地銀協データは120文字を1行あたりのデータとしていますので、「120」と設定
分割:1回のみ区切るのか、複数回分割するのかです。
「繰り返し」を選択します。
詳細設定オプションを開くとより詳細な設定が可能です。
分割の方向
・列を選択した場合
「分割後の列数」の入力ボックスが表示されます。
最初に表示される数字は、選択した列を指定した文字数ですべて分割した場合の数字です。
この数字は変更可能です。
例えば
「120」「120」「120」「残りのデータ」
この場合、分割後の列数を「4」とします。
・行を選択した場合
分割した列を行へと入れ替えての処理がされます。
「分割後の列数」の入力ボックスが表示されません。
※今回の処理は本来「行」を選択した方が望ましいですが、機能紹介のため列を選択しての説明にしています。
1列が120文字数ずつ、8列に分割されました。
ステップに「位置によって分割された列」が追加されました。
行と列の入れ替え
「変換」→「入れ替え」を実行します。
ステップに「転置されたテーブル」が追加されました。
分割され8列になっていたデータが1列8行になりました。
ステップに「転置されたテーブル」が追加されました。
ただ7行目のデータが「8.00003E+54」とおかしくなっています。
これはステップ
「位置によって分割された列」
の次のステップに
「変更された型1」
が原因です。
列を分割した際に、自動的にデータが変換され、このステップが追加されますので削除します。
ステップの削除
ステップに「変更された型1」を選択します。
ステップを遡ることになりますので、「転置されたテーブル」よりも前のステップを処理することになりますので、1列データが8列のデータに戻りました。
「変更された型1」の左横にある「×」をクリックします。
「ステップの削除」のウィンドウが表示されます。
『ステップを削除すると後続のステップに影響する場合があります』
実際に影響が出た場合、削除を取り消しすればエラーの対処できますので、とりあえず試してみましょう。
7行目のデータが変わりました。
「列の分割」はこの後何回も実行します。
そのたびに「変更された型」というステップが自動的に挿入されますので、その都度削除しましょう。
先頭文字のみ抽出する
「変換」→「列の分割」→「文字数による分割」
レコードの先頭数字がレコードの種類を表していますので、先頭数字のみ抽出し列を作ります。
「変換」→「列の分割」→「文字数による分割」
文字数:1
分割:できるだけ左側で1回
先頭数字のみ抽出されました。
「変換された型1」の左側にある×ボタンをクリックします。
先頭数字のみ抽出されました。
「変換された型1」を削除されました。
値の置換
選択した列で値を別の値に置き換えます。
「検索する値」に置き換えたい値
「置換後」に置き換え後の値
を入力します。
eLTAXフォーマットでは「2」ではなく「3」
ですので
検索する値:2
置換後:3
値が置換されました。
条件列の追加
データのうち編集するのは先頭文字が3(置換前は2)のみです。
編集するデータと編集しないデータでそれぞれ列を追加します。
編集しないデータの列を追加
設定条件
列名:Column1.1
演算子:指定の値に等しくない
値:3
出力: Column1.2
説明:「Column1.1」が「3」と「等しくない」場合、 「Column1.2」を新しい列として追加する
Column1.1が3でないColumn1.2のみのデータが新しい列として作成されました。
編集するデータの列を追加
設定条件
列名:Column1.1
演算子:指定の値に等しい
値:3
出力: Column1.2
Column1.1が3であるColumn1.2のみのデータが新しい列として作成されました。
編集部分(データレコード)を分割する
先頭文字はすでに分割していますので、2文字目からやっていきます。
表のうち、eLTAXで利用するのは黄色の項目、灰色項目は不要ですので削除します。
列の分割
項目名:市区町村コード
文字数:6
分割:できるだけ左側で1回
6文字数分のデータが分割され、列ができました。
ステップに
「位置によって分割された列」
が追加されると同時に
「変更された型」
が追加されます。
これは削除しましょう。
削除方法は「変更された型」を選択すると左側に×ボタンが表示されますので、クリックします。
「変更された型」は毎回出てきますので、その都度削除します。
左が変更された型を削除する前、右が変更された型を削除した後です。
ヘッダーの左部分が「123」「ABC」となっているのがわかります。
これは「123」が数値、「ABC」が文字列という意味です
項目名:市区町村名
文字数:15
分割:できるだけ左側で1回
※不要な項目ですが、その後の必要データを抽出するのに必要となるので分割
表に従って分割していきます。
列の削除
使わない列は後で削除しますので、ヘッダー名を変更しておくといいです。
ヘッダーをダブルクリックすると編集状態になりますので変更できます。
変更していきます。
削除したい列を選択した状態で、「ホーム」→「列の削除」→「列の削除」をクリックします。
不足データの追加とデータ結合、完成まで
地銀協フォーマットで利用する部分のみ抽出しました。
eLTAXフォーマットに必要な列を追加していきます。
黄色の項目です。
以下のステップで処理していきます。
①現状のデータの後ろに列を追加
②後に並べ替え
列の追加と値の置換
納付月分の設定条件
新しい列名:納付月
列名:Column1.1
演算子:指定の値に等しい
値:3
出力: ****(後で置換する)
出力を「****」とするのは、作成したテキストデータで置換して納付月に変更するので、とりあえず設定します。
督促手数料&延滞金の設定条件
新しい列名:督促延滞
列名:Column1.1
演算子:指定の値に等しい
値:3
出力: AA(後で置換する)
出力を「AA」とするのは、この後置換して変換するために、とりあえず設定します。
「督促延滞」の列を選択した状態で「変換」→「値の置換」を選択します。
「値の置換」のウィンドウが開きますので、
検索する値:AA
置換後:000000000000000000(0を18個)
を実行します。
置換されました。
備考&ダミーの設定条件
新しい列名:備考ダミー
列名:Column1.1
演算子:指定の値に等しい
値:3
出力:空白のスペース(半角28桁分)
備考ダミーの列を選択すると、左下の部分に選択したセルの値が表示されます。
見た目はわかりませんが、マウスでなぞると色が反転しスペースがあるのがわかります。
データの並びを整える
列を選択して、ドラッグ&ドロップで列を移動させます。
2列目残っている(残っていた場合)削除します。
列を結合
Nullとなっているセルは、空のセルですので無視されます。
すべての列を選択し、右クリックを押して、「列のマージ」を選択します。
区切り文字:なし
新しい列名:結合済み(後で削除するがとりあえず)
閉じて読み込む
完成しました。
「ホーム」→「閉じて読み込む」
を実行します。
Excelに戻ってきました。
データが1列に作成されています。
Excelを名前を付けて保存します。
Excelをテキストデータ(eLTAXフォーマット)に変換
Excelで保存したら、次にテキストデータを作成します。
「名前を付けて保存」します。
ちなみにF12で「名前を付けて保存」のウィンドウが開くことができます。
ファイルの種類のコンボボックスから
「テキスト(タブ区切り)」
を選択します。
『選択したファイルの種類は複数のシートを含むブックをサポートしていません。』
とメッセージが表示されます。
複数のシートがあっても現状選択しているシートのみ名前を付けて保存されます。
テキストファイルでデータが作成されました。
このような見た目になっています。
1行目が結合済みとなっていますので、削除します。
****を置換します。
検索する文字列に「****」
置換後の文字列に「0111」※
eLTAXの仕様では「年、月」(和暦)yymm(yy:平成通年2桁とする)となっているので、それに従う。
以上で完成です。
まとめ
以上がパワークエリを利用して地銀協フォーマットをeLTAXフォーマットに変換する方法の紹介でした。
あまり利用ケースはないとは思いますが、パワークエリの機能を色々利用しているので、実践練習には持ってこいかなと思います。