• ベストアンサー

エクセルでどうすればできるでしょうか。

4人の子供が水泳のメドレーリレーをします。↓のタイムがありますが誰がどの種目を泳げば最速のタイムになるでしょうか。と言うような計算をしたいのです。    クロール 背泳 平泳ぎ バタフライ   (単位、秒) A君  10  15  20  17 B君  15  20  25  23 C君  12  13  23  24 D君  11  14  20  22 たとえば、ABCDの順で泳ぐと75秒。DCBAの順で泳ぐと66秒になりますが。 どうでしょう・・・。

質問者が選んだベストアンサー

  • ベストアンサー
  • rukuku
  • ベストアンサー率42% (401/933)
回答No.4

はじめまして rukukuと申します。 人数、種目数ともさほど多くないので、力業でいきます 考え方としては、 Sheet1に A B C D 1 クロール 背泳 平泳ぎ バタフライ 2 A君 10 15 20 17 3 B君 15 20 25 23 4 C君 12 13 23 24 5 D君 11 14 20 22 という元データがあるとき、 Sheet3に A B C D 1 クロール 背泳 平泳ぎ バタフライ 2 A君 1 3 B君 1 4 C君 1 5 D君 1 という表を作り =SUMPRODUCT(Sheet1!B2:E5,Sheet3!B2:E5) で合計タイムを求めます。 「1」のついた種目が各選手の出場種目となります。  この「1」の位置を動かしていき、合計が最小になる 組み合わせを探せば、ご希望の結果が得られます。  4種目に4人が出場するのなら、その組み合わせは 4×3×2×1=24通り、なので手動で組み合わせを 求めても、さほど大変ではないかと思います。  これが4種目に5人から4人選んで出場となると、 5×4×3×2=120通りとなり、手動では大変に なってきます。こうなると、マクロやその他の方法を 使う方がよいかと思います。 以下がそのマクロの1例です Sheet1に A B C D 1 クロール 背泳 平泳ぎ バタフライ 2 A君 10 15 20 17 3 B君 15 20 25 23 4 C君 12 13 23 24 5 D君 11 14 20 22 6 E君 13 17 24 21 とあるときに、 A B C D E F 1 クロール 背泳 平泳ぎ バタフライ 2 A君 B君 C君 D君 75 3 A君 B君 C君 E君 74 4 A君 B君 D君 C君 74 5 A君 B君 D君 E君 71 6 A君 B君 E君 C君 78 … という表が得られます。後はF列をキーに並べ替えを行う と良いでしょう。 (4種目に4人ですと、種目数が変わったときや人数が変 わったときにどこを変えればいいのかが分かりにくいので 選手を5人にしました。)  マクロを実行する前に、「Sheet1」「Sheet2」「Sheet3」 の3つのワークシートを用意してください。「Sheet2」 「Sheet3」はマクロが内容を消しますので空のものにしてく ださい。新規のブックを使用すると安全です。  ワークシートが用意できましたら、「Sheet1」に上の例の ような表を作ってください。 マクロの使い方  1)「ツール」-「マクロ」-「新しいマクロの記録」  2)「マクロの記録」のダイアログボックスで「OK」  3)「ツール」-「マクロ」-「記録終了」  4)「ツール」-「マクロ」-「Visual Basic Editor」  5)「Micirosoft Visual Basic」で「表示」-「コード」  6)下のマクロのSub Macro2() ~ End Sub をコピー&ぺースト  7)Excelに戻る  8)「ツール」-「マクロ」-「マクロ」  9)「Macro2」を選択して実行 Sub Macro2() '初期設定 ''変数の定義 Dim Swimmer(5) As Boolean '選手。すでに種目が決まっていると真(True) 'まだ決まっていなければ偽(False) Dim i As Integer '種目1に出場する選手(の番号) Dim j As Integer '種目2に出場する選手(の番号) Dim k As Integer '種目3に出場する選手(の番号) Dim l As Integer '種目4に出場する選手(の番号) Dim r As Integer '出力する行番号 Dim Overlap As Boolean '重複の確認 ''出力先のシートの初期化 Worksheets("Sheet2").Range("A1").EntireRow.EntireColumn.ClearContents Worksheets("Sheet2").Range("A1") = Worksheets("Sheet1").Range("B1") Worksheets("Sheet2").Range("B1") = Worksheets("Sheet1").Range("C1") Worksheets("Sheet2").Range("C1") = Worksheets("Sheet1").Range("D1") Worksheets("Sheet2").Range("D1") = Worksheets("Sheet1").Range("E1") r = 1 ''計算用のシートの初期化 Worksheets("Sheet3").Range("A1").EntireRow.EntireColumn.ClearContents Worksheets("Sheet1").Range("A:A").Copy (Worksheets("Sheet3").Range("A1")) Worksheets("Sheet1").Range("1:1").Copy (Worksheets("Sheet3").Range("A1")) '組み合わせとその組み合わせのタイムを求める For i = 1 To 5 For j = 1 To 5 For k = 1 To 5 For l = 1 To 5 'Swimmer(1)~Swimmer(5)を一旦すべてFalseに Swimmer(1) = False Swimmer(2) = False Swimmer(3) = False Swimmer(4) = False Swimmer(5) = False Overlap = False '重複の確認 If Swimmer(i) = False Then Swimmer(i) = True Else Overlap = True If Swimmer(j) = False Then Swimmer(j) = True Else Overlap = True If Swimmer(k) = False Then Swimmer(k) = True Else Overlap = True If Swimmer(l) = False Then Swimmer(l) = True Else Overlap = True '重複がなければその組み合わせについて計算を実行し結果を出力 If Not Overlap = True _ Then '計算シートへ記入 Worksheets("Sheet3").Range("B2:IV65536").ClearContents Worksheets("Sheet3").Cells(i + 1, 2) = 1 Worksheets("Sheet3").Cells(j + 1, 3) = 1 Worksheets("Sheet3").Cells(k + 1, 4) = 1 Worksheets("Sheet3").Cells(l + 1, 5) = 1 '結果の出力 r = r + 1 Worksheets("Sheet2").Cells(r, 1) = Worksheets("Sheet1").Cells(i + 1, 1) Worksheets("Sheet2").Cells(r, 2) = Worksheets("Sheet1").Cells(j + 1, 1) Worksheets("Sheet2").Cells(r, 3) = Worksheets("Sheet1").Cells(k + 1, 1) Worksheets("Sheet2").Cells(r, 4) = Worksheets("Sheet1").Cells(l + 1, 1) Worksheets("Sheet2").Cells(r, 6) = WorksheetFunction. _ SumProduct(Worksheets("Sheet1").Range("B2:E6"), Worksheets("Sheet3").Range("B2:E6")) End If Next l Next k Next j Next i End Sub  このマクロは、あらゆる組み合わせ(例えば全種目を A君に出場など)から出場種目が重複するものを除いた 組み合わせを出力します。  人数、種目数が少ないので、このような単純な考え方 でも良いと思います。  また、理解のしやさすさの点から、繰り返し処理で簡略 化できるところもそのままになっています。  4人に変更するには For i = 1 To 5 For j = 1 To 5 For k = 1 To 5 For l = 1 To 5 の「5」をすべて「4」にしてください。  人数を増やす場合には、他にも変更する点があります。 減らす場合にはいまのままで構いません。 Dim Swimmer(5) As Boolean の5を人数以上にしてください。 Swimmer(1) = False Swimmer(2) = False Swimmer(3) = False Swimmer(4) = False Swimmer(5) = False を人数分に追加してください。 ↓こちらで質問してみるのもよいかと思います。 http://cgi.fuji.ne.jp/~fj2094/cgi-bin1/wwwlng.cgi

