順列組み合わせ→エクセルの達人の皆様へ

質問

エクセルは使えるが、数学(算数)はからっきし駄目人間からの質問です。

商品が下記の通り7種類有ります(単価全部違います)
AAA　単価8500円
BBB　単価6800円
CCC　単価8000円
DDD　単価5800円
EEE　単価4800円
FFF　単価9000円
GGG　単価8800円

次回キャンペーンで、下記企画を打ち出す計画があります。
条件（１）同一商品を3個買えば、同一商品又は自分の好きな他の商品を1個無料進呈
条件（２）違う商品でも兎に角3個買えば、他の商品1個を無料進呈

無料進呈は当社負担なので、例えば最悪(売り手にとって)一番安い組み合わせ（EEEを3個で無料進呈FFF）の割合が多ければ、大損します(多分)(笑)。(全体で在庫が減るという営業側面的利益は別にして)、さて、問題は
（１）何種類の組み合わせがあり
（２）その組み合わせの具体例
上記でシミユレーションをしてみたいと思います。Visual Basic不得意です。出来ればEXCELの関数で、どうしてもVBなら一から勉強しますが(泣)

回答No.6

pc_knight

No.4です。「（３）３ヶとも異なる商品を選ぶ　」のところの
組合せ数の記述をまちがえていました。スミマセン。
＜正＞７Ｃ３＝７！／｛（７－３）！×３！｝｝＝（７×６×５）／（３×２）＝３５通り

＜誤＞７Ｃ２＝７！／｛（７－２）！×２！｝｝＝（７×６×５）／（３×２）＝３５通り

お礼

皆様ご回答多謝!　PC買替後、突然「教えてgoo」の読み書き不能に陥りました。お返事お礼遅れましたことをお詫びします。お手間と労力お掛けした皆様のご回答に対し感謝・感謝です。勉強になりました。
　　皆様に追記です。
全員の方に●ポイント●差し上げたいのですが、それも出来ませんので、勘弁して下さい。

回答No.5

nicotinism

No2 です。
慣れぬことはせぬもの。お恥ずかしい限り (^_^;)
３種類の組み合わせは８４
それに、それぞれ無料進呈が７種あるので５８８通りですが
No2さんの回答にあるとおり、
賢い消費者なら、無料進呈分も考慮して安い組み合わせを選択するでしょう
その場合は２１０通りかな？？？？

Sub test2()
Dim j1 As Integer
Dim j2 As Integer
Dim j3 As Integer
Dim j4 As Integer
Dim k As Integer

With Application

　　For j1 = 1 To 7
　　　　For j2 = j1 To 7
　　　　　　For j3 = j2 To 7
　　　　　　　　For j4 = j3 To 7 '←☆☆☆☆☆☆☆ここの j3 を 1 に変えると５８８通り
　　　　　　　　　　k = k + 1
　　　　　　　　　　Worksheets(2).Cells(k, 1).Value = Worksheets(1).Cells(j1, 1).Value
　　　　　　　　　　Worksheets(2).Cells(k, 2).Value = Worksheets(1).Cells(j1, 2).Value
　　　　　　　　　　Worksheets(2).Cells(k, 3).Value = Worksheets(1).Cells(j1, 3).Value

　　　　　　　　　　Worksheets(2).Cells(k, 4).Value = Worksheets(1).Cells(j2, 1).Value
　　　　　　　　　　Worksheets(2).Cells(k, 5).Value = Worksheets(1).Cells(j2, 2).Value
　　　　　　　　　　Worksheets(2).Cells(k, 6).Value = Worksheets(1).Cells(j2, 3).Value

　　　　　　　　　　Worksheets(2).Cells(k, 7).Value = Worksheets(1).Cells(j3, 1).Value
　　　　　　　　　　Worksheets(2).Cells(k, 8).Value = Worksheets(1).Cells(j3, 2).Value
　　　　　　　　　　Worksheets(2).Cells(k, 9).Value = Worksheets(1).Cells(j3, 3).Value

　　　　　　　　　　Worksheets(2).Cells(k, 10).Value = Worksheets(1).Cells(j4, 1).Value
　　　　　　　　　　Worksheets(2).Cells(k, 11).Value = Worksheets(1).Cells(j4, 2).Value
　　　　　　　　　　Worksheets(2).Cells(k, 12).Value = Worksheets(1).Cells(j4, 3).Value
　　　　　　　　Next
　　　　　　Next
　　　　Next
　　Next

