ほっとひといき給湯室

ほっとひといき給湯室の掲示板です。お気軽にどうぞ!
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
小口現金の取り扱い関連で、「両替」について
投稿日時: 19/08/21 16:51:37
投稿者: WinArrow
投稿者のウェブサイトに移動

複数人への立替払いの場面で、
各々の人への支払金額から金種毎の必要個数は、いわゆる「金種計算」で求めることはできます。
 
ところが手持現金(金種別)と照合すると、いくつかの金種の個数が不足することがあります。
そんな時、両替をすると思いますが、
エクセルの数式を使って、最も少ない個数への両替計算ができないものかと
悩んでいます。
ありそうなツールですが、探してもヒットしない。・・・探し方が悪いのかな?
 
相手の口座に振り込めば、両替計算しなくてもよいことはわかっていますが、
口座振込には手数料が発生するので、できない事情があります。
なお、上位貨幣から下位貨幣への両替計算だけで、下位貨幣で上位貨幣を代用することは
今回の計算には含まないものとします。
 
アイデアがありましたら、お願いします。

回答
投稿日時: 19/08/25 10:26:16
投稿者: simple

すみませんが、簡単な実例を示してもらえませんか?
そうすれば、問題のイメージがより明確になって、
コメントが寄せられるのではないかと思います。

投稿日時: 19/08/25 16:40:01
投稿者: WinArrow
投稿者のウェブサイトに移動

>簡単な実例を示してもらえませんか?
 分かりにくて申し訳ありません。
  
簡単なれいです。
 3名(A、B、C)から立替代金の請求がありました。
これを金種計算すると、下表の様に金種別の個数が求められます。
 金種計算は、最も少ない個数で支払うため計算です。
 3名分の金種別個数の合計を満たす手持現金(金種別)があれば、
 問題はありませんが、手持の個数が不足する場合、 両替をする必要が出てきます。
 
   
     支払先等→Åさん Bさん Cさん
金種 個数合計  6,120 3,091 2,015
 10000
 5000 1    1
 1000 6    1    3   2
 500
 100 1           1
 50 1    1
 10 7      2    4   1
 5 1                1
 1 1    1
合計 18  6,120  3,091  2,015
   
   
金種"手持個数" "必要個数"
10000   1     0
 5000 0   1
 1000   10    6
 500    1     0
 100  1     1
 50    0      1
 10    1      7
 5     3     1
 1     0     1
計 20,525 11,226
   
とあった場合
 上位貨幣から下位貨幣への両替計算を数式で対応する方法がありませんか?
ということです。
 

回答
投稿日時: 19/08/27 12:00:48
投稿者: 素人眼鏡

時間があったので考えてみましたが、これ難しいですね。
 
示してい頂いた例で両替対象の貨幣を出すとこまで作ってみましたが、
実際に使うとき人数が増えて、と条件を変えて考えた場合に以下の条件の
反映方法を考えているとこで躓きました。
 
両替対象の貨幣を跨ぐ場合に、最下位貨幣の両替までに、上位貨幣で
使い切ってしまうときの計算。
(例で言うと50円と10円を作るのに500円を使いますが、50円の両替で
500円を使いきる場合の両替貨幣の判定…これがまず、そこそこ厄介。
さらにもっと桁を跨いだ場合を反映しようとすると、なかなか面倒。
金銭計算が慣れている人だと上記の場合、結局1000円を両替するのが
いいとサクッと出ますが、その考え方を数式にすると判定箇所が多い…)
 
>エクセルの数式を使って、最も少ない個数への両替計算
も考えてみましたが、こちらも上記の類似で上位貨幣両替後の余りからの
両替で足りるのかなどを補完できないとだめですね。
(例で言うと100円分を50円1個、10円5個にしても10円の必要分に足りない
ので、200円分を50円2個、10円10個にする必要がある。
逆に足りる場合は100円分を50円1個、10円5個にするというのを式と結果に
示すのが、なかなか厄介。
上記だと2種ですが、3種まではあり得ると思うので、それも反映となると…)
 
と言ったところで私はいったん心が折れたので、条件の気付きに投稿です。
 

回答
投稿日時: 19/08/27 21:19:13
投稿者: simple

