「いくつかの関数だけ」で請求書が作れる
Excelで請求書を作成するという方は割と多いのではないでしょうか?
個人的な経験で恐縮ですが、以前に商工会でExcel勉強会をさせていただいたとき、多くの方がExcelベースの作業をしておられ、その作業の中に「請求書を作成」しているという方も多くいらっしゃったのを記憶しております。
定型的な作業といえ手間がかかる請求書ですが、ここで挙げた内容を踏まえておくと作りっぱなしで、請求データさえ入力しておけばいつでも手軽に請求書が作れます。
なお、このサンプルをダウンロードできるようにしています。
これを見ながら実際に触ってもらえれば理解も早いと思います。
今回の請求書は「こういうとき」に役に立つ
一度きりのお客様ならば、請求書の発行は一回のみです。であるならば、無理やり手作業で作成してもよいかもしれません。
と、このような目線でみたとき、今回の方法は以下の条件下では役に立つと思います。
- 請求書の作成が定例作業である
- 複数の請求先がある
- 請求書の内容は明細と請求先が異なるが作り(構成)は同じ
多くの企業ではほとんど当てはまると思います(笑)。
ですので使える方法と思います。
なお、タイトルで「関数だけを・・」と謳っておりますのは、「マクロを使ってない」ことと対比させているためです。
(シートではセルの色、罫線なども入れております。これらの機能も使っていますのでよろしくお願い致します・・)
また、今回の請求書作成では、請求内容によっては問題となりうるのが「明細行」です。ダウンロードのファイルでは10明細(10行)分の枠を用意しています。1つの請求書に11個以上の明細があると、この請求書では11個目以降が漏れてしまいます。
このため、事前に最大何明細分の枠が必要なのか確保しておく(その行数分だけ用意する)ことが必要になります。
関数だけを使ったこの請求書のファイルでできること
このシートでできることは、タイトルの通り請求書を作ることなんですが、メリットとしては、1度シートを作れば、後はその請求書シートを使い回すことができます。
後述する請求データさえ消さなければ、過去にさかのぼって請求書を作成することもできます。
言い換えると、請求書(シート)を作った後、それを「シートのまま」で保存するとなれば、メリットというか旨みはほぼなくなります。
「シートのままで」ではなく、印刷やPDFとしたものを保存、保管できるのであれば、このファイルは利用価値が高いと思います。
使い方は1つのセルに値を入れるだけ
以降に説明しているデータの入力等ができていれば、あとは請求書を発行したい検索値の入力です。
この辺はサンプルをダウンロードして見たほうが分かりやすいと思います。
なお、無用に面倒な部分がここの箇所でもあります。(後述する請求書データシートからコピーし)セルの値を貼り付けようとするとセルの結合をしているためそのまま貼り付けられないのですね・・
セルを解除すれば解決しますが、こうすると画像の説明が少し見にくくなりそうでセルを結合しました。
ここは見た目を重視しました。
(参考)
ポイントは「2つのシート」を用意すること
私自身が色々なお客様のところで見てきた「Excelで作った請求書」は、多くは請求書シートにそのまま明細を書き込み、金額の合計部分のみSUMなど使っています。そして翌月にはそのシートをコピーして使う・・
この方法自体に問題があるというわけではないですが、関数を活かせる部分が狭く、入力値もセル位置を確認しつつで堅苦しく感じます。
今回の方法は、このような手間とミス(誤入力)を減らすということに繋がりやすくなるため有効だと思います。
ということで、今回はこの方法を実現するために「2つのシート」を使います。
今回の請求書では以下のようにシートを作っておきます。
- 請求書印刷
- 請求書データ
なお、この2つのシート構成にすることは請求書作成に限った話ではないです。
いろいろな集計(レポート作成)にも役立ちますので覚えておいて損はないと思います。
2つのシートの連携は「関数」で行う
上記のようにシートを2つに分けることで、「印刷(レイアウト)」と「データ」で2つの固まりにすることができます。
こうすると各シートに「役割を分担できる」という面でやりやすくなるんですね。
ただ、ここで問題となるのが2つのシート間の「繋がり」ですね。
それぞれ独立したシートとなっているため何ら繋がりは持っていません。そのためシート間の繋がりを作ってあげる必要があります。
これは関数で解決できます。関数でつなげます。
つまり極端な言い方をすると・・
- 請求書印刷 → 関数・数式だけ入力しておく
- 請求書データ→ 関数・数式の参照元となる(請求)データだけを入力しておく
という形なります。
例外もありますが、通常は関数を使うとき「どこかのセルを指定し」、「そのセルの値を使い」、「その結果を返し」ます。
この請求書もこの仕組みを使います。
となると、請求書印刷のシートには、「どんな繋がりを持つ関数を入れておくと良いのか」という流れになりますよね。
そして、それらの関数がどこのセルを指定(参照)するのかということもですね。
はい、以降よりそれらについて説明いたします。
関数の中心はVLOOKUP
今回は割とメジャーどころである(と勝手に思っております)VLOOKUP関数を中心として請求書印刷シートに埋め込んでいきたいと思います。(他の関数でも使用できますし、そのほうがより便利と思います)
VLOOKUPのおさらい
まずはVLOOKUPのおさらいですが、この関数のよいところは、検索のため縦と横に動いてくれます。さらに見つけた「セルの横列」の値を表示(返す)してくれることですね。
請求書印刷シートでもこの動きを使います。
このVLOOKUPについては、いくつか当サイトでも触れていますので、関心ある方は関連ページからご確認くださいませ。
つまり、VLOOKUPが持ってくるデータ(シート)側に、この特有の動きが活用できるような形(フォーマット)にするということですね。
VLOOKUP関数の仕様に対応するために変更するのはデータの方
VLOOKUPは今回の請求書でも期待した動きをしてくれます。
しかし、、、一点問題があります。
下の画像を見れば分かるように、VLOOKUPで抽出したい値で、明細の部分に「複数あるケース」が起こりえます。しかし、複数のデータがあっても表示されるのは1件のみなんですね。
これはVLOOKUP関数の仕様ですね。仕様なだけにしようがない・・・失礼しましたー。
そのため「請求書データ」シート側で、その問題を回避できるように加工しています。
(これはVLOOKUP以外の他の関数を使えば回避できると思います)
実際、このために請求書データの方では複数の値を考慮して被らない値を作っています。それは、VLOOKUPが「1件だけ見つけられる(表示できる)」ようにするためです。
上記の画像では、「2種類のデータ」が含まれていることが分かると思います。
- 請求書に必要なデータ(会社名、金額・・)
- VLOOKUP関数を使うために必要なデータ
ポイントは「2」ですね。「検索値1」と「検索値2」。
今回の請求書シートにVLOOKUPを使うことを考慮したとき、シート同士を「繋げる」役割、そして該当する複数のデータから1つに絞る(検索値2)ために必要なわけです。
この「仕掛け」はVLOOKUPを使うためのキモにもなりますのでもう一度説明しますね。
VLOOKUPを使って表示できる結果は「1件」だけです。しかし請求書の明細(〇〇出版2020年1月)は「3件」あります。
このため、(検索値1だけだと)2件目、3件目が表示できなくなります。
これを回避するために、検索値2という列を作っています。検索値2の値を使えば、VLOOKUPを使っても1行が特定できます。
つまり請求書印刷シート側の「明細部分」に入れているVLOOKUPは「検索値2」を使って検索、値を表示しています。
そして、この2件目、3件目のデータを見つけるため、請求書印刷シート側で、この複数の明細に対応できるようVLOOKUPの検索値で2つのセルを「1つの文字として検索値として指定」しています。
このような方法を苦肉の策と取るのか、あるいは工夫の賜物と取るのか、それは皆さんの判断にお任せします(笑)。が、いずれにしても、今回の請求書作成では、このようにすることでVLOOKUPでも対応できるようにしています。
VLOOKUP以外の関数
関数は、VLOOKUP以外の関数も使っています。といっても、ある意味VLOOKUPのために使っているともいえますが。。
VLOOKUPでは値が見つからないとエラーが出ます。
どこでエラーが出るのか・・それは「明細」部分ですね。サンプルでは3件(3行)分まで表示しています。しかしVLOOOKUP関数は10行分入っています。これを回避していないケースでは次のように表示されます。
ここでIFERRORで、「エラーならば表示しない」という結果になるように入力しています。
同様に明細のVLOOKUPがエラーになると、それに引きずられて金額部分もエラーになります。
このようにエラーとなり得る箇所にはIFERRORを入れ、エラーは表示しないように設定しています。
(ダウンロードのサンプルで見れば分かると思います)
一度作成すれば、以降は請求書データだけを入力するだけ
一度、請求書印刷シートを作ると、以降は請求書データを入力していくだけです。
これはもう単純に表の下に追加していくだけですね。
VLOOKUP用に追加したセルは式が入力してあるのでそのままドラッグでコピーできますね(通し番号は手入力が必要です)。
これから分かるのは、請求書印刷シートに直接データを入力するよりも簡単であることではないですかね。
(少なくとも私はそのように思います)
その他、まとめ
その他の関数では、請求書データの部分で日付がシリアル値表示されるのを防ぐためTEXT関数を使っていますが、実質VLOOKUPメインです。
言い換えると、VLOOKUPの仕様がわかっていると、この請求書で何をしているのかが把握できると思います。
今回に限らず使用頻度が高いVLOOKUPです。これを機に覚えてみるのはいかがでしょうか?