End With
End Sub

お礼

皆様ご回答多謝!　PC買替後、突然「教えてgoo」の読み書き不能に陥りました。お返事お礼遅れましたことをお詫びします。お手間と労力お掛けした皆様のご回答に対し感謝・感謝です。勉強になりました。

回答No.4

pc_knight

ANo3 Sige1701さんのお答えにありますように「7種類から3個を選ぶ組み合わせは84とおり」です。（無料進呈を含めた組合せは８４×７＝５８８通り）
因みに３ヶ商品を選ぶケースは、次の３つに類別できます。
（１）３ヶとも同じ商品を選ぶ　→　７通り
（２）２ヶが同じ商品で、残り１ヶは他の商品を選ぶ
→７通り×６通り＝４２通り
（３）３ヶとも異なる商品を選ぶ　
→　７種の中から異なる３種を選ぶ組合せは、７Ｃ２＝７！／｛（７－２）！×２！｝｝＝（７×６×５）／（３×２）＝３５通り
合計が８４通りの組合せになります。
その組み合わせの具体例は、（１）・（２）・（３）について、考えながら手作業でインプットするか、（１）・（２）・（３）のロジックをVBＡでやるかだろうと思います。
VBＡは、勉強の楽しみを残して割愛します。

５８８通りの組合せの販売企画への活用ですが、同じ局面の自分を想定して見ました。
きっと５８８通りの各組合せの中から、損のし過ぎや儲け過ぎの両極端の組合せ販売はやめ、そこそこの利益の出せる組合せ範囲を取捨選択するとか・・・。商品群を複数のブロックに分け対象を絞り、損のし過ぎや儲け過ぎを小さくし、ブロック毎
に時期をずらしてキャンペーンをするとか・・。あっ大きなお世話でした。スミマセン。

お礼

皆様ご回答多謝!　PC買替後、突然「教えてgoo」の読み書き不能に陥りました。お返事お礼遅れましたことをお詫びします。お手間と労力お掛けした皆様のご回答に対し感謝・感謝です。勉強になりました。

回答No.3

sige1701

条件（１）については二人の方が回答していますので省略

条件（２）の組み合わせについて　

7種類から3個を選ぶ組み合わせは84とおり
84*7=588通りになると思います

商品名をA,B,C・・と省略して
A,A,B+B とA,B,A+B　は同じ

また、やすい買い物のしかたというクイズだとすると

A,A,G+G　という買い方ではなく　G,G,A+A
というのも考慮するのかな

お礼

皆様ご回答多謝!　PC買替後、突然「教えてgoo」の読み書き不能に陥りました。お返事お礼遅れましたことをお詫びします。お手間と労力お掛けした皆様のご回答に対し感謝・感謝です。勉強になりました。

回答No.2

nicotinism

アクセスのクエリなら直積で一分も掛からないのですが・・
Excel だとどうなんでしょ
条件（１）同一商品を3個買えば、同一商品又は自分の好きな他の商品を1個無料進呈
これは７パターンじゃなくて無料進呈のも計算に入れるですよね
imogasiさんのレスを見て気が付きました
同一商品の組み合わせは７パターンで
それぞれで無料進呈７品目が選ばれるから 7^2 = 49

条件（２）違う商品でも兎に角3個買えば、他の商品1個を無料進呈
こっちは、購入商品 7^3 = 343 パターン
それぞれで無料進呈７品目が加わるから 結局 7^4 = 2401
Excel のワークシート関数はほとんど分からないので
VBAだと
一番目のシートのA列を挿入して、下記のようになってるとして
1　AAA　8500
2　BBB　6800
3　CCC　8000
4　DDD　5800
5　EEE　4800
6　FFF　9000
7　GGG　8800

下記コードを標準モジュールに貼り付けて実行すれば
シート２の
A列～I列が購入商品パターン、J列からL列が無料進呈となると思います。

Option Explicit
Sub test()
Dim j1 As Integer
Dim j2 As Integer
Dim j3 As Integer
Dim j4 As Integer
Dim k As Integer