・10000円を 5000円×2
・ 500円を 100円×3 + 50円×2 + 10円×10
・ 5円を 1円×5
という両替を行う、というのが答えなんでしょうか。
 
 

 金種  必要個数  手持個数      両替      最終形
10000          0         1        -1          0
 5000          1         0         2          2
 1000          6        10         0         10
  500          0         1        -1          0
  100          1         1         3          4
   50          1         0         2          2
   10          7         1        10         11
    5          1         3        -1          2
    1          1         0         5          5

投稿日時: 19/08/27 21:52:18
投稿者: WinArrow
投稿者のウェブサイトに移動

simple さんの引用:
・10000円を 5000円×2
・ 500円を 100円×3 + 50円×2 + 10円×10
・ 5円を 1円×5
という両替を行う、というのが答えなんでしょうか。

 
そうです、その通りです。

回答
投稿日時: 19/08/27 23:35:32
投稿者: simple

計算式で求めるのは難しそうですねえ。
 
1)ソルバー利用
2)マクロ利用
では何とかなった気がするけど。

回答
投稿日時: 19/08/28 06:44:33
投稿者: simple

 金種  必要個数  保有枚数
10000     0         x1
 5000     1         x2
 1000     6         x3
  500     0         x4
  100     1         x5
   50     1         x6
   10     7         x7
    5     1         x8
    1     1         x9

金種毎の必要個数をAiと書くとき、
制約条件:
    各xi は 0 以上の整数  
    Σ(Ai * xi) = 20625
    Ai <= xi
のもとで、Σxi を最小にする
という問題ということでは?
 
ソルバーで解かせると良い問題なので、
その解が計算式で書けなくても不思議はないかもしれません。
整数計画法の解が解析的に解けてしまうという話は聞きません。
 

こんなアルゴリズムを考えてみた。
(1)上位から見ていき、不足する金種があれば、
   その上位貨幣を直ぐ下の貨幣に両替する。
(2)もちろん、手持ち個数の範囲内で実行するので、
   上位方向に順に見ていくことになる。
   (この作業を、仮に「基本両替」と呼ぶことにする。)
(3)上記の「基本両替」を、不足金種がなくなるまで何度も繰り返す。
(4)それらの「基本両替」を合計したものが、必要とする両替となるのでは?
 
        必   手                                                両
        要   持                                          両    替
        個   個                                          替    後
金種    数   数                                          計    数
10000   0     1    -1                                    -1     0
 5000   1     0     2                                     2     2
 1000   6    10                                           0    10
  500   0     1          -1                              -1     0
  100   1     1           5    -1         -1              3     4
   50   1     0                 2    -1    2   -1         2     2
   10   7     1                       5         5        10    11
    5   1     3                                      -1  -1     2
    1   1     0                                       5   5     5

こんな感じでどうだろうか。
あとは、こいつをマクロで書けばOKということになる。

投稿日時: 19/08/28 10:49:39
投稿者: WinArrow
投稿者のウェブサイトに移動

simpleさん、ご回答ありがとうございます。
 
数式では無理かな‥‥とは思っていました。
 
ご提案の両替の回数を、左から@ABCDEと6回としたとき、
 
Aの500円を100円に両替する場面ですがm
 
100円が、手持個数>=必要個数の関係にあるため、
個数チェックだけではAは発生しない。
Bの100円→50円の段階で、もう一つ上の500円を両替する必要が出てくる
これがAということで考えたよいのですよね?

回答
投稿日時: 19/08/28 11:45:29
投稿者: simple

Aのところは、
50円の個所の不足を埋めるために
2ステップ上から両替したということですね。
 
最適枚数にするため、
すぐ下の貨幣にしか両替しない。
ただし何度も繰り返し実行する
というところがミソかと。
 
遠い昔に聞いた「置換は必ず互換の積で生成される」
なんていう話を思い出しました。

投稿日時: 19/08/28 14:13:46
投稿者: WinArrow
投稿者のウェブサイトに移動

2つ上の金種から両替・・・・
 
極端な話、
 
金種毎の必要個数に対して、
10000円しか手持がない場合、1円をつくるために、1万円までさかのぼるということも出てきます。
上から下へ・・・という一方向だと考えやすいが、下から上へ、両替元を探すことになると、
ロジックが大変になりそう!!!
 
でも、個数だけではなく、金額を加味すると、上から下への一方向になるような気がします。
 
