近所のお店で、特定の商品を購入したレシートの合計金額が3,000円になると500円の商品券をいただけるセールを時々やっています。
うちの奥さんから、合計金額が3,000円や6,000円になるレシートの組合せを、10数枚のレシートの中から見つけられないかというお題をいただきました。
すぐに思いついた方法は、手元にあるレシートの全ての組合せ金額をExcelで計算して、計算した金額を小さい順に並べて3,000円や6,000円に近いレシートの組合せを探すというやり方です。
まずは計算例として、レシートが3枚の場合の計算方法を下の表に示します。
一番上の緑色セルがレシートの金額で、その金額に下にある「0」または「1」の数字と掛け算し、横方向に足し合わせた結果が「合計金額」になるので、そこから目標としている合計金額のレシートの組合せを探すという方法です。
この考え方を拡張して、レシート8枚の場合の計算例を下に示します。
このExcel表の構成ですが、上の緑色のセルに8枚分のレシートの金額を入れています。
計算した合計金額（Ｌ列）を、小さい順に確認することができるように、レシートの行に「フィルター」を追加しました。
「フィルター」の機能を使って、3,000円や6,000円に近い金額になるレシートの組み合わせを求めてみます。
最初に「合計金額」のセルの「▼」を押すと、表示する行を選択する画面が現れるので、「☑（すべて選択）」を押して✓を外します。
次に、右のスクロールバーを使って表示させたい金額の左側のチェックボックスに✓を入れます。今回は「3,027円」と「6,002円」の左のチェックボックスに✓を入れて「OK」ボタンを押しました。
そうすると、下の画面が表示されます。
例えば合計金額が「3027円」になるレシートの組合せは、この画像から「727円」「827円」「1473円」の組合せになることが分かります。
ちなみにこの表を作成するときに、D列からK列に0や1を順番に入れていくのは結構な手間で、レシートの枚数が増えるとかなり大変な作業です。
これらの「0」や「1」は二進数の並びにしていて、下の行にいくほど大きな数字にしています。
レシート8枚の場合、これらの二進数は「00000001～11111111」で、十進数だと「1～255」に相当するので、十進数の「1～255」からExcelの関数を使用して「0」や「1」が表示されるようにしました。
まずC列には、B列の十進数から求めた二進数の文字列を表示していますが、ここで使用している関数は、例えばセル「C5」の場合は「=RIGHT("0000000"&DEC2BIN(B5),8)」です。
自分はあまりExcelの関数に詳しくないので、最初は「00000001」にしたいのに「1」と表示されて苦労しましたが、「"0000000"&DEC2BIN(B5)」にすると、「＆」によって「0000000」と「1」の文字列をくっつけて表示できるので、なんとか「00000001」にできました。
ただ、くっつけるだけだと「255」は「000000011111111」になるので、右側の8文字だけを表示するように「RIGHT(______,8)」にしました。
次にセル「D5」には、「C5」の文字列「00000001」の一番左の文字「0」を抽出し、数字として「0」を入れる必要があるので、「=MID($C5,D$3,1)*1」と書きました。
この関数の中の「D$3」は、レシート金額の上に書いている数字「1」で、抽出する文字列の開始位置を指定しています。「C5」の文字列「00000001」の左から1個目の「0」を指定しています。
「MID(＿,＿,1)」の右端の「1」は、開始位置から何文字抽出するかの指定ですが、今回は1文字だけなので「1」にしています。
ただ、「MID(_,_,1)」で抽出できるのは文字列なので計算に使用できません。そのため「MID()」に数字の「1」を掛けて数字にするというのが「MID()*1」に「*1」が入っている理由です。
こうすることでレシートの枚数が増えても、あまり手間をかけずに計算する表を更新することができるようになりました。
ただ、レシートの枚数が20枚とかになるとファイルサイズが大きくなります。
改善したいのですが、今後の課題にしておきます。
よろしければ以下のバナーをクリックしていただけると励みになります！
