Menggunakan Excel untuk Memecahkan Model Kompleks Apa Pun
Lev adalah komisaris liga renang kompetitif. Dia menulis: "Saya adalah komisaris liga renang. Ada delapan tim tahun ini. Setiap tim menyelenggarakan satu pertemuan dan merupakan tim tuan rumah. Pertemuan akan memiliki 4 atau 5 tim. Bagaimana mengatur jadwal sehingga setiap tim akan bertanding melawan setiap tim lain dua kali? Dulu, ketika kami memiliki 5, 6, atau 7 tim, saya bisa menyelesaikannya dengan menekan F9 hingga hampir habis. Tapi tahun ini, dengan 8 tim, tidak keluar. "
Salah satu kendala adalah bahwa beberapa pool hanya menawarkan 4 jalur, jadi Anda hanya dapat memiliki 4 tim saat pool tersebut menjadi tuan rumah gala. Untuk pool lain, mereka mungkin memiliki 5, 6, atau lebih jalur, tetapi pertemuan yang ideal adalah tim tuan rumah ditambah empat lainnya.
Saran saya: Tekan F9 lebih cepat! Untuk membantu itu: kembangkan "ukuran kedekatan" dalam model Anda. Dengan begitu, saat Anda menekan F9, Anda dapat memperhatikan satu nomor. Ketika Anda menemukan solusi yang "lebih baik" daripada yang terbaik yang pernah Anda temukan, simpan itu sebagai solusi terbaik menengah.
Langkah-langkah Khusus untuk Masalah Renang
- Buat daftar 8 tim tuan rumah di atas.
- Berapa banyak cara untuk mengisi 4 jalur lainnya?
- Buat daftar semua cara.
- Berapa banyak cara untuk mengisi 3 jalur lainnya (untuk venue kecil?). Buat daftar semua cara.
- Gunakan
RANDBETWEEN(1,35)untuk memilih tim untuk setiap pertandingan.
Perhatikan bahwa ada 35 8 kemungkinan cara untuk mengatur musim (2,2 triliun). "Tidak mungkin" melakukan semuanya dengan PC rumahan. Jika hanya ada 4000 kemungkinan, Anda dapat melakukan semuanya, dan itu adalah video untuk hari lain. Tetapi dengan 2,2 triliun kemungkinan, menebak secara acak lebih mungkin untuk menemukan solusi.
Kembangkan Ukuran Kedekatan
Dalam skenario renang, hal terpenting adalah Apakah setiap tim berenang melawan tim lain dua kali?
Ambil 8 angka acak saat ini dan gunakan rumus untuk merencanakan semua pertandingan. Buat daftar 28 kemungkinan pertandingan. Gunakan COUNTIFuntuk melihat berapa kali setiap pencocokan terjadi dengan nomor acak saat ini. Hitung berapa banyak 2 atau lebih besar. Tujuannya adalah menjadikan angka ini menjadi 28.
Gol Sekunder: Ada 28 pertandingan. Masing-masing perlu terjadi dua kali. Itu adalah 56 pertarungan yang harus terjadi. Dengan 8 pool dan 6 dengan lima jalur, Anda akan memiliki 68 pertarungan. Itu berarti beberapa tim akan berenang melawan tim lain 3 kali dan mungkin 4 kali. Gol sekunder: Pastikan sesedikit mungkin tim yang memiliki 4 pertandingan. Gol tersier: Minimalkan Max.
Cara Lambat untuk Mengatasi Ini
Tekan F9. Lihat hasilnya. Tekan F9 beberapa kali untuk melihat hasil yang Anda peroleh. Saat Anda mendapatkan hasil yang tinggi, simpan 8 input dan tiga variabel output. Terus tekan F9 sampai Anda mendapatkan hasil yang lebih baik. Simpan yang satu dengan merekam 8 sel input dan 3 sel hasil.
Makro untuk Menyimpan Hasil Saat Ini
Makro ini menyimpan hasil ke baris berikutnya.
Sub SaveThis() NR = Range("Z1048576").End(xlUp).Row + 1 Cells(NR, 26).Resize(1, 11).Value = Array(Range("c8").Value, _ Range("D8").Value, Range("E8").Value, Range("F8").Value, _ Range("G8").Value, Range("H8").Value, Range("I8").Value, _ Range("J8").Value, Range("O1").Value, Range("P1").Value, _ Range("Q1").Value) End SubMakro Tekan F9 Berulang Kali dan Periksa Hasilnya
Tulis makro untuk menekan F9 berulang kali, hanya mencatat solusi "lebih baik". Minta makro berhenti saat Anda mencapai hasil 28 & 0 yang diinginkan.
Sub TrySome() NR = Range("Z1048576").End(xlUp).Row + 1 Ctr = Range("T1").Value Application.ScreenUpdating = Range("AH2").Value SolutionFound = False GoAgain: ActiveSheet.Calculate Ctr = Ctr + 1 UseIt = 0 If Range("O1").Value> Range("AK1").Value Then UseIt = 1 ElseIf Range("O1").Value = Range("AK1").Value Then If Range("P1").Value 300 Then Application.ScreenUpdating = True Exit Sub End If If SolutionFound = True Then Application.ScreenUpdating = True Exit Sub End If If Ctr Mod 1000 = 0 Then Range("T1").Value = Ctr Application.ScreenUpdating = True If Selection.Address = "$T$1" Then Cells(NR, 34).Select Else Range("T1").Select End If Application.ScreenUpdating = Range("AH2").Value End If GoTo GoAgain End SubSidebar tentang ScreenUpdating
Sidebar: Pada awalnya, "menyenangkan" untuk melihat iterasi terus berjalan. Tetapi Anda akhirnya menyadari bahwa Anda mungkin harus menguji jutaan kemungkinan. Setelah Excel menggambar ulang layar memperlambat makro. Use Application.ScreenUpdating = False untuk tidak mengecat ulang layar.
Setiap kali Anda mendapat jawaban baru atau setiap 1000, biarkan Excel menggambar ulang layar. Masalah: Excel tidak menggambar ulang layar kecuali penunjuk sel bergerak. Saya menemukan bahwa dengan memilih sel baru saat ScreenUpdating adalah True, Excel akan mengecat ulang layar. Saya memutuskan untuk membuatnya bergantian antara sel Counter dan Hasil Terbaik Sejauh Ini.
Application.ScreenUpdating = True If Selection.Address = "$T$1" Then Cells(NR, 34).Select Else Range("T1").Select End If Application.ScreenUpdating = Range("AH2").ValueSolusi Pemecahan Alternatif
Saya mempertimbangkan banyak judul untuk video ini: Tekan F9 Sampai Dekat, Tebak Sampai Benar, Pemecahan Kekuatan Brute, Ukur Kedekatan
Perhatikan bahwa saya mencoba menggunakan Solver untuk menyelesaikan masalah. Tapi Solver tidak bisa mendekat. Tidak pernah lebih baik dari 26 tim saat golnya masih 28.
Perhatikan juga bahwa solusi apa pun yang saya dapatkan dalam video ini adalah "keberuntungan bodoh". Tidak ada yang cerdas tentang metode pemecahan. Misalnya, makro tidak mengatakan, "Sejauh ini kita harus mulai dari solusi terbaik dan membuat beberapa penyesuaian mikro." Bahkan jika Anda mendapatkan solusi yang jaraknya hanya satu nomor, itu akan menekan F9 lagi secara membabi buta. Mungkin ada cara yang lebih cerdas untuk mengatasi masalah tersebut. Tapi… sekarang… untuk komisaris renang kami, pendekatan ini berhasil.
Unduh Buku Kerja
Menonton video
Unduh berkas
Unduh file sampel di sini: Podcast2180.zip
