知人が「商品を発送するのに必要な段ボール箱を用意しなくちゃいけないから、MOD 関数とかを
使って表を作ってみたんだけど、これ合ってる？」とシートを見せてくれました。
結果は合っていました。

しかーし！

とっても数式が長いのです。(下図の赤枠のところ参照)

何箱必要かを算出して、余りがあったら 1 箱プラスして・・・という数式のようです。

 

間違ってないよ、でも、もっと数式を短くする方法はあるよ、と伝えました。
そして、「もしピッタリ入りきらない分があったら 1 箱追加してよいのよね？」と確認しました。
これによって使う関数が変わってくるからです。

「いいよ」とのことなので、CEILING 関数を使って、下図のように数式を作りました。

だいぶ数式がすっきりしました。

 

 

今回は先に、CEILING 関数の説明を書きます。
ここを読んで上記のサンプルの意味がわかるという方は、後半の手順は見なくてもよいと思います。

CEILING 関数は、指定された「基準値」の倍数のうち、最も近い値に「数値」を切り上げる関数です。

厳密には表現が違ってしまうかもしれないけれど、イメージとしては下図です。
「基準値」(100) の倍数を並べておいて、その中で、
「数値」(985) に一番近い数値 (1000) を見つけて、それを答えとしている感じです。

CEILING 関数では「数値を切り上げる」ため、「900」にはならず「1000」となります。
ちなみに、お仲間の関数である FLOOR 関数では「数値を切り捨てる」ので「900」となります。

最初のやり取りで、
「もしピッタリ入りきらない分があったら 1 箱追加してよいのよね？」という問いに対して、
「いや、入りきらない分は別の方法で梱包するから 1 箱は使わないよ」という回答だったら、
FLOOR 関数を選択していました。

 

 

数式は、下記のように引数を指定します。

CEILING (数値,基準値)

数値 には、対象となる数値をセル参照や数値で指定します。

基準値 には、倍数の基準となる数値をセル参照や数値指定します。
 

 

[発送個数] 列に入力されている数値と、[1箱あたりの個数] 列に入力されている基準値を元に
値を算出し、その値を [1箱あたりの個数] 列に入力されている値で割って、
[必要な箱の個数] 列に箱の個数を表示します。

操作に慣れていない方は、日本語入力をオフにして操作してください。

最初に作成する数式は、「=CEILING(数値,基準値)」とします。その後、数式を編集して、
「=CEILING(数値,基準値)/基準値」となるようにします。

 

1.結果を表示したいセルを選択し、「=CEILING(」と入力、指定します。
関数名は小文字でもよいです。

 

2.数値として、箱に詰めたい商品の数が入力されているセル (C4) を選択します。

 

3.「,」を入力し、基準値として、1 箱あたりに入れられる商品の数が入力されているセル () を
クリックします。

 

4.「)」を入力して、[Enter] キーを押します。

 

5.結果が表示されます。

ここまでの数式の結果では、数値 (1000) を切り上げ、基準値 (100) の倍数のうち、
数値に一番近い値 (1000) が表示されています。

数値が「1000」で基準値が「100」だとわかりにくいけれど、
表の 2 行目にある、数値が「985」で基準値が「100」のデータだとイメージが
つきやすいかもしれません。

「985」を切り上げて、基準値である「100」の倍数の中で一番近い値は、「1000」ですよね。

次の手順で、この結果を「1 箱あたりの個数」(基準値) で割って箱の数を求める数式に
変更します。

 

6.数式が入力されているセル (F4) をダブル クリックするか、セルを選択して [F2] キーを
押してセルを編集状態にし、「/」を入力して基準値のセル (1箱あたりの個数、セル E4) を
クリックして [Enter] キーを押します。

 

7.計算結果が表示されます。
 

 

8.他の行に数式をコピーします。
それぞれの行の [必要な箱の個数] のセルに箱の個数が表示されます。

今回の例に限らず、1 つの答えに辿りつくために、いろいろな方法が考えられます。
しかし、長い数式はわかりにくく、修正をしたり、ほかのユーザーが確認したいときにミスが
起きやすくなります。また、場合によっては処理速度にも影響が出てしまいます。

=A1+A2+A3+A4+A5+C1+C2+C3+C4+C5 よりも
=SUM(A1:A5,C1:C5) のほうがわかりやすいですよね。

同じ結果を得られるのであれば、より短く、わかりやすいほうがよいでしょう。
関数を 1 つ知れば解決したりするものです。
だからといってすべての関数を覚えておきなさい、ということではなくて、必要になったときに
調べられる力とテクニックがあればよいのかなと思っています。
私だってすべての関数を知っているわけではないですから。

[関数の挿入] ダイアログ ボックスやヘルプ、インターネット、書籍で調べられるんですけど、
このとき何をキーワードにするかで結果が違ってきますし、検索結果をもとに自分が使うケースに
置き換える応用力が求められますね。

実は私はあまり検索が得意ではないのですが．．．がんばります。がんばりましょう。

石田 かのこ