前回の売上明細から自動で請求書を作る１ Excel(エクセル)から引き続いて、請求書を作成していきます。
請求書に明細を載せる(関数を使う)※スピル使用
「FILTER」関数の使い方
※こちらはスピル機能を使うのでMicrosoft365またはExcel2021以降の必要があります。
この方法は「FILTER」関数を使います。
「B15」セルにこのように記載します。
=FILTER(売上明細!$D$3:$D$19,売上明細!$C$3:$C$19=$B$6,"")「売上明細!$D$3:$D$19」は「売上明細」シートに移動し、キーとなる請求先列を選択します。
「売上明細!$C$3:$C$19」は「売上明細」シートの表示させたい品名の列を選択します。
「$B$6」は「請求書」シートの請求先名のセルを選択します。
関数の入力が終わったら「エンター」キーを押します。
すると「B15」セルにしか関数を入れてないのに「B15」セルから「B19」セルまで値が表示されました。このデータは「売上明細」の請求先が「AAA」の売上データと一致していることがかくにんできるはずです。
同様に「数量」・「単価」・「金額」の列についても「FILTER」関数を入力します。
これで請求先ごとの売上明細が完成しました。
「FILTER」関数のメリット
- 関数の作り方が非常にシンプルで簡単に作ることができます。
- 使っていくうちに表示項目を変更したり、追加する場合にメンテナンスが簡単。
「FILTER」関数のデメリット
- スピル機能を使うのでMicrosoft365またはExcel2021以降でなければ対応できません。これ以前のExcelの場合はこの後ご紹介するスピルを使わない方法かマクロを使うしかありません。
- 表示する行数が増えてリストの行数をオーバーしてしまった場合はエラーになってすべて表示されなくなってしまいます。この可能性がある場合は別途対策を検討する必要があります。
請求書に明細を載せる(関数を使う)※スピル使わない
これはちょっと強引無いやり方であまり好きではありませんが、スピルを使えるようになる以前でマクロを使いたくないときはよくこの方法を使っていました。
「売上明細」シートに検索キー用の列を挿入する
請求先ごとに抽出したいですが、順番を付けるために請求先の隣に2列挿入します。
挿入したい列の右側2列を選択した状態で「右クリック」→「挿入」を選択
すると「D」「E」列に空白の列が挿入されました。
これは必ずではないですが、分かりやすくするために「D3」セルに「連番」、「E3」セルに「結合」と入力します。
「D４」セルに下のように入力します。この時「C3」の「3」の前にだけ「$」マークを付けてください。
=COUNTIFS(C$3:C4,C4)そして「E4」セルに下のように入力します。
=C4&D4ここでも関数を入力したセルは青字になるようにしておくと分かりやすいです。
「D4」セルから「E4」セルを選択した状態で右下の部分をダブルクリックします。
するとデータの入っている一番下の行まで関数が自動的に入力されました。
この時「D4」セルと「D9」セルに入力されている関数を比較してみると一つだけ「$」マークを入れたことによって入力されている範囲が変わっていることが分かります。これによって行によって請求先が同じ場合に順番を付けることが出来るわけです。
ここまでで、「売上明細」シートの準備は完了です。
「請求書」シートに明細を表示する
ここではINDEX・MATCH関数を使って表示していく方法を解説します。INDEX・MATCHの詳しい使い方はこちらも参照してみてください。Excel(エクセル)INDEX関数とMATCH関数の組み合わせた使い方
「請求書」シートの「B15」セルにこの関数を記載します。
=INDEX(売上明細!F:F,MATCH($B$6&ROW()-14,売上明細!$E:$E,0))
売上明細!F:Fは「売上明細」シートの品名の列、売上明細!$E:$Eは先ほど作った検索するキーとなる列を参照しています。
売上明細!$E:$Eはキーとなる列なので他のセルでも同じ列を参照してほしいので「$」マークを付けています。
$B$6&ROW()-14は一番ややこしいですが、「$B$6」が請求先名、今回は「AAA」を参照し、「ROW()-14」は今入力しているセルの行数(15行目)から14を引いて「1」となるようにしています。
これを＆でつなぐことで「AAA1」を表現しています。これが「売上明細」シートの結合列の値と一致するわけです。
ここまで入力して「エンター」キーを押すと
この数式を入力したセルをコピーし、式を入れたい下の部分を選択して「数式」を貼り付けします。
この状態ですとデータのない行では「#N/A」のエラーを返してきます。
これを回避するために「IFERROR」関数で挟みます
=IFERROR(INDEX(売上明細!F:F,MATCH($B$6&ROW()-14,売上明細!$E:$E,0)),"")この関数の意味は上記式の黒字部分がエラーでなければ式の値を返し、エラーだったら「””」空白を返す。という式です。これを同様に下までコピーすると
値のあるセルは値が表示され、値のないセルは何も表示されないという状態になりました。
あとは同様に「B15」から「B39」を選択し、右下の四角を表の終わりまでドラッグします。
すると表の全体で値が正しく表示されました。
※今回はたまたま「請求書」シートの列の並び順と「売上明細」シートの列の並び順が同じだったので、右にドラッグしてくるだけで正しく表示されましたが、もし並び順が違う場合は1列ずつ関数の調整が必要です。
スピルを使わない方法のメリット
- スピル機能を使わないので、Microsoft365またはExcel2021以前のバージョンでも利用可能。
スピルを使わない方法のデメリット
- 売上明細に余分な関数行が必要になり、行追加をした際に関数をコピーする手間が発生する
- 売上明細の関数が壊れていると請求書が正しく表示できなくなってしまう。
マクロを使う場合
少々長くなってきましたので、マクロを使う場合と最後の仕上げは次回またご紹介させていただきます。
コメント