ブログ練習帳ブログ練習帳かけだしWebデザイナー見習のボロボロブログホームブログ練習帳についてわたしのサイト見習い奮闘記くつろぐ、たびログ新しい記事お問い合わせExcelで金種計算(複数の人にお金を分けるとき)まとめてお金を下ろしてきて、複数の人に分けなければならない!でも、それぞれの金種で何枚ずつ出金すればいいのかしら????計算がめちゃくちゃ苦手な見習が出くわした課題。銀行の出金伝票には、10000円、5000円、2000円、1000円...、それぞれの金種の枚数を指定する欄があって、そこに枚数を記入するのみだけど、指折り計算すると間違いがありそう。そんなときに、必要枚数を計算してくれるExcelブックを考えました。(スクリーンショットはExcel2013でとりましたが、基本的な機能だけなので、どのバージョンでもだいたい同じです)金種計算用のブックこんな風なのです。B3:B17に、それぞれ必要な金額を入力していきます。私の場合、15人くらいに分けるのがマックスっぽかったので、15行分です。すると、O列に、それぞれ10000円から1円まで必要枚数を算出した結果が表示されるというもの。黄色いエリアに入力するだけで、あとは自動計算になります。ここで使うものは、基本的な関数と機能ばかり。関数についてはいくつか別案もあるけれども、基本的なもので作成してみました。あと、2000円札については、あえて割愛。2000円札で出金したくないので...。非表示の列で計算まず、最初にですが、実はC:K列を非表示にしてありました。ここで途中計算させることで、簡単な関数のみで計算ができます。配列をつかったりすることで、途中計算を省くこともできますが...。表示形式を使って、「円」や「枚」を表示させることで、それぞれの数をExcelに「数値」として認識させ、計算できるようにしています。C2:K2には、左から10000、5000、1000、500、100、50、10、5、1と入力、N4:N12は、上から、10000、5000、1000、500、100、50、10、5、1と入力しておきます。「円」とついているセルB3:B18、C2:K2、N4:N12は、表示形式を「#,###”円”」と設定する(ユーザー定義)。「枚」とついているセルC3:K18、O4:O12は、表示形式を「#,##0′′枚”」と設定する(ユーザー定義)。C3:K17エリアの計算このエリアは、それぞれ一人分に必要な金種を計算するエリアです。ここでは、B列に入力した金額をその行内でそれぞれの金種の枚数を計算します。C3:C17の計算ここはそれぞれ1万円札の枚数を計算します。C列では式を入力するのはC3のみで、あとはオートフィルします。C3に入力するのは、「=ROUNDDOWN($B3/C$2,0)」です。この式の意味は、「B3の値をC2の値(つまり10000)で割り、その値を整数になるよう切り捨てする」という意味。「$B3/C$2」とドルマークを使って複合参照にしたことで、C4:C17はオートフィルで簡単入力できます。(オートフィルの関係上「B3/$C$2」でもいける)D3:K17の計算このエリア、D3にのみ式を入力すれば、あとはオートフィルでいけます。D3に入力するのは、「=ROUNDDOWN(MOD($B3,C$2)/D$2,0)」です。この式の意味は、「B3の値(25637)をC2(10000)で割った余り、つまり5637を、D2(5000)で割って、その値を整数になるように切り捨てする」という意味です。B3の値から10000円札で支払える金額を引いて、その数を5000円で割って整数で切り捨てすることで、5000円札の必要枚数を算出しています。ここでポイントになるのは、それ以降の金種1000円札とか500円玉の扱いです。それぞれの金種の枚数を調べるときは、一つ前の金種で払えない端数の金額のみをその金種で払えるか調べるだけで大丈夫。(これが、2000円札が入ると、そうならないところがあるけれど...)1000円札の枚数は、5000円で払えない端数のみ、500円の枚数は、1000円で払えない端数のみ、100円の枚数は500円で払えない端数のみ計算すればいい。いうことで、「MOD($B3,C$2)/D$2」というように複合参照を使ってあげることにより、D3:K17エリアは、D3にみに式を入力することで、あとはオートフィルでOKです。B18:K18の計算ここは単純に合計を出すエリアなのでB18に「=SUM(B3:B9)」と入力して、C18:K18へオートフィルすればOK。O4:O12の式C18:K18で、すでに答えは出ているのですが、C:K列は計算過程で、使う人には見えなくていい。非表示にします(C:Kを列選択後、右クリックから「非表示」)。C18:K18は、O4:O12に見やすく転記してあげます。単なる転記なので、リンク貼り付けでOK。O4には「=C18」、O5には「=D18」、O6には「=E18」...というようにO12の「=K18」までリンク貼り付けを設定します。★キー操作の短縮のため...データの入力規則このブックでは、使用時には、B3:B17に半角で数値を入力するだけです。効率よく半角入力できるようにします。「オフ(英語モード)」にし、OKをクリックします。セルの保護このブック、使うときに入力するのは、B3:B17のみで、あとは入力すべきところではないです。間違って数式を消してしまわないよう、セルに保護をかけます。B3:B17の「セルのロック」をはずします。B3:B17を選択後、「ホーム」⇒「書式」⇒「セルのロック」をクリックします。セルのロックをはずすその後、「ホーム」⇒「書式」⇒「シートの保護」をクリックし、「ロックされていないセル範囲の選択」にのみチェックをつけて「OK」をします。単なるミス防止なので、パスワードは設定しなくてもOK。シートの保護はここからパスワードは任意にこれで必要な設定はおわり。あとは、セルに色をつけたり、罫線を引いたりして、「どこに入力すればいいのかしら」と直感的にわかるようにしてあげれば完成。コメントをキャンセルメールアドレスが公開されることはありません。*サイト上に表示された文字を入力してください。