定時で上がろう! Excel関数の底力 第69回 いつか役に立つかもしれない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を入力すればよい。
この結果は「147」という数値になった。つまり「段ボールは147箱必要」という訳だ。
商品を箱詰めした後に端数として残る商品の数は「余り」を求める関数MODで計算できる。こちらも割り算は3,547÷24となるので、先ほどと同様に引数を指定すればよい。
結果は「19」という数値になった。つまり「19個の商品が箱詰め後に余る」ということになる。
このように、小学校で習った割り算のように「商」と「余り」を求めたいときは、関数QUOTIENTや関数MODを利用するとよい。
「商」と「余り」を使って単位を換算する
QUOTIENTやMODといった関数を使って「10進法でない数値」を別の単位に換算することも可能だ。たとえば、普通の“数値"として記録されている「秒数」を「h時間mm分ss秒」に換算する、といった場合にもQUOTIENTとMODが活用できる。具体的な例で見ていこう。
たとえば、1個あたり5.8秒で部品を製作できる工作機械があったとしよう。必要な部品が2,000個であった場合、5.8×2,000＝11,600(秒)で作業を完了できることになる。とはいえ、11,600秒と言われても時間のイメージがわかない方が多いだろう。
そこで、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を記述すると求められる。
同様に「奥行」の最小公倍数は、以下の図のように関数を記述すると求められる。
これらの結果は、それぞれ「120」「360」という数値になった。
つまり、幅120cm×奥行360cmの段ボール箱を製作すれば、すべての商品を隙間なく詰められる訳だ。とはいえ、一辺が360cmもある段ボール箱は、どう考えても現実的ではない。奥行が3m以上もある段ボール箱なんて「誰も運べない……」と考えるのが普通だろう。よって、このままでは机上の空論に終わってしまう。
でも諦めるのはまだ早い。先ほどの表をよく見ると、「商品Aの奥行」と「商品Cの幅」はどちらも5cmで、同じサイズであることに気付くと思う。そこで、商品Cを90度回転させて段ボール箱に詰めた場合を考えてみよう。商品Cの「幅」と「奥行」を入れ替えると、以下の図のような結果を得ることができた。
つまり、幅72cm×奥行40cmの段ボール箱でも、すべての商品を隙間なく詰められる訳だ。これなら十分に実現可能なサイズといえる。
このように「小学校の算数レベルであるが、実際に解いてみると意外に難しい……」といった問題に関数が活用できるケースもある。電卓をたたいて計算する場合に比べて、色々なパターンを手軽に試せることも関数の利点といえるだろう。
参考までに、関数LCMの仕様について補足しておこう。関数LCMの引数に「整数以外の数値」を指定すると、それぞれの数値の「小数点以下を切り捨てた値」について最小公倍数が求められる。このため、想定外の結果が表示される恐れがある。これは最大公約数を求める関数GCDも同様だ。
数値に小数点以下が含まれる場合は、単位を「cm」から「mm」に変更するなど、数値を整数にした状態で引数を指定しなければならない。念のため、覚えておくとよいだろう。
ということで今回は、QUOTIENT、MOD、LCM、GCDといった関数の使い方を紹介した。次回は「さまざまな計算に使える関数の小ネタ集」の第2弾を紹介していこう。