やっぱり難しいですね・・・

投稿日時: 19/08/28 18:03:59
投稿者: WinArrow
投稿者のウェブサイトに移動

個数と金額(当該金種以下のサマリ金額)
両方で判断したマクロ
とりあえずできたと思います。
 
名前定義:M_OUTPUTが完成形です。
 
 
シート名:両替計算
 
名前定義;金種;A4:A12
 
名前定義;M_INPUT;A4:C12
 
セルの値/数式
 
A4:10000
A5:5000
A6:1000
A7:500
A8:100
A9:50
A10:10
A11:5
A12:1
 
B4:1
B5:0
B6:10
B7:1
B8:1
B9:0
B10:1
B11:3
B12:0
 
名前定義;手持現金;B13:=SUMPRODUCT((金種)*(B4:B12))
 
C4:0
C5:1
C6:6
C7:0
C8:1
C9:1
C10:7
C11:1
C12:1
C13:=SUMPRODUCT((金種)*(C4:C12))
 
名前定義;M_OUTPUT;E4:G12
 
 
マクロ
Sub 両替試算()
 
Dim KINTBL, Kx1 As Long, kx2 As Long, kin1 As Long, kin2 As Long
Dim KOSU As Long
 
    With Sheets("両替計算")
        If .Range("手持現金").Value < .Range("手持現金").Offset(, 1).Value Then
            MsgBox "必要金額に対して手持金額が不足しています。"
            Exit Sub
        End If
     
        KINTBL = .Range("M_INPUT").Value
        For Kx1 = LBound(KINTBL) To UBound(KINTBL)
            kin1 = 0: kin2 = 0
                 
            For kx2 = Kx1 + 1 To UBound(KINTBL)
                kin1 = kin1 + KINTBL(kx2, 1) * KINTBL(kx2, 2)
                kin2 = kin2 + KINTBL(kx2, 1) * KINTBL(kx2, 3)
            Next
            If kin2 > kin1 Or KINTBL(Kx1, 2) < KINTBL(Kx1, 3) Then
                KOSU = Int((kin2 - kin1) / KINTBL(Kx1, 1)) + 1
                KINTBL(Kx1, 2) = CLng(KINTBL(Kx1, 2)) - KOSU
                KINTBL(Kx1 + 1, 2) = KINTBL(Kx1 + 1, 2) + IIf(Left(KINTBL(Kx1 + 1, 1), 1) = 5, 2, 5) * KOSU
            End If
        Next
        .Range("M_OUTPUT").Value = KINTBL
        With .Range("M_OUTPUT")
            For Kx1 = 1 To .Rows.Count
                If .Cells(Kx1, 2) < .Cells(Kx1, 3) Then
                    MsgBox .Cells(Kx1, 1) & "円の両替後の個数が不足しています"
                End If
            Next
        End With
    End With
End Sub
 
 
 
 
 
 
 

投稿日時: 19/08/28 18:14:20
投稿者: WinArrow
投稿者のウェブサイトに移動

項目タイトルを忘れていました。
 
A3:金種
B3:手持個数
C3:必要個数
 
E3:金種・・・A列と同じ
F3:両替後個数
G3:必要個数・・・C列と同じ
 
I列:両替前
J列:両替後
 
I4:=IF(B4>F4,B4-F4,0)
J4:=IF(F4>B4,F4-B4,0)
I4:J4を下へフィルドラッグ
 

回答
投稿日時: 19/08/28 23:02:06
投稿者: simple

完成されたようでなによりです。
 
ところで、
> 下位貨幣で上位貨幣を代用することは
> 今回の計算には含まないものとします。

というのは、どこで考慮されているんでしょうか。
問題によっては、KOSUがマイナスになってしまいませんか?
 
その場合、下位の貨幣を上位の貨幣にまるめることになります。
「くずす」の逆方向ですね。それはアリなんでしょうか。

投稿日時: 19/08/29 09:23:00
投稿者: WinArrow
投稿者のウェブサイトに移動

simple さんの引用:
完成されたようでなによりです。
 
ところで、
> 下位貨幣で上位貨幣を代用することは
> 今回の計算には含まないものとします。

というのは、どこで考慮されているんでしょうか。
問題によっては、KOSUがマイナスになってしまいませんか?
 
