以前は「配車表から請求書作成」記事で紹介したようなしくみも作っていましたが、今は、Excelパワークエリや新しい関数を活用して、もっと、手際よく請求書を用意できるようになりました。
↓以前の記事
ここでは、データが2行ずつ並ぶ複数のシートを全てひとつのシートにまとめて、2行のデータを1行のデータに変換し、得意先ごとの請求明細を組立てる手順の一例を記します。
2行ずつのデータが並ぶ複数シートのExcelファイルが元
分かりにくい見出しなので、どういうことか、下図の具体例で説明します。ある配送業者は、運行管理者が、下図のような配送シートをExcelで作っています。
実際の配車表は、項目もシート数ももっと多いものですが、ここでは省略して説明します。配車内容はそれぞれ2行のデータとして記録されています。タブ「1」は、1日、「2」は2日、「30」は30日というように、日別の配車内容がそれぞれのシートとなっています。ひと月28シートから30シートになり、月ごとにファイルが分かれるといういうわけです。
以上のシートが含まれる「11月配車明細」というxlsxファイルを使って、これらのデータから取引先別請求明細を組立てる一例を説明していきます。
パワークエリで全シートのデータをまとめて、1行ずつのデータを作る
「11月配車明細.xlsx」からデータを取り込む
新しいExcelファイル（ここでは「請求明細.xlsx」と命名）を作り、「データの取得」で、上記の「11月配車明細.xlsx」を指定して、インポートすると、右図のナビゲータ画面が表示されます。
左の欄に、各シートのタブ名も表示されていますが、シート名でなく、「11月配車明細.xlsx」と記されたファイル名をクリックし、右下の「データの変換」をクリックします。
パワークエリで2行のデータを1行のデータに並び替える
Power Query エディターが開きます。使うのは「Data」列だけなので、他の列は削除して、「Data」を展開します。
Column2は、元データの「業者名／車両No」項目の列です。元シートの日付の行と空白の行が「null」値になっています。これらの行は不要なので、列フィルターで「null」値を削除します。
⇩
「列の追加」メニューから、1から始まるインデックス列を追加し、カスタム列で、そのインデックス列＋１を計算する列を追加します。
「ホームメニュー」の「クエリのマージ」をクリック、マージ画面が開きます。11月配車明細.xlsxの「インデックス」列と同じファイルの「カスタム列」をマージします。
「追加されたカスタム」列を展開すると右側に列が追加されます。
ここで、「ホーム」メニューから「１行目をヘッダーとして使用」をクリックします。「インデックス」列の列名が「2」に変わっていますが、この「2」列を選択し、「列の追加」メニューの「情報」から、「偶数」をクリックすると、元のインデックスが偶数の行に「TRUE」奇数の行には「FALSE」と表示されます。
「偶数」列をフィルターで「FALSE」を非表示にし、元データの項目名が列名になっている列ならどこでもいいのですが、例えば、「車両No」列を選択、フィルターで、残っている項目名「車両No」を消します。
列名が「Column1」や「2」など、元データの項目名と異なる列を全て削除し、見やすいように列の順番を入れ替えれば、全シートの、２行だったデータが１行に置き直された一覧が出来上がります。
FILTER関数で取引先ごとの請求明細を抽出
ここでは、上記でできたテーブル名を「請求明細」としています。このテーブルから、取引先ごとに請求明細を抽出します。と言っても、別シートにひな形を用意して、FILTER関数を入れるだけです。
上図、「A請求」シートに、お決まりの文言などでひな形を作り、A13セルにFILTER関数を入れます。
=FILTER(請求明細,請求明細[得意先名]=A2,””)
「請求明細」テーブルの、「得意先名」列で、A2セル（「A」株式会社）に該当する値を表示する、という関数です。A13セル、一か所だけに用意するだけで、該当する値が全て表示されます。
この内容をコピペして、B株式会社など別の取引先でシートを用意しておけば、上記のデータを更新するだけでいつでも、すぐにこの明細を表示して利用できます。
どうもお疲れさまでした。
今の仕事や経営のためのExcel活用プロモーティング
アナタの今の仕事や経営のためのExcel活用を後押しします。Excelを使って、もっと楽に、楽しく仕事に取り掛かれるようにするプロモーティングです。お気軽にご連絡ください。
