Tekan F9 Hingga Tutup - Petua Excel

Isi kandungan

Menggunakan Excel untuk Menyelesaikan Model Yang Kompleks
Langkah-langkah Khusus untuk Masalah Berenang
Kembangkan Ukuran Kedekatan
Cara perlahan untuk menyelesaikannya
Makro untuk Menyimpan Hasil Semasa
Makro untuk Tekan F9 berulang kali dan Periksa Hasilnya
Sidebar mengenai Pembaharuan Skrin
Penyelesaian Penyelesaian Alternatif
Muat turun Buku Kerja
Tonton Video

Menggunakan Excel untuk Menyelesaikan Model Yang Kompleks

Lev adalah pesuruhjaya liga renang yang kompetitif. Dia menulis: "Saya adalah pesuruhjaya liga renang. Terdapat lapan pasukan tahun ini. Setiap pasukan menjadi tuan rumah satu pertemuan dan merupakan pasukan tuan rumah. Satu perjumpaan akan mempunyai 4 atau 5 pasukan. Bagaimana mengatur jadual sehingga setiap pasukan berenang setiap pasukan lain dua kali? Pada masa lalu, ketika kami mempunyai 5, 6, atau 7 pasukan, saya dapat menyelesaikannya dengan menekan F9 sehingga ditutup. Tetapi tahun ini, dengan 8 pasukan, ia tidak akan keluar. "

Salah satu kekangannya ialah sebilangan kolam hanya menawarkan 4 lorong, jadi anda hanya dapat memiliki 4 pasukan ketika kolam itu menjadi tuan rumah gala. Untuk kolam lain, mereka mungkin mempunyai 5, 6, atau lebih lorong, tetapi pertemuan yang ideal akan mempunyai pasukan tuan rumah ditambah dengan empat yang lain.

Cadangan saya: Tekan F9 lebih pantas! Untuk menolongnya: kembangkan "ukuran kedekatan" dalam model anda. Dengan cara itu, apabila anda menekan F9, anda dapat mengawasi satu nombor. Apabila anda menjumpai penyelesaian "lebih baik" daripada yang terbaik yang anda temukan, simpan sebagai penyelesaian terbaik perantaraan.

Langkah-langkah Khusus untuk Masalah Berenang

Senaraikan 8 pasukan tuan rumah di bahagian atas.
Berapa banyak kaedah untuk mengisi 4 lorong lain?
Senaraikan semua cara.
Berapa banyak kaedah untuk mengisi 3 lorong lain (untuk tempat kecil?). Senaraikan semua cara.
Gunakan RANDBETWEEN(1,35)untuk memilih pasukan untuk setiap perlawanan.

Perhatikan bahawa terdapat 35 8 kemungkinan cara untuk mengatur musim (2.2 trilion). Adalah mustahil untuk melakukan semuanya dengan PC di rumah. Sekiranya hanya ada 4000 kemungkinan, anda boleh melakukan semuanya, dan itu adalah video untuk hari lain. Tetapi dengan 2.2 trilion kemungkinan, meneka secara rawak lebih cenderung untuk mencari jalan penyelesaian.

Kembangkan Ukuran Kedekatan

Dalam senario renang, perkara yang paling penting ialah Adakah setiap pasukan berenang melawan setiap pasukan lain dua kali?

Ambil 8 nombor rawak semasa dan gunakan formula untuk merangka semua pertandingan. Senaraikan 28 kemungkinan perlawanan. Gunakan COUNTIFuntuk melihat berapa kali setiap pertandingan berlaku dengan nombor rawak semasa. Kira berapa 2 atau lebih besar. Tujuannya adalah untuk mendapatkan nombor ini menjadi 28.

Matlamat Sekunder: Terdapat 28 perlawanan. Masing-masing perlu berlaku dua kali. Itulah 56 pertarungan yang mesti berlaku. Dengan 8 kolam dan 6 dengan lima lorong, anda akan mempunyai 68 pertandingan. Ini bermakna beberapa pasukan akan berenang menentang pasukan lain sebanyak 3 kali dan mungkin 4 kali. Matlamat kedua: Pastikan sebanyak mungkin pasukan mempunyai 4 perlawanan. Matlamat tersier: Minimumkan Maksimum

Cara perlahan untuk menyelesaikannya

Tekan F9. Lihatlah hasilnya. Tekan F9 beberapa kali untuk melihat hasil yang anda perolehi. Apabila anda mendapat hasil yang tinggi, simpan 8 input dan ketiga pemboleh ubah output. Terus tekan F9 sehingga anda mendapat hasil yang lebih baik. Simpan yang satu dengan merakam 8 sel input dan 3 sel hasil.

Makro untuk Menyimpan Hasil Semasa

Makro ini menyimpan hasilnya ke baris seterusnya.

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 Sub

Makro untuk Tekan F9 berulang kali dan Periksa Hasilnya

Tulis makro untuk menekan F9 berulang kali, log hanya penyelesaian "lebih baik". Berhenti makro apabila anda mencapai hasil yang diinginkan pada 28 & 0.

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 Sub

Sidebar mengenai Pembaharuan Skrin

Sidebar: Pada mulanya, adalah "menyeronokkan" untuk menonton lelaran berulang. Tetapi anda akhirnya menyedari bahawa anda mungkin harus menguji berjuta-juta kemungkinan. Setelah Excel melukis semula skrin, memperlambat makro ke bawah. Gunakan Application.ScreenUpdating = Salah untuk tidak mengecat semula skrin.

Setiap kali anda mendapat jawapan baru atau setiap 1000, biarkan Excel melukis semula skrin. Masalah: Excel tidak melukis semula skrin melainkan penunjuk sel bergerak. Saya dapati bahawa dengan memilih sel baru sementara ScreenUpdating Benar, Excel akan mengecat semula skrin. Saya memutuskan untuk menukarnya 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").Value

Penyelesaian Penyelesaian Alternatif

Saya telah mempertimbangkan banyak tajuk untuk video ini: Tekan F9 Hingga Tutup, Tebak Hingga Benar, Penyelesaian Brute Force, Ukuran Kedekatan

Perhatikan bahawa saya telah menggunakan Solver untuk menyelesaikan masalah tersebut. Tetapi Solver tidak dapat menghampiri. Ia tidak pernah menjadi lebih baik daripada 26 pasukan ketika golnya adalah 28.

Perhatikan juga bahawa sebarang penyelesaian yang saya dapat dalam video ini adalah "dumb-luck". Tidak ada yang bijak mengenai kaedah penyelesaian. Sebagai contoh, makro tidak mengatakan, "Kita harus bermula dari penyelesaian terbaik setakat ini dan membuat beberapa penyesuaian mikro." Walaupun anda mendapat penyelesaian yang hanya tinggal satu nombor, ia menekan F9 secara membuta tuli sekali lagi. Mungkin ada cara yang lebih bijak untuk mengatasi masalah tersebut. Tetapi … sekarang … untuk pesuruhjaya renang kami, pendekatan ini berjaya.