よねさんのWordとExcelの小部屋Excel(エクセル)講座の総目次Excel(エクセル)実用編:目次|請求書の作成(関数を使った処理)
よねさんのWordとExcelの小部屋

Excel(エクセル)実用編:請求書の作成(関数を使った処理)


スポンサードリンク


完成例 商品一覧表の作成 請求書一覧表の作成 請求書の作成

完成例

シートは、商品一覧請求書一覧請求書の3枚から成っています。
  1. 商品一覧表シートを作成します。
  2. 請求書一覧シートに請求内容を入力します。
  3. 請求書シートのH2セルに請求書[No]を入力するだけで、請求書が作成されます。

商品一覧表の作成     Topへ

  1. 「商品一覧」シートに[品番]、[商品名]、[価格]の一覧表を作成します。
    品番は検索する時のキーになりますので、重複しないようにします
    ※シートの名前はシート見出し[Sheet3]をダブルクリックすると変更できます。
    または、メニューバーの[書式]→[シート]→[名前の変更]で変更します。
  2. 品番等の入力されている範囲に[商品一覧表]と名前をつけます
    1. A3:C14(データの入力されている範囲:タイトルは除く) を選択します。
    2. 名前ボックスに[商品一覧表]と入力します。

(ステップアップ)

  1. 商品一覧表に後からデータが追加されることを考えた場合、範囲の名前を毎回付け替えるのは面倒です。
    そこで、名前を付けるときに一行下までの範囲の名前を付けておきます。
    上の例では、セル範囲『A3:C15』に『商品一覧表』と名前を付けます。
  2. データを追加する時には、15行目に直接入力するのではなく、
    15行目を選択し、メニューバーの【挿入】→【行】で1行挿入してから、データを15行目に入力します。
    『商品一覧表』と名前を付けたセル範囲は、1行挿入した時点でA3:C16に拡大されます。

請求書一覧表の作成        Topへ

請求書一覧表を作成します。

  1. シート見出し[Sheet2]をダブルクリックして、シート名を[請求書一覧]に変更します。
  2. A2から[No.]、[日付]、[宛先]、[品番1]、[品名1]、[数量1]・・・・・[品番5]、[品名5]、[数量5]とR2まで項目を入力します。
  3. [No]の値はA3には基準となる値を入力し、A4以降は連番にします。(重複を避けるためです。)
    A4に「=$A$3+ROW()-3」と入力し、下のほうへオートフィルで連続値をコピーします。
    A3に入力した値にA4の行数4から3を引く(つまり1を加える)式にしています。
    A5セルではA5の5から3を引くことで、2を加えることになりますので、連続値になります。

    (1)A4に数式を入力して、オートフィルで下方向へコピー
    (2)連続した値が表示されました。
  4. A2:R2の範囲を選択して、この範囲に[項目名]名前をつけます。
  5. データの範囲A3:R50を選択し、この範囲に[請求書一覧表]と名前をつけます。
    この[請求書一覧表]の範囲は以後のデータが増えるのを見越して、広い範囲に設定したほうが良いでしょう。
  6. (完成例)


(ステップアップ)
[A] A〜C列と2行目まではタイトルとして、いつでも表示させていたいので、ウィンドウ枠の固定を使います。

[B] 品番を入力セルに、『入力規則のリスト』を適用しておくといちいち入力しなくてすみます。
   (操作手順)

  1. 【商品一覧】シートの品番部分+1行(セル範囲A3:A15)に『品番』と名前を付けます。
  2. 【請求書一覧】シートの品番入力セルに『入力規則のリスト』を適用します。
  3. 他の品番入力セルにコピーして貼り付けます。

[C] 宛先もシートを作成して入力規則のリストを使って、選択して入力するようにしたほうが楽ができそうです。


請求書の作成        Topへ

(完成例)

文字の入力と体裁を整える          請求書Topへ

  1. C2セルに[請求書]と入力し、C3:E3を選択して、[セルを結合して中央揃え]ボタンをクリックします。
  2. [罫線]ボタンの下三角ボタンをクリックし、[下二重罫線]ボタンをクリックします。
  3. 品番、商品名・・・・小計からなる表部分は、式や関数を入力後、罫線を入れます。

請求書一覧表から日付を取り出す         請求書Topへ

  1. H2セルには、表示したい[No.] を入力します。
  2. G3セルには、請求書一覧表からデータを取り込みます。
    H2の入力した[No] で検索します。VLOOKUP関数とMATCH関数の組み合わせです。

請求書一覧表から宛先、品番を取り出す        請求書Topへ

  1. B5セルの宛先もVLOOKUP関数とMATCH関数の組み合わせです。

商品一覧表から商品名,価格を取り出す         請求書Topへ

  1. C8:C12セル、F8:F12セルはIF関数とVLOOKUP関数の組み合わせです。

商品一覧表から数量を取り出す           請求書Topへ

  1. G8:G12セルもIF関数、VLOOKUP関数、MATCH関数の組み合わせです。

小計、合計、消費税、総計の計算式を入力する          請求書Topへ

  1. H8:H12セルは掛け算[価格]×[数量]です。ただし、B列の品番欄が空欄のときは空白となるようにIF関数を使っています。
  2. 小計欄H13はSUM関数。 消費税欄H14には少数以下切捨てのためROUNDDOWN関数を使っています。
  3. 総計欄H15はSUM関数を使っています。(=H13+H14(足し算)でも同じ結果です。)

スポンサードリンク


よねさんのWordとExcelの小部屋Excel(エクセル)講座の総目次Excel(エクセル)実用編:目次|請求書の作成(関数を使った処理)

PageViewCounter
 Counter
 Since2006/2/27