Excel Solver là gì?

2024 Tác giả: Abigail Brown | [email protected]. Sửa đổi lần cuối: 2023-12-17 07:05

Phần bổ trợ Excel Solver thực hiện tối ưu hóa toán học. Điều này thường được sử dụng để điều chỉnh các mô hình phức tạp với dữ liệu hoặc tìm giải pháp lặp đi lặp lại cho các vấn đề. Ví dụ: bạn có thể muốn điều chỉnh một đường cong qua một số điểm dữ liệu, sử dụng một phương trình. Solver có thể tìm thấy các hằng số trong phương trình phù hợp nhất với dữ liệu. Một ứng dụng khác là rất khó để sắp xếp lại một mô hình để biến đầu ra cần thiết trở thành chủ đề của một phương trình.

Solver ở đâu trong Excel?

Phần bổ trợ Solver được bao gồm trong Excel nhưng không phải lúc nào nó cũng được tải như một phần của cài đặt mặc định. Để kiểm tra xem nó đã được tải chưa, hãy chọn tab DATAvà tìm biểu tượng Solvertrong phần Analysis.

Nếu bạn không thể tìm thấy Bộ giải trong tab DỮ LIỆU thì bạn cần tải bổ trợ:

1. Chọn tab FILErồi chọn Options.

   

2. Trong hộp thoại Optionschọn Add-Instừ các tab ở phía bên trái.

   

3. Ở cuối cửa sổ, chọn Excel Add-intừ trình đơn thả xuống Managevà chọn Go…

   

4. Đánh dấu vào hộp kiểm bên cạnh Solver Add-invà chọn OK.

   

5. Lệnh Solverbây giờ sẽ xuất hiện trên tab DATA. Bạn đã sẵn sàng sử dụng Solver.

   

Sử dụng Solver trong Excel

Hãy bắt đầu với một ví dụ đơn giản để hiểu những gì Người giải quyết làm. Hãy tưởng tượng rằng chúng ta muốn biết bán kính sẽ cho một hình tròn có diện tích là 50 đơn vị hình vuông. Chúng ta biết phương trình diện tích hình tròn (A=pi r²). Tất nhiên, chúng tôi có thể sắp xếp lại phương trình này để cung cấp bán kính cần thiết cho một khu vực nhất định, nhưng vì lợi ích của ví dụ, hãy giả sử chúng tôi không biết cách thực hiện điều đó.

Tạo bảng tính với bán kính trong B1và tính diện tích trong B2bằng phương trình =pi ()B1 ^ 2.

Chúng tôi có thể điều chỉnh thủ công giá trị trong B1cho đến khi B2hiển thị giá trị đủ gần với 50. Tùy thuộc vào mức độ chính xác của chúng tôi cần phải được, đây có thể là một cách tiếp cận thực tế. Tuy nhiên, nếu chúng ta cần rất chính xác, sẽ mất nhiều thời gian để thực hiện các điều chỉnh cần thiết. Trên thực tế, đây thực chất là những gì Solver làm. Nó thực hiện điều chỉnh giá trị trong các ô nhất định và kiểm tra giá trị trong ô đích:

1. Chọn tab DATAvà Solver, để tải hộp thoại Solver Parameters

2. Đặt Mục tiêu ô thành Vùng, B2. Đây là giá trị sẽ được kiểm tra, điều chỉnh các ô khác cho đến khi ô này đạt đến giá trị chính xác.

   

3. Chọn nút cho Giá trị của:và đặt giá trị là 50. Đây là giá trị mà B2 sẽ đạt được.

   

4. Trong hộp có tiêu đề Bằng cách Thay đổi Ô Biến:nhập ô chứa bán kính, B1.

   

5. Để các tùy chọn khác theo mặc định và chọn Giải. Quá trình tối ưu hóa được thực hiện, giá trị của B1 được điều chỉnh cho đến khi B2 là 50 và hộp thoại Giải Kết quảđược hiển thị.

   

6. Chọn OKđể giữ giải pháp.

   

