ExcelでMOD関数とQUOTIENT関数を用いて金種表を作成する。

2019年4月6日整数の性質, 日進月歩そうだったのか!, 数学, 数検準2級

読了時間: 約740


「必要は発明の母」といいますが、「必要は学習の母」と言ってもいいかもしれません。
最近、金種表を作る必要があり、ひさびさにMicrosoft(マイクロソフト)の表計算ソフトExcel(エクセル)の勉強をしました。

しかし、数学を勉強してこなければ、合同式を用いて金種表を作成できないか。なんて思いつきもしなかったと思います。

Lukia_74

Lukia

数学って、日常生活でも使えるし、使われているものなんだな。と実感した一件でした。

金種欄に手で入力してみた。

職場で、交通費などを計算し、金種ごとに必要な数(枚数)を数える必要にせまられました。
たとえば、交通費が780円請求されたとします。
この場合、
$$780=500\times 1+100\times 2+50\times 1+10\times 3$$
となるので、支払うための金種ごとの枚数は、以下の表のようになりますね。

金種 500円 100円 50円 10円
枚数 1 2 1 3

ひとまずこのときは、金種の列を必要な分だけ作成し、頭の中で計算しながら、金種ごとの枚数を入力していきました。
交通費を支給する必要があった人数が少なかったので、なんとか手入力でもいけましたし、
念のため、合計金額が請求額と合致するかを確かめる計算式も設定しておいたので、その場は無事におさまりました。
しかし、人間はミスをするのが常。
どんなに予防策を打っておいても、頭がクリアじゃないときや、手入力する人数が多かったりしたら、計算間違いをするおそれもあります。

そこで、「交通費を入力したら、金種ごとの必要枚数が自動的に計算できるようにならないか」と思うようになりました。

起きぬけに合同式の利用を思いつく。

問題を抱えながら、いったん眠りに落ち、明け方ふと思いつきました。

Lukia_74

Lukia

金種分、文字をおいて、方程式で解くのは?

イメージは、
$$780=500\times w+100\times x+50\times y+10\times z$$
のような。

でも、これだと連立方程式を解くには、4つも式が必要になります。

Lukia_74

Lukia

さすがにそれは無理だ。

と思いました。
高校数学の「整数の性質」の単元を学んでいれば、
$$5x+3y=3$$
のような方程式をある程度解くことができますが、こんなに多元の方程式は解けません。

次に、同じく「整数の性質」の単元で習った「合同式」を思い出しました。

「合同式」は簡単にいうと、余りを求める式のことです。

この合同式を用いれば、うまくいくかもしれない。と思いました。

出勤前に数学やアイディアを書き留めていて、あやうく遅刻しかけたことがあるので、
ひとまず、ある金額を「合同式」を用いて表現するところまでやって、出勤しました。

27,890円を合同式で考える。

仮に、27,890円を紙幣や硬貨を用いて表すとします。

$$\begin{align}{27890} \equiv {7890} &\pmod {10000}\\
{7890} \equiv {2890} &\pmod {5000}\\
{2890} \equiv {890} &\pmod {1000}\\
{890} \equiv {390} &\pmod {500}\\
{390} \equiv {90} &\pmod {100}\\
{90} \equiv {40} &\pmod {50}\\
{40} \equiv {0} &\pmod {10}\end{align}$$

イメージとしては、それぞれの紙幣・硬貨で表せる数字をどんどん引いていくようなものです。
余りが表現できなくなる(ゼロ)になるまで額の低い金種で割っていきます。

しかし、これには、問題点があります。
合同式では、「商」を表現できないため、金種ごとに必要な枚数が算出されません。

Lukia_74

Lukia

おそらく、Excel(エクセル)には、合同式を表す関数があるはずで、
金種ごとに必要な枚数を割り出す関数も存在するだろう。

そこで、「エクセル 関数 合同式」などでネット検索をし、
タイムリーなことに「エクセルで金種表を作成する」なんて記事リンクを見つけたので、
ひとまず自分宛にリンクをメールし、出勤しました。

Lukia_74

Lukia

これ以上続けていたら、ついつい集中しちゃって遅刻しそうだったので。(汗)
ホント、出勤前や通勤中にペンやスマホを持つのは危険です。

金種表作成には2つの関数が必要。

自分宛にメールしておいた記事をじっくり読んでみると、
Excel(エクセル)にMOD関数なるものは存在するのですが、
金種表を作成するには、MOD関数のみでは不足で、
まずは、MOD関数で余りの額を小さくしておいてから、
QUOTIENT関数にて商を算出するという
2段階を経ることがわかりました。

というわけで、以下のような表を作成し、試すことにしました。

まずはMOD関数で余りを小さくしていく。

MOD
名前 金額 1
5
1
5
1
5
1
Aさん 27,890
(A0)
A1 A2 A3 A4 A5 A6 A7
合計

