小規模な書類の管理ならExcelで台帳を作って管理をすることもよくある。
通常、台帳に記載される項目と帳票に記載される項目は共通するものが多くあるので、別々に作成すると手間が増える上に間違いが発生する原因にもなる。
今回は、XLOOKUP関数とFILTERを使って台帳の情報を帳票に自動的に表示させることで二重入力の手間を省く。
XLOOKUP関数とFILTERの使い方は下記で解説済である。
サンプルデータ
このメモで使用したデータは下からダウンロードできる
台帳と帳票の概要
今回は、売上台帳と顧客データベースから売上No.を選んだら自動で領収書を作成する。
売上台帳
売上台帳はテーブルで作成されている。テーブル名は「売上データ」である。
このテーブルは、売上No.、売上日、顧客、商品名／品名、数量、単位、単価、金額の項目がある。
顧客データベース
顧客データベースもテーブルで作成されている。テーブル名は「顧客データ」である。
このテーブルは顧客名、郵便番号、住所、部署、担当者の項目がある。
領収書
売上データと顧客データから作る領収書は下図である。
領収書No.を選択すると売上データと顧客データから自動的に必要な情報が入力される。
領収書の設定の詳細
領収書で入力が必要なのは領収書No.（青枠）のみである。
このNo.が決まれば、残りの項目は自毒的に表示させる。
赤枠は今回の本題であるXLOOKUP関数とFILTER関数を使って台帳から情報を抽出している。
黄枠は簡単な関数が入力して自動計算している項目である。
XLOOKUP関数で会社名を抽出
会社名(赤枠)は領収書のNo.（青枠）を検索値として売上データからXLOOKUP関数で抽出する。
=XLOOKUP(G2,売上データ[売上No.],売上データ[顧客])
XLOOKUP関数で住所と担当者を抽出
「住所」と「担当者」（赤枠）は、前項で抽出した会社名を検索値として顧客データからXLOOKUP関数で抽出する。
フォーマットの見栄えを整えるために文字列やXLOOKUP関数を&で結合させている。
=”〒”&XLOOKUP(B5,顧客データ[顧客名],顧客データ[郵便番号])&” ”&XLOOKUP(B5,顧客データ[顧客名],顧客データ[住所])
=XLOOKUP(B5,顧客データ[顧客名],顧客データ[部署])&” ”&XLOOKUP(B5,顧客データ[顧客名],顧客データ[担当者])&” 様”
FILTER関数で領収書明細を抽出
領収明細（赤枠）は領収書のNo.（青枠）を検索値として売上データからFILTER関数で抽出している。
=FILTER(売上データ[[商品名 ／ 品名]:[金額]],売上データ[売上No.]=G2,””)
領収明細の項目の並び順と、台帳の項目の並びが同じになっているので、１つのFILTER関数で全て表示できる。
並び順が違う場合は、一列毎にFILTER関数の設定が必要となる。
その他の設定
黄色の部分のその他の設定です。
発行日は「TODAY関数」のみで、書式で年号が令和で表示されるようにしてある。
合計金額は最終行の合計の値と文字列を＆で繋いでいるだけである。
=”合計金額： \”&G30&”-“
小計（税抜）は全てのNo.の範囲をSUM関数で足し算している。
消費税（10%）は小計に0.1をかけているだけである。
合計(税込)は小計（税抜）と消費税（10%）を足している。
