問い合わせ・自己紹介プロフィールサイトマップAIに仕事を奪われないためのサラリーマン経理・総務の働き方を模索するサイトです定期的に勉強会や交流会を開催!2019/12/19「バックオフィス忘年会」を開催しました。36名の方に参加していただきました。・出力されるデータが実質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項目。元ファイル読み込む文字コードを選択します。基本的にはExcelが自動的に選択してくれますが、必要に応じて変更しましょう。今回は「シフトJIS」になっていました。区切り記号どの記号で区切るかを設定をします。「固定幅」これらでも区切ることが可能です。データ型検出データ型検出は、Excelが取り込まれたデータをもとに自動的に判定してくれるというものです。区切り記号を「コロン」に変更しました。今回は「コロン」で区切りたいための処理ではなく、区切らないために、データの中にない記号を選択しました。先程まで分割されていたデータは1列データに変換されました。「データの変換」をクリックします。Queryエディターが開きます。データが1列で読み込まれました。ステップには「変換された型」が追加されました。データ型検出についてデータ型検出について、どのように違いが出るのか比較して見てみましょう。比較するのは「最初の200行に基づく」と「データ型を検出しない」。「ホーム」→「列の分割」→「文字数による分割」を実行します。文字数による分割その名の通り、何文字数えたら列を区切るかというものです。分割:1回のみ区切るのか、複数回分割するのかです。「繰り返し」を選択します。詳細設定オプションを開くとより詳細な設定が可能です。「分割後の列数」の入力ボックスが表示されます。最初に表示される数字は、選択した列を指定した文字数ですべて分割した場合の数字です。この数字は変更可能です。「120」「120」「120」「残りのデータ」この場合、分割後の列数を「4」とします。分割した列を行へと入れ替えての処理がされます。「分割後の列数」の入力ボックスが表示されません。※今回の処理は本来「行」を選択した方が望ましいですが、機能紹介のため列を選択しての説明にしています。1列が120文字数ずつ、8列に分割されました。ステップに「位置によって分割された列」が追加されました。行と列の入れ替え「変換」→「入れ替え」を実行します。ステップに「転置されたテーブル」が追加されました。分割され8列になっていたデータが1列8行になりました。ステップに「転置されたテーブル」が追加されました。7行目のデータが「8.00003E+54」とおかしくなっています。これはステップ「位置によって分割された列」の次のステップに「変更された型1」が原因です。列を分割した際に、自動的にデータが変換され、このステップが追加されますので削除します。ステップの削除ステップに「変更された型1」を選択します。ステップを遡ることになりますので、「転置されたテーブル」よりも前のステップを処理することになりますので、1列データが8列のデータに戻りました。「変更された型1」の左横にある「×」をクリックします。「ステップの削除」のウィンドウが表示されます。実際に影響が出た場合、削除を取り消しすればエラーの対処できますので、とりあえず試してみましょう。7行目のデータが変わりました。「列の分割」はこの後何回も実行します。そのたびに「変更された型」というステップが自動的に挿入されますので、その都度削除しましょう。先頭文字のみ抽出する「変換」→「列の分割」→「文字数による分割」レコードの先頭数字がレコードの種類を表していますので、先頭数字のみ抽出し列を作ります。先頭数字のみ抽出されました。「変換された型1」の左側にある×ボタンをクリックします。先頭数字のみ抽出されました。「変換された型1」を削除されました。選択した列で値を別の値に置き換えます。「置換後」に置き換え後の値を入力します。eLTAXフォーマットでは「2」ではなく「3」ですので検索する値:2置換後:3値が置換されました。条件列の追加データのうち編集するのは先頭文字が3(置換前は2)のみです。編集するデータと編集しないデータでそれぞれ列を追加します。「Column1.2」を新しい列として追加するColumn1.1が3でないColumn1.2のみのデータが新しい列として作成されました。Column1.2Column1.1が3であるColumn1.2のみのデータが新しい列として作成されました。編集部分(データレコード)を分割する先頭文字はすでに分割していますので、2文字目からやっていきます。表のうち、eLTAXで利用するのは黄色の項目、灰色項目は不要ですので削除します。項目名:市区町村コード文字数:6分割:できるだけ左側で1回6文字数分のデータが分割され、列ができました。ステップに「位置によって分割された列」が追加されると同時に「変更された型」が追加されます。これは削除しましょう。削除方法は「変更された型」を選択すると左側に×ボタンが表示されますので、クリックします。「変更された型」は毎回出てきますので、その都度削除します。左が変更された型を削除する前、右が変更された型を削除した後です。ヘッダーの左部分が「123」「ABC」となっているのがわかります。表に従って分割していきます。使わない列は後で削除しますので、ヘッダー名を変更しておくといいです。ヘッダーをダブルクリックすると編集状態になりますので変更できます。削除したい列を選択した状態で、「ホーム」→「列の削除」→「列の削除」をクリックします。不足データの追加とデータ結合、完成まで地銀協フォーマットで利用する部分のみ抽出しました。eLTAXフォーマットに必要な列を追加していきます。以下のステップで処理していきます。****(後で置換する)出力を「****」とするのは、作成したテキストデータで置換して納付月に変更するので、とりあえず設定します。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フォーマットに変換する方法の紹介でした。利用ケースはないとは思いますが、パワークエリの機能を色々利用しているので、実践練習には持ってこいかなと思います。Query(パワークエリ)で複数シートを1つのシートにまとめて新しいデータを作成する方法NEXT飲みすぎて帰れなくなったのでカーシェアをホテル代わりに。15時間2,640円~で使える。経理をメインに、総務をちょこっと、Excelやクラウドサービスなどについて書いています。会社の仕事はブログのネタと思ってやってます。↓ブログきっかけで出版した本です。利益最大化×EXCELピボットテーブルを学習したい人にピボットテーブルを未利用の方からすでに使いこなしている人にもおすすめです。84,400ビューVLOOKUP関数は「Excelが使える」の指標。24,800ビューピボットテーブルで活用したい5つの機能。問い合わせ・自己紹介testaa経理と総務の効率化AIに仕事を奪われないためのサラリーマン経理・総務の働き方を模索するサイトです定期的に勉強会や交流会を開催!2019/12/19「バックオフィス忘年会」を開催しました。36名の方に参加していただきました。