AIに仕事を奪われないためのサラリーマン経理・総務の働き方を模索するサイトです

経理と総務の効率化

定期的に勉強会や交流会を開催!


2019/11/22(金)に「経理雑談交流会」を開催します。
募集ページ→「こくちーずプロ

ExcelのPower Query(パワークエリ)

ExcelのPower Query(パワークエリ)地銀協フォーマット(全銀データ)をeLTAXフォーマットに変換する方法

更新日:

かなりマニアックな使い方ですが・・・

スポンサーリンク

やりたいこと

・給与奉行から出力できる銀行振り込みデータを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フォーマットに変換する方法の紹介でした。

あまり利用ケースはないとは思いますが、パワークエリの機能を色々利用しているので、実践練習には持ってこいかなと思います。

おすすめExcel本(知り合いの羽毛田さんが書いた本です)
おまけ(私が執筆した本です)

03投稿記事下_関連コンテンツ

-ExcelのPower Query(パワークエリ)

Copyright© 経理と総務の効率化 , 2019 All Rights Reserved.