ExcelでMOD関数とQUOTIENT関数を用いて金種表を作成する。 【2021年版】
[mathjax]
複数人に現金支給しなければならないとき、
金種表があれば便利ですよね。
今回は、エクセルなどの表計算ソフトで、QUOTIENT関数とMOD関数、SUM関数を用いて、金種表を作る方法をご紹介します。テンプレートもありますよ。
Webライターに現金支給?
Webライター 47名のお給料を現金支給しなければならなくなり、
金種表が必要になったという想定です。
47名分のお給料を、それぞれの給料袋にきっちり入れるためには、
紙幣や硬貨、すなわち金種毎の必要枚数をあらかじめ過不足なく準備しておく必要があります。
そのためにも、金種表は必要ですね。
QUOTIENT関数とMOD関数で金種表を作る
表計算ソフトには、MOD(モッド、またはモード)関数と
QUOTIETNT(クオシェント)関数があります。
エクセル以外でも、Googleのスプレッドシートにもこの関数はあります。
今回は、この2つの関数を使って、それぞれ必要な金種の数を求め、
SUM関数を使って、支給総額と、それぞれの金種の合計枚数を求めていきます。
金種表、ダウンロードできます。
記事を読みながら、練習してみたい方は、以下のエクセルファイルをダウンロードしてください。
中には練習なんてしていられない!すぐに金種計算しなくちゃならないの!という方もいらっしゃると思います。そんな方にも朗報。
このエクセルファイルには、金種表のテンプレートが入っていますので、
ひとまずはそれを使って、余裕があるときに練習用シートで自分でも関数が入力できるようになってください。
ExcelでMOD関数とQUOTIENT関数を用いて金種表を作成する。
それでは、金種表の作成をしてみましょう。
QUOTIENT関数で「商(枚数)」を求める
エクセルファイルを開き、「練習用」シートを選択します。
1番 北海さんの37,932円は、
1万円札が3枚、
5千円札が1枚、
千円札が2枚、
500円硬貨が1枚、
100円硬貨が4枚、
10円硬貨が3枚、
1円硬貨が2枚が必要ですね。
これをQUOTIENT(クオシェント)関数とMOD(モッド、モード)関数を用いて、求めていきます。
QUOTIENT(クオシェント)とは「商」のことです。
D3セルに、=QUOTIENT(C3,D2の絶対参照)
を入力します。
北海さんのお給料37932を10000で割ったときの商は?
と書いていることになります。
算数でいうと、
37932÷10000
ですね。
D2(10000)をクリックしたら、F4キーを押して、「絶対参照」にしてください。
MOD関数で余りを求める
次は、5000円の必要枚数(商)を求めます。
E3セルに、=QUOTIENT(MOD(C3,D2の絶対参照),E2の絶対参照)
と入力します。
MOD(モッド、またはモード)とは、余りを求める関数です。
高校数学でも扱われています。
算数や数学で習ったとおり、カッコでくくられている場合、より内側が優先となります。
MOD関数は、
北海さんの給料37,932円を10000で割った余りを求めています。
37932÷10000=3・・・7932
商(QUOTIENT)は3、
余り(MOD)は7932
ですね。
外側のQUOTIENT関数は、
7932÷5000
で得られる商を求めています。
商は1ですね。
このように5000円以降は、給料を一つ前の金種で割ったときの余りを、該当する金種で割った商を求めていきます。
入力した関数は正しいか?
1円のところまで、QUOTIENT関数とMOD関数が入れられたら、
確かめをしてみましょう。
北海さんのお給料が正しく表示されるかどうか。
M3セルに、
=D2の絶対参照*D3+E2の絶対参照*E3+・・・+L2の絶対参照*L3
と入力します。
北海さんのお給料37,932円が表示されれば、間違いなく関数が入っていることがわかります。
この一行(横)だけしっかりと2つの関数を入れておけば、あとの46人分は自動入力することができます。
46人分を一気に自動入力する
D3セルをクリックすると、緑の枠で囲まれ、右下にドットがつきます。
そのあたりにマウスをホバーさせると、黒い十字が出てきます。
そこでダブルクリックすれば、D4以降のセルに関数が自動入力されます。
同様の操作をE列からM列まで行い、自動入力の便利さを堪能してください。
各金種の合計枚数を求める
ここまでで、それぞれにお給料を支払うために必要な金種毎の枚数を算出しました。
ここからは、SUM(サム)関数を用いて金種毎の合計数を求めていきます。
D1セルをクリックし、
=SUM(D3 から D49までドラッグ)を入力します。
すると、47人のお給料に必要な1万円札の合計枚数が算出されます。
再びD1セルをクリックして、セルの右下にマウスをホバーし、
黒い十字が出たら、L1セルまでドラッグして、各列のSUM関数を自動入力します。
お給料の支払い総額が出てびっくりすることでしょう。(笑)
すぐ使える金種表あります。
エクセルは、関数を覚えるとできることが増えて楽しいのですが、
今回のシチュエーションのように、練習なんてしていられない!
すぐにでも金種表を使って計算しなきゃいけない。という方のために、
名前と金額が入っていないテンプレートを準備しています。
実際に「金種表」シートを使いながら、今回ご紹介した3つの関数においおい慣れていくのもいいと思います。
ExcelでMOD関数とQUOTIENT関数を用いて金種表を作成する。
余談
個人的には、INTよりはQUOTIENT
金種表に使われる関数としては、ほかにINT関数や、TRUNC関数を紹介しているサイトもありました。
しっかり読んでいないのですが、小数点以下まで求めるようなので、
金種表で使うには、ちょっとやりすぎな感じがします。
だって、金額は、整数(自然数)ですからね。
QUOTIENTは、整数で表されるので、算数や数学的にもシンプルでいいかなと思います。
2019年版よりシンプルに
エクセルで金種表を作成する方法は、実は2019年にも書いています。しかし、当時はQUOTIENTとMODの列を別々に作成していたため、横に長い表となっていました。
また、金種表でほしいのはQUOTIENTだけなので、MODの列はグループ化や非表示対応でしのいでいました。
2021年は、2つの関数を入れ子型に書くことを覚えたので、
最小限の列でまかなえるシンプルな表になっています。
2000円札も使いたい場合は
めっきり見かけなくなった2000円札ですが、沖縄では、よく使われているんだそうです。
金種表に2000円札も含めたい場合は、5000円の列と1000円の列の間に2000円の列を作ります。
その場合、2000円以降の列の式は入れ直す必要がありますね。
アイディア次第でめちゃくちゃ使える
私は、このMOD関数とQUOTIENT関数を用いて、
袋詰めしたグッズを一定数ダンボール箱に詰める際、必要なダンボール箱の数はいくつか。とか、
アンケートの集計などに使いました。(機会があれば、別の記事でご紹介しますね)
エクセルなど表計算ソフトの関数って、難しそうですが、
覚えてしまえば、これほど応用範囲の広いものはありません。
ただ、本ではシチュエーションが設定されていないので、どう使ったらいいのかすぐには思いつかないのも事実。
でも、そこはネット社会の現代。
ネット上には、エクセルの達人がわんさかいらっしゃいます。
「こういうこと、できないの?」と思ったら、検索してみてください。
あなたが知りたいことを教えてくれる方がいらっしゃるかもしれませんよ。
ディスカッション
コメント一覧
まだ、コメントがありません