$$\begin{align}
表のA1セルには\quad &{27890} \equiv {7890} \pmod {10000}\quad を表す\quad \\ &\color{#0004fc}{=\mathrm{MOD}\left( A0 \ , \ 10000\right) }\quad を入力します.\\
表のA2セルには\quad &{7890} \equiv {2890} \pmod {5000}\quad を表す\quad \\ &\color{#0004fc}{=\mathrm{MOD}\left( A1 \ , \ 10000\right) }\quad を入力します.\\
表のA3セルには\quad &{2890} \equiv {890} \pmod {1000}\quad を表す\quad \\ &\color{#0004fc}{=\mathrm{MOD}\left( A2 \ , \ 10000\right) }\quad を入力します.\\
表のA4セルには\quad &{890} \equiv {390} \pmod {500}\quad を表す\quad \\ &\color{#0004fc}{=\mathrm{MOD}\left( A3 \ , \ 10000\right) }\quad を入力します.\\
表のA5セルには\quad &{390} \equiv {90} \pmod {100}\quad を表す\quad \\ &\color{#0004fc}{=\mathrm{MOD}\left( A4 \ , \ 10000\right)} \quad を入力します.\\
表のA6セルには\quad &{90} \equiv {40} \pmod {50}\quad を表す\quad \\ &\color{#0004fc}{=\mathrm{MOD}\left( A5 \ , \ 10000\right) }\quad を入力します.\\
表のA7セルには\quad &{40} \equiv {0} \pmod {10}\quad を表す\quad \\ &\color{#0004fc}{=\mathrm{MOD}\left( A6 \ , \ 10000\right)} \quad を入力します.\\
\end{align}$$

次にQUOTIENT関数で商を求める。

Lukia_74

Lukia

QUOTIENTは、クオシェントと読むんだそうです。
高校数学で、商を\( \ \mathrm{Q}\left( x\right) \ \)と表すことがあるのですが、あの\( \ \mathrm{Q} \ \)はテキト~においているわけじゃなくて、
この QUOTIENT (クオシェント)の頭文字からきていたんですね。

Excel(エクセル)のシートには、上の表の右側にさらに7列加えました。(A8~A14)
さらに、視認性を高めるため、各金種の合計枚数が最上の行(段)に表示されるように設定しました。

必要な枚数の合計
名前 金額 1
5
1
5
1
5
1
Aさん 27,890(A0) A8 A9 A10 A11 A12 A13 A14
合計

$$\begin{align}
表のA8セルには\quad &27890\div 10000\quad の商を表す\quad \\ &\color{#0004fc}{=\mathrm{QUOTIENT}\left( A0 \ , \ 10000\right) }\quad を入力します.\\
表のA9セルには\quad &7890\div 5000\quad の商を表す\quad \\ &\color{#0004fc}{=\mathrm{QUOTIENT}\left( A1 \ , \ 5000\right) }\quad を入力します.\\
表のA10セルには\quad &2890\div 1000\quad の商を表す\quad \\ &\color{#0004fc}{=\mathrm{QUOTIENT}\left( A2 \ , \ 1000\right) }\quad を入力します.\\
表のA11セルには\quad &890\div 500\quad の商を表す\quad \\ &\color{#0004fc}{=\mathrm{QUOTIENT}\left( A3 \ , \ 500\right) }\quad を入力します.\\
表のA12セルには\quad &390\div 100\quad の商を表す\quad \\ &\color{#0004fc}{=\mathrm{QUOTIENT}\left( A4 \ , \ 100\right)} \quad を入力します.\\
表のA13セルには\quad &90\div 50\quad の商を表す\quad \\ &\color{#0004fc}{=\mathrm{QUOTIENT}\left( A5 \ , \ 50\right) }\quad を入力します.\\
表のA14セルには\quad &40\div 10\quad の商を表す\quad \\ &\color{#0004fc}{=\mathrm{QUOTIENT}\left( A6 \ , \ 10\right)} \quad を入力します.\\
\end{align}$$

Lukia_74

Lukia

ここまで作ったら、A15セルに、再度入力した金額になるかどうかをたしかめる計算式を入れておきます。
さらに、金種表に必要なのは、QUOTIENT関数のほうなので、
QUOTIENT関数まで作成出来たら、MOD関数が入力されている7列は非表示にしておいてもかまいません。

数学と表計算ソフトは関連性がある。

これまでも、何かと便利がいいので、Word(ワード)よりは、Excel(エクセル)を活用してきましたが、
数学(関数)と関連付ければ、もっとすごいことが簡単にできそうだなぁ。と思いました。
以前も、厚さが4~5センチぐらいありそうな分厚いExcel(エクセル)の大事典を買ったことがあるのですが、活用できない間にOfficeのバージョンが変わってしまいました。
このたびは、以前よりはもっと活用できるとは思いますが、さすがにすべての関数が網羅されたものを活用する自信はないので、お値段も掲載された関数の数も手頃な本を買って、勉強しなおしてみようと思います。

カテゴリー