エクセル実践塾 > エクセル実践塾2016

仕入伝票と売上伝票を使って、月ごとで在庫管理したい@

 

それぞれの伝票に「月」列を追加すれば、それを条件にしたSUMIFS関数式で入出庫数を求めることができます

「月」列は、「日付」を元にしてMONTH関数で求めることができます。

その「月」と「品番」を条件にして、SUMIFS関数式で入庫数と出庫数をそれぞれ求めれば、その差で在庫数を得られます。

Excel 2016/2013/2010/2007

 

@MONTH関数を使って、日付から月を表す数値を抜き出しておきます

作例では仕入伝票はテーブル化してあります。テーブル化してある表では、関数の引数は列項目名で表されます。

A作例では、入出庫数の管理は、商品の一覧が記載されている「商品リスト」と同じシートで行います

B半角で「=sum」と入力し、表示される関数の一覧から「SUMIFS」を選んで、Tabキーでそれを入力します

 

CSUMIFS関数式が入力されます

SUMIFS関数では、「合計対象範囲」の列を指定してから、条件範囲と条件値を、必要なだけ指定していきます。

L1セルには、4月を表す4を入力しておきます。

DF5キーで表示される「ジャンプ」ダイアログボックスを使って、「合計対象範囲」を指定します

作例では、テーブル化してある仕入伝票を選んで、「OK」をクリックします。

E「仕入伝票」のテーブルに移動したところで、列項目の「数量」をクリックして、そのデータ範囲を「合計対象範囲」として指定します

テーブル化されている表では、項目名をクリックするだけで、その列データすべてを選択できます。

F半角のカンマをはさんで、ひとつ目の「条件範囲」となる「月」列のデータ範囲を指定します

G半角のカンマを入力してから、シートタブで「商品リスト」に戻り、月を表す数字を入力してあるセルを「条件値」として指定します

HF4キーを2度押して、行番号のみの絶対指定に変えます

行番号のみを絶対指定にしておくと、下方向へのセルコピーでは番地指定が固定され、横方向へのセルコピーではその場所に応じて列番号が変わるようになります。

 

I半角のカンマを入力してから、F5キーを使って「仕入伝票」の「品番」データを、ふたつ目の「条件範囲」として指定します

J半角のカンマを入力してから、「商品リスト」に戻って、設定中の数式と同じ行に入力されている「商品番号」をクリックで指定します

「商品リスト」もテーブル化してあるので、指定したセルは、番地ではなく項目列名で表されます。

K半角の閉じ括弧でSUMIFS関数式を完成させると、4月に仕入れた、同じ行の商品の個数が表示されます

L同様の手順で、売上伝票から、4月に出庫した商品の個数を求めます

M棚卸しの個数に入庫数を足し、そこから出庫数を引くと、在庫数が得られます

N3つの数式をコピーすると、各商品の4月の入出庫数と在庫が求められます

 

関連する他のページ

「オイル」と名のつく製品名と、識別数字の両方の条件に合致するものを、SUMIFS関数で合計したい
SUMIFS関数の検索条件には、任意の文字列を表すワイルドカードも使えます

「オイル」と名のつく製品をのぞいて、SUMIFS関数でその個数を合計したい
SUMIFS関数の検索条件では、等しくないことを表す、比較演算子「<> (不等号)」も使えます

識別数字が7以上の製品をSUMIFS関数で合計したい
SUMIFS関数の検索条件では、「>= 」(〜以上)などの比較演算子も使えます

指定した識別数字と製品名の両方に合致するものを合計したい
Excel 2007から用意されたSUMIFS関数を使えば、複数の条件に該当する行の値だけを合計できます

複数の検索データに合致するセル数を調べたい
配列数式を使えば、COUNTIF関数で調べられます

論理値を乗算し、AND条件として使う
論理値の乗算では、1(TRUE)のみであるときにだけ、1(TRUE)となります

型番を入力すると、小売価格が表示されるようにしたい
検索するべき型番が横(行)方向に並んでいる場合にはHLOOKUP関数を使います

あとから見てもわかりやすいVLOOKUP関数式にしたい
「範囲」として指定するセル範囲に、あらかじめ適当な名前を付けておけば、VLOOKUP関数式でもそれを使えるのでわかりやすくなります

数式に使っている、セル範囲につけた名前を変更したい
「数式」タブの「名前の管理」ボタンで、セル範囲につけた名前などの変更を行えます