その場合、下位の貨幣を上位の貨幣にまるめることになります。
「くずす」の逆方向ですね。それはアリなんでしょうか。

 
ご懸念はごもっともだと思います。
全てのパターンをテストしたわけではないので、コードが完成したとは言い難いのですが、
「くずす」の逆方向をコードに組み込んでしまうと、かえって混乱するので、
それは、組み込んでありません。
 
また、KOSUがマイナス(0を含む)になるっ条件は、
全下位貨幣の金額が足りて、当該貨幣の個数が不足している場合に発生します。
金額と個数の両方をチェックすることで、両替が成立するっ物と考えています。
 
下位貨幣で上位貨幣を代用する場合、
例えば、5千円を支払うのに、5千札がない場合、
千円札x5というパターンも
千円札x4+500円硬貨x2というパターンもあります。
個々は人間の判断になると考え、両替後に不足する金種をメッセージで表示しています。
 
通常、両替というと、自貨幣が不足する場合、上位貨幣を両替しようと考えます(甘えん坊タイプ)。
そろばんでも引き算で自桁の数が足りない場合、上位の桁から借りてくるという発想になっています。
今回のコードは、上位貨幣から見て全下位貨幣で不足していたら両替してあげよう
題して「世話焼きタイプ」という発想によるものです。
 
テストパターンを追加して、確認するつもりです。
 

回答
投稿日時: 19/08/29 09:31:44
投稿者: simple

金種	保有	必要
10000	1	0
5000	0	3
1000	15	6
500	1	0
100	1	1
50	0	1
10	1	7
5	3	1
1	0	1

といったケースでは、「逆にくずす」結果が得られます。

投稿日時: 19/08/29 11:36:29
投稿者: WinArrow
投稿者のウェブサイトに移動

ロジックの結果、
 
自金種の手持がない場合、
下位金種に加算して、自金種から差し引いていなかった。
チェック漏れがありました。
 
例示の例では
5000円が1枚不足します。
 
この場合、逆くずし(下位貨幣を上位貨幣には両替する)のロジックを組み込んでありません。
 
どの貨幣で代用するかは、人手で判断すルということです。
 
例示のばあいは、千円札x5枚で代用すればよいことになります。
 
また、実運用では、ここでの計算結果で運用しなさいということを考えているわけではありません。
例えば、991円を小銭だけで支払ってもらいよりは、
千円札x1を貰い、おつり9円を返した方が、お互いに効率的だと思います。
 
 

回答
投稿日時: 19/08/29 13:04:15
投稿者: simple

ご返事ありがとうございます。
 
>この場合、逆くずし(下位貨幣を上位貨幣には両替する)の
>ロジックを組み込んでありません
結果として、すぐ下の貨幣を上に持ち上げる
ことになっていますね。
この場合は、解なしとするんでしょうね。
 
ありがとうございました。

投稿日時: 19/08/29 15:18:13
投稿者: WinArrow
投稿者のウェブサイトに移動

この問題に至った経緯を説明しておかなかったので、
誤解を与えてしまったと思います。
 
複数の人の立替金を支払うのに、金種毎の手持の現金では、足りない・・・・
とき、請求1件毎に、足りない/足りるをチェックして、場当たり的な両替を繰り返すのではなく
纏めて両替するためのツールを考えることは、できないかと始めました。
 
どこの経理でも、金種別に現金の在庫管理をしているわけではないと思いますが、
甲斐貨幣で上位貨幣を代用する(つまり、5千円札が1枚足りなくても千円札や、500円硬貨が余っていればそれを代用して支払うということ)とか、請求額が991円の場合、1000円支払って9円おつりを貰う
というような人間の判断を残しながら運用できればと思っています。
そういう意味から、ツールで正解を求める必要もないのではないかと思います。
 
現段階の最終コードをアップしておきます。
 

Sub 両替試算()

