エクセルで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の合計それぞれわかれば良いですが、
どのような組み合わせになるかもエクセル上に表示できるとなお良いです。

よろしくお願いします。

回答の条件
  • 1人2回まで
  • 13歳以上
  • 登録:2010/03/16 10:20:41
  • 終了:2010/03/16 15:16:44

ベストアンサー

id:SALINGER No.3

SALINGER回答回数3454ベストアンサー獲得回数9692010/03/16 14:53:20

ポイント100pt

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
id:harakiri2

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

2010/03/16 15:15:14

その他の回答(2件)

id:hiko4karasu No.1

hiko4karasu回答回数106ベストアンサー獲得回数12010/03/16 13:19:48

ポイント20pt

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

「合計の最大数は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行までコピーする。

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

id:harakiri2

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

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

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

2010/03/16 14:42:33
id:Baku7770 No.2

Baku7770回答回数2832ベストアンサー獲得回数1812010/03/16 14:46:40

ポイント5pt

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

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

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

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

です。

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

すれば求められます。

id:harakiri2

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

2010/03/16 15:14:41
id:SALINGER No.3

SALINGER回答回数3454ベストアンサー獲得回数9692010/03/16 14:53:20ここでベストアンサー

ポイント100pt

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
id:harakiri2

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

2010/03/16 15:15:14

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません