xls-hashimotoのExcel学

xls-hashimoto mougモーグ回答集

回答0010　レジスター回収金・つり銭金種計算表

「当日のレジにたまった売上金【レジ総金額】のうち、
100,000円を明日のつり銭用に残し、あとは回収する。
その際のつり銭と回収金の金種を計算したい」

ただし、各つり銭の金種の枚数は 50枚までとする。
つり銭の金種 50・10・5・1円の枚数は 10枚刻みとする。

との質問が掲載されました。

Excelで「仮計算を１回して計算する方法」を作り、次の様に回答しました。
ただし、2,000円はつり銭に残しません！

見直した結果間違いがあったので、次の様に修正しました。

Excelファイルは、次の様になります。

「レジスター回収金・つり銭金種計算表」(Exce22kB)

条件を変えて計算すると分かりますが、50枚縛りの中では、
100円玉または 1000円札が 0枚の時は、100,000円のつり銭は残せません。

この計算は、次の様な順番で行います。
まずは、100,000円を超える最小の枚数を仮計算します。

1.1. 1円の仮枚数 G25セル
=MIN(INT(G12/10)*10,50)
1円の10枚刻みで何10枚取れるかの計算値と、50枚との小さい方を仮枚数とする。

1.2. 5円の仮枚数 G24セル
=MIN(INT(G11/10)*10,50)
5円の10枚刻みで何10枚取れるかの計算値と、50枚との小さい方を仮枚数とする。

1.3. 10円の仮枚数 G23セル
=MIN(INT(G10/10)*10,50)
10円の10枚刻みで何10枚取れるかの計算値と、50枚との小さい方を仮枚数とする。

1.4. 50円の仮枚数 G22セル
=MIN(INT(G9/10)*10,50)
50円の10枚刻みで何10枚取れるかの計算値と、50枚との小さい方を仮枚数とする。

1.5. 100円の仮枚数 G21セル
=MIN(G8,50)
100円の枚数と、50枚との小さい方を仮枚数とする。

1.6. 500円の仮枚数 G20セル
=MIN(G7,50)
500円の枚数と、50枚との小さい方を仮枚数とする。

1.7. 1,000円の仮枚数 G19セル
=MIN(G6,50)
1,000円の枚数と、50枚との小さい方を仮枚数とする。

1.8. 2,000円の仮枚数 G18セル
0
2,000円は、つり銭として残さない。

1.9. 5,000円の仮枚数 G17セル
=MIN(ROUNDUP((100000-SUM(H18:H25))/F17,0),G4,50)
100,000円から1円～2,000円の仮金額を引いて5,000で割って求めた枚数
ROUNDUP((100000-SUM(H18:H25))/F17,0)と、
5,000円の枚数と、50枚との小さい方を仮枚数とする。

1.10. 10,000円の仮枚数 G16セル
=IF(ROUNDUP((100000-SUM(H17:H25))/F16,0)<0,0,ROUNDUP((100000-SUM(H17:H25))/F16,0))
100,000円から1円～5,000円の仮金額を引いて10,000で割って求めた枚数
ROUNDUP((100000-SUM(H17:H25))/F16,0)が、0より小さかったら0、
大きかったらその計算値を仮枚数とする。
ROUNDUP関数を使い、端数が出たら1枚増やして100,000円以上にするのが、１つ目のポイントです。

次に、求めた100,000円を超える仮金額を100,000円にするために、
それぞれの金種で差し引く枚数を計算して、残す枚数を求めます。

掲載のExcelファイルでは、仮金額が「101,600円」となるので、
差し引く金額として「1,600円」を求めれば良いことになります。
つまり、1,000円が1枚、500円が1枚、100円が1枚と求めることです。
各桁とも5円、50円、500円の枚数（ 0 or 1 ）を先に求め、
次に1円、10円、100円の枚数を求めるのが、２つ目のポイントです。

式の中では桁数を、LOG関数とINT関数を使って求めていますが、これは、
パソコンが世の中に存在していなかった時代に身についた悲しい癖です。
Excelの最新の機能を使うと、もっとスマートな式になる可能性があります。

2.1. 5円の枚数 I24セル
=G24-INT(MID($H$26,LEN($H$26)-INT(LOG(F24)),1)*10^INT(LOG(F24))/F24)
仮金額の 1円の桁の数値 MID($H$26,LEN($H$26)-INT(LOG(F24)),1) を求め、
それに *10^INT(LOG(F24)) を掛けて、金種5円 /F24 で割って、
整数部分 INT をとると、差し引く枚数が求まるので、
これを仮枚数から差し引く事で、5円の残す枚数が計算出来ます。

2.2. 1円の枚数 I25セル
=G25-INT((MID($H$26,LEN($H$26)-INT(LOG(F25)),1)*10^INT(LOG(F25))-F24*(G24-I24))/F25)
仮金額の 1円の桁の数値 (MID($H$26,LEN($H$26)-INT(LOG(F25)),1)*10^INT(LOG(F25)) から
すでに、5円で差し引いた数値 -F24*(G24-I24) を差し引いて、金種1円 /F25 で割って、
整数部分 INT をとると、差し引く枚数が求まるので、
これを仮枚数から差し引く事で、1円の残す枚数が計算出来ます。

2.3. 50円の枚数 I22セル
=G22-INT(MID($H$26,LEN($H$26)-INT(LOG(F22)),1)*10^INT(LOG(F22))/F22)
仮金額の 10円の桁の数値 MID($H$26,LEN($H$26)-INT(LOG(F22)),1) を求め、
それに *10^INT(LOG(F22)) を掛けて、金種50円 /F22 で割って、
整数部分 INT をとると、差し引く枚数が求まるので、
これを仮枚数から差し引く事で、50円の残す枚数が計算出来ます。

2.4. 10円の枚数 I23セル
=G23-INT((MID($H$26,LEN($H$26)-INT(LOG(F23)),1)*10^INT(LOG(F23))-F22*(G22-I22))/F23)
仮金額の 10円の桁の数値 (MID($H$26,LEN($H$26)-INT(LOG(F23)),1)*10^INT(LOG(F23)) から
すでに、50円で差し引いた数値 -F22*(G22-I22) を差し引いて、金種10円 /F23 で割って、
整数部分 INT をとると、差し引く枚数が求まるので、
これを仮枚数から差し引く事で、10円の残す枚数が計算出来ます。

2.5. 500円の枚数 I20セル
=G20-INT(MID($H$26,LEN($H$26)-INT(LOG(F20)),1)*10^INT(LOG(F20))/F20)
仮金額の 100円の桁の数値 MID($H$26,LEN($H$26)-INT(LOG(F20)),1) を求め、
それに *10^INT(LOG(F20)) を掛けて、金種500円 /F20 で割って、
整数部分 INT をとると、差し引く枚数が求まるので、
これを仮枚数から差し引く事で、500円の残す枚数が計算出来ます。

2.6 100円の枚数 I21セル
=G21-INT((MID($H$26,LEN($H$26)-INT(LOG(F21)),1)*10^INT(LOG(F21))-F20*(G20-I20))/F21)
仮金額の 10円の桁の数値 (MID($H$26,LEN($H$26)-INT(LOG(F21)),1)*10^INT(LOG(F21)) から
すでに、500円で差し引いた数値 -F20*(G20-I20) を差し引いて、金種100円 /F20 で割って、
整数部分 INT をとると、差し引く枚数が求まるので、
これを仮枚数から差し引く事で、100円の残す枚数が計算出来ます。

2.7. 5,000円の枚数 I17セル
=G17-INT(MID($H$26,LEN($H$26)-INT(LOG(F17)),1)*10^INT(LOG(F17))/F17)
仮金額の 1,000円の桁の数値 MID($H$26,LEN($H$26)-INT(LOG(F17)),1) を求め、
それに *10^INT(LOG(F17)) を掛けて、金種5,000円 /F17 で割って、
整数部分 INT をとると、差し引く枚数が求まるので、
これを仮枚数から差し引く事で、5,000円の残す枚数が計算出来ます。

2.8. 2,000円の枚数 I18セル
0
2,000円は、つり銭として残さない。

2.9 1,000円の枚数 I19セル
=G19-INT((MID($H$26,LEN($H$26)-INT(LOG(F19)),1)*10^INT(LOG(F19))-F17*(G17-I17))/F19)
仮金額の 10円の桁の数値 (MID($H$26,LEN($H$26)-INT(LOG(F19)),1)*10^INT(LOG(F19)) から
すでに、5,000円で差し引いた数値 -F17*(G17-I17) を差し引いて、金種1,000円 /F19 で割って、
整数部分 INT をとると、差し引く枚数が求まるので、
これを仮枚数から差し引く事で、1,000円の残す枚数が計算出来ます。

2.10. 10,000円の枚数 I16セル
=G16-INT(MID($H$26,LEN($H$26)-INT(LOG(F16)),1)*10^INT(LOG(F16))/F16)
仮金額の 10,000円の桁の数値 MID($H$26,LEN($H$26)-INT(LOG(F16)),1) を求め、
それに *10^INT(LOG(F16)) を掛けて、金種10,000円 /F16 で割って、
整数部分 INT をとると、差し引く枚数が求まるので、
これを仮枚数から差し引く事で、10,000円の残す枚数が計算出来ます。

レジスター（５台）別に、
「売上金・回収金・つり銭の金種を計算する一覧表」
の１つのサンプルは、次の様になります。
ただし、青色セルは実際の枚数、緑色セルは増減(±)の調整枚数です。
両替して小銭を用意するための、金種毎の枚数の確認計算に使います。、
この結果は、回収金とつり銭の金種毎の枚数に必ず反映させます。
合計金額が 0 にならないと、緑色セル内の全ての数値が赤色になります。

「レジスター別売上金・回収金・つり銭金種表」(Exce42kB)

「xls-hashimoto mougモーグ回答集」に戻る

トップページに戻る

xls-hashimotoのExcel学

xls-hashimoto mougモーグ 回答集

xls-hashimoto mougモーグ回答集