Dim KINTBL, Kx1 As Long, kx2 As Long, kin1 As Long, kin2 As Long
Dim KOSU As Long  ', HUSOKU As Long, KAIKIN As Long

    With Sheets("両替計算")
        If .Range("手持現金").Value < .Range("手持現金").Offset(, 1).Value Then
            MsgBox "必要金額に対して手持金額が不足しています。"
            Exit Sub
        End If
    
        .Unprotect
        KINTBL = .Range("M_INPUT").Value
        For Kx1 = LBound(KINTBL) To UBound(KINTBL)
            kin1 = 0: kin2 = 0
                
            For kx2 = Kx1 + 1 To UBound(KINTBL)
                kin1 = kin1 + KINTBL(kx2, 1) * KINTBL(kx2, 2)
                kin2 = kin2 + KINTBL(kx2, 1) * KINTBL(kx2, 3)
            Next
            If kin2 > kin1 Or KINTBL(Kx1, 2) <= KINTBL(Kx1, 3) Then
                If KINTBL(Kx1, 2) > KINTBL(Kx1, 3) Then
                    KOSU = Int((kin2 - kin1) / KINTBL(Kx1, 1)) + 1
                    KINTBL(Kx1, 2) = CLng(KINTBL(Kx1, 2)) - KOSU
                    KINTBL(Kx1 + 1, 2) = KINTBL(Kx1 + 1, 2) + IIf(Left(KINTBL(Kx1 + 1, 1), 1) = 5, 2, 5) * KOSU
                End If
            End If
        Next
        .Range("M_OUTPUT").Value = KINTBL
        With .Range("M_OUTPUT")
            For Kx1 = 1 To .Rows.Count
                If .Cells(Kx1, 2) < .Cells(Kx1, 3) Then
                    MsgBox .Cells(Kx1, 1) & "円の両替後の個数が不足しています"
                End If
            Next
        End With

        .Protect
    End With
End Sub

 
なぜ、逆「くずし」のこだわっているかというと
1000円札x1枚不足の場合、500円x2で代用できればいいが、
10円x100個なんてことになると、受け取る方が迷惑しますよね?
 
上位貨幣に両替する場合は、個数制限、又は、下位2段階までといった制限が必要いなるのではないかと思います。
 
最終のメッセージが両替後の個数が不足をアナウンスするところです。
このメッセージで人が下位貨幣からの代用支払いを考えます。
 
その方が運用しやすいと考えています。
 
まだ、テストパターンが不足していると思っていますので、引き続きテストします。
 
 
 
 

回答
投稿日時: 19/09/02 18:00:58
投稿者: 素人眼鏡

simple さんの引用:

こんなアルゴリズムを考えてみた。
(1)上位から見ていき、不足する金種があれば、
   その上位貨幣を直ぐ下の貨幣に両替する。
(2)もちろん、手持ち個数の範囲内で実行するので、
   上位方向に順に見ていくことになる。
   (この作業を、仮に「基本両替」と呼ぶことにする。)
(3)上記の「基本両替」を、不足金種がなくなるまで何度も繰り返す。
(4)それらの「基本両替」を合計したものが、必要とする両替となるのでは?
 
        必   手                                                両
        要   持                                          両    替
        個   個                                          替    後
金種    数   数                                          計    数
10000   0     1    -1                                    -1     0
 5000   1     0     2                                     2     2
 1000   6    10                                           0    10
  500   0     1          -1                              -1     0
  100   1     1           5    -1         -1              3     4
   50   1     0                 2    -1    2   -1         2     2
   10   7     1                       5         5        10    11
    5   1     3                                      -1  -1     2
    1   1     0                                       5   5     5

こんな感じでどうだろうか。
あとは、こいつをマクロで書けばOKということになる。

 
WinArrowさんはマクロで対応しているので、蛇足ですが・・・。
simpleさんのアルゴリズムは実運用でシートを横に長く使うのを気にしなければ、擬似的に関数でも
再現できますね。
上から順に・・・とはなりませんが、マイナスがなくなるまで繰り返すと、手持ちの個数が足りる限り、
上位の貨幣からの両替を再度するので、同様の答えが出ました。
 
 
一応、話に出てきた下位貨幣で上位貨幣の補填する場合も考慮して作ってみましたが、計算してるだけになってます。
(下位→上位は、計算時点で上位から引いていって引けない、且つ直下の貨幣で補填できる場合に実施、以下の例では@、Aで補填しています。)
また、実際の構成では各両替の間には、以降の計算用に差分の結果を出す列を設けています。
 
        必   手  必                          両
        要   持  要  両  両  両  両  両  両  替
  金    個   個  差  替  替  替  替  替  替  枚
  種    数   数  分  @  A  B  C  D  E  数