Ví dụ đơn giản này đã cho thấy cách hoạt động của bộ giải. Trong trường hợp này, chúng ta có thể dễ dàng tìm ra giải pháp hơn theo những cách khác. Tiếp theo, chúng ta sẽ xem xét một số ví dụ trong đó Solver đưa ra các giải pháp mà khó có thể tìm thấy bất kỳ cách nào khác.

Lắp Mô hình Phức tạp Sử dụng Phần bổ trợ Trình giải quyết Excel

Excel có một chức năng tích hợp để thực hiện hồi quy tuyến tính, điều chỉnh một đường thẳng thông qua một tập dữ liệu. Nhiều hàm phi tuyến tính phổ biến có thể được tuyến tính hóa có nghĩa là hồi quy tuyến tính có thể được sử dụng để phù hợp với các hàm như cấp số nhân. Đối với các chức năng phức tạp hơn, Bộ giải có thể được sử dụng để thực hiện 'thu nhỏ bình phương nhỏ nhất'. Trong ví dụ này, chúng ta sẽ xem xét việc kết hợp một phương trình có dạng ax ^ b + cx ^ dvới dữ liệu được hiển thị bên dưới.

Điều này bao gồm các bước sau:

1. Sắp xếp tập dữ liệu với các giá trị x trong cột A và các giá trị y trong cột B.
2. Tạo 4 giá trị hệ số (a, b, c và d) ở đâu đó trên bảng tính, chúng có thể được cung cấp các giá trị bắt đầu tùy ý.

3. Tạo một cột các giá trị Y phù hợp, sử dụng phương trình có dạng ax ^ b + cx ^ d, tham chiếu đến các hệ số được tạo ở bước 2 và các giá trị x trong cột A. Lưu ý rằng để sao chép công thức xuống cột, các tham chiếu đến các hệ số phải là tuyệt đối trong khi các tham chiếu đến các giá trị x phải là tương đối.

   

4. Mặc dù không cần thiết nhưng bạn có thể nhận được chỉ báo trực quan về mức độ phù hợp của phương trình bằng cách vẽ biểu đồ cả hai cột y so với giá trị x trên một biểu đồ phân tán XY. Sẽ rất hợp lý khi sử dụng các điểm đánh dấu cho các điểm dữ liệu gốc, vì đây là các giá trị rời rạc có nhiễu và sử dụng một đường cho phương trình phù hợp.

   

5. Tiếp theo, chúng ta cần một cách định lượng sự khác biệt giữa dữ liệu và phương trình phù hợp của chúng ta. Cách tiêu chuẩn để làm điều này là tính tổng của các chênh lệch bình phương. Trong cột thứ ba, đối với mỗi hàng, giá trị dữ liệu ban đầu cho Y được trừ đi giá trị phương trình được điều chỉnh và kết quả là bình phương. Vì vậy, trong D2, giá trị được cho bởi =(C2-B2) ^ 2Sau đó sẽ tính tổng tất cả các giá trị bình phương này. Vì các giá trị được bình phương nên chúng chỉ có thể là số dương.

   

6. Bây giờ bạn đã sẵn sàng để thực hiện tối ưu hóa bằng Solver. Có bốn hệ số cần được điều chỉnh (a, b, c và d). Bạn cũng có một giá trị mục tiêu duy nhất để giảm thiểu, tổng của sự khác biệt bình phương. Khởi chạy bộ giải, như ở trên và đặt các thông số của bộ giải để tham chiếu các giá trị này, như được hiển thị bên dưới.

   

7. Bỏ chọn tùy chọn để Đặt các biến không bị ràng buộc thành không phủ định, điều này sẽ buộc tất cả các hệ số phải nhận giá trị dương.

   

8. Chọn Giảivà xem lại kết quả. Biểu đồ sẽ cập nhật cho một dấu hiệu tốt về mức độ phù hợp. Nếu trình giải quyết không phù hợp trong lần thử đầu tiên, bạn có thể thử chạy lại. Nếu sự phù hợp đã được cải thiện, hãy thử giải quyết từ các giá trị hiện tại. Nếu không, bạn có thể thử cải thiện độ vừa vặn theo cách thủ công trước khi giải quyết.

   