With Application

　　For j1 = 1 To 7
　　　　For j2 = 1 To 7
　　　　　　For j3 = 1 To 7
　　　　　　　　For j4 = 1 To 7
　　　　　　　　　　k = k + 1
　　　　　　　　　　Worksheets(2).Cells(k, 1).Value = Worksheets(1).Cells(j1, 1).Value
　　　　　　　　　　Worksheets(2).Cells(k, 2).Value = Worksheets(1).Cells(j1, 2).Value
　　　　　　　　　　Worksheets(2).Cells(k, 3).Value = Worksheets(1).Cells(j1, 3).Value

　　　　　　　　　　Worksheets(2).Cells(k, 4).Value = Worksheets(1).Cells(j2, 1).Value
　　　　　　　　　　Worksheets(2).Cells(k, 5).Value = Worksheets(1).Cells(j2, 2).Value
　　　　　　　　　　Worksheets(2).Cells(k, 6).Value = Worksheets(1).Cells(j2, 3).Value

　　　　　　　　　　Worksheets(2).Cells(k, 7).Value = Worksheets(1).Cells(j3, 1).Value
　　　　　　　　　　Worksheets(2).Cells(k, 8).Value = Worksheets(1).Cells(j3, 2).Value
　　　　　　　　　　Worksheets(2).Cells(k, 9).Value = Worksheets(1).Cells(j3, 3).Value

　　　　　　　　　　Worksheets(2).Cells(k, 10).Value = Worksheets(1).Cells(j4, 1).Value
　　　　　　　　　　Worksheets(2).Cells(k, 11).Value = Worksheets(1).Cells(j4, 2).Value
　　　　　　　　　　Worksheets(2).Cells(k, 12).Value = Worksheets(1).Cells(j4, 3).Value
　　　　　　　　Next
　　　　　　Next
　　　　Next
　　Next
End With
End Sub

お礼

皆様ご回答多謝!　PC買替後、突然「教えてgoo」の読み書き不能に陥りました。お返事お礼遅れましたことをお詫びします。お手間と労力お掛けした皆様のご回答に対し感謝・感謝です。勉強になりました。

回答No.1

imogasi

(A)同一商品を買う例
買う品物に７種ありそれに無料進呈が７種あり、
買う商品と無料進呈品は影響を受けないから
７ｘ７＝４９
重複順列＝７＾２ 49
（B)違う商品でも兎に角3個+無料進呈１個
重複順列＝７＾４ 2401
式
（A） Sheet1の
A1:D1にAを入れる。
下記式を入れる
A2 =CHAR(CODE(A$1)+INT((ROW()-1)/7))
B2 =CHAR(CODE(B$1)+INT((ROW()-1)/7))
C2 =CHAR(CODE(C$1)+INT((ROW()-1)/7))
D2 =CHAR(CODE(D$1)+(MOD(ROW()-1,7)))
(B) Sheet2の
A1:D1にAを入れる
下記式を入れる ２４０１行まで式を複写する。
A2 =CHAR(CODE(A$1)+MOD(INT((ROW()-1)/7^3),7))
B2 =CHAR(CODE(B$1)+MOD(INT((ROW()-1)/7^2),7))
C2 =CHAR(CODE(C$1)+MOD(INT((ROW()-1)/7),7))
D2 =CHAR(CODE(D$1)+(MOD(ROW()-1,7)))
これで組み合わせの表ができる。A-Gを商品名で置換する（７回）
すれば商品組み合わせ表らしきなる。
エクセル向きの問題ではないと思う。
組み合わせや順列の問題は、COMBIN、PERMUT関数はあっても、実際の組み合わせ表示等はエクセル向きではない。
VBAでやる問題かもしれない。
上記まで考えたが、自信なし。参考にしてください。（B)で重複した
同じ組み合わせを数えているかもしれない。これを参考にできれば
よく考えてください。
正しい良い回答を期待するなら、数学のカテに質問すべきと思う。

お礼

皆様ご回答多謝!6月末にPC買替後、突然「教えてgoo」の読み書き不能に陥りました。お返事お礼遅れましたことをお詫びします。お手間と労力お掛けした皆様のご回答に対し感謝・感謝です。勉強になりました。