いつか役に立つかもしれないExcel関数の小ネタ集(1)

今回は「知っていると役に立つかもしれない関数」として、QUOTIENT、MOD、LCM、GCDの使い方を小ネタ集のように紹介していこう。いずれも関数の使い方そのものは簡単だ。それよりも「どのように活用するか？」を考えるのが難しいかもしれない。最低限の知識として「こういう関数も用意ある」ということを覚えておくとよいだろう。

いつか役に立つかもしれない関数

Excelには、数学的な計算を行ってくれる関数が数多く用意されている。その代表例はSUMやAVERAGEなどの関数となるが、それ以外にも「知っていると役に立つかも!?」という関数がいくつかある。

さまざまな計算に使える関数の小ネタ集

頻繁に使用する関数ではないが、「こういう関数もある」ということを覚えておくと、いざというときに活用できるかもしれない。順番に紹介していこう。

割り算の「商」と「余り」を求める関数(QUOTIENT、MOD)

最初に紹介するのは、“整数"に限定して割り算したときの「商」と「余り」を求めてくれる関数だ。通常の割り算は「9÷4＝2.25」のように小数点以下も計算されるが、これを「9÷4＝2余り1」のように計算してくれる関数が「QUOTIENT」と「MOD」になる。

まずは、それぞれの関数の書式を示しておこう。どちらも、第1引数に「割られる数値」(分子)、第2引数に「割る数値」(分母)を指定すればよい。

◆割り算の「商」を求める関数
=QUOTIENT(分子, 分母)

◆割り算の「余り」を求める関数
=MOD(分子, 分母)

具体的な例を示していこう。以下の図は、3,547個ある商品を24個ずつ箱詰めしていった場合に「段ボールは何箱必要で、また何個の商品が余るか？」を計算するものだ。

「段ボールは何箱必要？」は、3,547÷24の「商」で求められる。よって、以下の図のように関数QUOTIENTを入力すればよい。

関数QUOTIENTの入力

この結果は「147」という数値になった。つまり「段ボールは147箱必要」という訳だ。

関数QUOTIENTにより求められた割り算の「商」

商品を箱詰めした後に端数として残る商品の数は「余り」を求める関数MODで計算できる。こちらも割り算は3,547÷24となるので、先ほどと同様に引数を指定すればよい。

関数MODの入力

結果は「19」という数値になった。つまり「19個の商品が箱詰め後に余る」ということになる。

関数MODにより求められた割り算の「余り」

このように、小学校で習った割り算のように「商」と「余り」を求めたいときは、関数QUOTIENTや関数MODを利用するとよい。

「商」と「余り」を使って単位を換算する

QUOTIENTやMODといった関数を使って「10進法でない数値」を別の単位に換算することも可能だ。たとえば、普通の“数値"として記録されている「秒数」を「h時間mm分ss秒」に換算する、といった場合にもQUOTIENTとMODが活用できる。具体的な例で見ていこう。

たとえば、1個あたり5.8秒で部品を製作できる工作機械があったとしよう。必要な部品が2,000個であった場合、5.8×2,000＝11,600(秒)で作業を完了できることになる。とはいえ、11,600秒と言われても時間のイメージがわかない方が多いだろう。

2,000個の部品製作に要する時間(秒)

そこで、11,600秒を「h時間mm分ss秒」という形に換算してみよう。

1時間は60分なので、秒に換算すると60分×60秒＝3,600秒になる。よって、11,600秒を3,600秒で割り算した「商」を求めると「h時間」の部分を求められる。これを関数で記すと「=QUOTIENT(C5,3600)」となる。その結果は「3時間」という数値になった。

「時間」を求める関数の入力

関数により求められた「時間」

次は「分」の部分を算出していこう。先ほど求めた「3時間」の部分を除いた「余り」の秒数は「=MOD(C5,3600)」で計算できる。これを60秒で割り算した「商」が「mm分」になる。これを関数で記すと「=QUOTIENT(MOD(C5,3600),60)」となる。結果は「13分」という数値になった。

「分」を求める関数の入力

関数により求められた「分」

最後は「秒」の部分の計算だ。こちらも「11,600秒から3時間と13分を除いて……」と考えるかもしれないが、その必要はない。60以上の秒数は、すでに「分」や「時間」として処理されているので、単純に「11,600÷60の余り」だけを計算すればよい。よって、関数の記述は「=MOD(C5,60)」となる。結果は「20秒」という数値になった。

「秒」を求める関数の入力

関数により求められた「秒」

これらの結果を見ると、11,600秒は「3時間13分20秒」になることがわかる。よって、少し余裕を持たせて「3時間半くらいあれば作業を完了できるはず……」と予測を立てることができる。

このように「10進法でない数値」を別の単位に換算するときにもQUOTIENTとMODが活用できる。いずれの関数も使い方そのものは難しくないが、「どのように処理していくか？」を考えるロジックの方が難しい……、となるかもしれない。

「最小公倍数」や「最大公約数」を求める関数(LCM、GCD)

続いては、「最小公倍数」を求める関数LCM、「最大公約数」を求める関数GCDについて紹介していこう。

◆「最小公倍数」を求める関数
=LCM(数値1, 数値2, 数値3, ……)

◆「最大公約数」を求める関数
=GCD(数値1, 数値2, 数値3, ……)

どちらの関数も、数値をカンマ区切りで羅列していくだけで「最小公倍数」や「最大公約数」を求められる仕組みになっている。引数にセル範囲を記述して「複数の数値」を一括指定することも可能だ。

具体的な例で見ていこう。たとえば、自社の商品を発送するために専用の段ボール箱を設計するとしよう。商品はA、B、Cの3種類があり、それぞれ「幅」と「奥行」は以下のようなサイズになっている。

・商品A:12×5cm
・商品B:8×8cm
・商品C:5×9cm

各商品のサイズ

このとき、商品ごとに段ボール箱のサイズを変えてしまうと何かと不便なので、すべての商品で共通して使える段ボール箱を設計するとしよう。

この場合、「幅」と「奥行」が「各商品の最小公倍数」になるように段ボール箱のサイズを決めていくと、A、B、Cの3商品とも隙間なく詰められる段ボール箱になる。

まずは「幅」の最小公倍数について。こちらは、以下の図のように関数LCMを記述すると求められる。

関数LCMの入力(幅の最小公倍数)

同様に「奥行」の最小公倍数は、以下の図のように関数を記述すると求められる。

関数LCMの入力(奥行の最小公倍数)

これらの結果は、それぞれ「120」「360」という数値になった。

関数LCMにより求められた最小公倍数

つまり、幅120cm×奥行360cmの段ボール箱を製作すれば、すべての商品を隙間なく詰められる訳だ。とはいえ、一辺が360cmもある段ボール箱は、どう考えても現実的ではない。奥行が3m以上もある段ボール箱なんて「誰も運べない……」と考えるのが普通だろう。よって、このままでは机上の空論に終わってしまう。

でも諦めるのはまだ早い。先ほどの表をよく見ると、「商品Aの奥行」と「商品Cの幅」はどちらも5cmで、同じサイズであることに気付くと思う。そこで、商品Cを90度回転させて段ボール箱に詰めた場合を考えてみよう。商品Cの「幅」と「奥行」を入れ替えると、以下の図のような結果を得ることができた。