【Excel】CEILING関数を使って必要なセット数、実際の数量を特定する

商品はばら売りだけでなく、1ケース○○個、1セット○○個といった売り方がされる場合も多いです。その際、ExcelのCEILING関数を使うことで、必要個数からセット数、実際の数量を特定することができます。

CEILING関数とは

CEILING関数は、指定した数値を基準値の倍数の中で、最も近い値に切り上げてくれる関数です。

関数	役割
CEILING(数値,基準値)	指定した数値を基準値の倍数の中で、最も近い値に切り上げる

たとえば、10個で1セットの商品を56個購入したい場合、実際に購入する数量は60個（10個×6セット）になります。CEILING関数では、この60個を自動で計算してくれます。

CEILING関数とVLOOKUP関数を組み合わせる

CEILING関数はVLOOKUP関数と組み合わせることで、効率的に計算を行うことができます。

たとえば、以下のように必要な物資と1セットの個数が一覧化された商品リストがある場合を考えます。ここで、実際に注文する「換算数」と「必要セット数」を特定したいと思います。

CEILING関数に基準値を手入力しても良いのですが、非効率的でミスも発生する可能性があるため、今回はVLOOKUP関数を用いて、商品リストから基準値を引用したいと思います。

まず、「A4用紙」の必要数から「換算数」を算出するため、セルF4に以下のように数式を入力します。

=CEILING(E4,VLOOKUP(C4,$K$4:$L$13,2,FALSE))

数式	役割
CEILING(E4	必要数「2,600」に基づき、換算数を算出する
VLOOKUP	基準値を商品リストから引用する。今回は、A4用紙1セット300枚を基準とする。
C4	商品名「A4用紙」という名前で検索する
$H$4:$I$13	検索に用いたい商品名と1セットの個数のデータの範囲を指定する。今回は商品名で検索したいので、商品名の列が範囲の左端に来るようにする。 他の商品も同じ範囲で検索したいので、絶対参照にする。
2	検索した結果、参照したいデータの列を指定する。今回は1セットの個数を参照したいので範囲の「2」列目を指定する。
FALSE	検索値と完全に一致するデータを検索する

VLOOKUP関数について詳しく知りたい場合は下記記事も参考にしてみてください。

そうすると、必要数から実際の数量「2,700」を算出することができます。A4用紙1セット300枚なので、9セットで2,700枚を購入することになります。

ベニヤ板やパイプも同様に、セルF4の数式をコピー&ペーストして換算数を出すことができます。

必要セット数もVLOOKUP関数を用いて算出します。

必要セット数は「換算数÷1セットの個数」で算出しますが、1セットの個数はVLOOKUP関数で商品名から検索します。

「A4用紙」の必要セット数を算出するため、換算数2,700÷1セットの個数300で計算する数式をセルG4入力します。

=F4/VLOOKUP(C4,$K$4:$L$13,2,FALSE)

数式	役割
F4	換算数「2,700」に基づき、必要セット数を算出する。
/	「換算数÷1セットの個数」で割り算を行う。
VLOOKUP	1セットの個数を商品リストから引用する。今回は、A4用紙1セット300枚を引用する
C4	商品名「A4用紙」という名前で検索する
$H$4:$I$13	検索に用いたい商品名と1セットの個数のデータの範囲を指定する。今回は商品名で検索したいので、商品名の列が範囲の左端に来るようにする。 他の商品も同じ範囲で検索したいので、絶対参照にする。
2	検索した結果、参照したいデータの列を指定する。今回は1セットの個数を参照したいので範囲の「2」列目を指定する。
FALSE	検索値と完全に一致するデータを検索する

そうすると以下のようにA4用紙の必要セット数「9」が算出されます。

ベニヤ板やパイプも同様に、セルG4の数式をコピー&ペーストして必要セット数を出すことができます。

このようにCEILING関数を使うことで、必要個数からセット数、実際の数量を特定することができます。また、VLOOKUP関数と組み合わせることで、膨大なデータでも正確に換算することができます。