po-
質問者

お礼

あっりがとうございます。本当にありがとうございます。 わたくし、エクセルの「エ」位しか出来ない素人者でしたが、それでも十分理解できました。理解は出来てませんが、見事結果がでました。あのコピペには感動しました。「エクセル全くかじってません」と、補足説明出したつもりが出てなく、難解用語が出てきてどうしようかと思いましたが、大変わかりやすい説明に感服いたしました。 >という表を作り =SUMPRODUCT(Sheet1!B2:E5,Sheet3!B2:E5) で合計タイムを求めます。 「1」のついた種目が各選手の出場種目となります。 ↑の  =SUMPRODUCT(Sheet1!B2:E5,Sheet3!B2:E5) これすらも判らないのです。お恥ずかしい・・・。 しかし、マクロの意味もわからずに言われたとおりにやってみて出来たときにはビックリしました。 本当にありがとうございました。

その他の回答 (5)

  • imogasi
  • ベストアンサー率27% (4737/17069)
回答No.6

#2で解答を入れた者ですが、そのアルゴリズムによる具体的なコーディング例を載せます。 何をしているかは、コーヂングの中のコメントを見てください。 要点は「各競技レコードをソートして、第1位が出場すれば良いのです。唯し、同一人が2種目で第1位を占めていることがあるので、次席と差の大きい方に出て、少ない方は次席を第1位に繰り上げる」ことです。 途中「STOP」のStatementが入っていますが変化をシート上で確認し追えるようにしたものです。同記録の者がいる時の繰り上がりについて、まだ不完全ですが取りあえず載せます。 Sheet2の第2行目に最終残った者が出場するのが一番記録が良いはず。この問題は最短時間経路やクリチカルパス問題と似ているような気がするのですが、説明は私の手におえない。結果は12+20+20+17=69と出ました。演算スピードは他者より早いはず。 Sub test01() shumoku = 4 '競技種目数 ク+背+平+バタ '------最終行数の取得 Sheets("sheet1").Select e = Range("a1").CurrentRegion.Rows.Count ' MsgBox e '-----名前の列のコピー Columns(1).Select Selection.Copy For i = 1 To shumoku + 1 J = (i - 1) * 3 + 1 Sheets("Sheet2").Select Columns(J).Select ActiveSheet.Paste Next i '------競技記録タイムのコピー For i = 2 To shumoku + 1 Sheets("sheet1").Select Columns(i).Select Selection.Copy J = (i - 1) * 3 - 1 Sheets("Sheet2").Select Columns(J).Select ActiveSheet.Paste Next i '------競技記録タイムのソート For i = 2 To shumoku * 3 Step 3 Range(Cells(1, i - 1), Cells(e, i + 1)).Select Selection.Sort Key1:=Cells(1, i), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _ :=xlPinYin Next i '------次順位の者との競技レコードの差分の計算 For i = 2 To shumoku * 3 Step 3 For J = 2 To e - 1 Cells(J, i + 1) = Cells(J + 1, i) - Cells(J, i) Next J Next i '-------第1位の者で同一人はいないか '------おれば第2位の者との差の大きい種目で出場さ   せる(残す) '------第2位の者との差の小さい方の種目は第2位を   上行に繰り上げる p01: kuriageari = "n" For i = 1 To shumoku * 3 Step 3 Name = Cells(2, i) For J = i + 3 To shumoku * 3 Step 3 If Cells(2, J) = Name Then If Cells(2, J + 2) >= Cells(2, i + 2) Then Range(Cells(2, i), Cells(2, i + 2)).Select Selection.Delete Shift:=xlUp kuriageari = "y" Else Range(Cells(2, J), Cells(2, J + 2)).Select Selection.Delete Shift:=xlUp kuriageari = "y" End If Stop 'プロセス確認のため入れた End If Next J Next i '-----繰り上げ者がいるともう一度繰り返し If kuriageari = "y" Then GoTo p01 End Sub

  • rukuku
  • ベストアンサー率42% (401/933)