9. Sau khi đã có được sự phù hợp tốt, bạn có thể thoát khỏi trình giải quyết.

Giải Mô hình Lặp đi lặp lại

Đôi khi có một phương trình tương đối đơn giản đưa ra kết quả đầu ra về một số đầu vào. Tuy nhiên, khi chúng ta cố gắng đảo ngược vấn đề, không thể tìm ra một giải pháp đơn giản. Ví dụ: công suất tiêu thụ của một chiếc xe được xấp xỉ cho bởi P=av + bv ^ 3trong đó v là vận tốc, a là hệ số cho lực cản lăn và b là hệ số cho lực cản khí động học. Mặc dù đây là một phương trình khá đơn giản nhưng không dễ dàng sắp xếp lại để đưa ra phương trình vận tốc mà xe sẽ đạt được đối với một công suất đầu vào nhất định. Tuy nhiên, chúng ta có thể sử dụng Solver để tìm vận tốc này một cách lặp đi lặp lại. Ví dụ: tìm vận tốc đạt được với công suất đầu vào là 740 W.

1. Thiết lập một bảng tính đơn giản với vận tốc, các hệ số a và b và công suất được tính toán từ chúng.

   

2. Khởi chạy Bộ giải và nhập nguồn điện, B5, làm mục tiêu. Đặt giá trị mục tiêu là 740và chọn vận tốc, B2, làm ô thay đổi. Chọn giảiđể bắt đầu giải pháp.

   

3. Bộ giải điều chỉnh giá trị của vận tốc cho đến khi công suất rất gần với 740, cung cấp vận tốc mà chúng tôi yêu cầu.

   

4. Giải quyết các mô hình theo cách này thường có thể nhanh hơn và ít bị lỗi hơn so với đảo ngược các mô hình phức tạp.

Việc hiểu các tùy chọn khác nhau có sẵn trong trình giải có thể khá khó khăn. Nếu bạn gặp khó khăn trong việc tìm kiếm một giải pháp hợp lý thì việc áp dụng các điều kiện biên cho các ô có thể thay đổi thường rất hữu ích. Đây là những giá trị giới hạn mà chúng không nên được điều chỉnh. Ví dụ, trong ví dụ trước, vận tốc không được nhỏ hơn 0 và cũng có thể đặt giới hạn trên. Đây sẽ là tốc độ mà bạn chắc chắn rằng chiếc xe không thể chạy nhanh hơn. Nếu bạn có thể đặt giới hạn cho các ô biến có thể thay đổi, thì nó cũng làm cho các tùy chọn nâng cao khác hoạt động tốt hơn, chẳng hạn như đa phần. Thao tác này sẽ chạy một số giải pháp khác nhau, bắt đầu từ các giá trị ban đầu khác nhau cho các biến.

Việc chọn Phương pháp Giải cũng có thể khó khăn. Simplex LP chỉ phù hợp với mô hình tuyến tính, nếu vấn đề không phải là tuyến tính, nó sẽ không thành công với thông báo rằng điều kiện này không được đáp ứng. Hai phương pháp còn lại đều phù hợp với phương pháp phi tuyến tính. GRG Nonlinear là nhanh nhất nhưng giải pháp của nó có thể phụ thuộc nhiều vào các điều kiện khởi động ban đầu. Nó có tính linh hoạt mà nó không yêu cầu các biến phải đặt giới hạn. Trình giải quyết Tiến hóa thường đáng tin cậy nhất nhưng nó yêu cầu tất cả các biến phải có cả giới hạn trên và giới hạn dưới, điều này có thể khó tính trước.

Phần bổ trợ Excel Solver là một công cụ rất mạnh có thể được áp dụng cho nhiều vấn đề thực tế. Để tiếp cận đầy đủ sức mạnh của Excel, hãy thử kết hợp Bộ giải với macro Excel.