2016.02.01 (Mon)

オフィスソフト使いこなし（第9回）

関数を使って請求書を作ろう

　今回は、エクセルを使った請求書の作例を紹介します。関数を使うことで、商品のコードを入力すれば商品名と単価が自動的に表示される請求書を作ることができます。
本文内では、エクセル2010を使用していますが、他のバージョンでも活用できます。

商品リストを作る

　請求書が商品名や単価を参照するための、「商品リスト」（商品の一覧表）をあらかじめ作成しておきます。商品リストには、ひとつの商品コードに対し、商品名と単価を入力しておきます。

商品リスト

シートの名前を“商品リスト”にしておきます。

商品リストの商品コードは、昇順（下に行くほど数字が大きくなる）ように並べます。また、商品コードは重複しないように入力してください。

請求書の体裁を整える

　商品リストとば別のシートに請求書を作成します。普段使用している請求書に合わせて項目を設定し、デザインを行いましょう。

請求書

請求書の作成に関しては、以前のコラム（「10分で作るエクセル見積書」）を参考にすると良いでしょう。

商品コードから商品名を表示する

商品コード欄にコード番号を入力すると、自動的に商品名が表示されるように該当するセルに関数を設定します。

商品コードから商品名を表示

①商品コード欄に商品コードを入力します。このコードは、請求書完成後に削除する予定なので、商品リストに存在するコードであれば何でも構いません。

②VLOOKUP関数を使って、コードから商品名が表示されるようにします。商品名欄をクリックし、数式バーに「=VLOOKUP(」と入力します。（すべて小文字、Enterキーは押さないでください）

商品コードから商品名を表示

以前のコラム（第1回　10分で作るエクセル見積書）では、数式バーの関数ボタンをクリックすると表示される［関数の挿入］ダイアログボックスを使って関数を入力する方法を解説しました。今回は、もうひとつの方法である数式バーに直接関数を入力していく方法を解説します。

商品コードから商品名を表示

③検索値を入力するセルをクリックします。ここでは「商品コード」のセルになります。

④セル番号の後に「,」（コロン）を入力します。

⑤続けて検索するセル範囲を指定します。商品リストのシートタブをクリックします。

⑥商品リストの範囲をドラッグして指定します。商品コードなどの項目名部分は範囲に含みません。

指定する範囲は、商品コード、商品名、単価が入力されているセル範囲となります。上記の例の場合には、A2からC15となります。セル範囲を指定すると、数式バーには、

=VLOOKUP(A11,商品リスト!A2:C15

のように表示されます。これは、「商品リスト」シートの「A2」から「C15」の範囲で「A11」に入力された文字列を検索する、という意味になります。なお、範囲指定後、商品リストに項目を追加する場合には、VLOOKUP関数の範囲設定も変更してください。
※範囲の左端列に、検索に使うコードが入力されている必要があります。

⑦セル範囲を絶対参照に変更するため、＜F4＞を押します。

セル範囲の指定を絶対参照に変更するのは、数式をコピーする際に指定した範囲がずれてしまうことを防止するためです。

⑧セル範囲の後に「,」（コロン）を入力します。

⑨検索値に対応する値が入力された列番号を入力します。ここでは商品コードに対して商品名を表示させたいので、商品名が入力されている列の番号、「2」を入力します。

ここまでの時点で、数式の意味は「A11」に入力した値を「商品リスト」シートの「$A$2」から「$C$15」の範囲から検索し、該当する行の左から「2」列目にある文字列を表示するという意味になります。単価を表示する場合には３列目なので、列番号を「3」にします。

⑩列番号の後に「, FALSE」と入力します。

表示されるバルーンヘルプから”FALSE”をダブルクリックしても構いません。（エクセル2003にはバルーンヘルプの機能はありません）

⑪最後に「)」（括弧）を入力して、Enterキーを押します。商品コードに対応する商品名が表示されればOKです。

・VLOOKUP関数と引数
VLOOKUP関数は、指定したブック/セル範囲から値を参照する関数です。

＝VLOOKUP(検索値、検索範囲、列番号、検索方法)

検索値：検索する値あるいは値を入力したセルを指定します。
検索範囲：検索するセルの範囲を指定します。
列番号：検索結果として参照する値が入力された列を指定します。
検索方法：検索値が一致しなかった場合の動作を指定します。省略可。

この関数を使うと、「検索範囲」の中から「検索値」と同じ値や文字列を検索し、見つかった「検索値」を含む行の「列番号」にある値や文字列を表示できます。

エラー表示を回避する

　VLOOKUP関数を使って商品コードから商品名を表示させることは出来ましたが、このままだと商品コードが空欄だとエラーが表示されてしまいます。IFERROR関数を使ってエラーを表示しないように設定します。

≪エラーの例≫

①VLOOKUP関数を設定したセルをクリックします。

②数式バーに表示された関数の「=」と「VLOOKUP」の間をクリックします。

③「IFERROR(」と入力し、行末までカーソルを移動させます。

④関数の最後に「,””)」と入力し、Enterキーを押します。これで、エラーの場合には空欄が表示されるようになります。

・IFERROR関数と引数
IFERROR関数は、エラーの場合に表示する値を指定する関数です。

値、エラーを検証する値を指定します。
エラーの場合の値、エラーの場合に表示する値を指定します。

この関数を使うと、値や数式がエラーになった場合に表示する、値や文字列を指定できます。

・エクセル2002/2003の場合
　IFERROR関数は、エクセル2007から搭載された関数なので、それ以前のバージョンには存在しません。2007以前のバージョンでエラー表示を回避するには、IF関数を使ってエラー時の動作を指定するなど、いくつかの方法があります。
　たとえば、次のような数式が考えられます。

=IF(A11=””,””,VLOOKUP(A11,商品リスト!$A$2:$C$15,2,FALSE))

　上記の数式では、A11のセルが””、すなわち空白だった場合には空白を表示し、そうでない場合にはVLOOKUP関数で参照された値が表示されます。

関数をコピーする

　関数の入力が終わったら、その関数を他のセルにもコピーします。

①関数の入力されたセル（商品名欄１行目）をクリックします。
②セル右下の点（フィルハンドル）をドラッグし、隣のセル（単価）にコピーします。

②セル右下の点（フィルハンドル）をドラッグし、隣のセル（単価）にコピーします。

③コピーしたセルをクリック

③コピーしたセルをクリックし、数式バーを見て内容を修正します。

④検索値の参照セル番号がずれているので修正します。

⑤表示するのは「単価」なので、列数を「2」から「3」に変更します。

⑥修正が終わったら、数式バーのチェックボタンをクリックして数式を確定します。

⑦商品名と単価のセルをドラッグして選択します。

⑧フィルハンドルをドラッグして、数式をコピーすれば完成です。

ここで紹介した方法は、請求書だけでなく見積書や納入書にも応用可能です。

まとめ

　他の関数を組み込むことで、もっと便利な請求書に仕上げることも可能です。「関数」と聞くと難しいように思えるかもしれませんが、エクセルには関数を簡単に利用するための機能が搭載されていますから、関数の機能をしっかりと抑えておけばそれほど難しくはありません。関数を使うことで、作業は飛躍的に楽になるので、是非使い方をマスターしてください。