エクセルの関数技 CEILING関数、FLOOR関数の使い方

「ケース単位」も「単品」もCEILING関数とFLOOR関数

商品を発注する時に、ケースものとバラもの(単品もの)がある場合がありますね。

ケースものの発注は、発注単位で繰り上げて必要発注数を求めます。
バラ売りも可の場合は、ケース何個、バラ何個というように、注文したいわけです。

こんな場合、必要数をいれるだけで、自動的に発注内容が出る表を作るには
どうしたらよいでしょう。
(サンプルファイルは、こちらから 関数技51b回サンプルデータ

ケースものと単品もの

【例題】

例題は、DVDの10枚入りパックと、DVD50枚入りパックを注文する場合です。
DVDの10枚入りパックは、単品注文ができません。
DVD50枚入りパックは、単品注文が可能です。

注文はケース売りだけなら、使用する関数は、CEILING関数です。 

まず、ケース売りしか受け付けない場合を考えてみましょう。
使う関数は、CEILING関数です。CEILING関数

(1)ケース売りのみ、単品注文不可の場合

(1) まず、ケース売りのみ、単品注文不可の場合です。
E3セルに、1ケース12個入りなので、12の倍数になるように切り上げた個数を出します。使う関数は、CEILING関数 です。CEILING関数書式
=CEILING(数値, 基準値)

CEILING(シーリング)はご存知のように「天井」の意味です。
数値を、基準値の倍数のうち最も近い値(天井)に切り上げる関数ということです。
数値には、必要数である、D3セル基準値は1ケースの入り数を当てはめます。
CEILING関数1
=CEILING(E3,B3) 
何ケース注文するかは、=CEILING(E3,B3)/B3
CEILING関数2
H3セルには、単品注文ができない時に、不可と表示しておくようにしましょう。
CEILING関数3
=IF(C3="不可","不可",MOD(E3,B3))
スポンサーリンク
スポンサーリンク

(2)ばら売り、単品注文可の場合はFLOOR関数

(2) 次に、単品注文可の場合です。

1ケース12個入りなので、ケース何個で、バラ何個注文するのかを出します。
使う関数は、FLOOR関数 です。
=FLOOR(数値, 基準値)
FLOOR関数
FLOOR(フローア)はご存知のように「床」の意味です。
数値を、基準値の倍数のうち最も近い値(床)に切り下げる関数ということで す。
単品注文もOKの場合は、必要数=発注数ですね。
そこで、ケース数に入れる式を考えてみます。G4セルにケース数を出します。
式は、
=FLOOR(E4,B4)/B4
FLOOR関数
単品注文も可能な商品のケース注文数が出ました。単品注文数は、さきほどの
=IF(C3="不可","不可",MOD(E3,B3))
の式により、4と出ています。
FLOOR関数2
以上のように、「ケース単位」も「単品」もCEILING関数、FLOOR関数を使って 求めることができます。

応用例IF関数でCEILING関数とFLOOR関数をまとめる

以上で完成でもいいのですが、ケース注文だけの場合と単品注文もOKの場合の式をまとめてしまったほうが、使い回しが楽です。
発注数の式は、IF関数を使ってC3セルが不可の場合と可の場合に分けます。
=IF(C3="不可",G3*B3,E3)

同様に、注文するケース数の場合もIF関数を使ってC3セルが不可の場合と可の場合に分けます。

=IF(C3="不可",CEILING(E3,B3)/B3,FLOOR(E3,B3)/B3)

今日の講義は以上です。お疲れ様でした。
スポンサーリンク
スポンサーリンク