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

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

 

配列数式にすると、「月」列なしに入出庫数を求めることができます

SUMIFS関数を使うときには、「日付」を元にしてMONTH関数で求め た「月」列が必要です。

SUMIFの2つの関数を使った配列数式の場合には、この「月」列は必要ありません。条件式としてMONTH関数を利用できるからです。

配列数式を有効にするには、Ctrl+Shift+Enterキーで数式を確定します。

Excel 2016/2013/2010/2007

 

@作例の「仕入伝票」には、「年/月/日」の形式で納品日が入力されています

 

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

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

 

C月と品番の2つを条件にするので、それぞれの論理式は括弧で囲んで指定します

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

 

EMONTH関数が括弧付きで入力されたら、F5キーを押します

 

F「仕入伝票」を選んで「OK」をクリックします

「ジャンプ」を使うと、指定したテーブルにすばやく移動できます。

G「仕入伝票」が表示されたら、「日付」をクリックして、そのデータ範囲を指定します

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

 

 

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

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

J半角の「)*(」を入力して、ふたつ目の論理式を指定します

 

KF5キーでもう一度「仕入伝票」にジャンプして、今度は「品番」のデータ範囲を指定します

L半角の「=」を入力してから、シートタブで「商品リスト」に戻り、設定中の数式と同じ行に入力されている「商品番号」をクリックで指定します

M半角の閉じ括弧とカンマを入力して、論理式が「真の場合」の指定に移ります

NF5キーで「仕入伝票」にジャンプして、「数量」のデータ範囲を指定します

論理式が正しいときには、ここで指定した「数量」のデータが、SUM関数による合計の対象となります。

O半角のカンマを入力し、論理式が「偽の場合」の値として0を指定してから、半角の閉じ括弧2つで数式を完成させます

Pその数式をCtrl+Shift+Enterキーで確定すると、配列数式が有効になります

Q同様の配列数式で、「売上伝票」から出庫数を求めます

R2つの配列数式を必要なセル範囲にコピーします

S月を表す数値の指定は、行番号のみの絶対指定にしてあるので、コピーしてそのまま利用できます

 

デル株式会社

関連する他のページ

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

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

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

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

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

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

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

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

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