Nhấn F9 cho đến khi đóng - Mẹo Excel

Mục lục

Sử dụng Excel để giải quyết mọi mô hình phức tạp
Các bước cụ thể cho vấn đề bơi lội
Xây dựng thước đo mức độ gần gũi
Cách chậm để giải quyết vấn đề này
Macro để lưu kết quả hiện tại
Macro để nhấn F9 nhiều lần và kiểm tra kết quả
Thanh bên về Cập nhật màn hình
Các giải pháp giải quyết thay thế
Tải xuống Sổ làm việc
Xem video

Sử dụng Excel để giải quyết mọi mô hình phức tạp

Lev là ủy viên của một liên đoàn bơi lội cạnh tranh. Anh viết: "Tôi là ủy viên của một liên đoàn bơi lội. Năm nay có tám đội. Mỗi đội đăng cai tổ chức một lần gặp nhau và là đội chủ nhà. Một lần gặp mặt sẽ có 4 hoặc 5 đội. Cách sắp xếp lịch thi đấu để đội nào cũng bơi đối kháng mỗi đội khác hai lần? Trước đây, khi chúng tôi có 5, 6 hoặc 7 đội, tôi có thể giải quyết bằng cách nhấn F9 cho đến khi kết thúc. Nhưng năm nay, với 8 đội, điều đó sẽ không ra mắt. "

Một trong những hạn chế là một số hồ bơi chỉ cung cấp 4 làn đường, vì vậy bạn chỉ có thể có 4 đội khi nhóm đó tổ chức gala. Đối với các nhóm khác, họ có thể có 5, 6 hoặc nhiều làn hơn, nhưng cuộc gặp gỡ lý tưởng sẽ có đội chủ nhà cộng với bốn người khác.

Đề xuất của tôi: Nhấn F9 nhanh hơn! Để hỗ trợ điều đó: hãy phát triển "thước đo mức độ gần gũi" trong mô hình của bạn. Bằng cách đó, khi bạn nhấn F9, bạn có thể để mắt đến một số. Khi bạn tìm thấy một giải pháp "tốt hơn" so với giải pháp tốt nhất bạn đã tìm thấy, hãy lưu đó làm giải pháp tốt nhất trung gian.

Các bước cụ thể cho vấn đề bơi lội

Liệt kê 8 đội chủ nhà dẫn đầu.
Có bao nhiêu cách lấp 4 làn đường còn lại?
Liệt kê tất cả các cách.
Có bao nhiêu cách để lấp đầy 3 làn đường còn lại (đối với các vị trí nhỏ?). Liệt kê tất cả các cách.
Sử dụng RANDBETWEEN(1,35)để chọn đội cho mỗi trận đấu.

Lưu ý rằng có 35 8 cách có thể để sắp xếp mùa (2,2 nghìn tỷ). Sẽ là "không thể" để làm tất cả chúng với một máy tính gia đình. Nếu chỉ có 4000 khả năng, bạn có thể làm tất cả, và đó là video cho một ngày khác. Nhưng với 2,2 nghìn tỷ khả năng, phỏng đoán ngẫu nhiên có nhiều khả năng tìm ra giải pháp hơn.

Xây dựng thước đo mức độ gần gũi

Trong kịch bản bơi, điều quan trọng nhất là mỗi đội có bơi với đội khác hai lần không?

Lấy 8 số ngẫu nhiên hiện tại và sử dụng công thức để vẽ ra tất cả các kết quả phù hợp. Liệt kê 28 kết hợp có thể có. Sử dụng COUNTIFđể xem mỗi trận đấu diễn ra bao nhiêu lần với các số ngẫu nhiên hiện tại. Đếm xem có bao nhiêu là 2 hoặc lớn hơn. Mục tiêu là nâng con số này lên 28.

Mục tiêu phụ: Có 28 trận đấu. Mỗi lần cần xảy ra hai lần. Đó là 56 trận đấu phải xảy ra. Với 8 hồ bơi và 6 với năm làn đường, bạn sẽ có 68 trận đấu xảy ra. Điều đó có nghĩa là một số đội sẽ bơi với các đội khác 3 lần và có thể là 4 lần. Mục tiêu phụ: Đảm bảo ít đội có 4 trận đấu nhất có thể. Mục tiêu thứ ba: Giảm thiểu giá thầu CPC

Cách chậm để giải quyết vấn đề này

Nhấn F9. Nhìn vào kết quả. Nhấn F9 một vài lần để xem kết quả bạn nhận được. Khi bạn nhận được kết quả cao, hãy lưu 8 đầu vào và ba biến đầu ra. Tiếp tục nhấn F9 cho đến khi bạn nhận được kết quả tốt hơn. Lưu một ô đó bằng cách ghi lại 8 ô đầu vào và 3 ô kết quả.

Macro để lưu kết quả hiện tại

Macro này lưu kết quả vào hàng tiếp theo.

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

Macro để nhấn F9 nhiều lần và kiểm tra kết quả

Viết macro để nhấn F9 liên tục, chỉ ghi lại các giải pháp "tốt hơn". Đặt macro dừng khi bạn nhận được kết quả mong muốn là 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

Thanh bên về Cập nhật màn hình

Thanh bên: Lúc đầu, rất "vui" khi xem các lần lặp lại. Nhưng cuối cùng bạn nhận ra rằng bạn có thể phải thử nghiệm hàng triệu khả năng. Việc để Excel vẽ lại màn hình sẽ làm chậm macro. Sử dụng Application.ScreenUpdating = False để không sơn lại màn hình.

Mỗi khi bạn nhận được một câu trả lời mới hoặc mỗi 1000 câu, hãy để Excel vẽ lại màn hình. Sự cố: Excel không vẽ lại màn hình trừ khi con trỏ ô di chuyển. Tôi thấy rằng bằng cách chọn một ô mới trong khi Cập nhật màn hình là Đúng, Excel sẽ vẽ lại màn hình. Tôi quyết định đặt nó xen kẽ giữa ô Bộ đếm và ô Kết quả tốt nhất cho đến nay.

Application.ScreenUpdating = True If Selection.Address = "$T$1" Then Cells(NR, 34).Select Else Range("T1").Select End If Application.ScreenUpdating = Range("AH2").Value

Các giải pháp giải quyết thay thế

Tôi đã cân nhắc nhiều tiêu đề cho video này: Nhấn F9 Cho đến Khi Đóng, Đoán Cho đến Khi Chính xác, Giải Quyết Lực lượng Brute, Đo Độ gần

Lưu ý rằng tôi đã thử sử dụng Solver để giải quyết vấn đề. Nhưng Solver không thể đến gần. Nó không bao giờ tốt hơn 26 đội khi mục tiêu là 28.

Cũng lưu ý rằng bất kỳ giải pháp nào mà tôi nhận được trong video này là "may mắn". Không có gì thông minh về phương pháp giải quyết. Ví dụ, vĩ mô không nói, "Chúng ta nên bắt đầu từ giải pháp tốt nhất cho đến nay và thực hiện một số điều chỉnh vi mô." Ngay cả khi bạn nhận được một giải pháp chỉ là một số, nó sẽ nhấn F9 một lần nữa. Có thể có một cách thông minh hơn để tấn công vấn đề. Nhưng… ngay bây giờ… đối với ủy viên bơi lội của chúng tôi, cách tiếp cận này đã hiệu quả.