回答No.5

下の部分の表が崩れてしまったので送信し直します A B C D 1 クロール 背泳 平泳ぎ バタフライ 2 A君 1 3 B君 1 4 C君 1 5 D君 1  A    B    C    D     E 1     クロール 背泳 平泳ぎ バタフライ 2 A君   1 3 B君         1 4 C君            1 5 D君                  1

参考URL:
http://cgi.fuji.ne.jp/~fj2094/cgi-bin1/wwwlng.cgi
  • psss
  • ベストアンサー率28% (59/209)
回答No.3

>1の方の解答は、1つの種目に出ると、他の種目には出られないことを組み込んでないのではないでしょうか。  いいえ。For~Nextの開始値を考えて記述すれば、どちらでも組めますが?

  • imogasi
  • ベストアンサー率27% (4737/17069)
回答No.2

こう言うロジックになるのでしょうか。ちょっと難しくて。間違っていたらごめんなさい。批評をお願いします。 #1の方の解答は、1つの種目に出ると、他の種目には出られないことを組み込んでないのではないでしょうか。 (1)データの各列の前に名前の列を挿入する。 (2)データの各列の後に差分をいれる列を挿入する。 第2行で言うと A名前 Bクロ-ルデータ C差分 D名前 E背泳データ F差分 G名前 H平泳ぎデータ I差分 J名前 Kバタフライデータ L差分とする。 (3)A列の名前をD,G、Jに複写する。 (4)ABCをB列で昇順ソートする。DEF列もE列で、GHIもH列で JKLもK列で昇順ソートする。 (5)すぐ上の行との差を計算し表示する。B3には=B3-B2をいれ、B4,B5に複写する。E,H、K列も同じくする。  ---- (6)一番上の第3行で差分の一番大きいL列のAの5に注目してバタフライはAの17秒に出てもらう。Aのデータの「組み」(JKL)には色をつける。 他の種目には出れないから、色をつけて、以後はないものと考え為マークする。 (7)C,F,Iの第4行の1,1,3のうち一番大きい3に注目して、平泳ぎはDの20に出てもらう。Dのデータの組にはAと違う色をつける。 (8)背泳は第1のCが残っているので、13秒で出てもらう。 (9)クロールは残るBに15秒で出てもらう。 合計=17+20+13+15=65が最小。 以下表がOKWEBでは乱れると思いますがご免。$はスペースを示す。 クロール 背泳 A B     C      D     E     F a 10 $ c 13 $ d 11 1 d 14 1 c 12 1 a 15 1 b 15 3 b 20 5 b 15 $ c 13 $ <-選択 ---- 平泳ぎ バタフライ G     H     I      J       K     L a 20 $ a 17 $ d 20 0 d 22 5 c 23 3 b 23 1 b 25 2 c 24 1 d 20 $ a 17 $ <-選択

  • psss
  • ベストアンサー率28% (59/209)
回答No.1

 VBAの基本は判っているとして方法だけを説明します。 (実は休み中なので、手元にエクセルが動くマシンがない)  配列変数を設定して、選手と種目の組み合わせでタイムを代入します。  次に、選手毎に、4種目ですからFor~Nextを4重で組んで、各組み合わせの合計を出しつつ、IF文で比較、常に合計が小さいほうの、合計と配列変数の組み合わせを保存し、最後にその保存した結果を表示出来る様にすればいいと思います。  実際、もっとスマートな計算方法もあるでしょうが、100人程度までの選出であれば、問題ないでしょう。

関連するQ&A

専門家に質問してみよう