10000    1    2  -1   1                       1
 5000    2    0   2  -2                      -2
 1000   10   10   0                           0
  500    1    2  -1       1                   1
  100   10    1   9      -5      -1          -6
   50    0    0   0          -1   2  -1       0
   10    1    7  -6           5       5      10
    5    3    1   2                      -1  -1
    1    0    1  -1                       5   5

 
実際の構成と私が作った式は以下です。
両替1、差分1以降はこの二つをセットに列方向にフィルコピーしていきます。
(5000円の両替数式には不要部分がありますが、作った式を縦方向にフィルコピーで済ませたかったのと
結果に影響ないので、そのまま残しています。修正のときに個別のところが多いと面倒ですし。)
なお、検証しきっていないので、おかしくなるパターンもあるかもしれません。
 
最後の両替枚数は、両替列の足し算なので数式は割愛しますが、両替計算が以下のパターンのときに視覚的にわかりやすいように、それぞれの場合に条件付書式でセルに色をつけてます。
例の場合、5000円の-2は1万円へ補填のみなので@の条件、100円は500円へ補填と10円への両替なのでAの条件でセルの色が変わります。
(1万円は適用外、適用はAを@より優先順位を上に。簡潔な式が思い当らなかったので力技です。)
 
@下位貨幣で上位貨幣の代用を行う場合(黄色を設定)
・頭1:=AND($Y4<-4,OR(E4=-5,G4=-5,I4=-5,K4=-5,M4=-5,O4=-5))
・頭5:=AND($Y3<-1,OR(E3=-2,G3=-2,I3=-2,K3=-2,M3=-2,O3=-2))
A下位貨幣で上位貨幣の代用と下位貨幣への両替を行う場合(橙を設定)
・頭1:=AND($Y4<-5,OR(E4=-1,G4=-1,I4=-1,K4=-1,M4=-1,O4=-1),OR(E4=-5,G4=-5,I4=-5,K4=-5,M4=-5,O4=-5))
・頭5:=AND($Y3<-2,OR(E3=-1,G3=-1,I3=-1,K3=-1,M3=-1,O3=-1),OR(E3=-2,G3=-2,I3=-2,K3=-2,M3=-2,O3=-2))
 
A(金種) B(手持ち) C(必要) D(必要差分)        
10000      1           2        =B2-C2   
 5000      2           0        =B3-C3   
 1000     10          10        =B4-C4   
  500      1           2        =B5-C5   
  100     10           1        =B6-C6   
   50      0           0        =B7-C7   
   10      1           7        =B8-C8   
    5      3           1        =B9-C9   
    1      0           1        =B10-C10

 
E(両替1)
・10000円
=IF(AND(D2<0,D3>=2),1,IF(AND(D2>0,D3<0),-1,""))
・5000円
=IF(COUNTIF(E$2:E2,-5)+COUNTIF(E$2:E2,-2)>0,"",IF(E2=1,-2,IF(E2=-1,2,IF(AND(COUNTIF(E$2:E2,-1)=0,D2>=0,D3>=0,D4<0,D5<=2,D4+1<=0),-1,IF(AND(COUNTIF(E$2:E2,-1)=0,D3<0,D4>=5),1,"")))))
・1000円
=IF(COUNTIF(E$2:E3,-5)+COUNTIF(E$2:E3,-2)>0,"",IF(E3=1,-5,IF(E3=-1,5,IF(AND(COUNTIF(E$2:E3,-1)=0,D3>=0,D4>=0,D5<0,D6<=5,D5+1<=0),-1,IF(AND(COUNTIF(E$2:E3,-1)=0,D4<0,D5>=2),1,"")))))

〜500円から10円は5000円&1000円のセットを下方向にフィルコピー〜

・5円
=IF(COUNTIF(E$2:E8,-5)+COUNTIF(E$2:E8,-2)>0,"",IF(E8=1,-2,IF(E8=-1,2,IF(AND(COUNTIF(E$2:E8,-1)=0,D8>=0,D9>=0,D10<0),-1,IF(AND(COUNTIF(E$2:E8,-1)=0,D9<0,D10>=5),1,"")))))
・1円
=IF(COUNTIF(E$2:E9,-5)+COUNTIF(E$2:E9,-2)>0,"",IF(E9=1,-5,IF(E9=-1,5,"")))

 
F(差分1)
・10000円
=IF(OR(E2=-1,E2=1),D2+E2,D2)
・5000円
=IF(OR(E2=-1,E3=1,E3=-1),D3+E3,IF(E2=1,D3-2,D3))
・1000円
=IF(OR(E3=-1,E4=1,E4=-1),D4+E4,IF(E3=1,D4-5,D4))

