人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

エクセルでA列からG列の数値の合計の組合せを計算する方法を教えてください。

A?Gに記録される数値の内容は以下の通りです。

A 35か0か-30
B 25か0か-20
C 25か0か-20
D 25か0か-20
E 25か0か-20
F 15か0か-15
G 15か0か-15

のいずれかが記録されますので、合計の最大数は165、中間は0、最小は-165です。

各組合せによるA?Gの合計それぞれわかれば良いですが、
どのような組み合わせになるかもエクセル上に表示できるとなお良いです。

よろしくお願いします。

●質問者: harakiri2
●カテゴリ:コンピュータ
✍キーワード:エクセル 組合 計算
○ 状態 :終了
└ 回答数 : 3/3件

▽最新の回答へ

1 ● hiko4karasu
●20ポイント

スマートなやり方ではないですが、全ての組み合わせを一覧にしてみましょう。

「合計の最大数は165、中間は0、最小は-165」ということなので、

35か0か-30 は 35か0か-35

25か0か-20 は 25か0か-25

の間違いだと仮定して、

A1に

=(MOD(INT((ROW()-1)/3^6),3)-1)*35

B1に

=(MOD(INT((ROW()-1)/3^5),3)-1)*25

C1に

=(MOD(INT((ROW()-1)/3^4),3)-1)*25

D1に

=(MOD(INT((ROW()-1)/3^3),3)-1)*25

E1に

=(MOD(INT((ROW()-1)/3^2),3)-1)*15

F1に

=(MOD(INT((ROW()-1)/3^0),3)-1)*15

G1に

=SUM(A1:G1)

と入力、

これを2187行までコピーする。

これで一覧表ができます。

◎質問者からの返答

正しくは、合計の「最大数は165、中間は0、最小は-140」で、

35か0か-30、25か0か-20の方が正解でした。

その場合の式はどうなりますでしょうか?


2 ● Baku7770
●5ポイント

3進法7桁の数値を考えます。0000000は各列の1行目を合計することを意味し、同様に

2222222は各列の3行目を合計することを意味します。

十進0から2186までの間ですべての合計に対し、最大値、最小値、中間値を求めることになり

ます。合計に際してOFFSET関数あるいはINDIRECT関数を使えば自動的に計算が可能

です。

最大値、最小値はMax、Min関数を使えば出せるでしょうし、中間値はRank関数を応用

すれば求められます。

◎質問者からの返答

よくわかりませんが、ありがとうございます。


3 ● SALINGER
●100ポイント ベストアンサー

VBAで表示するマクロです。H列が合計になります。

A?Gの位置を保持した組み合わせの場合はMacro1で2187通り。


Sub Macro1()
 Application.ScreenUpdating = False
 Dim ha, hb, hc, hd, he, hf, hg
 Dim i1, i2, i3, i4, i5, i6, i7, c
 ha = Array(35, 0, -30)
 hb = Array(25, 0, -20)
 hc = hb
 hd = hc
 he = hd
 hf = Array(15, 0, -15)
 hg = hf
 c = 1
 For i1 = 0 To 2
 For i2 = 0 To 2
 For i3 = 0 To 2
 For i4 = 0 To 2
 For i5 = 0 To 2
 For i6 = 0 To 2
 For i7 = 0 To 2
 Cells(c, 1).Value = ha(i1)
 Cells(c, 2).Value = hb(i2)
 Cells(c, 3).Value = hc(i3)
 Cells(c, 4).Value = hd(i4)
 Cells(c, 5).Value = he(i5)
 Cells(c, 6).Value = hf(i6)
 Cells(c, 7).Value = hg(i7)
 Cells(c, 8).Value = ha(i1) + hb(i2) + hc(i3) + hd(i4) + he(i5) + hf(i6) + hg(i7)
 c = c + 1
 Next
 Next
 Next
 Next
 Next
 Next
 Next
 
 Range("A1:H2187").Sort Key1:=Range("H1"), Order1:=xlDescending, Header:= _
 xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
 SortMethod:=xlPinYin, DataOption1:=xlSortNormal
 Application.ScreenUpdating = True
End Sub

更に、単純に数字の組み合わせだけがほしいならば、Macro1の結果からMacro2を実行して、270通りです。

Sub Macro2()
 Application.ScreenUpdating = False
 Dim i As Integer
 Dim c As Integer
 Dim f As Boolean
 For i = 1 To 2187
 Range(Cells(i, 1), Cells(i, 7)).Sort Key1:=Cells(i, 1), Order1:=xlDescending, Header:=xlGuess, _
 OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, SortMethod _
 :=xlPinYin, DataOption1:=xlSortNormal
 Next
 For i = 2187 To 2 Step -1
 f = False
 c = 1
 While Cells(i, 8).Value = Cells(i - c, 8).Value
 If Cells(i, 1).Value = Cells(i - c, 1).Value And _
 Cells(i, 2).Value = Cells(i - c, 2).Value And _
 Cells(i, 3).Value = Cells(i - c, 3).Value And _
 Cells(i, 4).Value = Cells(i - c, 4).Value And _
 Cells(i, 5).Value = Cells(i - c, 5).Value And _
 Cells(i, 6).Value = Cells(i - c, 6).Value And _
 Cells(i, 7).Value = Cells(i - c, 7).Value Then
 f = True
 End If
 c = c + 1
 Wend
 If f Then
 Rows(i).Delete
 End If
 Next
 Application.ScreenUpdating = True
End Sub
◎質問者からの返答

すばらしいです。1発で解決しました。ありがとうございます。

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