〜500円から1円は5000円&1000円のセットを下方向にフィルコピー〜

投稿日時: 19/09/02 22:34:02
投稿者: WinArrow
投稿者のウェブサイトに移動

素人眼鏡 さん、ありがとうございます。
 
例示の表では、
必要個数と手持個数を金額に変換した場合、
手持金額が必要金額より、少ないため、両替できないと思います。

投稿日時: 19/09/02 22:38:09
投稿者: WinArrow
投稿者のウェブサイトに移動

個数だけの比較に加えて、金額も比較しないと両替する/しないの判断は出来ないと思っています。

回答
投稿日時: 19/09/03 10:26:47
投稿者: 素人眼鏡

WinArrow さんの引用:
素人眼鏡 さん、ありがとうございます。
 
例示の表では、
必要個数と手持個数を金額に変換した場合、
手持金額が必要金額より、少ないため、両替できないと思います。

あれ?と思って確認したら、書いている手持ち個数と必要個数の列が、作ったExcelと逆でした。
(個数から合計金額は出して不足が無いのは見てたのですが、レスからコピペした項目の並びに書き込むときの確認不足でした。。。)
 
   手    必   必  
   持    要   要  
   金    個   個  差  
   種    数   数  分  
10000     1    2  -1  
 5000     2    0   2  
 1000    10   10   0  
  500     1    2  -1  
  100    10    1   9  
   50     0    0   0  
   10     1    7  -6  
    5     3    1   2  
    1     0    1  -1  

 
 
 
 

投稿日時: 19/09/03 16:11:44
投稿者: WinArrow
投稿者のウェブサイトに移動

素人眼鏡 さん、再三ありがとうございます。
 
2列とも、必要個数になっています、
前回の表と列を入れ替えたとして

引用:
(1)上位から見ていき、不足する金種があれば、
   その上位貨幣を直ぐ下の貨幣に両替する。

ということならば、
金種:10000円が、必要個数=2、手持個数=1
必要個数>手持個数の関係にあり、上位貨幣は存在しないのでなので、
この時点でアウトになってしまいます。
 
しかし、下位貨幣であまりがあれば、代用することができます。
余りがあれば、
という条件は、全部、基本両替の計算した後で判明するのではないでしょうか?
 
銀行内では
両替:上位貨幣→下位貨幣
逆両替:下位貨幣→上位貨幣
と呼んでいるらしい。
 
 
 

回答
投稿日時: 19/09/04 17:35:01
投稿者: 素人眼鏡

WinArrow さんの引用:
素人眼鏡 さん、再三ありがとうございます。
 
2列とも、必要個数になっています、
前回の表と列を入れ替えたとして
引用:
(1)上位から見ていき、不足する金種があれば、
   その上位貨幣を直ぐ下の貨幣に両替する。

ということならば、
金種:10000円が、必要個数=2、手持個数=1
必要個数>手持個数の関係にあり、上位貨幣は存在しないのでなので、
この時点でアウトになってしまいます。
 
しかし、下位貨幣であまりがあれば、代用することができます。
余りがあれば、
という条件は、全部、基本両替の計算した後で判明するのではないでしょうか?
 
銀行内では
両替:上位貨幣→下位貨幣
逆両替:下位貨幣→上位貨幣
と呼んでいるらしい。

これは貨幣両替の性質上、両替可能(手持ち金額が足りている)である限り、全部の基本両替計算をした後でなくても判断していいものと考えます。
 
端的に言うと両替可能であるならば、下位貨幣で余りが出ないということはないです。
両替可能である(手持ち金額が足りている)=必要個数と同じ分布にすることができということです。
 
例えば、4人に9999円渡す場合、下記のような例でも両替/逆両替が可能です。
金種   手持個数 必要個数 必要差分                                               両替枚数
10000      0        0        0                                                      0
 5000      1        4       -3     1  1  1                                          3
 1000     33       16       17    -5 -5 -5 -1    -1                               -17
  500      2        4       -2              2 -1  2 -1                              2
  100      5       16      -11                 5     5                    1        11
   50      0        4       -4                          1  1  1  1  1  1 -2         4
   10     47       16       31                         -5 -5 -5 -5 -5 -5    -1    -31
    5      3        4       -1                                               2 -1   1
    1     11       16       -5                                                  5   5
合計   39996    39996     両替可    

また、手持ちの方が多い場合、その貨幣の余分も含めて計算していくので、逆両替の可否判定も両替と同様に上から下に行えば問題ないと考えます。
(逆両替はどこまでの下位貨幣で対応するかはありますが、それは運用的な考えなので今は捨ててます。)
 
要するに、両替/逆両替が可能ならマイナスがなくなるように繰り返し処理すれば最適になるかと。
 
説明しようと色々考えてたら条件が整理できたので、一旦、下記条件で見直してみました。
(下記の通り整理したら、確認した内では上から順に処理するようになったので、正解に近づいたかなと思います。)
 
[条件整理]
・自貨幣より上位で両替または逆両替が行われているか確認し、実施済みの場合は「""」(処理なし)
・直上の上位貨幣で両替または逆両替の判定が行われている場合、自貨幣の加算または減算を実施
・上位で何もしていない場合、以下を判定し結果を出す
 両替:自貨幣が0より大きい、且つ直下の貨幣が0未満のとき、
    または直上の貨幣が0より大きい、且つ自貨幣が0以下、且つ直下の貨幣が0未満のとき
 逆両替:自貨幣が0未満、且つ直下の貨幣から逆両替可能のとき
     または、直上の貨幣が0未満、且つ自貨幣から逆両替が不可、且つ直下の貨幣から
     逆両替が可能のとき
・これらに当てはまらない場合は「""」(処理なし)
 
上記の条件から、E(両替1)の式は下記にしました。
・10000円
=IF(AND(D2>0,D3<0),-1,IF(AND(D2<0,D3>=2),1,""))
・5000円
=IF(COUNTIF(E$2:E2,-5)+COUNTIF(E$2:E2,-2)>0,"",IF(E2=1,-2,IF(E2=-1,2,IF(OR(AND(COUNTIF(E$2:E2,-1)=0,D3<0,D4>=5),AND(COUNTIF(E$2:E2,-1)=0,D2<0,D3<2,D4>=5)),1,IF(OR(AND(COUNTIF(E$2:E2,-1)=0,D3>0,D4<0),AND(COUNTIF(E$2:E2,-1)=0,D2>0,D3<=0,D4<0)),-1,"")))))
・1000円
=IF(COUNTIF(E$2:E3,-5)+COUNTIF(E$2:E3,-2)>0,"",IF(E3=1,-5,IF(E3=-1,5,IF(OR(AND(COUNTIF(E$2:E3,-1)=0,D4<0,D5>=2),AND(COUNTIF(E$2:E3,-1)=0,D3<0,D4<5,D5>=2)),1,IF(OR(AND(COUNTIF(E$2:E3,-1)=0,D4>0,D5<0),AND(COUNTIF(E$2:E3,-1)=0,D3>0,D4<=0,D5<0)),-1,"")))))

〜500円から5円は5000円&1000円のセットを下方向にフィルコピー〜

・1円
=IF(COUNTIF(E$2:E9,-5)+COUNTIF(E$2:E9,-2)>0,"",IF(E9=1,-5,IF(E9=-1,5,"")))

投稿日時: 19/09/04 19:49:21
投稿者: WinArrow
投稿者のウェブサイトに移動

>(逆両替はどこまでの下位貨幣で対応するかはありますが、それは運用的な考えなので今は捨ててます。)
 
そもそも、ソフトは、運用する(運用しやすい)ことを目的に
作成するものなので、運用的な考えを除外したソフトは利用価値がない‥と思います。
 
自貨幣から、上位貨幣への逆両替と下位貨幣への両替が混在すると、
利用者は混乱するのではないでしょうか?
 
一つ下の貨幣で間に合うならが、下位貨幣で代用したってよいわけです。
どうしても逆両替しなければいけないわけではないと思います。
 
 
とりあえず、このあたりで、終了といたします。
 
ご意見ありがとうございました。